Re: Implementing a change log

Greg Sabino Mullane wrote:

>-----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.
>
>
>
There was a very exciting discussion of this last May, in which Greg
Patnude suggested the most insightful, and in hindsight obviously
appropriate, use of table inheritance ever (IMHO). I slightly refined
the idea and posted documentation comments at the time. See "User
Comments" at

"http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html"

for something that should set you afire.