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 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 function also returns warnings when:

  • a sequence linked to a column belongs to a tables group, but the associated table does not belong to the same tables group,
  • a table of a tables group is linked to another table by a foreign key, but the associated table does not belong to the same tables group.

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.

Exporting and importing parameters configurations

Two functions sets allow to respectively export and import parameters configurations. They can be useful to deploy a standardized parameters set on several databases, or during E-Maj version upgrades by a full extension uninstallation and reinstallation.

Exporting a parameters configuration

Two versions of the emaj_export_parameters_configuration() function export all the parameters registered in the emaj_param table in a JSON structure, except the parameter of key “emaj_version”, which is linked to the emaj extension itself and is not really a configuration parameter.

The parameters data can be written to a file with:

SELECT emaj_export_parameters_configuration('<file.path>');

The file path must be accessible in write mode by the PostgreSQL instance.

The function returns the number of exported parameters.

If the file path is not supplied, the function directly returns the JSON structure containing the parameters value. This structure looks like this:

{
  "_comment": "E-Maj parameters, generated from the database <db> with E-Maj version <version> at <date_heure>",
  "parameters": [
    {
       "key": "...",
       "value": "..."
    },
    {
       ...
    }
  ]
}

Importing a parameters configuration

Two versions of the emaj_import_parameters_configuration() function import parameters from a JSON structure into the emaj_param table.

A file containing parameters to load can be read with:

SELECT emaj_import_parameters_configuration('<file.path>', <delete.current.configuration>);

The file path must be accessible by the PostgreSQL instance.

The file must contain a JSON structure having an attribute named “parameters”, of array type, and containing sub-structures with the attributes “key” and “value”:

{"parameters": [
  {
    "key": "...",
    "value": "..."
  },
  {
        ...
  }
]}

If a paramater has no “value” attribute or if this attribute is set to NULL, the parameter is not inserted into the emaj_param table, and is deleted if it already exists in the table. So the parameter’s default value will be used by the emaj extension.

The function can directly load a file generated by the emaj_export_parameters_configuration() function.

If present, the paramètre of key “emaj_version” is not processed.

The second parameter, boolean, is optional. It tells whether the current parameter configuration has to be deleted before the load. It is FALSE by default, meaning that the keys currenly stored into the emaj_param table, but not listed in the JSON structure are kept (differential mode load). If the value of this second parameter is set to TRUE, the function performs a full replacement of the parameters configuration (full mode load).

The function returns the number of imported parameters.

As an alternative, the first input parameter of the function directly contains the JSON structure of the parameters to load:

SELECT emaj_import_parameters_configuration('<JSON.structure>', <delete.current.configuration>);

Getting the current log table linked to an application table

The emaj_get_current_log_table() function allows to get the schema and table names of the current log table linked to a given application table.

SELECT log_schema, log_table FROM
        emaj_get_current_log_table(<schema>, <table>);

The function always returns 1 row. If the application table does not currently belong to any tables group, the log_schema and log_table columns are set to NULL.

The emaj_get_current_log_table() function can be used by emaj_adm and emaj_viewer E-Maj roles.

It is possible to build a statement accessing a log table. For instance:

SELECT 'select count(*) from '
        || quote_ident(log_schema) || '.' || quote_ident(log_table)
        FROM emaj.emaj_get_current_log_table('myschema','mytable');

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.

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.