table_log for PostgreSQL

Table of contents:

1. Info
2. License
3. Installation
4. Documentation 4.1. Manual table log and trigger creation 4.2. Restore table data
5. Hints 5.1. Security tips
6. Bugs
7. Todo
8. Changes
9. Contact

1. Info

table_log is a set of functions to log changes on a table in PostgreSQL and to restore the state of the table or a specific row on any time in the past.

For now it contains 2 functions:

• table_log() -- log changes to another table
• table_log_restore_table() -- restore a table or a specific column

NOTE: you can only restore a table where the original table and the logging table has a primary key!

This means: you can log everything, but for the restore function you must have a primary key on the original table (and of course a different pkey on the log table).

In the beginning (for table_log()) i have used some code from noup.c (you will find this in the contrib directory), but there should be no code left from noup, since i rewrote everything during the development. In fact, it makes no difference since both software is licensed with the BSD style licence which is used by PostgreSQL.

2. License

Copyright (c) 2002-2007 Andreas Scherbaum

Basically it's the same as the PostgreSQL license (BSD license).

Permission to use, copy, modify, and distribute this software and its documentation for any purpose and without fee is hereby granted, provided that the above copyright notice appear in all copies and that both that copyright notice and this permission notice appear in supporting documentation, and that the name of the author not be used in advertising or publicity pertaining to distribution of the software without specific, written prior permission. The author makes no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

3. Installation

Build table_log:

make USE_PGXS=1
(and as root)
make USE_PGXS=1 install

If you are using a version >= 9.1 of PostgreSQL, you can use the new regression tests to verify the working state of the module:

USE_PGXS=1 make installcheck

Since the regression checks require the extension infrastructure, this won't work on version below 9.1.

3.1 Pre-9.1 installation procedure

The pg_config tool must be in your $PATH for installation and you must have the PostgreSQL development packages installed. Of course, the usual development tools like make, gcc ect. should also be there ;-)

After this you have to create some new functions:

• in every database you want to use this functions
• if you add this functions to template1, they will be copied to every new database
• for older pg versions <= 7.2 change "RETURNS trigger" to "RETURNS opaque", but versions < 7.4 are no longer supported

CREATE FUNCTION "table_log_basic" ()
    RETURNS trigger
    AS '$libdir/table_log', 'table_log' LANGUAGE 'C';
CREATE FUNCTION "table_log" ()
    RETURNS trigger
    AS '$libdir/table_log', 'table_log' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';

There's also a script available in PATH_TO_YOUR_PGSQL/share/contrib, which contains all required function definitions plus some regression tests, for example:

psql -f PATH_TO_YOUR_PGSQL/share/contrib/table_log.sql

Install table_log_init() by running psql < table_log_init.sql. This function does all the work which is necessary for creating a logging table.

NOTE:

Currently the Makefile doesn't distinguish between extension and contrib installation procedures and will install table_log--x.x.sql and control files in pre-9.1 versions nevertheless. This might get solved in the future, but don't use those scripts in pre-9.1 installations.

3.2 Installation procedure with versions >= 9.1

Starting with PostgreSQL 9.1 you should use the new extension infrastructure. table_log supports this starting with version 0.5. After compiling and installing the module, all you need to do is to connect to the target database and issue the following command:

CREATE EXTENSION table_log;

This will install all functions including table_log_init() into the public schema.

NOTE: The old contrib scripts are going to be installed, still. We currently don't have distinguished the installation procedure version-wise within the Makefile, so don't get confused if you still find the old table_log.sql scripts in share/extension directories.

3.3 Upgrading from earlier installations to the new extension infrastructure

If you are upgrading from an earlier pre-9.1 installation, and you want to use the new extension infrastructure, you could use the unpackaged creation procedure to migrate an existing table_log installation within a database into an extension. To accomplish this, you need to execute the following command in the new 9.1+ database (of course, after installing the new version of table_log):

CREATE EXTENSION table_log FROM unpackaged;

4. Documentation

The entire log table and trigger creation can be done using the table_log_init(ncols, ...) function. The parameter ncols decides how many extra columns will be added to the created log table, it can be 3, 4 or 5. The extra columns are described in chapter 4.1.

The function can be used with the following parameters:

table_log_init(ncols, tablename): create the log table as tablename_log

table_log_init(ncols, tablename, logschema): create the log table with the same name as the original table, but in the schema logschema.

table_log_init(ncols, tableschema, tablename, logschema, logname): log the changes in table tableschema.tablename into the log table logschema.logname.

table_log_init(ncols, tableschema, tablename, logschema, logname, partition_mode, basic_mode, log_actions): log the changes in table tableschema.tablename into the log table logschema.logname. The parameter partition_mode can be SINGLE or PARTITION, which creates two log tables *_0 and *_1 which can be switched by setting table_log.active_partition to the corresponding partition id 1 or 2. basic_mode defines wether we use full logging mode or basic mode. When set to TRUE, table_log_basic() will be used internally which suppresses logging of NEW values by UPDATE actions. log_actions is a TEXT[] array which specifies a list of either INSERT, DELETE or UPDATE or any combinations from them to tell when a table_log trigger should be fired.

