Re: Implementing a change log - Mailing list pgsql-general

From Greg Sabino Mullane
Subject Re: Implementing a change log
Date
Msg-id 498079be1a9f7ca6290e78f4dcc17c04@biglumber.com
Whole thread Raw
In response to Implementing a change log  (Michael Schuerig <michael@schuerig.de>)
Responses Re: Implementing a change log
List pgsql-general
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> My original intention was to keep two sets of tables. The first
> containing only the working set of current records. The second
> containing all prior versions. I haven't experimented with such a setup
> yet and I'm wondering if it is even necessary. The alternative being to
> keep only a single set of tables.

> Can anyone relate their experiences with such a thing? Which approaches
> should I take into consideration?

I like the multi-table approach; I use a schema named "audit" that contains
a copy of some of the important tables (sans constraints). The nice part is
that I can use the exact same table name, which makes things easier. A few
extra columns on each audit table track who made the change, what type it
was (insert, update, or delete [trigger event]), and the time of the change
[default timestamptz]. Throw in some triggers and you're done.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509192258
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFDL3txvJuQZxSWSsgRArxSAJ9z4v+pRjULrBg4AiyD4jw7iHpE2wCg/qa0
UwTQQdH4CVfs97l4OgLUATY=
=Yap5
-----END PGP SIGNATURE-----



pgsql-general by date:

Previous
From: "Thomas F. O'Connell"
Date:
Subject: Re: Replication
Next
From: Tom Lane
Date:
Subject: Re: update problem in triggers