Thread: Trigger question
Hi, I am using pg 7.4.1 and have created a trigger over table with 3 M rows. If I start masive update on this table, pg executes this trigger on every row and dramaticaly slows the system. Exists in pg any way to define the trigger execution only if I have changes on some fields? For example I am able to declare this in oracle. My trigger is writen in pgSQL. regards, ivan.
On Thursday 15 January 2004 13:13, pginfo wrote: > Hi, > > I am using pg 7.4.1 and have created a trigger over table with 3 M rows. > If I start masive update on this table, pg executes this trigger on > every row and dramaticaly slows the system. > Exists in pg any way to define the trigger execution only if I have > changes on some fields? Not at the moment (and I don't know of any plans for it). > For example I am able to declare this in oracle. > My trigger is writen in pgSQL. Hmm - I can only think of two things you can try: 1. check for the change first thing you do and exit if not there 2. do the same, but write the trigger function in 'C' -- Richard Huxton Archonet Ltd
Guten Tag Richard Huxton, Am Donnerstag, 15. Januar 2004 um 17:10 schrieben Sie: RH> On Thursday 15 January 2004 13:13, pginfo wrote: >> Hi, >> >> I am using pg 7.4.1 and have created a trigger over table with 3 M rows. >> If I start masive update on this table, pg executes this trigger on >> every row and dramaticaly slows the system. >> Exists in pg any way to define the trigger execution only if I have >> changes on some fields? That not, but perhaps a STATEMENT Level Trigger could help. It´s a new feature in 7.4.x and rather undocumented. http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html "FOR EACH STATEMENT" probably this can help you a bit. Christoph Nelles -- Mit freundlichen Grüssen Evil Azrael mailto:evilazrael@evilazrael.de
> Exists in pg any way to define the trigger execution only if I have > changes on some fields? No, but you chould check for those fields and return if no changes have been made. Depending on how intensive the trigger is, this might help. You may also want to look at statement-level triggers or conditional rules. Best Wishes, Chris Travers
In article <1022542776.20040115180811@evilazrael.de>, Evil Azrael <evilazrael@evilazrael.de> writes: > That not, but perhaps a STATEMENT Level Trigger could help. It´s a new > feature in 7.4.x and rather undocumented. http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html > "FOR EACH STATEMENT" probably this can help you a bit. Does anyone know how to access the affected values for statement-level triggers? I mean what the "old" and "new" pseudo-records are for row-level triggers.
Harald Fuchs <hf99@protecting.net> writes: > Does anyone know how to access the affected values for > statement-level triggers? I mean what the "old" and "new" > pseudo-records are for row-level triggers. Yeah, I didn't get around to implementing that. If anyone wants this feature, I'd encourage them to step up to the plate -- I'm not sure when I'll get the opportunity/motivation to implement this myself. -Neil
On Tuesday 20 January 2004 00:01, Neil Conway wrote: > Harald Fuchs <hf99@protecting.net> writes: > > Does anyone know how to access the affected values for > > statement-level triggers? I mean what the "old" and "new" > > pseudo-records are for row-level triggers. > > Yeah, I didn't get around to implementing that. If anyone wants this > feature, I'd encourage them to step up to the plate -- I'm not sure > when I'll get the opportunity/motivation to implement this myself. I didn't think they'd be meaningful for a statement-level trigger. Surely OLD/NEW are by definition row-level details. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: > I didn't think they'd be meaningful for a statement-level > trigger. Surely OLD/NEW are by definition row-level details. Granted; the feature in question is *some* means of accessing the result set of a statement-level trigger -- it probably would not use the same UI (i.e. OLD/NEW) that row-level triggers use. -Neil
Richard Huxton <dev@archonet.com> writes:
> On Tuesday 20 January 2004 00:01, Neil Conway wrote:
>> Yeah, I didn't get around to implementing that. If anyone wants this
>> feature, I'd encourage them to step up to the plate -- I'm not sure
>> when I'll get the opportunity/motivation to implement this myself.
> I didn't think they'd be meaningful for a statement-level trigger. Surely
> OLD/NEW are by definition row-level details.
According to the complainants, OLD/NEW are commonly available as
recordsets (tables) inside a statement trigger. I'm not very clear on
how that works myself --- in particular, one would think it important to
be able to work with corresponding pairs of OLD and NEW rows, which
would be painful with a table-like abstraction. Can anyone explain
exactly how it's done in, say, Oracle?
regards, tom lane
In article <24300.1074614549@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> writes: > Richard Huxton <dev@archonet.com> writes: >> On Tuesday 20 January 2004 00:01, Neil Conway wrote: >>> Yeah, I didn't get around to implementing that. If anyone wants this >>> feature, I'd encourage them to step up to the plate -- I'm not sure >>> when I'll get the opportunity/motivation to implement this myself. >> I didn't think they'd be meaningful for a statement-level trigger. Surely >> OLD/NEW are by definition row-level details. > According to the complainants, OLD/NEW are commonly available as > recordsets (tables) inside a statement trigger. Yes. > I'm not very clear on > how that works myself --- in particular, one would think it important to > be able to work with corresponding pairs of OLD and NEW rows, which > would be painful with a table-like abstraction. Why? If the underlying table has a primary key, finding corresponding pairs is trivial; if there isn't, it's impossible.
On Tue, 20 Jan 2004, Harald Fuchs wrote: > In article <24300.1074614549@sss.pgh.pa.us>, > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > Richard Huxton <dev@archonet.com> writes: > >> On Tuesday 20 January 2004 00:01, Neil Conway wrote: > >>> Yeah, I didn't get around to implementing that. If anyone wants this > >>> feature, I'd encourage them to step up to the plate -- I'm not sure > >>> when I'll get the opportunity/motivation to implement this myself. > > >> I didn't think they'd be meaningful for a statement-level trigger. Surely > >> OLD/NEW are by definition row-level details. > > > According to the complainants, OLD/NEW are commonly available as > > recordsets (tables) inside a statement trigger. > > Yes. > > > I'm not very clear on > > how that works myself --- in particular, one would think it important to > > be able to work with corresponding pairs of OLD and NEW rows, which > > would be painful with a table-like abstraction. > > Why? If the underlying table has a primary key, finding corresponding > pairs is trivial; if there isn't, it's impossible. I don't think that's sufficient unless you can guarantee that the primary key values never change for any reason that causes the trigger to try to correspond them.
Harald Fuchs <hf118@protecting.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I'm not very clear on
>> how that works myself --- in particular, one would think it important to
>> be able to work with corresponding pairs of OLD and NEW rows, which
>> would be painful with a table-like abstraction.
> Why? If the underlying table has a primary key, finding corresponding
> pairs is trivial; if there isn't, it's impossible.
Exactly. Nonetheless, the correspondence exists --- the UPDATE
definitely updated some particular row of the OLD set into some
particular one of the NEW set. If the trigger API makes it impossible
to reconstruct the matchup, the API is broken.
Even if there is a primary key, the API should not force you to rely
on that; what of an UPDATE that changes the primary key?
regards, tom lane
On Tuesday 20 January 2004 16:42, Tom Lane wrote: > Harald Fuchs <hf118@protecting.net> writes: > > Why? If the underlying table has a primary key, finding corresponding > > pairs is trivial; if there isn't, it's impossible. > > Exactly. Nonetheless, the correspondence exists --- the UPDATE > definitely updated some particular row of the OLD set into some > particular one of the NEW set. If the trigger API makes it impossible > to reconstruct the matchup, the API is broken. Perhaps they should be cursors? The only sensible way I can think of working with them would be: 1. count how many rows affected 2. step through one row at a time, doing something. I suppose there might be cases where you'd want to GROUP BY... which would mean you'd need some oid/row-id added to a "real" recordset. -- Richard Huxton Archonet Ltd
In article <200401201905.46699.dev@archonet.com>, Richard Huxton <dev@archonet.com> writes: > On Tuesday 20 January 2004 16:42, Tom Lane wrote: >> Harald Fuchs <hf118@protecting.net> writes: >> > Why? If the underlying table has a primary key, finding corresponding >> > pairs is trivial; if there isn't, it's impossible. >> >> Exactly. Nonetheless, the correspondence exists --- the UPDATE >> definitely updated some particular row of the OLD set into some >> particular one of the NEW set. If the trigger API makes it impossible >> to reconstruct the matchup, the API is broken. I would not say so. You could use tables without primary keys, and you could define statement-level triggers on them, but you could not identify a particular row in this very special and probably rare case. > Perhaps they should be cursors? The only sensible way I can think of working > with them would be: > 1. count how many rows affected > 2. step through one row at a time, doing something. When I read about the "insert" and "delete" pseudotables in a book about Transact-SQL, i was enthusiastic about the elegance of this idea: you're operating on multiple (perhaps lots of) rows, and the SQL way of doing that is by set-operations, i.e. single operations affecting a set of rows. Pseudotables extend this idea nicely into the area of statement-level triggers. Your cursor idea doesn't look very SQL-like to me. We really should find an Oracle/DB2/Informix guy who can tell us how to get that right.
On Wed, 21 Jan 2004, Harald Fuchs wrote: > In article <200401201905.46699.dev@archonet.com>, > Richard Huxton <dev@archonet.com> writes: > > > On Tuesday 20 January 2004 16:42, Tom Lane wrote: > >> Harald Fuchs <hf118@protecting.net> writes: > >> > Why? If the underlying table has a primary key, finding corresponding > >> > pairs is trivial; if there isn't, it's impossible. > >> > >> Exactly. Nonetheless, the correspondence exists --- the UPDATE > >> definitely updated some particular row of the OLD set into some > >> particular one of the NEW set. If the trigger API makes it impossible > >> to reconstruct the matchup, the API is broken. > > I would not say so. You could use tables without primary keys, and > you could define statement-level triggers on them, but you could not > identify a particular row in this very special and probably rare case. A technique that requires matching of primary key values also undercuts its usefulness for at least some types of triggers. For example, ON UPDATE referential actions need three pieces of information, the starting state, the end state and the mapping between those states. AFAICS you cannot fake the last by trying to map primary key values and still implement the constraint correctly. Even if 99.9% of the time the primary key value doesn't change, you can't safly implement the triggers this way. Other triggers of the same sort may run into the same problems. > > Perhaps they should be cursors? The only sensible way I can think of working > > with them would be: > > 1. count how many rows affected > > 2. step through one row at a time, doing something. > > When I read about the "insert" and "delete" pseudotables in a book > about Transact-SQL, i was enthusiastic about the elegance of this > idea: you're operating on multiple (perhaps lots of) rows, and the SQL > way of doing that is by set-operations, i.e. single operations > affecting a set of rows. Pseudotables extend this idea nicely into > the area of statement-level triggers. Your cursor idea doesn't look > very SQL-like to me. > > We really should find an Oracle/DB2/Informix guy who can tell us how > to get that right. It wouldn't surprise me if there was an internal key (or row number) that could be used to match the rows between the old and new.