Обсуждение: BUG #19420: Zombie FK exists after partition is detached.

The following bug has been logged on the website:

Bug reference:      19420
Logged by:          Agnieszka Chodkowska
Email address:      a.chodkowska@gmail.com
PostgreSQL version: 16.6
Operating system:   Linux, Debian 12.2.0 64 bit
Description:

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;

I updated configuration according to documentation as follows:
retention_keep_table=false ,
retention_keep_index = false

Somehow postgresql retains the foreign keys of the detached/dropped
partitions, to the parent table partitions.

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.

The following bug has been logged on the website:

Bug reference:      19420
Logged by:          Agnieszka Chodkowska
Email address:      a.chodkowska@gmail.com
PostgreSQL version: 16.6
Operating system:   Linux, Debian 12.2.0 64 bit
Description:       

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>

On Fri, 2026-02-27 at 09:09 +0000, PG Bug reporting form wrote:
> Logged by:          Agnieszka Chodkowska
> PostgreSQL version: 16.6
> Operating system:   Linux, Debian 12.2.0 64 bit
>
> 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;

This is a table partition with a foreign key to another table partition,
correct?

> I updated configuration according to documentation as follows:
> retention_keep_table=false ,
> retention_keep_index = false

These are not PostgreSQL configuration parameters...

> Somehow postgresql retains the foreign keys of the detached/dropped
> partitions, to the parent table partitions.

That is as expected.  partitions are tables in their own right and can
entertain foreign key constraints to other tables, independent of their
role as partitions of a partitioned table.

> 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.

I'd say that you have to drop the foreign key constraint explicitly.

Yours,
Laurenz Albe

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)

On 2026-Mar-02, Álvaro Herrera wrote:

> [...] 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;

Sorry, I misspoke -- the fix was not in 16.0, but for 16.5 in that
branch.  It's still true that 16.6 contains the fix.  Here's a rundown
of release numbers on each branch for that bugfix:

Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Branch: master Release: REL_18_BR [53af9491a] 2024-10-22 16:01:18 +0200
Branch: REL_17_STABLE Release: REL_17_1 [5914a22f6] 2024-10-22 16:01:18 +0200
Branch: REL_16_STABLE Release: REL_16_5 [2aaf2a28b] 2024-10-22 16:01:18 +0200
Branch: REL_15_STABLE Release: REL_15_9 [5d83bad6b] 2024-10-22 16:01:18 +0200
Branch: REL_14_STABLE Release: REL_14_14 [46a8c27a7] 2024-10-22 16:01:18 +0200
Branch: REL_13_STABLE Release: REL_13_17 [d20194cea] 2024-10-22 16:01:18 +0200

    Restructure foreign key handling code for ATTACH/DETACH
    
    ... to fix bugs when the referenced table is partitioned.


(Here I must decidedly credit
    Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
for working on this fix.)

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)