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
