Re: Implementing a change log

On Tuesday 20 September 2005 08:44, Berend Tober wrote:
> Greg Sabino Mullane wrote:
> >-----BEGIN PGP SIGNED MESSAGE-----
> >Hash: SHA1

[MS: on audit 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"

Looks interesting, but I'm hesitant to use the approach as I don't (yet)
understand it well enough. Also, I'm using the Rails framework and I
don't think it would play well with too much intelligence in the DB.

The consequences are that for my current project I'll do the change
logging at application level. However, this doesn't preclude learning
more about programming PgSQL and possibly writing an extension for
Rails latter on.

Regarding the former, learning about programming on the DB server, my
impression is that documentation is scarce. There is reference
documentation, of course, and the book by Douglas & Douglas devotes a
few chapters to the topic. Still, coverage appears pretty introductory.
In comparison, there's a wealth of books on everything above the
database: languages, design and architecture, frameworks. Literature on
database-server programming seems to be tied to specific products,
mostly Oracle. I'd like to be wrong, of course. What are good ways to
learn about these things in a free database world?

Michael

--
Michael Schuerig Failures to use one's frontal lobes
mailto:michael(at)schuerig(dot)de can result in the loss of them.
http://www.schuerig.de/michael/ --William H. Calvin