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)
