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

On Fri, May 15, 2026 at 12:42:18AM +0530, Ayush Tiwari wrote:
> I had originally tried parameterizing the SET values, but as Nathan
> pointed out in the BUG #19476 thread, that is not generally correct
> because the referenced-relation key type is not necessarily the right
> type for the referencing relation's SET target. This patch instead
> uses the simpler approach suggested there: do not cache prepared plans
> for cascade UPDATE actions. The existing cached-plan path remains in
> place for restrict, cascade DELETE, and setnull actions.

This looks generally reasonable. My first thought was that we would leave
most of check_foreign_key() the same, but we'd evict the plans at the end.
I am not sure that's any particular advantage to that approach, and
skipping the extra work for a temporary plan is arguably better.

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.

--
nathan