Column level triggers

On Mon, Oct 13, 2008 at 3:44 AM, Laurent Wandrebeck
<l.wandrebeck@gmail.com> wrote:

Hi,

According to the documentation (
http://www.postgresql.org/docs/8.3/interactive/sql-createtrigger.html
), the feaure "SQL allows triggers to fire on updates to specific
columns (e.g., AFTER UPDATE OF col1, col2)" is missing.
After a bit of research, I found that this feature was in the TODO
list ( http://wiki.postgresql.org/wiki/Todo#Triggers ), and that a
patch was proposed on 2005/07.
Is it going to be implemented soon ? It would greatly help, IMHO, for
load, and simplify the write of plpgsql functions called by before
triggers.
Regards, and keep up the good work, that DBMS (mostly;) rocks !

You'll probably have to ask that in -hackers. I'm guessing it's one
of those things that if one wrote a sufficiently large check one could
find a hacker to implement it. But I can't imagine it being a weekend
project, and if it's not already in 8.4 beta it wouldn't make it to
8.4, but you'd have to shoot for 8.5.

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

... which is pretty much the same thing a built-in implementation would
have to do, too. So it'd save you a bit of typing but nothing more.

regards, tom lane

On Tue, Oct 14, 2008 at 6:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

... which is pretty much the same thing a built-in implementation would
have to do, too. So it'd save you a bit of typing but nothing more.

Well, I'd assume that a built in solution would be doing the short
circuiting in C which would make plpgsql based triggers fire less
often, so I'd expect there to be some small performance gain. But if
you write triggers in C I'm guessing there wouldn't be much of one
then, right?

Scott Marlowe wrote:

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

I think column-level triggers actually fire when one of the columns is
written to, not only when the value there is distinct from the previous
one. This small difference is not easy to emulate by comparing OLD and
NEW in the trigger body. Details might need to be checked in the
standard and existing implementations.

2008/10/15 Scott Marlowe <scott.marlowe@gmail.com>:

You'll probably have to ask that in -hackers. I'm guessing it's one
of those things that if one wrote a sufficiently large check one could
find a hacker to implement it. But I can't imagine it being a weekend
project, and if it's not already in 8.4 beta it wouldn't make it to
8.4, but you'd have to shoot for 8.5.

OK, will forward that to the more appropriate ml.

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

Agreed, but it's not the cleanest way (well, actually, it is, right now:).
Laurent.

Hi,

According to the documentation (
http://www.postgresql.org/docs/8.3/interactive/sql-createtrigger.html
), the feaure "SQL allows triggers to fire on updates to specific
columns (e.g., AFTER UPDATE OF col1, col2)" is missing.
After a bit of research, I found that this feature was in the TODO
list ( http://wiki.postgresql.org/wiki/Todo#Triggers ), and that a
patch was proposed on 2005/07.
Is it going to be implemented soon ? It would greatly help, IMHO, for
load, and simplify the write of plpgsql functions called by before
triggers.
Regards, and keep up the good work, that DBMS (mostly;) rocks !

Laurent

On Wednesday 15 October 2008 04:19:59 Laurent Wandrebeck wrote:

2008/10/15 Scott Marlowe <scott.marlowe@gmail.com>:

You'll probably have to ask that in -hackers. I'm guessing it's one
of those things that if one wrote a sufficiently large check one could
find a hacker to implement it. But I can't imagine it being a weekend
project, and if it's not already in 8.4 beta it wouldn't make it to
8.4, but you'd have to shoot for 8.5.

Actually, the final commitfest for 8.4 isn't untill November 1st, so if you
did have something you wanted to get into 8.4, you have 2 weeks to make it
into the last commitfest; after that you're probably looking at 8.5.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Oct 15, 2008, at 3:21 AM, Laurent Wandrebeck wrote:

According to the documentation (
http://www.postgresql.org/docs/8.3/interactive/sql-createtrigger.html
), the feaure "SQL allows triggers to fire on updates to specific
columns (e.g., AFTER UPDATE OF col1, col2)" is missing.
After a bit of research, I found that this feature was in the TODO
list ( http://wiki.postgresql.org/wiki/Todo#Triggers ), and that a
patch was proposed on 2005/07.
Is it going to be implemented soon ? It would greatly help, IMHO, for
load, and simplify the write of plpgsql functions called by before
triggers.
Regards, and keep up the good work, that DBMS (mostly;) rocks !

If the patch was submitted back in 2005 and nothing's happened since
then I'd say the author probably lost interest, which means that it
won't be added until someone else gets interested in it. So I'd
suggest either rolling up your sleeves or dangling a carrot (money)
in front of some of the people that do consulting and back-end hacking.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Peter Eisentraut <peter_e@gmx.net> writes:

Scott Marlowe wrote:

Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.

I think column-level triggers actually fire when one of the columns is
written to, not only when the value there is distinct from the previous
one. This small difference is not easy to emulate by comparing OLD and
NEW in the trigger body.

So what happens when one of the target columns is modified by another
trigger, rather than by the SQL query? (For extra credit, what if it's
a trigger that comes after the column trigger in the firing order?)

regards, tom lane

2008/10/15 Decibel! <decibel@decibel.org>:

If the patch was submitted back in 2005 and nothing's happened since then
I'd say the author probably lost interest, which means that it won't be
added until someone else gets interested in it. So I'd suggest either
rolling up your sleeves or dangling a carrot (money) in front of some of the
people that do consulting and back-end hacking.

Well I theorically could try to work on it in a couple months, once I
have finished the project I'm now on, which made me it hit that
limitation.
The main problem are:
- I know absolutely nothing of pg internals, as I'm just a pg user.
- The carrot I can propose as a person is likely to be too small.
@tom: the answers to your questions are of interest. I'd be glad to
know the answers too :)
Regards,
Laurent.