Re: BEFORE UPDATE Triggers - Mailing list pgsql-sql

Chris Anderson wrote:
> PostgreSQL Version: 7.2.3
> Procedural Language: PL/pgSQL
> 
> I have a table which contains a field for the user who last modified 
> the record. Whenever a row in this table is updated, I want to have an 
> UPDATE trigger do the following things:
> 
> 1) Ensure the UPDATE query supplied a value for the action_user column
> 2) Log the record to an audit table so I can retrieve a change log
> 
> Part 2 was trivial, however it seemed natural that if I had the 
> following conditional in the trigger function:
> 
>     IF NEW.action_user ISNULL THEN ...
> 
> I could raise an exception if that field was not supplied. (which would 
> be the case if the function were triggered on an INSERT)
> 
> Unfortunately it seems this is not the case. The NEW record contains 
> values representing both the values explicitly provided with the UPDATE 
> as well as the existing values which were not stipulated in the query.

Unfortunately, you're right. There is no way do distinguish in a trigger 
or rule if a value in the new row did result from the UPDATE query or 
from target list expansion with OLD values. This would be a usefull 
information to restrict trigger invocation to cases where a specific 
attribute is touched (attribute triggers ... IIRC we have a TODO on that).

It would not be terribly hard to examine the original query during 
executor start, looking for bare OLD referencing Var nodes, and stick 
something like a flag array into the trigger information. That would 
misinterpret cases where someone explicitly does
    UPDATE t1 SET id = id, a = 2 WHERE id = 4711;

since this would result in the same parsetree construct as
    UPDATE t1 SET a = 2 WHERE id = 4711;

But what's the difference between the two queries from a business 
process point of view anyway? Then again, is
    UPDATE t1 SET id = 4711, a = 2 WHERE id = 4711;

significantly different? With the above suggested target list 
examination, the executor would claim "id" got modified - I object.

So far, the above suggested functionality could be used to avoid useless 
trigger invocation. A trigger checking validity of a value doesn't need 
to get fired if the value doesn't change. But you want it the other way 
around anyway.

> Is there any clever way around this limitation? It isn't the end of the 
> world if I cannot verify this constraint in postgres, however it would 
> have made it easier to ensure no one is making mistakes.

Why would it be a mistake if one does NOT overwrite an already correct 
value with the same value? Thinking of toasted values, where PostgreSQL 
actually does reuse an OLD value (like one resulting from targetlist 
expansion) for the NEW row by copying the toast reference instead of 
duplicating possibly megabytes of data just for a changed bool in the 
master row. You missed the recent discussions about VACUUM?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #