Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

Hi Alvaro,

> On 29 Nov 2024, at 18:15, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 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