Traces of operations

The emaj_hist table

All operations performed by E-Maj, and that impact in any way a tables group, are traced into a table named emaj_hist.

Any user having emaj_adm or emaj_viewer rights may look at the emaj_hist content.

The emaj_hist table structure is the following:

Column

Type

Description

hist_id

BIGSERIAL

serial number identifying a row in this history table

hist_datetime

TIMESTAMPTZ

recording date and time of the row

hist_function

TEXT

function associated to the traced event

hist_event

TEXT

kind of event

hist_object

TEXT

object related to the event (group, table or sequence)

hist_wording

TEXT

additional comments

hist_user

TEXT

role whose action has generated the event

hist_txid

BIGINT

identifier of the transaction that has generated the event

The hist_function column can take the following values:

Value

Meaning

ADJUST_GROUP_PROPERTIES

ajust the group_has_waiting_changes column content of the emaj_group table

ASSIGN_SEQUENCE

sequence assigned to a tables group

ASSIGN_SEQUENCES

sequences assigned to a tables group

ASSIGN_TABLE

table assigned to a tables group

ASSIGN_TABLES

tables assigned to a tables group

CLEANUP_RLBK_STATE

cleanup the state of recently completed rollback operations

COMMENT_GROUP

comment set on a group

COMMENT_MARK_GROUP

comment set on a mark for a tables group

COMMENT_ROLLBACK

comment set on an E-Maj rollback

CONSOLIDATE_RLBK_GROUP

consolidate a logged rollback operation

CREATE_GROUP

tables group creation

DBLINK_OPEN_CNX

open a dblink connection for a rollback operation

DBLINK_CLOSE_CNX

close a dblink connection for a rollback operation

DELETE_MARK_GROUP

mark deletion for a tables group

DISABLE_PROTECTION

desactivate event triggers

DROP_GROUP

tables group suppression

EMAJ_INSTALL

E-Maj installation or version update

ENABLE_PROTECTION

activate event triggers

EXPORT_GROUPS

export a tables groups configuration

EXPORT_PARAMETERS

export an E-maj parameters configuration

FORCE_DROP_GROUP

tables group forced suppression

FORCE_STOP_GROUP

tables group forced stop

FORGET_GROUP

erase historical traces for a dropped tables group

GEN_SQL_GROUP

generation of a psql script to replay updates for a tables group

GEN_SQL_GROUPS

generation of a psql script to replay updates for several tables groups

IMPORT_GROUPS

import a tables groups configuration

IMPORT_PARAMETERS

import an E-maj parameters configuration

LOCK_GROUP

lock set on tables of a group

LOCK_GROUPS

lock set on tables of several groups

LOCK_SESSION

lock set on tables for a rollback session

MODIFY_TABLE

table properties change

MODIFY_TABLES

tables properties change

MOVE_SEQUENCE

sequence moved to another tables group

MOVE_SEQUENCES

sequences moved to another tables group

MOVE_TABLE

table moved to another tables group

MOVE_TABLES

tables moved to another tables group

PROTECT_GROUP

set a protection against rollbacks on a group

PROTECT_MARK_GROUP

set a protection against rollbacks on a mark for a group

PURGE_HISTORIES

delete from the historical tables the events prior the retention delay

REMOVE_SEQUENCE

sequence removed from its tables group

REMOVE_SEQUENCES

sequences removed from their tables group

REMOVE_TABLE

table removed from its tables group

REMOVE_TABLES

tables removed from their tables group

RENAME_MARK_GROUP

mark rename for a tables group

RESET_GROUP

log tables content reset for a group

ROLLBACK_GROUP

rollback updates for a tables group

ROLLBACK_GROUPS

rollback updates for several tables groups

ROLLBACK_SEQUENCE

rollback one sequence

ROLLBACK_TABLE

rollback updates for one table

SET_MARK_GROUP

mark set on a tables group

SET_MARK_GROUPS

mark set on several tables groups

SNAP_GROUP

snap all tables and sequences for a group

SNAP_LOG_GROUP

snap all log tables for a group

START_GROUP

tables group start

START_GROUPS

tables groups start

STOP_GROUP

tables group stop

STOP_GROUPS

tables groups stop

UNPROTECT_GROUP

remove a protection against rollbacks on a group

UNPROTECT_MARK_GROUP

remove a protection against rollbacks on a mark for a group

The hist_event column can take the following values:

Value

Meaning

BEGIN

DELETED PARAMETER

parameter deleted from emaj_param

END

EVENT TRIGGER RECREATED

EVENT TRIGGERS DISABLED

EVENT TRIGGERS ENABLED

GROUP CREATED

new tables group created

INSERTED PARAMETER

parameter inserted into emaj_param

LOG DATA TABLESPACE CHANGED

tablespace for the log table modified

LOG INDEX TABLESPACE CHANGED

tablespace for the log index modified

LOG_SCHEMA CREATED

secondary schema created

LOG_SCHEMA DROPPED

secondary schema dropped

MARK DELETED

NAMES PREFIX CHANGED

E-Maj names prefix modified

NOTICE

warning message issued by a rollback

PRIORITY CHANGED

priority level modified

SEQUENCE ADDED

sequence added to a logging tables group

SEQUENCE MOVED

sequence moved from one group to another

SEQUENCE REMOVED

sequence removed from a logging tables group

TABLE ADDED

table added to a logging tables group

TABLE MOVED

table moved from one group to another

TABLE REMOVED

table removed from a logging tables group

TABLE REPAIRED

table repaired for E-Maj

TIME STAMP SET

internal time stamp recorded

TRIGGERS TO IGNORE CHANGED

set of application triggers to ignore at rollback time changed

UPDATED PARAMETER

parameter updated in emaj_param

WARNING

warning message issued by a rollback

Other history tables

Several other internal tables store historical data :

  • emaj_version_hist keeps the trace of the extension version changes;

  • emaj_group_hist records tables groups creations and drops;

  • emaj_rel_hist keeps tables and sequences assignments to tables groups;

  • emaj_log_session records the periods of time when the tables groups are enabled (started);

  • and several other tables handling E-Maj rollbacks data.

The Emaj_web client is the easiest way to examine these tables content.

Purge obsolete traces

When a tables group is started with reset (emaj_start_group() function), or when old marks are deleted (emaj_delete_before_mark_group() function), the oldest events are deleted from most historical tables. The events kept are those not older than:

  • a parametrised retention delay,

  • the oldest mark,

  • and the oldest uncompleted rollback operation.

By default, the retention delay for events equals 1 year. But this value can be modified at any time by inserting the history_retention parameter into the emaj_param table with a SQL statement. If the history_retention parameter is set to 100 years or more, no histories purge is executed.

The obsolete traces purge can also be initiated by explicitely calling the emaj_purge_histories() function. The input parameter of the function defines a retention delay that overloads the history_retention parameter of the emaj_param table.

In order to schedule purges periodically, it is possible to:

  • set the history_retention parameter to a very high value (for instance ‘100 YEARS’)

  • and schedule purge operations by any mean (crontab, pgAgent, pgTimeTable or any other tool).