Other functions

Get the emaj extension current version

The emaj_get_version() function returns the current version identifier of the emaj extension.

SELECT emaj.emaj_get_version();

Check the E-Maj environment consistency

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,

  • a foreign key is inheritated from a partitionned table but either is not DEFERRABLE or holds an ON DELETE or ON UPDATE clause, blocking its potential drop / recreation during an E-Maj rollback in both cases,

  • 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.

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

Purge history data

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. If present, it overloads the ‘history_retention’ E-Maj parameter.

The function returns a synthetic message of executed deletions.

Deactivate/reactive 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 absolutely needs to temporarily deactivate 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.

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 on the primary key ascending order (or on all columns if the table has no primary key). Each row contains all table columns, including generated columns.

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: