README
------
--
-- E-Maj : logs and rollbacks table updates : V 0.8.0
-- 
-- This software is distributed under the GNU General Public License.
--

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
Used in development or test environments, it may bring a good help in testing application, providing an easy way to rollback all updates generated by programs execution and replay these processings as many times as needed.  

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
	- the transaction id of the transaction that issues 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)
- Two roles are available to help the E-maj use.


3- E-Maj uage

A- environment setup
For these operations, the user must be logged as superuser.

If an old E-Maj version is already installed, use the uninstall.sql script to suppress all previously installed E-Maj components.
 
- 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. 

- setup role policy: 
The emaj.sql script creates 2 roles without logging capacity but having the following permissions:
	- emaj_adm can execute all emaj functions,
	- emaj_viewer can only read emaj and log tables, and execute both emaj statistics functions that do not update any emaj components. 
So there are two ways to manage emaj operations: either let a superuser work or dedicate this mission to a specific role. In the later case, the role must become a member of emaj_adm group with the following command :
GRANT emaj_adm TO <role>;
Similarly, a role can be allowed to look at emaj and log tables (for application support purpose for instance) if the following command has been issued:
GRANT emaj_viewer TO <role>;
Note that all operations that an emaj_viewer role can perform are also allowed for members of emaj_adm role.
So the next operations can be done either by a superuser of by a role member of emaj_adm.

- 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>');
- it may be necessary to increase the max_locks_per_transaction parameter in the postgresql.conf file, in particular if the number of tables in groups is high.

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>');
When the log is stopped, the marks are set to a DELETED state and it is not possible to issue a rollback command for this group anymore.
But the log tables are still there and can be viewed.
An emaj_stop_group call for a group that is not currently logging only produces a warning.
- 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
A 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 at least one mark must be kept active to be used with a emaj.emaj_rollback_group function call. If the deleted mark is the first known mark for the group, the log rows that cannot be used for rollback anymore are purged from the related log tables.

A mark set by the emaj_set_mark_group function can be renamed by typing :
SELECT emaj.emaj_rename_mark_group('<group_name>','<mark_name>','<new_mark_name>');

A function provides statistics facility to get the number of inserted, updated and deleted rows between 2 marks, or between a mark and the current situation. It can be useful to estimate the time needed to rollback to a specified mark. 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>)
The keyword 'EMAJ_LAST_MARK' can also be used instead of the last active mark name for the group.

More detailed statistics are also available, showing for each table the number of log rows per statement type (INSERT, UPDATE, DELETE) and per ROLE. They can be obtained using a function that returns a result table. They can be viewed by:
select * from emaj_detailed_log_stat_group('<group_Name>','<starting_mark or NULL>, <ending_mark or NULL>)
The keyword 'EMAJ_LAST_MARK' can also be used instead of the last active mark name for the group.
As the function needs to scan log tables, it may take much more time to return the result.

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>);
Due to the use of COPY statements, it must be called by a superuser (a emaj_adm role is not sufficient).

A special function emaj_force_drop_group can be used to drop a group in very special case when the group to delete is not consistent and the standart emaj_drop_group function call aborts due to a group remaining in LOGGING state and a emaj_stop_group also aborts because of this unconsistency of the group.

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.
- as each sub-group issues a 2 phase-commit, it is necessary that the max_prepared_transaction parameter in the postgresql.conf file be set to value at least equal to the number of sub-groups.


4- Limits

- PostgreSQL version >= 8.2
- maximum length for schema name + table name = 52 characters
- the maximum number of row insertions, deletions and updates for a logged application table is practicaly unlimited(4,611,686,018,427,387,903).
- 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
		- drops 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 and 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 a mark created by a emaj_set_mark_group function call
	- emaj.emaj_rename_mark_group(v_groupName TEXT, v_mark TEXT, v_new_name TEXT)
		- rename a mark previously created by a emaj_set_mark_group function call
	- emaj.emaj_log_stat_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT) RETURNS SETOF emaj.emaj_log_stat_type
		- quickly returns statistics showing the number of log tables rows between 2 marks.
	- emaj_detailed_log_stat_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT) RETURNS SETOF emaj.emaj_detailed_log_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_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.
	- emaj.emaj_force_drop_group(v_groupName TEXT) RETURNS INT
		- deletes the emaj objects for all tables of a group, without checking the group's state.

Level 2 functions:

	- _verify_all() RETURNS SETOF TEXT
		- identifies in emaj schema tables or functions that are not linked to configured groups
	- _lock_group (v_groupName TEXT, v_lockMode TEXT) RETURNS INT
		- lock all tables of a group in a specified mode ; returns the number of processed tables
	- _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
	- _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
	- _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
	- _drop_group (v_groupName TEXT) RETURNS INT
		- effectively drops the emaj objects for all tables of a group ; returns the number of processed tables and sequences
		- called by emaj_drop_group and emaj_force_drop_group
	- _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
	- _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
	- _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
	- _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
	- _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:

	- _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, emaj_reset_group
	- _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 _rlbk_group_step1
	- _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 _rlbk_group function and the emajParallelRollback.php module
	- _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 _rlbk_group function and the emajParallelRollback.php module
	- _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 _rlbk_group function and the emajParallelRollback.php module
	- _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 _rlbk_group function and the emajParallelRollback.php module
	- _rlbk_group_step5(v_groupName TEXT, v_subGroup INT) RETURNS void
		- step 5 of the rollback_group operation ; recreate foreign keys
		- called by _rlbk_group function and the emajParallelRollback.php module
	- _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 _rlbk_group function and the emajParallelRollback.php module
	- _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 _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
	- FORCE_DROP_GROUP : drop of a tables/sequences group (without group's state checking)
	- 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
	- RENAME_MARK_GROUP : mark rename 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 or a role member of the emaj_adm role
	- triggers on application tables are created as SECURITY DEFINER
	- member of emaj_viewer role can look at all emaj and log tables

Performances (on a laptop with VirtualBox):
	- 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.

