ALTER TABLE DETACH PARTITION violates serializability

I wasn't aware of $SUBJECT ... were you?

Here's a demonstration:

drop table if exists pk, fk, pk1, pk2;
create table pk (f1 int primary key) partition by list(f1);
create table pk1 partition of pk for values in (1);
create table pk2 partition of pk for values in (2);
insert into pk values(1);
insert into pk values(2);
create table fk (f1 int references pk);
insert into fk values(1);
insert into fk values(2);

In session 1, next do

regression=# begin isolation level serializable;
BEGIN
regression=*# select * from unrelated_table; -- to set the xact snapshot
...

Now in session 2, do

regression=# delete from fk where f1=2;
DELETE 1
regression=# alter table pk detach partition pk2;
ALTER TABLE

Back at session 1, we now see what's not only a serializability
violation, but a not-even-self-consistent view of the database:

regression=*# select * from fk;
f1
----
1
2
(2 rows)

regression=*# select * from pk;
f1
----
1
(1 row)

This is slightly ameliorated by the fact that if session 1 has
already touched either pk or fk, locking considerations will
block the DETACH. But only slightly.

(Dropping a partition altogether has the same issue, of course.)

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.

regards, tom lane