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 <tndrw...@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 <alvhe...@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/ >> >
