Thread

1. POSTGRES 15 - CONSTRAINT TRIGGER CREATION

   Cars Jeeva <carsjeeva@gmail.com> — 2024-02-07T10:11:22Z

    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
   

2. Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION

   Steve Midgley <science@misuse.org> — 2024-02-07T17:50:07Z

   On Wed, Feb 7, 2024 at 7:11 AM Cars Jeeva <carsjeeva@gmail.com> 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();
   

3. Re: POSTGRES 15 - CONSTRAINT TRIGGER CREATION

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-02-07T23:30:17Z

   Cars Jeeva <carsjeeva@gmail.com> writes:
   > The below sample operation is working fine in Progress version 11, but it
   > is facing an issue in Version 15.
   
   When I run this in v11, I get
   
   psql:constrtrig.sql:25: NOTICE:  ignoring incomplete trigger group for constraint "id_order" FOREIGN KEY orders(customer_id) REFERENCES customers(customer_id)
   DETAIL:  Found referenced table's DELETE trigger.
   CREATE TRIGGER
   psql:constrtrig.sql:33: NOTICE:  ignoring incomplete trigger group for constraint "id_order_2" FOREIGN KEY orders(customer_id) REFERENCES customers(customer_id)
   DETAIL:  Found referenced table's UPDATE trigger.
   CREATE TRIGGER
   UPDATE 1
   psql:constrtrig.sql:37: ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
   DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".
   
   So I'm not sure what your expectation for "working fine" is, but
   it doesn't look to me like it's working.
   
   
   > 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');
   
   Why in the world are you doing this, rather than using the normal,
   SQL-standard, far shorter syntax for creating a foreign key constraint?
   This has no advantage over that, and what it does have is a completely
   unsafe level of intimacy with the implementation details of FKs
   --- details that we can and have changed from time to time.
   
   I gather that this might be left over from some pre-Postgres-7.3
   script, but surely it is well past time to move on from that.
   
   For reference, the NOTICEs I show above are coming out of some
   code that v11 had for converting pre-7.3 pg_dump scripts to modern
   FK constraints.  That bore the following comments:
   
    * Convert legacy (pre-7.3) CREATE CONSTRAINT TRIGGER commands into
    * full-fledged foreign key constraints.
    *
    * The conversion is complex because a pre-7.3 foreign key involved three
    * separate triggers, which were reported separately in dumps.  While the
    * single trigger on the referencing table adds no new information, we need
    * to know the trigger functions of both of the triggers on the referenced
    * table to build the constraint declaration.  Also, due to lack of proper
    * dependency checking pre-7.3, it is possible that the source database had
    * an incomplete set of triggers resulting in an only partially enforced
    * FK constraint.  (This would happen if one of the tables had been dropped
    * and re-created, but only if the DB had been affected by a 7.0 pg_dump bug
    * that caused loss of tgconstrrelid information.)  We choose to translate to
    * an FK constraint only when we've seen all three triggers of a set.
   
   We dropped that code somewhere around v13, reasoning that pre-7.3
   servers were extinct in the wild.  But even if it were still there,
   your script would not work because it supplies only 2 of the 3
   triggers.  v11 was doing what it said and ignoring those commands,
   so you never actually got any triggers created there.
   
   			regards, tom lane