Re: ALTER TABLE DETACH PARTITION violates serializability

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> On 2021-Nov-12, Tom Lane wrote:
>> How about adding an option UNSAFE (orthogonal to CONCURRENTLY) that
>> activates the current behavior, and without it we wait for everything?

> Something like that might be the best compromise. I don't like having
> to grab AEL on the parent table (which the non-concurrent version does)
> for as long as every old snapshot is gone, so changing the default
> behavior without any escape hatch doesn't sound very pallatable.

> I think the best behavior is the CONCURRENTLY option with your
> additional wait phase -- if only it worked in a transaction block, it
> would be perfect. Users doing DDL as strong as DETACH should be willing
> to wait potentially long time for it to finish, but only if it doesn't
> disrupt concurrent load involving other partitions.

I grant that a lot of people will be less worried about the transactional
semantics problem than about how strong a lock they need. I'd be willing
to settle for "DETACH CONCURRENTLY is safe and plain DETACH isn't", as
long as that's very prominently documented.

However ... it appears to me that focusing on the lock strength for
the bare DETACH is misleading. When I was experimenting with this
stuff yesterday, I observed that if I had any foreign keys on the
partitioned table, DETACH would insist on AEL both on the partition
*and* on the table that's the other end of the FK constraint.
I suppose that's needed to get rid of triggers and pg_constraint
entries. But it seems to put a large hole in the argument that
you're going to be able to avoid strong locks and corresponding
waits/blockages when you do a DETACH.

regards, tom lane