Hi, In refint's check_foreign_key(), a second cascade UPDATE through the same trigger silently propagates the *first* UPDATE's new key value to the referencing row. This is a pre-existing issue we noticed while looking at BUG #19476 [1].
check_foreign_key() caches prepared plans across trigger invocations, but the generated cascade UPDATE query embeds the current NEW key values directly in the SET clause. As a result, the first set of new key values gets reused for later cascade UPDATEs from the same trigger. The attached regression test demonstrates this: updating two referenced keys in sequence should cascade to two different referencing values. Without the fix, the cached plan for the first UPDATE is reused for the second one, so the second referencing row is updated to the first UPDATE's new key value instead. 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. Thoughts? Regards, Ayush [1] PostgreSQL: BUG #19476: Segmentation fault in contrib/spi <https://www.postgresql.org/message-id/flat/19476-bd04ea6241345303%40postgresql.org>
v1-0001-Avoid-reusing-refint-cascade-UPDATE-plans.patch
Description: Binary data
