Triggers on columns

On Thu, Sep 03, 2009 at 10:52:09AM +0900, Itagaki Takahiro wrote:

Here is a patch to implement "Support triggers on columns" in our ToDo list.

The syntax is:
CREATE TRIGGER name
BEFORE UPDATE OF col1, col12, ...
ON tbl FOR EACH ROW EXECUTE PROCEDURE func();

Kudos!

I consulted the previous work following:
Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04)
http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php
and completed some under-construction parts.

It's still arguable that we should add dependencies from column
triggers to referenced columns.

+1 for adding the dependencies.

Cheers,
David.

In the present patch, dropeed
columns are just ignored and always considered as not-modified.
Please grep with "TODO: (TRIGGER)" to check the issue.

Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, Sep 2, 2009 at 9:52 PM, Itagaki
Takahiro<itagaki.takahiro@oss.ntt.co.jp> wrote:

Here is a patch to implement "Support triggers on columns" in our ToDo list.

The syntax is:
   CREATE TRIGGER name
       BEFORE UPDATE OF col1, col12, ...
       ON tbl FOR EACH ROW EXECUTE PROCEDURE func();

I consulted the previous work following:
   Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04)
   http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php
and completed some under-construction parts.

It's still arguable that we should add dependencies from column
triggers to referenced columns. In the present patch, dropeed
columns are just ignored and always considered as not-modified.
Please grep with "TODO: (TRIGGER)" to check the issue.

Comments welcome.

Wow, so I wouldn't have to do this any more?

IF (TG_OP = 'UPDATE') THEN
IF (OLD.foo IS NOT DISTINCT FROM NEW.foo AND OLD.bar IS NOT
DISTINCT FROM NEW.bar
AND OLD.baz IS NOT DISTINCT FROM NEW.baz) THEN
RETURN NULL;
END IF;
END IF;

Apart from any possible gain in efficiency, the sheer savings in
typing sound quite awesome.

...Robert

Robert Haas <robertmhaas@gmail.com> wrote:

Wow, so I wouldn't have to do this any more?

IF (TG_OP = 'UPDATE') THEN
IF (OLD.foo IS NOT DISTINCT FROM NEW.foo AND OLD.bar IS NOT
DISTINCT FROM NEW.bar
AND OLD.baz IS NOT DISTINCT FROM NEW.baz) THEN
RETURN NULL;
END IF;
END IF;

Sure, and I found there might be difference between "UPDATE" and
"UPDATE OF {all-columns}" triggers. UPDATE trigger is always fired
when a row is updated even if none of the columns are actually
modified, but UPDATE OF {all-columns} trigger is fired only when
at least one of the columns is modified.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

David Fetter <david@fetter.org> wrote:

It's still arguable that we should add dependencies from column
triggers to referenced columns.

+1 for adding the dependencies.

But how? First, I tried to use existing dependency mechanism:

ObjectAddress referenced;
referenced.classId = AttributeRelationId;
referenced.objectId = {relid};
referenced.objectSubId = {attnum};
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);

but we don't use ObjectAddress with classId = AttributeRelationId
for now in any places. Does it work? or do I also need to modify
dependency.c to support dependency-to-columns?

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Itagaki Takahiro wrote:

David Fetter <david@fetter.org> wrote:

It's still arguable that we should add dependencies from column
triggers to referenced columns.

+1 for adding the dependencies.

But how? First, I tried to use existing dependency mechanism:

ObjectAddress referenced;
referenced.classId = AttributeRelationId;
referenced.objectId = {relid};
referenced.objectSubId = {attnum};
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);

but we don't use ObjectAddress with classId = AttributeRelationId
for now in any places. Does it work?

Well, apparently you've been tasked with making sure it works properly :-)
The only problem I see with it is the fact that the objectId is not the
attribute's OID, but it should be possible.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:

But how? First, I tried to use existing dependency mechanism:

