Re: access to original-statement predicates in an INSTEAD-OF row trigger

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(dot)klaver(at)aklaver(dot)com