Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Amit Langote <amitlangote09@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2021-03-23T14:18:26Z
Lists: pgsql-hackers 
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)

Commits

  1. Make detach-partition-concurrently-4 less timing sensitive

  2. Improve documentation on DETACH PARTITION lock levels

  3. Track detached partitions more accurately in partdescs

  4. Allow a partdesc-omitting-partitions to be cached

  5. Fix relcache inconsistency hazard in partition detach

  6. Don't add a redundant constraint when detaching a partition

  7. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY

  8. Let ALTER TABLE Phase 2 routines manage the relation pointer

  9. Check default partitions constraints while descending