Hi,
On Fri, 15 May 2026 at 02:44, Nathan Bossart <[email protected]> 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
v2-0001-refint-Remove-plan-cache-from-check_foreign_key.patch
Description: Binary data
