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 dblink connection is not operationnal,

  • event triggers protecting E-Maj are missing or are disabled.

The emaj_verify_all() function can be executed by any role belonging to emaj_adm or emaj_viewer roles (the dblink connection not being tested for the later).

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

History data purge

E-Maj keeps some historical data: traces of elementary operations, E-Maj rollback details, tables groups structure changes (more details…). Oldest traces are automaticaly purged by the extension. But it is also possible to purge these obsolete traces on demand using:

SELECT emaj.emaj_purge_histories('<retention.delay>');

The <retention.delay> parameter is of type INTERVAL. It overloads the ‘history_retention’ parameter of the emaj_param table.

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.

Snap tables and sequences of a tables group

It may be useful to take images of all tables and sequences belonging to a group to be able to analyse their content or compare them. It is possible to dump to files all tables and sequences of a group with:

SELECT emaj.emaj_snap_group('<group.name>', '<storage.directory>', '<COPY.options>');

The directory/folder name must be supplied as an absolute pathname and must have been previously created. This directory/folder must have the appropriate permission so that the PostgreSQL instance can write in it.

The third parameter defines the output files format. It is a character string that matches the precise syntax available for the COPY TO SQL statement. Look at the PostgreSQL documentation to get more details about the available options (https://www.postgresql.org/docs/current/sql-copy.html).

The function returns the number of tables and sequences contained by the group.

This emaj_snap_group() function generates one file per table and sequence belonging to the supplied tables group. These files are stored in the directory or folder corresponding to the second parameter.

New files will overwrite existing files of the same name.

Created files are named with the following pattern: <schema.name>_<table/sequence.name>.snap

In order to manipulate generated files more easily, any unconvenient in file name characters, namely spaces, “/”, “”, “$”, “>”, “<”, “|”, single or double quotes and “*” are replaced by “_”. Beware, these file names adjusment may lead to duplicates, the last generated file overwriting then the previous ones.

Each file corresponding to a sequence has only one row, containing all characteristics of the sequence.

Files corresponding to tables contain one record per row, in the format corresponding to the supplied parameter. These records are sorted in ascending order of the primary key.

At the end of the operation, a file named _INFO is created in this same directory/folder. It contains a message including the tables group name and the date and time of the snap operation.

It is not necessary that the tables group be in IDLE state to snap tables.

As this function may generate large or very large files (of course depending on tables sizes), it is user’s responsibility to provide a sufficient disk space.

Thanks to this function, a simple test of the E-Maj behaviour could chain: