From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Tender Wang <tndrwang@gmail.com>, Alexander Lakhin <exclusion@gmail.com>,
        Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
        Guillaume Lelarge <guillaume@lelarge.info>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
In-reply-to: <202411061753.ufb6tepg5cr4@alvherre.pgsql>
References: <202411061753.ufb6tepg5cr4@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Wed, 06 Nov 2024 18:53:07 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3645002.1731080579.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 08 Nov 2024 10:42:59 -0500
Message-ID: <3645003.1731080579@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3645003.1731080579%40sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> Perhaps one more task for me is to figure out a way to get a list of al=
l
>> the constraints that are broken because of this ... let me see if I can
>> figure that out.

> It's gotta be something like this,

> SELECT conrelid::regclass AS "constrained table",
>        conname as constraint, confrelid::regclass AS "references"
> FROM pg_constraint
> WHERE contype =3D 'f' and conparentid =3D 0 AND
>    (SELECT count(*) FROM pg_constraint p2 WHERE conparentid =3D pg_const=
raint.oid) <>
>    (SELECT count(*)
>       FROM pg_inherits
>      WHERE inhparent =3D pg_constraint.conrelid OR inhparent =3D pg_cons=
traint.confrelid);

Hmm ... interestingly, if I run this in HEAD's regression database,
I get

 constrained table |  constraint   | references  =

-------------------+---------------+-------------
 clstr_tst         | clstr_tst_con | clstr_tst_s
(1 row)

Digging a bit deeper, the sub-select for conparentid finds no rows,
but the sub-select on pg_inherits finds

regression=3D# SELECT inhrelid::regclass, inhparent::regclass, inhseqno,in=
hdetachpending from pg_inherits WHERE inhparent =3D 'clstr_tst'::regclass =
or inhparent =3D 'clstr_tst_s'::regclass;
   inhrelid    | inhparent | inhseqno | inhdetachpending =

---------------+-----------+----------+------------------
 clstr_tst_inh | clstr_tst |        1 | f
(1 row)

So it looks like this query needs a guard to make it ignore
constraints on traditional-inheritance tables.

			regards, tom lane