Modifying tables groups

Several types of events may lead to alter a tables group:

  • the tables group definition may change, some tables or sequences may have been added or suppressed,
  • one of the parameters linked to a table (priority, schema, tablespaces,…) may have been modified,
  • the structure of one or several application tables of the tables group may have changed, such as an added or dropped column or a change in a column type.

Modifying a tables group in IDLE state

In all cases, the following steps can be performed:

But this last step can be also performed by the emaj_alter_group() function, with a statement like:

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

The function returns the number of tables and sequences that now belong to the tables group.

The emaj_alter_group() function also recreates E-Maj objects that may be missing (log tables, functions, …).

The function creates and drops the secondary schemas when needed.

Once altered, a tables group remains in IDLE state, but its log tables become empty.

The “ROLLBACKABLE” or “AUDIT_ONLY” characteristic of the tables group cannot be changed using the emaj_alter_group() function. To change it, the tables group must be dropped and re-created using the emaj_drop_group() and emaj_create_group() functions.

All actions that are chained by the emaj_alter_group() function are executed on behalf of a unique transaction. As a consequence, if an error occurs during the operation, the tables group remains in its previous state.

In most cases, executing the emaj_alter_group() function is much more efficient than chaining both emaj_drop_group() and emaj_create_group() functions.

It is possible to update the emaj_group_def table, when the tables group is in LOGGING state. However it will not have an effect until the group is altered (or dropped and re-created).

Using the emaj_alter_groups() function, several groups can be modified at once:

SELECT emaj.emaj_alter_groups('<group.names.array>');

This function allows to move a table or a sequence from one tables group to another in a single operation.

More information about multi-groups functions.

Modifying a tables group in LOGGING state

But the previous method has several drawbacks:

  • logs recorded before the operation are lost,
  • it is not possible to rollback a tables group to a previous state anymore.

However, some actions are possible while the tables groups are in LOGGING state. The following table lists the allowed actions.

Action LOGGING Group Method
Change the groupe ownership No  
Change the log schema suffix Yes emaj_group_def update
Change the E-Maj names prefix Yes emaj_group_def update
Change the log data tablespace Yes emaj_group_def update
Change the log index tablespace Yes emaj_group_def update
Change the E-Maj priority Yes emaj_group_def update
Remove a table from a group No  
Remove a sequence from a group No  
Add a table to a group No  
Add a sequence to a group No  
Repair a table or a sequence No  
Rename a table No  
Rename a sequence No  
Change the schema of a table No  
Change the schema of a sequence No  
Rename a table’s column No  
Change a table’s structure No  
Other forms of ALTER TABLE Yes No E-Maj impact
Other forms of ALTER SEQUENCE Yes No E-Maj impact

The “emaj_group_def update” method

Most attributes of the emaj_group_def table describing the tables groups can be dynamicaly changed while groups have not been stopped.

To do this, the following steps can be performed:

  • modify the emaj_group_def table,
  • call one of the emaj_alter_group() or emaj_alter_groups() functions.

For tables groups in LOGGING state, these functions set a ROW EXCLUSIVE lock on each application table of these groups.

On these same tables groups, they also set a mark whose name can be suppled as parameter. The syntax of these calls becomes:

SELECT emaj.emaj_alter_group('<group.name>' [,’<mark>’]);

or

SELECT emaj.emaj_alter_groups('<group.names.array>' [,’<mark>’]);

If the parameter representing the mark is not specified, or is empty or NULL, a name is automatically generated: “ALTER_%”, where the ‘%’ character represents the current transaction start time with a “hh.mn.ss.mmm” pattern.

An E-Maj rollback operation targeting a mark set before such groups changes does NOT automaticaly cancel these changes.

However, the administrator can apply the same procedure to reset a tables group to a prior state.