Thread: UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏

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.

Is this at all possible?    I have tried with variations of ALSO|INSTEAD etc
but the RULE is never invoked in the failing case.      And my reading of
chapter 38.3.1. How Update Rules Work
is that the original query's quallification is always present.

Example :

create table updatable (id bigint , version int , discriminator text)

insert into updatable values (1 , 0 , 'root')
insert into updatable values (2 , 0 , 'leaf')
select * from updatable order by id
 id | version | discriminator
----+---------+---------------
  1 |       0 | root
  2 |       0 | leaf
(2 rows)

CREATE or REPLACE FUNCTION optlock_control( OLD public.updatable , NEW public.updatable )
 returns bool LANGUAGE c AS '\$libdir/optlock_control.so', 'optlock_control'

/*  for this little test,  this function always inserts one row with a high id into the table update  */

CREATE OR REPLACE RULE update_updatable AS ON UPDATE to updatable
    DO INSTEAD SELECT optlock_control(OLD,NEW)

Now I want optlock_control() invoked on every UPDATE of updatable.

try it with an UPDATE that would have succeeded:
update updatable set version = 1 where id = 2 and version = 0
 optlock_control
-----------------
 f
(1 row)
/*  verify function was invoked */
select * from updatable order by id
 id  | version | discriminator
-----+---------+---------------
   1 |       0 | root
   2 |       0 | leaf
 999 |       0 | 9            /*  yes it was */
(3 rows)


Now try it with an UPDATE that would have failed:

update updatable set version = 2 where id = 1 and version = 1
 optlock_control
-----------------
(0 rows)

UPDATE 0
/*  was my function invoked ? */
select * from updatable order by id"
 id  | version | discriminator
-----+---------+---------------
   1 |       0 | root
   2 |       0 | leaf
 999 |       0 | 9
(3 rows)
/*  no it wasnt */

_______________________________
> 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

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

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

On 2 September 2012 22:42, johnlumby <johnlumby@hotmail.com> wrote:
> On 09/01/12 03:46, Dean Rasheed wrote:
>> 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."
>

What it's saying is that if you run a SELECT statement inside the
BEFORE trigger function, you won't see the new values because the
table hasn't been updated yet. However, a BEFORE UPDATE trigger has
access to variables called OLD and NEW which are designed specifically
for that purpose (you don't need to do a SELECT in the trigger). OLD
is the value currently in the table (before the update) and NEW is the
value about to be set on the table (modulo the caveat below).

So you can implement optimistic locking as follows:
1). SELECT the original data from the table, including the original
value of version
2). Work out the new values to set
3). UPDATE the table with the new values, and set version=original_version+1

Then in the BEFORE UPDATE trigger NEW.version will be equal to
original_version+1. So if you compare NEW.version with OLD.version+1,
you are really comparing OLD.version with original_version, i.e.,
testing that the value in the table immediately before the update is
same as in step (1). Thus it traps the case where another process has
modified the row under your feet. By that point, postgresql has a lock
on the row about to be modified, so you are guarded against race
conditions.


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

That's just referring to the fact that you may have defined multiple
different BEFORE triggers. Each has access to OLD and NEW variables,
and is allowed to modify NEW in order to change what ends up in the
table. So the final result would depend on the order that the triggers
are fired.

In practice it is probably best to avoid this by not defining multiple
BEFORE triggers for the same operation on the same table.


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

I can understand that. However...

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

I don't think that it is possible in postgresql.

It might be possible on the Hibernate side. I've never looked to see
if that code can be overridden.

Regards,
Dean

----------------------------------------
> Date: Mon, 3 Sep 2012 09:31:21 +0100
> Subject: Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏
> From: dean.a.rasheed@gmail.com
> To: johnlumby@hotmail.com
> CC: pgsql-general@postgresql.org; pavan.deolasee@gmail.com
>
> On 2 September 2012 22:42, johnlumby <johnlumby@hotmail.com> wrote:
> > On 09/01/12 03:46, Dean Rasheed wrote:
> >> 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."
> >
>
> What it's saying is that if you run a SELECT statement inside the
> BEFORE trigger function, you won't see the new values because the
> table hasn't been updated yet. However, a BEFORE UPDATE trigger has
> access to variables called OLD and NEW which are designed specifically
> for that purpose (you don't need to do a SELECT in the trigger). OLD
> is the value currently in the table (before the update) and NEW is the
> value about to be set on the table (modulo the caveat below).
>
> So you can implement optimistic locking as follows:
> 1). SELECT the original data from the table, including the original
> value of version
> 2). Work out the new values to set
> 3). UPDATE the table with the new values, and set version=original_version+1
>

Thanks Dean.      I tried it out and it works perfectly using the BEFORE row trigger.

Incidentally :  testing this It also brought to light a strange aspect of
postgresql locking that I had never seen before - the "transactionid" lock
and a deadlock involving same in certain circumstances.   I mention this
just in case someone comes along and tries out this trigger idea for
enforcing serialization.      Because of the transactionid lock, deadlocks
can arise even when the two deadlocked transactions are both updating
the very same database row and hold no other contested row/table locks,
which is hardly intuitive.     Some discussion of this here
       http://archives.postgresql.org/pgsql-novice/2010-05/msg00065.php
Here is what my particular deadlock looked like

STATEMENT:  update ENTITY set version=$1, inherit_right=$2, name=$3, parent_id=$4, association_id=$5,
association2_id=$6,long1=$7, long2=$8 where id=$9 
LOG:  00000: execute <unnamed>: update ENTITY set version=$1, inherit_right=$2, name=$3, parent_id=$4,
association_id=$5,association2_id=$6, long1=$7, long2=$8 where id=$9 
DETAIL:  parameters: $1 = '3', $2 = 't', $3 = NULL, $4 = '5', $5 = '5', $6 = NULL, $7 = '100663296', $8 = '117440511',
$9= '909' 
LOCATION:  exec_execute_message, postgres.c:1976
ERROR:  40P01: deadlock detected
DETAIL:  Process 11251 waits for ExclusiveLock on tuple (0,91) of relation 16416 of database 16384; blocked by process
11246.
    Process 11246 waits for ShareLock on transaction 3196; blocked by process 11251.
    Process 11251: update ENTITY set version=$1, inherit_right=$2, name=$3, parent_id=$4, association_id=$5,
association2_id=$6,long1=$7, long2=$8 where id=$9 
    Process 11246: update ENTITY set version=$1, inherit_right=$2, name=$3, parent_id=$4, association_id=$5,
association2_id=$6,long1=$7, long2=$8 where id=$9 


> Then in the BEFORE UPDATE trigger NEW.version will be equal to
> original_version+1. So if you compare NEW.version with OLD.version+1,
> you are really comparing OLD.version with original_version, i.e.,
> testing that the value in the table immediately before the update is
> same as in step (1). Thus it traps the case where another process has
> modified the row under your feet. By that point, postgresql has a lock
> on the row about to be modified, so you are guarded against race
> conditions.
>
>
> Regards,
> Dean