Thread: history tables with only one function?

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
tablecan 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? 


-Another question I have is for those who use this approach: How often o you have to flush those history tables
and when you flush the tables, where do you put the flushed data? In another database on another server or on tape?

-Would it be possible to use the replication of Slony-I and only log in the history tables in the slave database? Or is
somthing similar possible?

Thanks.

Raph

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



>
> -Another question I have is for those who use this approach: How often o
> you have to flush those history tables
> and when you flush the tables, where do you put the flushed data? In
> another database on another server or on tape?
>
> -Would it be possible to use the replication of Slony-I and only log in
> the history tables in the slave database? Or is
> somthing similar possible?
> Thanks.
>
> Raph
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org

-----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.




> 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-----
>

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....
Andthat 
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



>
>             regards, tom lane