Oversight the DetachPartitionFinalize() again, I found the root cause why 'r_p_id_fkey' wat not removed.
DetachPartitionFinalize() call the GetParentedForeignKeyRefs() func to get tuple from pg_constraint that will be delete but failed.
according to the comments, the GetParentedForeignKeyRefs() func get the tuple reference me not I reference others.
I try to fix this bug :
i. ConstraintSetParentConstraint() should not be called in DetachPartitionFinalize(), because after conparentid was set to 0,
we can not find inherited foreign keys.
ii. create another function like GetParentedForeignKeyRefs(), but the ScanKey should be conrelid field not confrelid.
I quickly test on my above solution in my env, can be solve above issue.
Oversight the DetachPartitionFinalize(), I found another bug below:
postgres=# CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id);
CREATE TABLE
postgres=# CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE r_1 (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE r (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL,
postgres(# FOREIGN KEY (p_id) REFERENCES p (id)
postgres(# ) PARTITION BY list (id);
CREATE TABLE
postgres=# ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
ALTER TABLE
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# insert into r_1 values(1,1);
ERROR: insert or update on table "r_1" violates foreign key constraint "r_p_id_fkey"
DETAIL: Key (p_id)=(1) is not present in table "p".
After detach operation, r_1 is normal relation and the inherited foreign key 'r_p_id_fkey' should be removed.
I think the code to determine that fk of a partition is inherited or not is not enough.
For example, in this case, foreign key r_1_p_id_fkey1 is not inherited from parent.
If conform->conparentid(in DetachPartitionFinalize func) is valid, we should recheck confrelid(pg_constraint) field.
I try to fix this problem in the attached patch.
Any thoughts.
On 2023-Aug-03, tender wang wrote:
> I think old "sub-FK" should not be dropped, that will be violates foreign
> key constraint.
Yeah, I've been playing more with the patch and it is definitely not
doing the right things. Just eyeballing the contents of pg_trigger and
pg_constraint for partitions added by ALTER...ATTACH shows that the
catalog contents are inconsistent with those added by CREATE TABLE
PARTITION OF.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/