Re: [PERFORM] Trigger question
On Wed, 21 Jan 2004, Harald Fuchs wrote: In article [EMAIL PROTECTED], Richard Huxton [EMAIL PROTECTED] writes: On Tuesday 20 January 2004 16:42, Tom Lane wrote: Harald Fuchs [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Trigger question
On Tuesday 20 January 2004 00:01, Neil Conway wrote: Harald Fuchs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Trigger question
Richard Huxton [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Trigger question
Harald Fuchs [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Trigger question
On Tuesday 20 January 2004 16:42, Tom Lane wrote: Harald Fuchs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Trigger question
Harald Fuchs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Trigger question
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Trigger question
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
