From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
        Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [BUG] wrong FK constraint name when colliding name on ATTACH
In-reply-to: <20220908172029.sejft2ppckbo6oh5@awork3.anarazel.de>
References: <20220908094026.640a0224@karst>
 <20220908112515.5pe5obtk4a63aiee@alvherre.pgsql>
 <20220908172029.sejft2ppckbo6oh5@awork3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Thu, 08 Sep 2022 10:20:29 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4118053.1662666873.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 08 Sep 2022 15:54:33 -0400
Message-ID: <4118054.1662666873@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4118054.1662666873%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> Something here doesn't look to be quite right. Starting with this commit=
 CI
> [1] started to fail on freebsd (stack trace [2]), and in the meson branc=
h I've
> also seen the crash on windows (CI run[3], stack trace [4]).

The crash seems 100% reproducible if I remove the early-exit optimization
from GetForeignKeyActionTriggers:

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/table=
cmds.c
index 53b0f3a9c1..112ca77d97 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10591,8 +10591,6 @@ GetForeignKeyActionTriggers(Relation trigrel,
            Assert(*updateTriggerOid =3D=3D InvalidOid);
            *updateTriggerOid =3D trgform->oid;
        }
-       if (OidIsValid(*deleteTriggerOid) && OidIsValid(*updateTriggerOid)=
)
-           break;
    }
 =

    if (!OidIsValid(*deleteTriggerOid))

With that in place, it's probabilistic whether the Asserts notice anything
wrong, and mostly they don't.  But there are multiple matching triggers:

regression=3D# select oid, tgconstraint, tgrelid,tgconstrrelid, tgtype, tg=
name from pg_trigger where tgconstraint =3D 104301;
  oid   | tgconstraint | tgrelid | tgconstrrelid | tgtype |            tgn=
ame             =

--------+--------------+---------+---------------+--------+---------------=
----------------
 104302 |       104301 |  104294 |        104294 |      9 | RI_ConstraintT=
rigger_a_104302
 104303 |       104301 |  104294 |        104294 |     17 | RI_ConstraintT=
rigger_a_104303
 104304 |       104301 |  104294 |        104294 |      5 | RI_ConstraintT=
rigger_c_104304
 104305 |       104301 |  104294 |        104294 |     17 | RI_ConstraintT=
rigger_c_104305
(4 rows)

I suspect that the filter conditions being applied are inadequate
for the case of a self-referential FK, which this evidently is
given that tgrelid and tgconstrrelid are equal.

I'd counsel dropping the early-exit optimization; it doesn't
save much I expect, and it evidently hides bugs.  Or maybe
make it conditional on !USE_ASSERT_CHECKING.

			regards, tom lane