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:
- stop the group, if it is in LOGGING state, using the emaj_stop_group() function,
- update the emaj_group_def table and/or modify the application schema,
- drop and recreate the tables group, using the emaj_drop_group() and emaj_create_group() functions.
But this last step can be also performed by the emaj_alter_group() function, with a statement like:
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.
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:
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.
|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>’]);
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.