On 11/15/19 10:37 AM, John Lumby wrote:
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?
Not following.
Do you want OLD.primary_key set to 11 or BT.counter set to 12/both/or
some other action?
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
