Hi Alvaro, > On 29 Nov 2024, at 18:15, Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> wrote: > > This all was to say that the query in the release notes is undoubtedly > wrong. After thinking some more about it, I think the fix is to add 1 > to the number of constraints: > > 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 1 ELSE 0 END); > > This reports case 2 as OK and case 1 as bogus, as should be. I tried > adding more partitions and this seems to hold correctly. I was afraid > though that this would fail if we create an FK in an intermediate level > of the partition hierarchy ... but experimentation doesn't seem to give > that result. I've run out of time today to continue to look though. Thanks very much for this really detailed analysis and sharing your insights. I'll give the new query a try on Monday when I'm back at work. Do I also need to recheck all other databases with this new query which didn't report anything with the original query? > Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ > "La vida es para el que se aventura" You're located in the middle of the forest east of Freiburg im Breisgau in Germany? 🤣 Cheers, Paul