Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

 





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux