Log Table Changes in PostgreSQL with tablelog

Andreas 'ads' Scherbaum

Ever had the need to know what happened with your data? Tablelog could be an answer.

The usage is easy: you create a second table which has the same format like your table you want to keep an eye on. Plus you need some additional columns to maintain the logged data.

But step by step:

First thing: you need the software. Get it from pgfoundry: http://pgfoundry.org/projects/tablelog/

Unpack the archive, join the table_log-version directory and type in:
"make -f Makefile.nocontrib".
This should work without problems. If you get an error about missing include files, please install the PostgreSQL development packages from your distribution.

If compilation was done successfully, install the library. You have to be a superuser to do that, so first become root with "su". Type in: "make -f Makefile.nocontrib install".
This will install the library into the PostgreSQL library directory.

Now we have to tell your database that we want to use tablelog, so make sure, you have support for plpgsql enabled (with createlang). Connect as a superuser to the database which contains your tables and execute the following commands (you can find them in the file table_log.sql):

-- create function
CREATE FUNCTION table_log ()
    RETURNS TRIGGER
    AS '$libdir/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';

After this you may add some supporting functions which will make life more easy, you can find them in the file table_log_init.sql. Just paste the content into your database shell or include it using the \i command in psql.

Thats all, now you can use tablelog in your database.


Here's an example (taken from table_log.sql):

-- create demo table
CREATE TABLE test (
  id                    INT                 NOT NULL
                                            PRIMARY KEY,
  name                  VARCHAR(20)         NOT NULL
);

-- create the table without data from demo table
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');

-- create trigger
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
               EXECUTE PROCEDURE table_log();

-- test trigger
INSERT INTO test VALUES (1, 'name');
SELECT * FROM test;
SELECT * FROM test_log;
UPDATE test SET name='other name' WHERE id=1;
SELECT * FROM test;
SELECT * FROM test_log;

You will see that the test_log table contains all changes which happened to the original table. If you want to avoid the overhead for creating the logtable, take a look into the support functions created by table_log_init.sql.

Ok, but what use would be the logging data if we have no good chance of using it?
So the next example will show you, how you can take a look into the state of your table at any time
in the past:

-- create restore table
SELECT table_log_restore_table('test', 'id', 'test_log', 'test_log_id', 'test_recover', NOW());
SELECT * FROM test_recover;

The meaning of the arguments, from left to right: name of the original table, primary key of the original table, name of the logging table, primary key of the logging table, recovery table to create by table_log_restore_table(), timestamp for the lookup. Some more arguments are available, take a look into the README for a full description.

Have fun with your table history.

If you want to provide feedback, you will find contact infos at the end of the README file.