README
------
--
-- E-MAJ : logs and rollbacks table updates : V 0.5
-- 

1- Objectives

The main goal of E-MAJ is to be able to logicaly restore a set of tables to a predefined stable state, without been obliged to restore a whole database or cluster.

It has been designed to particularly fit C.N.A.F. needs. It brings a good solution to: 
	- set inter-batch savepoints on group of tables,
	- easily "restore" this group of tables at a stable state, without being obliged to stop the cluster,
	- handle several savepoints during batch windows, each of them been usable at any time as "restore point",
	- consequently suppress a mirror of the databases (a part of the freed disk space being dedicated to additional space needed for log tables),
	- consequently decrease the number of clusters by grouping several databases into a single cluster,

2- Architecture

- One log table (<schema_name>_<table_name>_log) is created for each application table to manage, 
- A trigger is added on each application table to log row updates generated by INSERT, UPDATE and DELETE verbs
- The log table structure is similar to the application table structure, with some additional columns :
	- an identifier (associated to a sequence), 
	- the timestamp of the update, 
	- the SQL statement type (INS, UPD, DEL) that has issued the update,
	- 'OLD' or 'NEW' to distinguish the old and new column values for UPDATES
	- the role name that has initiated the update
- For each application table, a rollback function is created,
- Some technical tables and plpgsql functions are created at emaj initialisation time,
- All log and technical tables are created in a separate schema (emaj) and physicaly stored in a separate tablespace (tspemaj)


3- EMAJ uage

All the following operations need to be executed by a role having superuser privileges.

A- environment setup
- allow the use of plpgsql (for PostgreSQL version prior to 9.0) by issuing the following command:
CREATE LANGUAGE PLPGSQL;
- create the tspemaj tablespace:
	- first create the empty directory that will hold the tablespace
	- then type the command
CREATE TABLESPACE tspemaj LOCATION '<directory>';
- execute the emaj.sql script
- populate the emaj_group table to define the different groups E-MAJ will handle with their associated tables and sequences. This can be done either by INSERT or by COPY commands. The content of schema and table name columns is case sensitive and must exactly fit how schema and table names are recorded inside PostgreSQL. I.e. usual names are lower case, but if a table or schema name is embeded inside a pair of " characters, this table or schema name must be recorded with the same lower case and upper case characters as well with the same space characters if any.
- create the groups, by typing for each group:
SELECT emaj.emaj_create_group('<group_name>');

B- log activation
- to start the log for one group, type:
SELECT emaj.emaj_start_group('<group_name>','<mark_name>');
the mark name being a characters string identifying the first mark. Then each mark name can be used to rollback the whole group to its corresponding point in time.

C- intermediate mark setup
- at a stable point, a mark can be set by typing :
SELECT emaj.emaj_set_mark_group('<group_name>','<mark_name>');

D- rollback
- if it is needed to rollback a group at a mark state, just type :
SELECT emaj.emaj_rollback_group('<group_name>','<mark_name>');

E- log stop
- when log for a group is not needed anymore (at the end of a batch window for instance), the log can be stopped by: 
SELECT emaj.emaj_stop_group('<group_name>');
Note that when the log is stopped, it is not anymore possible to issue a rollback command for this group. So a stop and re-start erase all previously set marks.
But the log tables are still there and can be edited.
- to empty the log tables, the following command can be used:
SELECT emaj.emaj_reset_group('<group_name>');

F- group definition change
If the definition of a group needs to be changed (addition or deletion of tables or sequences), or if the structure of a table already belonging to a group changes, it is necesseray to :
- first delete the group by 
SELECT emaj.emaj_delete_group('<group_name>');
- then modify the content of the emaj_group table
- and finaly recreate the group by
SELECT emaj.emaj_create_group('<group_name>');

G- additional features
An intermediate mark set by the emaj_set_mark_group function can be deleted by typing :
SELECT emaj.emaj_delete_mark_group('<group_name>','<mark_name>');
It will be no longer possible to rollback to this deleted mark, but previous one can used with a emaj.emaj_rollback_group function call.

A function provides statistics facility to get the number of inserted, updated and deleted rows between 2 marks, and showing the role that generated these table updates. This function returns a result table that can be viewed by:
select * from emaj_log_stat_group('<group_Name>','<starting_mark or NULL>, <ending_mark or NULL>)


4- Limits

- PostgreSQL version >= 8.2
- maximum length for schema name + table name = 52 characters
- a tablespace, named "tspemaj" and containing emaj and all log tables must be created before emaj initialisation. If the name needs to be changed, emaj.sql script has to be adapted
- a schema, named "emaj" is created by emaj initialisation. nom du schéma de log figé "emaj". If the name needs to be changed, emaj.sql script has to be adapted
- if a TRUNCATE is performed on a table, it is not possible for EMAJ to rollback the table to a previous state.
- if DDL operations are performed on a table belonging to a group currently in log mode, it is not possible for EMAJ to rollback the table to a previous state. (see further for more details)


5- Emaj functions description

Level 1 functions:

	- emaj_create_group (v_groupName TEXT) RETURNS INT
		- creates emaj objects for all tables of a group ; returns the number of processed tables and sequences
	- emaj_delete_group (v_groupName TEXT) RETURNS INT
		- deletes the emaj objects for all tables of a group ; returns the number of processed tables and sequences
	- emaj_start_group (v_groupName TEXT, v_mark TEXT) RETURNS INT
		- activates the log triggers of all the tables for a group and set a first mark ; returns the number of processed tables and sequences
	- emaj_set_mark_group (v_groupName TEXT, v_mark TEXT) RETURNS void
		- inserts a mark in the history table and takes an image of the sequences definitions for the group
	- emaj_stop_group (v_groupName TEXT) RETURNS INT
		- de-activates the log triggers of all the tables for a group ; returns the number of processed tables and sequences
	- emaj_rollback_group (v_groupName TEXT, v_mark TEXT) RETURNS INT
		- rollbacks all tables ans sequences of a group up to a mark in the history ; returns the number of processed tables and sequences
	- emaj_reset_group (v_groupName TEXT) RETURNS INT
		- empties the log tables for all tables of a group, using a TRUNCATE ; returns the number of processed tables
	- emaj_delete_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS void
		- suppress an intermediate mark created by a emaj_set_mark_group function call
	- emaj_log_stat_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT) RETURNS SETOF emaj.emaj_stat_type
		- displays statistics from log tables showing the number of inserted, updated and deleted rows by role and between 2 marks

