From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Foerster <paul.foerster@gmail.com>
cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
        Adrian Klaver <adrian.klaver@aklaver.com>,
        pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
In-reply-to: <F103529F-49ED-4A2F-9EB1-19AFC561635A@gmail.com>
References: <5456A1D0-CD47-4315-9C65-71B27E7A2906@gmail.com> <78ec2af8-48f2-42c0-b317-cbb77cc5adc8@aklaver.com> <DCCB9653-F185-454B-8B15-DE3997DA8AF8@gmail.com> <1723211.1732656332@sss.pgh.pa.us> <F103529F-49ED-4A2F-9EB1-19AFC561635A@gmail.com>
Comments: In-reply-to Paul Foerster <paul.foerster@gmail.com>
	message dated "Wed, 27 Nov 2024 08:22:49 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1900195.1732733556.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 27 Nov 2024 13:52:36 -0500
Message-ID: <1900196.1732733556@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1900196.1732733556%40sss.pgh.pa.us>
Precedence: bulk

Paul Foerster <paul.foerster@gmail.com> writes:
> On 26 Nov 2024, at 22:25, Tom Lane <tgl@sss.pgh.pa.us> 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. A=
ccording 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=3D# create table p_ci_pipelines(partition_id int, id int, prima=
ry key(partition_id,id), auto_canceled_by_partition_id int, auto_canceled_=
by_id int) partition by LIST (partition_id);
CREATE TABLE
regression=3D# create table ci_pipelines partition of p_ci_pipelines FOR V=
ALUES IN ('100', '101', '102');
CREATE TABLE
regression=3D# ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p F=
OREIGN 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=3D# 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 =3D 'f' AND conparentid =3D 0 AND
   (SELECT count(*) FROM pg_catalog.pg_constraint c2
    WHERE c2.conparentid =3D c.oid) <>
   (SELECT count(*) FROM pg_catalog.pg_inherits i
    WHERE (i.inhparent =3D c.conrelid OR i.inhparent =3D c.confrelid) AND
      EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
              WHERE partrelid =3D i.inhparent));
 constrained table |   constraint    |   references   |                   =
         drop                             |                               =
                                                                      add
-------------------+-----------------+----------------+-------------------=
------------------------------------------+-------------------------------=
--------------------------------------------------------------------------=
--------------------------------------------------------------------------=
---------------------------
 p_ci_pipelines    | fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE p_ci_p=
ipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines ADD=
 CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, au=
to_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE C=
ASCADE 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