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

From Mike Rylander
Subject Re: Implementing a change log
Date
Msg-id b918cf3d0509181629550d59d0@mail.gmail.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
On 9/18/05, Michael Schuerig <michael@schuerig.de> wrote:
>
> In my current project I have a customer requirement for implementing a
> change log. This is not just for auditing purposes, rather it is meant
> to be accessible by users so they can get an overview of the change
> history of an object. The entire data set is not big, I'm expecting
> considerably less than 50.000 records. Changes are only made by about
> 30 human users.
>

Will you need to tell who made what changes?  If so, you'll probably
want each user to be a fully fledged Postgres users.

> Queries related to change history are (a) for all changes during a
> specific time interval and (b) for all changes to a particular record.
>
> 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.
>

Keeping a single set of tables can get pretty complex.  You'd need to
mask each table with a view and a set of rules for working with only
the newest version of each record.  I always set up as separate set of
audit tables to record the old versions of each row.

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

I blogged about my most recent incarnation of "audit tables" here:
http://open-ils.org/blog/?p=28 .  We don't use Postgres users (we have
2 million), but it would be trivial to modify what I've done there to
work with real PG users or any other particulars of your environment.

Hope that helps!

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

pgsql-general by date:

Previous
From: Michael Schuerig
Date:
Subject: Implementing a change log
Next
From: Michael Schuerig
Date:
Subject: Re: Implementing a change log