Creating and Dropping Table Groups

Table Groups Configuration Principles

Configuring a table group consists of:

  • Defining the table group characteristics.

  • Defining the tables and sequences to assign to the group.

  • Optionally, defining specific properties for each table.

The Table Group

A table group is identified by its name. Thus, the name must be unique within the database. A table group name contains at least 1 character. It may contain spaces and/or any punctuation characters. However, it is advisable to avoid commas, single quotes, 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 table group is created. If it needs to be changed, the table group must be dropped and then recreated.

The Tables and Sequences to Assign

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

Not all tables in a schema need to be members of the same group. Some may belong to another group, and others may not belong to any group.

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

Caution

To guarantee the integrity of tables managed by E-Maj, it is essential to pay particular attention to the definition of table group content. If a table is missing, its content will be out of synchronization with other related tables after an E-Maj rollback operation. In particular, when application tables are created or dropped, it is important to always maintain an up-to-date group 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 partitioned tables created with declarative DDL. They are processed like any other table. Not all partitions of a partitioned table need to belong to a table group. Partitions of a partitioned table can be assigned to different table groups.

By their nature, TEMPORARY TABLES are not supported by E-Maj. UNLOGGED tables can only be members of AUDIT_ONLY table groups.

If a sequence is associated with an application table, it is advisable to assign it to the same group as its table. This ensures that, in the event of an E-Maj rollback, the sequence can be reset to its state at the time the mark was set. Otherwise, an E-Maj rollback would simply generate a gap in the sequence values.

E-Maj log tables and sequences should not be assigned to a table group.

Specific Table Properties

Four properties are associated with tables assigned to a table group:

  • The priority level.

  • The tablespace for log data.

  • The tablespace for log indexes.

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

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

To optimize the performance of E-Maj installations with a large number of tables, it may be useful to spread log tables and their indexes across multiple 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 table group creation is used.

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

Creating a Table Group

To create a table group, execute the following SQL statement:

SELECT emaj.emaj_create_group(p_groupName, p_isRollbackable, p_comment);

Input Parameters

  • p_groupName (TEXT): The name of the group to create.

  • p_isRollbackable (BOOLEAN, optional):

    • TRUE (default): The group is of type ROLLBACKABLE.

    • FALSE: The group is of type AUDIT_ONLY.

  • p_comment (TEXT, optional): Comment describing the group. If it is not provided or if it is set to NULL, no comment is registered for the group.

Returned data

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

Notes

The comment describing a table group can be modified or deleted later using the emaj_comment_group() function.

To insert a table group creation into an idempotent script, it is possible to condition the creation on its non-existence by using the emaj_does_exist_group() function in a WHERE clause.

Assigning Tables to a Table Group

Three functions allow assigning tables to a group.

To add a single table to a table group:

SELECT emaj.emaj_assign_table(p_schema, p_table, p_group, p_properties, p_mark);

To add several tables of a single schema to a table group at once:

SELECT emaj.emaj_assign_tables(p_schema, p_tables, p_group, p_properties, p_mark);

or:

SELECT emaj.emaj_assign_tables(p_schema, p_tablesIncludeFilter, p_tablesExludeFilter,
                               p_group, p_properties, p_mark);

Input Parameters

  • p_schema (TEXT): Schema holding the table(s) to assign.

  • p_table (TEXT): Name of the table to assign.

  • p_tables (TEXT[]): Names array of the tables to assign.

  • p_tablesIncludeFilter (TEXT): Regular expression to select tables.

  • p_tablesExludeFilter (TEXT): Regular expression to exclude tables.

  • p_group (TEXT): Target table group name.

  • p_properties (JSONB, optional): Set of table assignment properties (see notes below).

  • p_mark (TEXT, optional): Mark set if the target table group is in LOGGING state.

Returned data

These functions return the number of assigned tables.

Notes

For functions that process multiple tables at once, the list of tables to process is either provided by a parameter of type TEXT array, or built using two regular expressions.

A TEXT array is typically expressed with a syntax like:

ARRAY['element_1', 'element_2', ...]

Both regular expressions follow 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 to the selected tables. An inclusion filter set to NULL or an empty string selects no element. An exclusion filter set to NULL or an empty string excludes no element. For example:

To select all tables of the schema my_schema:

'my_schema', '.*', ''

To select all tables of this schema whose names start with tbl:

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