NOTE:

When using basic_mode and/or log_actions without all actions, you won't be
able to use table_log_restore_table() anymore.

When calling table_log_init(), you can omit logname in any case. The function
will then generate a log tablename from the given source tablename and a
`_log` string appended.

4.1. Manual table log and trigger creation

Create an trigger on a table with log table name as argument. If no table name is given, the actual table name plus _log will be used by table_log.

Example:

CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test_table FOR EACH ROW
               EXECUTE PROCEDURE table_log();
^^^^^ 'test_table_log' will be used to log changes

CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test_table FOR EACH ROW
               EXECUTE PROCEDURE table_log('log_table');
^^^^^ 'log_table' will be used to log changes

The log table needs exact the same columns as the original table (but without any constraints) plus three, four or five extra columns:

trigger_mode VARCHAR(10)
trigger_tuple VARCHAR(5)
trigger_changed TIMESTAMPTZ
trigger_user VARCHAR(32)     -- optional

trigger_mode contains 'INSERT', 'UPDATE' or 'DELETE'
trigger_tuple contains 'old' or 'new'
trigger_changed is the actual timestamp inside the trancaction
   (or maybe i should use here the actual system timestamp?)
trigger_user contains the session user name (the one who connected to the
   database, this must not be the actual one)

On INSERT, a log entry with the 'new' tuple will be written. On DELETE, a log entry with the 'old' tuple will be written. On UPDATE, a log entry with the old tuple and a log entry with the new tuple will be written.

A fourth column is possible on the log table: trigger_id BIGINT contains an unique id for sorting table log entries

NOTE: for the restore function you must have this 4. column!

Q: Why do i need this column? A: Because we have to sort the logs to get them back in correct order. Hint: if you are sure, you don't have an OID wrapover yet, you can use the OID column as unique id (but if you have an OID wrapover later, the new OIDs doesn't follow a linear scheme, see VACUUM documentation)

A fifth column is possible on the log table: trigger_user VARCHAR(32) contains the username from the user who originally opened the database connection

Q: Why not the actual user? A: If someone changed the actual user with a setuid function, you always know the original username

Q: Why 32 bytes? A: The function doesn't uses 32 bytes but instead NAMEDATALEN defined at compile time (which defaults to 32 bytes)

Q: May i skip the log table name? A: No, because Pg then thinks, the '1' is the first parameter and will fail to use '1' as logging table This is an backwards compatibility issue, sorry for this.

For backward compatibility table_log() works with 3, 4 or 5 extra columns, but you should use the 4 or 5 column version everytimes.

A good method to create the log table is to use the existing table:

-- create the table without data
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
-- now activate the history function
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
               EXECUTE PROCEDURE table_log();

-- or the 4 column method:
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
CREATE SEQUENCE test_log_id;
SELECT SETVAL('test_log_id', 1, FALSE);
ALTER TABLE test_log ALTER COLUMN trigger_id SET DEFAULT NEXTVAL('test_log_id');
-- now activate the history function
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
               EXECUTE PROCEDURE table_log();

-- or the 5 column method (with user name in 'trigger_user'):
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
ALTER TABLE test_log ADD COLUMN trigger_user VARCHAR(32);
ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
CREATE SEQUENCE test_log_id;
SELECT SETVAL('test_log_id', 1, FALSE);
ALTER TABLE test_log ALTER COLUMN trigger_id SET DEFAULT NEXTVAL('test_log_id');
-- now activate the history function
-- you have to give the log table name!
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
               EXECUTE PROCEDURE table_log('test_log', 1);

See table_log.sql for a demo

4.2. Restore table data

Now insert, update and delete some data in table 'test'. After this, you may want to restore your table data:

SELECT table_log_restore_table(<original table name>,
                               <original table primary key>,
                               <log table name>,
                               <log table primary key>,
                               <restore table name>,
                               <timestamp>,
                               <primary key to restore>,
                               <restore method: 0/1>,
                               <dont create temporary table: 0/1>);

The parameter list means:

• original table name: string The name of the original table (test in your example above)
• original table primary key: string The primary key name of the original table
• log table name: string The name of the logging table
• log table primary key: string The primary key of the logging table (trigger_id in your example above) Note: this cannot be the same as the original table pkey!
• restore table name: string The name for the restore table Note: this table must not exist! Also see
• timestamp: timestamp The timestamp in past Note: if you give a timestamp where no logging data exists, absolutly nothing will happen. But see
• primary key to restore: string (or NULL) If you want to restore only a single primary key, name it here. Then only data for this pkey will be searched and restored Note: this parameter is optional and defaults to NULL (restore all pkeys) you can say NULL here, if you want to skip this parameter
• restore method: 0/1 (or NULL) 0 means: first create the restore table and then restore forward from the beginning of the log table 1 means: first create the log table and copy the actual content of the original table into the log table, then restore backwards Note: this can speed up things, if you know, that your timestamp point is near the end or the beginning Note: this parameter is optional and defaults to NULL (= 0)
• dont create temporary table: 0/1 (or NULL) Normal the restore table will be created temporarly, this means, the table is only available inside your session and will be deleted, if your session (session means connection, not transaction) is closed This parameter allows you to create a normal table instead Note: if you want to use the restore function sometimes inside a session and you want to use the same restore table name again, you have to drop the restore table or the restore function will blame you Note: this parameter is optional and defaults to NULL (= 0)

