Subject: Detaching a partition with a FK on itself is not possible

From: Christophe Courtois <[email-masked]>
Date: Thu, 17 Mar 2022 18:31:37 +0100

Hi,

A customer has found a limitation, more than a bug:

When a partitioned table has a foreign key that points to itself,
and this FK points only to lines in the same partition
(the partition key is part of the FK),
you cannot detach the partition: PostgreSQL claims that
the constraint is violated ;
although it's impossible that the remaining partitions
contain lines pointing to the partition to-be-detached (or vice versa).

In some way this is logical:
the FK on a detached partition is still there, and points to the partitioned table.

For a human, this sounds illogical:
the data in the detached partition is « self-contained » and points to the same table.

It's not possible to modify the inherited constraint before detaching the table,
and not possible to detach the table because of the constraint.

The only realistic workaround that we found was to get rid of the global FK, 
and rebuild independent FKs on each partition: logical but tedious, and error-prone to maintain.

A suggestion:
when the FK is on the partition itself and contains the partition key,
allow to rewrite the constraint to point to the detached partition,
or at least allow to drop it before detaching (in the same transaction).
I have no idea if some syntax changes would be necessary,
and no idea how easy to implement it would be. Is it worth it?

The script below reproduces the case.

Thanks for any comment.

\timing off

DROP TABLE IF EXISTS demo1, demo2, demo3, demo  ;

-- A table with 3 partitions and a FK to itself ;
-- * the partition key is in the PK and FK *
CREATE TABLE  demo (
    i  int,
    j  int,
    rj int,
    z  text )
PARTITION BY LIST (i);

ALTER TABLE demo ADD CONSTRAINT demo_pk PRIMARY KEY (i,j);
ALTER TABLE demo ADD CONSTRAINT demo_fk FOREIGN KEY (i,rj) REFERENCES demo (i,j) DEFERRABLE;

CREATE TABLE  demo1  PARTITION OF demo FOR VALUES IN (1);
CREATE TABLE  demo2  PARTITION OF demo FOR VALUES IN (2); 
CREATE TABLE  demo3  PARTITION OF demo FOR VALUES IN (3);

-- few data in each partition

INSERT INTO demo (i,j,rj) VALUES (1,10, null);
--  data pointing to the same partition: detaching this partition will have problems:
INSERT INTO demo (i,j,rj) VALUES (2, 21, null);
INSERT INTO demo (i,j,rj) VALUES (2, 31, 21);
--  no FK used:
INSERT INTO demo (i,j,rj) VALUES (3, 31, null);

\d+ demo
\d+ demo2

TABLE demo1 ;
TABLE demo2 ;
TABLE demo3 ;

-- Detaching partitions
\set ECHO queries

\echo "Detaching demo3: it works (FK unused)"
BEGIN ;
    ALTER TABLE demo DETACH PARTITION demo3 ;
    \echo "Note that the constraint still points to the partitioned table"
    \d+ demo3
ROLLBACK ;

BEGIN ;
  \echo "Cannot DETACH!"
  \echo "This is our problem"
  ALTER TABLE demo DETACH PARTITION demo2 ;
  -- ERROR:  removing partition "demo2" violates foreign key constraint "demo_i_rj_fkey1"
  --  DETAIL : Key (i, rj)=(2, 21) is still referenced from table "demo".
ROLLBACK ;

\echo 
\echo "Trying work arounds"
\echo 

BEGIN ;
  \echo "Drop FK only on partition: FAIL, not possible"
  ALTER TABLE demo2 DROP CONSTRAINT demo_fk ; 
  -- ERROR:  cannot drop inherited constraint "demo_fk" of relation "demo2"
  ALTER TABLE demo DETACH PARTITION demo2 ;
  -- fail
ROLLBACK ;

BEGIN ;
  \echo "UPDATE FK : works but destroys data and costly"
  UPDATE demo2 SET rj=null ;
  ALTER TABLE demo DETACH PARTITION demo2 ;
ROLLBACK ;

BEGIN ;
  \echo "UPDATE FK (partition key): not allowed and would be costly"
  UPDATE demo2 SET i=null ;
  --ERROR:  new row for relation "demo2" violates partition constraint
  ALTER TABLE demo DETACH PARTITION demo2 ; --KO
ROLLBACK ;

BEGIN ;
  \echo "DROP whole constraint, DETACH, recreate : works but costly"
  ALTER TABLE demo DROP CONSTRAINT demo_fk ;
  ALTER TABLE demo DETACH PARTITION demo2 ;
  ALTER TABLE demo ADD CONSTRAINT demo_fk FOREIGN KEY (i,rj) REFERENCES demo (i,j);
ROLLBACK ;

\echo "Re-declare keys on partitions only: works but painful"
BEGIN ;
  ALTER TABLE demo1 ADD CONSTRAINT demo_fk1 FOREIGN KEY (i,rj) REFERENCES demo1 ;
  ALTER TABLE demo2 ADD CONSTRAINT demo_fk2 FOREIGN KEY (i,rj) REFERENCES demo2 ;
  ALTER TABLE demo3 ADD CONSTRAINT demo_fk3 FOREIGN KEY (i,rj) REFERENCES demo3 ;
  ALTER TABLE demo DROP CONSTRAINT demo_fk ;
  ALTER TABLE demo DETACH PARTITION demo2 ;
ROLLBACK ;

-- 
Christophe Courtois
Consultant Dalibo
https://dalibo.com/

---

From: Arne Roland <[email-masked]>
Date: Thu, 17 Mar 2022 17:58:04 +0000

Hi,

I don't think this a bug, but a feature request. I therefore think hackers would be more appropriate.

I don't see how an additional syntax to modify the constraint should help.

If I'd want to fix this, I'd try to teach the detach partition code about self referencing foreign keys. It seems to me like that would be the cleanest solution, because the user doesn't need to care about this at all.

I don't think, I'll spend time on this in the near future though.

Regards
Arne

---

From: Jehan-Guillaume de Rorthais <[email-masked]>
Date: Mon, 21 Mar 2022 11:36:34 +0100

Hi,

On Thu, 17 Mar 2022 17:58:04 +0000

> would be more appropriate.

+1

I changed the list destination

> I don't see how an additional syntax to modify the constraint should help.

Me neiher.

> If I'd want to fix this, I'd try to teach the detach partition code about
> self referencing foreign keys. It seems to me like that would be the cleanest
> solution, because the user doesn't need to care about this at all.

Teaching the detach partition about self referencing means either:

* it's safe to remove the FK
* we can rewrite the FK for self referencing

Both solution are not ideal from the original schema and user perspective.

Another solution could be to teach the create partition to detect a self
referencing FK and actually create a self referencing FK, not pointing to the
partitioned table, and of course issuing a NOTICE to the client.

---

From: Arne Roland <[email-masked]>
Date: Mon, 21 Mar 2022 10:50:46 +0000

From: Jehan-Guillaume de Rorthais <[email-masked]>

Sent: Monday, March 21, 2022 11:36
Subject: Re: Detaching a partition with a FK on itself is not possible
 > I changed the list destination
Thanks

> partitioned table, and of course issuing a NOTICE to the client.
That's what I meant. I didn't think about the NOTICE, but that's a good idea.

Regards
Arne