Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
От | Tom Lane |
---|---|
Тема | Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION |
Дата | |
Msg-id | 851271.1707348617@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | POSTGRES 15 - CONSTRAINT TRIGGER CREATION (Cars Jeeva <carsjeeva@gmail.com>) |
Список | 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
В списке pgsql-sql по дате отправления: