Re: 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         |