Creating and dropping tables groups

Tables groups configuration principles

Configuring a tables group consists in:

  • defining the tables group characteristics,

  • defining the tables and sequences to assign to the group,

  • optionnaly, defining some specific properties for each table.

The tables group

A tables group is identified by its name. Thus, the name must be unique withing the database. A tables group name contains at least 1 character. It may contain spaces and/or any punctuation characters. But it is advisable to avoid commas, single or double quotes.

At creation time, the ROLLBACKABLE or AUDIT_ONLY property of the group must be set. Note that this property cannot be modified once the tables group is created. If it needs to be changed, the tables group must be dropped and then recreated.

The tables and sequences to assign

A tables group can contain tables and/or sequences belonging to one or several schemas.

All tables of a schema are not necessarily member of the same group. Some of them can belong to another group. Some others can belong to any group.

But at a given time, a table or a sequence cannot be assigned to more than one tables group.

Caution

To guarantee the integrity of tables managed by E-Maj, it is essential to take a particular attention to the tables groups content definition. If a table were missing, its content would be out of synchronisation with other tables it is related to, after an E-Maj rollback operation. In particular, when application tables are created or suppressed, it is important to always maintain an up-to-date groups configuration.

All tables assigned to a ROLLBACKABLE group must have an explicit primary key (PRIMARY KEY clause in CREATE TABLE or ALTER TABLE).

E-Maj can process elementary partitions of partitionned tables created with the declarative DDL (with PostgreSQL 10+). They are processed as any other tables. However, as there is no need to protect mother tables, which remain empty, E-Maj refuses to include them in tables groups. All partitions of a partitionned table do not need to belong to a tables group. Partitions of a partitionned table can be assigned to different tables groups.

By their nature, TEMPORARY TABLE are not supported by E-Maj. UNLOGGED tables and tables created as WITH OIDS can only be members of AUDIT_ONLY tables groups.

If a sequence is associated to an application table, it is advisable to assign it into the same group as its table, so that, in case of E-maj rollback, the sequence can be reset to its state at the set mark time. If it were not the case, an E-Maj rollback would simply generate a hole in the sequence values.

E-Maj log tables and sequences should NOT be assigned in a tables group.

Specific tables properties

Four properties are associated to tables assigned to tables group:

  • the priority level,

  • the tablespace for log data,

  • the tablespace for log index,

  • the list of triggers whose state (ENABLED/DISABLED) must be left unchanged during E-Maj rollback operations.

The priority level is of type INTEGER. It is NULL by default. It defines a priority order in E-Maj tables processing. This can be espacialy useful at table lock time. Indeed, by locking tables in the same order as what is typically done by applications, it may reduce the risk of deadlock. E-Maj functions process tables in priority ascending order, NULL being processed last. For a same priority level, tables are processed in alphabetic order of schema name and table name.

To optimize performances of E-Maj installations having a large number of tables, it may be useful to spread log tables and their index on several tablespaces. Two properties are available to specify:

  • the name of the tablespace to use for the log table of an application table,

  • the name of the tablespace to use for the index of the log table.

By default, these properties have a NULL value, meaning that the default tablespace of the current session at tables group creation is used.

When an E-Maj rollback is performed on a tables group, enabled triggers of concerned tables are neutralized, so that table’s content changes generated by the operation do not fire them. But this by default behaviour can be changed if needed. Note that this does not concern E-Maj or system triggers.

Create a tables group

To create a tables group, just execute the following SQL statement:

SELECT emaj.emaj_create_group('<group.name>',<is_rollbackable>);

The second parameter, of type boolean, indicates whether the group’s type is ROLLBACKABLE (with value TRUE) or AUDIT_ONLY (with value FALSE). If this second parameter is not supplied, the group is considered ROLLBACKABLE.

The function returns the number of created groups, i.e. 1.

Assign tables and sequences into a tables group

Six functions allow to assign one or several tables or sequences to a group.

To add one or several tables into a tables group:

SELECT emaj.emaj_assign_table('<schema>', '<table>', '<groupe.name>' [,'<properties>' [,'<mark>']]);

or:

SELECT emaj.emaj_assign_tables('<schema>', '<tables.array>', '<group.name>' [,'<properties>' [,'<mark>']] );

or:

SELECT emaj.emaj_assign_tables('<schema>', '<tables.to.include.filter>', '<tables.to.exclude.filter>', '<group.name>' [,'<properties>' [,'<mark>']] );

To add one or several sequences into a tables group:

SELECT emaj.emaj_assign_sequence('<schema>', '<sequence>', '<group.name>' [,'<mark>'] );

or:

SELECT emaj.emaj_assign_sequences('<schema>', '<sequences.array>', '<group.name>' [,'<mark>'] );

or:

SELECT emaj.emaj_assign_sequences('<schema>', '<sequences.to.include.filter>', '<sequences.to.exclude.filter>', '<group.name>' [,'<mark>'] );

For functions processing several tables or sequences in a single operation, the list of tables or sequences to process is:

  • either provided by a parameter of type TEXT array,

  • or built with two regular expressions provided as parameters.

A TEXT array is typically expressed with a syntax like:

ARRAY[‘element_1’,’ element_2’, ...]

Both regular expressions follow the POSIX rules. Refer to the PostgreSQL documentation for more details. The first one defines a filter that selects the tables of the schema. The second one defines an exclusion filter applied on the selected tables. For instance:

To select all tables or sequences of the schema my_schema:

'my_schema', '.*', ''

To select all tables of this schema and whose name start with ‘tbl’:

'my_schema', '^tbl.*', ''

To select all tables of this schema and whose name start with ‘tbl’, except those who end with ‘_sav’:

'my_schema', '^tbl.*', '_sav$'

The functions assigning tables or sequences to tables groups that build their selection with regular expressions take into account the context of the tables or sequences. Are not selected for instance: tables or sequences already assigned, or tables without primary key for rollbackable groups, or tables declared UNLOGGED.

The <properties> parameter of functions that assign tables to a group allows to set values to some properties for the table or tables. Of type JSONB, its value can be set like this:

'{ "priority" : <n> ,
   "log_data_tablespace" : "<ldt>" ,
   "log_index_tablespace" : "<lit>" ,
   "ignored_triggers" : ["<tg1>" , "<tg2>" , ...] ,
   "ignored_triggers_profiles" : ["<regexp1>" , "<regexp2>" , ...] }'

where:

  • <n> is the priority level for the table or tables

  • <ldt> is the name of the tablespace to handle log tables

  • <lit> is the name of the tablespace to handle log indexes

  • <tg1> and <tg2> are trigger names

  • <regexp1> and <regexp2> are regular expressions that select triggers names among those that exist for the table or the tables to assign into the group

If one of these properties is not set, its value is considered NULL.

If specific tablespaces are referenced for any log table or log index, these tablespaces must exist before the function’s execution and the user must have been granted the CREATE privilege on them.

Both “ignored_triggers” and “ignored_triggers_profiles” properties define the triggers whose state must remain unchanged during E-Maj rollback operations. Both properties are of type array. “ignored_triggers” can be a simple string if it only contains one trigger.

Triggers listed in the “ignored_triggers” property must exist for the table or the tables referenced by the function call. The triggers created by E-Maj (emaj_log_trg and emj_trunc_trg) cannot appear in this list.

If several regular expressions are listed in the “ignored_triggers_profiles” property, they each act as a filter selecting triggers.

Both “ignored_triggers” and “ignored_triggers_profiles” properties can be used jointly. In this case, the selected triggers set is the union of those listed by the “ignored_triggers” property and those selected by each regular expression of the “ignored_triggers_profiles” property.

More details about the management of application triggers.

For all these functions, an exclusive lock is set on each table of the concerned table groups, so that the groups stability can be guaranted during these operations.

All these functions return the number of assigned tables or sequences.

The tables assignment functions create all the needed log tables, the log functions and triggers, as well as the triggers that process the execution of TRUNCATE SQL statements. They also create the log schemas if needed.

Drop a tables group

To drop a tables group previously created by the emaj_create_group() function, this group must be already in IDLE state. If it is not the case, the emaj_stop_group() function has to be used first.

Then, just execute the SQL command:

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

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

For this tables group, the emaj_drop_group() function drops all the objects that have been created by the assignment functions: log tables, sequences, functions and triggers.

The function also drops all log schemas that are now useless.

The locks set by this operation can lead to deadlock. If the deadlock processing impacts the execution of the E-Maj function, the error is trapped and the lock operation is repeated, with a maximum of 5 attempts.