BUG #19420: Zombie FK exists after partition is detached.

On Friday, February 27, 2026, PG Bug reporting form <noreply@postgresql.org>
wrote:

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>

This bug report list is probably not presently the best place to report
this. The error seems like it most likely manifests from pgpartman and
should be reported there. Reporting bugs against unsupported minor
releases is also usually a bit problematic.

To keep it here a full reproducer using only core features and a supported
release would be requested.

David J.

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

I'm thinking about that too, but I wanted to make sure there wasn't a
better solution first.

pt., 27 lut 2026 o 15:45 Laurenz Albe <laurenz.albe@cybertec.at> napisał(a):

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

--
Zapraszam na moją stronę www. <http://www.nureczka.pl&gt;

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)