Thread: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
Hi Team,
When we were doing the process it failed due to foreign key constraint error.
I am involving the postgres upgrade from version 11 to 15.
In our legacy application, we have a customized script to upgrade the postgres.
When we were doing the process it failed due to foreign key constraint error.
And when I was creating a constraint trigger in V11, there was no entry for the created trigger in the pg_constraint table.
But in v15, I was able to see the created trigger entry in the pg_constraint table with the contype as 't'.
The below sample operation is working fine in Progress version 11, but it is facing an issue in Version 15. Maybe I used the old syntax mentioned in 11, which is not compatible with version 15. Someone helping me to sort out the issue.
CREATE TABLE customers (
customer_id serial PRIMARY KEY,
name varchar(255)
);
insert into customers values(1,'Jaffar');
insert into customers values(2,'John');
insert into customers values(3,'Javinder');
CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
insert into orders values(1,1);
insert into orders values(2,2);
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');
CREATE CONSTRAINT TRIGGER "id_order_2"
AFTER UPDATE ON customers
FROM orders
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('id_order_2', 'orders', 'customers', 'UNSPECIFIED', 'customer_id', 'customer_id');
airwave=> select * from customers;
-[ RECORD 1 ]---------
customer_id | 1
name | Jaffar
-[ RECORD 2 ]---------
customer_id | 2
name | John
-[ RECORD 3 ]---------
customer_id | 3
name | Javinder
airwave=> select * from orders;
-[ RECORD 1 ]--
order_id | 1
customer_id | 1
-[ RECORD 2 ]--
order_id | 2
customer_id | 2
airwave=> update customers set name ='John david' where customer_id= 2;
ERROR: constraint 336574 is not a foreign key constraint
airwave=> delete from customers where customer_id =1;
ERROR: constraint 336572 is not a foreign key constraint
airwave=> select * from pg_constraint where oid in(336574,336572);
-[ RECORD 1 ]--+-----------
oid | 336572
conname | id_order
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
-[ RECORD 2 ]--+-----------
oid | 336574
conname | id_order_2
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
customer_id serial PRIMARY KEY,
name varchar(255)
);
insert into customers values(1,'Jaffar');
insert into customers values(2,'John');
insert into customers values(3,'Javinder');
CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
insert into orders values(1,1);
insert into orders values(2,2);
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');
CREATE CONSTRAINT TRIGGER "id_order_2"
AFTER UPDATE ON customers
FROM orders
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('id_order_2', 'orders', 'customers', 'UNSPECIFIED', 'customer_id', 'customer_id');
airwave=> select * from customers;
-[ RECORD 1 ]---------
customer_id | 1
name | Jaffar
-[ RECORD 2 ]---------
customer_id | 2
name | John
-[ RECORD 3 ]---------
customer_id | 3
name | Javinder
airwave=> select * from orders;
-[ RECORD 1 ]--
order_id | 1
customer_id | 1
-[ RECORD 2 ]--
order_id | 2
customer_id | 2
airwave=> update customers set name ='John david' where customer_id= 2;
ERROR: constraint 336574 is not a foreign key constraint
airwave=> delete from customers where customer_id =1;
ERROR: constraint 336572 is not a foreign key constraint
airwave=> select * from pg_constraint where oid in(336574,336572);
-[ RECORD 1 ]--+-----------
oid | 336572
conname | id_order
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
-[ RECORD 2 ]--+-----------
oid | 336574
conname | id_order_2
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
Thank you Team
On Wed, Feb 7, 2024 at 7:11 AM Cars Jeeva <carsjeeva@gmail.com> wrote:
Hi Team,I am involving the postgres upgrade from version 11 to 15.In our legacy application, we have a customized script to upgrade the postgres.
When we were doing the process it failed due to foreign key constraint error.And when I was creating a constraint trigger in V11, there was no entry for the created trigger in the pg_constraint table.But in v15, I was able to see the created trigger entry in the pg_constraint table with the contype as 't'.The below sample operation is working fine in Progress version 11, but it is facing an issue in Version 15. Maybe I used the old syntax mentioned in 11, which is not compatible with version 15. Someone helping me to sort out the issue.CREATE TABLE customers (
customer_id serial PRIMARY KEY,
name varchar(255)
);
insert into customers values(1,'Jaffar');
insert into customers values(2,'John');
insert into customers values(3,'Javinder');
CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
insert into orders values(1,1);
insert into orders values(2,2);
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');
CREATE CONSTRAINT TRIGGER "id_order_2"
AFTER UPDATE ON customers
FROM orders
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('id_order_2', 'orders', 'customers', 'UNSPECIFIED', 'customer_id', 'customer_id');
airwave=> select * from customers;
-[ RECORD 1 ]---------
customer_id | 1
name | Jaffar
-[ RECORD 2 ]---------
customer_id | 2
name | John
-[ RECORD 3 ]---------
customer_id | 3
name | Javinder
airwave=> select * from orders;
-[ RECORD 1 ]--
order_id | 1
customer_id | 1
-[ RECORD 2 ]--
order_id | 2
customer_id | 2
airwave=> update customers set name ='John david' where customer_id= 2;
ERROR: constraint 336574 is not a foreign key constraint
airwave=> delete from customers where customer_id =1;
ERROR: constraint 336572 is not a foreign key constraint
airwave=> select * from pg_constraint where oid in(336574,336572);
-[ RECORD 1 ]--+-----------
oid | 336572
conname | id_order
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
-[ RECORD 2 ]--+-----------
oid | 336574
conname | id_order_2
connamespace | 2200
contype | t
condeferrable | f
condeferred | f
convalidated | t
conrelid | 336553
contypid | 0
conindid | 0
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey |
confkey |
conpfeqop |
conppeqop |
conffeqop |
confdelsetcols |
conexclop |
conbin |
Hi,
I wonder if the problem is that you have been using constraints and it would be more accurate to consider the functions you're writing as more traditional triggers? That might resolve the problem you are having.. Below is my quick attempt to rewrite the shell of what I can see in your first constraint as a trigger. I am NOT a Pg trigger writing expert! So please validate this idea (and corrections welcome from the community). Also, since you didn't share the internal functions such as "RI_FKey_noaction_del" it's hard to tell if I'm heading in the right direction. But possibly if you bring that code over to this custom trigger, it would work - or if those functions are used more widely, then maybe calling them from the trigger (with the appropriate parameters) would work.. I hope this is helpful..
Steve
RETURNS TRIGGER AS $$
BEGIN
-- Placeholder for custom logic to be executed after a customer is deleted.
-- This would mimic the 'RI_FKey_noaction_del' logic.
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_customer_delete
AFTER DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION perform_after_customer_delete();
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