Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

So I was about ready to get these patches pushed, when I noticed that in
REPEATABLE READ isolation mode it is possible to insert rows violating
an FK referencing the partition that is being detached. I'm not sure
what is a good solution to this problem.

The problem goes like this:

/* setup */
drop table if exists d4_primary, d4_primary1, d4_fk;
create table d4_primary (a int primary key) partition by list (a);
create table d4_primary1 partition of d4_primary for values in (1);
insert into d4_primary values (1);
create table d4_fk (a int references d4_primary);

/* session 1 */
begin isolation level repeatable read;
select * from d4_primary;

/* session 2 */
alter table d4_primary detach partition d4_primary1 concurrently;
-- blocks
-- Cancel wait: Ctrl-c

/* session 1 */
insert into d4_fk values (1);
commit;

At this point, d4_fk contains the value (1) which is not present in
d4_primary.

This doesn't happen in READ COMMITTED mode; the INSERT at the final step
fails with "insert or update in table f4_fk violates the foreign key",
which is what I expected to happen here too.

I had the idea that the RI code, in REPEATABLE READ mode, used a
different snapshot for the RI queries than the transaction snapshot.
Maybe I'm wrong about that.

I'm looking into that now.

--
Álvaro Herrera Valdivia, Chile
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)