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

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,
	- allow use of PostgreSQL advanced features like log-shipping and PITR

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 if it is not already the case (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
- a non-regression test can be launched by executing the test-emaj.sql script. If no error is encountered, a final message "--- test-emaj script successfuly completed ---" should appear. To cleanup the test environment, just rerun the emaj.sql script. A second test script is provided: test-emaj-2. It is intended to prepare a test environment for the parallel rollback extension. 
 
- populate the emaj_group_def table to define the tables and sequences associated to the different groups E-MAJ will handle. 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. Starting a group results in the deletion of all previously logged data.

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>');
Once this function is performed, it is possible to continue the log collection for further processing.
- as an alternative, and if the group has to be stopped after the rollback operation, it is possible to speed up the rollback by typing :
SELECT emaj.emaj_rollback_and_stop_group('<group_name>','<mark_name>');
- the 'EMAJ_LAST_MARK' keyword can be used as mark name instead of the last active mark for the group

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 before restarting the group, the following command can be used:
SELECT emaj.emaj_reset_group('<group_name>');
If not used, the next emaj_start_group function call will purge log tables for the group.

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 :
- stop the group if it is in logging state
- and drop the group by 
SELECT emaj.emaj_drop_group('<group_name>');
- then modify the content of the emaj_group_def table
- and finaly recreate the group by
SELECT emaj.emaj_create_group('<group_name>');
Note that the content's change of the emaj_group_def table can be prepared before the emaj_drop_table function call. 

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

Another statistics function quickly computes for each log table of a group the number of rows involved in a rollback to a given mark. This function returns a result table that can be viewed by:
select * from emaj_rlbk_stat_group('<group_Name>','<mark>');
The keyword 'EMAJ_LAST_MARK' can also be used instead of the last active mark name for the group.

A function identifies tables or functions from emaj schema that would not be linked to configured groups. Returning several text rows, it has to be called with:
select * from emaj.emaj_verify_all();

For test purpose, a function can snap all tables and sequences of a group on individual files located on a given directory; rows are ordered by primay keys. After the directory has been created with proper permissions, the function can be called by:
select emaj.emaj_snap_group(<group_name>,<absolute directory pathname>);


H- parallel rollback extension
In order to speed up the rollback operation, it is possible to perform a parallel rollback. This can be achieved by using a supplied php module, named emajParallelRollback.php. It automaticaly spreads to rollback activity on a given number of sub-groups. To prepare the subgroups' content, it uses statistics provided by the emaj_rlbk_stat_group function and alse takes into account the foreign key contraints that link several tables, putting them in the same subgroup.
Php and its PostgreSQL interface must be installed before using this feature. 
It also requires that the max_prepared_transaction parameter in the postgresql.conf file be set to a value at least equal to the maximum number of subgroups that will be processed in parallel.
The command syntax and the available options can be displayed by typing:
emajParallelRollback.php --help
This returns: 
emajParallelRollback belongs to the E-Maj PostgreSQL contrib (version 0.7).
It performs E-Maj rollback for a group and a previously set mark, processing tables in parallel.

Usage:
  emajParallelRollback -g <E-Maj group name> -m <E-Maj mark> -s <number of sub_groups> [OPTION]... 

Options:
  -v          verbose mode; writes more information about the processing
  --help      shows this help, then exit
  --version   outputs version information, then exit

Connection options:
  -d,         database to connect to
  -h,         database server host or socket directory
  -p,         database server port
  -U,         user name to connect as
  -W,         password associated to the user, if needed

Note that :
- the usual environment variables specifying connection parameters (PGPORT,...) can also be used.
- it is recommended not to use the -W option to supply a password and to use instead the .pgpass file.
- the keyword 'EMAJ_LAST_MARK' can also be used instead of the last active mark name for the group.


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. 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 (those that can be directly called by users) :

	- 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_drop_group (v_groupName TEXT) RETURNS INT
		- drop 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_rollback_and_stop_group (v_groupName TEXT, v_mark TEXT) RETURNS INT
		- rollbacks all tables ans sequences of a group up to a mark in the history and calls for a emaj_stop_group function ; but the log table are not deleted to speed up the rollback operation when possible ; 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
		- returns statistics from log tables showing the number of inserted, updated and deleted rows by role and between 2 marks (active or deleted)
	- emaj.emaj_rlbk_stat_group(v_groupName TEXT, v_Mark TEXT) RETURNS SETOF emaj.emaj_rlbk_stat_type
		- quickly returns statistics showing the number of log tables rows that would be concerned by a rollback to a given mark operation.
	- emaj_verify_all() RETURNS SETOF TEXT
		- identifies in emaj schema tables or functions that are not linked to configured groups
	- emaj.emaj_snap_group(v_groupName TEXT, v_dir TEXT) RETURNS INT 
		- creates a file for each table belonging to the group and containing all rows of this table ordered by primary key ; returns the number of processed tables and sequences.

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, v_logOnly BOOLEAN) 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), except if log_only emaj parameter is currently set to true
		- 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_drop_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_drop_group
	- emaj_rlbk_group (v_groupName TEXT, v_mark TEXT) RETURNS INT
		- effectively rollbacks all tables and sequences of a group up to a mark in the history
		- called by emaj_rollback_group and emaj_rollback_group_and_stop functions
	- emaj_rlbk_table (v_schemaName TEXT, v_tableName TEXT, v_timestamp TIMESTAMPTZ, v_delete_log BOOLEAN) 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, v_delete_log BOOLEAN) RETURNS void
		- rollbacks one sequence to a given timestamp (used by emaj_rollback_group)
		- called by emaj_rollback_group
	- emaj_verify_group() RETURNS void
		- verifies the consistency between all emaj components for the group
		- called by emaj_start_group, emaj_set_mark_group, emaj_rollback_group
	- emaj_check_fk_group (v_groupName TEXT) RETURNS INT
		- issues a warning if foreign keys exist between table inside and ouside the processed group
		- called by emaj_create_group, emaj_start_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_drop_group, emaj_start_group, emaj_set_mark_group, emaj_stop_group, emaj_rollback_group, reset_group
	- emaj.emaj_check_fk_group (v_groupName TEXT) RETURNS void
		- verifies that there is foreign key relationship between tables of a group and tables outside this group
		- called by emaj_create_group, emaj_start_group and emaj_rlbk_group_step1
	- emaj.emaj_rlbk_group_step1(v_groupName TEXT, v_mark TEXT, v_nb_subGroup INT) RETURNS INT 
		- step 1 of the rollback_group operation ; initiate a rollback_group
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_step2(v_groupName TEXT, v_subGroup INT) RETURNS void  
		- step 2 of the rollback_group operation ; lock tables for a sub-group
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_step3(v_groupName TEXT, v_subGroup INT) RETURNS void
		- step 3 of the rollback_group operation ; drop fkey for a sub-group
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_step4(v_groupName TEXT, v_mark TEXT, v_subGroup INT, v_delete_log BOOLEAN) RETURNS INT 
		- step 4 of the rollback_group operation ; rollback tables and sequences
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_step5(v_groupName TEXT, v_subGroup INT) RETURNS void
		- step 5 of the rollback_group operation ; recreate foreign keys
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_step6(v_groupName TEXT, v_mark TEXT, v_nbTb INT) RETURNS void
		- step 6 of the rollback_group operation ; complete the rollback operation
		- called by emaj_rlbk_group function and the emajParallelRollback.php module
	- emaj.emaj_rlbk_group_set_subgroup(v_groupName TEXT, v_schema TEXT, v_table TEXT, v_subGroup INT, v_rows BIGINT) RETURNS BIGINT
		- is used to generate the content of sub-groups
		- called by emaj.emaj_rlbk_group_step2 function