To select all tables of this schema whose names start with tbl, except those that end with _sav:

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

The functions that build their selection using regular expressions take into account the tables context. Tables are not selected if:

  • They are already assigned to any group.

  • They have no PRIMARY KEY and the target group is of type ROLLBACKABLE.

  • They are declared as UNLOGGED.

The <properties> parameter allows setting values for some properties of the table or tables. Of type JSONB, its value can be set as follows:

'{ "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 for log tables.

  • <lit> is the name of the tablespace for log indexes.

  • <tg1> and <tg2> are trigger names.

  • <regexp1> and <regexp2> are regular expressions that select trigger names from those that exist for the table or tables to assign to 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 execution, and the user must have been granted the CREATE privilege on them.

Both the 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. The ignored_triggers property can be a simple string if it contains only one trigger.

Triggers listed in the ignored_triggers property must exist for the table or 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 multiple regular expressions are listed in the ignored_triggers_profiles property, each acts 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 in the ignored_triggers property and those selected by each regular expression in the ignored_triggers_profiles property.

For more details, see management of application triggers.

If the target table group is currently in LOGGING state, a mark is automaticaly set. If a not NULL and not empty p_mark parameter is supplied, this mark name is used. Otherwise, a default ASSIGN_% is used, where % represents the current time expressed as hh.mm.ss.mmmm.

For all these functions, an exclusive lock is set on each table of the concerned table groups to ensure group stability during these operations.

These functions create the required log tables, log functions, and triggers. They also create the log schemas, if needed.

Assigning Sequences to a Table Group

Three functions allow assigning sequences to a group.

To add a single sequence to a table group:

SELECT emaj.emaj_assign_sequence(p_schema, p_sequence, p_group, p_mark);

To add several sequences of a single schema to a table group at once:

SELECT emaj.emaj_assign_sequences(p_schema, p_sequences, p_group, p_mark);

or:

SELECT emaj.emaj_assign_sequences(p_schema, p_sequencesIncludeFilter, p_sequencesExludeFilter,
                                  p_group, p_mark);

Input Parameters

  • p_schema (TEXT): Schema holding the sequence(s) to assign.

  • p_sequence (TEXT): Name of the sequence to assign.

  • p_sequences (TEXT[]): Names array of the sequences to assign.

  • p_sequencesIncludeFilter (TEXT): Regular expression to select sequences.

  • p_sequencesExludeFilter (TEXT): Regular expression to exclude sequences.

  • p_group (TEXT): Target table group name.

  • p_mark (TEXT, optional): Mark set if the target table group is in LOGGING state.

Returned data

These functions return the number of assigned sequences.

Notes

For functions that process multiple sequences at once, the list of sequences to process is either provided by a parameter of type TEXT array, or built using two regular expressions.

A TEXT array is typically expressed with a syntax like:

ARRAY['element_1', 'element_2', ...]

Both regular expressions follow POSIX rules. Refer to the PostgreSQL documentation for more details. The first one defines a filter that selects the sequences of the schema. The second one defines an exclusion filter applied to the selected sequences. An inclusion filter set to NULL or an empty string selects no element. An exclusion filter set to NULL or an empty string excludes no element. For example:

To select all sequences of the schema my_schema:

'my_schema', '.*', ''

To select all sequences of this schema whose names start with seq:

'my_schema', '^seq.*', ''

To select all sequences of this schema whose names start with seq, except those that end with _sav:

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

The function that assigns sequences to table groups based on regular expressions does not process sequences already assigned to any table group.

If the target table group is currently in LOGGING state, a mark is automaticaly set. If a not NULL and not empty p_mark parameter is supplied, this mark name is used. Otherwise, a default ASSIGN_% is used, where % represents the current time expressed as hh.mm.ss.mmmm.

Dropping a Table Group

To drop a table group previously created by the emaj_create_group() function, execute the SQL command:

SELECT emaj.emaj_drop_group(p_groupName);

Input Parameters

  • p_groupName (TEXT): The name of the group to drop.

Returned data

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

Notes

The group must already be in IDLE state. If it is not, the emaj_stop_group() function must be used first.

The function drops all objects that were created by the assignment functions: log tables, sequences, functions, and triggers. It also drops all log schemas that are no longer needed.

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

To insert a table group drop into an idempotent script, it is possible to condition the operation on the group’s existence by using the emaj_does_exist_group() function in a WHERE clause.