Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXm4L-00CaaD-HN for pgsql-sql@arkaria.postgresql.org; Wed, 07 Feb 2024 17:50:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXm4J-00CNzL-9u for pgsql-sql@arkaria.postgresql.org; Wed, 07 Feb 2024 17:50:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXm4I-00CNzB-Tw for pgsql-sql@lists.postgresql.org; Wed, 07 Feb 2024 17:50:43 +0000 Received: from mail-lf1-f51.google.com ([209.85.167.51]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXm4F-005z8e-LA for pgsql-sql@lists.postgresql.org; Wed, 07 Feb 2024 17:50:42 +0000 Received: by mail-lf1-f51.google.com with SMTP id 2adb3069b0e04-51168572090so1338029e87.0 for ; Wed, 07 Feb 2024 09:50:39 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707328238; x=1707933038; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=IEphGXpIczJuBnIRr1lKlgK+QVs71VyT7JbXhj+VEvg=; b=YyhQ5AqipwO/CadzNoohSstKLtkv38+kMglc+C2DheiH9THEH8hsSQxxkrCUs0cmJ2 wV2omxjsPirpTnD7QvSHzMVO8Il78cQeyI5SF6qsQC2YDbIGA+2Fl4WfSzdjpjjT+vmU fZACNgdkmH+qb4oWVGNq2QcJ5xLP42UhTz86rxBxSjxet5awGrSt1Naex7apR6QDKZu2 IKtr2AJjF8JtGv+zIovfkvv7MjyIknVQSC6o9pBy8UyrfLQqSxeIQuzJciwy1ADFWg6B aN6H7Jn3IhoANxm5m/inN2RZgc5A1i/XBLpP9ZBslTvyVrDo4fN6jnE7HKll/Ffr+X92 1VrA== X-Gm-Message-State: AOJu0Yy95g3PYCWgKS8AW1L/sxJ/G95cc76lOxBJ8uOThbAt0XUAQY5x 4lCkrkOb1mA+4dZ6GMvzmmlO5fNDvDHX0QktaPOM403VJkFaBK7zHns/DI5UNwU= X-Google-Smtp-Source: AGHT+IFT4R5xr1CTKLoLneTAvVtpY/U4ukyV/rdKWXztH558y1I1cO2Alfi5+xlbUQk24V6rWq9rYw== X-Received: by 2002:ac2:5e9d:0:b0:511:60eb:cb1e with SMTP id b29-20020ac25e9d000000b0051160ebcb1emr3520905lfq.67.1707328237755; Wed, 07 Feb 2024 09:50:37 -0800 (PST) Received: from mail-wr1-f45.google.com (mail-wr1-f45.google.com. [209.85.221.45]) by smtp.gmail.com with ESMTPSA id ss11-20020a170907c00b00b00a3868b8e78dsm996775ejc.52.2024.02.07.09.50.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 07 Feb 2024 09:50:36 -0800 (PST) Received: by mail-wr1-f45.google.com with SMTP id ffacd0b85a97d-33b4b121e28so518273f8f.1 for ; Wed, 07 Feb 2024 09:50:36 -0800 (PST) X-Received: by 2002:a5d:64ca:0:b0:33b:48fb:5f97 with SMTP id f10-20020a5d64ca000000b0033b48fb5f97mr4642568wri.42.1707328236352; Wed, 07 Feb 2024 09:50:36 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Wed, 7 Feb 2024 09:50:07 -0800 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION To: Cars Jeeva Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000000afdf80610ce5558" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000afdf80610ce5558 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 7, 2024 at 7:11=E2=80=AFAM Cars Jeeva wro= te: > 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 i= n > 11, which is not compatible with version 15. Someone helping me to sort o= ut > 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=3D> 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=3D> select * from orders; > > -[ RECORD 1 ]-- > > order_id | 1 > > customer_id | 1 > > -[ RECORD 2 ]-- > > order_id | 2 > > customer_id | 2 > > > airwave=3D> update customers set name =3D'John david' where customer_id= =3D 2; > > ERROR: constraint 336574 is not a foreign key constraint > > airwave=3D> delete from customers where customer_id =3D1; > > ERROR: constraint 336572 is not a foreign key constraint > > airwave=3D> 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(); --0000000000000afdf80610ce5558 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, Feb 7, 2024 at 7:11=E2=80=AFA= M Cars Jeeva <carsjeeva@gmail.com= > wrote:
=
=C2=A0H= i Team,

I am involving the postgres upgrade from version= 11 to 15.
In our legacy application, we have a customized=C2=A0s= cript to upgrade=C2=A0the postgres.

When we were doing the pr= ocess it failed due to foreign=C2=A0key constraint error.

And when I was creating a constraint trigger in V11, there was no e= ntry 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 ta= ble with the contype as 't'.

The below sam= ple operation is working fine in Progress version 11, but it is facing an i= ssue in Version 15. Maybe I used the old syntax mentioned in 11, which is n= ot compatible with version 15. Someone helping me to sort out the issue.

CREATE TABLE customers (

=C2=A0 customer_= id serial PRIMARY KEY,

=C2=A0 name varchar(255)

);

ins= ert into customers values(1,'Jaffar');
insert into customers val= ues(2,'John');
insert into customers values(3,'Javinder'= );



CREATE TABLE orders (

=C2=A0 order_id serial PRIMA= RY KEY,

=C2=A0 customer_id integer NOT NULL,

=C2=A0 FOREIGN K= EY (customer_id) REFERENCES customers(customer_id)

);

insert = into orders values(1,1);
insert into orders values(2,2);

CREATE CONSTRAINT TRIGGER "id_= order"

=C2=A0 =C2=A0 AFTER DELETE ON customers

=C2=A0 = =C2=A0 FROM orders

=C2=A0 =C2=A0 NOT DEFERRABLE INITIALLY IMMEDIATE<= br>
=C2=A0 =C2=A0 FOR EACH ROW

=C2=A0 =C2=A0 EXECUTE PROCEDURE &q= uot;RI_FKey_noaction_del"('id_order', 'orders', 'c= ustomers', 'UNSPECIFIED', 'customer_id', 'customer_= id');


CREATE CONSTRAINT TRIGGER "id_order_2"
=C2=A0 =C2=A0 AFTER UPDATE ON customers

=C2=A0 =C2=A0 FROM orders<= br>
=C2=A0 =C2=A0 NOT DEFERRABLE INITIALLY IMMEDIATE

=C2=A0 =C2= =A0 FOR EACH ROW

=C2=A0 =C2=A0 EXECUTE PROCEDURE "RI_FKey_noact= ion_upd"('id_order_2', 'orders', 'customers', = 'UNSPECIFIED', 'customer_id', 'customer_id');

airwave=3D> select * from customers;

-[ RECORD 1 ]--= -------

customer_id | 1

name =C2=A0 =C2=A0 =C2=A0 =C2=A0| Jaf= far

-[ RECORD 2 ]---------

customer_id | 2

name =C2=A0= =C2=A0 =C2=A0 =C2=A0| John

-[ RECORD 3 ]---------

customer_i= d | 3

name =C2=A0 =C2=A0 =C2=A0 =C2=A0| Javinder


airwave= =3D> select * from orders;

-[ RECORD 1 ]--

order_id =C2=A0= =C2=A0| 1

customer_id | 1

-[ RECORD 2 ]--

order_id = =C2=A0 =C2=A0| 2

customer_id | 2


airwave=3D> update customers set name =3D'Jo= hn david' where customer_id=3D 2;

ERROR: =C2=A0constraint 336574= is not a foreign key constraint

airwave=3D> delete from customer= s where customer_id =3D1;

ERROR: =C2=A0constraint 336572 is not a fo= reign key constraint

airwave=3D> select * from pg_constrai= nt where oid in(336574,336572);



-[ RECORD 1 ]--+-----------<= br>
oid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 336572

conname= =C2=A0 =C2=A0 =C2=A0 =C2=A0| id_order

connamespace =C2=A0 | 2200
contype =C2=A0 =C2=A0 =C2=A0 =C2=A0| t

condeferrable =C2=A0| f<= br>
condeferred =C2=A0 =C2=A0| f

convalidated =C2=A0 | t

c= onrelid =C2=A0 =C2=A0 =C2=A0 | 336553

contypid =C2=A0 =C2=A0 =C2=A0 = | 0

conindid =C2=A0 =C2=A0 =C2=A0 | 0

conparentid =C2=A0 =C2= =A0| 0

confrelid =C2=A0 =C2=A0 =C2=A0| 0

confupdtype =C2=A0 = =C2=A0|

confdeltype =C2=A0 =C2=A0|

confmatchtype =C2=A0|
<= br>conislocal =C2=A0 =C2=A0 | t

coninhcount =C2=A0 =C2=A0| 0

= connoinherit =C2=A0 | t

conkey =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

= confkey =C2=A0 =C2=A0 =C2=A0 =C2=A0|

conpfeqop =C2=A0 =C2=A0 =C2=A0|=

conppeqop =C2=A0 =C2=A0 =C2=A0|

conffeqop =C2=A0 =C2=A0 =C2= =A0|

confdelsetcols |

conexclop =C2=A0 =C2=A0 =C2=A0|

= conbin =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

-[ RECORD 2 ]--+-----------
=
oid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 336574

conname = =C2=A0 =C2=A0 =C2=A0 =C2=A0| id_order_2

connamespace =C2=A0 | 2200
contype =C2=A0 =C2=A0 =C2=A0 =C2=A0| t

condeferrable =C2=A0| f=

condeferred =C2=A0 =C2=A0| f

convalidated =C2=A0 | t

= conrelid =C2=A0 =C2=A0 =C2=A0 | 336553

contypid =C2=A0 =C2=A0 =C2=A0= | 0

conindid =C2=A0 =C2=A0 =C2=A0 | 0

conparentid =C2=A0 =C2= =A0| 0

confrelid =C2=A0 =C2=A0 =C2=A0| 0

confupdtype =C2=A0 = =C2=A0|

confdeltype =C2=A0 =C2=A0|

confmatchtype =C2=A0|
<= br>conislocal =C2=A0 =C2=A0 | t

coninhcount =C2=A0 =C2=A0| 0

= connoinherit =C2=A0 | t

conkey =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

= confkey =C2=A0 =C2=A0 =C2=A0 =C2=A0|

conpfeqop =C2=A0 =C2=A0 =C2=A0|=

conppeqop =C2=A0 =C2=A0 =C2=A0|

conffeqop =C2=A0 =C2=A0 =C2= =A0|

confdelsetcols |

conexclop =C2=A0 =C2=A0 =C2=A0|

= conbin =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

Hi,

I wonder i= f 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 t= riggers? 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 id= ea (and corrections welcome from the community). Also, since you didn't= share the internal functions such as "RI_FKey_noaction_del" it&#= 39;s hard to tell if I'm heading in the right direction. But possibly i= f you bring that code over to this custom trigger, it would work - or if th= ose functions are used more widely, then maybe calling them from the trigge= r (with the appropriate parameters) would work.. I hope this is helpful..

Steve

= CREATE OR REPLACE FUNCTION perform_after_customer_delete()
RETURNS TRIGG= ER AS $$
BEGIN
=C2=A0 =C2=A0 -- Placeholder for custom logic to be ex= ecuted after a customer is deleted.
=C2=A0 =C2=A0 -- This would mimic th= e 'RI_FKey_noaction_del' logic.
=C2=A0 =C2=A0=C2=A0
=C2=A0 = =C2=A0 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER af= ter_customer_delete
AFTER DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION perform_after_customer_dele= te();=C2=A0
--0000000000000afdf80610ce5558--