I have an INSTEAD-OF row trigger one of whose purposes is to re-direct the
original statement
(which is operating on a VIEW) to a different real base table.
Suppose the original statement is
UPDATE myview VW set VW.counter = 11 where VW.primary_key = xxxx and
AND VW.counter = 10;
and my trigger constructs this statement
UPDATE basetable BT set BT.counter = 11 where BT.primary_key =
OLD.primary_key;
based on what it finds in OLD tuple and NEW tuple.
This will never update the wrong row since it specifies the primary key -
good. But I have realized there is a problem concerning the returned TAG.
Suppose that, *after* the backend executor started executing the statement but
*before* the trigger is fired and this statement is issued, a different
transaction updated BT.counter to 11 (or higher).
My trigger still runs the update, but the original statement specified to do so
only if the current value of counter is 10.
Or rather, it specified that no row should be found for update if counter <>
10.
Is there any way my trigger can discover this predicate condition and apply it
to its generated statement?
Or if not, (because I suppose in general such predicates could be very
complex) is there some other way of doing this that avoids this problem and
that does not require modification of the application? (**)
I have a feeling this must have come up before but Idon't see any reference.
postgresqI version 12.
Cheers, John
(**) I know a SHARE lock could be obtained by the application running the
orginal statement but assume for this question that that is not possible.
I am looking for some self-contained way in trigger or similar code.
