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

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/

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