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

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
problemconcerning the returned TAG.
 
> Suppose that,  *after* the backend executor started executing the statement but *before* the trigger is fired and
thisstatement 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
is10.
 
> 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
thisthat 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
questionthat that is not possible.      I am looking for some self-contained way in trigger or similar code.
 
> 
>   
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com