Re: creating audit tables - Mailing list pgsql-general

Ah, time travel.  I don't think it will be quite that easy since if
there was no modification of a record on that day, there will be no data
returned, and if there were several modifications on that day, there
will be several records returned.  I think you will need a correlated
subquery for each table looking for the max(<timestamp field>) where <=
<your search condition timestamp>.

There used to be a time travel module somewhere.

My audit is more interested in "What is the history of my timecard for
payperiod 7" which is very different that what you are doing.

Good luck!!

- Ian
>>> Scott Cain <cain@cshl.org> 10/15/04 11:53 AM >>>
Hi Ian,

I created one audit table for each table in the database just because
that seemed to me to be the sensible thing to do.  The reason we want
audit tables is so that we can ask the question: "what was the state of
the database 6 months ago" and the easiest way to answer that question
is with shadow tables where I can write the same queries I do now, just
changing (slightly) the table name and adding a date check to the where
clause.  Using a big, unified table makes it much harder to ask that
sort of question, unless you spend a fair amount of effort making views
to simulate the real audit tables I already have.  I don't see any
advantage to us in using a unified table.

Scott


On Fri, 2004-10-15 at 13:00, Ian Harding wrote:
> Hmm.  You have an audit_ table for each table that is audited.  I
chose
> to have one big ugly audit table for all audited tables.  I wonder
which
> is more flexible/useful.
>
> Right off the bat I can see that if you add or rename a column you
would
> need to add or rename a column in your audit_ table and re-produce the
> functions/triggers.  I guess dropped columns would just show nulls
from
> then on.  Column name changes lose history of the field name too.
>
> Queries are a PITA with my schema, I can see where they would be
easier
> with yours.  I can imagine a pivot function that would make life
easier
> with my schema though.
>
> Any thoughts would be appreciated, I might take a hack at this in C.
>
> - Ian
>
> >>> Scott Cain <cain@cshl.org> 10/15/04 8:27 AM >>>
> Hi Tom,
>
> You are probably right that the performance will become an issue.  I
do
> have a working solution using plpgsql, though, so I will at least try
it
> out for a while.
>
> For anyone who is interested, I created a template file (using the
perl
> module Template.pm syntax) that works with the perl module
> SQL::Translator to examine my ddl file and create from it the audit
> tables and the functions and triggers to make them work.  The template
> file copied below, and SQL::Translator is available from CPAN and from
> http://sqlfairy.sourceforge.net/ .
>
> Thanks,
> Scott
>
> ----------------------------------------------
> --audit tables generated from
> -- % sqlt -f PostgreSQL -t TTSchema --template add-audits.tmpl
> nofuncs.sql > \
> --        audits.sql
>

>

>
> [% FOREACH table IN schema.get_tables %]
>    DROP TABLE audit_[% table.name %];
>    CREATE TABLE audit_[% table.name %] ( [% FOREACH field IN
> table.get_fields %]
>        [% field.name %] [% IF field.data_type == 'serial'; 'int';
ELSE;
> field.data_type; END %][% IF field.size AND (field.data_type == 'char'
> OR field.data_type == 'varchar') %]([% field.size.join(', ') %])[% END
> %], [% END %]
>        transaction_date timestamp not null default now(),
>        transaction_type char(1) not null
>    );
>    GRANT ALL on audit_[% table.name %] to PUBLIC;
>

>

>
>    CREATE OR REPLACE FUNCTION audit_update_delete_[% table.name %]()
> RETURNS trigger AS
>    '
>    DECLARE
>        [% FOREACH field IN table.get_fields %][% field.name %]_var [%
IF
> field.data_type == 'serial'; 'int'; ELSE; field.data_type; END %][% IF
> field.size AND (field.data_type == 'char' OR field.data_type ==
> 'varchar') %]([% field.size.join(', ') %])[% END %];
>        [% END %]
>        transaction_type_var char;
>    BEGIN
>        [% FOREACH field IN table.get_fields %][% field.name %]_var =
> OLD.[% field.name %];
>        [% END %]
>        IF TG_OP = ''DELETE'' THEN
>            transaction_type_var = ''D'';
>        ELSE
>            transaction_type_var = ''U'';
>        END IF;
>

>

>
>        INSERT INTO audit_[% table.name %] ( [% FOREACH field IN
> table.get_fields %]
>              [% field.name %], [% END %]
>              transaction_type
>        ) VALUES ( [% FOREACH field IN table.get_fields %]
>              [% field.name %]_var, [% END %]
>              transaction_type_var
>        );
>

>

>
>        IF TG_OP = ''DELETE'' THEN
>            return null;
>        ELSE
>            return NEW;
>        END IF;
>    END
>    '
>    LANGUAGE plpgsql;
>

>

>
>    DROP TRIGGER [% table.name %]_audit_ud ON [% table.name %];
>    CREATE TRIGGER [% table.name %]_audit_ud
>        BEFORE UPDATE OR DELETE ON [% table.name %]
>        FOR EACH ROW
>        EXECUTE PROCEDURE audit_update_delete_[% table.name %] ();
>

>

>
> [% END %]
>
>
> On Fri, 2004-10-15 at 11:02, Tom Lane wrote:
> > Scott Cain <cain@cshl.org> writes:
> > > Heck!  So much for feeling close.  It is somewhat frustrating to
me
> that
> > > such an obviously useful tool (having and using audit tables)
should
> be
> > > so difficult to implement.
> >
> > The only really reasonable way to implement this is as a C function
> > anyway.  I think anything involving a PL language is going to be a
> huge
> > performance drag, if you intend to put it on essentially every
table.
> >
> > There are some pretty closely related examples in contrib/spi/,
though
> > I don't see anything that does *exactly* what you want.  If you came
> up
> > with something that does, I think it'd be reasonable to add it to
that
> > set of examples ...
> >
> >             regards, tom lane
--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain@cshl.org
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly