The foreign key still works even though partition was detached. Is this behavior expected? I can't find the answer in the document. If it is expected behavior , please ignore the bug I reported a few days ago.
tender wang <tndrw...@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 <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/ >>> >>
