history tables with only one function?

Raphael Bauduin wrote:

Hi,

I'm looking at the logging of a database we'll put in production soon.
I've seen some posts on this list about history tables, like mentioned
in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html .
I think I'll go that way too, but I still have some questions on the
approach, and would appreciate any advice on it.

Here are some questions I have:

- is it possible to write only one function used for all logging
triggers? As illustrated in
http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use
one function for tracking last update times for all tables:

CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
BEGIN
NEW.modified_timestamp = now();
RETURN NEW;
END
' LANGUAGE 'plpgsql';

Is it possible to create only one function to insert rows in the
corresponding history table? The name of the history table can be
constructed from the original table.
and I guess all fields of the table can be retrieved from the db's
metadata. Would that be feasible, and more importantly, would it be usable?

I found a solution to this one, thanks to a post of Tom Lane on the postgres-novice mailing list:

CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
BEGIN
insert into $1_log select new.*,''UPDATE'';
return new;
END;
' LANGUAGE 'plpgsql';

I can then create a trigger and pass the table name as argument (does the function know
which table fired the trigger?):

CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');

This creates entries in the customers_log table each time I update a customer.

I'll see if I use the same function for the creation log or not. For the delete log, I'll have to use another function anyway,
as new is not defined for a deletion IIRC.

Still interested in tips on this technique ;-)

Raph

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Raphael Bauduin wrote:

Raphael Bauduin wrote:

Hi,

I'm looking at the logging of a database we'll put in production soon.
I've seen some posts on this list about history tables, like mentioned
in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html .
I think I'll go that way too, but I still have some questions on the
approach, and would appreciate any advice on it.

Here are some questions I have:

- is it possible to write only one function used for all logging
triggers? As illustrated in
http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use
one function for tracking last update times for all tables:

CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
BEGIN
NEW.modified_timestamp = now();
RETURN NEW;
END
' LANGUAGE 'plpgsql';

Is it possible to create only one function to insert rows in the
corresponding history table? The name of the history table can be
constructed from the original table.
and I guess all fields of the table can be retrieved from the db's
metadata. Would that be feasible, and more importantly, would it be
usable?

I found a solution to this one, thanks to a post of Tom Lane on the
postgres-novice mailing list:

CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
BEGIN
insert into $1_log select new.*,''UPDATE'';
return new;
END;
' LANGUAGE 'plpgsql';

I can then create a trigger and pass the table name as argument (does
the function know
which table fired the trigger?):

CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for
each row execute procedure "customers_update_log"('customers');

This creates entries in the customers_log table each time I update a
customer.

And this construct indeed works? I'm stunned!
Which PostgreSQL version is this?

As far as I know your function should have the following problems:

*) Trigger functions can not be declared with arguments in the
CREATE FUNCTION statement. They can have arguments when they
are used in the CREATE TRIGGER statement, but trigger functions
have to read the values of their arguments from the array TG_ARGV[]

*) You can not use variables as a placeholder for table- or column-
names in SQL statements. You would have to create the SQL statement
dynamically and execute it in your function with EXECUTE

IMHO this is true at least for PostgreSQL 7.4

See the thread "Trigger functions with dynamic SQL" on pgsql-sql
around July 24th where I described my problems with dynamically
created SQL statements. Finally I got around all the hassels with
quotation marks and my trigger functions work as expected.

Could you please confirm that your function works as you described?
I tried with similar functions and the failed with syntax errors,
so I had to use dynamically created SQL statements.

- - andreas

- --
Andreas Haumer | mailto:andreas@xss.co.at
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBGdE4xJmyeGcXPhERAsbZAJ4rS3E8ng3D/Hx/ywsxKM5CVjRd3ACfcdwi
Dt5vUZsSVPbjDfjTMte/MzY=
=RAJ4
-----END PGP SIGNATURE-----

Andreas Haumer wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Raphael Bauduin wrote:

Raphael Bauduin wrote:

Hi,

I'm looking at the logging of a database we'll put in production soon.
I've seen some posts on this list about history tables, like mentioned
in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html .
I think I'll go that way too, but I still have some questions on the
approach, and would appreciate any advice on it.

Here are some questions I have:

- is it possible to write only one function used for all logging
triggers? As illustrated in
http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use
one function for tracking last update times for all tables:

CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
BEGIN
NEW.modified_timestamp = now();
RETURN NEW;
END
' LANGUAGE 'plpgsql';

