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