*** POSTGRESQL HISTORY TRACKER *** AUTHOR Ivan Mincik, Gista s.r.o., ivan.mincik@gista.sk ABOUT This project is set of functions which adds possibility to store full editing history of Your database tables, recover its state to any time, visualize diffs and place tags to mark particular table state. Inspiration was taken from versioning functions created by Horst Duester and Andreas Neumann, but implemented in other way. (http://www.kappasys.ch/pgtools/pghistory/index.html) LICENSE The GNU General Public License version 2 (GPLv2) or later PREREQUISITES All tables MUST contain primary key (currently only one-column primary key is supported). INSTALLATION Prerequisites: Assuming you have already working PostgreSQL database server. In addition PL/Python procedural language is required. Under Debian install package 'postgresql-plpython-<pg version>'. Preparing database: 1. Adding PL/PgSQL language support: 'createlang plpgsql <database>' 2. Adding PL/Python language support: 'createlang plpythonu <database>' 3. Init tracker: 'psql <database> -f init_schema.sql' 4. Loading functions: 'psql <database> -f install_tracker.sql' Compatibility: For using with PostgreSQL 8.3 and below load also 'compat/array_agg.sql' file to Your database. USAGE 1. Adding versioning support to table: 'SELECT HT_Init('<schema>', '<table>');' 2. Retrieving table state in particulal time: 'SELECT * FROM <schema>.<table>_AtTime('<timestamp>');' 3. Retrieving table changes to particulal time: 'SELECT * FROM <schema>.<table>_Diff('<timestamp>');' 4. Retrieving table changes between two times: 'SELECT * FROM <schema>.<table>_Diff('<timestamp>', '<timestamp>');' 5. Retrieving table changes to particulal tag: 'SELECT * FROM <schema>.<table>_DiffToTag(<tag number>);' More examples of retrieving table history: 1. Current state: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp);' 2. Five minutes ago: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp - INTERVAL '5 minutes');' 3. One hour ago: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp - INTERVAL '1 hour');' SUPPORTED POSTGRESQL VERSIONS Project is currently tested in PostgreSQL 8.3 and 8.4 versions. HOW IT WORKS After running 'HT_Init' function, 'history table' is created in 'history_tracker' schema. This table will save all editing history. To achieve this, set of trigger functions is appended to your original table to enable forwarding of all editing information to 'history table'. No other changes to your original table. Table structure can not be changed after running 'HT_Init'. Adding and removing history should be fully invisible for any client software. NOTES When running tests against PostgreSQL 8.3 load PgTAP compatibility patch 'compat/pgtap-8.3.patch' and uncomment line in Makefile to load 'compat/array_agg.sql' file. Good resource on retrieving PostgreSQL table metadata: http://www.alberton.info/postgresql_meta_info.html