RE: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏

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