pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBEFORE UPDATE Triggers
4+ messages / 4 participants
[nested] [flat]
* BEFORE UPDATE Triggers
@ 2003-08-28 21:48 Chris Anderson <cva@pobox.com>
2003-08-30 02:50 ` Re: BEFORE UPDATE Triggers Jan Wieck <jan@black-lion.info>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Anderson @ 2003-08-28 21:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BEFORE UPDATE Triggers
2003-08-28 21:48 BEFORE UPDATE Triggers Chris Anderson <cva@pobox.com>
@ 2003-08-30 02:50 ` Jan Wieck <jan@black-lion.info>
2003-08-30 21:56 ` Re: BEFORE UPDATE Triggers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Wieck @ 2003-08-30 02:50 UTC (permalink / raw)
To: Chris Anderson <cva@pobox.com>; +Cc: 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 #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BEFORE UPDATE Triggers
2003-08-28 21:48 BEFORE UPDATE Triggers Chris Anderson <cva@pobox.com>
2003-08-30 02:50 ` Re: BEFORE UPDATE Triggers Jan Wieck <jan@black-lion.info>
@ 2003-08-30 21:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-01 02:12 ` Re: BEFORE UPDATE Triggers Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-08-30 21:56 UTC (permalink / raw)
To: Jan Wieck <jan@black-lion.info>; +Cc: Chris Anderson <cva@pobox.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BEFORE UPDATE Triggers
2003-08-28 21:48 BEFORE UPDATE Triggers Chris Anderson <cva@pobox.com>
2003-08-30 02:50 ` Re: BEFORE UPDATE Triggers Jan Wieck <jan@black-lion.info>
2003-08-30 21:56 ` Re: BEFORE UPDATE Triggers Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-01 02:12 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2003-09-01 02:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Anderson <cva@pobox.com>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-09-01 02:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-28 21:48 BEFORE UPDATE Triggers Chris Anderson <cva@pobox.com>
2003-08-30 02:50 ` Jan Wieck <jan@black-lion.info>
2003-08-30 21:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-01 02:12 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox