The E-Maj Rollback Under the Hood

Planning and Execution

E-Maj rollbacks are complex operations. They can be logged or not, involve one or several table groups, with or without parallelism, and be launched by a direct SQL function call or by a client. Thus, E-Maj rollbacks are split into elementary steps.

An E-Maj rollback is executed in two phases: a planning phase and an execution phase.

The planning phase determines all the required elementary steps and estimates the execution duration. The estimate is computed for each step by taking into account:

  • Duration statistics of similar steps from previous rollback operations, stored in the emaj_rlbk_stat table.

  • Predefined parameters of the cost model.

Then, for parallel rollbacks, elementary steps are assigned to the requested number of sessions.

The emaj_estimate_rollback_group() function executes the planning phase and returns its result without proceeding to the execution phase.

The plan produced by the planning phase is recorded in the emaj_rlbk_plan table.

The E-Maj rollback execution phase simply chains the elementary steps of the built plan.

First, an EXCLUSIVE lock is set on all tables of the rolled-back table group or table groups, so that any attempt to change a table’s content from another client is blocked.

Then, for each table with changes to revert, the elementary steps are chained in ascending order:

  • Preparing application triggers.

  • Disabling E-Maj triggers.

  • Deleting or setting as DEFERRED foreign keys.

  • Rolling back the table.

  • Deleting the content of the log table.

  • Recreating or resetting the state of foreign keys.

  • Resetting the state of application triggers.

  • Re-enabling E-Maj triggers.

The processing of all sequences involved in the E-Maj rollback is performed by a single elementary step scheduled at the beginning of the operation.

For each elementary step, the function that drives the plan execution updates the emaj_rlbk_plan table. Reading this table’s content may provide interesting information about how the E-Maj rollback operation was processed.

If the dblink_user_password parameter is set, the emaj_rlbk_plan updates are processed in autonomous transactions, making it possible to monitor the rollback operation in real time. This is what the emaj_rollback_activity() function and the emajRollbackMonitor and Emaj_web clients do. If the dblink connection is not operational, the emaj_verify_all() function explains why.

Rolling Back a Table

Rolling back a table consists in resetting its content to the state at the time the E-Maj rollback target mark was set.

To optimize the operation and avoid executing one SQL statement for each elementary change, a table rollback executes just 4 global SQL statements:

  • Create and populate a temporary table containing all primary keys to process.

  • Delete from the table to process all rows corresponding to changes to revert of type INSERT and UPDATE.

  • ANALYZE the log table if the rollback is logged and if the number of changes is greater than 1000 (to avoid a poor execution plan for the last statement).

  • Insert into the table to process the oldest rows’ content corresponding to the changes to revert of type UPDATE and DELETE.

Foreign Keys Management

If a table processed by the rollback operation has a foreign key or is referenced by a foreign key belonging to another table, then this foreign key must be taken into account for the rollback execution.

Depending on the context, several behaviors exist.

For a given table, if all other tables linked to it by foreign keys belong to the same table group or table groups processed by the E-Maj rollback operation, reverting the changes on all tables will safely preserve referential integrity.

For this first case (which is the most frequent), the table rollback is executed with a session_replication_role parameter set to ‘replica’. In this mode, no check on foreign keys is performed while updating the table.

On the other hand, if tables are linked to other tables that do not belong to the table groups processed by the rollback operation or that are not included in any table groups, then it is essential that referential integrity be checked.

In this second case, checking referential integrity is performed:

  • Either by deferring the checks to the end of the transaction with a SET CONSTRAINTS ... DEFERRED statement executed if the key is declared DEFERRABLE INITIALLY IMMEDIATE.

  • Or by dropping the foreign key before rolling back the table and recreating it afterward.

The first option is chosen if the foreign key is declared DEFERRABLE and does not have an ON DELETE or ON UPDATE clause.

A foreign key defined on a partitioned table is only supported by E-Maj rollback operations if:

  • All tables/partitions linked by the foreign key belong to the same table groups to process.

  • The foreign key is of type DEFERRABLE.

  • The foreign key does not have an ON DELETE or ON UPDATE clause.

Other Integrity Constraints

Tables may have other integrity constraints: NOT NULL, CHECK, UNIQUE, and EXCLUDE. However, these constraints only concern the content of the table that holds them, without any link to other tables.

During an E-Maj rollback, these constraints are verified by PostgreSQL immediately at data change or at the transaction end for UNIQUE or EXCLUDE constraints that are defined as DEFERRED. Considering the way elementary tables are rolled back, no specific action is performed to support these constraints, and no integrity violation should occur if all these integrity constraints already existed when the rollback target mark was set.

Application Triggers Management

Triggers belonging to tables to roll back that are not E-Maj triggers are temporarily disabled during the operation. However, this default behavior can be adjusted when assigning a table to a table group or importing a table group configuration, by defining a trigger as “not to be disabled at rollback time”.

The technical way to disable or not disable the application triggers depends on the session_replication_role parameter value set for each table to roll back.

If session_replication_role equals ‘replica’, then the enabled triggers at the E-Maj rollback start are not called. If a trigger is declared as “not to be disabled”, it is temporarily changed into an ALWAYS trigger during the operation.

If session_replication_role keeps its default value, enabled triggers to neutralize are just temporarily disabled during the operation.

In a declarative partitioning context, it is possible to create a trigger on a partitioned table. As a result, each partition of the table inherits the trigger. There is no practical issue with this in E-Maj rollbacks.