Re: [fixed] Trigger test

Dmitrii Bondar писал(а) 2025-01-29 16:53:
> Hi, Hackers!
> 
> I was testing a connection pooler with `make installcheck` and noticed 
> that `check_foreign_key()` from the `refint` library reuses the same 
> cached plan for cascade `UPDATE`/`DELETE` operations. As a result, a 
> cascade `DELETE` is applied after an `UPDATE` command on the primary 
> key table (which should not happen after the commit 
> https://github.com/postgres/postgres/commit/d489fdfc7f4ccf0010fe0397e7272bdfc257e8f2). 
> I have attached a file (test.sql) to reproduce an issue and the 
> solution.
> 
> Regards,
> Dmitrii Bondar.
Found a mistake. Now it should work even if the SPI call fails (v2 
attachment). However, the whole solution looks awkward because if 
`check_primary_key` is triggered by a function other than 
check_foreign_key, we still encounter invalid behavior. The root of the 
problem is the inability to see the row that triggered the initial 
`check_foreign_key`.

I am also considering another solution (v3 attachment): instead of using 
static variables, restrict the use of the `check_primary_key` and 
`check_foreign` functions in BEFORE triggers so that the 
`check_primary_key` trigger can find the new row. This still doesn't 
solve the problem (a user could create their own BEFORE trigger that 
make `UPDATE` and trigger `check_primary_key`), but it adds less new 
code, at least.

Regards,
Dmitrii Bondar