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 :ref:`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 :doc:`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. .. _table_emaj_properties: 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. ---- .. _emaj_create_group: Creating a Table Group ---------------------- To create a table group, execute the following SQL statement:: SELECT emaj.emaj_create_group(p_group, p_isRollbackable, p_comment); **Input Parameters** - ``p_group`` (*TEXT*): The name of the table **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 :ref:`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 :ref:`emaj_does_exist_group()` function in a *WHERE* clause. ---- .. _assign_table_sequence: .. _assign_table: 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. It may contain a generic ``%`` character, which is replaced by the current time with the pattern ``hh.mm.ss.mmmm``. If the parameter is not specified, or is empty or *NULL*, a name is automatically generated: ``ASSIGN_%``. **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 ```` parameter allows setting values for some properties of the table or tables. Of type *JSONB*, its value can be set as follows:: '{ "priority": , "log_data_tablespace": "", "log_index_tablespace": "", "ignored_triggers": ["", "", ...], "ignored_triggers_profiles": ["", "", ...] }' where: * ```` is the priority level for the table or tables. * ```` is the name of the tablespace for log tables. * ```` is the name of the tablespace for log indexes. * ```` and ```` are trigger names. * ```` and ```` 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 (or the *emaj_adm* role) 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 :ref:`management of application triggers`. If the target table group is currently in *LOGGING* state, a **mark** is automaticaly set. 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. ---- .. _assign_sequence: 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. It may contain a generic ``%`` character, which is replaced by the current time with the pattern ``hh.mm.ss.mmmm``. If the parameter is not specified, or is empty or *NULL*, a name is automatically generated: ``ASSIGN_%``. **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 end 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. ---- .. _emaj_drop_group: Dropping a Table Group ---------------------- To drop a table group previously created by the :ref:`emaj_create_group() ` function, execute the SQL command:: SELECT emaj.emaj_drop_group(p_group); **Input Parameters** - ``p_group`` (*TEXT*): The name of the tables **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 :ref:`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 :ref:`emaj_does_exist_group()` function in a *WHERE* clause.