Thread

Re: [PATCH] refint: Avoid reusing cascade UPDATE plans.

Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-05-15T06:43:21Z

Hi,


On Fri, 15 May 2026 at 02:44, Nathan Bossart <nathandbossart@gmail.com>
wrote:

>
> That being said, the plan cache still has problems.  There's no
> invalidation mechanism, so you could still end up with the wrong plan.
> Here's a quick example:
>
>     CREATE EXTENSION refint;
>
>     CREATE TABLE p AS SELECT 1 AS a;
>     CREATE TABLE f1 AS SELECT 1 AS a;
>     CREATE TABLE f2 AS SELECT 1 AS a;
>     CREATE TRIGGER t
>         AFTER DELETE OR UPDATE ON p
>         FOR EACH ROW EXECUTE PROCEDURE
>         check_foreign_key(2, 'c', 'a', 'f1', 'a', 'f2', 'a');
>     UPDATE p SET a = 2;
>
>     DROP TRIGGER t ON p;
>     CREATE TRIGGER t
>         AFTER DELETE OR UPDATE ON p
>         FOR EACH ROW EXECUTE PROCEDURE
>         check_foreign_key(1, 'c', 'a', 'f1', 'a');
>     UPDATE p SET a = 3;
>
> The last UPDATE fails with:
>
>     ERROR:  t: check_foreign_key: # of plans changed in meantime
>
> A simple way to fix this could be to use the trigger OID instead of the
> trigger name in the plan cache key.  That's not perfect because the OID
> could be reused, but IMHO it's better than what's there today.  An even
> better approach would involve more sophisticated invalidation or removing
> the cache altogether.
>
>
Thanks for the review and the drop/recreate example.

After looking at it again, the root issue is that check_foreign_key()'s
private plan cache is not robust enough for the SQL it stores: the
cascade UPDATE query text embeds the current NEW key values in its SET
clause, and the cache itself has no invalidation mechanism, so the
trigger-name-keyed entries survive a drop and recreate.  Rather than
keep teaching the cache more special cases, v2 just removes the cache
from check_foreign_key() entirely.  Plans are prepared on each trigger
invocation and released by SPI_finish().  refint is example code, so I
think the simplicity is worth more than the per-call SPI_prepare.

check_primary_key() still uses the existing per-trigger cache; its
generated SELECT does not embed row values and the same invalidation
concern is far less interesting there, so I left it alone for now.

v2 keeps the cascade-UPDATE regression test from v1 and adds a second
test that drops and recreates a cascade trigger with a different number
of references, which reproduces your example.

Regards,
Ayush