Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 92C4F6318F3 for ; Fri, 3 Sep 2010 20:46:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 93315-03 for ; Fri, 3 Sep 2010 23:46:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailsrv14.zmi.at (mailsrv1.zmi.at [212.69.164.54]) by mail.postgresql.org (Postfix) with ESMTP id EB8FB62FBBF for ; Fri, 3 Sep 2010 20:46:37 -0300 (ADT) Received: from mailsrv.i.zmi.at (h081217106033.dyn.cm.kabsi.at [81.217.106.33]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified)) by mailsrv14.zmi.at (Postfix) with ESMTPSA id EB73A626 for ; Sat, 4 Sep 2010 01:46:33 +0200 (CEST) Received: from saturn.localnet (saturn.i.zmi.at [10.72.27.2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mailsrv.i.zmi.at (Postfix) with ESMTPSA id A1A12401C2F for ; Sat, 4 Sep 2010 01:46:33 +0200 (CEST) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: Re: fail-safe sql update triggers Date: Sat, 4 Sep 2010 01:46:28 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.35.4-zmi; KDE/4.3.5; x86_64; ; ) References: <04A6DB42D2BA534FAC77B90562A6A03D015542F9@server.rad-con.local> In-Reply-To: <04A6DB42D2BA534FAC77B90562A6A03D015542F9@server.rad-con.local> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart1390723.2LQgOGzWEF"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201009040146.33101@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201009/40 X-Sequence-Number: 35584 --nextPart1390723.2LQgOGzWEF Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. =20 I have a table "filter" where the real work is done. Data in there is=20 needed up to 30 hours, the table growing quickly. We only need to keep=20 the last state of a record, not all it's revisions. When the record is=20 not needed anymore (can be within one minute after inserting also) it=20 should be deleted out of the "filter" table, but kept in "old_filter".=20 We also need the data to be immediately in the "old_filter" table, so we=20 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=20 record. That's OK, but an UPDATE would be better for performance. But=20 there's no easy and quick method to do that, right? =2D-=20 mit freundlichen Gr=FCssen, 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=E4user zu verkaufen: // http://zmi.at/langegg/ // http://zmi.at/haus2009/ --nextPart1390723.2LQgOGzWEF Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.12 (GNU/Linux) iEYEABECAAYFAkyBiNkACgkQzhSR9xwSCbQm+ACg2KX91vRX1Hfa8nonZnRQkrST OfYAn0+G/lS/IHY3Xi7+pzK4WG+Xrf64 =9VFB -----END PGP SIGNATURE----- --nextPart1390723.2LQgOGzWEF--