RE: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏ - Mailing list pgsql-general

On 09/01/12 03:46, Dean Rasheed wrote:
> On 31 August 2012 16:32, John Lumby<johnlumby@hotmail.com>  wrote:
>> _______________________________
>>> From: pavan.deolasee@gmail.com
>>> Date: Fri, 31 Aug 2012 11:09:42 +0530
>>> Subject: Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏
>>>
>>> On Thu, Aug 30, 2012 at 6:31 PM, John Lumby
>>> <johnlumby@hotmail.com<mailto:johnlumby@hotmail.com>>  wrote:
>>>
>>> I would like to use an UPDATE RULE to modify the action performed
>>> when any UPDATE is attempted on a certain table,
>>> *including* an UPDATE which would fail because of no rows matching the WHERE.
>>>
>>> You did not mention why you need such a facility, but AFAICS RULEs will
>>> only be applied on the qualifying rows. So as you rightly figured out,
>>> you won't see them firing unless there are any qualifying rows. Is this
>>> not something you can achieve via statement-level triggers though ?
>> Thanks Pavan;   what I need to do is to intercept certain UPDATE statements
>> which would fail because of no rows matching the WHERE,  and instead
>> issue a different UPDATE which will not fail but will have the same intended effect.
>>
>> The context is a java application which uses hibernate for object-relational mapping,
>> and the specific case is hibernate "optimistic locking".
>>
>> hibernate provides a way of serializing all INS/UPD/DEL operations performed
>> under any single "parent" row in a table that has a heirarchy defined by a
>> kind of self-referencing referential constraint,   that is,
>> each row has a parent_id column pointing to some other row.
>>
>>
>>
>> Also,  when I ran the test of the RULE,  I thought it was significant that psql showed the
>> name of my RULE function as though it was somehow being invoked :
>> update updatable set version = 2 where id = 1 and version = 1
>>   optlock_control
>> -----------------
>> (0 rows)
>>
>> UPDATE 0
> It shows the name of your function because your rule is rewriting the
> UPDATE statement, effectively turning into SELECT optlock_control()
> WHERE<condition that evaluates to false>, so the function name
> becomes the column name of the result, but it isn't actually invoked
> because there are no matching rows. Even if it did work, turning an
> UPDATE into a SELECT like that is likely to confuse Hibernate when it
> tries to check the statement's return status.
>
> What you are trying to do cannot be achieved rules, and doing it this
> way with triggers is likely to be messy. I think you need to consider
> a different approach.
>
> It sounds like what you really want is finer-grained control over the
> Hibernate optimistic locking check. One way of doing that would be to
> do the check yourself in a BEFORE UPDATE ROW trigger, with something
> to the effect of:
>
> if new.version != old.version+1:
>      raise concurrency error (will cause the entire transaction to be
> rolled back)

Thanks Dean.     A nice suggestion but my reading of the rules for a
BEFORE row-level trigger
is that it cannot see the NEW tuple :

    "The data change (insertion, update, or deletion) causing the
trigger to fire
          is naturally not visible to SQL commands executed in a
row-level BEFORE trigger,
          because it hasn't happened yet."

and also under notes to the CREATE TRIGGER statement :
    "In a BEFORE trigger, [....]
          Note in particular that the NEW row seen by the condition
          is the current value, as possibly modified by earlier triggers."

>
> Then you could turn off the Hibernate check and add any finer-grained
> control you needed in your trigger function.


Even if your suggestion could somehow work,   we really prefer to adopt
the approach
of selectively overriding the hibernate optimistic locking only where
needed,
rather than throwing it out completely and doing that function all in
our own code.

So we are really hoping that there is some functionality somewhere in
postgresql
that allows to intercept a failing UPDATE.    With all of the
capabilities of triggers
and rules,  it seemed to me that this must be possible.

John

>
> Regards,
> Dean
>
>