Re: creating audit tables

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(at)cshl(dot)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(at)cshl(dot)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(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory