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

Hi,

On Fri, 15 May 2026 at 02:44, Nathan Bossart <nathandbossart(at)gmail(dot)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