public inbox for [email protected]
help / color / mirror / Atom feedBUG #19420: Zombie FK exists after partition is detached.
3+ messages / 3 participants
[nested] [flat]
* BUG #19420: Zombie FK exists after partition is detached.
@ 2026-02-27 09:09 PG Bug reporting form <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2026-02-27 09:09 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19420
Logged by: Agnieszka Chodkowska
Email address: [email protected]
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19420: Zombie FK exists after partition is detached.
@ 2026-02-27 14:20 David G. Johnston <[email protected]>
parent: PG Bug reporting form <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2026-02-27 14:20 UTC (permalink / raw)
To: [email protected] <[email protected]>; [email protected] <[email protected]>
On Friday, February 27, 2026, PG Bug reporting form <[email protected]>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19420
> Logged by: Agnieszka Chodkowska
> Email address: [email protected]
> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19420: Zombie FK exists after partition is detached.
@ 2026-03-02 10:22 Álvaro Herrera <[email protected]>
parent: PG Bug reporting form <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Álvaro Herrera @ 2026-03-02 10:22 UTC (permalink / raw)
To: [email protected]; [email protected]
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=53af9491a0439720094a11b72602952d7...
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-03-02 10:22 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-27 09:09 BUG #19420: Zombie FK exists after partition is detached. PG Bug reporting form <[email protected]>
2026-02-27 14:20 ` David G. Johnston <[email protected]>
2026-03-02 10:22 ` Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox