Re: WaitForOlderSnapshots in DETACH PARTITION causes deadlocks

On Mon, Jul 24, 2023 at 07:40:04PM +0000, Imseih (AWS), Sami wrote:
> WaitForOlderSnapshots is used here to ensure that snapshots older than
> the start of the ALTER TABLE DETACH CONCURRENTLY are completely removed
> to guarantee consistency, however it does seem to cause deadlocks for at
> least RR/SERIALIZABLE transactions.
>
> There are cases [2] in which certain operations are accepted as not being
> MVCC-safe, and now I am wondering if this is another case? Deadlocks are
> not a good scenario, and WaitForOlderSnapshot does not appear to do
> anything for READ COMMITTED transactions.
>
> So do we actually need WaitForOlderSnapshot in the FINALIZE code? and
> Could be acceptable that this operation is marked as not MVCC-safe like
> the other aforementioned operations?

I guess that there is an argument with lifting that a bit. Based on
the test case your are providing, a deadlock occuring between the
FINALIZE and a scan of the top-level partitioned table in a
transaction that began before the DETACH CONCURRENTLY does not seem
like the best answer to have from the user perspective. I got to
wonder whether there is room to make the wait for older snapshots in
the finalize phase more robust, though, and actually make it wait
until the first transaction commits rather than fail because of a
deadlock like that.

> Perhaps I am missing some important point here, so any feedback will be
> appreciated.

Adding Alvaro in CC as the author of 71f4c8c6 for input, FYI.
--
Michael