Re: BUG #19420: Zombie FK exists after partition is detached. - Mailing list pgsql-bugs

On 2026-Feb-27, PG Bug reporting form wrote:

> I have child table  with FK defined as folllows
> 
> ALTER TABLE IF EXISTS tst.child_test_1
>     ADD CONSTRAINT child_test_1_parent_id_parent_part_by_fkey FOREIGN KEY
> (parent_id, parent_part_by)
>     REFERENCES tst.maintenance_test_1_p20260218 (id, part_by) MATCH SIMPLE
>     ON UPDATE NO ACTION
>     ON DELETE CASCADE;
> 
> Somehow postgresql retains the foreign keys of the detached/dropped
> partitions, to the parent table partitions.

I don't understand this example fully because (unless I misunderstood)
it's incomplete.  The partition seems to have a foreign key to a
partition of a different partitioned table ...?  As Laurenz said, we
purposely preserve foreign keys on detach.  But we had bugs in this area
in previous versions, so maybe the problem is simply that you set up the
partitions with the old versions, and the FKs have not been updated.
See one of those fixes, probably the most relevant one, here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=53af9491a0439720094a11b72602952d79f59ac7

Version 16.0 already contained this fix, so it certainly shouldn't
happen with 16.6; but I don't know what would happen if you were
previously running 15.8 or older and then pg_upgrade'd your way to 16.
It might be that the upgrade would preserve the broken FKs somehow.

we published this query (in the release notes) that should hopefully
display FKs that are broken in this way; maybe try that:

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)) +
    CASE WHEN pg_catalog.pg_partition_root(conrelid) = confrelid THEN
              (SELECT count(*) FROM pg_catalog.pg_partition_tree(confrelid)
                WHERE level = 1)
         ELSE 0 END);


> I try the following methods:
> partman.run_maintenance('tst.child_test_1')
> partman.run_maintenance()
> ALTER TABLE tst.child_test_1 DETACH PARTITION <child_test_p_20260218>
> 
> The error persisted regardless of the method used.

If you can still recreate the problem tables in 16.11, can you provide a
standalone reproducer, as a SQL script starting from an empty database?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)