Thread: fail-safe sql update triggers
I want to log all activity from a table to a old_table. Creating an ON INSERT trigger is simple, it just needs to INSERT INTO old_filter SELECT NEW.*; in a procedure that is called via the trigger. But what about updates? There is no simple UPDATE old_filter SET NEW.* WHERE id=NEW.id; so I would need to declare each column like UPDATE old_filter SET field1=NEW.field1,f2=NEW.f2,.... where id=NEW.id; But that is error prone, because when the "filter" table is changed to have a new column, the UPDATE statement would notcontain it. Is there a fail-proof shortcut? mit freundlichen Grüßen Michael Monnerie, Ing. BSc it-management Internet Services http://proteger.at [gesprochen: Prot-e-schee] Tel: 0660 / 415 65 31 // Wir haben im Moment zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://zmi.at/haus2009/
Michael Monnerie<michael.monnerie@is.it-management.at> wrote: > But that is error prone, because when the "filter" table is > changed to have a new column, the UPDATE statement would not > contain it. Is there a fail-proof shortcut? You might draw inspiration for a C-based solution from Andrew Dunstan's "minimal update" code. A quick search turned up this, but you might want to read the whole thread and look for possible later versions of the code: http://archives.postgresql.org/pgsql-hackers/2008-10/msg01111.php -Kevin
I thought I sent this earlier, but it's not in my Sent box, so I'll try again. Your solution maintains an exact copy of two tables. Whenever a record is updated in the first, it is updated in the second, and you have lost information about the previous value. Whenever I do anything like this, I have three triggers on the source table, one each for insert, update and delete. The history table has the same columns as the source table, plus two more, one named "action" and the other named "event_time". The event_time field defaults to the current time. The bodies of the three trigger functions are: insert into history select new.*, 'Insert' insert into history select new.*, 'Update' insert into history select old.*, 'Delete' That way, I can track everything that happened in my source table. Typically, there's something else, like maybe another trigger, that deletes old records from the history table. I hope this helps! RobR
On Freitag, 3. September 2010 Rob Richardson wrote: > Your solution maintains an exact copy of two tables. Whenever a > record is updated in the first, it is updated in the second, and you > have lost information about the previous value. I have a table "filter" where the real work is done. Data in there is needed up to 30 hours, the table growing quickly. We only need to keep the last state of a record, not all it's revisions. When the record is not needed anymore (can be within one minute after inserting also) it should be deleted out of the "filter" table, but kept in "old_filter". We also need the data to be immediately in the "old_filter" table, so we can't simply copy the record on delete. What I do now in the function is IF we are UPDATEing a record: DELETE from old_filter; ENDIF INSERT INTO old_filter SELECT NEW.*; So when an update occurred on filter, I simply delete and insert the record. That's OK, but an UPDATE would be better for performance. But there's no easy and quick method to do that, right? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://proteger.at [gesprochen: Prot-e-schee] Tel: 0660 / 415 65 31 ****** Aktuelles Radiointerview! ****** http://www.it-podcast.at/aktuelle-sendung.html // Wir haben im Moment zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://zmi.at/haus2009/
Attachment
On Freitag, 3. September 2010 Kevin Grittner wrote: > You might draw inspiration for a C-based solution from Andrew > Dunstan's "minimal update" code. Sorry that is too code-ish for me. I looked into it, but didn't see an UPDATE statement that I'd need. -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://proteger.at [gesprochen: Prot-e-schee] Tel: 0660 / 415 65 31 ****** Aktuelles Radiointerview! ****** http://www.it-podcast.at/aktuelle-sendung.html // Wir haben im Moment zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://zmi.at/haus2009/
Attachment
> -----Original Message----- > From: Michael Monnerie [mailto:michael.monnerie@is.it-management.at] > Sent: Friday, September 03, 2010 9:03 AM > To: pgsql-admin@postgresql.org > Subject: fail-safe sql update triggers > > I want to log all activity from a table to a old_table. > Creating an ON INSERT trigger is simple, it just needs to > > INSERT INTO old_filter SELECT NEW.*; > > in a procedure that is called via the trigger. But what about > updates? There is no simple > > UPDATE old_filter SET NEW.* WHERE id=NEW.id; > > so I would need to declare each column like > > UPDATE old_filter SET field1=NEW.field1,f2=NEW.f2,.... where > id=NEW.id; > > But that is error prone, because when the "filter" table is > changed to have a new column, the UPDATE statement would not > contain it. Is there a fail-proof shortcut? > > > mit freundlichen Grüßen > Michael Monnerie, Ing. BSc > Michael, You are seeing only half of the problem. When you modify your "filter" table (i.e. add a column), not only UPDATE statement in your trigger function should be modifiedto reflect the change in the original "filter" table, but also your history table "old_filter" should be modifiedas well: new column should be added. So, in short there is no "easy" way around. When "source" table is modified, "destination" table and trigger function should be also modified. Regards, Igor Neyman