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

Hi Alvaro,

> On 29 Nov 2024, at 18:15, Alvaro Herrera <alvherre@alvh.no-ip.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