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


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, Ph. D. cain (AT) cshl (DOT) org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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:
------------------------------------------------------------------------
Scott Cain, Ph. D. cain (AT) cshl (DOT) 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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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

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:
------------------------------------------------------------------------
Scott Cain, Ph. D. cain (AT) cshl (DOT) 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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Have you thought about unifying the audit + the current table and add
from/to datestamps for every record?

Example:

from_dt to_dt value
9/1/2004 9/30/2004 ABC
9/30/2004 10/5/2004 XYZ
10/6/2004 12/31/9999 123

This would let you use the following query on the same table whether you
wanted historic values or current values.

SELECT * FROM table WHERE from_dt >= as_of_date AND to_dt <= as_of_date





Scott Cain wrote:

Oh yes. I do that a lot for attributes that need a history (last name,
which changes when you get married, etc) It is a bit more complicated
for queries though, since I use null to indicate an unknown end date
instead of the Y2K problem solution below.

-Ian

from/to datestamps for every record?

Example:

from_dt to_dt value
9/1/2004 9/30/2004 ABC
9/30/2004 10/5/2004 XYZ
10/6/2004 12/31/9999 123

This would let you use the following query on the same table whether you

wanted historic values or current values.

SELECT * FROM table WHERE from_dt >= as_of_date AND to_dt <= as_of_date





Scott Cain wrote:

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

12/31/9999 would be Y10K problem.

Dunno about you but I sorta don't care if somebody has to rewrite my app
8000 years from now.



Ian Harding wrote: