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

_______________________________
> 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
>
> Thanks,
> Pavan