Is it possible to create only one function to insert rows in the
corresponding history table? The name of the history table can be
constructed from the original table.
and I guess all fields of the table can be retrieved from the db's
metadata. Would that be feasible, and more importantly, would it be
usable?

I found a solution to this one, thanks to a post of Tom Lane on the
postgres-novice mailing list:

CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
BEGIN
insert into $1_log select new.*,''UPDATE'';
return new;
END;
' LANGUAGE 'plpgsql';

I can then create a trigger and pass the table name as argument (does
the function know
which table fired the trigger?):

CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for
each row execute procedure "customers_update_log"('customers');

This creates entries in the customers_log table each time I update a
customer.

And this construct indeed works? I'm stunned!
Which PostgreSQL version is this?

As far as I know your function should have the following problems:

*) Trigger functions can not be declared with arguments in the
CREATE FUNCTION statement. They can have arguments when they
are used in the CREATE TRIGGER statement, but trigger functions
have to read the values of their arguments from the array TG_ARGV[]

*) You can not use variables as a placeholder for table- or column-
names in SQL statements. You would have to create the SQL statement
dynamically and execute it in your function with EXECUTE

IMHO this is true at least for PostgreSQL 7.4

See the thread "Trigger functions with dynamic SQL" on pgsql-sql
around July 24th where I described my problems with dynamically
created SQL statements. Finally I got around all the hassels with
quotation marks and my trigger functions work as expected.

Could you please confirm that your function works as you described?

It works as expected:

log=# DROP TRIGGER "customers_update_log_t" on "customers";
DROP TRIGGER
log=# DROP FUNCTION "update_log"();
ERROR: function update_log() does not exist
log=# CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
log'# BEGIN
log'# insert into $1_log select new.*,''UPDATE'';
log'# return new;
log'# END;
log'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
log=#
log=# CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');
CREATE TRIGGER
log=# select count(*) from customers_log;
count
-------
18
(1 row)

log=# update customers set name='EDITED AND LOOGED GENERIC FUNCTION NEW' where customer_id=20003;
UPDATE 1
log=# select count(*) from customers_log;
count
-------
19
(1 row)

And the row added to customers_log is absolutely correct. version is 7.4.3 (debian package)

Raph

PS: I also have a problem of quoting in a trigger (as you seem to have had from the pgsql-sql thread you refered to).
I want this to be executed:
EXECUTE ''insert into ''|| TG_RELNAME||''_log select ''||new.*||'',''||TG_OP;
but I get this output:
NOTICE: table = customers
NOTICE: operation = UPDATE
ERROR: NEW used in query that is not in a rule
CONTEXT: PL/pgSQL function "activity_log" line 4 at execute statement

I posted a message to pgsql-novice, but maybe you can help me forward also?
Thanks.

Raphael Bauduin <raphael.bauduin@be.easynet.net> writes:

Could you please confirm that your function works as you described?

It works as expected:

log=# DROP TRIGGER "customers_update_log_t" on "customers";
DROP TRIGGER
log=# DROP FUNCTION "update_log"();
ERROR: function update_log() does not exist
log=# CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
log'# BEGIN
log'# insert into $1_log select new.*,''UPDATE'';
log'# return new;
log'# END;
log'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
log=#
log=# CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');
CREATE TRIGGER

That trigger is not executing that function, and would not be executing
it even if you had used the same function name. Trigger functions are
always parameterless.

regards, tom lane

Tom Lane wrote:

Raphael Bauduin <raphael.bauduin@be.easynet.net> writes:

Could you please confirm that your function works as you described?

It works as expected:

log=# DROP TRIGGER "customers_update_log_t" on "customers";
DROP TRIGGER
log=# DROP FUNCTION "update_log"();
ERROR: function update_log() does not exist
log=# CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
log'# BEGIN
log'# insert into $1_log select new.*,''UPDATE'';
log'# return new;
log'# END;
log'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
log=#
log=# CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');
CREATE TRIGGER

That trigger is not executing that function, and would not be executing
it even if you had used the same function name. Trigger functions are
always parameterless.

I tested it again and it worked, but the fact you were as categoric made me start from scratch, and you are absolutely right.
As you can even see in the code above, the trigger uses "customers_update_log"('customers') but I define the function "update_log"().....
There was a parameterless function customers_update_log I had defined in previous tests which hadn't been dropped.... And that
was the function used by the trigger, even if I passed a parameter. The fact that it worked made me think it used the parameter.

Thanks for the clarification!

Raph