Check the consistency of the E-Maj environment¶
A function is also available to check the consistency of the E-Maj environment. It consists in checking the integrity of all E-Maj schemas and all created tables groups. This function can be called with the following SQL statement:
SELECT * FROM emaj.emaj_verify_all();
For each E-Maj schema (emaj and each log schema) the function verifies that:
- all tables, functions, sequences and types contained in the schema are either objects of the extension, or linked to created tables groups,
- they don’t contain any view, foreign table, domain, conversion, operator or operator class.
Then, for each created tables group, the function performs the same checks as those performed when a group is started, a mark is set, or a rollback is executed (more details).
The function returns a set of rows describing the detected discrepancies. If no error is detected, the function returns a single row containing the following messages:
'No error detected'
The emaj_verify_all() function can be executed by any role belonging to emaj_adm or emaj_viewer roles.
If errors are detected, for instance after an application table referenced in a tables group has been dropped, appropriate measures must be taken. Typically, the potential orphan log tables or functions must be manually dropped.
Not disabling application triggers at E-Maj rollback time¶
Application triggers are automatically disabled during E-Maj rollback operations. Under some circumstances, it may be desirable to keep them enabled (more details here). The emaj_ignore_app_trigger() function achieves this. It allows to add or remove triggers into/from a list of triggers that do not need to be disabled during rollback operations.
SELECT emaj.emaj_ignore_app_trigger(<action>, <schema.name>, <table.name>, <trigger.name>);
The <action> parameter accepts 2 values: ‘ADD’ to add a trigger to the list or ‘REMOVE’ to delete a trigger from the list.
The trigger is identified by the 3 components: schema name, table name and trigger name.
The trigger name may contain ‘%’ and ‘_’ wildcard characters. These characters have the same meaning as in the LIKE clause of the SQL language. Thus several triggers of a single table can be processed by a unique function call.
The function returns the number of triggers effectively added or removed.
The function does not process E-Maj triggers (log triggers or triggers protecting against TRUNCATE).
The triggers referenced as “not to be automatically disabled during E-Maj rollbacks” are registered into the emaj.emaj_ignored_app_trigger table. This table contains 3 columns:
- trg_schema : schema of the table holding the trigger
- trg_table : table holding the trigger
- trg_name : trigger name
In order to know the list of registered triggers, just display the table’s content.
Monitoring rollback operations¶
When the volume of recorded updates to cancel leads to a long rollback, it may be interesting to monitor the operation to appreciate how it progresses. A function, named emaj_rollback_activity(), and a client, emajRollbackMonitor.php, fit this need.
To allow E-Maj administrators to monitor the progress of a rollback operation, the activated functions update several technical tables as the process progresses. To ensure that these updates are visible while the transaction managing the rollback is in progress, they are performed through a dblink connection.
Recording the connection identifier can be performed with a statement like:
INSERT INTO emaj.emaj_param (param_key, param_value_text) VALUES ('dblink_user_password','user=<user> password=<password>');
The declared connection role must have been granted the emaj_adm rights (or be a superuser).
Lastly, the main transaction managing the rollback operation must be in a “read committed” concurrency mode (the default value).
The emaj_rollback_activity() function allows one to see the progress of rollback operations.
Invoke it with the following statement:
SELECT * FROM emaj.emaj_rollback_activity();
The function does not require any input parameter.
It returns a set of rows of type emaj.emaj_rollback_activity_type. Each row represents an in progress rollback operation, with the following columns:
|rlbk_groups||TEXT||tables groups array associated to the rollback|
|rlbk_mark||TEXT||mark to rollback to|
|rlbk_mark_datetime||TIMESTAMPTZ||date and time when the mark to rollback to has been set|
|rlbk_is_logged||BOOLEAN||boolean taking the “true” value for logged rollbacks|
|rlbk_nb_session||INT||number of parallel sessions|
|rlbk_nb_table||INT||number of tables contained in the processed tables groups|
|rlbk_nb_sequence||INT||number of sequences contained in the processed tables groups|
|rlbk_eff_nb_table||INT||number of tables having updates to cancel|
|rlbk_status||ENUM||rollback operation state|
|rlbk_start_datetime||TIMESTAMPTZ||rollback operation start timestamp|
|rlbk_elapse||INTERVAL||elapse time spent since the rollback operation start|
|rlbk_remaining||INTERVAL||estimated remaining duration|
|rlbk_completion_pct||SMALLINT||estimated percentage of the completed work|
An in progress rollback operation is in one of the following state:
- PLANNING : the operation is in its initial planning phase,
- LOCKING : the operation is setting locks,
- EXECUTING : the operation is currently executing one of the planned steps.
If the functions executing rollback operations cannot use dblink connections (extension not installed, missing or incorrect connection parameters,…), the emaj_rollback_activity() does not return any rows.
The remaining duration estimate is approximate. Its precision is similar to the precision of the emaj_estimate_rollback_group() function.
Updating rollback operations state¶
The emaj_rlbk technical table and its derived tables contain the history of E-Maj rollback operations.
When rollback functions cannot use dblink connections, all updates of these technical tables are all performed inside a single transaction. Therefore:
- any rollback operation that has not been completed is invisible in these technical tables,
- any rollback operation that has been validated is visible in these technical tables with a “COMMITTED” state.
When rollback functions can use dblink connections, all updates of emaj_rlbk and its related tables are performed in autonomous transactions. In this working mode, rollback functions leave the operation in a “COMPLETED” state when finished. A dedicated internal function is in charge of transforming the “COMPLETED” operations either into a “COMMITTED” state or into an “ABORTED” state, depending on how the main rollback transaction has ended. This function is automatically called when a new mark is set and when the rollback monitoring function is used.
If the E-Maj administrator wishes to check the status of recently executed rollback operations, he can use the emaj_cleanup_rollback_state() function at any time:
The function returns the number of modified rollback operations.
Deactivating or reactivating event triggers¶
The E-Maj extension installation procedure activates event triggers to protect it. Normally, these triggers must remain in their state. But if the E-Maj administrator needs to deactivate and the reactivate them, he can use 2 dedicated functions.
To deactivate the existing event triggers:
The function returns the number of deactivated event triggers (this value depends on the installed PostgreSQL version).
To reactivate existing event triggers:
The function returns the number of reactivated event triggers.