Skip site navigation (1) Skip section navigation (2)

Re: fail-safe sql update triggers

From: Michael Monnerie <michael(dot)monnerie(at)is(dot)it-management(dot)at>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: fail-safe sql update triggers
Date: 2010-09-03 23:46:28
Message-ID: 201009040146.33101@zmi.at (view raw or flat)
Thread:
Lists: 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/

In response to

pgsql-admin by date

Next:From: Michael MonnerieDate: 2010-09-03 23:53:20
Subject: Re: fail-safe sql update triggers
Previous:From: A JDate: 2010-09-03 22:57:18
Subject: Re: Confused by 'timing' results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group