Thread: access to original-statement predicates in an INSTEAD-OF row trigger
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 this
statementis 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
thatavoids 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.
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
Adrian Klaver wrote :
>
> On 11/15/19 10:37 AM, John Lumby wrote:
>
> > Suppose the original statement is
>
> > UPDATE myview VW set VW.counter = 11 where VW.primary_key = xxxx and VW.counter = 10;
>
> > and my trigger constructs this statement
>
> > UPDATE basetable BT set BT.counter = 11 where BT.primary_key = OLD.primary_key;
>
> Not following.
>
> Do you want OLD.primary_key set to 11 or BT.counter set to 12/both/or
>
> some other action?
Sorry I did not make it clear.
I want some way for the trigger to discover and apply any predicates *other* than
primary key equality condition that were applied to the original statement,
which in the example is
VW.counter = 10
(the repeated AND in the original append's example was a typo, corrected above)
so for this example I want the trigger to build a statement reading
UPDATE basetable BT set BT.counter = 11 where BT.primary_key = xxxx and BT.counter = 10;
where xxxx is the value of OLD.primary_key
so that, if some other transaction had updated BT.counter to some other value such as 11
in that tiny window I described in previous append,
the result of the generated statement would be no rows updated and a return TAG of 0 rows.
The significance being that the original application would be able to discover
that its update was not applied based on this return TAG
(actually the trigger returns a null tuple to indicate this).
>
> > Cheers, John
>
> Adrian Klaver
>
> adrian.klaver@aklaver.com
>
On 11/15/19 12:57 PM, John Lumby wrote: > Adrian Klaver wrote : >> >> On 11/15/19 10:37 AM, John Lumby wrote: >> >>> Suppose the original statement is >> >>> UPDATE myview VW set VW.counter = 11 where VW.primary_key = xxxx and VW.counter = 10; >> >>> and my trigger constructs this statement >> >>> UPDATE basetable BT set BT.counter = 11 where BT.primary_key = OLD.primary_key; >> >> Not following. >> >> Do you want OLD.primary_key set to 11 or BT.counter set to 12/both/or >> >> some other action? > > Sorry I did not make it clear. > > I want some way for the trigger to discover and apply any predicates *other* than > primary key equality condition that were applied to the original statement, > which in the example is > > VW.counter = 10 > > (the repeated AND in the original append's example was a typo, corrected above) > > so for this example I want the trigger to build a statement reading > > UPDATE basetable BT set BT.counter = 11 where BT.primary_key = xxxx and BT.counter = 10; > > where xxxx is the value of OLD.primary_key > > so that, if some other transaction had updated BT.counter to some other value such as 11 > in that tiny window I described in previous append, > the result of the generated statement would be no rows updated and a return TAG of 0 rows. Seems you are looking for Serializable Isolation Level: https://www.postgresql.org/docs/11/transaction-iso.html#XACT-SERIALIZABLE Though the above results in a rollback. > > The significance being that the original application would be able to discover > that its update was not applied based on this return TAG > (actually the trigger returns a null tuple to indicate this). > >> >>> Cheers, John >> >> Adrian Klaver >> >> adrian.klaver@aklaver.com >> > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver wrote :
>
> Seems you are looking for Serializable Isolation Level:
>
True , that would solve the race condition, but it is too drastic.
We need to run with Read Committed.
I am looking for a solution which does not alter the application or overall behaviour,
but just addresses detecting which predicates to apply in some way.
Cheers, John
> Seems you are looking for Serializable Isolation Level:
>
True , that would solve the race condition, but it is too drastic.
We need to run with Read Committed.
I am looking for a solution which does not alter the application or overall behaviour,
but just addresses detecting which predicates to apply in some way.
Cheers, John
On 11/15/19 1:54 PM, John Lumby wrote: > Adrian Klaver wrote : >> >> Seems you are looking for Serializable Isolation Level: >> > > True , that would solve the race condition, but it is too drastic. > We need to run with Read Committed. > > I am looking for a solution which does not alter the application or > overall behaviour, > but just addresses detecting which predicates to apply in some way. Not sure how that could be pulled off with Read Committed as it would involve predicting the future from the POV of the transaction. > > Cheers, John > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/15/19 17:38, Adrian Klaver wrote: > On 11/15/19 1:54 PM, John Lumby wrote: >> Adrian Klaver wrote : >>> >> We need to run with Read Committed. >> >> I am looking for a solution which does not alter the application or >> overall behaviour, >> but just addresses detecting which predicates to apply in some way. > > Not sure how that could be pulled off with Read Committed as it would > involve predicting the future from the POV of the transaction. > No need for time-travel! Let me re-phrase the question in a simpler fashion : How can a row trigger access the original SQL statement at the root of the current operation? Either in string form or any other form (e.g. parse tree). >> >> Cheers, John >> > >
John Lumby <johnlumby@hotmail.com> writes:
> How can a row trigger access the original SQL statement at the root of
> the current operation?
It can't; at least not in any way that'd be reliable or maintainable.
I concur with the upthread recommendation that switching to serializable
mode would be a more manageable way of dealing with concurrent-update
problems.
regards, tom lane