pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfail-safe sql update triggers
6+ messages / 4 participants
[nested] [flat]
* fail-safe sql update triggers
@ 2010-09-03 13:03 Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-03 16:23 ` Re: fail-safe sql update triggers Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-09-03 17:50 ` Re: fail-safe sql update triggers Rob Richardson <Rob.Richardson@rad-con.com>
2010-09-07 12:55 ` Re: fail-safe sql update triggers Igor Neyman <ineyman@perceptron.com>
0 siblings, 3 replies; 6+ messages in thread
From: Michael Monnerie @ 2010-09-03 13:03 UTC (permalink / raw)
To: pgsql-admin
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
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fail-safe sql update triggers
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2010-09-03 16:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-09-03 23:53 ` Re: fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
2 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-09-03 16:23 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fail-safe sql update triggers
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-03 16:23 ` Re: fail-safe sql update triggers Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-09-03 23:53 ` Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Monnerie @ 2010-09-03 23:53 UTC (permalink / raw)
To: pgsql-admin
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/
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fail-safe sql update triggers
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2010-09-03 17:50 ` Rob Richardson <Rob.Richardson@rad-con.com>
2010-09-03 23:46 ` Re: fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
2 siblings, 1 reply; 6+ messages in thread
From: Rob Richardson @ 2010-09-03 17:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fail-safe sql update triggers
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-03 17:50 ` Re: fail-safe sql update triggers Rob Richardson <Rob.Richardson@rad-con.com>
@ 2010-09-03 23:46 ` Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Monnerie @ 2010-09-03 23:46 UTC (permalink / raw)
To: pgsql-admin
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/
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fail-safe sql update triggers
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2010-09-07 12:55 ` Igor Neyman <ineyman@perceptron.com>
2 siblings, 0 replies; 6+ messages in thread
From: Igor Neyman @ 2010-09-07 12:55 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; pgsql-admin
> -----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 modified to reflect the change in the original "filter" table, but also your history table "old_filter" should be modified as 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-09-07 12:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-03 13:03 fail-safe sql update triggers Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-03 16:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-09-03 23:53 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-03 17:50 ` Rob Richardson <Rob.Richardson@rad-con.com>
2010-09-03 23:46 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2010-09-07 12:55 ` Igor Neyman <ineyman@perceptron.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox