From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cars Jeeva <carsjeeva@gmail.com>
cc: pgsql-sql@lists.postgresql.org
Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
In-reply-to: 
 <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
References: 
 <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
Comments: In-reply-to Cars Jeeva <carsjeeva@gmail.com>
	message dated "Wed, 07 Feb 2024 15:41:22 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <851270.1707348617.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 07 Feb 2024 18:30:17 -0500
Message-ID: <851271.1707348617@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/851271.1707348617%40sss.pgh.pa.us>
Precedence: bulk

Cars Jeeva <carsjeeva@gmail.com> writes:
> The below sample operation is working fine in Progress version 11, but i=
t
> is facing an issue in Version 15.

When I run this in v11, I get

psql:constrtrig.sql:25: NOTICE:  ignoring incomplete trigger group for con=
straint "id_order" FOREIGN KEY orders(customer_id) REFERENCES customers(cu=
stomer_id)
DETAIL:  Found referenced table's DELETE trigger.
CREATE TRIGGER
psql:constrtrig.sql:33: NOTICE:  ignoring incomplete trigger group for con=
straint "id_order_2" FOREIGN KEY orders(customer_id) REFERENCES customers(=
customer_id)
DETAIL:  Found referenced table's UPDATE trigger.
CREATE TRIGGER
UPDATE 1
psql:constrtrig.sql:37: ERROR:  update or delete on table "customers" viol=
ates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=3D(1) is still referenced from table "orders".

So I'm not sure what your expectation for "working fine" is, but
it doesn't look to me like it's working.


> CREATE CONSTRAINT TRIGGER "id_order"
>     AFTER DELETE ON customers
>     FROM orders
>     NOT DEFERRABLE INITIALLY IMMEDIATE
>     FOR EACH ROW
>     EXECUTE PROCEDURE "RI_FKey_noaction_del"('id_order', 'orders',
> 'customers', 'UNSPECIFIED', 'customer_id', 'customer_id');

Why in the world are you doing this, rather than using the normal,
SQL-standard, far shorter syntax for creating a foreign key constraint?
This has no advantage over that, and what it does have is a completely
unsafe level of intimacy with the implementation details of FKs
--- details that we can and have changed from time to time.

I gather that this might be left over from some pre-Postgres-7.3
script, but surely it is well past time to move on from that.

For reference, the NOTICEs I show above are coming out of some
code that v11 had for converting pre-7.3 pg_dump scripts to modern
FK constraints.  That bore the following comments:

 * Convert legacy (pre-7.3) CREATE CONSTRAINT TRIGGER commands into
 * full-fledged foreign key constraints.
 *
 * The conversion is complex because a pre-7.3 foreign key involved three
 * separate triggers, which were reported separately in dumps.  While the
 * single trigger on the referencing table adds no new information, we nee=
d
 * to know the trigger functions of both of the triggers on the referenced
 * table to build the constraint declaration.  Also, due to lack of proper
 * dependency checking pre-7.3, it is possible that the source database ha=
d
 * an incomplete set of triggers resulting in an only partially enforced
 * FK constraint.  (This would happen if one of the tables had been droppe=
d
 * and re-created, but only if the DB had been affected by a 7.0 pg_dump b=
ug
 * that caused loss of tgconstrrelid information.)  We choose to translate=
 to
 * an FK constraint only when we've seen all three triggers of a set.

We dropped that code somewhere around v13, reasoning that pre-7.3
servers were extinct in the wild.  But even if it were still there,
your script would not work because it supplies only 2 of the 3
triggers.  v11 was doing what it said and ignoring those commands,
so you never actually got any triggers created there.

			regards, tom lane