Paul Foerster <paul.foerster@xxxxxxxxx> writes: > On 26 Nov 2024, at 22:25, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> I'm suspicious that our repair recipe might not have accounted >> for self-reference FKs fully, but that's just a gut feeling at >> this point. > Of course, it contains no secret data. Please find the full log below. According to the add constraint statement, it is a self reference. > Thanks for looking into it. Okay, so I was able to reproduce this from scratch on HEAD: regression=# create table p_ci_pipelines(partition_id int, id int, primary key(partition_id,id), auto_canceled_by_partition_id int, auto_canceled_by_id int) partition by LIST (partition_id); CREATE TABLE regression=# create table ci_pipelines partition of p_ci_pipelines FOR VALUES IN ('100', '101', '102'); CREATE TABLE regression=# ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE regression=# SELECT conrelid::pg_catalog.regclass AS "constrained table", conname AS constraint, confrelid::pg_catalog.regclass AS "references", pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;', conrelid::pg_catalog.regclass, conname) AS "drop", pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;', conrelid::pg_catalog.regclass, conname, pg_catalog.pg_get_constraintdef(oid)) AS "add" FROM pg_catalog.pg_constraint c WHERE contype = 'f' AND conparentid = 0 AND (SELECT count(*) FROM pg_catalog.pg_constraint c2 WHERE c2.conparentid = c.oid) <> (SELECT count(*) FROM pg_catalog.pg_inherits i WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table WHERE partrelid = i.inhparent)); constrained table | constraint | references | drop | add -------------------+-----------------+----------------+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- p_ci_pipelines | fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL; (1 row) I doubt that there's anything actually wrong with the catalog state at this point (perhaps Alvaro would confirm that). That leads to the conclusion that what's wrong is the release notes' query for fingering broken constraints, and it needs some additional test to avoid complaining about (I suspect) self-reference cases. regards, tom lane