public inbox for [email protected]
help / color / mirror / Atom feedRe: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
2+ messages / 2 participants
[nested] [flat]
* Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
@ 2025-05-27 17:00 Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Alvaro Herrera @ 2025-05-27 17:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Paul Foerster <[email protected]>; Adrian Klaver <[email protected]>; pgsql-general list <[email protected]>
Hello,
Belatedly, I came back to this issue in the release notes. Here's a
query for correctly reporting the problem and not reporting the cases
where there isn't a problem:
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_partition_root(conrelid) = confrelid THEN
(SELECT count(*) FROM pg_catalog.pg_partition_tree(confrelid) WHERE level = 1)
ELSE 0 END);
The difference from the query that's currently in the release notes is
that here we count the number of direct partitions of the referenced
table and expect that there be exactly that number of additional
constraint entries in a self-referential FK, compared to the situation
where the FK references a different table. (The query I suggested
previously in this thread had a "+1" instead of adding the number of
partitions, which obviously works correctly only in one particular
case.)
I tested this using Paul's scenario, and a few more, and as far as I can
tell, it is correct.
I'm going to fix the query in the release notes for all past branches
now, to avoid confusing people upgrading in the future ... hopefully not
many, but I don't think it's going to be zero people.
Regards
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Para tener más hay que desear menos"
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
@ 2025-05-27 17:16 Tom Lane <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-05-27 17:16 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Paul Foerster <[email protected]>; Adrian Klaver <[email protected]>; pgsql-general list <[email protected]>
Alvaro Herrera <[email protected]> writes:
> I'm going to fix the query in the release notes for all past branches
> now, to avoid confusing people upgrading in the future ... hopefully not
> many, but I don't think it's going to be zero people.
OK, thanks.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-05-27 17:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-27 17:00 Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION Alvaro Herrera <[email protected]>
2025-05-27 17:16 ` Tom Lane <[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