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"
