On 09/01/12 03:46, Dean Rasheed wrote:
On 31 August 2012 16:32, John Lumby<johnlumby@xxxxxxxxxxx> wrote:
_______________________________
From: pavan.deolasee@xxxxxxxxx
Date: Fri, 31 Aug 2012 11:09:42 +0530
Subject: Re: UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?
On Thu, Aug 30, 2012 at 6:31 PM, John Lumby
<johnlumby@xxxxxxxxxxx<mailto:johnlumby@xxxxxxxxxxx>> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
