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

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.
>
> It is possible to tell hibernate to serialize INS/UPD/DELon any particular table.
> hibernate then uses another column named "version" to do the serialization -
> using a sequence like so (for example of an INS):
>
>  1  .    SELECT parent entity of entity to be INSerted,
>                  by specifying WHERE id = <parent_id>
>                                    and note its version   -  let's say version = V
>
>  2  .    INSERT the new entity with version set to 0
>
>  3  .    UPDATE the parent entity  :   set version = (V+1)
>                                  WHERE id= <parent_id>  AND version = V
>                 throw exception and ROLLBACK the INSERT if this UPDATE failed
>                 (it will fail if another thread had performed another
>                 intervening INSERT and updated parent's version)
>
> Now,   our problem is that control of this optimistic locking behaviour is per table,
> whereas we ideally want it to operate at the level of object type within table.
> That is,  in certain well-defined cases,   we do not want this serialization to be done.
> My idea was to intercept the UPDATE in these cases and change the UPDATE into
>    UPDATE the parent entity  :   set version = (OLD.version+1)
>
>                                  WHERE id= <parent_id>
> so the parent's version would be set correctly but concurrent inserts would be permitted.
>
> So now to your suggestion of a trigger  -
> Yes,   I think it can be invoked in the case in question,  but only if it is defined as
> a BEFORE statement trigger,  not an INSTEAD OF trigger,   and then it cannot
> prevent the failing UPDATE from being done after it (trigger) has run.
> We would really need an INSTEAD OF statement-level trigger but there is no such capability.
>
> RULEs seem to be more general than triggers and I didn't see anything quite so clear-cut
> in the documentation to imply it can't be done,   other than the notes I quoted earlier
> from chapter 38.3.1. How Update Rules Work
> about the query trees and that the original query's qualification is always present.
>
> 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)

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

Regards,
Dean