Thread: BEFORE UPDATE Triggers
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. 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. Oh, and I am aware of the current_user variable. In my case this is useless as I don't care about the user at the database layer but rather at the application layer. Thanks in advance, cva
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 #
Jan Wieck <jan@black-lion.info> writes:
> 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.
> 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.
People keep suggesting this, but I've never thought it was a very sane
idea. What if some BEFORE trigger upstream of yours changes the column?
You won't find that out unless you actually compare the OLD and NEW
column values. If you assume the column has not changed just because
the original query text didn't change it, you are in for a world of hurt.
regards, tom lane
Tom Lane wrote: > Jan Wieck <jan@black-lion.info> writes: >> 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. > >> 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. > > People keep suggesting this, but I've never thought it was a very sane > idea. What if some BEFORE trigger upstream of yours changes the column? > You won't find that out unless you actually compare the OLD and NEW > column values. If you assume the column has not changed just because > the original query text didn't change it, you are in for a world of hurt. That's exactly why I allways ask to look at it from a business process point of view. The NEW data that will be persistent when the transaction commits has to match the new status of the business process. Where this data exactly is coming from, got manipulated or how it got there is not really relevant. What counts is that the system transforms from one consistent state into another in an ACID transaction. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #