Обсуждение: 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