public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Tom Lane <[email protected]>
Cc: Paul Foerster <[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: Tue, 27 May 2025 19:00:37 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

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"

view thread (2+ messages)  latest in thread

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