Re: ALTER TABLE DETACH PARTITION violates serializability

On 2021-Nov-12, Tom Lane wrote:

> Meh ... "wrong by default" doesn't seem like it fits the Postgres ethos.

I certainly agree with this, yeah.

> 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 don't feel a need to add UNSAFE as an option to CONCURRENTLY, though.
Users are supposed to be already aware that it takes a long time, and it
doesn't block queries using the remaining partitions.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/