Re: Automatic auditing suggestion

On Thu, 30 Oct 2003, Scott Chapman wrote:

> On Thursday 30 October 2003 06:38, scott.marlowe wrote:
> > On Wed, 29 Oct 2003, Scott Chapman wrote:
> > > In my further discussion with Andrew offline, we came up with a joint
> > > suggestion to have PostgreSQL do automatic auditing. This would be VERY
> > > NICE, imho. Any input?
> > >
> > > Scott wrote:
> > > > It seems like it would be nice if you could flip a toggle on a
> > > > table and have it automatically build audit entries in another table.
> > >
> > > Andrew replied:
> > > > Yeah - that would be a great feature - automatic auditing...
> > > > Maybe you should post that to someone (whoever it would be?) at
> > > > PostgreSQL - sure, there would be major performance hit problems (maybe
> > > > rather than at table level, field/column level would be better), but it
> > > > would be a boon for many...
> >
> > I like the idea. It would be kinda nice to do:
> >
> > create table test (name text, id serial primary key)
> > with audit
> > (id keyid, 10 cycle,fifo|stop);
> >
> > and have an auditing table with a historical view of the table up to 10
> > deep per key, and either have it either fifo them so the ones older than
> > 10 disappear or have it stop inserts into the parent when the history gets
> > too deep.
> >
> > I'd guess the proof of concept could be done in plpgsql, with the with
> > audit part programmed as a before trigger.
>
> I wouldn't limit it to 10 layers deep. That should be all user configurable.
> Some implementations would need full history audits, etc. My skill with
> triggers and plpgsql is not up to this task currently, but this is a
> suggestion for the PostgreSQL developers.

No, I wouldn't either, that's why there was a cycle var, that set it to
that. I.e.

create table test (name text, id serial primary key)
with audit
(id keyid, 0 cycle)

would set it to infinite.

create table test (name text, id serial primary key)
with audit
(1000 cycle,stop)

would automagically pick the pk but stop after 1,000 versions of the same
row were stored...

I might play with some of this as a plpgsql function if I get a few free
minutes.