public inbox for [email protected]
help / color / mirror / Atom feedFrom: Cars Jeeva <[email protected]>
To: [email protected]
Subject: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
Date: Wed, 7 Feb 2024 15:41:22 +0530
Message-ID: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com> (raw)
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 |
Thank you Team
view thread (3+ messages) latest in thread
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]
Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
In-Reply-To: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
* 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