public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Midgley <[email protected]>
To: Cars Jeeva <[email protected]>
Cc: [email protected]
Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
Date: Wed, 7 Feb 2024 09:50:07 -0800
Message-ID: <CAJexoSJw7o5y3KU2XEUefaceUH91UNzdVHmx95Ox+Db+vy9uHw@mail.gmail.com> (raw)
In-Reply-To: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
References: <CA+C4Fcs=PTxgYxgDpyP2_QgoYePcqN4OMNeL=VPZOQVZOOYkOA@mail.gmail.com>
On Wed, Feb 7, 2024 at 7:11 AM Cars Jeeva <[email protected]> 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
CREATE OR REPLACE FUNCTION perform_after_customer_delete()
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();
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], [email protected]
Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION
In-Reply-To: <CAJexoSJw7o5y3KU2XEUefaceUH91UNzdVHmx95Ox+Db+vy9uHw@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