Subject: POSTGRES 15 - CONSTRAINT TRIGGER CREATION

From: Cars Jeeva <[email-masked]>
Date: Wed, 7 Feb 2024 15:41:22 +0530

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

---

From: Steve Midgley <[email-masked]>
Date: Wed, 7 Feb 2024 09:50:07 -0800

On Wed, Feb 7, 2024 at 7:11 AM Cars Jeeva <[email-masked]> wrote:

> And when I was creating a constraint trigger in V11, there was no entry
> for the created trigger in the pg_constraint table.

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();

---

From: Tom Lane <[email-masked]>
Date: Wed, 07 Feb 2024 18:30:17 -0500

Cars Jeeva <[email-masked]> writes:
> 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.

> '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