creating audit tables

Scott Cain <cain@cshl.org> writes:

I am trying to create audit tables for all of the tables in my
database. The function, table and trigger create statements are below.
Apparently, I am not doing it quite right, because I get these messages
when I try to run the create statements below:

Trigger functions don't take any explicit parameters. Everything they
need they get through specialized mechanisms (in plpgsql, it's special
variables like tgargv).

regards, tom lane

OK, I've reworked my function and I can now create my functions and
triggers; however, when I try to do a test update, I get the following
error:

ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement

Which I think corresponds to 'audit_table' in the INSERT line below:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
table_name text;
BEGIN
table_name = TG_RELNAME;
audit_table = ''audit_'' || table_name;
INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM table_name);
return NEW;
END
'
LANGUAGE plpgsql;

I am trying to dynamically construct the audit table's name from the
TG_RELNAME variable (the audit table is always named as the name of the
original table with 'audit_' prepended to it). Is this not a valid
thing to do?

Thanks,
Scott

On Wed, 2004-10-13 at 23:59, Tom Lane wrote:

Scott Cain <cain@cshl.org> writes:

I am trying to create audit tables for all of the tables in my
database. The function, table and trigger create statements are below.
Apparently, I am not doing it quite right, because I get these messages
when I try to run the create statements below:

Trigger functions don't take any explicit parameters. Everything they
need they get through specialized mechanisms (in plpgsql, it's special
variables like tgargv).

regards, tom lane

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

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

I think you want to EXECUTE that sql so it doesn't get compiled into the
function.

http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

- Ian

Scott Cain <cain@cshl.org> 10/14/04 8:01 AM >>>

OK, I've reworked my function and I can now create my functions and
triggers; however, when I try to do a test update, I get the following
error:

ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement

Which I think corresponds to 'audit_table' in the INSERT line below:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
table_name text;
BEGIN
table_name = TG_RELNAME;
audit_table = ''audit_'' || table_name;
INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM
table_name);
return NEW;
END
'
LANGUAGE plpgsql;

I am trying to dynamically construct the audit table's name from the
TG_RELNAME variable (the audit table is always named as the name of the
original table with 'audit_' prepended to it). Is this not a valid
thing to do?

Thanks,
Scott

On Wed, 2004-10-13 at 23:59, Tom Lane wrote:

Scott Cain <cain@cshl.org> writes:

I am trying to create audit tables for all of the tables in my
database. The function, table and trigger create statements are

below.

Apparently, I am not doing it quite right, because I get these

messages

when I try to run the create statements below:

Trigger functions don't take any explicit parameters. Everything they
need they get through specialized mechanisms (in plpgsql, it's special
variables like tgargv).

regards, tom lane

---------------------------(end of

broadcast)---------------------------

TIP 4: Don't 'kill -9' the postmaster

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

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

I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:

ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement

It seems that I am not able to use OLD in this context, but that is
exactly what I need to do, to get the contents of the old row in the
original table to put it in the audit table. Here is the function now:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
BEGIN
audit_table = ''audit_''||TG_RELNAME;
EXECUTE ''INSERT INTO ''
||quote_ident(audit_table)
||'' VALUES (''
||OLD.*
||'',''
||now()
||'',''''U'''')'';
return NEW;
END
'
LANGUAGE plpgsql;

Thanks again,
Scott

On Thu, 2004-10-14 at 11:16, Ian Harding wrote:

I think you want to EXECUTE that sql so it doesn't get compiled into the
function.

http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

- Ian

Scott Cain <cain@cshl.org> 10/14/04 8:01 AM >>>

OK, I've reworked my function and I can now create my functions and
triggers; however, when I try to do a test update, I get the following
error:

ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement

Which I think corresponds to 'audit_table' in the INSERT line below:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
table_name text;
BEGIN
table_name = TG_RELNAME;
audit_table = ''audit_'' || table_name;
INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM
table_name);
return NEW;
END
'
LANGUAGE plpgsql;

I am trying to dynamically construct the audit table's name from the
TG_RELNAME variable (the audit table is always named as the name of the
original table with 'audit_' prepended to it). Is this not a valid
thing to do?

Thanks,
Scott

On Wed, 2004-10-13 at 23:59, Tom Lane wrote:

Scott Cain <cain@cshl.org> writes:

I am trying to create audit tables for all of the tables in my
database. The function, table and trigger create statements are

below.

Apparently, I am not doing it quite right, because I get these

messages

when I try to run the create statements below:

Trigger functions don't take any explicit parameters. Everything they
need they get through specialized mechanisms (in plpgsql, it's special
variables like tgargv).

regards, tom lane

---------------------------(end of

broadcast)---------------------------

TIP 4: Don't 'kill -9' the postmaster

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

Scott Cain wrote:

I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:

ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement

