Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION

Cars Jeeva <carsjeeva(at)gmail(dot)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