Horton

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Alexander Law <exclusion@gmail.com>

View thread View on postgr.es
From: Alexander Lakhin <exclusion@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>, Tender Wang <tndrwang@gmail.com>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>, Junwang Zhao <zhjwpku@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Guillaume Lelarge <guillaume@lelarge.info>, Michael Paquier <michael@paquier.xyz>
Date: 2024-10-25T14:00:00Z
Lists: pgsql-hackers 
Hello Alvaro and Tender Wang,

24.10.2024 17:00, Alexander Lakhin wrote:
> Please look at a new anomaly introduced with 53af9491a. When running the
> following script:
> CREATE TABLE t (a int, b int, PRIMARY KEY (a, b));
> CREATE TABLE pt (a int, b int, FOREIGN KEY (a, b) REFERENCES t(a, b))
>  PARTITION BY LIST (a);
>
> CREATE TABLE tp1 (x int, a int, b int);
> ALTER TABLE tp1 DROP COLUMN x;
>
> ALTER TABLE pt ATTACH PARTITION tp1 FOR VALUES IN (1);
>
> ALTER TABLE pt DETACH PARTITION tp1;

I've also discovered another anomaly with a similar setup, but it's not
related to 53af9491a.

CREATE TABLE t (a int, PRIMARY KEY (a));
INSERT INTO t VALUES (1);

CREATE TABLE pt (b int, a int) PARTITION BY RANGE (a);
ALTER TABLE pt DROP COLUMN b;
ALTER TABLE pt ADD FOREIGN KEY (a) REFERENCES t ON DELETE SET DEFAULT (a);

CREATE TABLE tp1 PARTITION OF pt FOR VALUES FROM (1) TO (2);
ALTER TABLE pt DETACH PARTITION tp1;

DELETE FROM t;
\d+ t

This script ends up with:
ERROR:  invalid attribute number 2
ERROR:  cache lookup failed for attribute 2 of relation 16398

(Perhaps it deserves a separate discussion.)

Best regards,
Alexander

Commits

  1. Fix some more bugs in foreign keys connecting partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Create foreign key triggers in partitioned tables too