From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | John Lumby <johnlumby(at)hotmail(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: access to original-statement predicates in an INSTEAD-OF row trigger |
Date: | 2019-11-15 21:22:52 |
Message-ID: | 8abf71b6-26b0-3d7e-f02c-9c33f323d792@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
>>
>
>
>
>
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | John Lumby | 2019-11-15 21:54:12 | Re: access to original-statement predicates in an INSTEAD-OF row trigger |
Previous Message | John Lumby | 2019-11-15 20:57:14 | Re: access to original-statement predicates in an INSTEAD-OF row trigger |