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.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).

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

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).

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