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 1rXja7-00CLy2-Mk for pgsql-sql@arkaria.postgresql.org; Wed, 07 Feb 2024 15:11:24 +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 1rXja6-00BZhv-Jl for pgsql-sql@arkaria.postgresql.org; Wed, 07 Feb 2024 15:11:22 +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 1rXeu5-009f0L-7Z for pgsql-sql@lists.postgresql.org; Wed, 07 Feb 2024 10:11:41 +0000 Received: from mail-ua1-x92a.google.com ([2607:f8b0:4864:20::92a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXeu1-005vLy-AW for pgsql-sql@lists.postgresql.org; Wed, 07 Feb 2024 10:11:40 +0000 Received: by mail-ua1-x92a.google.com with SMTP id a1e0cc1a2514c-7d5bbbe5844so227701241.0 for ; Wed, 07 Feb 2024 02:11:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1707300695; x=1707905495; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=JveujLq5QPHHjo+UwIogdq9zazpElBXbnSSaW13nyuk=; b=TqrlvBnz2kijDWW039oTwmNEpmdoZ/eFXkIkRWCrAOqBiX4hGxV5Od7kD3udJrhSfK JoOZBDu+BlIqD0LmABa5NpWAmqkok/NTsOMsYXAKw74BjuYDrAoouP3XfSXCPHIi/Cby w+zr5uLnVlkQ6NURwq9aW0Z7uGZ7qhQjZN+7uGcJn8lgrHQggUNcbiZ5v1KArBotvucJ LMLBUN5XbnwJ+OwqwBsgSZN6NVod5v4pWspaeIYnU0ndxs4UQCNEh4VBBB2nwtsNypyj ACKv9wjEWmkHWhidUyhmbw+OKQKOXbJH+O1zZWw8iy3LInECGopJw0wVfa9FOuJ4vzta yHaw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707300695; x=1707905495; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=JveujLq5QPHHjo+UwIogdq9zazpElBXbnSSaW13nyuk=; b=QIUdhjDeokdFMLtbfMjlaI5aAVFpNYORnGkgtjjRBeWWS7mEUfc0QIehYoJ3ygwjrk Ns8CjBxjdBSgJFKdZAhmPhvaD80DTUSerfx0g7bNHWqwxpEkPD6tZ/cOuq+JJDT5vjxu 8Prur+xT3GpqAMTCkPP6Dv34/Y4KENrj4IgmizHE2VpZJRxxo7nen+PvJ3lTZtapt1q3 ldCi5/EJvmMrlqZlpvsuM4lcN56Dvq0N8k54wGXdep6xrI0XScmvHTsPzXhHg6CfnYw6 izcmNYXAjtrV0PTJV2p89cR4KHLdKirXNMszUUiw1jnM5Qc+vJ8U05t5OdNkgFzGv8g2 0HpA== X-Gm-Message-State: AOJu0YzzGX1gBkZKh+dcBbRUGo3e7O4Iv1+ikHCgbD4n0ibct6zGRekl eO2K3i2KogK2R5PXQ5AbFDJyD7U0ZMPyzRyzhd/GY2AzSuUiZziXS8/HRAkepFgsLKoeSLsNLa2 EaevcHBC/q8vRy9d4tZXaOMWPGQXk9lR49Nk= X-Google-Smtp-Source: AGHT+IEAU/o+LXeF2ahaWK9HG/KrqnaGBMuwd+co3tYDvgQng+P0rw9k6q97P2REbxH2Hbz8XqZPOFyNe8jlMgGJHjo= X-Received: by 2002:a05:6102:3a12:b0:46d:4b99:1b50 with SMTP id b18-20020a0561023a1200b0046d4b991b50mr2577946vsu.13.1707300694585; Wed, 07 Feb 2024 02:11:34 -0800 (PST) MIME-Version: 1.0 From: Cars Jeeva Date: Wed, 7 Feb 2024 15:41:22 +0530 Message-ID: Subject: POSTGRES 15 - CONSTRAINT TRIGGER CREATION To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006cf75c0610c7eb94" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006cf75c0610c7eb94 Content-Type: text/plain; charset="UTF-8" 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 --0000000000006cf75c0610c7eb94 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
<= div id=3D"gmail-:om" class=3D"gmail-a3s gmail-aiL" style=3D"direction:initi= al;font-variant-numeric:normal;font-variant-east-asian:normal;font-variant-= alternates:normal;font-kerning:auto;font-feature-settings:normal;font-stret= ch:normal;font-size:small;line-height:1.5;font-family:Arial,Helvetica,sans-= serif;overflow:auto hidden">
=C2=A0Hi Team,

<= div>I am involving the postgres upgrade from version 11 to 15.
In= our legacy application, we have a customized=C2=A0script to upgrade=C2=A0t= he postgres.

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

And when I was = creating a constraint trigger in V11, there was no entry for the created tr= igger in the pg_constraint table.
But in v15, I was able to s= ee the created trigger entry in the pg_constraint table with the contype as= 't'.

The below sample operation is workin= g fine in Progress version 11, but it is facing an issue in Version 15. May= be I used the old syntax mentioned in 11, which is not compatible with vers= ion 15. Someone helping me to sort out the issue.

<= div>CREATE TABLE customers (

=C2=A0 customer_id serial PRIMARY KEY,<= br>
=C2=A0 name varchar(255)

);

insert into customers valu= es(1,'Jaffar');
insert into customers values(2,'John');<= br>insert into customers values(3,'Javinder');



CREAT= E TABLE orders (

=C2=A0 order_id serial PRIMARY KEY,

=C2=A0 c= ustomer_id integer NOT NULL,

=C2=A0 FOREIGN KEY (customer_id) REFERE= NCES 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

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

=C2=A0 =C2=A0 EXECUTE PROCEDURE "RI_FKey_noaction_del= "('id_order', 'orders', 'customers', 'UNSP= ECIFIED', 'customer_id', 'customer_id');


CRE= ATE CONSTRAINT TRIGGER "id_order_2"

=C2=A0 =C2=A0 AFTER UP= DATE ON customers

=C2=A0 =C2=A0 FROM orders

=C2=A0 =C2=A0 NOT= DEFERRABLE INITIALLY IMMEDIATE

=C2=A0 =C2=A0 FOR EACH ROW

= =C2=A0 =C2=A0 EXECUTE PROCEDURE "RI_FKey_noaction_upd"('id_or= der_2', 'orders', 'customers', 'UNSPECIFIED', &= #39;customer_id', 'customer_id');


airwave=3D&= gt; select * from customers;

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

customer_= id | 1

name =C2=A0 =C2=A0 =C2=A0 =C2=A0| Jaffar

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

customer_id | 2

name =C2=A0 =C2=A0 =C2=A0 =C2=A0| J= ohn

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

customer_id | 3

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


airwave=3D> select * from ord= ers;

-[ RECORD 1 ]--

order_id =C2=A0 =C2=A0| 1

custome= r_id | 1

-[ RECORD 2 ]--

order_id =C2=A0 =C2=A0| 2

cus= tomer_id | 2


air= wave=3D> update customers set name =3D'John david' where custome= r_id=3D 2;

ERROR: =C2=A0constraint 336574 is not a foreign key const= raint

airwave=3D> delete from customers where customer_id =3D1;
ERROR: =C2=A0constraint 336572 is not a foreign key constraint=

airwave=3D> select * from pg_constraint where oid in(336574,3365= 72);



-[ RECORD 1 ]--+-----------

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

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

confrel= id =C2=A0 =C2=A0 =C2=A0| 0

confupdtype =C2=A0 =C2=A0|

confdel= type =C2=A0 =C2=A0|

confmatchtype =C2=A0|

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|

confdelset= cols |

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

conindi= d =C2=A0 =C2=A0 =C2=A0 | 0

conparentid =C2=A0 =C2=A0| 0

confr= elid =C2=A0 =C2=A0 =C2=A0| 0

confupdtype =C2=A0 =C2=A0|

confd= eltype =C2=A0 =C2=A0|

confmatchtype =C2=A0|

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|

confdel= setcols |

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

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



<= div>


Thank you Team





--0000000000006cf75c0610c7eb94--