On 2021-Nov-12, Tom Lane wrote:
> I wasn't aware of $SUBJECT ... were you?
Yeah, I remember pointing out that DETACH and DROP and not fully correct
for serializability, but I can't find any thread where I said it in so
many words. At the time I had no ideas on how to fix it; the idea of
waiting for snapshots to go away didn't occur to anybody, or at least it
didn't reach me.
> AFAICS, the only real way to fix this is to acquire lock on
> the target partition and then wait out any snapshots that are
> older than the lock, just in case those transactions would look
> at the partitioned table later. I'm not sure if we want to go
> there, but if we don't, we at least have to document this gotcha.
ALTER TABLE DETACH PARTITION CONCURRENTLY already has a
wait-for-snapshots phase. It doesn't fix this problem, because it
doesn't wait for all snapshots, just the ones holding any lock on the
parent table. I'm not sure how upset would people be if we made it wait
on *all* snapshots, and worse, to make it in all cases and not just
CONCURRENTLY.
I understand that the behavior is not fully correct, but given the way
most people are going to use this (which is that they're no longer
terribly interested in the data of the partition being detached/dropped)
and the severity of the penalties if we implement a full solution, I
lean towards documenting it rather than fixing it.
Another option might be to play with the trade-offs in the CONCURRENTLY
mode vs. the regular one. If we make the CONCURRENTLY mode wait for all
snapshots, we would only be making worse a mode that's already
documented to potentially take a long time. So people who want safety
can use that mode, and the others are aware of the risk.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)