Thread: Column level triggers

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 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.

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

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