public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Foerster <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: pgsql-general list <[email protected]>
Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Date: Fri, 29 Nov 2024 21:24:17 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Hi Alvaro,

> On 29 Nov 2024, at 18:15, Alvaro Herrera <[email protected]> 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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox