Other groups management functions

Reset log tables of a group

In standard use, all log tables of a tables group are purged at emaj_start_group time. But, if needed, it is possible to reset log tables, using the following SQL statement:

SELECT emaj.emaj_reset_group('<group.name>');

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

Of course, in order to reset log tables, the tables group must be in IDLE state.

Comments on groups

In order to set a comment on any group, the following statement can be executed:

SELECT emaj.emaj_comment_group('<group.name>', '<comment>');

The function doesn’t return any data.

To modify an existing comment, just call the function again for the same tables group, with the new comment.

To delete a comment, just call the function, supplying a NULL value as comment.

Comments are stored into the group_comment column from the emaj_group table, which describes … groups.

Protection of a tables group against rollbacks

It may be useful at certain time to protect tables groups against accidental rollbacks, in particular with production databases. Two functions fit this need.

The emaj_protect_group() function set a protection on a tables group.

SELECT emaj.emaj_protect_group('<group.name>');

The function returns the integer 1 if the tables group was not already protected, or 0 if it was already protected.

Once the group is protected, any logged or unlogged rollback attempt will be refused.

An AUDIT_ONLY or IDLE tables group cannot be protected.

When a tables group is started, it is not protected. When a tables group that is protected against rollbacks is stopped, it looses its protection.

The emaj_unprotect_group() function remove an existing protection on a tables group.

SELECT emaj.emaj_unprotect_group('<group.name>');

The function returns the integer 1 if the tables group was previously protected, or 0 if it was not already protected.

An AUDIT_ONLY tables group cannot be unprotected.

Once the protection of a tables group is removed, it becomes possible to execute any type of rollback operation on the group.

A protection mechanism at mark level complements this scheme.

Forced stop of a tables group

It may occur that a corrupted tables group cannot be stopped. This may be the case for instance if an application table belonging to a tables group has been inadvertently dropped while the group was in LOGGING state. If usual emaj_stop_group() or emaj_stop_groups() functions return an error, it is possible to force a group stop using the emaj_force_stop_group() function.

SELECT emaj.emaj_force_stop_group('<group.name>');

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

The emaj_force_stop_group() function performs the same actions as the emaj_stop_group() function, except that:

  • it supports the lack of table or E-Maj trigger to deactivate, generating a “warning” message in such a case,
  • it does NOT set a stop mark.

Once the function is completed, the tables group is in IDLE state. It may then be altered or dropped, using the emaj_alter_group() or emaj_drop_group() functions.

It is recommended to only use this function if it is really needed.

Forced suppression of a tables group

It may happen that a damaged tables group cannot be stopped. But not being stopped, it cannot be dropped. To be able to drop a tables group while it is still in LOGGING state, a special function exists.:

SELECT emaj.emaj_force_drop_group('<group.name>');

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

This emaj_force_drop_group() functions performs the same actions than the emaj_drop_group() function, but without checking the state of the group. So, it is recommended to only use this function if it is really needed.

Note

Since the emaj_force_stop_group() function has been created, this emaj_force_drop_group() function becomes useless. It may be removed in a future version.

Logged rollback consolidation

Following the execution of a “logged rollback”, and once the rollback operation recording becomes useless, it is possible to “consolidate” this rollback, meaning to some extent to transform it into “unlogged rollback”. A the end of the consolidation operation, marks and logs between the rollback target mark and the end rollback mark are deleted. The emaj_consolidate_rollback_group() function fits this need.:

SELECT emaj.emaj_consolidate_rollback_group('<group.name>', <end.rollback.mark>);

The concerned logged rollback operation is identified by the name of the mark generated at the end of the rollback. This mark must always exist, but may have been renamed.

The ‘EMAJ_LAST_MARK’ keyword may be used as mark name to reference the last set mark.

The emaj_get_consolidable_rollbacks() function may help to identify the rollbacks that may be condolidated.

Like rollback functions, the emaj_consolidate_rollback_group() function returns the number of effectively processed tables and sequences.

The tables group may be in LOGGING or in IDLE state.

The rollback target mark must always exist but may have been renamed. However, intermediate marks may have been deleted.

When the consolidation is complete, only the rollback target mark and the end rollback mark are kept.

The disk space of deleted rows will become reusable as soon as these log tables will be “vacuumed”.

Of course, once consolidated, a “logged rollback” cannot be cancelled (or rolled back) any more, the start rollback mark and the logs covering this rollback being deleted.

The consolidation operation is not sensitive to the protections set on groups or marks, if any.

If a database has enough disk space, it may be interesting to replace a simple unlogged rollback by a logged rollback followed by a consolidation so that the application tables remain readable during the rollback operation, thanks to the lower locking mode used for logged rollbacks.

List of “consolidable rollbacks”

The emaj_get_consolidable_rollbacks() function help to identify the rollbacks that may be consolidated.:

SELECT * FROM emaj.emaj_get_consolidable_rollbacks();

The function returns a set of rows with the following columns:

Column Type Description
cons_group TEXT rolled back tables group
cons_target_rlbk_mark_name TEXT rollback target mark name
cons_target_rlbk_mark_time_id BIGINT temporal reference of the target mark (*)
cons_end_rlbk_mark_name TEXT rollback end mark name
cons_end_rlbk_mark_time_id BIGINT temporal reference of the end mark (*)
cons_rows BIGINT number of intermediate updates
cons_marks INT number of intermediate marks

(*) emaj_time_stamp table identifiers ; this table contains the time stamps of the most important events of the tables groups life.

Using this function, it is easy to consolidate at once all “consolidable” rollbacks for all tables groups in order to recover as much as possible disk space:

SELECT emaj.emaj_consolidate_rollback_group(cons_group, cons_end_rlbk_mark__name) FROM emaj.emaj_get_consolidable_rollbacks();

The emaj_get_consolidable_rollbacks() function may be used by emaj_adm and emaj_viewer roles.

Exporting and importing tables groups configurations

A set of functions allow to export and import tables groups configurations. They may be useful to deploy a standardized tables group configuration on several databases or to upgrade the E-Maj version by a complete extension un-install and re-install.

Export a tables groups configuration

Two versions of the emaj_export_groups_configuration() function export a description of one or several tables groups as a JSON structure.

A tables groups configuration can be written to a file with:

SELECT emaj_export_groups_configuration('<file.path>', <groups.names.array>);

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

The second parameter is optional. It lists in an array the tables groups names to processed. If the parameter is not supplied or is set to NULL, the configuration of all tables groups is exported.

The function returns the number of exported tables groups.

If the file path is not supplied (i.e. is set to NULL), the function directly returns the JSON structure containing the configuration. This structure looks like this:

{
     "_comment": "Generated on database <db> with E-Maj version <version> at <date_heure>",
     "tables_groups": [
             {
             "group": "...",
             "is_rollbackable": ...,
             "comment": "...",
             "tables": [
                     {
                     "schema": "...",
                     "table": "...",
                     "priority": ...,
                     "log_data_tablespace": "...",
                     "log_index_tablespace": "...",
                     "ignored_triggers": [
                             {
                             "trigger": "...",
                             ...
                              },
                     ]
                     },
                     {
                     ...
                     }
             ],
             "sequences": [
                     {
                     "schema": "myschema1",
                     "sequence": "mytbl1",
                     },
                     {
                     ...
                     }
             ],
             },
             ...
     ]
}

Import a tables groups configuration

Two versions of the emaj_import_groups_configuration() function import a description of tables groups as a JSON structure.

A tables groups configuration can be read from a file with:

SELECT emaj_import_groups_configuration('<file.path>', <groups.names.array>, <alter_started_groups>);

The file must be accessible by the PostgreSQL instance.

The file must contain a JSON structure with an attribute named “tables-groups” of type array, and containing sub-structures describing each tables group, as described in the previous chapter about tables groups configuration exports.

The function can directly import a file generated by the emaj_export_groups_configuration() function.

The second parameter is of type array and is optional. It contains the list of the tables groups to import. By default, all tables groups described in the file are imported.

If a tables group to import does not exist, it is created and its tables and sequences are assigned into it.

If a tables group to import already exists, its configuration is adjusted to reflect the target configuration: some tables and sequences may be added or removed, and some attributes may be modified. When the tables group is in LOGGING state, its configuration adjustment is only possible if the third parameter is explicitly set to TRUE.

The function returns the number of imported tables groups.

Note that importing a tables group overrides the previous group’s configuration, if any, in the emaj_group_def technical table.

In a variation of the function, the first input parameter directly contains the JSON description of the groups to load:

SELECT emaj_import_parameters_configuration('<JSON.structure>, <groups.names.array>, <alter_started_groups>);