Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails - Mailing list pgsql-hackers

From tender wang
Subject Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Date
Msg-id CAHewXN=YC_6vFmoXuVz=ZA5+Uvqbi37xcEVJKWX_hSRixNd+Bw@mail.gmail.com
Whole thread Raw
In response to Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails  (tender wang <tndrwang@gmail.com>)
List pgsql-hackers
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. 

tender wang <tndrwang@gmail.com> 于2023年8月4日周五 17:04写道:
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.
 

tender wang <tndrwang@gmail.com> 于2023年8月3日周四 17:34写道:
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.

Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年8月3日周四 17:02写道:
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/

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Simplify some logical replication worker type checking
Next
From: Richard Guo
Date:
Subject: Check volatile functions in ppi_clauses for memoize node