It seems that I am not able to use OLD in this context, but that is
exactly what I need to do, to get the contents of the old row in the
original table to put it in the audit table. Here is the function now:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
BEGIN
audit_table = ''audit_''||TG_RELNAME;
EXECUTE ''INSERT INTO ''
||quote_ident(audit_table)
||'' VALUES (''
||OLD.*
||'',''
||now()
||'',''''U'''')'';
return NEW;
END
'
LANGUAGE plpgsql;

Looks like people were fixing your errors, not looking at what you were
trying to do. Apologies, but it's easy to fixate on an error message.

Unless something is changing in 8.0 you're using the wrong tool for the
job here. Plpgsql isn't good at dynamic queries, and can't unwrap OLD
for you. Try a different language - tcl would be an obvious choice.

--
Richard Huxton
Archonet Ltd

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. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how to do it in tkl.

If someone would show me a simple example for doing this for one table,
I will happily make available the script I am writing that will generate
audit tables and the functions and triggers for using them
automatically, given any ddl file. It is based on the Perl module
SQL::Translator.

Thanks,
Scott

On Thu, 2004-10-14 at 14:07, Richard Huxton wrote:

Scott Cain wrote:

I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:

ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement

It seems that I am not able to use OLD in this context, but that is
exactly what I need to do, to get the contents of the old row in the
original table to put it in the audit table. Here is the function now:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
BEGIN
audit_table = ''audit_''||TG_RELNAME;
EXECUTE ''INSERT INTO ''
||quote_ident(audit_table)
||'' VALUES (''
||OLD.*
||'',''
||now()
||'',''''U'''')'';
return NEW;
END
'
LANGUAGE plpgsql;

Looks like people were fixing your errors, not looking at what you were
trying to do. Apologies, but it's easy to fixate on an error message.

Unless something is changing in 8.0 you're using the wrong tool for the
job here. Plpgsql isn't good at dynamic queries, and can't unwrap OLD
for you. Try a different language - tcl would be an obvious choice.

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

Here's what I do... It's not pretty but it works.

create table auditlog (
auditwhen timestamp not null default CURRENT_TIMESTAMP,
auditwhat char(10) not null,
audittable varchar not null,
auditkeyval int not null,
auditfield varchar not null,
oldval text null,
newval text null);

CREATE OR REPLACE FUNCTION "tsp_audit_atrig" () RETURNS trigger AS '

if {[string match $TG_op INSERT]} {
foreach field $TG_relatts {
if {[info exists NEW($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''INSERT'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op DELETE]} {
foreach field $TG_relatts {
if {[info exists OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''DELETE'', ''$1'', ''$OLD($2)'',
''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op UPDATE]} {
foreach field $TG_relatts {
# Was data changed or is this the key field?

if {([info exists NEW($field)] &&
[info exists OLD($field)] &&
![string match $OLD($field) $NEW($field)])} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$OLD($field)'', ''$NEW($field)'')"
spi_exec "$sql"

# Is this a field replacing a null?

} elseif {[info exists NEW($field)] && ![info exists
OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"

# Is this a field being replaced with null?

} elseif {![info exists NEW($field)] && [info exists
OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"

}
}
}

return "OK"

' LANGUAGE 'pltcl';

drop trigger trig_timecardaudit_atrig on timecard;
CREATE TRIGGER "trig_timecardaudit_atrig" AFTER INSERT OR DELETE OR
UPDATE ON "timec
ard" FOR EACH ROW EXECUTE PROCEDURE "tsp_audit_atrig" ('timecard',
'timecardid');

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

Scott Cain <cain@cshl.org> 10/14/04 11:19 AM >>>

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. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how to do it in tkl.

If someone would show me a simple example for doing this for one table,
I will happily make available the script I am writing that will generate
audit tables and the functions and triggers for using them
automatically, given any ddl file. It is based on the Perl module
SQL::Translator.

Thanks,
Scott

On Thu, 2004-10-14 at 14:07, Richard Huxton wrote:

Scott Cain wrote:

I feel like I am getting very close, but I am still not quite there.

I

rewrote the trigger function below to use execute, but now I get the
following error:

ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute

statement

It seems that I am not able to use OLD in this context, but that is
exactly what I need to do, to get the contents of the old row in the
original table to put it in the audit table. Here is the function

now:

CREATE FUNCTION audit_update() RETURNS trigger
AS '
DECLARE
audit_table text;
BEGIN
audit_table = ''audit_''||TG_RELNAME;
EXECUTE ''INSERT INTO ''
||quote_ident(audit_table)
||'' VALUES (''
||OLD.*
||'',''
||now()
||'',''''U'''')'';
return NEW;
END
'
LANGUAGE plpgsql;

Looks like people were fixing your errors, not looking at what you

were

trying to do. Apologies, but it's easy to fixate on an error message.

Unless something is changing in 8.0 you're using the wrong tool for

the

job here. Plpgsql isn't good at dynamic queries, and can't unwrap OLD
for you. Try a different language - tcl would be an obvious choice.

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

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

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

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

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

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

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

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:

Show quoted text

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

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

William Yu <wyu@talisys.com> 10/15/04 12:46 PM >>>

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:

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

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

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:

Show quoted text

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

William Yu <wyu@talisys.com> 10/15/04 12:46 PM >>>

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