ObjectAddress referenced;
referenced.classId = AttributeRelationId;
referenced.objectId = {relid};
referenced.objectSubId = {attnum};

This is just wrong. The correct representation of a column is
classId = RelationRelationId
objectId = relid
objectSubId = attnum
The column is a sub-object of a pg_class item, not an object in
its own right.

regards, tom lane

Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:

Sure, and I found there might be difference between "UPDATE" and
"UPDATE OF {all-columns}" triggers. UPDATE trigger is always fired
when a row is updated even if none of the columns are actually
modified, but UPDATE OF {all-columns} trigger is fired only when
at least one of the columns is modified.

I'm betraying the fact that I haven't read the patch, but ...
exactly how, and when, are you determining whether a column has
been "modified"? I can't count the number of times somebody
has proposed simplistic and incorrect solutions to that.
Usually they forget about BEFORE triggers changing the row.

regards, tom lane

Tom Lane wrote:

Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:

Sure, and I found there might be difference between "UPDATE" and
"UPDATE OF {all-columns}" triggers. UPDATE trigger is always fired
when a row is updated even if none of the columns are actually
modified, but UPDATE OF {all-columns} trigger is fired only when
at least one of the columns is modified.

I'm betraying the fact that I haven't read the patch, but ...
exactly how, and when, are you determining whether a column has
been "modified"? I can't count the number of times somebody
has proposed simplistic and incorrect solutions to that.
Usually they forget about BEFORE triggers changing the row.

It uses heap_tuple_attr_equals() to check whether a certain
column is modified, or not.

Itagaki-san, isn't it more suitable to check rte->modifiedCols
than heap_tuple_attr_equals()? Although, this information is
not delivered to executor...

What is the correct behavior when UPDATE statement set a new
value but it was identical to the original value?
In this case, heap_tuple_attr_equals() cannot detect the column
is used as a target of the UPDATE.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

Tom Lane <tgl@sss.pgh.pa.us> wrote:

exactly how, and when, are you determining whether a column has
been "modified"? I can't count the number of times somebody
has proposed simplistic and incorrect solutions to that.
Usually they forget about BEFORE triggers changing the row.

There are some approaches:

1. Just check conditions in alphabetical order. Ignore subsequent
modifications after the conditions are examined.

2. Recheck conditions if NEW values are modified, but triggers that
have been fired already are not executed twice.

3. Column triggers are called after non-conditional UPDATE triggers
and column triggers cannot modify NEW values.

I like approach 2. because it is the most user-friendly. There is a
possibility that another trigger changes NEW values to "unmodified"
state after some conditional triggers are executed, but it could be
admissible. The approach 3. seems to be the most strict, but hard to
use because of the restriction.

----
Just for reference:

- Oracle Database:
They support multiple triggers and UPDATE OF and WHEN clause and
can modify NEW values in trigger bodies. So they must have same
problems discussing here -- but I cannot find how they work around it...
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2064026

- MySQL:
They can modify NEW values, but no problem because they don't support
UPDATE OF, WHEN clause, nor multiple triggers for each event.
http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

KaiGai Kohei <kaigai@ak.jp.nec.com> wrote:

Itagaki-san, isn't it more suitable to check rte->modifiedCols
than heap_tuple_attr_equals()? Although, this information is
not delivered to executor...

I'd like to check conditions by comparing actual values but not
a target of UPDATE statement because I think almost user expects
the former behavior. Unmodified UPDATE-targets are common case
if we use a framework that generates SQL statements internally.

Anyway, we need to compare the actual values if we want to treat
NEW value modifed by another trigger correctly.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote:

I'd like to check conditions by comparing actual values but not
a target of UPDATE statement because I think almost user expects
the former behavior. Unmodified UPDATE-targets are common case
if we use a framework that generates SQL statements internally.

The SQL standard specifies that a trigger is fired if the column is
mentioned in the UPDATE statement, independent of whether the value is
actually changed through the update.

On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote:

I'd like to check conditions by comparing actual values but not
a target of UPDATE statement because I think almost user expects
the former behavior. Unmodified UPDATE-targets are common case
if we use a framework that generates SQL statements internally.

The SQL standard specifies that a trigger is fired if the column is
mentioned in the UPDATE statement, independent of whether the value is
actually changed through the update.

That is thorougly bizarre, IMO.

...Robert

Robert Haas wrote:

On Wed, Sep 2, 2009 at 9:52 PM, Itagaki
Takahiro<itagaki.takahiro@oss.ntt.co.jp> wrote:

Here is a patch to implement "Support triggers on columns" in our ToDo list.

The syntax is:
CREATE TRIGGER name
BEFORE UPDATE OF col1, col12, ...
ON tbl FOR EACH ROW EXECUTE PROCEDURE func();

I consulted the previous work following:
Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04)
http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php
and completed some under-construction parts.

It's still arguable that we should add dependencies from column
triggers to referenced columns. In the present patch, dropeed
columns are just ignored and always considered as not-modified.
Please grep with "TODO: (TRIGGER)" to check the issue.

Comments welcome.

Wow, so I wouldn't have to do this any more?

IF (TG_OP = 'UPDATE') THEN
IF (OLD.foo IS NOT DISTINCT FROM NEW.foo AND OLD.bar IS NOT
DISTINCT FROM NEW.bar
AND OLD.baz IS NOT DISTINCT FROM NEW.baz) THEN
RETURN NULL;
END IF;
END IF;

Apart from any possible gain in efficiency, the sheer savings in
typing sound quite awesome.

You could make it nicer with something like:

row(new.foo,new.bar,new.baz) is distinct from
row(old.foo,old.bar,old.baz)

couldn't you?

I'm actually having trouble thinking of a case where I'd find this
feature very useful.

cheers

andrew

On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote:

On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

The SQL standard specifies that a trigger is fired if the column is
mentioned in the UPDATE statement, independent of whether the value is
actually changed through the update.

That is thorougly bizarre, IMO.

Well, if you find that bizarre, consider the existing behavior: Why
should an ON UPDATE row trigger fire when none of the values of the
row's columns actually change? I think if you read

TRIGGER ON UPDATE

as

TRIGER ON UPDATE OF <all columns>

then it makes some sense.

On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut<peter_e@gmx.net> wrote:

On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote:

On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

The SQL standard specifies that a trigger is fired if the column is
mentioned in the UPDATE statement, independent of whether the value is
actually changed through the update.

That is thorougly bizarre, IMO.

Well, if you find that bizarre, consider the existing behavior: Why
should an ON UPDATE row trigger fire when none of the values of the
row's columns actually change?  I think if you read

TRIGGER ON UPDATE

as

TRIGER ON UPDATE OF <all columns>

then it makes some sense.

Not to me. I use triggers to maintain database invariants, such as:

CREATE TABLE foo (id serial, name varchar, number_of_bars integer not
null default 0, primary key (id));
CREATE TABLE bar (id serial, foo_id integer not null references foo (id));

By setting up INSERT, UPDATE, and DELETE triggers on bar, I can
maintain the invariant that number_of_bars for each foo is in fact the
number of bars where foo_id is the id of that foo. However, in order
to suppress unnecessary updates to the foo table, I have to have the
update trigger check whether OLD.foo_id = NEW.foo_id before it does
anything.

If TRIGGER ON UPDATE OF foo_id means whether the value actually
changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id
means whether the column was present in the update list, then it
doesn't. Perhaps there are some use cases where we can be certain
that we only care about whether the value was in the update list, and
not whether it was changed, but off the top of my head it seems like
0% of mine would fall into that category.

It also seems to me logically inconsistent that we would expose this
information via the CREATE TRIGGER interface but not to the trigger
function itself. From within the function, you can compare NEW and
OLD, but you get no visibility into which columns were actually
updated. And apparently now from within CREATE TRIGGER we'll have
just the opposite. Blech...

By the way, I completely agree that it would be useful to have a way
to suppress triggers from firing when no columns were actually
modified. But I also wouldn't argue that should be the only available
behavior. Sometimes it's useful to schedule a no-op update explicitly
for the purpose of firing triggers.

...Robert

On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote:

If TRIGGER ON UPDATE OF foo_id means whether the value actually
changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id
means whether the column was present in the update list, then it
doesn't. Perhaps there are some use cases where we can be certain
that we only care about whether the value was in the update list, and
not whether it was changed, but off the top of my head it seems like
0% of mine would fall into that category.

Yeah, probably. I didn't make this up; I'm just reading the
standard. ;-)

But of course you can already do what you do, so you don't lose anything
if it turns out that this proposed feature ends up working the other
way.

It also seems to me logically inconsistent that we would expose this
information via the CREATE TRIGGER interface but not to the trigger
function itself. From within the function, you can compare NEW and
OLD, but you get no visibility into which columns were actually
updated. And apparently now from within CREATE TRIGGER we'll have
just the opposite. Blech...

Well, it might make sense to make this information available within the
trigger function through new variables.

Robert Haas <robertmhaas@gmail.com> wrote:

It also seems to me logically inconsistent that we would expose this
information via the CREATE TRIGGER interface but not to the trigger
function itself. From within the function, you can compare NEW and
OLD, but you get no visibility into which columns were actually
updated. And apparently now from within CREATE TRIGGER we'll have
just the opposite. Blech...

Sybase provides an "if update(columnname)" syntax to allow such tests.
Perhaps PostgreSQL could do something similar?

Sometimes it's useful to schedule a no-op update explicitly for the
purpose of firing triggers.

Yes. It's a less frequent need, but it does exist. The thing is, if
you only fire triggers if something was actually changed to a new
value, you can't get to that. If you fire on all updates you can test
whether there were actual changes. Of course, ideally, both would be
convenient.

-Kevin

Hi,

Robert Haas <robertmhaas@gmail.com> writes:

By the way, I completely agree that it would be useful to have a way
to suppress triggers from firing when no columns were actually
modified.

http://www.postgresql.org/docs/8.4/static/functions-trigger.html

Currently PostgreSQL provides one built in trigger function,
suppress_redundant_updates_trigger, which will prevent any update that
does not actually change the data in the row from taking place, in
contrast to the normal behaviour which always performs the update
regardless of whether or not the data has changed. (This normal
behaviour makes updates run faster, since no checking is required, and
is also useful in certain cases.)

...

The suppress_redundant_updates_trigger function can be added to a table like this:

CREATE TRIGGER z_min_update
BEFORE UPDATE ON tablename
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

Regards,
--
dim

On Thu, Sep 3, 2009 at 10:37 AM, Peter Eisentraut<peter_e@gmx.net> wrote:

On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote:

If TRIGGER ON UPDATE OF foo_id means whether the value actually
changed, then I can skip the check.  If TRIGGER ON UPDATE OF foo_id
means whether the column was present in the update list, then it
doesn't.  Perhaps there are some use cases where we can be certain
that we only care about whether the value was in the update list, and
not whether it was changed, but off the top of my head it seems like
0% of mine would fall into that category.

Yeah, probably.  I didn't make this up; I'm just reading the
standard. ;-)

But of course you can already do what you do, so you don't lose anything
if it turns out that this proposed feature ends up working the other
way.

Sure, but I don't think it makes a lot of sense to spend a lot of time
implementing the standard behavior unless someone can provide a
plausible use case. If that means we have to give our non-standard
feature an incompatible syntax or whatever so as not to create
confusion with the "standard" behavior, then let's do that, because it
sounds WAY more useful.

...Robert