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. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
