public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Cars Jeeva <[email protected]>
Cc: [email protected]
Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
Date: Wed, 07 Feb 2024 18:30:17 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
References: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>

Cars Jeeva <[email protected]> writes:
> The below sample operation is working fine in Progress version 11, but it
> 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 constraint "id_order" FOREIGN KEY orders(customer_id) REFERENCES customers(customer_id)
DETAIL:  Found referenced table's DELETE trigger.
CREATE TRIGGER
psql:constrtrig.sql:33: NOTICE:  ignoring incomplete trigger group for constraint "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" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(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 need
 * 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 had
 * an incomplete set of triggers resulting in an only partially enforced
 * FK constraint.  (This would happen if one of the tables had been dropped
 * and re-created, but only if the DB had been affected by a 7.0 pg_dump bug
 * 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

view thread (3+ messages)

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]
  Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
  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