Level 2 functions:

	- emaj_lock_group (v_groupName TEXT) RETURNS INT
		- lock all tables of a group ; returns the number of processed tables
	- emaj_create_log (v_schemaName TEXT, v_tableName TEXT) RETURNS void
		- creates all what is needed to manage the log and rollback operations for an application table
			- the associated log table, with its own sequence
			- the function that logs the tables updates, defined as a trigger
			- the rollback function (one per table)
		- called by emaj_create_group
	- emaj_delete_log (v_schemaName TEXT, v_tableName TEXT) RETURNS void
		- deletes all what has been created by emaj_create_log
		- called by emaj_delete_group
	- emaj_delete_seq (v_schemaName TEXT, v_seqName TEXT) RETURNS void
		- deletes the rows stored into emaj_sequence for a particular sequence
		- called by emaj_delete_group
	- emaj_rlbk_table (v_schemaName TEXT, v_tableName TEXT, v_timestamp TIMESTAMPTZ) RETURNS void
		- rollbacks one table to a given timestamp (used by emaj_rollback_group)
		- called by emaj_rollback_group
	- emaj_rlbk_sequence (v_schemaName TEXT, v_seqName TEXT, v-timestamp TIMESTAMPTZ) RETURNS void
		- rollbacks one sequence to a given timestamp (used by emaj_rollback_group)
		- called by emaj_rollback_group
	- emaj_verify_all() RETURNS void
		- verifies the consistency between log and application tables
		- called by emaj_start_group, emaj_set_mark_group, emaj_rollback_group

Level 3 functions:

	- emaj_check_class (v_schemaName TEXT, v_className TEXT) RETURNS TEXT
		- verifies that an application table or sequence exists in pg_class
		- called by emaj_verify_all, emaj_create_group, emaj_delete_group, emaj_start_group, emaj_set_mark_group, emaj_stop_group, emaj_rollback_group, reset_group


6- Miscellaneous

Operation history : possible values for column oper_type : 
	- EMAJ_INIT : emaj initilisation
	- CREATE_GROUP : creation of a tables/sequences group
	- DELETE_GROUP : deletion of a tables/sequences group
	- START_GROUP : log start for a group
	- STOP_GROUP : log stop for a group
	- LOCK_GROUP : exclusive lock set on all tables belonging to a group
	- SET_MARK_GROUP : mark set for a group
	- DELETE_MARK_GROUP : mark delete for a group
	- ROLLBACK_GROUP : rollback updates for a group
	- RESET_GROUP : reset of the content of log tables and sequences
	- ROLLBACK_TABLE : rollback updates for one table
	- ROLLBACK_SEQUENCE : reset a sequence to a saved state

Consistency checks between application tables and log tables include:
	- check no table/sequence belong to several groups
	- check all tables of a group have their associated log table, both trigger and rollback functions, sequence and trigger
	- check the log tables structure is consistent with associated application tables
	- check all existing log tables correspond to entries in the emaj_group table

Permissions to emaj schema objects: 
	- all emaj objects are created by a superuser
	- all functions must be executed by a superuser 
	- triggers on application tables are created as SECURITY DEFINER

Performances (on a laptop with Virtual Box):
	- Insert 1 million rows into an empty table without log trigger = 20.5 sec
	- Insert 1 million rows into an empty table with log trigger enabled = 38.5 sec
	- Rollback 1 million rows = 55.0 sec

Recommandations:
	- it is not mandatory to declare in the emaj_group table sequences associated to a SERIAL column of a table already defined in the emaj_group. If such a sequence is not declared in the emaj_group table, a rollback operation on the associated table will not restore the sequence at it's set_mark state.
	- the emaj_start_group, emaj_set_mark_group and emaj_rollback_group functions set an exclusive lock on all tables of the group to be sure that no current transaction are in progress during these operations. But it is the user's responsability to be sure that these functions are performed at the right time.
	- for performance reason it is wise to implement the tspemaj tablespace and application tables on separate physical disk space.

What happens when DDL operation is perform on a table:
	-> if a new table is created while a group is in log mode, it cannot be managed by EMAJ inside this group until the group is stopped, deleted and recreated
	-> if a table is dropped, there is no way to get it back without a restore operation (not handled by EMAJ)
	-> changes on index, rights or constraints are possible while the related group is in log mode, but moving back to a previous state has to be done manually
	-> if a table's column is added or dropped while the related group is in log mode, the log trigger will likely fail at the next insert/update/delete operation
	-> if a table's column is renamed while the related group is in log mode, the log trigger will not fail at the next insert/update/delete operations ; but any further rollback attempt will be rejected because of detected discrepancy between application and log tables
	-> if a column's type is changed, the format of the log table will not be consistent anymore. But the next insert/update/delete operations will likely not fail. There is a risk of data loss for instance if data length has been increased. Again, any further rollback attempt will be rejected because of detected discrepancy between application and log tables.