-- -- E-Maj: migration from 2.3.1 to 3.0.0 -- -- This software is distributed under the GNU General Public License. -- -- This script upgrades an existing installation of E-Maj extension. -- -- complain if this script is executed in psql, rather than via an ALTER EXTENSION statement \echo Use "ALTER EXTENSION emaj UPDATE TO..." to upgrade the E-Maj extension. \quit --SET client_min_messages TO WARNING; SET client_min_messages TO NOTICE; ------------------------------------ -- -- -- checks -- -- -- ------------------------------------ -- Check that the upgrade conditions are met. DO $do$ DECLARE v_emajVersion TEXT; v_groupList TEXT; BEGIN -- check the current role is a superuser PERFORM 0 FROM pg_roles WHERE rolname = current_user AND rolsuper; IF NOT FOUND THEN RAISE EXCEPTION 'E-Maj upgrade: the current user (%) is not a superuser.', current_user; END IF; -- the emaj version registered in emaj_param must be '2.3.1' SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version'; IF v_emajVersion <> '2.3.1' THEN RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 2.3.1',v_emajVersion; END IF; -- the installed postgres version must be at least 9.5 IF current_setting('server_version_num')::int < 90500 THEN RAISE EXCEPTION 'E-Maj upgrade: the current PostgreSQL version (%) is not compatible with the new E-Maj version. The PostgreSQL version should be at least 9.5.', current_setting('server_version'); END IF; -- the E-Maj environment is not damaged PERFORM * FROM (SELECT * FROM emaj.emaj_verify_all()) AS t(msg) WHERE msg <> 'No error detected'; IF FOUND THEN RAISE EXCEPTION 'E-Maj upgrade: the E-Maj environment is damaged. Please fix the issue before upgrading. You may execute "SELECT * FROM emaj.emaj_verify_all();" to get more details.'; END IF; -- no existing group must have been created with a postgres version prior 8.4 SELECT string_agg(group_name, ', ') INTO v_groupList FROM emaj.emaj_group WHERE cast(to_number(substring(group_pg_version FROM E'^(\\d+)'),'99') * 100 + to_number(substring(group_pg_version FROM E'^\\d+\\.(\\d+)'),'99') AS INTEGER) < 804; IF v_groupList IS NOT NULL THEN RAISE EXCEPTION 'E-Maj upgrade: groups "%" have been created with a too old postgres version (< 8.4). Drop these groups before upgrading. ',v_groupList; END IF; END; $do$; -- OK, the upgrade operation can start... -- insert the upgrade begin record in the operation history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('EMAJ_INSTALL','BEGIN','E-Maj 3.0.0', 'Upgrade from 2.3.1 started'); -- lock emaj_group table to avoid any concurrent E-Maj activity LOCK TABLE emaj.emaj_group IN EXCLUSIVE MODE; -- disable the event triggers SELECT emaj._disable_event_triggers(); ---------------------------------------------- -- -- -- emaj enums, tables, views and sequences -- -- -- ---------------------------------------------- -- -- process the emaj_group table -- -- create a temporary table with the old structure and copy the source content CREATE TEMP TABLE emaj_group_old (LIKE emaj.emaj_group); INSERT INTO emaj_group_old SELECT * FROM emaj.emaj_group; -- drop the old table ALTER EXTENSION emaj DROP TABLE emaj.emaj_group; DROP TABLE emaj.emaj_group CASCADE; -- create the new table, with its indexes, comment, constraints (except foreign key)... CREATE TABLE emaj.emaj_group ( group_name TEXT NOT NULL, group_is_rollbackable BOOLEAN NOT NULL, -- false for 'AUDIT_ONLY' and true for 'ROLLBACKABLE' groups group_creation_time_id BIGINT NOT NULL, -- time stamp of the group's creation group_pg_version TEXT NOT NULL -- postgres version at emaj_create_group() time DEFAULT substring (version() FROM E'PostgreSQL\\s([.,0-9,A-Z,a-z]*)'), group_last_alter_time_id BIGINT, -- time stamp of the last emaj_alter_group() call -- set to NULL at emaj_create_group() time group_has_waiting_changes BOOLEAN NOT NULL, -- are there recent changes in emaj_group_def not yet applied in emaj_group ? group_is_logging BOOLEAN NOT NULL, -- are log triggers activated ? -- true between emaj_start_group(s) and emaj_stop_group(s) -- false in other cases group_is_rlbk_protected BOOLEAN NOT NULL, -- is the group currently protected against rollback ? -- always true for AUDIT_ONLY groups group_nb_table INT, -- number of tables at emaj_create_group time group_nb_sequence INT, -- number of sequences at emaj_create_group time group_comment TEXT, -- optional user comment PRIMARY KEY (group_name), FOREIGN KEY (group_creation_time_id) REFERENCES emaj.emaj_time_stamp (time_id), FOREIGN KEY (group_last_alter_time_id) REFERENCES emaj.emaj_time_stamp (time_id) ); COMMENT ON TABLE emaj.emaj_group IS $$Contains created E-Maj groups.$$; -- Populate the new table. -- Initialize the new group_has_waiting_changes to false. -- (It will be reset to the proper value at the end of the script, once the new function that performs the task will be created.) INSERT INTO emaj.emaj_group ( group_name, group_is_rollbackable, group_creation_time_id, group_pg_version, group_last_alter_time_id, group_has_waiting_changes, group_is_logging, group_is_rlbk_protected, group_nb_table, group_nb_sequence, group_comment) SELECT group_name, group_is_rollbackable, group_creation_time_id, group_pg_version, group_last_alter_time_id, FALSE, group_is_logging, group_is_rlbk_protected, group_nb_table, group_nb_sequence, group_comment FROM emaj_group_old; -- recreate the foreign keys that point on this table ALTER TABLE emaj.emaj_relation ADD FOREIGN KEY (rel_group) REFERENCES emaj.emaj_group (group_name); ALTER TABLE emaj.emaj_mark ADD FOREIGN KEY (mark_group) REFERENCES emaj.emaj_group (group_name) ON DELETE CASCADE; -- -- process the emaj_relation table -- -- a new column is just added to the table ALTER TABLE emaj.emaj_relation ADD COLUMN rel_emaj_verb_attnum SMALLINT; -- set a value only for tables that have been previously removed from their group UPDATE emaj.emaj_relation SET rel_emaj_verb_attnum = attnum FROM pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND attrelid = pg_class.oid AND nspname = rel_log_schema AND relname = rel_log_table AND attname = 'emaj_verb' AND rel_kind = 'r'; -- -- process the emaj_mark table -- -- drop column mark_id ALTER EXTENSION emaj DROP SEQUENCE emaj.emaj_mark_mark_id_seq; ALTER TABLE emaj.emaj_mark DROP COLUMN mark_id; -- -- drop now useless temp tables -- DROP TABLE emaj_group_old; -- -- add created or recreated tables and sequences to the list of content to save by pg_dump -- SELECT pg_catalog.pg_extension_config_dump('emaj_group',''); ------------------------------------ -- -- -- emaj triggers -- -- -- ------------------------------------ -- Triggers for changes and truncate on the emaj_group_def table CREATE OR REPLACE FUNCTION emaj._emaj_group_def_change_fnct() RETURNS TRIGGER LANGUAGE plpgsql AS $_emaj_group_def_change_fnct$ -- This function is associated to the emaj_emaj_group_def_change_trg trigger set on the emaj_group_def table -- It sets the group_has_waiting_changes boolean column of the emaj_group table to TRUE when a change is recorded into the emaj_group_def table -- If the group doesn't exists (yet), the update statements will silently not update any row BEGIN IF (TG_OP = 'DELETE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = OLD.grpdef_group; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = OLD.grpdef_group OR group_name = NEW.grpdef_group; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = NEW.grpdef_group; RETURN NEW; ELSIF (TG_OP = 'TRUNCATE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE; RETURN NULL; END IF; RETURN NULL; END; $_emaj_group_def_change_fnct$; CREATE TRIGGER emaj_group_def_change_trg AFTER INSERT OR UPDATE OR DELETE ON emaj.emaj_group_def FOR EACH ROW EXECUTE PROCEDURE emaj._emaj_group_def_change_fnct(); CREATE TRIGGER emaj_group_def_truncate_trg AFTER TRUNCATE ON emaj.emaj_group_def FOR EACH STATEMENT EXECUTE PROCEDURE emaj._emaj_group_def_change_fnct(); ------------------------------------ -- -- -- emaj types -- -- -- ------------------------------------ -- drop the previous definition of the _verify_groups_type type and the _verify_groups() fonction that uses it DROP TYPE emaj._verify_groups_type CASCADE; CREATE TYPE emaj._verify_groups_type AS ( -- this type is not used by functions called by users ver_schema TEXT, ver_tblseq TEXT, ver_group TEXT, ver_msg TEXT ); COMMENT ON TYPE emaj._verify_groups_type IS $$Represents the structure of rows returned by the internal _verify_groups() function.$$; -- drop the previous definition of the emaj_consolidable_rollback_type type and the emaj_get_consolidable_rollbacks() fonction that uses it DROP TYPE emaj.emaj_consolidable_rollback_type CASCADE; CREATE TYPE emaj.emaj_consolidable_rollback_type AS ( cons_group TEXT, -- group name cons_target_rlbk_mark_name TEXT, -- name of the mark used as target of the logged rollback operation cons_target_rlbk_mark_time_id BIGINT, -- timestamp of the mark used as target of the logged rollback operation cons_end_rlbk_mark_name TEXT, -- name of the mark set at the end of the logged rollback operation cons_end_rlbk_mark_time_id BIGINT, -- timestamp of the mark set at the end of the logged rollback operation cons_rows BIGINT, -- estimated number of update events that can be consolidated for the rollback cons_marks INT -- number of marks that would be deleted by a consolidation ); COMMENT ON TYPE emaj.emaj_consolidable_rollback_type IS $$Represents the structure of rows returned by the emaj_get_consolidable_rollbacks() function.$$; -- add the new _check_conf_groups_type type CREATE TYPE emaj._check_conf_groups_type AS ( chk_msg_type INT, -- message number chk_severity INT, -- severity level: 1 = the error blocks any type group creation, -- 2 = the error only blocks ROLLBACKABLE groups creation chk_group TEXT, -- group name chk_schema TEXT, -- schema name chk_tblseq TEXT, -- table or sequence name chk_extra_data TEXT, -- additional piece of data that populates the error message, depending on the message type chk_message TEXT -- the formatted error message ); COMMENT ON TYPE emaj._check_conf_groups_type IS $$Represents the structure of rows returned by the _check_conf_groups() function.$$; ------------------------------------ -- -- -- drop event triggers -- -- -- ------------------------------------ -- the name of the function called by this event trigger has changed DROP EVENT TRIGGER IF EXISTS emaj_table_rewrite_trg CASCADE; DROP FUNCTION _emaj_event_trigger_table_rewrite_fnct(); ------------------------------------ -- -- -- emaj functions -- -- -- ------------------------------------ -- recreate functions that have been previously dropped in the tables structure upgrade step and will not be recreated later in this script -- pattern used by the tool that extracts and insert the functions definition ------------------------------------------------------------------ -- drop obsolete functions or functions with modified interface -- ------------------------------------------------------------------ DROP FUNCTION IF EXISTS emaj._check_groups_content(V_GROUPNAMES TEXT[],V_ISROLLBACKABLE BOOLEAN); DROP FUNCTION IF EXISTS emaj._gen_sql_tbl(R_REL EMAJ.EMAJ_RELATION,V_CONDITIONS TEXT); DROP FUNCTION IF EXISTS emaj._get_current_sequences_state(V_GROUPNAMES TEXT[],V_RELKIND TEXT,V_TIMEID BIGINT); DROP FUNCTION IF EXISTS emaj._delete_intermediate_mark_group(V_GROUPNAME TEXT,V_MARKNAME TEXT,V_MARKID BIGINT,V_MARKTIMEID BIGINT); ------------------------------------------------------------------ -- create new or modified functions -- ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION emaj._emaj_group_def_change_fnct() RETURNS TRIGGER LANGUAGE plpgsql AS $_emaj_group_def_change_fnct$ -- This function is associated to the emaj_emaj_group_def_change_trg trigger set on the emaj_group_def table -- It sets the group_has_waiting_changes boolean column of the emaj_group table to TRUE when a change is recorded into the emaj_group_def table -- If the group doesn't exists (yet), the update statements will silently not update any row BEGIN IF (TG_OP = 'DELETE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = OLD.grpdef_group; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = OLD.grpdef_group OR group_name = NEW.grpdef_group; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE WHERE group_name = NEW.grpdef_group; RETURN NEW; ELSIF (TG_OP = 'TRUNCATE') THEN UPDATE emaj.emaj_group SET group_has_waiting_changes = TRUE; RETURN NULL; END IF; RETURN NULL; END; $_emaj_group_def_change_fnct$; CREATE OR REPLACE FUNCTION emaj._pg_version_num() RETURNS INTEGER LANGUAGE SQL IMMUTABLE AS $$ -- This function returns as an integer the current postgresql version SELECT current_setting('server_version_num')::INT; $$; CREATE OR REPLACE FUNCTION emaj._check_conf_groups(v_groupNames TEXT[]) RETURNS SETOF emaj._check_conf_groups_type LANGUAGE plpgsql AS $_check_conf_groups$ -- This function verifies that the content of tables group as defined into the emaj_group_def table is correct. -- Any detected issue is reported as a message row. The caller defines what to do with them, depending on the tables group type. -- It is called by the emaj_create_group() and _alter_groups() functions. -- This function checks that the referenced application tables and sequences: -- - exist, -- - is not located into an E-Maj schema (to protect against an E-Maj recursive use), -- - do not already belong to another tables group, -- - will not generate conflicts on emaj objects to create (when emaj names prefix is not the default one) -- It also checks that: -- - tables are not TEMPORARY -- - for rollbackable groups, tables are not UNLOGGED or WITH OIDS -- - for rollbackable groups, all tables have a PRIMARY KEY -- - for sequences, the tablespaces, emaj log schema and emaj object name prefix are all set to NULL -- - for tables, configured tablespaces exist -- Input: name array of the tables groups to check BEGIN -- check that all application tables and sequences listed for the group really exist RETURN QUERY SELECT 1, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table or sequence %s.%s does not exist.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def WHERE grpdef_group = ANY(v_groupNames) AND (grpdef_schema, grpdef_tblseq) NOT IN ( SELECT nspname, relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND relkind IN ('r','S','p')); ---- check that no application table is a partitioned table (only elementary partitions can be managed by E-Maj) RETURN QUERY SELECT 2, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table %s.%s is a partitionned table (only elementary partitions are supported by E-Maj).', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = grpdef_schema AND relname = grpdef_tblseq AND grpdef_group = ANY(v_groupNames) AND relkind = 'p'; ---- check no application schema listed for the group in the emaj_group_def table is an E-Maj schema RETURN QUERY SELECT 3, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table or sequence %s.%s belongs to an E-Maj schema.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, emaj.emaj_schema WHERE grpdef_group = ANY(v_groupNames) AND grpdef_schema = sch_name; ---- check that no table or sequence of the checked groups already belongs to other created groups RETURN QUERY SELECT 4, 1, grpdef_group, grpdef_schema, grpdef_tblseq, rel_group, format('in the group %s, the table or sequence %s.%s already belongs to the group %s.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq), quote_ident(rel_group)) FROM emaj.emaj_group_def, emaj.emaj_relation WHERE grpdef_schema = rel_schema AND grpdef_tblseq = rel_tblseq AND upper_inf(rel_time_range) AND grpdef_group = ANY (v_groupNames) AND NOT rel_group = ANY (v_groupNames); ---- check no table is a TEMP table RETURN QUERY SELECT 5, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table %s.%s is a TEMPORARY table.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND relpersistence = 't'; ---- check that several tables of the group have not the same emaj names prefix RETURN QUERY WITH dupl_prefix AS ( SELECT coalesce(grpdef_emaj_names_prefix, grpdef_schema || '_' || grpdef_tblseq) AS prefix, count(*) FROM emaj.emaj_group_def WHERE grpdef_group = ANY (v_groupNames) GROUP BY 1 HAVING count(*) > 1) SELECT 10, 1, grpdef_group, grpdef_schema, grpdef_tblseq, prefix, format('in the group %s, the table %s.%s would have a duplicate emaj prefix "%s".', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq), prefix) FROM emaj.emaj_group_def, dupl_prefix WHERE coalesce(grpdef_emaj_names_prefix, grpdef_schema || '_' || grpdef_tblseq) = prefix AND grpdef_group = ANY (v_groupNames); ---- check that emaj names prefix that will be generared will not generate conflict with objects from existing groups RETURN QUERY WITH dupl_prefix AS ( SELECT coalesce(grpdef_emaj_names_prefix, grpdef_schema || '_' || grpdef_tblseq) AS prefix FROM emaj.emaj_group_def, emaj.emaj_relation WHERE coalesce(grpdef_emaj_names_prefix, grpdef_schema || '_' || grpdef_tblseq) || '_log' = rel_log_table AND grpdef_group = ANY (v_groupNames) AND NOT rel_group = ANY (v_groupNames) ) SELECT 11, 1, grpdef_group, grpdef_schema, grpdef_tblseq, prefix, format('in the group %s, the table %s.%s would have an already used emaj prefix "%s".', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq), prefix) FROM emaj.emaj_group_def, dupl_prefix WHERE coalesce(grpdef_emaj_names_prefix, grpdef_schema || '_' || grpdef_tblseq) = prefix AND grpdef_group = ANY (v_groupNames); ---- check that the log data tablespaces for tables exist RETURN QUERY SELECT 12, 1, grpdef_group, grpdef_schema, grpdef_tblseq, grpdef_log_dat_tsp, format('in the group %s, for the table %s.%s, the data log tablespace %s does not exist.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq), quote_ident(grpdef_log_dat_tsp)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND grpdef_log_dat_tsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = grpdef_log_dat_tsp); ---- check that the log index tablespaces for tables exist RETURN QUERY SELECT 13, 1, grpdef_group, grpdef_schema, grpdef_tblseq, grpdef_log_idx_tsp, format('in the group %s, for the table %s.%s, the index log tablespace %s does not exist.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq), quote_ident(grpdef_log_idx_tsp)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND grpdef_log_idx_tsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = grpdef_log_idx_tsp); ---- check no table is an unlogged table (blocking rollbackable groups only) RETURN QUERY SELECT 20, 2, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table %s.%s is an UNLOGGED table.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND relpersistence = 'u'; ---- check no table is a WITH OIDS table (blocking rollbackable groups only) RETURN QUERY SELECT 21, 2, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table %s.%s is declared WITH OIDS.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND relhasoids; ---- check every table has a primary key (blocking rollbackable groups only) RETURN QUERY SELECT 22, 2, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, the table %s.%s has no PRIMARY KEY.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'r' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_constraint WHERE relnamespace = pg_namespace.oid AND connamespace = pg_namespace.oid AND conrelid = pg_class.oid AND contype = 'p' AND nspname = grpdef_schema AND relname = grpdef_tblseq); ---- all sequences described in emaj_group_def have their log schema suffix attribute set to NULL RETURN QUERY SELECT 30, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, for the sequence %s.%s, the secondary log schema suffix is not NULL.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'S' AND grpdef_log_schema_suffix IS NOT NULL; ---- all sequences described in emaj_group_def have their emaj names prefix attribute set to NULL RETURN QUERY SELECT 31, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, for the sequence %s.%s, the emaj names prefix is not NULL.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'S' AND grpdef_emaj_names_prefix IS NOT NULL; ---- all sequences described in emaj_group_def have their data log tablespaces attributes set to NULL RETURN QUERY SELECT 32, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, for the sequence %s.%s, the data log tablespace is not NULL.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'S' AND grpdef_log_dat_tsp IS NOT NULL; ---- all sequences described in emaj_group_def have their index log tablespaces attributes set to NULL RETURN QUERY SELECT 33, 1, grpdef_group, grpdef_schema, grpdef_tblseq, NULL::TEXT, format('in the group %s, for the sequence %s.%s, the index log tablespace is not NULL.', quote_ident(grpdef_group), quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND grpdef_group = ANY (v_groupNames) AND relkind = 'S' AND grpdef_log_idx_tsp IS NOT NULL; -- RETURN; END; $_check_conf_groups$; CREATE OR REPLACE FUNCTION emaj._check_mark_name(v_groupNames TEXT[], v_mark TEXT, v_checkList TEXT) RETURNS TEXT LANGUAGE plpgsql AS $_check_mark_name$ -- This function verifies that a mark name exists for one or several groups -- It processes the EMAJ_LAST_MARK keyword. -- When several groups are supplied, it checks that the mark represents the same point in time for all groups. -- Input: array of group names, name of the mark to check, list of checks to perform (currently only 'ACTIVE') -- Output: internal name of the mark DECLARE v_markName TEXT = v_mark; v_groupList TEXT; v_count INTEGER; BEGIN -- process the 'EMAJ_LAST_MARK' keyword, if needed IF v_mark = 'EMAJ_LAST_MARK' THEN -- detect groups that have no recorded mark SELECT string_agg(group_name,', ' ORDER BY group_name), count(*) INTO v_groupList, v_count FROM (SELECT unnest(v_groupNames) EXCEPT SELECT mark_group FROM emaj.emaj_mark) AS t(group_name); IF v_count > 0 THEN IF v_count = 1 THEN RAISE EXCEPTION '_check_mark_name: The group "%" has no mark.', v_groupList; ELSE RAISE EXCEPTION '_check_mark_name: The groups "%" have no mark.', v_groupList; END IF; END IF; -- count the number of distinct lastest mark_time_id for all concerned groups SELECT count(DISTINCT mark_time_id) INTO v_count FROM (SELECT mark_group, max(mark_time_id) AS mark_time_id FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) GROUP BY 1) AS t; IF v_count > 1 THEN RAISE EXCEPTION '_check_mark_name: The EMAJ_LAST_MARK does not represent the same point in time for all groups.'; END IF; -- get the name of the last mark for the first group in the array, as we now know that all groups share the same last mark SELECT mark_name INTO v_markName FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] ORDER BY mark_time_id DESC LIMIT 1; ELSE -- for usual mark name (i.e. not EMAJ_LAST_MARK) -- check that the mark exists for all groups SELECT string_agg(group_name,', ' ORDER BY group_name), count(*) INTO v_groupList, v_count FROM (SELECT unnest(v_groupNames) EXCEPT SELECT mark_group FROM emaj.emaj_mark WHERE mark_name = v_markName) AS t(group_name); IF v_count > 0 THEN IF v_count = 1 THEN RAISE EXCEPTION '_check_mark_name: The mark "%" does not exist for the group "%".', v_markName, v_groupList; ELSE RAISE EXCEPTION '_check_mark_name: The mark "%" does not exist for the groups "%".', v_markName, v_groupList; END IF; END IF; -- check that the mark represents the same point in time for all groups SELECT count(DISTINCT mark_time_id) INTO v_count FROM emaj.emaj_mark WHERE mark_name = v_markName AND mark_group = ANY (v_groupNames); IF v_count > 1 THEN RAISE EXCEPTION '_check_mark_name: The mark "%" does not represent the same point in time for all groups.', v_markName; END IF; END IF; -- if requested, check the mark is active for all groups IF strpos(v_checkList,'ACTIVE') > 0 THEN SELECT string_agg(mark_group,', ' ORDER BY mark_group), count(*) INTO v_groupList, v_count FROM emaj.emaj_mark WHERE mark_name = v_markName AND mark_group = ANY(v_groupNames) AND mark_is_deleted; IF v_count = 1 THEN RAISE EXCEPTION '_check_mark_name: For the group "%", the mark "%" is DELETED.', v_groupList, v_markName; END IF; IF v_count > 1 THEN RAISE EXCEPTION '_check_mark_name: For the groups "%", the mark "%" is DELETED.', v_groupList, v_markName; END IF; END IF; RETURN v_markName; END; $_check_mark_name$; CREATE OR REPLACE FUNCTION emaj._check_marks_range(v_groupNames TEXT[], INOUT v_firstMark TEXT, INOUT v_lastMark TEXT, OUT v_firstMarkTimeId BIGINT, OUT v_lastMarkTimeId BIGINT) LANGUAGE plpgsql AS $_check_marks_range$ -- This function verifies that a marks range is valid for one or several groups. -- It checks that both marks defining the bounds exist and are in chronological order -- It processes the EMAJ_LAST_MARK keyword. -- If the first mark (lower bound) is NULL, find the first (deleted or not) mark known for each group. -- A last mark (upper bound) set to NULL means "the current situation". In this case, no specific checks is performed -- When several groups are supplied, it checks that the marks represent the same point in time for all groups. -- Input: array of group names, name of the first mark, name of the last mark -- Output: internal name and time id of both marks DECLARE v_groupList TEXT; v_count INTEGER; v_firstMarkTs TIMESTAMPTZ; v_lastMarkTs TIMESTAMPTZ; BEGIN -- if the first mark is NULL or empty, look for the first known mark for the group IF v_firstMark IS NULL OR v_firstMark = '' THEN -- detect groups that have no recorded mark SELECT string_agg(group_name,', ' ORDER BY group_name), count(*) INTO v_groupList, v_count FROM (SELECT unnest(v_groupNames) EXCEPT SELECT mark_group FROM emaj.emaj_mark) AS t(group_name); IF v_count > 0 THEN IF v_count <> array_length(v_groupNames, 1) THEN -- some but not all groups have no mark IF v_count = 1 THEN RAISE EXCEPTION '_check_marks_range: The group "%" has no mark.', v_groupList; ELSE RAISE EXCEPTION '_check_marks_range: The groups "%" have no mark.', v_groupList; END IF; ELSE -- all groups have no mark, force the first mark to NULL to be able to return statistics with 0 row v_firstMark = NULL; END IF; ELSE -- all groups have at least 1 mark -- count the number of distinct first mark_time_id for all concerned groups SELECT count(DISTINCT mark_time_id) INTO v_count FROM (SELECT mark_group, min(mark_time_id) AS mark_time_id FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) GROUP BY 1) AS t; IF v_count > 1 THEN RAISE EXCEPTION '_check_marks_range: The oldest marks of each group do not represent the same point in time.'; END IF; -- count the number of distinct first mark name for all concerned groups SELECT min(mark_time_id) INTO v_firstMarkTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1]; SELECT count(DISTINCT mark_name) INTO v_count FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) AND mark_time_id = v_firstMarkTimeId; IF v_count > 1 THEN RAISE EXCEPTION '_check_marks_range: The oldest marks of each group have not the same name.'; END IF; -- get the name of the first mark for the first group in the array, as we now know that all groups share the same first mark SELECT mark_name INTO v_firstMark FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] ORDER BY mark_time_id LIMIT 1; END IF; ELSE -- checks the supplied first mark SELECT emaj._check_mark_name (v_groupNames := v_groupNames, v_mark := v_firstMark, v_checkList := '') INTO v_firstMark; END IF; -- get some time data about the first mark (that may be NULL) -- (use the first group of the array, as we are now sure that all groups share the same mark) SELECT mark_time_id, time_clock_timestamp INTO v_firstMarkTimeId, v_firstMarkTs FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupNames[1] AND mark_name = v_firstMark; IF v_lastMark IS NOT NULL AND v_lastMark <> '' THEN -- if the last mark is not NULL or empty, check it SELECT emaj._check_mark_name (v_groupNames := v_groupNames, v_mark := v_lastMark, v_checkList := '') INTO v_lastMark; -- get some time data about the last mark (that may be NULL) -- (use the first group of the array, as we are now sure that all groups share the same mark) SELECT mark_time_id, time_clock_timestamp INTO v_lastMarkTimeId, v_lastMarkTs FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupNames[1] AND mark_name = v_lastMark; -- and check that the last mark has been set after the first mark IF v_firstMarkTimeId > v_lastMarkTimeId THEN RAISE EXCEPTION '_check_marks_range: The start mark "%" (%) has been set after the end mark "%" (%).', v_firstMark, v_firstMarkTs, v_lastMark, v_lastMarkTs; END IF; END IF; RETURN; END; $_check_marks_range$; CREATE OR REPLACE FUNCTION emaj._create_tbl(r_grpdef emaj.emaj_group_def, v_timeId BIGINT, v_isRollbackable BOOLEAN) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $_create_tbl$ -- This function creates all what is needed to manage the log and rollback operations for an application table -- Input: the emaj_group_def row related to the application table to process, the time id of the operation, a boolean indicating whether the group is rollbackable -- Are created in the log schema: -- - the associated log table, with its own sequence -- - the function that logs the tables updates, defined as a trigger -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of the application table. DECLARE v_emajSchema TEXT = 'emaj'; v_schemaPrefix TEXT = 'emaj'; v_emajNamesPrefix TEXT; v_baseLogTableName TEXT; v_baseLogIdxName TEXT; v_baseLogFnctName TEXT; v_baseSequenceName TEXT; v_logSchema TEXT; v_fullTableName TEXT; v_logTableName TEXT; v_logIdxName TEXT; v_logFnctName TEXT; v_sequenceName TEXT; v_dataTblSpace TEXT; v_idxTblSpace TEXT; v_colList TEXT; v_pkColList TEXT; v_pkCondList TEXT; v_attnum SMALLINT; v_alter_log_table_param TEXT; v_stmt TEXT; v_triggerList TEXT; BEGIN -- the checks on the table properties are performed by the calling functions -- build the prefix of all emaj object to create, by default _ v_emajNamesPrefix = coalesce(r_grpdef.grpdef_emaj_names_prefix, r_grpdef.grpdef_schema || '_' || r_grpdef.grpdef_tblseq); -- build the name of emaj components associated to the application table (non schema qualified and not quoted) v_baseLogTableName = v_emajNamesPrefix || '_log'; v_baseLogIdxName = v_emajNamesPrefix || '_log_idx'; v_baseLogFnctName = v_emajNamesPrefix || '_log_fnct'; v_baseSequenceName = v_emajNamesPrefix || '_log_seq'; -- build the different name for table, trigger, functions,... v_logSchema = coalesce(v_schemaPrefix || r_grpdef.grpdef_log_schema_suffix, v_emajSchema); v_fullTableName = quote_ident(r_grpdef.grpdef_schema) || '.' || quote_ident(r_grpdef.grpdef_tblseq); v_logTableName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseLogTableName); v_logIdxName = quote_ident(v_baseLogIdxName); v_logFnctName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseLogFnctName); v_sequenceName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseSequenceName); -- prepare TABLESPACE clauses for data and index v_dataTblSpace = coalesce('TABLESPACE ' || quote_ident(r_grpdef.grpdef_log_dat_tsp),''); v_idxTblSpace = coalesce('TABLESPACE ' || quote_ident(r_grpdef.grpdef_log_idx_tsp),''); -- Build some pieces of SQL statements that will be needed at table rollback time -- build the tables's columns list SELECT string_agg(col_name, ',') INTO v_colList FROM ( SELECT 'tbl.' || quote_ident(attname) AS col_name FROM pg_catalog.pg_attribute WHERE attrelid = v_fullTableName::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum) AS t; -- build the pkey columns list and the "equality on the primary key" conditions SELECT string_agg(col_pk_name, ','), string_agg(col_pk_cond, ' AND ') INTO v_pkColList, v_pkCondList FROM ( SELECT quote_ident(attname) AS col_pk_name, 'tbl.' || quote_ident(attname) || ' = keys.' || quote_ident(attname) AS col_pk_cond FROM pg_catalog.pg_attribute, pg_catalog.pg_index WHERE pg_attribute.attrelid = pg_index.indrelid AND attnum = ANY (indkey) AND indrelid = v_fullTableName::regclass AND indisprimary AND attnum > 0 AND attisdropped = FALSE ORDER BY attnum) AS t; -- create the log table: it looks like the application table, with some additional technical columns EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName; EXECUTE 'CREATE TABLE ' || v_logTableName || ' (LIKE ' || v_fullTableName || ') ' || v_dataTblSpace; EXECUTE 'ALTER TABLE ' || v_logTableName || ' ADD COLUMN emaj_verb VARCHAR(3),' || ' ADD COLUMN emaj_tuple VARCHAR(3),' || ' ADD COLUMN emaj_gid BIGINT NOT NULL DEFAULT nextval(''emaj.emaj_global_seq''),' || ' ADD COLUMN emaj_changed TIMESTAMPTZ DEFAULT clock_timestamp(),' || ' ADD COLUMN emaj_txid BIGINT DEFAULT txid_current(),' || ' ADD COLUMN emaj_user VARCHAR(32) DEFAULT session_user'; -- get the attnum of the emaj_verb column SELECT attnum INTO STRICT v_attnum FROM pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND attrelid = pg_class.oid AND nspname = v_logSchema AND relname = v_baseLogTableName AND attname = 'emaj_verb'; -- adjust the log table structure with the alter_log_table parameter, if set SELECT param_value_text INTO v_alter_log_table_param FROM emaj.emaj_param WHERE param_key = ('alter_log_table'); IF v_alter_log_table_param IS NOT NULL AND v_alter_log_table_param <> '' THEN EXECUTE 'ALTER TABLE ' || v_logTableName || ' ' || v_alter_log_table_param; END IF; -- create the index on the log table EXECUTE 'CREATE UNIQUE INDEX ' || v_logIdxName || ' ON ' || v_logTableName || ' (emaj_gid, emaj_tuple) ' || v_idxTblSpace; -- set the index associated to the primary key as cluster index. It may be useful for CLUSTER command. EXECUTE 'ALTER TABLE ONLY ' || v_logTableName || ' CLUSTER ON ' || v_logIdxName; -- remove the NOT NULL constraints of application columns. -- They are useless and blocking to store truncate event for tables belonging to audit_only tables SELECT string_agg(action, ',') INTO v_stmt FROM ( SELECT ' ALTER COLUMN ' || quote_ident(attname) || ' DROP NOT NULL' AS action FROM pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND attrelid = pg_class.oid AND nspname = v_logSchema AND relname = v_baseLogTableName AND attnum > 0 AND attnum < v_attnum AND attisdropped = FALSE AND attnotnull) AS t; IF v_stmt IS NOT NULL THEN EXECUTE 'ALTER TABLE ' || v_logTableName || v_stmt; END IF; -- create the sequence associated to the log table EXECUTE 'CREATE SEQUENCE ' || v_sequenceName; -- create the log function and the log trigger PERFORM emaj._create_log_trigger(v_fullTableName, v_logTableName, v_sequenceName, v_logFnctName); -- Deactivate the log trigger (it will be enabled at emaj_start_group time) EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_log_trg'; -- creation of the trigger that manage any TRUNCATE on the application table -- But the trigger is not immediately activated (it will be at emaj_start_group time) EXECUTE 'DROP TRIGGER IF EXISTS emaj_trunc_trg ON ' || v_fullTableName; IF v_isRollbackable THEN -- For rollbackable groups, use the common _forbid_truncate_fnct() function that blocks the operation EXECUTE 'CREATE TRIGGER emaj_trunc_trg' || ' BEFORE TRUNCATE ON ' || v_fullTableName || ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._forbid_truncate_fnct()'; ELSE -- For audit_only groups, use the common _log_truncate_fnct() function that records the operation into the log table EXECUTE 'CREATE TRIGGER emaj_trunc_trg' || ' BEFORE TRUNCATE ON ' || v_fullTableName || ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._log_truncate_fnct()'; END IF; EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_trunc_trg'; -- register the table into emaj_relation INSERT INTO emaj.emaj_relation (rel_schema, rel_tblseq, rel_time_range, rel_group, rel_priority, rel_log_schema, rel_log_dat_tsp, rel_log_idx_tsp, rel_kind, rel_log_table, rel_log_index, rel_log_sequence, rel_log_function, rel_sql_columns, rel_sql_pk_columns, rel_sql_pk_eq_conditions, rel_emaj_verb_attnum) VALUES (r_grpdef.grpdef_schema, r_grpdef.grpdef_tblseq, int8range(v_timeId, NULL, '[)'), r_grpdef.grpdef_group, r_grpdef.grpdef_priority, v_logSchema, r_grpdef.grpdef_log_dat_tsp, r_grpdef.grpdef_log_idx_tsp, 'r', v_baseLogTableName, v_baseLogIdxName, v_baseSequenceName, v_baseLogFnctName, v_colList, v_pkColList, v_pkCondList, v_attnum); -- -- check if the table has (neither internal - ie. created for fk - nor previously created by emaj) trigger SELECT string_agg(tgname, ', ' ORDER BY tgname) INTO v_triggerList FROM ( SELECT tgname FROM pg_catalog.pg_trigger WHERE tgrelid = v_fullTableName::regclass AND tgconstraint = 0 AND tgname NOT LIKE E'emaj\\_%\\_trg') AS t; -- if yes, issue a warning (if a trigger updates another table in the same table group or outside) it could generate problem at rollback time) IF v_triggerList IS NOT NULL THEN RAISE WARNING '_create_tbl: The table "%" has triggers (%). Verify the compatibility with emaj rollback operations (in particular if triggers update one or several other tables). Triggers may have to be manualy disabled before rollback.', v_fullTableName, v_triggerList; END IF; -- grant appropriate rights to both emaj roles EXECUTE 'GRANT SELECT ON TABLE ' || v_logTableName || ' TO emaj_viewer'; EXECUTE 'GRANT ALL PRIVILEGES ON TABLE ' || v_logTableName || ' TO emaj_adm'; EXECUTE 'GRANT SELECT ON SEQUENCE ' || v_sequenceName || ' TO emaj_viewer'; EXECUTE 'GRANT ALL PRIVILEGES ON SEQUENCE ' || v_sequenceName || ' TO emaj_adm'; RETURN; END; $_create_tbl$; CREATE OR REPLACE FUNCTION emaj._move_tbl(r_plan emaj.emaj_alter_plan, v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql AS $_move_tbl$ -- The function change the group ownership of a table. It is called during an alter group operation. -- Required inputs: row from emaj_alter_plan corresponding to the appplication table to proccess, time stamp id of the alter group operation BEGIN IF NOT r_plan.altr_group_is_logging AND NOT r_plan.altr_new_group_is_logging THEN -- no group is logging, so just adapt the last emaj_relation row related to the table UPDATE emaj.emaj_relation SET rel_group = r_plan.altr_new_group, rel_time_range = int8range(v_timeId, NULL, '[)') WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); ELSE -- register the end of the previous relation time frame and create a new relation time frame with the new group UPDATE emaj.emaj_relation SET rel_time_range = int8range(lower(rel_time_range),v_timeId,'[)') WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); INSERT INTO emaj.emaj_relation (rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind, rel_priority, rel_log_schema, rel_log_table, rel_log_dat_tsp, rel_log_index, rel_log_idx_tsp, rel_log_sequence, rel_log_function, rel_sql_columns, rel_sql_pk_columns, rel_sql_pk_eq_conditions,rel_log_seq_last_value, rel_emaj_verb_attnum) SELECT rel_schema, rel_tblseq, int8range(v_timeId, NULL, '[)'), r_plan.altr_new_group, rel_kind, rel_priority, rel_log_schema, rel_log_table, rel_log_dat_tsp, rel_log_index, rel_log_idx_tsp, rel_log_sequence, rel_log_function, rel_sql_columns, rel_sql_pk_columns, rel_sql_pk_eq_conditions, rel_log_seq_last_value, rel_emaj_verb_attnum FROM emaj.emaj_relation WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper(rel_time_range) = v_timeId; -- ... and insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('ALTER_GROUPS', 'TABLE MOVED', quote_ident(r_plan.altr_schema) || '.' || quote_ident(r_plan.altr_tblseq), 'From group ' || r_plan.altr_group || ' to group ' || r_plan.altr_new_group); END IF; RETURN; END; $_move_tbl$; CREATE OR REPLACE FUNCTION emaj._create_seq(grpdef emaj.emaj_group_def, v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql AS $_create_seq$ -- The function checks whether the sequence is related to a serial column of an application table. -- If yes, it verifies that this table also belong to the same group -- Required inputs: the emaj_group_def row related to the application sequence to process, the time id of the operation DECLARE v_tableSchema TEXT; v_tableName TEXT; v_tableGroup TEXT; BEGIN -- the checks on the sequence properties are performed by the calling functions -- get the schema and the name of the table that contains a serial or a "generated as identity" column this sequence is linked to, if one exists SELECT nt.nspname, ct.relname INTO v_tableSchema, v_tableName FROM pg_catalog.pg_class cs, pg_catalog.pg_namespace ns, pg_depend, pg_catalog.pg_class ct, pg_catalog.pg_namespace nt WHERE cs.relname = grpdef.grpdef_tblseq AND ns.nspname = grpdef.grpdef_schema -- the selected sequence AND cs.relnamespace = ns.oid -- join condition for sequence schema name AND ct.relnamespace = nt.oid -- join condition for linked table schema name AND pg_depend.objid = cs.oid -- join condition for the pg_depend table AND pg_depend.refobjid = ct.oid -- join conditions for depended table schema name AND pg_depend.classid = pg_depend.refclassid -- the classid et refclassid must be 'pg_class' AND pg_depend.classid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_class'); IF FOUND THEN SELECT grpdef_group INTO v_tableGroup FROM emaj.emaj_group_def WHERE grpdef_schema = v_tableSchema AND grpdef_tblseq = v_tableName; IF NOT FOUND THEN RAISE WARNING '_create_seq: The sequence %.% is linked to table %.% but this table does not belong to any tables group.', grpdef.grpdef_schema, grpdef.grpdef_tblseq, v_tableSchema, v_tableName; ELSE IF v_tableGroup <> grpdef.grpdef_group THEN RAISE WARNING '_create_seq: The sequence %.% is linked to table %.% but this table belong to another tables group (%).', grpdef.grpdef_schema, grpdef.grpdef_tblseq, v_tableSchema, v_tableName, v_tableGroup; END IF; END IF; END IF; -- record the sequence in the emaj_relation table INSERT INTO emaj.emaj_relation (rel_schema, rel_tblseq, rel_time_range, rel_group, rel_priority, rel_kind) VALUES (grpdef.grpdef_schema, grpdef.grpdef_tblseq, int8range(v_timeId, NULL, '[)'), grpdef.grpdef_group, grpdef.grpdef_priority, 'S'); RETURN; END; $_create_seq$; CREATE OR REPLACE FUNCTION emaj._gen_sql_tbl(r_rel emaj.emaj_relation, v_firstEmajGid BIGINT, v_lastEmajGid BIGINT) RETURNS BIGINT LANGUAGE plpgsql SECURITY DEFINER SET standard_conforming_strings = ON AS $_gen_sql_tbl$ -- This function generates SQL commands representing all updates performed on a table between 2 marks -- or beetween a mark and the current situation. These command are stored into a temporary table created -- by the _gen_sql_groups() calling function. -- Input: row from emaj_relation corresponding to the appplication table to proccess, -- the global sequence value at requested start and end marks -- Output: number of generated SQL statements DECLARE v_fullTableName TEXT; v_logTableName TEXT; v_valList TEXT; v_setList TEXT; v_pkCondList TEXT; v_unquotedType TEXT[] = array['smallint','integer','bigint','numeric','decimal', 'int2','int4','int8','serial','bigserial', 'real','double precision','float','float4','float8','oid']; v_rqInsert TEXT; v_rqUpdate TEXT; v_rqDelete TEXT; v_rqTruncate TEXT; v_conditions TEXT; v_lastEmajGidRel BIGINT; v_nbSQL BIGINT; r_col RECORD; BEGIN -- build schema specified table name and log table name v_fullTableName = quote_ident(r_rel.rel_schema) || '.' || quote_ident(r_rel.rel_tblseq); v_logTableName = quote_ident(r_rel.rel_log_schema) || '.' || quote_ident(r_rel.rel_log_table); -- retrieve from pg_attribute all columns of the application table and build : -- - the VALUES list used in the INSERT statements -- - the SET list used in the UPDATE statements v_valList = ''; v_setList = ''; FOR r_col IN SELECT attname, format_type(atttypid,atttypmod) FROM pg_catalog.pg_attribute WHERE attrelid = v_fullTableName ::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum LOOP -- test if the column format (up to the parenthesis) belongs to the list of formats that do not require any quotation (like numeric data types) IF regexp_replace(r_col.format_type,E'\\(.*$','') = ANY(v_unquotedType) THEN -- literal for this column can remain as is v_valList = v_valList || ''' || coalesce(o.' || quote_ident(r_col.attname) || '::TEXT,''NULL'') || '', '; v_setList = v_setList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || coalesce(n.' || quote_ident(r_col.attname) || ' ::TEXT,''NULL'') || '', '; ELSE -- literal for this column must be quoted v_valList = v_valList || ''' || quote_nullable(o.' || quote_ident(r_col.attname) || ') || '', '; v_setList = v_setList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_nullable(n.' || quote_ident(r_col.attname) || ') || '', '; END IF; END LOOP; -- suppress the final separators v_valList = substring(v_valList FROM 1 FOR char_length(v_valList) - 2); v_setList = substring(v_setList FROM 1 FOR char_length(v_setList) - 2); -- retrieve all columns that represents the pkey and build the "pkey equal" conditions set that will be used in UPDATE and DELETE statements -- (taking column names in pg_attribute from the table's definition instead of index definition is mandatory -- starting from pg9.0, joining tables with indkey instead of indexrelid) v_pkCondList = ''; FOR r_col IN SELECT attname, format_type(atttypid,atttypmod) FROM pg_catalog.pg_attribute, pg_catalog.pg_index WHERE pg_attribute.attrelid = pg_index.indrelid AND attnum = ANY (indkey) AND indrelid = v_fullTableName::regclass AND indisprimary AND attnum > 0 AND NOT attisdropped LOOP -- test if the column format (at least up to the parenthesis) belongs to the list of formats that do not require any quotation (like numeric data types) IF regexp_replace (r_col.format_type,E'\\(.*$','') = ANY(v_unquotedType) THEN -- literal for this column can remain as is -- v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || o.' || quote_ident(r_col.attname) || ' || ''::' || r_col.format_type || ' AND '; v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || o.' || quote_ident(r_col.attname) || ' || '' AND '; ELSE -- literal for this column must be quoted -- v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_literal(o.' || quote_ident(r_col.attname) || ') || ''::' || r_col.format_type || ' AND '; v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_literal(o.' || quote_ident(r_col.attname) || ') || '' AND '; END IF; END LOOP; -- suppress the final separator v_pkCondList = substring(v_pkCondList FROM 1 FOR char_length(v_pkCondList) - 5); -- prepare sql skeletons for each statement type v_rqInsert = '''INSERT INTO ' || replace(v_fullTableName,'''','''''') || ' VALUES (' || v_valList || ');'''; v_rqUpdate = '''UPDATE ONLY ' || replace(v_fullTableName,'''','''''') || ' SET ' || v_setList || ' WHERE ' || v_pkCondList || ';'''; v_rqDelete = '''DELETE FROM ONLY ' || replace(v_fullTableName,'''','''''') || ' WHERE ' || v_pkCondList || ';'''; v_rqTruncate = '''TRUNCATE ' || replace(v_fullTableName,'''','''''') || ';'''; -- build the restriction conditions on emaj_gid, depending on supplied marks range and the relation time range upper bound v_conditions = 'o.emaj_gid > ' || v_firstEmajGid; -- get the EmajGid of the relation time range upper bound, if any IF NOT upper_inf(r_rel.rel_time_range) THEN SELECT time_last_emaj_gid INTO v_lastEmajGidRel FROM emaj.emaj_time_stamp WHERE time_id = upper(r_rel.rel_time_range); END IF; -- if the relation time range upper bound is before the requested end mark, restrict the EmajGid upper limit IF v_lastEmajGidRel IS NOT NULL AND (v_lastEmajGid IS NULL OR (v_lastEmajGid IS NOT NULL AND v_lastEmajGidRel < v_lastEmajGid)) THEN v_lastEmajGid = v_lastEmajGidRel; END IF; -- complete the restriction conditions IF v_lastEmajGid IS NOT NULL THEN v_conditions = v_conditions || ' AND o.emaj_gid <= ' || v_lastEmajGid; END IF; -- now scan the log table to process all statement types at once EXECUTE 'INSERT INTO emaj_temp_script ' || 'SELECT o.emaj_gid, 0, o.emaj_txid, CASE ' || ' WHEN o.emaj_verb = ''INS'' THEN ' || v_rqInsert || ' WHEN o.emaj_verb = ''UPD'' AND o.emaj_tuple = ''OLD'' THEN ' || v_rqUpdate || ' WHEN o.emaj_verb = ''DEL'' THEN ' || v_rqDelete || ' WHEN o.emaj_verb = ''TRU'' THEN ' || v_rqTruncate || ' END ' || ' FROM ' || v_logTableName || ' o' || ' LEFT OUTER JOIN ' || v_logTableName || ' n ON n.emaj_gid = o.emaj_gid' || ' AND (n.emaj_verb = ''UPD'' AND n.emaj_tuple = ''NEW'') ' || ' WHERE NOT (o.emaj_verb = ''UPD'' AND o.emaj_tuple = ''NEW'')' || ' AND ' || v_conditions; GET DIAGNOSTICS v_nbSQL = ROW_COUNT; RETURN v_nbSQL; END; $_gen_sql_tbl$; CREATE OR REPLACE FUNCTION emaj._verify_groups(v_groups TEXT[], v_onErrorStop BOOLEAN) RETURNS SETOF emaj._verify_groups_type LANGUAGE plpgsql AS $_verify_groups$ -- The function verifies the consistency of a tables groups array. -- Input: - tables groups array, -- - a boolean indicating whether the function has to raise an exception in case of detected unconsistency. -- If onErrorStop boolean is false, it returns a set of _verify_groups_type records, one row per detected unconsistency, including the faulting schema and table or sequence names and a detailed message. -- If no error is detected, no row is returned. -- This function may be directly called by the Emaj_web client. DECLARE v_hint TEXT = 'You may use "SELECT * FROM emaj.emaj_verify_all()" to look for other issues.'; r_object RECORD; BEGIN -- Note that there is no check that the supplied groups exist. This has already been done by all calling functions. -- Let's start with some global checks that always raise an exception if an issue is detected -- check the postgres version: E-Maj needs postgres 9.5+ IF emaj._pg_version_num() < 90500 THEN RAISE EXCEPTION '_verify_groups : The current postgres version (%) is not compatible with this E-Maj version. It should be at least 9.5.', version(); END IF; -- OK, now look for groups unconsistency -- Unlike emaj_verify_all(), there is no direct check that application schemas exist -- check all application relations referenced in the emaj_relation table still exist FOR r_object IN SELECT t.rel_schema, t.rel_tblseq, r.rel_group, 'In group "' || r.rel_group || '", the ' || CASE WHEN t.rel_kind = 'r' THEN 'table "' ELSE 'sequence "' END || t.rel_schema || '"."' || t.rel_tblseq || '" does not exist any more.' AS msg FROM ( -- all relations currently belonging to the groups SELECT rel_schema, rel_tblseq, rel_kind FROM emaj.emaj_relation WHERE rel_group = ANY (v_groups) AND upper_inf(rel_time_range) EXCEPT -- all relations known by postgres SELECT nspname, relname, relkind FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND relkind IN ('r','S') ) AS t, emaj.emaj_relation r -- join with emaj_relation to get the group name WHERE t.rel_schema = r.rel_schema AND t.rel_tblseq = r.rel_tblseq AND upper_inf(r.rel_time_range) ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (1): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check the log table for all tables referenced in the emaj_relation table still exist FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the log table "' || rel_log_schema || '"."' || rel_log_table || '" is not found.' AS msg FROM emaj.emaj_relation WHERE rel_group = ANY (v_groups) AND rel_kind = 'r' AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_namespace, pg_catalog.pg_class WHERE nspname = rel_log_schema AND relname = rel_log_table AND relnamespace = pg_namespace.oid) ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (2): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check the log function for each table referenced in the emaj_relation table still exists FOR r_object IN -- the schema and table names are rebuilt from the returned function name SELECT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the log function "' || rel_log_schema || '"."' || rel_log_function || '" is not found.' AS msg FROM emaj.emaj_relation WHERE rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_proc, pg_catalog.pg_namespace WHERE nspname = rel_log_schema AND proname = rel_log_function AND pronamespace = pg_namespace.oid) ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (3): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check log and truncate triggers for all tables referenced in the emaj_relation table still exist -- start with log trigger FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the log trigger "emaj_log_trg" on table "' || rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg FROM emaj.emaj_relation WHERE rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_trigger, pg_catalog.pg_namespace, pg_catalog.pg_class WHERE nspname = rel_schema AND relname = rel_tblseq AND tgname = 'emaj_log_trg' AND tgrelid = pg_class.oid AND relnamespace = pg_namespace.oid) ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (4): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- then truncate trigger FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the truncate trigger "emaj_trunc_trg" on table "' || rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg FROM emaj.emaj_relation WHERE rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_trigger, pg_catalog.pg_namespace, pg_catalog.pg_class WHERE nspname = rel_schema AND relname = rel_tblseq AND tgname = 'emaj_trunc_trg' AND tgrelid = pg_class.oid AND relnamespace = pg_namespace.oid) ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (5): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check all log tables have a structure consistent with the application tables they reference -- (same columns and same formats). It only returns one row per faulting table. FOR r_object IN WITH cte_app_tables_columns AS ( -- application table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM emaj.emaj_relation, pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = rel_schema AND relname = rel_tblseq AND attrelid = pg_class.oid AND attnum > 0 AND attisdropped = FALSE AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range)), cte_log_tables_columns AS ( -- log table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM emaj.emaj_relation, pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = rel_log_schema AND relname = rel_log_table AND attrelid = pg_class.oid AND attnum > 0 AND attisdropped = FALSE AND attnum < rel_emaj_verb_attnum AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range)) SELECT DISTINCT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the structure of the application table "' || rel_schema || '"."' || rel_tblseq || '" is not coherent with its log table ("' || rel_log_schema || '"."' || rel_log_table || '").' AS msg FROM ( ( -- application table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM cte_app_tables_columns EXCEPT -- minus log table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM cte_log_tables_columns ) UNION ( -- log table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM cte_log_tables_columns EXCEPT -- minus application table's columns SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod FROM cte_app_tables_columns )) AS t ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (6): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check all tables have their primary key if they belong to a rollbackable group FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In rollbackable group "' || rel_group || '", the table "' || rel_schema || '"."' || rel_tblseq || '" has no primary key any more.' AS msg FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND group_is_rollbackable AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_constraint WHERE nspname = rel_schema AND relname = rel_tblseq AND relnamespace = pg_namespace.oid AND connamespace = pg_namespace.oid AND conrelid = pg_class.oid AND contype = 'p') ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (7): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- for rollbackable groups, check no table has been altered as UNLOGGED or dropped and recreated as TEMP table after tables groups creation FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In rollbackable group "' || rel_group || '", the table "' || rel_schema || '"."' || rel_tblseq || '" is UNLOGGED or TEMP.' AS msg FROM emaj.emaj_relation, pg_catalog.pg_class, pg_catalog.pg_namespace, emaj.emaj_group WHERE relnamespace = pg_namespace.oid AND nspname = rel_schema AND relname = rel_tblseq AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND group_name = rel_group AND group_is_rollbackable AND relpersistence <> 'p' ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (8): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- for rollbackable groups, check no table has been altered as WITH OIDS after tables groups creation FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In rollbackable group "' || rel_group || '", the table "' || rel_schema || '"."' || rel_tblseq || '" is declared WITH OIDS.' AS msg FROM emaj.emaj_relation, pg_catalog.pg_class, pg_catalog.pg_namespace, emaj.emaj_group WHERE relnamespace = pg_namespace.oid AND nspname = rel_schema AND relname = rel_tblseq AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND group_name = rel_group AND group_is_rollbackable AND relhasoids ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (9): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check the primary key structure of all tables belonging to rollbackable groups is unchanged FOR r_object IN SELECT rel_schema, rel_tblseq, rel_group, 'In rollbackable group "' || rel_group || '", the primary key of the table "' || rel_schema || '"."' || rel_tblseq || '" has changed (' || rel_sql_pk_columns || ' => ' || current_pk_columns || ').' AS msg FROM ( SELECT rel_schema, rel_tblseq, rel_group, rel_sql_pk_columns, string_agg(quote_ident(attname), ',' ORDER BY attnum) AS current_pk_columns FROM emaj.emaj_relation, emaj.emaj_group, pg_catalog.pg_attribute, pg_catalog.pg_index, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE -- join conditions rel_group = group_name AND relname = rel_tblseq AND nspname = rel_schema AND pg_attribute.attrelid = pg_index.indrelid AND indrelid = pg_class.oid AND relnamespace = pg_namespace.oid -- filter conditions AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND group_is_rollbackable AND attnum = ANY (indkey) AND indisprimary AND attnum > 0 AND attisdropped = FALSE GROUP BY rel_schema, rel_tblseq, rel_group, rel_sql_pk_columns ) AS t WHERE rel_sql_pk_columns <> current_pk_columns ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (10): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- check all log tables have the 6 required technical columns. It only returns one row per faulting table. FOR r_object IN SELECT DISTINCT rel_schema, rel_tblseq, rel_group, 'In group "' || rel_group || '", the log table "' || rel_log_schema || '"."' || rel_log_table || '" miss some technical columns (' || string_agg(attname,', ') || ').' AS msg FROM ( SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname FROM emaj.emaj_relation, (VALUES ('emaj_verb'), ('emaj_tuple'), ('emaj_gid'), ('emaj_changed'), ('emaj_txid'), ('emaj_user')) AS t(attname) WHERE rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND EXISTS (SELECT NULL FROM pg_catalog.pg_namespace, pg_catalog.pg_class WHERE nspname = rel_log_schema AND relname = rel_log_table AND relnamespace = pg_namespace.oid) EXCEPT SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname FROM emaj.emaj_relation, pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = rel_log_schema AND relname = rel_log_table AND attrelid = pg_class.oid AND attnum > 0 AND attisdropped = FALSE AND attname IN ('emaj_verb', 'emaj_tuple', 'emaj_gid', 'emaj_changed', 'emaj_txid', 'emaj_user') AND rel_group = ANY (v_groups) AND rel_kind = 'r' AND upper_inf(rel_time_range) ) AS t2 GROUP BY rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table ORDER BY 1,2,3 LOOP IF v_onErrorStop THEN RAISE EXCEPTION '_verify_groups (11): % %',r_object.msg,v_hint; END IF; RETURN NEXT r_object; END LOOP; -- RETURN; END; $_verify_groups$; CREATE OR REPLACE FUNCTION emaj.emaj_create_group(v_groupName TEXT, v_isRollbackable BOOLEAN DEFAULT TRUE, v_is_empty BOOLEAN DEFAULT FALSE) RETURNS INT LANGUAGE plpgsql AS $emaj_create_group$ -- This function creates emaj objects for all tables of a group -- It also creates the secondary E-Maj schemas when needed -- Input: group name, -- boolean indicating whether the group is rollbackable or not (true by default), -- boolean explicitely indicating whether the group is empty or not -- Output: number of processed tables and sequences DECLARE v_timeId BIGINT; v_nbTbl INT = 0; v_nbSeq INT = 0; r_grpdef emaj.emaj_group_def%ROWTYPE; r RECORD; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('CREATE_GROUP', 'BEGIN', v_groupName, CASE WHEN v_isRollbackable THEN 'rollbackable' ELSE 'audit_only' END); -- check that the group name is valid IF v_groupName IS NULL OR v_groupName = ''THEN RAISE EXCEPTION 'emaj_create_group: The group name can''t be NULL or empty.'; END IF; -- check that the group is not yet recorded in emaj_group table PERFORM 0 FROM emaj.emaj_group WHERE group_name = v_groupName; IF FOUND THEN RAISE EXCEPTION 'emaj_create_group: The group "%" already exists.', v_groupName; END IF; -- check the consistency between the emaj_group_def table content and the v_is_empty input parameter PERFORM 0 FROM emaj.emaj_group_def WHERE grpdef_group = v_groupName LIMIT 1; IF NOT v_is_empty AND NOT FOUND THEN RAISE EXCEPTION 'emaj_create_group: The group "%" is unknown in the emaj_group_def table. To create an empty group, explicitely set the third parameter to true.', v_groupName; END IF; IF v_is_empty AND FOUND THEN RAISE EXCEPTION 'emaj_create_group: The group "%" is referenced into the emaj_group_def table. This is not consistent with the parameter set to true.', v_groupName; END IF; -- performs various checks on the group's content described in the emaj_group_def table FOR r IN SELECT chk_message FROM emaj._check_conf_groups(ARRAY[v_groupName]) WHERE (v_isRollbackable AND chk_severity <= 2) OR (NOT v_isRollbackable AND chk_severity <= 1) ORDER BY chk_msg_type, chk_group, chk_schema, chk_tblseq LOOP RAISE WARNING 'emaj_create_group: %', r.chk_message; END LOOP; IF FOUND THEN RAISE EXCEPTION 'emaj_create_group: One or several errors have been detected in the emaj_group_def table content.'; END IF; -- OK -- get the time stamp of the operation SELECT emaj._set_time_stamp('C') INTO v_timeId; -- insert the row describing the group into the emaj_group table -- (The group_is_rlbk_protected boolean column is always initialized as not group_is_rollbackable) INSERT INTO emaj.emaj_group (group_name, group_is_rollbackable, group_creation_time_id, group_has_waiting_changes, group_is_logging, group_is_rlbk_protected) VALUES (v_groupName, v_isRollbackable, v_timeId, FALSE, FALSE, NOT v_isRollbackable); -- create new E-Maj secondary schemas, if needed PERFORM emaj._create_log_schemas('CREATE_GROUP', ARRAY[v_groupName]); -- get and process all tables of the group (in priority order, NULLS being processed last) FOR r_grpdef IN SELECT emaj.emaj_group_def.* FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_group = v_groupName AND relnamespace = pg_namespace.oid AND nspname = grpdef_schema AND relname = grpdef_tblseq AND relkind = 'r' ORDER BY grpdef_priority, grpdef_schema, grpdef_tblseq LOOP PERFORM emaj._create_tbl(r_grpdef, v_timeId, v_isRollbackable); v_nbTbl = v_nbTbl + 1; END LOOP; -- get and process all sequences of the group (in priority order, NULLS being processed last) FOR r_grpdef IN SELECT emaj.emaj_group_def.* FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_group = v_groupName AND relnamespace = pg_namespace.oid AND nspname = grpdef_schema AND relname = grpdef_tblseq AND relkind = 'S' ORDER BY grpdef_priority, grpdef_schema, grpdef_tblseq LOOP PERFORM emaj._create_seq(r_grpdef, v_timeId); v_nbSeq = v_nbSeq + 1; END LOOP; -- update tables and sequences counters in the emaj_group table UPDATE emaj.emaj_group SET group_nb_table = v_nbTbl, group_nb_sequence = v_nbSeq WHERE group_name = v_groupName; -- check foreign keys with tables outside the group PERFORM emaj._check_fk_groups(array[v_groupName]); -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('CREATE_GROUP', 'END', v_groupName, v_nbTbl + v_nbSeq || ' tables/sequences processed'); RETURN v_nbTbl + v_nbSeq; END; $emaj_create_group$; COMMENT ON FUNCTION emaj.emaj_create_group(TEXT,BOOLEAN,BOOLEAN) IS $$Creates an E-Maj group.$$; CREATE OR REPLACE FUNCTION emaj._alter_groups(v_groupNames TEXT[], v_multiGroup BOOLEAN, v_mark TEXT) RETURNS INT LANGUAGE plpgsql AS $_alter_groups$ -- This function effectively alters a tables groups array. -- It takes into account the changes recorded in the emaj_group_def table since the groups have been created. -- Input: group names array, flag indicating whether the function is called by the multi-group function or not -- Output: number of tables and sequences belonging to the groups after the operation DECLARE v_loggingGroups TEXT[]; v_markName TEXT; v_timeId BIGINT; v_eventTriggers TEXT[]; r RECORD; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (CASE WHEN v_multiGroup THEN 'ALTER_GROUPS' ELSE 'ALTER_GROUP' END, 'BEGIN', array_to_string(v_groupNames,',')); -- check the group names SELECT emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := v_multiGroup, v_lockGroups := TRUE, v_checkList := '') INTO v_groupNames; IF v_groupNames IS NOT NULL THEN -- performs various checks on the groups content described in the emaj_group_def table FOR r IN SELECT chk_message FROM emaj._check_conf_groups(v_groupNames), emaj.emaj_group WHERE chk_group = group_name AND ((group_is_rollbackable AND chk_severity <= 2) OR (NOT group_is_rollbackable AND chk_severity <= 1)) ORDER BY chk_msg_type, chk_group, chk_schema, chk_tblseq LOOP RAISE WARNING '_alter_groups: %', r.chk_message; END LOOP; IF FOUND THEN RAISE EXCEPTION '_alter_groups: One or several errors have been detected in the emaj_group_def table content.'; END IF; -- build the list of groups that are in logging state SELECT array_agg(group_name ORDER BY group_name) INTO v_loggingGroups FROM emaj.emaj_group WHERE group_name = ANY(v_groupNames) AND group_is_logging; -- check and process the supplied mark name, if it is worth to be done IF v_loggingGroups IS NOT NULL THEN SELECT emaj._check_new_mark(v_groupNames, v_mark) INTO v_markName; END IF; -- OK -- get the time stamp of the operation SELECT emaj._set_time_stamp('A') INTO v_timeId; -- for LOGGING groups, lock all tables to get a stable point IF v_loggingGroups IS NOT NULL THEN -- use a ROW EXCLUSIVE lock mode, preventing for a transaction currently updating data, but not conflicting with simple read access or vacuum operation. PERFORM emaj._lock_groups(v_loggingGroups, 'ROW EXCLUSIVE', v_multiGroup); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, v_multiGroup, TRUE, NULL, v_timeId); END IF; -- disable event triggers that protect emaj components and keep in memory these triggers name SELECT emaj._disable_event_triggers() INTO v_eventTriggers; -- we can now plan all the steps needed to perform the operation PERFORM emaj._alter_plan(v_groupNames, v_timeId); -- create the needed secondary schemas PERFORM emaj._create_log_schemas(CASE WHEN v_multiGroup THEN 'ALTER_GROUPS' ELSE 'ALTER_GROUP' END, v_groupNames); -- execute the plan PERFORM emaj._alter_exec(v_timeId, v_multiGroup); -- drop the E-Maj secondary schemas that are now useless (i.e. not used by any created group) PERFORM emaj._drop_log_schemas(CASE WHEN v_multiGroup THEN 'ALTER_GROUPS' ELSE 'ALTER_GROUP' END, FALSE); -- update some attributes in the emaj_group table UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, group_has_waiting_changes = FALSE, group_nb_table = (SELECT count(*) FROM emaj.emaj_relation WHERE rel_group = group_name AND upper_inf(rel_time_range) AND rel_kind = 'r'), group_nb_sequence = (SELECT count(*) FROM emaj.emaj_relation WHERE rel_group = group_name AND upper_inf(rel_time_range) AND rel_kind = 'S') WHERE group_name = ANY (v_groupNames); -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); -- check foreign keys with tables outside the groups PERFORM emaj._check_fk_groups(v_groupNames); END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'ALTER_GROUPS' ELSE 'ALTER_GROUP' END, 'END', array_to_string(v_groupNames,','), 'Timestamp Id : ' || v_timeId ); -- and return RETURN sum(group_nb_table) + sum(group_nb_sequence) FROM emaj.emaj_group WHERE group_name = ANY (v_groupNames); END; $_alter_groups$; CREATE OR REPLACE FUNCTION emaj._start_groups(v_groupNames TEXT[], v_mark TEXT, v_multiGroup BOOLEAN, v_resetLog BOOLEAN) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER AS $_start_groups$ -- This function activates the log triggers of all the tables for one or several groups and set a first mark -- It also delete oldest rows in emaj_hist table -- Input: array of group names, name of the mark to set, boolean indicating whether the function is called by a multi group function, boolean indicating whether the function must reset the group at start time -- Output: number of processed tables -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of application tables and sequences. DECLARE v_nbTblSeq INT = 0; v_markName TEXT; v_fullTableName TEXT; v_eventTriggers TEXT[]; r_tblsq RECORD; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'START_GROUPS' ELSE 'START_GROUP' END, 'BEGIN', array_to_string(v_groupNames,','), CASE WHEN v_resetLog THEN 'With log reset' ELSE 'Without log reset' END); -- check the group names SELECT emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := v_multiGroup, v_lockGroups := TRUE, v_checkList := 'IDLE') INTO v_groupNames; IF v_groupNames IS NOT NULL THEN -- if there is at least 1 group to process, go on -- check that no group is damaged PERFORM 0 FROM emaj._verify_groups(v_groupNames, TRUE); -- check foreign keys with tables outside the group PERFORM emaj._check_fk_groups(v_groupNames); -- purge the emaj history, if needed PERFORM emaj._purge_hist(); -- if requested by the user, call the emaj_reset_groups() function to erase remaining traces from previous logs if v_resetLog THEN PERFORM emaj._reset_groups(v_groupNames); -- drop the secondary schemas that would have been emptied by the _reset_groups() call SELECT emaj._disable_event_triggers() INTO v_eventTriggers; PERFORM emaj._drop_log_schemas(CASE WHEN v_multiGroup THEN 'START_GROUPS' ELSE 'START_GROUP' END, FALSE); PERFORM emaj._enable_event_triggers(v_eventTriggers); END IF; -- check the supplied mark name (the check must be performed after the _reset_groups() call to allow to reuse an old mark name that is being deleted IF v_mark IS NULL OR v_mark = '' THEN v_mark = 'START_%'; END IF; SELECT emaj._check_new_mark(v_groupNames, v_mark) INTO v_markName; -- OK, lock all tables to get a stable point -- one sets the locks at the beginning of the operation (rather than let the ALTER TABLE statements set their own locks) to decrease the risk of deadlock. PERFORM emaj._lock_groups(v_groupNames,'SHARE ROW EXCLUSIVE',v_multiGroup); -- enable all log triggers for the groups -- for each relation currently belonging to the group, FOR r_tblsq IN SELECT rel_priority, rel_schema, rel_tblseq, rel_kind FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) ORDER BY rel_priority, rel_schema, rel_tblseq LOOP CASE r_tblsq.rel_kind WHEN 'r' THEN -- if it is a table, enable the emaj log and truncate triggers v_fullTableName = quote_ident(r_tblsq.rel_schema) || '.' || quote_ident(r_tblsq.rel_tblseq); EXECUTE 'ALTER TABLE ' || v_fullTableName || ' ENABLE TRIGGER emaj_log_trg, ENABLE TRIGGER emaj_trunc_trg'; WHEN 'S' THEN -- if it is a sequence, nothing to do END CASE; v_nbTblSeq = v_nbTblSeq + 1; END LOOP; -- update the state of the group row from the emaj_group table UPDATE emaj.emaj_group SET group_is_logging = TRUE WHERE group_name = ANY (v_groupNames); -- Set the first mark for each group PERFORM emaj._set_mark_groups(v_groupNames, v_markName, v_multiGroup, TRUE); END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'START_GROUPS' ELSE 'START_GROUP' END, 'END', array_to_string(v_groupNames,','), v_nbTblSeq || ' tables/sequences processed'); -- RETURN v_nbTblSeq; END; $_start_groups$; CREATE OR REPLACE FUNCTION emaj._stop_groups(v_groupNames TEXT[], v_mark TEXT, v_multiGroup BOOLEAN, v_isForced BOOLEAN) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER AS $_stop_groups$ -- This function effectively de-activates the log triggers of all the tables for a group. -- Input: array of group names, a mark name to set, and a boolean indicating if the function is called by a multi group function -- Output: number of processed tables and sequences -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of application tables and sequences. DECLARE v_groupList TEXT; v_count INT; v_nbTblSeq INT = 0; v_markName TEXT; v_fullTableName TEXT; r_schema RECORD; r_tblsq RECORD; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (CASE WHEN v_multiGroup THEN 'STOP_GROUPS' WHEN NOT v_multiGroup AND NOT v_isForced THEN 'STOP_GROUP' ELSE 'FORCE_STOP_GROUP' END, 'BEGIN', array_to_string(v_groupNames,',')); -- check the group names SELECT emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := v_multiGroup, v_lockGroups := TRUE, v_checkList := '') INTO v_groupNames; -- for all groups already IDLE, generate a warning message and remove them from the list of the groups to process SELECT string_agg(group_name,', ' ORDER BY group_name), count(*) INTO v_groupList, v_count FROM emaj.emaj_group WHERE group_name = ANY(v_groupNames) AND NOT group_is_logging; IF v_count = 1 THEN RAISE WARNING '_stop_groups: The group "%" is already in IDLE state.', v_groupList; END IF; IF v_count > 1 THEN RAISE WARNING '_stop_groups: The groups "%" are already in IDLE state.', v_groupList; END IF; SELECT array_agg(DISTINCT group_name) INTO v_groupNames FROM emaj.emaj_group WHERE group_name = ANY(v_groupNames) AND group_is_logging; -- process the LOGGING groups IF v_groupNames IS NOT NULL THEN -- check and process the supplied mark name (except if the function is called by emaj_force_stop_group()) IF v_mark IS NULL OR v_mark = '' THEN v_mark = 'STOP_%'; END IF; IF NOT v_isForced THEN SELECT emaj._check_new_mark(v_groupNames, v_mark) INTO v_markName; END IF; -- OK (no error detected and at least one group in logging state) -- lock all tables to get a stable point -- one sets the locks at the beginning of the operation (rather than let the ALTER TABLE statements set their own locks) to decrease the risk of deadlock. PERFORM emaj._lock_groups(v_groupNames,'SHARE ROW EXCLUSIVE',v_multiGroup); -- verify that all application schemas for the groups still exists FOR r_schema IN SELECT DISTINCT rel_schema FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND NOT EXISTS (SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname = rel_schema) ORDER BY rel_schema LOOP IF v_isForced THEN RAISE WARNING '_stop_groups: The schema "%" does not exist any more.', r_schema.rel_schema; ELSE RAISE EXCEPTION '_stop_groups: The schema "%" does not exist any more.', r_schema.rel_schema; END IF; END LOOP; -- for each relation currently belonging to the groups to process, FOR r_tblsq IN SELECT rel_priority, rel_schema, rel_tblseq, rel_kind FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) ORDER BY rel_priority, rel_schema, rel_tblseq LOOP CASE r_tblsq.rel_kind WHEN 'r' THEN -- if it is a table, check the table still exists PERFORM 1 FROM pg_catalog.pg_namespace, pg_catalog.pg_class WHERE relnamespace = pg_namespace.oid AND nspname = r_tblsq.rel_schema AND relname = r_tblsq.rel_tblseq; IF NOT FOUND THEN IF v_isForced THEN RAISE WARNING '_stop_groups: The table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; ELSE RAISE EXCEPTION '_stop_groups: The table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; END IF; ELSE -- and disable the emaj log and truncate triggers -- errors are captured so that emaj_force_stop_group() can be silently executed v_fullTableName = quote_ident(r_tblsq.rel_schema) || '.' || quote_ident(r_tblsq.rel_tblseq); BEGIN EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_log_trg'; EXCEPTION WHEN undefined_object THEN IF v_isForced THEN RAISE WARNING '_stop_groups: The log trigger "emaj_log_trg" on table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; ELSE RAISE EXCEPTION '_stop_groups: The log trigger "emaj_log_trg" on table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; END IF; END; BEGIN EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_trunc_trg'; EXCEPTION WHEN undefined_object THEN IF v_isForced THEN RAISE WARNING '_stop_groups: The truncate trigger "emaj_trunc_trg" on table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; ELSE RAISE EXCEPTION '_stop_groups: The truncate trigger "emaj_trunc_trg" on table "%.%" does not exist any more.', r_tblsq.rel_schema, r_tblsq.rel_tblseq; END IF; END; END IF; WHEN 'S' THEN -- if it is a sequence, nothing to do END CASE; v_nbTblSeq = v_nbTblSeq + 1; END LOOP; IF NOT v_isForced THEN -- if the function is not called by emaj_force_stop_group(), set the stop mark for each group PERFORM emaj._set_mark_groups(v_groupNames, v_markName, v_multiGroup, TRUE); -- and set the number of log rows to 0 for these marks UPDATE emaj.emaj_mark m SET mark_log_rows_before_next = 0 WHERE mark_group = ANY (v_groupNames) AND (mark_group, mark_time_id) IN -- select only last mark of each concerned group (SELECT mark_group, max(mark_time_id) FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) AND NOT mark_is_deleted GROUP BY mark_group); END IF; -- set all marks for the groups from the emaj_mark table as 'DELETED' to avoid any further rollback and remove protection if any UPDATE emaj.emaj_mark SET mark_is_deleted = TRUE, mark_is_rlbk_protected = FALSE WHERE mark_group = ANY (v_groupNames) AND NOT mark_is_deleted; -- update the state of the groups rows from the emaj_group table (the rollback protection of rollbackable groups is reset) UPDATE emaj.emaj_group SET group_is_logging = FALSE, group_is_rlbk_protected = NOT group_is_rollbackable WHERE group_name = ANY (v_groupNames); END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'STOP_GROUPS' WHEN NOT v_multiGroup AND NOT v_isForced THEN 'STOP_GROUP' ELSE 'FORCE_STOP_GROUP' END, 'END', array_to_string(v_groupNames,','), v_nbTblSeq || ' tables/sequences processed'); -- RETURN v_nbTblSeq; END; $_stop_groups$; CREATE OR REPLACE FUNCTION emaj._set_mark_groups(v_groupNames TEXT[], v_mark TEXT, v_multiGroup BOOLEAN, v_eventToRecord BOOLEAN, v_loggedRlbkTargetMark TEXT DEFAULT NULL, v_timeId BIGINT DEFAULT NULL) RETURNS INT LANGUAGE plpgsql AS $_set_mark_groups$ -- This function effectively inserts a mark in the emaj_mark table and takes an image of the sequences definitions for the array of groups. -- It also updates the previous mark of each group to setup the mark_log_rows_before_next column with the number of rows recorded into all log tables between this previous mark and the new mark. -- It is called by emaj_set_mark_group and emaj_set_mark_groups functions but also by other functions that set internal marks, like functions that start or rollback groups. -- Input: group names array, mark to set, -- boolean indicating whether the function is called by a multi group function -- boolean indicating whether the event has to be recorded into the emaj_hist table -- name of the rollback target mark when this mark is created by the logged_rollback functions (NULL by default) -- time stamp identifier to reuse (NULL by default) (this parameter is set when the mark is a rollback start mark) -- Output: number of processed tables and sequences -- The insertion of the corresponding event in the emaj_hist table is performed by callers. DECLARE v_nbTbl INT; v_nbSeq INT; BEGIN -- if requested, record the set mark begin in emaj_hist IF v_eventToRecord THEN INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'SET_MARK_GROUPS' ELSE 'SET_MARK_GROUP' END, 'BEGIN', array_to_string(v_groupNames,','), v_mark); END IF; -- get the time stamp of the operation, if not supplied as input parameter IF v_timeId IS NULL THEN SELECT emaj._set_time_stamp('M') INTO v_timeId; END IF; -- record sequences state as early as possible (no lock protects them from other transactions activity) -- the join on pg_namespace and pg_class filters the potentially dropped application sequences WITH seq AS ( -- selected sequences SELECT rel_schema, rel_tblseq FROM emaj.emaj_relation, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relname = rel_tblseq AND nspname = rel_schema AND relnamespace = pg_namespace.oid AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'S' ) INSERT INTO emaj.emaj_sequence (sequ_schema, sequ_name, sequ_time_id, sequ_last_val, sequ_start_val, sequ_increment, sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called) SELECT t.* FROM seq, LATERAL emaj._get_current_sequence_state(rel_schema, rel_tblseq, v_timeId) AS t; GET DIAGNOSTICS v_nbSeq = ROW_COUNT; -- record the number of log rows for the old last mark of each group -- the statement updates no row in case of emaj_start_group(s) WITH stat_group1 AS ( -- for each group, time id of the last active mark SELECT mark_group, max(mark_time_id) AS last_mark_time_id FROM emaj.emaj_mark WHERE NOT mark_is_deleted GROUP BY mark_group), stat_group2 AS ( -- compute the number of log rows for all tables currently belonging to these groups SELECT mark_group, last_mark_time_id, coalesce( (SELECT sum(emaj._log_stat_tbl(emaj_relation, greatest(last_mark_time_id, lower(rel_time_range)),NULL)) FROM emaj.emaj_relation WHERE rel_group = mark_group AND rel_kind = 'r' AND upper_inf(rel_time_range)), 0) AS mark_stat FROM stat_group1 ) UPDATE emaj.emaj_mark m SET mark_log_rows_before_next = mark_stat FROM stat_group2 s WHERE s.mark_group = m.mark_group AND s.last_mark_time_id = m.mark_time_id; -- for tables currently belonging to the groups, record the associated log sequence state into the emaj sequence table INSERT INTO emaj.emaj_sequence (sequ_schema, sequ_name, sequ_time_id, sequ_last_val, sequ_start_val, sequ_increment, sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called) SELECT seq.* FROM emaj.emaj_relation, LATERAL emaj._get_current_sequence_state(rel_log_schema, rel_log_sequence, v_timeId) AS seq WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'r'; GET DIAGNOSTICS v_nbTbl = ROW_COUNT; -- record the mark for each group into the emaj_mark table INSERT INTO emaj.emaj_mark (mark_group, mark_name, mark_time_id, mark_is_deleted, mark_is_rlbk_protected, mark_logged_rlbk_target_mark) SELECT group_name, v_mark, v_timeId, FALSE, FALSE, v_loggedRlbkTargetMark FROM emaj.emaj_group WHERE group_name = ANY(v_groupNames) ORDER BY group_name; -- before exiting, cleanup the state of the pending rollback events from the emaj_rlbk table IF emaj._dblink_is_cnx_opened('rlbk#1') THEN -- ... either through dblink if we are currently performing a rollback with a dblink connection already opened -- this is mandatory to avoid deadlock PERFORM 0 FROM dblink('rlbk#1','SELECT emaj._cleanup_rollback_state()') AS (dummy INT); ELSE -- ... or directly PERFORM emaj._cleanup_rollback_state(); END IF; -- if requested, record the set mark end in emaj_hist IF v_eventToRecord THEN INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'SET_MARK_GROUPS' ELSE 'SET_MARK_GROUP' END, 'END', array_to_string(v_groupNames,','), v_mark); END IF; -- RETURN v_nbSeq + v_nbTbl; END; $_set_mark_groups$; CREATE OR REPLACE FUNCTION emaj.emaj_delete_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS INT LANGUAGE plpgsql AS $emaj_delete_mark_group$ -- This function deletes all traces from a previous set_mark_group(s) function. -- Then, any rollback on the deleted mark will not be possible. -- It deletes rows corresponding to the mark to delete from emaj_mark and emaj_sequence -- If this mark is the first mark, it also deletes rows from all concerned log tables and holes from emaj_seq_hole. -- The statistical mark_log_rows_before_next column's content of the previous mark is also maintained -- At least one mark must remain after the operation (otherwise it is not worth having a group in LOGGING state !). -- Input: group name, mark to delete -- The keyword 'EMAJ_LAST_MARK' can be used as mark to delete to specify the last set mark. -- Output: number of deleted marks, i.e. 1 DECLARE v_markTimeId BIGINT; v_previousMarkTimeId BIGINT; v_previousMarkName TEXT; v_previousMarkGlobalSeq BIGINT; v_nextMarkTimeId BIGINT; v_nextMarkName TEXT; v_nextMarkGlobalSeq BIGINT; v_timeIdNewMin BIGINT; v_markNewMin TEXT; v_count INT; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('DELETE_MARK_GROUP', 'BEGIN', v_groupName, v_mark); -- check the group name PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_groupName], v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := ''); -- check the mark name SELECT emaj._check_mark_name(v_groupNames := ARRAY[v_groupName], v_mark := v_mark, v_checkList := '') INTO v_mark; -- count the number of marks in the group SELECT count(*) INTO v_count FROM emaj.emaj_mark WHERE mark_group = v_groupName; -- and check there are at least 2 marks for the group IF v_count < 2 THEN RAISE EXCEPTION 'emaj_delete_mark_group: "%" is the only mark of the group. It cannot be deleted.', v_mark; END IF; -- OK, now get the time stamp id of the mark to delete SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_name = v_mark; -- ... and the timestamp of the future first mark SELECT mark_time_id, mark_name INTO v_timeIdNewMin, v_markNewMin FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_name <> v_mark ORDER BY mark_time_id LIMIT 1; -- ... and the name, the time id and the last global sequence value of the previous mark SELECT emaj._get_previous_mark_group(v_groupName, v_mark) INTO v_previousMarkName; SELECT mark_time_id, time_last_emaj_gid INTO v_previousMarkTimeId, v_previousMarkGlobalSeq FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupName AND mark_name = v_previousMarkName; -- ... and the name, the time id and the last global sequence value of the next mark SELECT mark_name INTO v_nextMarkName FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id > (SELECT mark_time_id FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_name = v_mark) ORDER BY mark_time_id ASC LIMIT 1; SELECT mark_time_id, time_last_emaj_gid INTO v_nextMarkTimeId, v_nextMarkGlobalSeq FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupName AND mark_name = v_nextMarkName; -- effectively delete the mark for the group IF v_previousMarkTimeId IS NULL THEN -- if the mark to delete is the first one, process its deletion with _delete_before_mark_group(), as the first rows of log tables become useless PERFORM emaj._delete_before_mark_group(v_groupName, v_markNewMin); ELSE -- otherwise, the mark to delete is an intermediate mark for the group -- process the mark deletion with _delete_intermediate_mark_group() PERFORM emaj._delete_intermediate_mark_group(v_groupName, v_mark, v_markTimeId); END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('DELETE_MARK_GROUP', 'END', v_groupName, v_mark); RETURN 1; END; $emaj_delete_mark_group$; COMMENT ON FUNCTION emaj.emaj_delete_mark_group(TEXT,TEXT) IS $$Deletes a mark for an E-Maj group.$$; CREATE OR REPLACE FUNCTION emaj._delete_before_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS INT LANGUAGE plpgsql AS $_delete_before_mark_group$ -- This function deletes all logs and marks set before a given mark. -- The function is called by the emaj_delete_before_mark_group(), emaj_delete_mark_group() functions. -- It deletes rows corresponding to the marks to delete from emaj_mark and emaj_sequence. -- It deletes rows from emaj_relation corresponding to old versions that become unreacheable. -- It deletes rows from all concerned log tables. -- To complete, the function deletes oldest rows from emaj_hist. -- Input: group name, name of the new first mark. -- Output: number of deleted marks, number of tables effectively processed (for which at least one log row has been deleted) DECLARE v_eventTriggers TEXT[]; v_markGlobalSeq BIGINT; v_markTimeId BIGINT; v_nbMark INT; r_rel RECORD; BEGIN -- disable event triggers that protect emaj components and keep in memory these triggers name SELECT emaj._disable_event_triggers() INTO v_eventTriggers; -- retrieve the timestamp and the emaj_gid value and the time stamp id of the mark SELECT time_last_emaj_gid, mark_time_id INTO v_markGlobalSeq, v_markTimeId FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupName AND mark_name = v_mark; -- drop obsolete old log tables (whose end time stamp is older than the new first mark time stamp) FOR r_rel IN SELECT DISTINCT rel_log_schema, rel_log_table FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND upper(rel_time_range) <= v_markTimeId EXCEPT SELECT rel_log_schema, rel_log_table FROM emaj.emaj_relation WHERE rel_kind = 'r' AND upper(rel_time_range) > v_markTimeId ORDER BY 1,2 LOOP EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r_rel.rel_log_schema) || '.' || quote_ident(r_rel.rel_log_table) || ' CASCADE'; END LOOP; -- delete obsolete emaj_sequence and emaj_relation rows (those corresponding to the just dropped log tables) -- (the related emaj_seq_hole rows will be deleted just later ; they are not directly linked to a emaj_relation row) DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND upper(rel_time_range) <= v_markTimeId AND sequ_time_id < v_markTimeId; DELETE FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND upper(rel_time_range) <= v_markTimeId; -- drop the E-Maj secondary schemas that are now useless (i.e. not used by any created group) PERFORM emaj._drop_log_schemas('DELETE_BEFORE_MARK_GROUP', FALSE); -- delete rows from all other log tables FOR r_rel IN SELECT quote_ident(rel_log_schema) || '.' || quote_ident(rel_log_table) AS log_table_name FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND (upper_inf(rel_time_range) OR upper(rel_time_range) > v_markTimeId) ORDER BY rel_priority, rel_schema, rel_tblseq LOOP -- delete log rows prior to the new first mark EXECUTE 'DELETE FROM ' || r_rel.log_table_name || ' WHERE emaj_gid <= ' || v_markGlobalSeq; END LOOP; -- process emaj_seq_hole content -- delete all existing holes (if any) before the mark DELETE FROM emaj.emaj_seq_hole USING emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND rel_schema = sqhl_schema AND rel_tblseq = sqhl_table AND sqhl_begin_time_id < v_markTimeId; -- now the sequences related to the mark to delete can be suppressed -- delete first application sequences related data for the group DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_schema AND sequ_name = rel_tblseq AND rel_time_range @> sequ_time_id AND rel_group = v_groupName AND rel_kind = 'S' AND sequ_time_id < v_markTimeId AND lower(rel_time_range) <> sequ_time_id; -- delete then emaj sequences related data for the group DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND rel_time_range @> sequ_time_id AND rel_group = v_groupName AND rel_kind = 'r' AND sequ_time_id < v_markTimeId AND lower(rel_time_range) <> sequ_time_id; -- and that may have one of the deleted marks as target mark from a previous logged rollback operation UPDATE emaj.emaj_mark SET mark_logged_rlbk_target_mark = NULL WHERE mark_group = v_groupName AND mark_time_id >= v_markTimeId AND mark_logged_rlbk_target_mark IN ( SELECT mark_name FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id < v_markTimeId ); -- delete oldest marks DELETE FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id < v_markTimeId; GET DIAGNOSTICS v_nbMark = ROW_COUNT; --TODO: drop useless application tables (when a feature will need it) -- deletes obsolete versions of emaj_relation rows DELETE FROM emaj.emaj_relation WHERE upper(rel_time_range) < v_markTimeId AND rel_group = v_groupName; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); -- purge the emaj history, if needed (even if no mark as been really dropped) PERFORM emaj._purge_hist(); RETURN v_nbMark; END; $_delete_before_mark_group$; CREATE OR REPLACE FUNCTION emaj._delete_intermediate_mark_group(v_groupName TEXT, v_markName TEXT, v_markTimeId BIGINT) RETURNS VOID LANGUAGE plpgsql AS $_delete_intermediate_mark_group$ -- This function effectively deletes an intermediate mark for a group. -- It is called by the emaj_delete_mark_group() function. -- It deletes rows corresponding to the mark to delete from emaj_mark and emaj_sequence -- The statistical mark_log_rows_before_next column's content of the previous mark is also maintained -- Input: group name, mark name, mark id and mark time stamp id of the mark to delete DECLARE v_previousMark TEXT; v_nextMark TEXT; v_previousMarkTimeId BIGINT; v_nextMarkTimeId BIGINT; BEGIN -- delete the sequences related to the mark to delete -- delete first data related to the application sequences (those attached to the group at the set mark time, but excluding the time range bounds) DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_schema AND sequ_name = rel_tblseq AND rel_time_range @> sequ_time_id AND rel_group = v_groupName AND rel_kind = 'S' AND sequ_time_id = v_markTimeId AND lower(rel_time_range) <> sequ_time_id; -- delete then data related to the log sequences for tables (those attached to the group at the set mark time, but excluding the time range bounds) DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND rel_time_range @> sequ_time_id AND rel_group = v_groupName AND rel_kind = 'r' AND sequ_time_id = v_markTimeId AND lower(rel_time_range) <> sequ_time_id; -- physically delete the mark from emaj_mark DELETE FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_name = v_markName; -- adjust the mark_log_rows_before_next column of the previous mark -- get the name of the mark immediately preceeding the mark to delete SELECT mark_name, mark_time_id INTO v_previousMark, v_previousMarkTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id < v_markTimeId ORDER BY mark_time_id DESC LIMIT 1; -- get the name of the first mark succeeding the mark to delete SELECT mark_name, mark_time_id INTO v_nextMark, v_nextMarkTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id > v_markTimeId ORDER BY mark_time_id LIMIT 1; IF NOT FOUND THEN -- no next mark, so update the previous mark with NULL UPDATE emaj.emaj_mark SET mark_log_rows_before_next = NULL WHERE mark_group = v_groupName AND mark_name = v_previousMark; ELSE -- update the previous mark by computing the sum of _log_stat_tbl() call's result -- for all relations that belonged to the group at the time when the mark before the deleted mark had been set UPDATE emaj.emaj_mark SET mark_log_rows_before_next = (SELECT sum(emaj._log_stat_tbl(emaj_relation, v_previousMarkTimeId, v_nextMarkTimeId)) FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND rel_time_range @> v_previousMarkTimeId) WHERE mark_group = v_groupName AND mark_name = v_previousMark; END IF; -- reset the mark_logged_rlbk_target_mark column to null for other marks of the group -- that may have the deleted mark as target mark from a previous logged rollback operation UPDATE emaj.emaj_mark SET mark_logged_rlbk_target_mark = NULL WHERE mark_group = v_groupName AND mark_logged_rlbk_target_mark = v_markName; RETURN; END; $_delete_intermediate_mark_group$; CREATE OR REPLACE FUNCTION emaj._rlbk_check(v_groupNames TEXT[], v_mark TEXT, v_isAlterGroupAllowed BOOLEAN, isRollbackSimulation BOOLEAN) RETURNS TEXT LANGUAGE plpgsql AS $_rlbk_check$ -- This functions performs checks on group names and mark names supplied as parameter for the emaj_rollback_groups() -- and emaj_estimate_rollback_groups() functions. -- It returns the real mark name, or NULL if the groups array is NULL or empty. DECLARE v_markName TEXT; v_aGroupName TEXT; v_markTimeId BIGINT; v_protectedMarkList TEXT; BEGIN -- check the group names and states IF isRollbackSimulation THEN SELECT emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := 'LOGGING,ROLLBACKABLE') INTO v_groupNames; ELSE SELECT emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := 'LOGGING,ROLLBACKABLE,UNPROTECTED') INTO v_groupNames; END IF; IF v_groupNames IS NOT NULL THEN -- check the mark name SELECT emaj._check_mark_name(v_groupNames := v_groupNames, v_mark := v_mark, v_checkList := 'ACTIVE') INTO v_markName; IF NOT isRollbackSimulation THEN -- check that for each group that the rollback wouldn't delete protected marks (check disabled for rollback simulation) FOREACH v_aGroupName IN ARRAY v_groupNames LOOP -- get the target mark time id SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_aGroupName AND mark_name = v_markName; -- and look at the protected mark SELECT string_agg(mark_name,', ' ORDER BY mark_name) INTO v_protectedMarkList FROM ( SELECT mark_name FROM emaj.emaj_mark WHERE mark_group = v_aGroupName AND mark_time_id > v_markTimeId AND mark_is_rlbk_protected ORDER BY mark_time_id) AS t; IF v_protectedMarkList IS NOT NULL THEN RAISE EXCEPTION '_rlbk_check: Protected marks (%) for the group "%" block the rollback to the mark "%".', v_protectedMarkList, v_aGroupName, v_markName; END IF; END LOOP; END IF; -- if the isAlterGroupAllowed flag is not explicitely set to true, check that the rollback would not cross any alter group operation for the groups IF v_isAlterGroupAllowed IS NULL OR NOT v_isAlterGroupAllowed THEN SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] AND mark_name = v_markName; PERFORM 0 FROM emaj.emaj_alter_plan WHERE altr_time_id > v_markTimeId AND altr_group = ANY (v_groupNames) AND altr_rlbk_id IS NULL; IF FOUND THEN RAISE EXCEPTION '_rlbk_check: This rollback operation would cross some previously executed alter group operations, which is not allowed by the current function parameters.'; END IF; END IF; END IF; RETURN v_markName; END; $_rlbk_check$; CREATE OR REPLACE FUNCTION emaj._rlbk_session_lock(v_rlbkId INT, v_session INT) RETURNS VOID LANGUAGE plpgsql AS $_rlbk_session_lock$ -- It creates the session row in the emaj_rlbk_session table and then locks all the application tables for the session. DECLARE v_stmt TEXT; v_isDblinkUsable BOOLEAN = FALSE; v_groupNames TEXT[]; v_nbRetry SMALLINT = 0; v_ok BOOLEAN = FALSE; v_nbTbl INT; r_tbl RECORD; BEGIN -- try to open a dblink connection for #session > 1 (the attempt for session 1 has already been done) IF v_session > 1 THEN PERFORM emaj._dblink_open_cnx('rlbk#'||v_session); END IF; -- get the rollack characteristics for the emaj_rlbk SELECT rlbk_groups INTO v_groupNames FROM emaj.emaj_rlbk WHERE rlbk_id = v_rlbkId; -- create the session row the emaj_rlbk_session table. v_stmt = 'INSERT INTO emaj.emaj_rlbk_session (rlbs_rlbk_id, rlbs_session, rlbs_txid, rlbs_start_datetime) ' || 'VALUES (' || v_rlbkId || ',' || v_session || ',' || txid_current() || ',' || quote_literal(clock_timestamp()) || ') RETURNING 1'; IF emaj._dblink_is_cnx_opened('rlbk#'||v_session) THEN -- IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#'||v_session,v_stmt) AS (dummy INT); v_isDblinkUsable = TRUE; ELSE -- ... or directly EXECUTE v_stmt; END IF; -- insert lock begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('LOCK_GROUP', 'BEGIN', array_to_string(v_groupNames,','), 'Rollback session #' || v_session); -- -- acquire locks on tables -- -- in case of deadlock, retry up to 5 times WHILE NOT v_ok AND v_nbRetry < 5 LOOP BEGIN v_nbTbl = 0; -- scan all tables of the session, in priority ascending order (priority being defined in emaj_group_def and stored in emaj_relation) FOR r_tbl IN SELECT quote_ident(rlbp_schema) || '.' || quote_ident(rlbp_table) AS fullName, EXISTS (SELECT 1 FROM emaj.emaj_rlbk_plan rlbp2 WHERE rlbp2.rlbp_rlbk_id = v_rlbkId AND rlbp2.rlbp_session = v_session AND rlbp2.rlbp_schema = rlbp1.rlbp_schema AND rlbp2.rlbp_table = rlbp1.rlbp_table AND rlbp2.rlbp_step = 'DIS_LOG_TRG') AS disLogTrg FROM emaj.emaj_rlbk_plan rlbp1, emaj.emaj_relation WHERE rel_schema = rlbp_schema AND rel_tblseq = rlbp_table AND upper_inf(rel_time_range) AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'LOCK_TABLE' AND rlbp_session = v_session ORDER BY rel_priority, rel_schema, rel_tblseq LOOP -- lock each table -- The locking level is EXCLUSIVE mode. -- This blocks all concurrent update capabilities of all tables of the groups (including tables with no logged update to rollback), -- in order to ensure a stable state of the group at the end of the rollback operation). -- But these tables can be accessed by SELECT statements during the E-Maj rollback. EXECUTE 'LOCK TABLE ' || r_tbl.fullName || ' IN EXCLUSIVE MODE'; v_nbTbl = v_nbTbl + 1; END LOOP; -- ok, all tables locked v_ok = TRUE; EXCEPTION WHEN deadlock_detected THEN v_nbRetry = v_nbRetry + 1; RAISE NOTICE '_rlbk_session_lock: A deadlock has been trapped while locking tables for groups "%".', array_to_string(v_groupNames,','); END; END LOOP; IF NOT v_ok THEN PERFORM emaj._rlbk_error(v_rlbkId, '_rlbk_session_lock: Too many (5) deadlocks encountered while locking tables', 'rlbk#'||v_session); RAISE EXCEPTION '_rlbk_session_lock: Too many (5) deadlocks encountered while locking tables for groups "%".',array_to_string(v_groupNames,','); END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('LOCK_GROUP', 'END', array_to_string(v_groupNames,','), 'Rollback session #' || v_session || ': ' || v_nbTbl || ' tables locked, ' || v_nbRetry || ' deadlock(s)'); RETURN; -- trap and record exception during the rollback operation EXCEPTION WHEN SQLSTATE 'P0001' THEN -- Do not trap the exceptions raised by the function RAISE; WHEN OTHERS THEN -- Otherwise, log the E-Maj rollback abort in emaj_rlbk, if possible PERFORM emaj._rlbk_error(v_rlbkId, 'In _rlbk_session_lock() for session ' || v_session || ': ' || SQLERRM, 'rlbk#'||v_session); RAISE; END; $_rlbk_session_lock$; CREATE OR REPLACE FUNCTION emaj._rlbk_session_exec(v_rlbkId INT, v_session INT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $_rlbk_session_exec$ -- This function executes the main part of a rollback operation. -- It executes the steps identified by _rlbk_planning() and stored into emaj_rlbk_plan, for one session. -- It updates the emaj_rlbk_plan table, using dblink connection if possible, giving a visibility of the rollback progress. -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if it doesn't own the application tables. DECLARE v_stmt TEXT; v_isDblinkUsable BOOLEAN = FALSE; v_groupNames TEXT[]; v_mark TEXT; v_rlbkMarkTimeId BIGINT; v_rlbkTimeId BIGINT; v_isLoggedRlbk BOOLEAN; v_nbSession INT; v_maxGlobalSeq BIGINT; v_lastGlobalSeq BIGINT; v_nbRows BIGINT; r_step RECORD; BEGIN -- determine whether the dblink connection for this session is opened IF emaj._dblink_is_cnx_opened('rlbk#'||v_session) THEN v_isDblinkUsable = TRUE; END IF; -- get the rollback characteristics from the emaj_rlbk table SELECT rlbk_groups, rlbk_mark, rlbk_time_id, rlbk_is_logged, rlbk_nb_session, time_last_emaj_gid INTO v_groupNames, v_mark, v_rlbkTimeId, v_isLoggedRlbk, v_nbSession, v_maxGlobalSeq FROM emaj.emaj_rlbk, emaj.emaj_time_stamp WHERE rlbk_id = v_rlbkId AND rlbk_time_id = time_id; -- fetch the mark_time_id, the last global sequence at set_mark time for the first group of the groups array (they all share the same values) SELECT mark_time_id, time_last_emaj_gid INTO v_rlbkMarkTimeId, v_lastGlobalSeq FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupNames[1] AND mark_name = v_mark; -- scan emaj_rlbp_plan to get all steps to process that have been affected to this session, in batch_number and step order FOR r_step IN SELECT rlbp_step, rlbp_schema, rlbp_table, rlbp_fkey, rlbp_fkey_def, rlbp_target_time_id FROM emaj.emaj_rlbk_plan, (VALUES ('DIS_LOG_TRG',1),('DROP_FK',2),('SET_FK_DEF',3),('RLBK_TABLE',4), ('DELETE_LOG',5),('SET_FK_IMM',6),('ADD_FK',7),('ENA_LOG_TRG',8)) AS step(step_name, step_order) WHERE rlbp_step::TEXT = step.step_name AND rlbp_rlbk_id = v_rlbkId AND rlbp_step NOT IN ('LOCK_TABLE','CTRL-DBLINK','CTRL+DBLINK') AND rlbp_session = v_session ORDER BY rlbp_batch_number, step_order, rlbp_table, rlbp_fkey LOOP -- update the emaj_rlbk_plan table to set the step start time v_stmt = 'UPDATE emaj.emaj_rlbk_plan SET rlbp_start_datetime = clock_timestamp() ' || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || 'AND rlbp_step = ' || quote_literal(r_step.rlbp_step) || ' AND rlbp_schema = ' || quote_literal(r_step.rlbp_schema) || ' AND rlbp_table = ' || quote_literal(r_step.rlbp_table) || ' AND rlbp_fkey = ' || quote_literal(r_step.rlbp_fkey) || ' RETURNING 1'; IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#'||v_session,v_stmt) AS (dummy INT); ELSE -- ... or directly EXECUTE v_stmt; END IF; -- process the step depending on its type CASE r_step.rlbp_step WHEN 'DIS_LOG_TRG' THEN -- process a log trigger disable EXECUTE 'ALTER TABLE ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_table) || ' DISABLE TRIGGER emaj_log_trg'; WHEN 'DROP_FK' THEN -- process a foreign key deletion EXECUTE 'ALTER TABLE ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_table) || ' DROP CONSTRAINT ' || quote_ident(r_step.rlbp_fkey); WHEN 'SET_FK_DEF' THEN -- set a foreign key deferred EXECUTE 'SET CONSTRAINTS ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_fkey) || ' DEFERRED'; WHEN 'RLBK_TABLE' THEN -- process a table rollback -- for tables added to the group after the rollback target mark, get the last sequence value specific to each table SELECT emaj._rlbk_tbl(emaj_relation.*, CASE WHEN v_rlbkMarkTimeId = r_step.rlbp_target_time_id THEN v_lastGlobalSeq -- common case ELSE (SELECT time_last_emaj_gid FROM emaj.emaj_time_stamp WHERE time_id = r_step.rlbp_target_time_id) END, v_maxGlobalSeq, v_nbSession, v_isLoggedRlbk) INTO v_nbRows FROM emaj.emaj_relation WHERE rel_schema = r_step.rlbp_schema AND rel_tblseq = r_step.rlbp_table AND upper_inf(rel_time_range); WHEN 'DELETE_LOG' THEN -- process the deletion of log rows -- for tables added to the group after the rollback target mark, get the last sequence value specific to each table SELECT emaj._delete_log_tbl(emaj_relation.*, r_step.rlbp_target_time_id, v_rlbkTimeId, CASE WHEN v_rlbkMarkTimeId = r_step.rlbp_target_time_id THEN v_lastGlobalSeq -- common case ELSE (SELECT time_last_emaj_gid FROM emaj.emaj_time_stamp WHERE time_id = r_step.rlbp_target_time_id) END) INTO v_nbRows FROM emaj.emaj_relation WHERE rel_schema = r_step.rlbp_schema AND rel_tblseq = r_step.rlbp_table AND upper_inf(rel_time_range); WHEN 'SET_FK_IMM' THEN -- set a foreign key immediate EXECUTE 'SET CONSTRAINTS ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_fkey) || ' IMMEDIATE'; WHEN 'ADD_FK' THEN -- process a foreign key creation EXECUTE 'ALTER TABLE ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_table) || ' ADD CONSTRAINT ' || quote_ident(r_step.rlbp_fkey) || ' ' || r_step.rlbp_fkey_def; WHEN 'ENA_LOG_TRG' THEN -- process a log trigger enable EXECUTE 'ALTER TABLE ' || quote_ident(r_step.rlbp_schema) || '.' || quote_ident(r_step.rlbp_table) || ' ENABLE TRIGGER emaj_log_trg'; END CASE; -- update the emaj_rlbk_plan table to set the step duration -- NB: the computed duration does not include the time needed to update the emaj_rlbk_plan table v_stmt = 'UPDATE emaj.emaj_rlbk_plan SET rlbp_duration = ' || quote_literal(clock_timestamp()) || ' - rlbp_start_datetime'; IF r_step.rlbp_step = 'RLBK_TABLE' OR r_step.rlbp_step = 'DELETE_LOG' THEN -- and the effective number of processed rows for RLBK_TABLE and DELETE_LOG steps v_stmt = v_stmt || ' , rlbp_quantity = ' || v_nbRows; END IF; v_stmt = v_stmt || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || 'AND rlbp_step = ' || quote_literal(r_step.rlbp_step) || ' AND rlbp_schema = ' || quote_literal(r_step.rlbp_schema) || ' AND rlbp_table = ' || quote_literal(r_step.rlbp_table) || ' AND rlbp_fkey = ' || quote_literal(r_step.rlbp_fkey) || ' RETURNING 1'; IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#'||v_session,v_stmt) AS (dummy INT); ELSE -- ... or directly EXECUTE v_stmt; END IF; END LOOP; -- update the emaj_rlbk_session table to set the timestamp representing the end of work for the session v_stmt = 'UPDATE emaj.emaj_rlbk_session SET rlbs_end_datetime = clock_timestamp()' || ' WHERE rlbs_rlbk_id = ' || v_rlbkId || ' AND rlbs_session = ' || v_session || ' RETURNING 1'; IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#'||v_session,v_stmt) AS (dummy INT); -- and then close the connection for session > 1 IF v_session > 1 THEN PERFORM emaj._dblink_close_cnx('rlbk#'||v_session); END IF; ELSE -- ... or directly EXECUTE v_stmt; END IF; RETURN; -- trap and record exception during the rollback operation EXCEPTION WHEN SQLSTATE 'P0001' THEN -- Do not trap the exceptions raised by the function RAISE; WHEN OTHERS THEN -- Otherwise, log the E-Maj rollback abort in emaj_rlbk, if possible PERFORM emaj._rlbk_error(v_rlbkId, 'In _rlbk_session_exec() for session ' || v_session || ': ' || SQLERRM, 'rlbk#'||v_session); RAISE; END; $_rlbk_session_exec$; CREATE OR REPLACE FUNCTION emaj._rlbk_end(v_rlbkId INT, v_multiGroup BOOLEAN, OUT rlbk_severity TEXT, OUT rlbk_message TEXT) RETURNS SETOF RECORD LANGUAGE plpgsql AS $_rlbk_end$ -- This is the last step of a rollback group processing. It : -- - deletes the marks that are no longer available, -- - deletes the recorded sequences values for these deleted marks -- - copy data into the emaj_rlbk_stat table, -- - rollbacks all sequences of the groups, -- - set the end rollback mark if logged rollback, -- - and finaly set the operation as COMPLETED or COMMITED. -- It returns the execution report of the rollback operation (a set of rows). DECLARE v_stmt TEXT; v_isDblinkUsable BOOLEAN = FALSE; v_groupNames TEXT[]; v_mark TEXT; v_isLoggedRlbk BOOLEAN; v_isAlterGroupAllowed BOOLEAN; v_nbTbl INT; v_effNbTbl INT; v_rlbkDatetime TIMESTAMPTZ; v_ctrlDuration INTERVAL; v_markTimeId BIGINT; v_nbSeq INT; v_markName TEXT; v_messages TEXT; r_msg RECORD; BEGIN -- determine whether the dblink connection for this session is opened IF emaj._dblink_is_cnx_opened('rlbk#1') THEN v_isDblinkUsable = TRUE; END IF; -- get the rollack characteristics for the emaj_rlbk SELECT rlbk_groups, rlbk_mark, rlbk_is_logged, rlbk_is_alter_group_allowed, rlbk_nb_table, rlbk_eff_nb_table, time_clock_timestamp INTO v_groupNames, v_mark, v_isLoggedRlbk, v_isAlterGroupAllowed, v_nbTbl, v_effNbTbl, v_rlbkDatetime FROM emaj.emaj_rlbk, emaj.emaj_time_stamp WHERE rlbk_time_id = time_id AND rlbk_id = v_rlbkId; -- get the mark timestamp for the 1st group (they all share the same timestamp) SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] AND mark_name = v_mark; -- if "unlogged" rollback, delete all marks later than the now rolled back mark and the associated sequences IF NOT v_isLoggedRlbk THEN -- get the highest mark time id of the mark used for rollback, for all groups -- delete the marks that are suppressed by the rollback (the related sequences have been already deleted by rollback functions) -- with a logging in the history WITH deleted AS ( DELETE FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) AND mark_time_id > v_markTimeId RETURNING mark_time_id, mark_group, mark_name), sorted_deleted AS ( -- the sort is performed to produce stable results in regression tests SELECT mark_group, mark_name FROM deleted ORDER BY mark_time_id, mark_group) INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) SELECT CASE WHEN v_multiGroup THEN 'ROLLBACK_GROUPS' ELSE 'ROLLBACK_GROUP' END, 'MARK DELETED', mark_group, 'mark ' || mark_name || ' is deleted' FROM sorted_deleted; -- and reset the mark_log_rows_before_next column for the new last mark UPDATE emaj.emaj_mark SET mark_log_rows_before_next = NULL WHERE mark_group = ANY (v_groupNames) AND (mark_group, mark_time_id) IN -- select only the last non deleted mark of each concerned group (SELECT mark_group, max(mark_time_id) FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames) AND NOT mark_is_deleted GROUP BY mark_group); -- the sequences related to the deleted marks can be also suppressed -- delete first application sequences related data for the groups DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_schema AND sequ_name = rel_tblseq AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'S' AND sequ_time_id > v_markTimeId AND lower(rel_time_range) <> sequ_time_id; -- delete then emaj sequences related data for the groups DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'r' AND sequ_time_id > v_markTimeId AND lower(rel_time_range) <> sequ_time_id; END IF; -- delete the now useless 'LOCK TABLE' steps from the emaj_rlbk_plan table v_stmt = 'DELETE FROM emaj.emaj_rlbk_plan ' || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step = ''LOCK_TABLE'' RETURNING 1'; IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#1',v_stmt) AS (dummy INT); ELSE -- ... or directly EXECUTE v_stmt; END IF; -- Prepare the CTRLxDBLINK pseudo step statistic by computing the global time spent between steps SELECT coalesce(sum(ctrl_duration),'0'::INTERVAL) INTO v_ctrlDuration FROM ( SELECT rlbs_session, rlbs_end_datetime - min(rlbp_start_datetime) - sum(rlbp_duration) AS ctrl_duration FROM emaj.emaj_rlbk_session rlbs, emaj.emaj_rlbk_plan rlbp WHERE rlbp_rlbk_id = rlbs_rlbk_id AND rlbp_session = rlbs_session AND rlbs_rlbk_id = v_rlbkID GROUP BY rlbs_session, rlbs_end_datetime ) AS t; -- report duration statistics into the emaj_rlbk_stat table v_stmt = 'INSERT INTO emaj.emaj_rlbk_stat (rlbt_step, rlbt_schema, rlbt_table, rlbt_fkey,' || ' rlbt_rlbk_id, rlbt_quantity, rlbt_duration)' || -- copy elementary steps for RLBK_TABLE, DELETE_LOG, ADD_FK and SET_FK_IMM step types -- (record the rlbp_estimated_quantity as reference for later forecast) ' SELECT rlbp_step, rlbp_schema, rlbp_table, rlbp_fkey, rlbp_rlbk_id,' || ' rlbp_estimated_quantity, rlbp_duration' || ' FROM emaj.emaj_rlbk_plan, emaj.emaj_rlbk' || ' WHERE rlbk_id = rlbp_rlbk_id AND rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''RLBK_TABLE'',''DELETE_LOG'',''ADD_FK'',''SET_FK_IMM'') ' || ' UNION ALL ' || -- for 4 other steps, aggregate other elementary steps into a global row for each step type ' SELECT rlbp_step, '''', '''', '''', rlbp_rlbk_id, ' || ' count(*), sum(rlbp_duration)' || ' FROM emaj.emaj_rlbk_plan, emaj.emaj_rlbk' || ' WHERE rlbk_id = rlbp_rlbk_id AND rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''DIS_LOG_TRG'',''DROP_FK'',''SET_FK_DEF'',''ENA_LOG_TRG'') ' || ' GROUP BY 1, 2, 3, 4, 5' || ' UNION ALL ' || -- and the final CTRLxDBLINK pseudo step statistic ' SELECT rlbp_step, '''', '''', '''', rlbp_rlbk_id, ' || ' rlbp_estimated_quantity, ' || quote_literal(v_ctrlDuration) || ' FROM emaj.emaj_rlbk_plan, emaj.emaj_rlbk' || ' WHERE rlbk_id = rlbp_rlbk_id AND rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''CTRL+DBLINK'',''CTRL-DBLINK'') ' || ' RETURNING 1'; IF v_isDblinkUsable THEN -- ... either through dblink if possible PERFORM 0 FROM dblink('rlbk#1',v_stmt) AS (dummy INT); ELSE -- ... or directly EXECUTE v_stmt; END IF; -- rollback the application sequences belonging to the groups -- warning, this operation is not transaction safe (that's why it is placed at the end of the operation)! -- if the sequence has been added to its group after the target rollback mark, rollback up to the corresponding alter_group time PERFORM emaj._rlbk_seq(t.*, greatest(v_markTimeId, lower(t.rel_time_range))) FROM (SELECT * FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'S' ORDER BY rel_priority, rel_schema, rel_tblseq) as t; GET DIAGNOSTICS v_nbSeq = ROW_COUNT; -- if rollback is "logged" rollback, automaticaly set a mark representing the tables state just after the rollback. -- this mark is named 'RLBK__%_DONE', where % represents the rollback start time IF v_isLoggedRlbk THEN v_markName = 'RLBK_' || v_mark || '_' || to_char(v_rlbkDatetime, 'HH24.MI.SS.MS') || '_DONE'; PERFORM emaj._set_mark_groups(v_groupNames, v_markName, v_multiGroup, TRUE, v_mark); END IF; -- build and return the execution report -- start with the NOTICE messages rlbk_severity = 'Notice'; rlbk_message = format ('%s / %s tables effectively processed.', v_effNbTbl::TEXT, v_nbTbl::TEXT); INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'ROLLBACK_GROUPS' ELSE 'ROLLBACK_GROUP' END, 'NOTICE', 'Rollback id ' || v_rlbkId, rlbk_message); v_messages = quote_literal(rlbk_severity || ': ' || rlbk_message); IF v_isAlterGroupAllowed IS NULL THEN -- for old style calling functions just return the number of processed tables and sequences rlbk_message = (v_effNbTbl + v_nbSeq)::TEXT; RETURN NEXT; ELSE RETURN NEXT; END IF; -- return the execution report to new style calling functions -- ... the general notice messages with counters IF v_nbSeq > 0 THEN rlbk_message = format ('%s sequences processed.', v_nbSeq::TEXT); INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'ROLLBACK_GROUPS' ELSE 'ROLLBACK_GROUP' END, 'NOTICE', 'Rollback id ' || v_rlbkId, rlbk_message); v_messages = concat(v_messages, ',', quote_literal(rlbk_severity || ': ' || rlbk_message)); IF v_isAlterGroupAllowed IS NOT NULL THEN RETURN NEXT; END IF; END IF; -- then, for new style calling functions, return the WARNING messages for any elementary action from alter group operations that has not been rolled back IF v_isAlterGroupAllowed IS NOT NULL THEN rlbk_severity = 'Warning'; FOR r_msg IN -- steps are splitted into 2 groups to filter them differently SELECT altr_time_id, altr_step, altr_schema, altr_tblseq, (CASE altr_step WHEN 'ADD_SEQ' THEN 'The sequence ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has only been rolled back to its latest group attachment state (' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' WHEN 'ADD_TBL' THEN 'The table ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has only been rolled back to its latest group attachment (' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' WHEN 'REMOVE_SEQ' THEN 'The sequence ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has been left unchanged (not in group anymore since ' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' WHEN 'REMOVE_TBL' THEN 'The table ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has been left unchanged (not in group anymore since ' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' WHEN 'MOVE_SEQ' THEN 'The sequence ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has only been rolled back to its latest group attachment state (' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' WHEN 'MOVE_TBL' THEN 'The table ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) || ' has only been rolled back to its latest group attachment (' || to_char(time_tx_timestamp, 'YYYY/MM/DD HH:MI:SS TZ') || ')' END)::TEXT AS message FROM ( -- suppress duplicate ADD_TBL / REMOVE_TBL or ADD_SEQ / REMOVE_SEQ for same table or sequence, by keeping the most recent step SELECT altr_schema, altr_tblseq, altr_time_id, altr_step FROM ( SELECT altr_schema, altr_tblseq, altr_time_id, altr_step, rank() OVER (PARTITION BY altr_schema, altr_tblseq ORDER BY altr_time_id DESC) AS altr_rank FROM emaj.emaj_alter_plan WHERE altr_time_id > v_markTimeId AND altr_group = ANY (v_groupNames) AND altr_tblseq <> '' AND altr_rlbk_id IS NULL AND altr_step IN ('ADD_TBL','ADD_SEQ','REMOVE_TBL','REMOVE_SEQ','MOVE_TBL','MOVE_SEQ') ) AS t1 WHERE altr_rank = 1 ) AS t2, emaj.emaj_time_stamp WHERE altr_time_id = time_id UNION SELECT altr_time_id, altr_step, altr_schema, altr_tblseq, (CASE altr_step WHEN 'CHANGE_REL_PRIORITY' THEN 'Tables group change not rolled back: E-Maj priority for ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) WHEN 'CHANGE_TBL_LOG_SCHEMA' THEN 'Tables group change not rolled back: E-Maj log schema for ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) WHEN 'CHANGE_TBL_NAMES_PREFIX' THEN 'Tables group change not rolled back: E-Maj names prefix for ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) WHEN 'CHANGE_TBL_LOG_DATA_TSP' THEN 'Tables group change not rolled back: log data tablespace for ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) WHEN 'CHANGE_TBL_LOG_INDEX_TSP' THEN 'Tables group change not rolled back: log index tablespace for ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) ELSE altr_step::TEXT || ' / ' || quote_ident(altr_schema) || '.' || quote_ident(altr_tblseq) END)::TEXT AS message FROM ( -- suppress duplicates for other steps for each table or sequence SELECT altr_schema, altr_tblseq, altr_time_id, altr_step FROM ( SELECT altr_schema, altr_tblseq, altr_time_id, altr_step, rank() OVER (PARTITION BY altr_schema, altr_tblseq ORDER BY altr_time_id DESC) AS altr_rank FROM emaj.emaj_alter_plan WHERE altr_time_id > v_markTimeId AND altr_group = ANY (v_groupNames) AND altr_tblseq <> '' AND altr_rlbk_id IS NULL AND altr_step NOT IN ('ADD_TBL','ADD_SEQ','REMOVE_TBL','REMOVE_SEQ','MOVE_TBL','MOVE_SEQ') ) AS t1 WHERE altr_rank = 1 ) AS t2 ORDER BY altr_time_id, altr_step, altr_schema, altr_tblseq LOOP INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'ROLLBACK_GROUPS' ELSE 'ROLLBACK_GROUP' END, 'WARNING', 'Rollback id ' || v_rlbkId, r_msg.message); rlbk_message = r_msg.message; v_messages = concat(v_messages, ',', quote_literal(rlbk_severity || ': ' || rlbk_message)); RETURN NEXT; END LOOP; END IF; -- update the alter steps that have been covered by the rollback UPDATE emaj.emaj_alter_plan SET altr_rlbk_id = v_rlbkId WHERE altr_time_id > v_markTimeId AND altr_group = ANY (v_groupNames) AND altr_rlbk_id IS NULL; -- update the emaj_rlbk table to set the real number of tables to process, adjust the rollback status and set the result message IF v_isDblinkUsable THEN -- ... either through dblink if possible v_stmt = 'UPDATE emaj.emaj_rlbk SET rlbk_status = ''COMPLETED'', rlbk_end_datetime = clock_timestamp(), rlbk_messages = ARRAY[' || v_messages || ']' || ' WHERE rlbk_id = ' || v_rlbkId || ' RETURNING 1'; PERFORM 0 FROM dblink('rlbk#1',v_stmt) AS (dummy INT); -- and then close the connection PERFORM emaj._dblink_close_cnx('rlbk#1'); ELSE -- ... or directly (the status can be directly set to committed, the update being in the same transaction) EXECUTE 'UPDATE emaj.emaj_rlbk SET rlbk_status = ''COMMITTED'', rlbk_end_datetime = clock_timestamp(), rlbk_messages = ARRAY[' || v_messages || ']' || ' WHERE rlbk_id = ' || v_rlbkId; END IF; -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (CASE WHEN v_multiGroup THEN 'ROLLBACK_GROUPS' ELSE 'ROLLBACK_GROUP' END, 'END', array_to_string(v_groupNames,','), 'Rollback_id ' || v_rlbkId || ', ' || v_effNbTbl || ' tables and ' || v_nbSeq || ' sequences effectively processed' ); -- end of the function RETURN; -- trap and record exception during the rollback operation EXCEPTION WHEN SQLSTATE 'P0001' THEN -- Do not trap the exceptions raised by the function RAISE; WHEN OTHERS THEN -- Otherwise, log the E-Maj rollback abort in emaj_rlbk, if possible PERFORM emaj._rlbk_error(v_rlbkId, 'In _rlbk_end(): ' || SQLERRM, 'rlbk#1'); RAISE; END; $_rlbk_end$; CREATE OR REPLACE FUNCTION emaj._delete_between_marks_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT, OUT v_nbMark INT, OUT v_nbTbl INT) RETURNS RECORD LANGUAGE plpgsql AS $_delete_between_marks_group$ -- This function deletes all logs and intermediate marks set between two given marks. -- The function is called by the emaj_consolidate_rollback_group() function. -- It deletes rows corresponding to the marks to delete from emaj_mark and emaj_sequence. -- It deletes rows from emaj_relation corresponding to old versions that become unreacheable. -- It deletes rows from all concerned log tables. -- It also manages sequence holes in emaj_seq_hole. -- Input: group name, name of both marks that defines the range to delete. -- Output: number of deleted marks, number of tables effectively processed (for which at least one log row has been deleted) DECLARE v_firstMarkGlobalSeq BIGINT; v_firstMarkTimeId BIGINT; v_lastMarkGlobalSeq BIGINT; v_lastMarkTimeId BIGINT; v_nbUpd BIGINT; r_rel RECORD; BEGIN -- retrieve the timestamp and the emaj_gid value and the time stamp id of the first mark SELECT time_last_emaj_gid, mark_time_id INTO v_firstMarkGlobalSeq, v_firstMarkTimeId FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupName AND mark_name = v_firstMark; -- retrieve the timestamp and the emaj_gid value and the time stamp id of the last mark SELECT time_last_emaj_gid, mark_time_id INTO v_lastMarkGlobalSeq, v_lastMarkTimeId FROM emaj.emaj_mark, emaj.emaj_time_stamp WHERE mark_time_id = time_id AND mark_group = v_groupName AND mark_name = v_lastMark; -- delete rows from all log tables (no need to try to delete if v_firstMarkGlobalSeq and v_lastMarkGlobalSeq are equal) v_nbTbl = 0; IF v_firstMarkGlobalSeq < v_lastMarkGlobalSeq THEN -- loop on all tables that belonged to the group at the end of the period FOR r_rel IN SELECT quote_ident(rel_log_schema) || '.' || quote_ident(rel_log_table) AS log_table_name FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND rel_time_range @> v_lastMarkTimeId ORDER BY rel_priority, rel_schema, rel_tblseq LOOP -- delete log rows EXECUTE 'DELETE FROM ' || r_rel.log_table_name || ' WHERE emaj_gid > ' || v_firstMarkGlobalSeq || ' AND emaj_gid <= ' || v_lastMarkGlobalSeq; GET DIAGNOSTICS v_nbUpd = ROW_COUNT; IF v_nbUpd > 0 THEN v_nbTbl = v_nbTbl + 1; END IF; END LOOP; END IF; -- process emaj_seq_hole content -- delete all existing holes (if any) between both marks for tables that belonged to the group at the end of the period DELETE FROM emaj.emaj_seq_hole USING emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND rel_time_range @> v_lastMarkTimeId AND rel_schema = sqhl_schema AND rel_tblseq = sqhl_table AND sqhl_begin_time_id >= v_firstMarkTimeId AND sqhl_begin_time_id < v_lastMarkTimeId; -- create holes representing the deleted logs INSERT INTO emaj.emaj_seq_hole (sqhl_schema, sqhl_table, sqhl_begin_time_id, sqhl_end_time_id, sqhl_hole_size) SELECT rel_schema, rel_tblseq, greatest(v_firstMarkTimeId, lower(rel_time_range)), v_lastMarkTimeId, (SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END FROM emaj.emaj_sequence WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND sequ_time_id = v_lastMarkTimeId) - (SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END FROM emaj.emaj_sequence WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND sequ_time_id = greatest(v_firstMarkTimeId, lower(rel_time_range))) FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND rel_time_range @> v_lastMarkTimeId AND 0 < (SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END FROM emaj.emaj_sequence WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND sequ_time_id = v_lastMarkTimeId) - (SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END FROM emaj.emaj_sequence WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND sequ_time_id = greatest(v_firstMarkTimeId, lower(rel_time_range))); -- now the sequences related to the mark to delete can be suppressed -- delete first application sequences related data for the group (excluding the time range bounds) DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_schema AND sequ_name = rel_tblseq AND rel_time_range @> v_lastMarkTimeId AND rel_group = v_groupName AND rel_kind = 'S' AND sequ_time_id > v_firstMarkTimeId AND sequ_time_id < v_lastMarkTimeId AND lower(rel_time_range) <> sequ_time_id; -- delete then emaj sequences related data for the group (excluding the time range bounds) DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND rel_time_range @> v_lastMarkTimeId AND rel_group = v_groupName AND rel_kind = 'r' AND sequ_time_id > v_firstMarkTimeId AND sequ_time_id < v_lastMarkTimeId AND lower(rel_time_range) <> sequ_time_id; -- in emaj_mark, reset the mark_logged_rlbk_target_mark column to null for marks of the group that will remain -- and that may have one of the deleted marks as target mark from a previous logged rollback operation UPDATE emaj.emaj_mark SET mark_logged_rlbk_target_mark = NULL WHERE mark_group = v_groupName AND mark_time_id >= v_lastMarkTimeId AND mark_logged_rlbk_target_mark IN ( SELECT mark_name FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id > v_firstMarkTimeId AND mark_time_id < v_lastMarkTimeId ); -- set the mark_log_rows_before_next of the first mark to 0 UPDATE emaj.emaj_mark SET mark_log_rows_before_next = 0 WHERE mark_group = v_groupName AND mark_name = v_firstMark; -- and finaly delete all intermediate marks DELETE FROM emaj.emaj_mark WHERE mark_group = v_groupName AND mark_time_id > v_firstMarkTimeId AND mark_time_id < v_lastMarkTimeId; GET DIAGNOSTICS v_nbMark = ROW_COUNT; RETURN; END; $_delete_between_marks_group$; CREATE OR REPLACE FUNCTION emaj.emaj_get_consolidable_rollbacks() RETURNS SETOF emaj.emaj_consolidable_rollback_type LANGUAGE plpgsql AS $emaj_get_consolidable_rollbacks$ -- This function returns the list of logged rollback operations that can be consolidated, defined as a marks range for a group. -- It doesn't need input parameter. -- It returns a set of emaj_consolidable_rollback_type records, sorted by ascending rollback time. -- The cons_group and cons_end_rlbk_mark_name returned columns can be used as input parameters for the emaj_consolidate_rollback_group() function. BEGIN -- search and return all marks range corresponding to any logged rollback operation RETURN QUERY SELECT m1.mark_group AS cons_group, m2.mark_name AS cons_target_rlbk_mark_name, m2.mark_time_id AS cons_target_rlbk_mark_time_id, m1.mark_name AS cons_end_rlbk_mark_name, m1.mark_time_id AS cons_end_rlbk_mark_time_id, cast(coalesce( (SELECT sum(emaj._log_stat_tbl(emaj_relation, greatest(m2.mark_time_id, lower(rel_time_range)), m1.mark_time_id)) FROM emaj.emaj_relation -- for tables belonging to the group at the rollback time WHERE rel_group = m1.mark_group AND rel_kind = 'r' AND rel_time_range @> m1.mark_time_id) ,0) AS BIGINT) AS cons_rows, cast((SELECT count(*) FROM emaj.emaj_mark m3 WHERE m3.mark_group = m1.mark_group AND m3.mark_time_id > m2.mark_time_id AND m3.mark_time_id < m1.mark_time_id) AS INT) AS cons_marks FROM emaj.emaj_mark m1 JOIN emaj.emaj_mark m2 ON (m2.mark_name = m1.mark_logged_rlbk_target_mark AND m2.mark_group = m1.mark_group) WHERE m1.mark_logged_rlbk_target_mark IS NOT NULL ORDER BY m1.mark_time_id; END; $emaj_get_consolidable_rollbacks$; COMMENT ON FUNCTION emaj.emaj_get_consolidable_rollbacks() IS $$Returns the list of logged rollback operations that can be consolidated.$$; CREATE OR REPLACE FUNCTION emaj.emaj_snap_log_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT, v_dir TEXT, v_copyOptions TEXT) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER AS $emaj_snap_log_group$ -- This function creates a file for each log table belonging to the group. -- It also creates 2 files containing the state of sequences respectively at start mark and end mark -- For log tables, files contain all rows related to the time frame, sorted on emaj_gid. -- For sequences, files are names _sequences_at_, or _sequences_at_