5. Hints

• an index on the log table primary key (trigger_id) and the trigger_changed column will speed up things
• You can find another nice explanation in my blog: http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html

5.1. Security Tips

• You can create the table_log functions with 'SECURITY DEFINER' privileges and revoke the permissions for the logging table for any normal user. This allows you to have an audit which cannot be modified by users who are accessing the data table.
• Existing functions can be modified with: ALTER FUNCTION table_log() SECURITY DEFINER;

6. Bugs

• nothing known, but tell me, if you find one

7. Todo

• table_log_show_column() allows select of previous state (possible with PostgreSQL 7.3 and higher) see Table Function API
• is it binary safe? (\000)
• do not only check the number columns in both tables, really check the names of the columns

8. Changes:

• 2002-04-06: Andreas 'ads' Scherbaum (ads@ufp.de) first release

• 2002-04-25: Steve Head (smhf@onthe.net.au) there was a bug with NULL values, thanks to Steve Head for reporting this.

• 2002-04-25: Andreas 'ads' Scherbaum (ads@ufp.de) now using version numbers (0.0.5 for this release)

• 2002-09-09: Andreas 'ads' Scherbaum (ads@ufp.de) fix bug in calculating log table name release 0.0.6

• 2003-03-22: Andreas 'ads' Scherbaum (ads@ufp.de) fix some error messages (old name from 'noup' renamed to 'table_log') one additional check that the trigger is fired after release 0.0.7

• 2003-03-23: Andreas 'ads' Scherbaum (ads@ufp.de) create a second Makefile for installing from source release 0.1.0

• 2003-04-20: Andreas 'ads' Scherbaum (ads@ufp.de) change Makefile.nocontrib to Linux only and make a comment about installation informations for other platforms (its too difficult to have all install options here, i dont have the ability to test all platforms)

• 2003-06-12: Andreas 'ads' Scherbaum (ads@ufp.de) update documentation (thanks to Erik Thiele erik@thiele-hydraulik.de for pointing this out)

• 2003-06-13: Andreas 'ads' Scherbaum (ads@ufp.de)

  • release 0.2.0 now allow 3 or 4 columns update documentation about trigger_id column

• 2003-06-13: Andreas 'ads' Scherbaum (ads@ufp.de)

  • release 0.2.1 add debugging (activate TABLE_LOG_DEBUG in head of table_log.c and recompile)

• 2003-11-27: Andreas 'ads' Scherbaum (ads@ufp.de)

  • release 0.3.0 add function for restoring table from log cleanup source add more debugging

• 2003-12-11: Andreas 'ads' Scherbaum (ads@ufp.de)

  • release 0.3.1 add session_user to log table on request thanks to iago@patela.org.uk for the feature request fix a minor bug in returning the table name

• 2005-01-14: Andreas 'ads' Scherbaum (ads@wars-nicht.de)

  • release 0.4.0 ignore dropped columns on tables (this may cause errors, if you restore or use older backups) change email address, the old one does no longer work

• 2005-01-24: Andreas 'ads' Scherbaum (ads@wars-nicht.de)

  • release 0.4.1 there seems to be an problem with session_user

• 2005-04-22: Kim Hansen kimhanse@gmail.com

  • release 0.4.2 added table_log_init() added schema support

• 2006-08-30: Andreas 'ads' Scherbaum (ads@wars-nicht.de)

  • release 0.4.3 drop support for < 7.4 (return type is TRIGGER now) fix bug with dropped columns

• 2007-05-18: Andreas 'ads' Scherbaum (ads@pgug.de)

  • release 0.4.4 compatibility issues with 8.2.x some small fixes in table_log.sql.in remove some warnings docu cleanups Thanks to Michael Graziano mgraziano@invision.net for pointing out the 8.2 fix Thanks to Alexander Wirt formorer@formorer.de for Debian packaging Thanks to Devrim GÜNDÜZ devrim@CommandPrompt.com for RPM packaging

9. Contact

The project is hosted at http://pgfoundry.org/projects/tablelog/

If you have any hints, changes or improvements, please contact me.

my gpg key: pub 1024D/4813B5FE 2000-09-29 Andreas Scherbaum ads@wars-nicht.de Key fingerprint = 9F67 73D3 43AA B30E CA8F 56E5 3002 8D24 4813 B5FE