6- Miscellaneous

E-maj Parameters:
- 'version'           type = text         fixed value set at initialisation time ; should not be modified
- 'log_only'          type = boolean      default value = false ; can be set to true to only log without rollback capabilities
- 'history_retention' type = interval     default value = 1 month ; can be set to another value to adjust the retention of history rows

Operation history : possible values for column emaj_hist.oper_type : 
	- EMAJ_INIT : emaj initilisation
	- CREATE_GROUP : creation of a tables/sequences group
	- DROP_GROUP : drop 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
	- LOCK_SUBGROUP : exclusive lock set on all tables belonging to a sub-group of 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

Groups : possible value for column emaj_group.group_state:
	- IDLE : created but not in logging state
	- LOGGING : set at emaj_start_group time 

Marks : possible value for column emaj_mark.mark_state:
	- ACTIVE 
	- DELETED

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:
	- the emaj_start_group, emaj_set_mark_group, emaj_rollback_group and emaj_rollback_and_stop_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
	- when triggers are defined on application tables, it is the user's responsability to understand the impact of rollback operations on these tables. If trigger just adjusts the content of a row to insert or update, the final column values are recorded on log table so that a rollback reset this row to its original state and the trigger can be left at rollback time. On the contrary, if a trigger performs updates on another tables, any E-Maj rollback will not necessarily take into account these generated updates. Furthermore, the table updates generated by rollbacks may fire the trigger and generate damages in other application tables. In such cases, user has to at least deactivate such triggers before any rollback and take any other necessary actions to keep tables logically coherent.
	- for performance reason it may be 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.


