From: Michael Monnerie <michael.monnerie@is.it-management.at>
To: pgsql-admin@postgresql.org
Subject: Re: fail-safe sql update triggers
Date: Sat, 4 Sep 2010 01:46:28 +0200
Message-ID: <201009040146.33101@zmi.at> (raw)
In-Reply-To: <04A6DB42D2BA534FAC77B90562A6A03D015542F9@server.rad-con.local>
References: <vmime.4c80f217.42d2.c43fd4a13aa09607@zarafa14.zmi.at>
<04A6DB42D2BA534FAC77B90562A6A03D015542F9@server.rad-con.local>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: michael.monnerie@is.it-management.at
Subject: Re: fail-safe sql update triggers
In-Reply-To: <201009040146.33101@zmi.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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