POSTGRES 15 - CONSTRAINT TRIGGER CREATION

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