public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Paul Foerster <[email protected]>
Cc: Alvaro Herrera <[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: Wed, 27 Nov 2024 13:52:36 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Paul Foerster <[email protected]> writes:
> On 26 Nov 2024, at 22:25, Tom Lane <[email protected]> wrote:
>> I'm suspicious that our repair recipe might not have accounted
>> for self-reference FKs fully, but that's just a gut feeling at
>> this point.

> Of course, it contains no secret data. Please find the full log below. According to the add constraint statement, it is a self reference.
> Thanks for looking into it.

Okay, so I was able to reproduce this from scratch on HEAD:

regression=# create table p_ci_pipelines(partition_id int, id int, primary key(partition_id,id), auto_canceled_by_partition_id int, auto_canceled_by_id int) partition by LIST (partition_id);
CREATE TABLE
regression=# create table ci_pipelines partition of p_ci_pipelines FOR VALUES IN ('100', '101', '102');
CREATE TABLE
regression=# ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE
regression=# 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));
 constrained table |   constraint    |   references   |                            drop                             |                                                                                                     add
-------------------+-----------------+----------------+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 p_ci_pipelines    | fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
(1 row)

I doubt that there's anything actually wrong with the catalog state at
this point (perhaps Alvaro would confirm that).  That leads to the
conclusion that what's wrong is the release notes' query for fingering
broken constraints, and it needs some additional test to avoid
complaining about (I suspect) self-reference cases.

			regards, tom lane

view thread (5+ 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