Re: BEFORE UPDATE Triggers

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(at)Yahoo(dot)com #