Other functions¶
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 schema and each secondary schema if any) 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 here).
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.
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.
Prerequisite¶
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.
As a result, monitoring rollback operations requires the installation of the dblink extension as well as the insertion of a connection identifier usable by dblink into the emaj_param table.
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).
Monitoring function¶
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:
| Column | Type | Description |
|---|---|---|
| rlbk_id | INT | rollback identifier |
| 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:
SELECT emaj.emaj_cleanup_rollback_state();
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:
SELECT emaj.emaj_disable_protection_by_event_triggers();
The function returns the number of deactivated event triggers (this value depends on the installed PostgreSQL version).
To reactivate existing event triggers:
SELECT emaj.emaj_enable_protection_by_event_triggers();
The function returns the number of reactivated event triggers.