Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION - Mailing list pgsql-sql

Cars Jeeva <carsjeeva@gmail.com> 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