-- -- E-Maj: migration from 3.1.0 to 3.2.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_nbNoError INT; 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 '3.1.0' SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version'; IF v_emajVersion <> '3.1.0' THEN RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 3.1.0',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 BEGIN SELECT count(msg) FILTER (WHERE msg = 'No error detected') INTO v_nbNoError FROM emaj.emaj_verify_all() AS t(msg); EXCEPTION -- Errors during the emaj_verify_all() execution are trapped. The emaj_verify_all() code may be incompatible with the current PG version. WHEN OTHERS THEN -- do nothing END; IF v_nbNoError = 0 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. ' 'An "ALTER EXTENSION emaj UPDATE TO ''%'';" statement may be required before.', v_emajVersion; 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.2.0', 'Upgrade from 3.1.0 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 -- -- -- ---------------------------------------------- -- force the priority of sequences in emaj_group_def and emaj_relation to NULL ALTER TABLE emaj.emaj_group_def DISABLE TRIGGER emaj_group_def_change_trg; UPDATE emaj.emaj_group_def SET grpdef_priority = NULL FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_schema = nspname AND grpdef_tblseq = relname AND relnamespace = pg_namespace.oid AND relkind = 'S' AND grpdef_priority IS NOT NULL; UPDATE emaj.emaj_relation SET rel_priority = NULL WHERE rel_kind = 'S' AND rel_priority IS NOT NULL; ALTER TABLE emaj.emaj_group_def ENABLE TRIGGER emaj_group_def_change_trg; -- -- add created or recreated tables and sequences to the list of content to save by pg_dump -- ------------------------------------ -- -- -- emaj types -- -- -- ------------------------------------ ------------------------------------ -- -- -- 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._add_tbl(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._change_log_data_tsp_tbl(R_REL EMAJ.EMAJ_RELATION,V_NEWLOGDATTSP TEXT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._change_log_index_tsp_tbl(R_REL EMAJ.EMAJ_RELATION,V_NEWLOGIDXTSP TEXT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._remove_tbl(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._move_tbl(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT); DROP FUNCTION IF EXISTS emaj._create_seq(V_SCHEMA TEXT,V_SEQ TEXT,V_GROUPNAME TEXT,V_PRIORITY INT,V_TIMEID BIGINT); DROP FUNCTION IF EXISTS emaj._add_seq(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._remove_seq(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT,V_MULTIGROUP BOOLEAN); DROP FUNCTION IF EXISTS emaj._move_seq(R_PLAN EMAJ.EMAJ_ALTER_PLAN,V_TIMEID BIGINT); ------------------------------------------------------------------ -- create new or modified functions -- ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION emaj._dblink_open_cnx(v_cnxName TEXT, OUT v_status INT, OUT v_schema TEXT) LANGUAGE plpgsql AS $_dblink_open_cnx$ -- This function tries to open a named dblink connection. -- It uses as target: the current cluster (port), the current database and a role defined in the emaj_param table. -- This connection role must be defined in the emaj_param table with a row having: -- - param_key = 'dblink_user_password', -- - param_value_text = 'user= password=' with the rules that apply to usual libPQ connect strings. -- The password can be omited if the connection doesn't require it. -- The dblink_connect_u is used to open the connection so that emaj_adm but non superuser roles can access the -- cluster even when no password is required to log on. -- The function is directly called by Emaj_web. -- Input: connection name -- Output: integer status return. -- 1 successful connection -- 0 already opened connection -- -1 dblink is not installed -- -2 dblink functions are not visible for the session (obsolete) -- -3 dblink functions execution is not granted to the role -- -4 the transaction isolation level is not READ COMMITTED -- -5 no 'dblink_user_password' parameter is defined in the emaj_param table -- -6 error at dblink_connect() call -- name of the schema that holds the dblink extension (used later to schema qualify all calls to dblink functions) DECLARE v_nbCnx INT; v_UserPassword TEXT; v_connectString TEXT; BEGIN -- look for the schema holding the dblink functions -- (NULL if the dblink_connect_u function is not available, which should not happen) SELECT nspname INTO v_schema FROM pg_catalog.pg_proc, pg_catalog.pg_namespace WHERE pronamespace = pg_namespace.oid AND proname = 'dblink_connect_u' LIMIT 1; IF NOT FOUND THEN v_status = -1; -- dblink is not installed ELSIF NOT has_function_privilege(quote_ident(v_schema) || '.dblink_connect_u(text, text)', 'execute') THEN v_status = -3; -- current role has not the execute rights on dblink functions ELSIF substring(v_cnxName FROM 1 FOR 5) = 'rlbk#' AND current_setting('transaction_isolation') <> 'read committed' THEN v_status = -4; -- 'rlbk#*' connection (used for rollbacks) must only come from a -- READ COMMITTED transaction ELSE EXECUTE format('SELECT 0 WHERE %L = ANY (%I.dblink_get_connections())', v_cnxName, v_schema); GET DIAGNOSTICS v_nbCnx = ROW_COUNT; IF v_nbCnx > 0 THEN -- dblink is usable, so search the requested connection name in dblink connections list v_status = 0; -- the requested connection is already open ELSE -- so, get the 'dblink_user_password' parameter if exists, from emaj_param SELECT param_value_text INTO v_UserPassword FROM emaj.emaj_param WHERE param_key = 'dblink_user_password'; IF NOT FOUND THEN v_status = -5; -- no 'dblink_user_password' parameter is defined in the emaj_param table ELSE -- ... build the connect string v_connectString = 'host=localhost port=' || current_setting('port') || ' dbname=' || current_database() || ' ' || v_userPassword; -- ... and try to connect BEGIN EXECUTE format('SELECT %I.dblink_connect_u(%L ,%L)', v_schema, v_cnxName, v_connectString); v_status = 1; -- the connection is successful EXCEPTION WHEN OTHERS THEN v_status = -6; -- the connection attempt failed END; END IF; END IF; END IF; -- for connections used for rollback operations, record the dblink connection attempt in the emaj_hist table IF substring(v_cnxName FROM 1 FOR 5) = 'rlbk#' THEN INSERT INTO emaj.emaj_hist (hist_function, hist_object, hist_wording) VALUES ('DBLINK_OPEN_CNX', v_cnxName, 'Status = ' || v_status); END IF; RETURN; END; $_dblink_open_cnx$; CREATE OR REPLACE FUNCTION emaj._dblink_sql_exec(v_cnxName TEXT, v_stmt TEXT, v_dblinkSchema TEXT) RETURNS BIGINT LANGUAGE plpgsql AS $_dblink_sql_exec$ -- This function executes a SQL statement, either through an opened dblink connection when a schema name is provided or directly. -- It returns a bigint value. Consequently, all SQL statements to execute must return an integer numeric value. -- Input: connection name -- sql statement -- name of the schema that holds the dblink extension -- Output: the single return value DECLARE v_returnValue BIGINT; BEGIN IF v_dblinkSchema IS NOT NULL THEN -- a dblink schema is provided, so the connection name can be used to execute the requested SQL statement EXECUTE format('SELECT return_value FROM %I.dblink(%L, %L) AS (return_value BIGINT)', v_dblinkSchema, v_cnxName, v_stmt) INTO v_returnValue; ELSE -- the SQL statement has to be directly executed EXECUTE v_stmt INTO v_returnValue; END IF; RETURN v_returnValue; END; $_dblink_sql_exec$; CREATE OR REPLACE FUNCTION emaj._dblink_close_cnx(v_cnxName TEXT, v_dblinkSchema TEXT) RETURNS VOID LANGUAGE plpgsql AS $_dblink_close_cnx$ -- This function closes a named dblink connection. -- The function is directly called by Emaj_web. -- Input: connection name DECLARE v_nbCnx INT; BEGIN -- check the dblink connection exists EXECUTE format('SELECT 0 WHERE %L = ANY (%I.dblink_get_connections())', v_cnxName, v_dblinkSchema); GET DIAGNOSTICS v_nbCnx = ROW_COUNT; IF v_nbCnx > 0 THEN -- the connection exists, so disconnect EXECUTE format('SELECT %I.dblink_disconnect(%L)', v_dblinkSchema, v_cnxName); -- for connections used for rollback operations, record the dblink disconnection in the emaj_hist table IF substring(v_cnxName FROM 1 FOR 5) = 'rlbk#' THEN INSERT INTO emaj.emaj_hist (hist_function, hist_object) VALUES ('DBLINK_CLOSE_CNX', v_cnxName); END IF; END IF; RETURN; END; $_dblink_close_cnx$; 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 and emaj priority are all set to NULL -- - for tables, configured tablespaces exist -- The function is directly called by Emaj_web. -- 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 NOT EXISTS ( SELECT 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND grpdef_schema = nspname AND grpdef_tblseq = relname 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 a table is not assigned several time in the groups RETURN QUERY WITH dupl AS ( SELECT grpdef_schema, grpdef_tblseq, count(*) FROM emaj.emaj_group_def WHERE grpdef_group = ANY (v_groupNames) GROUP BY 1,2 HAVING count(*) > 1) SELECT 10, 1, v_groupNames[1], grpdef_schema, grpdef_tblseq, NULL::TEXT, format('the table %s.%s is assigned several times.', quote_ident(grpdef_schema), quote_ident(grpdef_tblseq)) FROM dupl; ---- 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'; ---- with PG11- check no table is a WITH OIDS table (blocking rollbackable groups only) IF emaj._pg_version_num() < 120000 THEN 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; END IF; ---- 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 priority 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 priority 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_priority IS NOT NULL; ---- all sequences described in emaj_group_def have their data log tablespace attribute 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 tablespace attribute 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_new_mark(v_groupNames TEXT[], v_mark TEXT) RETURNS TEXT LANGUAGE plpgsql AS $_check_new_mark$ -- This function verifies that a new mark name supplied the user is valid. -- It processes the possible NULL mark value and the replacement of % wild characters. -- It also checks that the mark name do not already exist for any group. -- Input: array of group names, name of the mark to set -- Output: internal name of the mark DECLARE v_markName TEXT = v_mark; v_groupList TEXT; v_count INTEGER; BEGIN -- check the mark name is not 'EMAJ_LAST_MARK' IF v_mark = 'EMAJ_LAST_MARK' THEN RAISE EXCEPTION '_check_new_mark: "%" is not an allowed name for a new mark.', v_mark; END IF; -- process null or empty supplied mark name IF v_markName = '' OR v_markName IS NULL THEN v_markName = 'MARK_%'; END IF; -- process % wild characters in mark name v_markName = replace(v_markName, '%', substring(to_char(clock_timestamp(), 'HH24.MI.SS.US') from 1 for 13)); -- check that the mark does not exist for any groups 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); IF v_count > 0 THEN IF v_count = 1 THEN RAISE EXCEPTION '_check_new_mark: The group "%" already contains a mark named "%".', v_groupList, v_markName; ELSE RAISE EXCEPTION '_check_new_mark: The groups "%" already contain a mark named "%".', v_groupList, v_markName; END IF; END IF; RETURN v_markName; END; $_check_new_mark$; CREATE OR REPLACE FUNCTION emaj._log_truncate_fnct() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $_log_truncate_fnct$ -- The function is triggered by the execution of TRUNCATE SQL verb on tables of an audit_only group in logging mode. DECLARE v_fullLogTableName TEXT; BEGIN IF (TG_OP = 'TRUNCATE') THEN SELECT quote_ident(rel_log_schema) || '.' || quote_ident(rel_log_table) INTO v_fullLogTableName FROM emaj.emaj_relation WHERE rel_schema = TG_TABLE_SCHEMA AND rel_tblseq = TG_TABLE_NAME AND upper_inf(rel_time_range); EXECUTE format('INSERT INTO %s (emaj_verb) VALUES (''TRU'')', v_fullLogTableName); END IF; RETURN NULL; END; $_log_truncate_fnct$; CREATE OR REPLACE FUNCTION emaj._create_log_schemas(v_function TEXT, v_groupNames TEXT[]) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_create_log_schemas$ -- The function creates all log schemas that will be needed to create new log tables. It gives the appropriate rights to emaj users on -- these schemas. -- Input: calling function to record into the emaj_hist table, -- array of group names -- The function is created as SECURITY DEFINER so that log schemas can be owned by superuser DECLARE v_schemaPrefix TEXT = 'emaj_'; r_schema RECORD; BEGIN FOR r_schema IN SELECT DISTINCT v_schemaPrefix || grpdef_schema AS log_schema FROM emaj.emaj_group_def WHERE grpdef_group = ANY (v_groupNames) AND NOT EXISTS -- minus those already created (SELECT 0 FROM emaj.emaj_schema WHERE sch_name = v_schemaPrefix || grpdef_schema) ORDER BY 1 LOOP -- check that the schema doesn't already exist PERFORM 0 FROM pg_catalog.pg_namespace WHERE nspname = r_schema.log_schema; IF FOUND THEN RAISE EXCEPTION '_create_log_schemas: The schema "%" should not exist. Drop it manually.',r_schema.log_schema; END IF; -- create the schema and give the appropriate rights EXECUTE format('CREATE SCHEMA %I', r_schema.log_schema); EXECUTE format('GRANT ALL ON SCHEMA %I TO emaj_adm', r_schema.log_schema); EXECUTE format('GRANT USAGE ON SCHEMA %I TO emaj_viewer', r_schema.log_schema); -- and record the schema creation into the emaj_schema and the emaj_hist tables INSERT INTO emaj.emaj_schema (sch_name) VALUES (r_schema.log_schema); INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (v_function, 'LOG_SCHEMA CREATED', quote_ident(r_schema.log_schema)); END LOOP; RETURN; END; $_create_log_schemas$; CREATE OR REPLACE FUNCTION emaj._drop_log_schemas(v_function TEXT, v_isForced BOOLEAN) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_drop_log_schemas$ -- The function looks for log schemas to drop. Drop them if any. -- Input: calling function to record into the emaj_hist table, -- boolean telling whether the schema to drop may contain residual objects -- The function is created as SECURITY DEFINER so that log schemas can be dropped in any case. DECLARE r_schema RECORD; BEGIN -- For each log schema to drop, FOR r_schema IN SELECT sch_name AS log_schema FROM emaj.emaj_schema -- the existing schemas WHERE sch_name <> 'emaj' EXCEPT SELECT DISTINCT rel_log_schema FROM emaj.emaj_relation -- the currently needed schemas (after tables drop) WHERE rel_kind = 'r' AND rel_log_schema <> 'emaj' ORDER BY 1 LOOP -- check that the schema really exists PERFORM 0 FROM pg_catalog.pg_namespace WHERE nspname = r_schema.log_schema; IF NOT FOUND THEN RAISE EXCEPTION '_drop_log_schemas: Internal error (the schema "%" does not exist).',r_schema.log_schema; END IF; IF v_isForced THEN -- drop cascade when called by emaj_force_xxx_group() EXECUTE format('DROP SCHEMA %I CASCADE', r_schema.log_schema); ELSE -- otherwise, drop restrict with a trap on the potential error BEGIN EXECUTE format('DROP SCHEMA %I', r_schema.log_schema); EXCEPTION -- trap the 2BP01 exception to generate a more understandable error message WHEN DEPENDENT_OBJECTS_STILL_EXIST THEN -- SQLSTATE '2BP01' RAISE EXCEPTION '_drop_log_schemas: Cannot drop the schema "%". It probably owns unattended objects.' ' Use the emaj_verify_all() function to get details.', r_schema.log_schema; END; END IF; -- remove the schema from the emaj_schema table DELETE FROM emaj.emaj_schema WHERE sch_name = r_schema.log_schema; -- record the schema drop in emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (v_function,'LOG_SCHEMA DROPPED',quote_ident(r_schema.log_schema)); END LOOP; RETURN; END; $_drop_log_schemas$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_table(v_schema TEXT, v_table TEXT, v_group TEXT, v_properties JSONB DEFAULT NULL, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_table$ -- The function assigns a table into a tables group. -- Inputs: schema name, table name, assignment group name, assignment properties (optional), -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively assigned to the tables group, ie. 1 BEGIN RETURN emaj._assign_tables(v_schema, ARRAY[v_table], v_group, v_properties, v_mark, FALSE, FALSE); END; $emaj_assign_table$; COMMENT ON FUNCTION emaj.emaj_assign_table(TEXT,TEXT,TEXT,JSONB,TEXT) IS $$Assign a table into a tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_tables(v_schema TEXT, v_tables TEXT[], v_group TEXT, v_properties JSONB DEFAULT NULL, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_tables$ -- The function assigns several tables at once into a tables group. -- Inputs: schema, array of table names, assignment group name, assignment properties (optional), -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively assigned to the tables group BEGIN RETURN emaj._assign_tables(v_schema, v_tables, v_group, v_properties, v_mark, TRUE, FALSE); END; $emaj_assign_tables$; COMMENT ON FUNCTION emaj.emaj_assign_tables(TEXT,TEXT[],TEXT,JSONB,TEXT) IS $$Assign several tables into a tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_tables(v_schema TEXT, v_tablesIncludeFilter TEXT, v_tablesExcludeFilter TEXT, v_group TEXT, v_properties JSONB DEFAULT NULL, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_tables$ -- The function assigns tables on name regexp pattern into a tables group. -- Inputs: schema name, 2 patterns to filter table names (one to include and another to exclude) , assignment group name, -- assignment properties (optional), mark name to set when logging groups (optional) -- Outputs: number of tables effectively assigned to the tables group DECLARE v_tables TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_tablesIncludeFilter = '' THEN NULL ELSE v_tablesIncludeFilter END, CASE WHEN v_tablesExcludeFilter = '' THEN NULL ELSE v_tablesExcludeFilter END INTO v_tablesIncludeFilter, v_tablesExcludeFilter; -- Build the list of tables names satisfying the pattern SELECT array_agg(relname) INTO v_tables FROM ( SELECT relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname ~ v_tablesIncludeFilter AND (v_tablesExcludeFilter IS NULL OR relname !~ v_tablesExcludeFilter) AND relkind IN ('r', 'p') ORDER BY relname) AS t; -- call the _assign_tables() function for execution RETURN emaj._assign_tables(v_schema, v_tables, v_group, v_properties, v_mark, TRUE, TRUE); END; $emaj_assign_tables$; COMMENT ON FUNCTION emaj.emaj_assign_tables(TEXT,TEXT,TEXT,TEXT,JSONB,TEXT) IS $$Assign tables on name patterns into a tables group.$$; CREATE OR REPLACE FUNCTION emaj._assign_tables(v_schema TEXT, v_tables TEXT[], v_group TEXT, v_properties JSONB, v_mark TEXT, v_multiTable BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_assign_tables$ -- The function effectively assigns tables into a tables group. -- Inputs: schema, array of table names, group name, properties as JSON structure -- mark to set for lonnging groups, a boolean indicating whether several tables need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of tables effectively assigned to the tables group -- The JSONB v_properties parameter has the following structure '{"priority":..., "log_data_tablespace":..., "log_index_tablespace":...}' -- each properties being NULL by default -- The function is created as SECURITY DEFINER so that log schemas can be owned by superuser DECLARE v_function TEXT; v_groupIsRollbackable BOOLEAN; v_groupIsLogging BOOLEAN; v_priority INT; v_logDatTsp TEXT; v_logIdxTsp TEXT; v_extraProperties JSONB; v_list TEXT; v_array TEXT[]; v_timeId BIGINT; v_markName TEXT; v_schemaPrefix TEXT = 'emaj_'; v_logSchema TEXT; v_eventTriggers TEXT[]; v_oneTable TEXT; v_nbAssignedTbl INT = 0; BEGIN v_function = CASE WHEN v_multiTable THEN 'ASSIGN_TABLES' ELSE 'ASSIGN_TABLE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check supplied parameters -- check the group name and if ok, get some properties of the group PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_group], v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := ''); SELECT group_is_rollbackable, group_is_logging INTO v_groupIsRollbackable, v_groupIsLogging FROM emaj.emaj_group WHERE group_name = v_group; -- check the supplied schema exists and is not an E-Maj schema PERFORM 1 FROM pg_catalog.pg_namespace WHERE nspname = v_schema; IF NOT FOUND THEN RAISE EXCEPTION '_assign_tables: The schema "%" does not exist.', v_schema; END IF; PERFORM 1 FROM emaj.emaj_schema WHERE sch_name = v_schema; IF FOUND THEN RAISE EXCEPTION '_assign_tables: The schema "%" is an E-Maj schema.', v_schema; END IF; -- check tables IF NOT v_arrayFromRegex THEN -- from the tables array supplied by the user, remove duplicates values, NULL and empty strings from the supplied table names array SELECT array_agg(DISTINCT table_name) INTO v_tables FROM unnest(v_tables) AS table_name WHERE table_name IS NOT NULL AND table_name <> ''; -- check that application tables exist WITH tables AS ( SELECT unnest(v_tables) AS table_name) SELECT string_agg(quote_ident(table_name), ', ') INTO v_list FROM ( SELECT table_name FROM tables WHERE NOT EXISTS ( SELECT 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = table_name AND relkind IN ('r','p')) ) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) do not exist.', quote_ident(v_schema), v_list; END IF; END IF; -- check or discard partitioned application tables (only elementary partitions can be managed by E-Maj) SELECT string_agg(quote_ident(relname), ', '), array_agg(relname) INTO v_list, v_array FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = ANY(v_tables) AND relkind = 'p'; IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) are partitionned tables (only elementary partitions are supported' ' by E-Maj).', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some partitionned tables (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; -- check or discard TEMP tables SELECT string_agg(quote_ident(relname), ', '), array_agg(relname) INTO v_list, v_array FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = ANY(v_tables) AND relkind = 'r' AND relpersistence = 't'; IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) are TEMP tables.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some TEMP tables (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; -- check or discard UNLOGGED tables in rollbackable groups IF v_groupIsRollbackable THEN SELECT string_agg(quote_ident(relname), ', '), array_agg(relname) INTO v_list, v_array FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = ANY(v_tables) AND relkind = 'r' AND relpersistence = 'u'; IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) are UNLOGGED tables.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some UNLOGGED tables (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; END IF; -- with PG11-, check or discard WITH OIDS tables in rollbackable groups IF emaj._pg_version_num() < 120000 AND v_groupIsRollbackable THEN SELECT string_agg(quote_ident(relname), ', '), array_agg(relname) INTO v_list, v_array FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = ANY(v_tables) AND relkind = 'r' AND relhasoids; IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) are declared WITH OIDS.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some WITH OIDS tables (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; END IF; -- check or discard tables whithout primary key in rollbackable groups IF v_groupIsRollbackable THEN SELECT string_agg(quote_ident(relname), ', '), array_agg(relname) INTO v_list, v_array FROM pg_catalog.pg_class t, pg_catalog.pg_namespace WHERE t.relnamespace = pg_namespace.oid AND nspname = v_schema AND t.relname = ANY(v_tables) AND relkind = 'r' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace, pg_catalog.pg_constraint WHERE c.relnamespace = pg_namespace.oid AND connamespace = pg_namespace.oid AND conrelid = c.oid AND contype = 'p' AND nspname = v_schema AND c.relname = t.relname); IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) have no PRIMARY KEY.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some tables without PRIMARY KEY (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; END IF; -- check or discard tables already assigned to a group SELECT string_agg(quote_ident(rel_tblseq), ', '), array_agg(rel_tblseq) INTO v_list, v_array FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range); IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_tables: In schema %, some tables (%) already belong to a group.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_tables: Some tables already belonging to a group (%) are not selected.', v_list; -- remove these tables from the tables to process SELECT array_agg(remaining_table) INTO v_tables FROM (SELECT unnest(v_tables) EXCEPT SELECT unnest(v_array)) AS t(remaining_table); END IF; END IF; -- check the priority is numeric BEGIN v_priority = (v_properties->>'priority')::INT; EXCEPTION WHEN invalid_text_representation THEN RAISE EXCEPTION '_assign_tables: the "priority" property is not numeric.'; END; -- check that the tablespaces exist, if supplied v_logDatTsp = v_properties->>'log_data_tablespace'; IF v_logDatTsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = v_logDatTsp) THEN RAISE EXCEPTION '_assign_tables: the log data tablespace "%" does not exists.', v_logDatTsp; END IF; v_logIdxTsp = v_properties->>'log_index_tablespace'; IF v_logIdxTsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = v_logIdxTsp) THEN RAISE EXCEPTION '_assign_tables: the log index tablespace "%" does not exists.', v_logIdxTsp; END IF; -- check no properties are unknown v_extraProperties = v_properties - 'priority' - 'log_data_tablespace' - 'log_index_tablespace'; IF v_extraProperties IS NOT NULL AND v_extraProperties <> '{}' THEN RAISE EXCEPTION '_assign_tables: properties "%" are unknown.', v_extraProperties; END IF; -- check the supplied mark SELECT emaj._check_new_mark(array[v_group], v_mark) INTO v_markName; -- OK, IF v_tables IS NULL OR v_tables = '{}' THEN -- when no tables are finaly selected, just warn RAISE WARNING '_assign_tables: No table to process.'; ELSE -- 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_groupIsLogging 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(ARRAY[v_group], 'ROW EXCLUSIVE', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(ARRAY[v_group], v_markName, FALSE, TRUE, NULL, v_timeId); END IF; -- create new log schemas if needed v_logSchema = v_schemaPrefix || v_schema; IF NOT EXISTS (SELECT 0 FROM emaj.emaj_schema WHERE sch_name = v_logSchema) THEN -- check that the schema doesn't already exist PERFORM 0 FROM pg_catalog.pg_namespace WHERE nspname = v_logSchema; IF FOUND THEN RAISE EXCEPTION '_assign_tables: The schema "%" should not exist. Drop it manually.',v_logSchema; END IF; -- create the schema and give the appropriate rights EXECUTE format('CREATE SCHEMA %I', v_logSchema); EXECUTE format('GRANT ALL ON SCHEMA %I TO emaj_adm', v_logSchema); EXECUTE format('GRANT USAGE ON SCHEMA %I TO emaj_viewer', v_logSchema); -- and record the schema creation into the emaj_schema and the emaj_hist tables INSERT INTO emaj.emaj_schema (sch_name) VALUES (v_logSchema); INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (CASE WHEN v_multiTable THEN 'ASSIGN_TABLES' ELSE 'ASSIGN_TABLE' END, 'LOG_SCHEMA CREATED', quote_ident(v_logSchema)); END IF; -- disable event triggers that protect emaj components and keep in memory these triggers name SELECT emaj._disable_event_triggers() INTO v_eventTriggers; -- effectively create the log components for each table FOREACH v_oneTable IN ARRAY v_tables LOOP -- create the table PERFORM emaj._add_tbl(v_schema, v_oneTable, v_group, v_priority, v_logDatTsp, v_logIdxTsp, v_groupIsLogging, v_timeId, v_function); -- insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'ADD_TBL', v_schema, v_oneTable, v_group, v_groupIsLogging); v_nbAssignedTbl = v_nbAssignedTbl + 1; END LOOP; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); -- adjust the group characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, group_nb_table = (SELECT count(*) FROM emaj.emaj_relation WHERE rel_group = group_name AND upper_inf(rel_time_range) AND rel_kind = 'r') WHERE group_name = v_group; -- check foreign keys with tables outside the groups PERFORM emaj._check_fk_groups(array[v_group]); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbAssignedTbl || ' tables assigned to the group ' || v_group); RETURN v_nbAssignedTbl; END; $_assign_tables$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_table(v_schema TEXT, v_table TEXT, v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_table$ -- The function removes a table from its tables group. -- Inputs: schema name, table name, mark name to set when logging groups (optional) -- Outputs: number of tables effectively removed to the tables group, ie. 1 BEGIN RETURN emaj._remove_tables(v_schema, ARRAY[v_table], v_mark, FALSE, FALSE); END; $emaj_remove_table$; COMMENT ON FUNCTION emaj.emaj_remove_table(TEXT,TEXT,TEXT) IS $$Remove a table from its tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_tables(v_schema TEXT, v_tables TEXT[], v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_tables$ -- The function removes several tables at once from their tables group. -- Inputs: schema, array of table names, mark name to set when logging groups (optional) -- Outputs: number of tables effectively removed from the tables group BEGIN RETURN emaj._remove_tables(v_schema, v_tables, v_mark, TRUE, FALSE); END; $emaj_remove_tables$; COMMENT ON FUNCTION emaj.emaj_remove_tables(TEXT,TEXT[],TEXT) IS $$Remove several tables from their tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_tables(v_schema TEXT, v_tablesIncludeFilter TEXT, v_tablesExcludeFilter TEXT, v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_tables$ -- The function removes tables on name patterns from their tables group. -- Inputs: schema, 2 patterns to filter table names (one to include and another to exclude), -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively removed from the tables group DECLARE v_tables TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_tablesIncludeFilter = '' THEN NULL ELSE v_tablesIncludeFilter END, CASE WHEN v_tablesExcludeFilter = '' THEN NULL ELSE v_tablesExcludeFilter END INTO v_tablesIncludeFilter, v_tablesExcludeFilter; -- Build the list of tables names satisfying the pattern SELECT array_agg(rel_tblseq) INTO v_tables FROM ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq ~ v_tablesIncludeFilter AND (v_tablesExcludeFilter IS NULL OR rel_tblseq !~ v_tablesExcludeFilter) AND rel_kind = 'r' AND upper_inf(rel_time_range) ORDER BY rel_tblseq) AS t; -- call the _remove_tables() function for execution RETURN emaj._remove_tables(v_schema, v_tables, v_mark, TRUE, TRUE); END; $emaj_remove_tables$; COMMENT ON FUNCTION emaj.emaj_remove_tables(TEXT,TEXT,TEXT,TEXT) IS $$Remove several tables on name patterns from their tables group.$$; CREATE OR REPLACE FUNCTION emaj._remove_tables(v_schema TEXT, v_tables TEXT[], v_mark TEXT, v_multiTable BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_remove_tables$ -- The function effectively removes tables from their tables group. -- Inputs: schema, array of table names, mark to set if for logging groups, -- boolean to indicate whether several tables need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of tables effectively removed to the tables group -- The function is created as SECURITY DEFINER so that log schemas can be dropped DECLARE v_function TEXT; v_list TEXT; v_markName TEXT; v_timeId BIGINT; v_groups TEXT[]; v_loggingGroups TEXT[]; v_groupName TEXT; v_groupIsLogging BOOLEAN; v_schemaPrefix TEXT = 'emaj_'; v_eventTriggers TEXT[]; v_oneTable TEXT; v_logSchema TEXT; v_nbRemovedTbl INT = 0; BEGIN v_function = CASE WHEN v_multiTable THEN 'REMOVE_TABLES' ELSE 'REMOVE_TABLE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check the tables list IF NOT v_arrayFromRegex THEN -- remove duplicates values, NULL and empty strings from the supplied table names array SELECT array_agg(DISTINCT table_name) INTO v_tables FROM unnest(v_tables) AS table_name WHERE table_name IS NOT NULL AND table_name <> ''; -- check that the tables currently belong to a tables group (not necessarily the same for all tables) WITH all_supplied_tables AS ( SELECT unnest(v_tables) AS table_name), tables_in_group AS ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range)) SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(table_name), ', ') INTO v_list FROM ( SELECT table_name FROM all_supplied_tables EXCEPT SELECT rel_tblseq FROM tables_in_group) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_remove_tables: some tables (%) do not currently belong to any tables group.', v_list; END IF; END IF; -- get the lists of groups and logging groups holding these tables, if any. -- It locks the tables groups so that no other operation simultaneously occurs these groups WITH tables_group AS ( SELECT group_name, group_is_logging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range) FOR UPDATE OF emaj_group ) SELECT (SELECT array_agg(group_name) FROM tables_group), (SELECT array_agg(group_name) FROM tables_group WHERE group_is_logging) INTO v_groups, v_loggingGroups; -- check the supplied mark SELECT emaj._check_new_mark(v_loggingGroups, v_mark) INTO v_markName; -- OK, IF v_tables IS NULL THEN -- when no tables are finaly selected, just warn RAISE WARNING '_remove_tables: No table to process.'; ELSE v_logSchema = v_schemaPrefix || v_schema; -- 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', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, FALSE, 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; -- effectively drop the log components for each table FOREACH v_oneTable IN ARRAY v_tables LOOP -- get some characteristics of the group that holds the table SELECT rel_group, group_is_logging INTO v_groupName, v_groupIsLogging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = v_oneTable AND upper_inf(rel_time_range); -- drop this table PERFORM emaj._remove_tbl(v_schema, v_oneTable, v_groupName, v_groupIsLogging, v_timeId, v_function); -- insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'REMOVE_TBL', v_schema, v_oneTable, v_groupName, v_groupIsLogging); v_nbRemovedTbl = v_nbRemovedTbl + 1; END LOOP; -- drop the log schema if it is now useless IF NOT EXISTS (SELECT 0 FROM emaj.emaj_relation WHERE rel_log_schema = v_logSchema) THEN -- drop the schema EXECUTE format('DROP SCHEMA %I', v_logSchema); -- and record the schema drop into the emaj_schema and the emaj_hist tables DELETE FROM emaj.emaj_schema WHERE sch_name = v_logSchema; INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES (CASE WHEN v_multiTable THEN 'REMOVE_TABLES' ELSE 'REMOVE_TABLE' END, 'LOG_SCHEMA DROPPED', quote_ident(v_logSchema)); END IF; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); -- adjust the groups characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, group_nb_table = (SELECT count(*) FROM emaj.emaj_relation WHERE rel_group = group_name AND upper_inf(rel_time_range) AND rel_kind = 'r') WHERE group_name = ANY (v_groups); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbRemovedTbl || ' tables removed from their groups'); RETURN v_nbRemovedTbl; END; $_remove_tables$; CREATE OR REPLACE FUNCTION emaj.emaj_move_table(v_schema TEXT, v_table TEXT, v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_table$ -- The function moves a table from its tables group to another tables group. -- Inputs: schema name, table name, new group name, mark name to set when logging groups (optional) -- Outputs: number of tables effectively moved to the new tables group, ie. 1 BEGIN RETURN emaj._move_tables(v_schema, ARRAY[v_table], v_newGroup, v_mark, FALSE, FALSE); END; $emaj_move_table$; COMMENT ON FUNCTION emaj.emaj_move_table(TEXT,TEXT,TEXT,TEXT) IS $$Move a table from its tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_move_tables(v_schema TEXT, v_tables TEXT[], v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_tables$ -- The function moves several tables at once from their tables group to another tables group. -- Inputs: schema, array of table names, new group name, mark name to set when logging groups (optional) -- Outputs: number of tables effectively moved to the new tables group BEGIN RETURN emaj._move_tables(v_schema, v_tables, v_newGroup, v_mark, TRUE, FALSE); END; $emaj_move_tables$; COMMENT ON FUNCTION emaj.emaj_move_tables(TEXT,TEXT[],TEXT,TEXT) IS $$Move several tables from their tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_move_tables(v_schema TEXT, v_tablesIncludeFilter TEXT, v_tablesExcludeFilter TEXT, v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_tables$ -- The function moves tables on name patterns from their tables group to another tables group. -- Inputs: schema, 2 patterns to filter table names (one to include and another to exclude), new group name, -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively moved to the new tables group DECLARE v_tables TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_tablesIncludeFilter = '' THEN NULL ELSE v_tablesIncludeFilter END, CASE WHEN v_tablesExcludeFilter = '' THEN NULL ELSE v_tablesExcludeFilter END INTO v_tablesIncludeFilter, v_tablesExcludeFilter; -- Build the list of tables names satisfying the pattern SELECT array_agg(rel_tblseq) INTO v_tables FROM ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq ~ v_tablesIncludeFilter AND (v_tablesExcludeFilter IS NULL OR rel_tblseq !~ v_tablesExcludeFilter) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND rel_group <> v_newGroup ORDER BY rel_tblseq) AS t; -- call the _move_tables() function for execution RETURN emaj._move_tables(v_schema, v_tables, v_newGroup, v_mark, TRUE, TRUE); END; $emaj_move_tables$; COMMENT ON FUNCTION emaj.emaj_move_tables(TEXT,TEXT,TEXT,TEXT,TEXT) IS $$Move several tables on name patterns from their tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj._move_tables(v_schema TEXT, v_tables TEXT[], v_newGroup TEXT, v_mark TEXT, v_multiTable BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql AS $_move_tables$ -- The function effectively moves tables from their tables group to another tables group. -- Inputs: schema, array of table names, new group name, mark to set if for logging groups, -- boolean to indicate whether several tables need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of tables effectively moved to the tables group -- The function is created as SECURITY DEFINER so that log schemas can be dropped DECLARE v_function TEXT; v_newGroupIsLogging BOOLEAN; v_list TEXT; v_uselessTables TEXT[]; v_markName TEXT; v_timeId BIGINT; v_groups TEXT[]; v_loggingGroups TEXT[]; v_groupName TEXT; v_groupIsLogging BOOLEAN; v_oneTable TEXT; v_nbMovedTbl INT = 0; BEGIN v_function = CASE WHEN v_multiTable THEN 'MOVE_TABLES' ELSE 'MOVE_TABLE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check the group name and if ok, get some properties of the group PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_newGroup], v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := ''); SELECT group_is_logging INTO v_newGroupIsLogging FROM emaj.emaj_group WHERE group_name = v_NewGroup; -- check the tables list IF NOT v_arrayFromRegex THEN -- remove duplicates values, NULL and empty strings from the supplied table names array SELECT array_agg(DISTINCT table_name) INTO v_tables FROM unnest(v_tables) AS table_name WHERE table_name IS NOT NULL AND table_name <> ''; -- check that the tables currently belong to a tables group (not necessarily the same for all table) WITH all_supplied_tables AS ( SELECT unnest(v_tables) AS table_name), tables_in_group AS ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range)) SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(table_name), ', ') INTO v_list FROM ( SELECT table_name FROM all_supplied_tables EXCEPT SELECT rel_tblseq FROM tables_in_group) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_move_tables: some tables (%) do not currently belong to any tables group.', v_list; END IF; -- remove tables that already belong to the new group SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(rel_tblseq), ', '), array_agg(rel_tblseq) INTO v_list, v_uselessTables FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range) AND rel_group = v_newGroup; IF v_list IS NOT NULL THEN RAISE WARNING '_move_tables: some tables (%) already belong to the tables group %.', v_list, v_newGroup; SELECT array_remove(v_tables, useless_table) INTO v_tables FROM unnest(v_uselessTables) AS useless_table; END IF; END IF; -- get the lists of groups and logging groups holding these tables, if any. -- It locks the tables groups so that no other operation simultaneously occurs these groups -- (the CTE is needed for the FOR UPDATE clause not allowed when aggregate functions) WITH tables_group AS ( SELECT group_name, group_is_logging FROM emaj.emaj_group WHERE group_name = v_newGroup OR group_name IN (SELECT DISTINCT rel_group FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range)) FOR UPDATE OF emaj_group ) SELECT array_agg(group_name ORDER BY group_name), array_agg(group_name ORDER BY group_name) FILTER (WHERE group_is_logging) INTO v_groups, v_loggingGroups FROM tables_group; -- check the supplied mark SELECT emaj._check_new_mark(v_loggingGroups, v_mark) INTO v_markName; -- OK, IF v_tables IS NULL THEN -- when no tables are finaly selected, just warn RAISE WARNING '_move_tables: No table to process.'; ELSE -- 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', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, TRUE, TRUE, NULL, v_timeId); END IF; -- effectively move each table FOREACH v_oneTable IN ARRAY v_tables LOOP -- get some characteristics of the group that holds the table before the move SELECT rel_group, group_is_logging INTO v_groupName, v_groupIsLogging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = v_oneTable AND upper_inf(rel_time_range); -- move this table PERFORM emaj._move_tbl(v_schema, v_oneTable, v_groupName, v_groupIsLogging, v_newGroup, v_newGroupIsLogging, v_timeId, v_function); -- insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging, altr_new_group, altr_new_group_is_logging) VALUES (v_timeId, 'MOVE_TBL', v_schema, v_oneTable, v_groupName, v_groupIsLogging, v_newGroup, v_newGroupIsLogging); v_nbMovedTbl = v_nbMovedTbl + 1; END LOOP; -- adjust the groups characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, group_nb_table = (SELECT count(*) FROM emaj.emaj_relation WHERE rel_group = group_name AND upper_inf(rel_time_range) AND rel_kind = 'r') WHERE group_name = ANY (v_groups); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbMovedTbl || ' tables moved to the new tables group ' || v_newGroup); RETURN v_nbMovedTbl; END; $_move_tables$; CREATE OR REPLACE FUNCTION emaj.emaj_modify_table(v_schema TEXT, v_table TEXT, v_changedProperties JSONB, v_mark TEXT DEFAULT 'MODIFY_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_modify_table$ -- The function modifies the assignment properties of a table. -- Inputs: schema name, table name, assignment properties changes, -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively modified, ie 0 or 1 BEGIN RETURN emaj._modify_tables(v_schema, ARRAY[v_table], v_changedProperties, v_mark, FALSE, FALSE); END; $emaj_modify_table$; COMMENT ON FUNCTION emaj.emaj_modify_table(TEXT,TEXT,JSONB,TEXT) IS $$Modify the assignment properties of a table.$$; CREATE OR REPLACE FUNCTION emaj.emaj_modify_tables(v_schema TEXT, v_tables TEXT[], v_changedProperties JSONB, v_mark TEXT DEFAULT 'MODIFY_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_modify_tables$ -- The function modifies the assignment properties for several tables at once. -- Inputs: schema, array of table names, assignment properties, -- mark name to set when logging groups (optional) -- Outputs: number of tables effectively modified BEGIN RETURN emaj._modify_tables(v_schema, v_tables, v_changedProperties, v_mark, TRUE, FALSE); END; $emaj_modify_tables$; COMMENT ON FUNCTION emaj.emaj_modify_tables(TEXT,TEXT[],JSONB,TEXT) IS $$Modify the assignment properties of several tables.$$; CREATE OR REPLACE FUNCTION emaj.emaj_modify_tables(v_schema TEXT, v_tablesIncludeFilter TEXT, v_tablesExcludeFilter TEXT, v_properties JSONB, v_mark TEXT DEFAULT 'MODIFY_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_modify_tables$ -- The function modifies the assignment properties for several tables selected on name regexp pattern at once. -- Inputs: schema name, 2 patterns to filter table names (one to include and another to exclude), -- assignment properties, mark name to set when logging groups (optional) -- Outputs: number of tables effectively modified DECLARE v_tables TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_tablesIncludeFilter = '' THEN NULL ELSE v_tablesIncludeFilter END, CASE WHEN v_tablesExcludeFilter = '' THEN NULL ELSE v_tablesExcludeFilter END INTO v_tablesIncludeFilter, v_tablesExcludeFilter; -- Build the list of tables names satisfying the pattern SELECT array_agg(relname) INTO v_tables FROM ( SELECT relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname ~ v_tablesIncludeFilter AND (v_tablesExcludeFilter IS NULL OR relname !~ v_tablesExcludeFilter) AND relkind IN ('r', 'p') ORDER BY relname) AS t; -- call the _modify_tables() function for execution RETURN emaj._modify_tables(v_schema, v_tables, v_properties, v_mark, TRUE, TRUE); END; $emaj_modify_tables$; COMMENT ON FUNCTION emaj.emaj_modify_tables(TEXT,TEXT,TEXT,JSONB,TEXT) IS $$Modify the assignment properties of several tables selected on name patterns.$$; CREATE OR REPLACE FUNCTION emaj._modify_tables(v_schema TEXT, v_tables TEXT[], v_changedProperties JSONB, v_mark TEXT, v_multiTable BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql AS $_modify_tables$ -- The function effectively modify the assignment properties of tables. -- Inputs: schema, array of table names, properties as JSON structure -- mark to set for logging groups, a boolean indicating whether several tables need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of tables effectively modified -- The JSONB v_properties parameter has the following structure '{"priority":..., "log_data_tablespace":..., "log_index_tablespace":...}' -- each properties can be set to NULL to delete a previously set value DECLARE v_function TEXT; v_priorityChanged BOOLEAN = FALSE; v_newPriority INT; v_logDatTspChanged BOOLEAN = FALSE; v_newLogDatTsp TEXT; v_logIdxTspChanged BOOLEAN = FALSE; v_newLogIdxTsp TEXT; v_extraProperties JSONB; v_list TEXT; v_array TEXT[]; v_groups TEXT[]; v_loggingGroups TEXT[]; v_timeId BIGINT; v_markName TEXT; v_isTableChanged BOOLEAN; v_nbChangedTbl INT = 0; r_rel RECORD; BEGIN v_function = CASE WHEN v_multiTable THEN 'MODIFY_TABLES' ELSE 'MODIFY_TABLE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check supplied parameters -- check tables IF NOT v_arrayFromRegex THEN -- from the tables array supplied by the user, remove duplicates values, NULL and empty strings from the supplied table names array SELECT array_agg(DISTINCT table_name) INTO v_tables FROM unnest(v_tables) AS table_name WHERE table_name IS NOT NULL AND table_name <> ''; -- check that the tables currently belong to a tables group (not necessarily the same for all tables) WITH all_supplied_tables AS ( SELECT unnest(v_tables) AS table_name), tables_in_group AS ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range)) SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(table_name), ', ') INTO v_list FROM ( SELECT table_name FROM all_supplied_tables EXCEPT SELECT rel_tblseq FROM tables_in_group) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_modify_tables: some tables (%) do not currently belong to any tables group.', v_list; END IF; END IF; -- get the new priority, if supplied, and check the priority is numeric IF v_changedProperties ? 'priority' THEN BEGIN v_newPriority = (v_changedProperties->>'priority')::INT; EXCEPTION WHEN invalid_text_representation THEN RAISE EXCEPTION '_modify_tables: the "priority" property is not numeric.'; END; v_priorityChanged = TRUE; END IF; -- get the new tablespaces if supplied and check that the tablespaces exist IF v_changedProperties ? 'log_data_tablespace' THEN v_newLogDatTsp = v_changedProperties->>'log_data_tablespace'; IF v_newLogDatTsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = v_newLogDatTsp) THEN RAISE EXCEPTION '_modify_tables: the log data tablespace "%" does not exists.', v_newLogDatTsp; END IF; v_logDatTspChanged = TRUE; END IF; IF v_changedProperties ? 'log_index_tablespace' THEN v_newLogIdxTsp = v_changedProperties->>'log_index_tablespace'; IF v_newLogIdxTsp IS NOT NULL AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tablespace WHERE spcname = v_newLogIdxTsp) THEN RAISE EXCEPTION '_modify_tables: the log index tablespace "%" does not exists.', v_newLogIdxTsp; END IF; v_logIdxTspChanged = TRUE; END IF; -- check no properties are unknown v_extraProperties = v_changedProperties - 'priority' - 'log_data_tablespace' - 'log_index_tablespace'; IF v_extraProperties IS NOT NULL AND v_extraProperties <> '{}' THEN RAISE EXCEPTION '_modify_tables: properties "%" are unknown.', v_extraProperties; END IF; -- get the lists of groups and logging groups holding these tables, if any. -- The FOR UPDATE clause locks the tables groups so that no other operation simultaneously occurs on these groups -- (the CTE is needed for the FOR UPDATE clause not allowed when aggregate functions) WITH tables_group AS ( SELECT group_name, group_is_logging FROM emaj.emaj_group WHERE group_name IN (SELECT DISTINCT rel_group FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range)) FOR UPDATE OF emaj_group ) SELECT array_agg(group_name ORDER BY group_name), array_agg(group_name ORDER BY group_name) FILTER (WHERE group_is_logging) INTO v_groups, v_loggingGroups FROM tables_group; -- check the supplied mark SELECT emaj._check_new_mark(v_loggingGroups, v_mark) INTO v_markName; -- OK, IF v_tables IS NULL OR v_tables = '{}' THEN -- when no tables are finaly selected, just warn RAISE WARNING '_modified_tables: No table to process.'; ELSE -- 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', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, TRUE, TRUE, NULL, v_timeId); END IF; -- process the changes for each table, if any FOR r_rel IN SELECT rel_tblseq, rel_time_range, rel_log_schema, rel_priority, rel_log_table, rel_log_index, rel_log_dat_tsp, rel_log_idx_tsp, rel_group, group_is_logging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = ANY(v_tables) AND upper_inf(rel_time_range) ORDER BY rel_priority, rel_schema, rel_tblseq LOOP v_isTableChanged = FALSE; -- change the priority, if needed IF v_priorityChanged AND (r_rel.rel_priority <> v_newPriority OR (r_rel.rel_priority IS NULL AND v_newPriority IS NOT NULL) OR (r_rel.rel_priority IS NOT NULL AND v_newPriority IS NULL)) THEN v_isTableChanged = TRUE; -- call the dedicated function PERFORM emaj._change_priority_tbl(v_schema, r_rel.rel_tblseq, r_rel.rel_priority, v_newPriority, v_function); -- and insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority, altr_group_is_logging) VALUES (v_timeId, 'CHANGE_REL_PRIORITY', v_schema, r_rel.rel_tblseq, r_rel.rel_group, v_newPriority, r_rel.group_is_logging); END IF; -- change the log data tablespace, if needed IF v_logDatTspChanged AND coalesce(v_newLogDatTsp, '') <> coalesce(r_rel.rel_log_dat_tsp, '') THEN v_isTableChanged = TRUE; -- call the dedicated function PERFORM emaj._change_log_data_tsp_tbl(v_schema, r_rel.rel_tblseq, r_rel.rel_log_schema, r_rel.rel_log_table, r_rel.rel_log_dat_tsp, v_newLogDatTsp, v_function); -- and insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'CHANGE_TBL_LOG_DATA_TSP', v_schema, r_rel.rel_tblseq, r_rel.rel_group, r_rel.group_is_logging); END IF; -- change the log index tablespace, if needed IF v_logIdxTspChanged AND coalesce(v_newLogIdxTsp, '') <> coalesce(r_rel.rel_log_idx_tsp, '') THEN v_isTableChanged = TRUE; -- call the dedicated function PERFORM emaj._change_log_index_tsp_tbl(v_schema, r_rel.rel_tblseq, r_rel.rel_log_schema, r_rel.rel_log_index, r_rel.rel_log_idx_tsp, v_newLogIdxTsp, v_function); -- and insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'CHANGE_TBL_LOG_INDEX_TSP', v_schema, r_rel.rel_tblseq, r_rel.rel_group, r_rel.group_is_logging); END IF; IF v_isTableChanged THEN v_nbChangedTbl = v_nbChangedTbl + 1; END IF; END LOOP; -- adjust the groups characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId WHERE group_name = ANY(v_groups); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbChangedTbl || ' tables effectively modified'); RETURN v_nbChangedTbl; END; $_modify_tables$; CREATE OR REPLACE FUNCTION emaj._create_tbl(v_schema TEXT, v_tbl TEXT, v_groupName TEXT, v_priority INT, v_logDatTsp TEXT, v_logIdxTsp TEXT, v_timeId BIGINT, v_groupIsRollbackable BOOLEAN, v_groupIsLogging BOOLEAN) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_create_tbl$ -- This function creates all what is needed to manage the log and rollback operations for an application table. -- Input: the application table to process, the group to add it into, the priority and tablespaces attributes, the time id of the -- operation, 2 booleans indicating whether the group is rollbackable and whether the group is currently in logging state. -- The objects 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_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 IF length(v_tbl) <= 50 THEN -- for not too long table name, the prefix is the table name itself v_emajNamesPrefix = v_tbl; ELSE -- for long table names (over 50 char long), compute the suffix to add to the first 50 characters (#1, #2, ...), by looking at the -- existing names SELECT substr(v_tbl, 1, 50) || '#' || coalesce(max(suffix) + 1, 1)::TEXT INTO v_emajNamesPrefix FROM (SELECT unnest(regexp_matches(substr(rel_log_table, 51),'#(\d+)'))::INT AS suffix FROM emaj.emaj_relation WHERE substr(rel_log_table, 1, 50) = substr(v_tbl, 1, 50) ) AS t; END IF; -- 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 = v_schemaPrefix || v_schema; v_fullTableName = quote_ident(v_schema) || '.' || quote_ident(v_tbl); 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(v_logDatTsp),''); v_idxTblSpace = coalesce('TABLESPACE ' || quote_ident(v_logIdxTsp),''); -- 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 format('DROP TABLE IF EXISTS %s', v_logTableName); EXECUTE format('CREATE TABLE %s (LIKE %s) %s', v_logTableName, v_fullTableName, v_dataTblSpace); EXECUTE format('ALTER TABLE %s' ' 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', v_logTableName); -- 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 format('ALTER TABLE %s %s', v_logTableName, v_alter_log_table_param); END IF; -- create the index on the log table EXECUTE format('CREATE UNIQUE INDEX %s ON %s(emaj_gid, emaj_tuple)', v_logIdxName, v_logTableName, v_idxTblSpace); -- set the index associated to the primary key as cluster index. It may be useful for CLUSTER command. EXECUTE format('ALTER TABLE ONLY %s CLUSTER ON %s', v_logTableName, 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 format('ALTER TABLE %s %s', v_logTableName, v_stmt); END IF; -- create the sequence associated to the log table EXECUTE format('CREATE SEQUENCE %s', v_sequenceName); -- create the log function and the log trigger PERFORM emaj._create_log_trigger(v_fullTableName, v_logTableName, v_sequenceName, v_logFnctName); -- If the group is idle, deactivate the log trigger (it will be enabled at emaj_start_group time) IF NOT v_groupIsLogging THEN EXECUTE format('ALTER TABLE %s DISABLE TRIGGER emaj_log_trg', v_fullTableName); END IF; -- 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 format('DROP TRIGGER IF EXISTS emaj_trunc_trg ON %s', v_fullTableName); IF v_groupIsRollbackable THEN -- For rollbackable groups, use the common _forbid_truncate_fnct() function that blocks the operation EXECUTE format('CREATE TRIGGER emaj_trunc_trg' ' BEFORE TRUNCATE ON %s' ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._forbid_truncate_fnct()', v_fullTableName); ELSE -- For audit_only groups, use the common _log_truncate_fnct() function that records the operation into the log table EXECUTE format('CREATE TRIGGER emaj_trunc_trg' ' BEFORE TRUNCATE ON %s' ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._log_truncate_fnct()', v_fullTableName); END IF; IF NOT v_groupIsLogging THEN EXECUTE format('ALTER TABLE %s DISABLE TRIGGER emaj_trunc_trg', v_fullTableName); END IF; -- 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 (v_schema, v_tbl, int8range(v_timeId, NULL, '[)'), v_groupName, v_priority, v_logSchema, v_logDatTsp, v_logIdxTsp, '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 (%). They will be automatically disabled during E-Maj rollback operations,' ' unless they have been recorded into the list of triggers that may be kept enabled, with the' ' emaj_ignore_app_trigger() function.', v_fullTableName, v_triggerList; END IF; -- grant appropriate rights to both emaj roles EXECUTE format('GRANT SELECT ON TABLE %s TO emaj_viewer', v_logTableName); EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %s TO emaj_adm', v_logTableName); EXECUTE format('GRANT SELECT ON SEQUENCE %s TO emaj_viewer', v_sequenceName); EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %s TO emaj_adm', v_sequenceName); RETURN; END; $_create_tbl$; CREATE OR REPLACE FUNCTION emaj._create_log_trigger(v_fullTableName TEXT, v_logTableName TEXT, v_sequenceName TEXT, v_logFnctName TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_create_log_trigger$ -- The function creates the log function and the associated log trigger for an application table. -- It is called by several functions. -- Inputs: the full name of the application table, the log table, the log sequence and the log function -- The function is defined as SECURITY DEFINER so that emaj_adm role can manage the trigger on the application table. DECLARE BEGIN -- drop the log trigger if it exists EXECUTE format('DROP TRIGGER IF EXISTS emaj_log_trg ON %s', v_fullTableName); -- create the log fonction that will be mapped to the log trigger just after -- the new row is logged for each INSERT, the old row is logged for each DELETE -- and the old and the new rows are logged for each UPDATE. EXECUTE 'CREATE OR REPLACE FUNCTION ' || v_logFnctName || '() RETURNS TRIGGER AS $logfnct$' || 'BEGIN' -- The sequence associated to the log table is incremented at the beginning of the function ... || ' PERFORM NEXTVAL(' || quote_literal(v_sequenceName) || ');' -- ... and the global id sequence is incremented by the first/only INSERT into the log table. || ' IF (TG_OP = ''DELETE'') THEN' || ' INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''DEL'', ''OLD'';' || ' RETURN OLD;' || ' ELSIF (TG_OP = ''UPDATE'') THEN' || ' INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''UPD'', ''OLD'';' || ' INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''UPD'', ''NEW'', lastval();' || ' RETURN NEW;' || ' ELSIF (TG_OP = ''INSERT'') THEN' || ' INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''INS'', ''NEW'';' || ' RETURN NEW;' || ' END IF;' || ' RETURN NULL;' || 'END;' || '$logfnct$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp;'; -- create the log trigger on the application table, using the previously created log function EXECUTE format('CREATE TRIGGER emaj_log_trg' ' AFTER INSERT OR UPDATE OR DELETE ON %s' ' FOR EACH ROW EXECUTE PROCEDURE %s()', v_fullTableName, v_logFnctName); RETURN; END; $_create_log_trigger$; CREATE OR REPLACE FUNCTION emaj._add_tbl(v_schema TEXT, v_table TEXT, v_group TEXT, v_priority INT, v_logDatTsp TEXT, v_logIdxTsp TEXT, v_groupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_add_tbl$ -- The function adds a table to a group. It is called during an alter group or a dynamic assignment operation. -- If the group is in idle state, it simply calls the _create_tbl() function. -- Otherwise, it calls the _create_tbl() function, activates the log trigger and -- sets a restart value for the log sequence if a previous range exists for the relation. -- Required inputs: schema and table to add, group name, priority, log data and index tablespace, the group's logging state, -- the time stamp id of the operation, main calling function. -- The function is defined as SECURITY DEFINER so that emaj_adm role can enable triggers on application tables. DECLARE v_groupIsRollbackable BOOLEAN; v_logSchema TEXT; v_logSequence TEXT; v_nextVal BIGINT; BEGIN -- get the is_rollbackable status of the related group SELECT group_is_rollbackable INTO v_groupIsRollbackable FROM emaj.emaj_group WHERE group_name = v_group; -- create the table PERFORM emaj._create_tbl(v_schema, v_table, v_group, v_priority, v_logDatTsp, v_logIdxTsp, v_timeId, v_groupIsRollbackable, v_groupIsLogging); -- if the group is in logging state, perform additional tasks IF v_groupIsLogging THEN -- ... get the log schema and sequence for the new relation SELECT rel_log_schema, rel_log_sequence INTO v_logSchema, v_logSequence FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- ... get the last log sequence value, if any, for this relation (recorded in emaj_relation at a previous REMOVE_TBL operation) SELECT max(rel_log_seq_last_value) + 1 INTO v_nextVal FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table AND rel_log_seq_last_value IS NOT NULL; -- ... set the new log sequence next_val, if needed IF v_nextVal IS NOT NULL AND v_nextVal > 1 THEN EXECUTE format('ALTER SEQUENCE %I.%I RESTART %s', v_logSchema, v_logSequence, v_nextVal); END IF; -- ... record the new log sequence state in the emaj_sequence table for the current operation mark 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 * FROM emaj._get_current_sequence_state(v_logSchema, v_logSequence, v_timeId); END IF; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'TABLE ADDED', quote_ident(v_schema) || '.' || quote_ident(v_table), 'To the ' || CASE WHEN v_groupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_group); RETURN; END; $_add_tbl$; CREATE OR REPLACE FUNCTION emaj._change_priority_tbl(v_schema TEXT, v_table TEXT, v_currentPriority INT, v_newPriority INT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql AS $_change_priority_tbl$ -- This function changes the priority for an application table. -- Input: the table identity, the old and new priorities and the calling function. BEGIN -- update the emaj_relation row for the table UPDATE emaj.emaj_relation SET rel_priority = v_newPriority FROM emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'PRIORITY CHANGED', quote_ident(v_schema) || '.' || quote_ident(v_table), coalesce(v_currentPriority::char, 'NULL') || ' => ' || coalesce(v_newPriority::char, 'NULL')); RETURN; END; $_change_priority_tbl$; CREATE OR REPLACE FUNCTION emaj._change_log_data_tsp_tbl(v_schema TEXT, v_table TEXT, v_logSchema TEXT, v_currentLogTable TEXT, v_currentLogDatTsp TEXT, v_newLogDatTsp TEXT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_change_log_data_tsp_tbl$ -- This function changes the log data tablespace for an application table. -- Input: the existing emaj_relation characteristics for the table, the new log data tablespace and the calling function. -- 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_newTsp TEXT; BEGIN -- build the new data tablespace name. If needed, get the name of the current default tablespace. v_newTsp = v_newLogDatTsp; IF v_newTsp IS NULL OR v_newTsp = '' THEN v_newTsp = emaj._get_default_tablespace(); END IF; -- process the log data tablespace change EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_logSchema, v_currentLogTable, v_newTsp); -- update the table attributes into emaj_relation UPDATE emaj.emaj_relation SET rel_log_dat_tsp = v_newLogDatTsp WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'LOG DATA TABLESPACE CHANGED', quote_ident(v_schema) || '.' || quote_ident(v_table), coalesce(v_currentLogDatTsp, 'Default tablespace') || ' => ' || coalesce(v_newLogDatTsp, 'Default tablespace')); RETURN; END; $_change_log_data_tsp_tbl$; CREATE OR REPLACE FUNCTION emaj._change_log_index_tsp_tbl(v_schema TEXT, v_table TEXT, v_logSchema TEXT, v_currentLogIndex TEXT, v_currentLogIdxTsp TEXT, v_newLogIdxTsp TEXT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_change_log_index_tsp_tbl$ -- This function changes the log index tablespace for an application table. -- Input: the existing emaj_relation characteristics for the table, the new log index tablespace and the calling function. -- 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_newTsp TEXT; BEGIN -- build the new data tablespace name. If needed, get the name of the current default tablespace. v_newTsp = v_newLogIdxTsp; IF v_newTsp IS NULL OR v_newTsp = '' THEN v_newTsp = emaj._get_default_tablespace(); END IF; -- process the log index tablespace change EXECUTE format('ALTER INDEX %I.%I SET TABLESPACE %I', v_logSchema, v_currentLogIndex, v_newTsp); -- update the table attributes into emaj_relation UPDATE emaj.emaj_relation SET rel_log_idx_tsp = v_newLogIdxTsp WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'LOG INDEX TABLESPACE CHANGED', quote_ident(v_schema) || '.' || quote_ident(v_table), coalesce(v_currentLogIdxTsp, 'Default tablespace') || ' => ' || coalesce(v_newLogIdxTsp, 'Default tablespace')); RETURN; END; $_change_log_index_tsp_tbl$; CREATE OR REPLACE FUNCTION emaj._remove_tbl(v_schema TEXT, v_table TEXT, v_group TEXT, v_groupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_remove_tbl$ -- The function removes a table from a group. It is called during an alter group or a dynamic removal operation. -- If the group is in idle state, it simply calls the _drop_tbl() function. -- Otherwise, only triggers, log function and log sequence are dropped now. The other components will be dropped later (at reset_group -- time for instance). -- Required inputs: schema and sequence to remove, related group name and logging state, -- time stamp id of the operation, main calling function. -- The function is defined as SECURITY DEFINER so that emaj_adm role can drop triggers on application tables. DECLARE v_logSchema TEXT; v_currentLogTable TEXT; v_currentLogIndex TEXT; v_logFunction TEXT; v_logSequence TEXT; v_logSequenceLastValue BIGINT; v_namesSuffix TEXT; v_fullTableName TEXT; BEGIN IF NOT v_groupIsLogging THEN -- if the group is in idle state, drop the table immediately PERFORM emaj._drop_tbl(emaj.emaj_relation.*, v_timeId) FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); ELSE -- if the group is in logging state, ... -- ... get the current relation characteristics SELECT rel_log_schema, rel_log_table, rel_log_index, rel_log_function, rel_log_sequence INTO v_logSchema, v_currentLogTable, v_currentLogIndex, v_logFunction, v_logSequence FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- ... get the current log sequence characteristics SELECT CASE WHEN sequ_is_called THEN sequ_last_val ELSE sequ_last_val - sequ_increment END INTO STRICT v_logSequenceLastValue FROM emaj.emaj_sequence WHERE sequ_schema = v_logSchema AND sequ_name = v_logSequence AND sequ_time_id = v_timeId; -- ... compute the suffix to add to the log table and index names (_1, _2, ...), by looking at the existing names SELECT '_' || coalesce(max(suffix) + 1, 1)::TEXT INTO v_namesSuffix FROM (SELECT unnest(regexp_matches(rel_log_table,'_(\d+)$'))::INT AS suffix FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table ) AS t; -- ... rename the log table and its index (they may have been dropped) EXECUTE format('ALTER TABLE IF EXISTS %I.%I RENAME TO %I', v_logSchema, v_currentLogTable, v_currentLogTable || v_namesSuffix); EXECUTE format('ALTER INDEX IF EXISTS %I.%I RENAME TO %I', v_logSchema, v_currentLogIndex, v_currentLogIndex || v_namesSuffix); -- ... drop the log and truncate triggers -- (check the application table exists before dropping its triggers to avoid an error fires with postgres version <= 9.3) v_fullTableName = quote_ident(v_schema) || '.' || quote_ident(v_table); PERFORM 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = v_table AND relkind = 'r'; IF FOUND THEN EXECUTE format('DROP TRIGGER IF EXISTS emaj_log_trg ON %s', v_fullTableName); EXECUTE format('DROP TRIGGER IF EXISTS emaj_trunc_trg ON %s', v_fullTableName); END IF; -- ... drop the log function and the log sequence -- (but we keep the sequence related data in the emaj_sequence and the emaj_seq_hole tables) EXECUTE format('DROP FUNCTION IF EXISTS %I.%I() CASCADE', v_logSchema, v_logFunction); EXECUTE format('DROP SEQUENCE IF EXISTS %I.%I', v_logSchema, v_logSequence); -- ... register the end of the relation time frame, the last value of the log sequence, the log table and index names change, -- reflect the changes into the emaj_relation rows -- - for all timeranges pointing to this log table and index -- (do not reset the rel_log_sequence value: it will be needed later for _drop_tbl() for the emaj_sequence cleanup) UPDATE emaj.emaj_relation SET rel_log_table = v_currentLogTable || v_namesSuffix , rel_log_index = v_currentLogIndex || v_namesSuffix, rel_log_function = NULL, rel_sql_columns = NULL, rel_sql_pk_columns = NULL, rel_sql_pk_eq_conditions = NULL, rel_log_seq_last_value = v_logSequenceLastValue WHERE rel_schema = v_schema AND rel_tblseq = v_table AND rel_log_table = v_currentLogTable; -- - and close the last timerange UPDATE emaj.emaj_relation SET rel_time_range = int8range(lower(rel_time_range), v_timeId, '[)') WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); END IF; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'TABLE REMOVED', quote_ident(v_schema) || '.' || quote_ident(v_table), 'From the ' || CASE WHEN v_groupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_group); RETURN; END; $_remove_tbl$; CREATE OR REPLACE FUNCTION emaj._move_tbl(v_schema TEXT, v_table TEXT, v_oldGroup TEXT, v_oldGroupIsLogging BOOLEAN, v_newGroup TEXT, v_newGroupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql AS $_move_tbl$ -- The function change the group ownership of a table. It is called during an alter group or a dynamic assignment operation. -- Required inputs: schema and table to move, old and new group names and their logging state, -- time stamp id of the operation, main calling function. DECLARE v_logSchema TEXT; v_logSequence TEXT; BEGIN -- 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 = v_schema AND rel_tblseq = v_table 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, '[)'), v_newGroup, 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 = v_schema AND rel_tblseq = v_table AND upper(rel_time_range) = v_timeId; -- if the table enters in a group in logging state, IF NOT v_oldGroupIsLogging AND v_newGroupIsLogging THEN -- ... get the log schema and sequence for the new relation SELECT rel_log_schema, rel_log_sequence INTO v_logSchema, v_logSequence FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_table AND upper_inf(rel_time_range); -- ... record the new log sequence state in the emaj_sequence table for the current operation mark 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 * FROM emaj._get_current_sequence_state(v_logSchema, v_logSequence, v_timeId); END IF; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'TABLE MOVED', quote_ident(v_schema) || '.' || quote_ident(v_table), 'From the ' || CASE WHEN v_oldGroupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_oldGroup || ' to the ' || CASE WHEN v_newGroupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_newGroup); RETURN; END; $_move_tbl$; CREATE OR REPLACE FUNCTION emaj._drop_tbl(r_rel emaj.emaj_relation, v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_drop_tbl$ -- The function deletes a timerange for a table. This centralizes the deletion of all what has been created by _create_tbl() function. -- Required inputs: row from emaj_relation corresponding to the appplication table to proccess, time id. -- 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_fullTableName TEXT; BEGIN v_fullTableName = quote_ident(r_rel.rel_schema) || '.' || quote_ident(r_rel.rel_tblseq); -- if the table is currently linked to a group, drop the log trigger, function and sequence IF upper_inf(r_rel.rel_time_range) THEN -- check the table exists before dropping its triggers PERFORM 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = r_rel.rel_schema AND relname = r_rel.rel_tblseq AND relkind = 'r'; IF FOUND THEN -- drop the log and truncate triggers on the application table EXECUTE format('DROP TRIGGER IF EXISTS emaj_log_trg ON %s', v_fullTableName); EXECUTE format('DROP TRIGGER IF EXISTS emaj_trunc_trg ON %s', v_fullTableName); END IF; -- drop the log function IF r_rel.rel_log_function IS NOT NULL THEN EXECUTE format('DROP FUNCTION IF EXISTS %I.%I() CASCADE', r_rel.rel_log_schema, r_rel.rel_log_function); END IF; -- drop the sequence associated to the log table EXECUTE format('DROP SEQUENCE IF EXISTS %I.%I', r_rel.rel_log_schema, r_rel.rel_log_sequence); END IF; -- drop the log table if it is not referenced on other timeranges (for potentially other groups) IF NOT EXISTS(SELECT 1 FROM emaj.emaj_relation WHERE rel_log_schema = r_rel.rel_log_schema AND rel_log_table = r_rel.rel_log_table AND rel_time_range <> r_rel.rel_time_range) THEN EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r_rel.rel_log_schema, r_rel.rel_log_table); END IF; -- process log sequence information if the sequence is not referenced in other timerange (for potentially other groups) IF NOT EXISTS(SELECT 1 FROM emaj.emaj_relation WHERE rel_log_schema = r_rel.rel_log_schema AND rel_log_sequence = r_rel.rel_log_sequence AND rel_time_range <> r_rel.rel_time_range) THEN -- delete rows related to the log sequence from emaj_sequence table -- (it may delete rows for other already processed time_ranges for the same table) DELETE FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_log_schema AND sequ_name = r_rel.rel_log_sequence; -- delete rows related to the table from emaj_seq_hole table -- (it may delete holes for timeranges that do not belong to the group, if a table has been moved to another group, -- but is safe enough for rollbacks) DELETE FROM emaj.emaj_seq_hole WHERE sqhl_schema = r_rel.rel_schema AND sqhl_table = r_rel.rel_tblseq; END IF; -- keep a trace of the table group ownership history and finaly delete the table reference from the emaj_relation table WITH deleted AS ( DELETE FROM emaj.emaj_relation WHERE rel_schema = r_rel.rel_schema AND rel_tblseq = r_rel.rel_tblseq AND rel_time_range = r_rel.rel_time_range RETURNING rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind ) INSERT INTO emaj.emaj_rel_hist (relh_schema, relh_tblseq, relh_time_range, relh_group, relh_kind) SELECT rel_schema, rel_tblseq, CASE WHEN upper_inf(rel_time_range) THEN int8range(lower(rel_time_range), v_timeId, '[)') ELSE rel_time_range END, rel_group, rel_kind FROM deleted; RETURN; END; $_drop_tbl$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_sequence(v_schema TEXT, v_sequence TEXT, v_group TEXT, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_sequence$ -- The function assigns a sequence into a tables group. -- Inputs: schema name, sequence name, assignment group name, mark name to set when logging groups (optional) -- Outputs: number of sequences effectively assigned to the tables group, ie. 1 BEGIN RETURN emaj._assign_sequences(v_schema, ARRAY[v_sequence], v_group, v_mark , FALSE, FALSE); END; $emaj_assign_sequence$; COMMENT ON FUNCTION emaj.emaj_assign_sequence(TEXT,TEXT,TEXT,TEXT) IS $$Assign a sequence into a tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_sequences(v_schema TEXT, v_sequences TEXT[], v_group TEXT, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_sequences$ -- The function assigns several sequences at once into a tables group. -- Inputs: schema, array of sequence names, assignment group name, -- mark name to set when logging groups (optional) -- Outputs: number of sequences effectively assigned to the tables group BEGIN RETURN emaj._assign_sequences(v_schema, v_sequences, v_group, v_mark, TRUE, FALSE); END; $emaj_assign_sequences$; COMMENT ON FUNCTION emaj.emaj_assign_sequences(TEXT,TEXT[],TEXT,TEXT) IS $$Assign several sequences into a tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_assign_sequences(v_schema TEXT, v_sequencesIncludeFilter TEXT, v_sequencesExcludeFilter TEXT, v_group TEXT, v_mark TEXT DEFAULT 'ASSIGN_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_assign_sequences$ -- The function assigns sequences on name regexp pattern into a tables group. -- Inputs: schema name, 2 patterns to filter sequence names (one to include and another to exclude), assignment group name, -- mark name to set when logging groups (optional) -- Outputs: number of sequences effectively assigned to the tables group DECLARE v_sequences TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_sequencesIncludeFilter = '' THEN NULL ELSE v_sequencesIncludeFilter END, CASE WHEN v_sequencesExcludeFilter = '' THEN NULL ELSE v_sequencesExcludeFilter END INTO v_sequencesIncludeFilter, v_sequencesExcludeFilter; -- Build the list of sequences names satisfying the pattern SELECT array_agg(relname) INTO v_sequences FROM ( SELECT relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname ~ v_sequencesIncludeFilter AND (v_sequencesExcludeFilter IS NULL OR relname !~ v_sequencesExcludeFilter) AND relkind IN ('S') ORDER BY relname) AS t; -- OK, call the _assign_sequences() function for execution RETURN emaj._assign_sequences(v_schema, v_sequences, v_group, v_mark, TRUE, TRUE); END; $emaj_assign_sequences$; COMMENT ON FUNCTION emaj.emaj_assign_sequences(TEXT,TEXT,TEXT,TEXT,TEXT) IS $$Assign sequences on name patterns into a tables group.$$; CREATE OR REPLACE FUNCTION emaj._assign_sequences(v_schema TEXT, v_sequences TEXT[], v_group TEXT, v_mark TEXT, v_multiSequence BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql AS $_assign_sequences$ -- The function effectively assigns sequences into a tables group. -- Inputs: schema, array of sequence names, group name, -- mark to set for lonnging groups, a boolean indicating whether several sequences need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of sequences effectively assigned to the tables group -- The JSONB v_properties parameter has currenlty only one field '{"priority":...}' the properties being NULL by default DECLARE v_function TEXT; v_groupIsLogging BOOLEAN; v_list TEXT; v_array TEXT[]; v_timeId BIGINT; v_markName TEXT; v_oneSequence TEXT; v_nbAssignedSeq INT = 0; BEGIN v_function = CASE WHEN v_multiSequence THEN 'ASSIGN_SEQUENCES' ELSE 'ASSIGN_SEQUENCE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check supplied parameters -- check the group name and if ok, get some properties of the group PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_group], v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := ''); SELECT group_is_logging INTO v_groupIsLogging FROM emaj.emaj_group WHERE group_name = v_group; -- check the supplied schema exists and is not an E-Maj schema PERFORM 1 FROM pg_catalog.pg_namespace WHERE nspname = v_schema; IF NOT FOUND THEN RAISE EXCEPTION '_assign_sequences: The schema "%" does not exist.', v_schema; END IF; PERFORM 1 FROM emaj.emaj_schema WHERE sch_name = v_schema; IF FOUND THEN RAISE EXCEPTION '_assign_sequences: The schema "%" is an E-Maj schema.', v_schema; END IF; -- check sequences IF NOT v_arrayFromRegex THEN -- from the sequences array supplied by the user, remove duplicates values, NULL and empty strings from the supplied sequence names array SELECT array_agg(DISTINCT sequence_name) INTO v_sequences FROM unnest(v_sequences) AS sequence_name WHERE sequence_name IS NOT NULL AND sequence_name <> ''; -- check that application sequences exist WITH sequences AS ( SELECT unnest(v_sequences) AS sequence_name) SELECT string_agg(quote_ident(sequence_name), ', ') INTO v_list FROM ( SELECT sequence_name FROM sequences WHERE NOT EXISTS ( SELECT 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND nspname = v_schema AND relname = sequence_name AND relkind IN ('S')) ) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_assign_sequences: In schema %, some sequences (%) do not exist.', quote_ident(v_schema), v_list; END IF; END IF; -- check or discard sequences already assigned to a group SELECT string_agg(quote_ident(rel_tblseq), ', '), array_agg(rel_tblseq) INTO v_list, v_array FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range); IF v_list IS NOT NULL THEN IF NOT v_arrayFromRegex THEN RAISE EXCEPTION '_assign_sequences: In schema %, some sequences (%) already belong to a group.', quote_ident(v_schema), v_list; ELSE RAISE WARNING '_assign_sequences: Some sequences already belonging to a group (%) are not selected.', v_list; -- remove these sequences from the sequences to process SELECT array_agg(remaining_sequence) INTO v_sequences FROM (SELECT unnest(v_sequences) EXCEPT SELECT unnest(v_array)) AS t(remaining_sequence); END IF; END IF; -- check the supplied mark SELECT emaj._check_new_mark(array[v_group], v_mark) INTO v_markName; -- OK, IF v_sequences IS NULL OR v_sequences = '{}' THEN -- when no sequences are finaly selected, just warn RAISE WARNING '_assign_sequences: No sequence to process.'; ELSE -- 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_groupIsLogging 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(ARRAY[v_group], 'ROW EXCLUSIVE', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(ARRAY[v_group], v_markName, FALSE, TRUE, NULL, v_timeId); END IF; -- effectively create the log components for each table FOREACH v_oneSequence IN ARRAY v_sequences LOOP PERFORM emaj._add_seq(v_schema, v_oneSequence, v_group, v_groupIsLogging, v_timeId, v_function); -- insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'ADD_SEQ', v_schema, v_oneSequence, v_group, v_groupIsLogging); v_nbAssignedSeq = v_nbAssignedSeq + 1; END LOOP; -- adjust the group characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, 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 = v_group; END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbAssignedSeq || ' sequences assigned to the group ' || v_group); RETURN v_nbAssignedSeq; END; $_assign_sequences$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_sequence(v_schema TEXT, v_sequence TEXT, v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_sequence$ -- The function removes a sequence from its tables group. -- Inputs: schema name, sequence name, mark name to set when logging groups (optional) -- Outputs: number of sequences effectively removed to the tables group, id. 1 BEGIN RETURN emaj._remove_sequences(v_schema, ARRAY[v_sequence], v_mark, FALSE, FALSE); END; $emaj_remove_sequence$; COMMENT ON FUNCTION emaj.emaj_remove_sequence(TEXT,TEXT,TEXT) IS $$Remove a sequence from its tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_sequences(v_schema TEXT, v_sequences TEXT[], v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_sequences$ -- The function removes several sequences at once from their tables group. -- Inputs: schema, array of sequence names, mark name to set when logging groups (optional) -- Outputs: number of sequences effectively removed from the tables group BEGIN RETURN emaj._remove_sequences(v_schema, v_sequences, v_mark, TRUE, FALSE); END; $emaj_remove_sequences$; COMMENT ON FUNCTION emaj.emaj_remove_sequences(TEXT,TEXT[],TEXT) IS $$Remove several sequences from their tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_remove_sequences(v_schema TEXT, v_sequencesIncludeFilter TEXT, v_sequencesExcludeFilter TEXT, v_mark TEXT DEFAULT 'REMOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_remove_sequences$ -- The function removes sequences on name patterns from their tables group. -- Inputs: schema, 2 patterns to filter sequence names (one to include and another to exclude), -- mark name to set when logging groups (optional) -- Outputs: number of sequences effectively removed from the tables group DECLARE v_sequences TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_sequencesIncludeFilter = '' THEN NULL ELSE v_sequencesIncludeFilter END, CASE WHEN v_sequencesExcludeFilter = '' THEN NULL ELSE v_sequencesExcludeFilter END INTO v_sequencesIncludeFilter, v_sequencesExcludeFilter; -- Build the list of sequences names satisfying the pattern SELECT array_agg(rel_tblseq) INTO v_sequences FROM ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq ~ v_sequencesIncludeFilter AND (v_sequencesExcludeFilter IS NULL OR rel_tblseq !~ v_sequencesExcludeFilter) AND rel_kind = 'S' AND upper_inf(rel_time_range) ORDER BY rel_tblseq) AS t; -- call the _remove_sequences() function for execution RETURN emaj._remove_sequences(v_schema, v_sequences, v_mark, TRUE, TRUE); END; $emaj_remove_sequences$; COMMENT ON FUNCTION emaj.emaj_remove_sequences(TEXT,TEXT,TEXT,TEXT) IS $$Remove several sequences on name patterns from their tables group.$$; CREATE OR REPLACE FUNCTION emaj._remove_sequences(v_schema TEXT, v_sequences TEXT[], v_mark TEXT, v_multiSequence BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql AS $_remove_sequences$ -- The function effectively removes sequences from their sequences group. -- Inputs: schema, array of sequence names, mark to set if for logging groups, -- a boolean to indicate whether several sequences need to be processed, -- a boolean indicating whether the tables array has been built from regex filters -- Outputs: number of sequences effectively assigned to the sequences group DECLARE v_function TEXT; v_list TEXT; v_markName TEXT; v_timeId BIGINT; v_groups TEXT[]; v_loggingGroups TEXT[]; v_groupName TEXT; v_groupIsLogging BOOLEAN; v_eventTriggers TEXT[]; v_oneSequence TEXT; v_nbRemovedSeq INT = 0; BEGIN v_function = CASE WHEN v_multiSequence THEN 'REMOVE_SEQUENCES' ELSE 'REMOVE_SEQUENCE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check sequences array IF NOT v_arrayFromRegex THEN -- remove duplicates values, NULL and empty strings from the supplied sequence names array SELECT array_agg(DISTINCT sequence_name) INTO v_sequences FROM unnest(v_sequences) AS sequence_name WHERE sequence_name IS NOT NULL AND sequence_name <> ''; -- check that the sequences currently belong to a tables group (not necessarily the same one) WITH all_supplied_sequences AS ( SELECT unnest(v_sequences) AS sequence_name), sequences_in_group AS ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range)) SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(sequence_name), ', ') INTO v_list FROM ( SELECT sequence_name FROM all_supplied_sequences EXCEPT SELECT rel_tblseq FROM sequences_in_group) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_remove_sequences: some sequences (%) do not currently belong to any tables group.', v_list; END IF; END IF; -- get the lists of groups and logging groups holding these sequences, if any -- It locks the tables groups so that no other operation simultaneously occurs these groups WITH tables_group AS ( SELECT group_name, group_is_logging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range) FOR UPDATE OF emaj_group ) SELECT (SELECT array_agg(group_name) FROM tables_group), (SELECT array_agg(group_name) FROM tables_group WHERE group_is_logging) INTO v_groups, v_loggingGroups; -- check the supplied mark SELECT emaj._check_new_mark(v_loggingGroups, v_mark) INTO v_markName; -- OK, IF v_sequences IS NULL THEN -- when no sequences are finaly selected, just warn RAISE WARNING '_remove_sequences: No sequence to process.'; ELSE -- 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', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, FALSE, 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; -- effectively drop the log components for each sequence FOREACH v_oneSequence IN ARRAY v_sequences LOOP -- get some characteristics of the group that holds the sequence SELECT rel_group, group_is_logging INTO v_groupName, v_groupIsLogging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = v_oneSequence AND upper_inf(rel_time_range); -- drop this sequence PERFORM emaj._remove_seq(v_schema, v_oneSequence, v_groupName, v_groupIsLogging, v_timeId, v_function); -- record the change into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging) VALUES (v_timeId, 'REMOVE_SEQ', v_schema, v_oneSequence, v_groupName, v_groupIsLogging); v_nbRemovedSeq = v_nbRemovedSeq + 1; END LOOP; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); -- adjust the groups characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, 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_groups); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbRemovedSeq || ' sequences removed from their groups'); RETURN v_nbRemovedSeq; END; $_remove_sequences$; CREATE OR REPLACE FUNCTION emaj.emaj_move_sequence(v_schema TEXT, v_sequence TEXT, v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_sequence$ -- The function moves a sequence from its tables group to another tables group. -- Inputs: schema name, sequence name, new group name, mark name to set when logging groups (optional) -- Outputs: number of sequences effectively moved to the new tables group, ie. 1 BEGIN RETURN emaj._move_sequences(v_schema, ARRAY[v_sequence], v_newGroup, v_mark, FALSE, FALSE); END; $emaj_move_sequence$; COMMENT ON FUNCTION emaj.emaj_move_sequence(TEXT,TEXT,TEXT,TEXT) IS $$Move a sequence from its tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_move_sequences(v_schema TEXT, v_sequences TEXT[], v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_sequences$ -- The function moves several sequences at once from their tables group to another tables group. -- Inputs: schema, array of sequence names, new group name, mark name to set when logging groups (optional) -- Outputs: number of sequences effectively moved to the new tables group BEGIN RETURN emaj._move_sequences(v_schema, v_sequences, v_newGroup, v_mark, TRUE, FALSE); END; $emaj_move_sequences$; COMMENT ON FUNCTION emaj.emaj_move_sequences(TEXT,TEXT[],TEXT,TEXT) IS $$Move several sequences from their tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_move_sequences(v_schema TEXT, v_sequencesIncludeFilter TEXT, v_sequencesExcludeFilter TEXT, v_newGroup TEXT, v_mark TEXT DEFAULT 'MOVE_%') RETURNS INTEGER LANGUAGE plpgsql AS $emaj_move_sequences$ -- The function moves sequences on name patterns from their tables group to another tables group. -- Inputs: schema, 2 patterns to filter sequence names (one to include and another to exclude), new group name, -- mark name to set when logging groups (optional) -- Outputs: number of sequences effectively moved to the new tables group DECLARE v_sequences TEXT[]; BEGIN -- process empty filters as NULL SELECT CASE WHEN v_sequencesIncludeFilter = '' THEN NULL ELSE v_sequencesIncludeFilter END, CASE WHEN v_sequencesExcludeFilter = '' THEN NULL ELSE v_sequencesExcludeFilter END INTO v_sequencesIncludeFilter, v_sequencesExcludeFilter; -- Build the list of sequences names satisfying the pattern SELECT array_agg(rel_tblseq) INTO v_sequences FROM ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq ~ v_sequencesIncludeFilter AND (v_sequencesExcludeFilter IS NULL OR rel_tblseq !~ v_sequencesExcludeFilter) AND rel_kind = 'S' AND upper_inf(rel_time_range) AND rel_group <> v_newGroup ORDER BY rel_tblseq) AS t; -- call the _move_sequences() function for execution RETURN emaj._move_sequences(v_schema, v_sequences, v_newGroup, v_mark, TRUE, TRUE); END; $emaj_move_sequences$; COMMENT ON FUNCTION emaj.emaj_move_sequences(TEXT,TEXT,TEXT,TEXT,TEXT) IS $$Move several sequences on name patterns from their tables group to another tables group.$$; CREATE OR REPLACE FUNCTION emaj._move_sequences(v_schema TEXT, v_sequences TEXT[], v_newGroup TEXT, v_mark TEXT, v_multiSequence BOOLEAN, v_arrayFromRegex BOOLEAN) RETURNS INTEGER LANGUAGE plpgsql AS $_move_sequences$ -- The function effectively moves sequences from their tables group to another tables group. -- Inputs: schema, array of sequence names, new group name, mark to set if for logging groups, -- boolean to indicate whether several sequences need to be processed, -- a boolean indicating whether the sequences array has been built from regex filters -- Outputs: number of sequences effectively moved to the tables group DECLARE v_function TEXT; v_newGroupIsLogging BOOLEAN; v_list TEXT; v_uselessSequences TEXT[]; v_markName TEXT; v_timeId BIGINT; v_groups TEXT[]; v_loggingGroups TEXT[]; v_groupName TEXT; v_groupIsLogging BOOLEAN; v_oneSequence TEXT; v_nbMovedSeq INT = 0; BEGIN v_function = CASE WHEN v_multiSequence THEN 'MOVE_SEQUENCES' ELSE 'MOVE_SEQUENCE' END; -- insert the begin entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event) VALUES (v_function, 'BEGIN'); -- check the group name and if ok, get some properties of the group PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_newGroup], v_mayBeNull := FALSE, v_lockGroups := TRUE, v_checkList := ''); SELECT group_is_logging INTO v_newGroupIsLogging FROM emaj.emaj_group WHERE group_name = v_NewGroup; -- check the sequences list IF NOT v_arrayFromRegex THEN -- remove duplicates values, NULL and empty strings from the supplied sequence names array SELECT array_agg(DISTINCT sequence_name) INTO v_sequences FROM unnest(v_sequences) AS sequence_name WHERE sequence_name IS NOT NULL AND sequence_name <> ''; -- check that the sequences currently belong to a tables group (not necessarily the same for all sequences) WITH all_supplied_sequences AS ( SELECT unnest(v_sequences) AS sequence_name), sequences_in_group AS ( SELECT rel_tblseq FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range)) SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(sequence_name), ', ') INTO v_list FROM ( SELECT sequence_name FROM all_supplied_sequences EXCEPT SELECT rel_tblseq FROM sequences_in_group) AS t; IF v_list IS NOT NULL THEN RAISE EXCEPTION '_move_sequences: some sequences (%) do not currently belong to any tables group.', v_list; END IF; -- remove sequences that already belong to the new group SELECT string_agg(quote_ident(v_schema) || '.' || quote_ident(rel_tblseq), ', '), array_agg(rel_tblseq) INTO v_list, v_uselessSequences FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range) AND rel_group = v_newGroup; IF v_list IS NOT NULL THEN RAISE WARNING '_move_sequences: some sequences (%) already belong to the tables group %.', v_list, v_newGroup; SELECT array_remove(v_sequences, useless_sequence) INTO v_sequences FROM unnest(v_uselessSequences) AS useless_sequence; END IF; END IF; -- get the lists of groups and logging groups holding these sequences, if any. -- It locks the tables groups so that no other operation simultaneously occurs these groups -- (the CTE is needed for the FOR UPDATE clause not allowed when aggregate functions) WITH tables_group AS ( SELECT group_name, group_is_logging FROM emaj.emaj_group WHERE group_name = v_newGroup OR group_name IN (SELECT DISTINCT rel_group FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = ANY(v_sequences) AND upper_inf(rel_time_range)) FOR UPDATE OF emaj_group ) SELECT array_agg(group_name ORDER BY group_name), array_agg(group_name ORDER BY group_name) FILTER (WHERE group_is_logging) INTO v_groups, v_loggingGroups FROM tables_group; -- check the supplied mark SELECT emaj._check_new_mark(v_loggingGroups, v_mark) INTO v_markName; -- OK, IF v_sequences IS NULL THEN -- when no sequences are finaly selected, just warn RAISE WARNING '_move_sequences: No sequence to process.'; ELSE -- 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', FALSE); -- and set the mark, using the same time identifier PERFORM emaj._set_mark_groups(v_loggingGroups, v_markName, TRUE, TRUE, NULL, v_timeId); END IF; -- effectively move each sequence FOREACH v_oneSequence IN ARRAY v_sequences LOOP -- get some characteristics of the group that holds the sequence before the move SELECT rel_group, group_is_logging INTO v_groupName, v_groupIsLogging FROM emaj.emaj_relation, emaj.emaj_group WHERE rel_group = group_name AND rel_schema = v_schema AND rel_tblseq = v_oneSequence AND upper_inf(rel_time_range); -- move this sequence PERFORM emaj._move_seq(v_schema, v_oneSequence, v_groupName, v_groupIsLogging, v_newGroup, v_newGroupIsLogging, v_timeId, v_function); -- insert an entry into the emaj_alter_plan table (so that future rollback may see the change) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_group_is_logging, altr_new_group, altr_new_group_is_logging) VALUES (v_timeId, 'MOVE_SEQ', v_schema, v_oneSequence, v_groupName, v_groupIsLogging, v_newGroup, v_newGroupIsLogging); v_nbMovedSeq = v_nbMovedSeq + 1; END LOOP; -- adjust the groups characteristics UPDATE emaj.emaj_group SET group_last_alter_time_id = v_timeId, 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_groups); END IF; -- insert the end entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_wording) VALUES (v_function, 'END', v_nbMovedSeq || ' sequences moved to the new tables group ' || v_newGroup); RETURN v_nbMovedSeq; END; $_move_sequences$; CREATE OR REPLACE FUNCTION emaj._create_seq(v_schema TEXT, v_seq TEXT, v_groupName TEXT, 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 application sequence to process, the group to add it into, the priority attribute, 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 = v_seq AND ns.nspname = v_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.', v_schema, v_seq, v_tableSchema, v_tableName; ELSE IF v_tableGroup <> v_groupName THEN RAISE WARNING '_create_seq: The sequence %.% is linked to table %.% but this table belong to another tables group (%).', v_schema, v_seq, 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_kind) VALUES (v_schema, v_seq, int8range(v_timeId, NULL, '[)'), v_groupName, 'S'); RETURN; END; $_create_seq$; CREATE OR REPLACE FUNCTION emaj._add_seq(v_schema TEXT, v_sequence TEXT, v_group TEXT, v_groupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql AS $_add_seq$ -- The function adds a sequence to a group. It is called during an alter group or a dynamic assignment operation. -- If the group is in idle state, it simply calls the _create_seq() function. -- Otherwise, it calls the _create_seql() function, and records the current state of the sequence. -- Required inputs: schema and sequence to add, group name, priority, the group's logging state, -- the time stamp id of the operation, main calling function. BEGIN -- create the sequence PERFORM emaj._create_seq(v_schema, v_sequence, v_group, v_timeId); -- if the group is in logging state, perform additional tasks IF v_groupIsLogging THEN -- ... record the new sequence state in the emaj_sequence table for the current alter_group mark 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 * FROM emaj._get_current_sequence_state(v_schema, v_sequence, v_timeId); END IF; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'SEQUENCE ADDED', quote_ident(v_schema) || '.' || quote_ident(v_sequence), 'To the ' || CASE WHEN v_groupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_group); RETURN; END; $_add_seq$; CREATE OR REPLACE FUNCTION emaj._remove_seq(v_schema TEXT, v_sequence TEXT, v_group TEXT, v_groupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql AS $_remove_seq$ -- The function removes a sequence from a group. It is called during an alter group or a dynamic removal operation. -- Required inputs: schema and sequence to remove, related group name and logging state, -- time stamp id of the operation, main calling function. BEGIN IF NOT v_groupIsLogging THEN -- if the group is in idle state, drop the sequence immediately PERFORM emaj._drop_seq(emaj.emaj_relation.*, v_timeId) FROM emaj.emaj_relation WHERE rel_schema = v_schema AND rel_tblseq = v_sequence AND upper_inf(rel_time_range); ELSE -- if the group is in logging state, just register the end of the relation time frame UPDATE emaj.emaj_relation SET rel_time_range = int8range(lower(rel_time_range),v_timeId, '[)') WHERE rel_schema = v_schema AND rel_tblseq = v_sequence AND upper_inf(rel_time_range); END IF; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'SEQUENCE REMOVED', quote_ident(v_schema) || '.' || quote_ident(v_sequence), 'From the ' || CASE WHEN v_groupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_group); RETURN; END; $_remove_seq$; CREATE OR REPLACE FUNCTION emaj._move_seq(v_schema TEXT, v_sequence TEXT, v_oldGroup TEXT, v_oldGroupIsLogging BOOLEAN, v_newGroup TEXT, v_newGroupIsLogging BOOLEAN, v_timeId BIGINT, v_function TEXT) RETURNS VOID LANGUAGE plpgsql AS $_move_seq$ -- The function change the group ownership of a sequence. It is called during an alter group or a dynamic assignment operation. -- Required inputs: schema and sequence to move, old and new group names and their logging state, -- time stamp id of the operation, main calling function. BEGIN -- 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 = v_schema AND rel_tblseq = v_sequence 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, '[)'), v_newGroup, 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 = v_schema AND rel_tblseq = v_sequence AND upper(rel_time_range) = v_timeId; -- insert an entry into the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES (v_function, 'SEQUENCE MOVED', quote_ident(v_schema) || '.' || quote_ident(v_sequence), 'From the ' || CASE WHEN v_oldGroupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_oldGroup || ' to the ' || CASE WHEN v_newGroupIsLogging THEN 'logging ' ELSE 'idle ' END || 'group ' || v_newGroup); RETURN; END; $_move_seq$; CREATE OR REPLACE FUNCTION emaj._drop_seq(r_rel emaj.emaj_relation, v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql AS $_drop_seq$ -- The function deletes the rows stored into emaj_sequence for a particular sequence timerange. -- Required inputs: row from emaj_relation corresponding to the appplication sequence to proccess. BEGIN -- delete rows from emaj_sequence, but only when dealing with the last timerange of the sequence IF NOT EXISTS(SELECT 1 FROM emaj.emaj_relation WHERE rel_schema = r_rel.rel_schema AND rel_tblseq = r_rel.rel_tblseq AND rel_time_range <> r_rel.rel_time_range) THEN DELETE FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq; END IF; -- keep a trace of the sequence group ownership history and finaly delete the sequence timerange from the emaj_relation table WITH deleted AS ( DELETE FROM emaj.emaj_relation WHERE rel_schema = r_rel.rel_schema AND rel_tblseq = r_rel.rel_tblseq AND rel_time_range = r_rel.rel_time_range RETURNING rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind ) INSERT INTO emaj.emaj_rel_hist (relh_schema, relh_tblseq, relh_time_range, relh_group, relh_kind) SELECT rel_schema, rel_tblseq, CASE WHEN upper_inf(rel_time_range) THEN int8range(lower(rel_time_range), v_timeId, '[)') ELSE rel_time_range END, rel_group, rel_kind FROM deleted; RETURN; END; $_drop_seq$; CREATE OR REPLACE FUNCTION emaj._rlbk_tbl(r_rel emaj.emaj_relation, v_minGlobalSeq BIGINT, v_maxGlobalSeq BIGINT, v_nbSession INT, v_isLoggedRlbk BOOLEAN) RETURNS BIGINT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_rlbk_tbl$ -- This function rollbacks one table to a given point in time represented by the value of the global sequence. -- The function is called by emaj._rlbk_session_exec(). -- Input: row from emaj_relation corresponding to the appplication table to proccess -- global sequence (non inclusive) lower and (inclusive) upper limits covering the rollback time frame -- number of sessions and a boolean indicating whether the rollback is logged -- Output: number of rolled back primary keys -- For unlogged rollback, the log triggers have been disabled previously and will be enabled later. -- 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_fullTableName TEXT; v_logTableName TEXT; v_tmpTable TEXT; v_tableType TEXT; v_insertClause TEXT = ''; v_nbPk BIGINT; BEGIN 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); -- insert begin event in history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('ROLLBACK_TABLE', 'BEGIN', v_fullTableName, 'All log rows with emaj_gid > ' || v_minGlobalSeq || ' and <= ' || v_maxGlobalSeq); -- create the temporary table containing all primary key values with their earliest emaj_gid IF v_nbSession = 1 THEN v_tableType = 'TEMP'; v_tmpTable = 'emaj_tmp_' || pg_backend_pid(); ELSE -- with multi session parallel rollbacks, the table cannot be a TEMP table because it would not be usable in 2PC -- but it may be an UNLOGGED table v_tableType = 'UNLOGGED'; v_tmpTable = 'emaj.emaj_tmp_' || pg_backend_pid(); END IF; EXECUTE format('CREATE %s TABLE %s AS ' ' SELECT %s, min(emaj_gid) as emaj_gid FROM %s' ' WHERE emaj_gid > %s AND emaj_gid <= %s' ' GROUP BY %s', v_tableType, v_tmpTable, r_rel.rel_sql_pk_columns, v_logTableName, v_minGlobalSeq, v_maxGlobalSeq, r_rel.rel_sql_pk_columns); GET DIAGNOSTICS v_nbPk = ROW_COUNT; -- delete all rows from the application table corresponding to each touched primary key -- this deletes rows inserted or updated during the rolled back period EXECUTE format('DELETE FROM ONLY %s tbl USING %s keys WHERE %s', v_fullTableName, v_tmpTable, r_rel.rel_sql_pk_eq_conditions); -- for logged rollbacks, if the number of pkey to process is greater than 1.000, ANALYZE the log table to take into account -- the impact of just inserted rows, avoiding a potentialy bad plan for the next INSERT statement IF v_isLoggedRlbk AND v_nbPk > 1000 THEN EXECUTE format('ANALYZE %s', v_logTableName); END IF; -- insert into the application table rows that were deleted or updated during the rolled back period IF emaj._pg_version_num() >= 100000 THEN v_insertClause = ' OVERRIDING SYSTEM VALUE'; END IF; EXECUTE format('INSERT INTO %s %s' ' SELECT %s FROM %s tbl, %s keys ' ' WHERE %s AND tbl.emaj_gid = keys.emaj_gid AND tbl.emaj_tuple = ''OLD''' ' AND tbl.emaj_gid > %s AND tbl.emaj_gid <= %s', v_fullTableName, v_insertClause, r_rel.rel_sql_columns, v_logTableName, v_tmpTable, r_rel.rel_sql_pk_eq_conditions, v_minGlobalSeq, v_maxGlobalSeq); -- drop the now useless temporary table EXECUTE format('DROP TABLE %s', v_tmpTable); -- insert end event in history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('ROLLBACK_TABLE', 'END', v_fullTableName, v_nbPk || ' rolled back primary keys'); RETURN v_nbPk; END; $_rlbk_tbl$; CREATE OR REPLACE FUNCTION emaj._delete_log_tbl(r_rel emaj.emaj_relation, v_beginTimeId BIGINT, v_endTimeId BIGINT, v_lastGlobalSeq BIGINT) RETURNS BIGINT LANGUAGE plpgsql AS $_delete_log_tbl$ -- This function deletes the part of a log table corresponding to updates that have been rolled back. -- The function is only called by emaj._rlbk_session_exec(), for unlogged rollbacks. -- It deletes sequences records corresponding to marks that are not visible anymore after the rollback. -- It also registers the hole in sequence numbers generated by the deleted log rows. -- Input: row from emaj_relation corresponding to the appplication table to proccess, -- begin and end time stamp ids to define the time range identifying the hole to create in the log sequence -- global sequence value limit for rollback, mark timestamp, -- Output: deleted rows DECLARE v_nbRows BIGINT; BEGIN -- delete obsolete log rows EXECUTE format('DELETE FROM %I.%I WHERE emaj_gid > %s', r_rel.rel_log_schema, r_rel.rel_log_table, v_lastGlobalSeq); GET DIAGNOSTICS v_nbRows = ROW_COUNT; -- record the sequence holes generated by the delete operation -- this is due to the fact that log sequences are not rolled back, this information will be used by the emaj_log_stat_group() function -- (and indirectly by emaj_estimate_rollback_group() and emaj_estimate_rollback_groups()) -- first delete, if exist, sequence holes that have disappeared with the rollback DELETE FROM emaj.emaj_seq_hole WHERE sqhl_schema = r_rel.rel_schema AND sqhl_table = r_rel.rel_tblseq AND sqhl_begin_time_id >= v_beginTimeId AND sqhl_begin_time_id < v_endTimeId; -- and then insert the new sequence hole IF emaj._pg_version_num() >= 100000 THEN EXECUTE format('INSERT INTO emaj.emaj_seq_hole (sqhl_schema, sqhl_table, sqhl_begin_time_id, sqhl_end_time_id, sqhl_hole_size)' ' VALUES (%L, %L, %s, %s, (' ' SELECT CASE WHEN rel.is_called THEN rel.last_value + increment_by ELSE rel.last_value END' ' FROM %I.%I rel, pg_sequences' ' WHERE schemaname = %L AND sequencename = %L' ' )-(' ' SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END' ' FROM emaj.emaj_sequence' ' WHERE sequ_schema = %L AND sequ_name = %L AND sequ_time_id = %s))', r_rel.rel_schema, r_rel.rel_tblseq, v_beginTimeId, v_endTimeId, r_rel.rel_log_schema, r_rel.rel_log_sequence, r_rel.rel_log_schema, r_rel.rel_log_sequence, r_rel.rel_log_schema, r_rel.rel_log_sequence, v_beginTimeId); ELSE EXECUTE format('INSERT INTO emaj.emaj_seq_hole (sqhl_schema, sqhl_table, sqhl_begin_time_id, sqhl_end_time_id, sqhl_hole_size)' ' VALUES (%L, %L, %s, %s, (' ' SELECT CASE WHEN is_called THEN last_value + increment_by ELSE last_value END FROM %I.%I' ' )-(' ' SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END' ' FROM emaj.emaj_sequence' ' WHERE sequ_schema = %L AND sequ_name = %L AND sequ_time_id = %s))', r_rel.rel_schema, r_rel.rel_tblseq, v_beginTimeId, v_endTimeId, r_rel.rel_log_schema, r_rel.rel_log_sequence, r_rel.rel_log_schema, r_rel.rel_log_sequence, v_beginTimeId); END IF; RETURN v_nbRows; END; $_delete_log_tbl$; CREATE OR REPLACE FUNCTION emaj._rlbk_seq(r_rel emaj.emaj_relation, v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_rlbk_seq$ -- This function rollbacks one application sequence to a given mark. -- The function is called by emaj.emaj._rlbk_end(). -- Input: the emaj_group_def row related to the application sequence to process, time id of the mark to rollback to. -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if it is not the owner of the application sequence. DECLARE v_fullSeqName TEXT; v_stmt TEXT; mark_seq_rec RECORD; curr_seq_rec RECORD; BEGIN -- Read sequence's characteristics at mark time BEGIN SELECT sequ_schema, sequ_name, sequ_last_val, sequ_start_val, sequ_increment, sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called INTO STRICT mark_seq_rec FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq AND sequ_time_id = v_timeId; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION '_rlbk_seq: No mark at time id "%" can be found for the sequence "%.%".', v_timeId, r_rel.rel_schema, r_rel.rel_tblseq; END; -- Read the current sequence's characteristics v_fullSeqName = quote_ident(r_rel.rel_schema) || '.' || quote_ident(r_rel.rel_tblseq); IF emaj._pg_version_num() >= 100000 THEN EXECUTE format('SELECT rel.last_value, start_value, increment_by, max_value, min_value, cache_size as cache_value, ' ' cycle as is_cycled, rel.is_called' ' FROM %s rel, pg_catalog.pg_sequences ' ' WHERE schemaname = %L AND sequencename = %L', v_fullSeqName, r_rel.rel_schema, r_rel.rel_tblseq) INTO STRICT curr_seq_rec; ELSE EXECUTE format('SELECT last_value, start_value, increment_by, max_value, min_value, cache_value, is_cycled, is_called FROM %s', v_fullSeqName) INTO STRICT curr_seq_rec; END IF; -- Build the ALTER SEQUENCE statement, depending on the differences between the present values and the related -- values at the requested mark time SELECT emaj._build_alter_seq(curr_seq_rec.last_value, curr_seq_rec.is_called, curr_seq_rec.increment_by, curr_seq_rec.start_value, curr_seq_rec.min_value, curr_seq_rec.max_value, curr_seq_rec.cache_value, curr_seq_rec.is_cycled, mark_seq_rec.sequ_last_val, mark_seq_rec.sequ_is_called, mark_seq_rec.sequ_increment, mark_seq_rec.sequ_start_val, mark_seq_rec.sequ_min_val, mark_seq_rec.sequ_max_val, mark_seq_rec.sequ_cache_val, mark_seq_rec.sequ_is_cycled) INTO v_stmt; -- and execute the statement if at least one parameter has changed IF v_stmt <> '' THEN EXECUTE format('ALTER SEQUENCE %s %s', v_fullSeqName, v_stmt); END IF; -- insert event in history INSERT INTO emaj.emaj_hist (hist_function, hist_object, hist_wording) VALUES ('ROLLBACK_SEQUENCE', v_fullSeqName, substr(v_stmt,2)); RETURN; END; $_rlbk_seq$; CREATE OR REPLACE FUNCTION emaj._build_alter_seq(v_refLastValue BIGINT, v_refIsCalled BOOLEAN, v_refIncrementBy BIGINT, v_refStartValue BIGINT, v_refMinValue BIGINT, v_refMaxValue BIGINT, v_refCacheValue BIGINT, v_refIsCycled BOOLEAN, v_trgLastValue BIGINT, v_trgIsCalled BOOLEAN, v_trgIncrementBy BIGINT, v_trgStartValue BIGINT, v_trgMinValue BIGINT, v_trgMaxValue BIGINT, v_trgCacheValue BIGINT, v_trgIsCycled BOOLEAN) RETURNS TEXT LANGUAGE plpgsql AS $_build_alter_seq$ -- This function builds an ALTER SEQUENCE clause including only the sequence characteristics that have changed between a reference -- and a target. -- The function is called by _rlbk_seq() and _gen_sql_groups() -- Input: elementary reference and target sequence characteristics -- Output: the alter sequence clause with all modified characteristics DECLARE v_stmt TEXT; BEGIN v_stmt=''; -- Build the ALTER SEQUENCE clause, depending on the differences between the reference and target values IF v_refLastValue <> v_trgLastValue OR v_refIsCalled <> v_trgIsCalled THEN IF v_trgIsCalled THEN v_stmt = v_stmt || ' RESTART ' || v_trgLastValue + v_trgIncrementBy; ELSE v_stmt = v_stmt || ' RESTART ' || v_trgLastValue; END IF; END IF; IF v_refStartValue <> v_trgStartValue THEN v_stmt = v_stmt || ' START ' || v_trgStartValue; END IF; IF v_refIncrementBy <> v_trgIncrementBy THEN v_stmt = v_stmt || ' INCREMENT ' || v_trgIncrementBy; END IF; IF v_refMinValue <> v_trgMinValue THEN v_stmt = v_stmt || ' MINVALUE ' || v_trgMinValue; END IF; IF v_refMaxValue <> v_trgMaxValue THEN v_stmt = v_stmt || ' MAXVALUE ' || v_trgMaxValue; END IF; IF v_refCacheValue <> v_trgCacheValue THEN v_stmt = v_stmt || ' CACHE ' || v_trgCacheValue; END IF; IF v_refIsCycled <> v_trgIsCycled THEN IF v_trgIsCycled = 'f' THEN v_stmt = v_stmt || ' NO '; END IF; v_stmt=v_stmt || ' CYCLE '; END IF; RETURN v_stmt; END; $_build_alter_seq$; CREATE OR REPLACE FUNCTION emaj._log_stat_tbl(r_rel emaj.emaj_relation, v_beginTimeId BIGINT, v_endTimeId BIGINT) RETURNS BIGINT LANGUAGE plpgsql AS $_log_stat_tbl$ -- This function returns the number of log rows for a single table between 2 time stamps or between a time stamp and the current situation. -- It is called by the emaj_log_stat_group(), _rlbk_planning(), _rlbk_start_mark() and _gen_sql_groups() functions. -- These statistics are computed using the serial id of log tables and holes is sequences recorded into emaj_seq_hole at rollback time or -- rollback consolidation time. -- Input: row from emaj_relation corresponding to the appplication table to proccess, the time stamp ids defining the time range to examine -- (a end time stamp id set to NULL indicates the current situation) -- Output: number of log rows between both marks for the table DECLARE v_beginLastValue BIGINT; v_endLastValue BIGINT; v_sumHole BIGINT; BEGIN -- get the log table id at begin time id SELECT CASE WHEN sequ_is_called THEN sequ_last_val ELSE sequ_last_val - sequ_increment END INTO STRICT v_beginLastValue FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_log_schema AND sequ_name = r_rel.rel_log_sequence AND sequ_time_id = v_beginTimeId; IF v_endTimeId IS NULL THEN -- last time id is NULL, so examine the current state of the log table id IF emaj._pg_version_num() >= 100000 THEN EXECUTE format('SELECT CASE WHEN rel.is_called THEN rel.last_value ELSE rel.last_value - increment_by END' ' FROM %I.%I rel, pg_sequences' ' WHERE schemaname = %L AND sequencename = %L ', r_rel.rel_log_schema, r_rel.rel_log_sequence, r_rel.rel_log_schema, r_rel.rel_log_sequence) INTO v_endLastValue; ELSE EXECUTE format('SELECT CASE WHEN is_called THEN last_value ELSE last_value - increment_by END FROM %I.%I', r_rel.rel_log_schema, r_rel.rel_log_sequence) INTO v_endLastValue; END IF; -- and count the sum of hole from the start time to now SELECT coalesce(sum(sqhl_hole_size),0) INTO v_sumHole FROM emaj.emaj_seq_hole WHERE sqhl_schema = r_rel.rel_schema AND sqhl_table = r_rel.rel_tblseq AND sqhl_begin_time_id >= v_beginTimeId; ELSE -- last time id is not NULL, so get the log table id at end time id SELECT CASE WHEN sequ_is_called THEN sequ_last_val ELSE sequ_last_val - sequ_increment END INTO v_endLastValue FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_log_schema AND sequ_name = r_rel.rel_log_sequence AND sequ_time_id = v_endTimeId; -- and count the sum of hole from the start time to the end time SELECT coalesce(sum(sqhl_hole_size),0) INTO v_sumHole FROM emaj.emaj_seq_hole WHERE sqhl_schema = r_rel.rel_schema AND sqhl_table = r_rel.rel_tblseq AND sqhl_begin_time_id >= v_beginTimeId AND sqhl_end_time_id <= v_endTimeId; END IF; -- return the stat row for the table RETURN (v_endLastValue - v_beginLastValue - v_sumHole); END; $_log_stat_tbl$; 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 commands 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) || ' || '' 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) || ') || '' 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 format('INSERT INTO emaj_temp_script ' 'SELECT o.emaj_gid, 0, o.emaj_txid, CASE ' ' WHEN o.emaj_verb = ''INS'' THEN %s' ' WHEN o.emaj_verb = ''UPD'' AND o.emaj_tuple = ''OLD'' THEN %s' ' WHEN o.emaj_verb = ''DEL'' THEN %s' ' WHEN o.emaj_verb = ''TRU'' THEN %s' ' END ' ' FROM %s o' ' LEFT OUTER JOIN %s 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 %s', v_rqInsert, v_rqUpdate, v_rqDelete, v_rqTruncate, v_logTableName, v_logTableName, v_conditions); GET DIAGNOSTICS v_nbSQL = ROW_COUNT; RETURN v_nbSQL; END; $_gen_sql_tbl$; CREATE OR REPLACE FUNCTION emaj._gen_sql_seq(r_rel emaj.emaj_relation, v_firstMarkTimeId BIGINT, v_lastMarkTimeId BIGINT, v_nbSeq BIGINT) RETURNS BIGINT LANGUAGE plpgsql AS $_gen_sql_seq$ -- This function generates a SQL command to set the final characteristics of a sequence. -- The command is stored into a temporary table created by the _gen_sql_groups() calling function. -- Input: row from emaj_relation corresponding to the appplication sequence to proccess, -- the time id at requested start and end marks, -- the number of already processed sequences -- Output: number of generated SQL statements (0 or 1) DECLARE v_fullSeqName TEXT; v_refLastValue BIGINT; v_refIsCalled BOOLEAN; v_refIncrementBy BIGINT; v_refStartValue BIGINT; v_refMinValue BIGINT; v_refMaxValue BIGINT; v_refCacheValue BIGINT; v_refIsCycled BOOLEAN; v_stmt TEXT; v_trgLastValue BIGINT; v_trgIsCalled BOOLEAN; v_trgIncrementBy BIGINT; v_trgStartValue BIGINT; v_trgMinValue BIGINT; v_trgMaxValue BIGINT; v_trgCacheValue BIGINT; v_trgIsCycled BOOLEAN; v_endTimeId BIGINT; v_rqSeq TEXT; BEGIN v_fullSeqName = quote_ident(r_rel.rel_schema) || '.' || quote_ident(r_rel.rel_tblseq); -- get the sequence characteristics at start mark SELECT sequ_last_val, sequ_is_called, sequ_increment, sequ_start_val, sequ_min_val, sequ_max_val, sequ_cache_val, sequ_is_cycled INTO STRICT v_refLastValue, v_refIsCalled, v_refIncrementBy, v_refStartValue, v_refMinValue, v_refMaxValue, v_refCacheValue, v_refIsCycled FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq AND sequ_time_id = v_firstMarkTimeId; -- get the sequence characteristics at end mark or the current state IF v_lastMarkTimeId IS NULL AND upper_inf(r_rel.rel_time_range) THEN -- no supplied last mark and the sequence currently belongs to its group, so get current sequence characteritics IF emaj._pg_version_num() >= 100000 THEN v_stmt = 'SELECT rel.last_value, is_called, increment_by, start_value, min_value, max_value, cache_size, cycle ' || 'FROM ' || v_fullSeqName || ' rel, pg_catalog.pg_sequences ' || ' WHERE schemaname = ' || quote_literal(r_rel.rel_schema) || ' AND sequencename = ' || quote_literal(r_rel.rel_tblseq); ELSE v_stmt = 'SELECT last_value, is_called, increment_by, start_value, min_value, max_value, cache_value, is_cycled ' || 'FROM ' || v_fullSeqName; END IF; EXECUTE v_stmt INTO v_trgLastValue, v_trgIsCalled, v_trgIncrementBy, v_trgStartValue, v_trgMinValue, v_trgMaxValue, v_trgCacheValue, v_trgIsCycled; ELSE -- a last mark is supplied, or the sequence does not belong to its groupe anymore, so get sequence characteristics from the emaj_sequence -- table v_endTimeId = CASE WHEN upper_inf(r_rel.rel_time_range) OR v_lastMarkTimeId < upper(r_rel.rel_time_range) THEN v_lastMarkTimeId ELSE upper(r_rel.rel_time_range) END; SELECT sequ_last_val, sequ_is_called, sequ_increment, sequ_start_val, sequ_min_val, sequ_max_val, sequ_cache_val, sequ_is_cycled INTO STRICT v_trgLastValue, v_trgIsCalled, v_trgIncrementBy, v_trgStartValue, v_trgMinValue, v_trgMaxValue, v_trgCacheValue, v_trgIsCycled FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq AND sequ_time_id = v_endTimeId; END IF; -- build the ALTER SEQUENCE clause SELECT emaj._build_alter_seq(v_refLastValue, v_refIsCalled, v_refIncrementBy, v_refStartValue, v_refMinValue, v_refMaxValue, v_refCacheValue, v_refIsCycled, v_trgLastValue, v_trgIsCalled, v_trgIncrementBy, v_trgStartValue, v_trgMinValue, v_trgMaxValue, v_trgCacheValue, v_trgIsCycled) INTO v_rqSeq; -- insert into the temp table and return 1 if at least 1 characteristic needs to be changed IF v_rqSeq <> '' THEN v_rqSeq = 'ALTER SEQUENCE ' || v_fullSeqName || ' ' || v_rqSeq || ';'; EXECUTE 'INSERT INTO emaj_temp_script ' ' SELECT NULL, -1 * $1, txid_current(), $2' USING v_nbSeq + 1, v_rqSeq; RETURN 1; END IF; -- otherwise return 0 RETURN 0; END; $_gen_sql_seq$; CREATE OR REPLACE FUNCTION emaj._get_current_sequence_state(v_schema TEXT, v_sequence TEXT, v_timeId BIGINT) RETURNS emaj.emaj_sequence LANGUAGE plpgsql AS $_get_current_sequence_state$ -- The function returns the current state of a single sequence. -- Input: schema and sequence name, -- time_id to set the sequ_time_id -- Output: an emaj_sequence record DECLARE r_sequ emaj.emaj_sequence%ROWTYPE; BEGIN IF emaj._pg_version_num() >= 100000 THEN EXECUTE format('SELECT schemaname, sequencename, %s, rel.last_value, start_value, increment_by, max_value, min_value, cache_size,' ' cycle, rel.is_called FROM %I.%I rel, pg_catalog.pg_sequences ' ' WHERE schemaname = %L AND sequencename = %L', v_timeId, v_schema, v_sequence, v_schema, v_sequence) INTO STRICT r_sequ; ELSE EXECUTE format('SELECT %L, %L, %s, last_value, start_value, increment_by, max_value, min_value, cache_value, is_cycled, is_called' ' FROM %I.%I', v_schema, v_sequence, v_timeId, v_schema, v_sequence) INTO STRICT r_sequ; END IF; RETURN r_sequ; END; $_get_current_sequence_state$; CREATE OR REPLACE FUNCTION emaj._check_fk_groups(v_groupNames TEXT[]) RETURNS VOID LANGUAGE plpgsql AS $_check_fk_groups$ -- This function checks foreign key constraints for tables of a groups array. -- Tables from audit_only groups are ignored in this check because they will never be rolled back. -- Input: group names array DECLARE r_fk RECORD; BEGIN -- issue a warning if a table of the groups has a foreign key that references a table outside the groups FOR r_fk IN SELECT c.conname,r.rel_schema,r.rel_tblseq,nf.nspname,tf.relname FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n, pg_catalog.pg_class t, pg_catalog.pg_namespace nf, pg_catalog.pg_class tf, emaj.emaj_relation r, emaj.emaj_group g WHERE contype = 'f' -- FK constraints only AND c.conrelid = t.oid AND t.relnamespace = n.oid -- join for table and namespace AND c.confrelid = tf.oid AND tf.relnamespace = nf.oid -- join for referenced table and namespace AND n.nspname = r.rel_schema AND t.relname = r.rel_tblseq -- join on emaj_relation table AND r.rel_group = g.group_name -- join on emaj_group table AND upper_inf(r.rel_time_range) AND r.rel_group = ANY (v_groupNames) -- only tables currently belonging to the selected groups AND g.group_is_rollbackable -- only tables from rollbackable groups AND NOT EXISTS -- referenced table currently outside the groups (SELECT NULL FROM emaj.emaj_relation WHERE rel_schema = nf.nspname AND rel_tblseq = tf.relname AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames)) ORDER BY 1,2,3 LOOP RAISE WARNING '_check_fk_groups: The foreign key "%" on the table "%.%" references the table "%.%" that is outside the groups (%).', r_fk.conname,r_fk.rel_schema,r_fk.rel_tblseq,r_fk.nspname,r_fk.relname,array_to_string(v_groupNames,','); END LOOP; -- issue a warning if a table of the groups is referenced by a table outside the groups FOR r_fk IN SELECT c.conname,n.nspname,t.relname,r.rel_schema,r.rel_tblseq FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n, pg_catalog.pg_class t, pg_catalog.pg_namespace nf, pg_catalog.pg_class tf, emaj.emaj_relation r, emaj.emaj_group g WHERE contype = 'f' -- FK constraints only AND c.conrelid = t.oid AND t.relnamespace = n.oid -- join for table and namespace AND c.confrelid = tf.oid AND tf.relnamespace = nf.oid -- join for referenced table and namespace AND nf.nspname = r.rel_schema AND tf.relname = r.rel_tblseq -- join with emaj_relation table AND r.rel_group = g.group_name -- join on emaj_group table AND upper_inf(r.rel_time_range) AND r.rel_group = ANY (v_groupNames) -- only tables currently belonging to the selected groups AND g.group_is_rollbackable -- only tables from rollbackable groups AND NOT EXISTS -- referenced table outside the groups (SELECT NULL FROM emaj.emaj_relation WHERE rel_schema = n.nspname AND rel_tblseq = t.relname AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames)) ORDER BY 1,2,3 LOOP RAISE WARNING '_check_fk_groups: The table "%.%" is referenced by the foreign key "%" on the table "%.%" that is outside' ' the groups (%).', r_fk.rel_schema, r_fk.rel_tblseq, r_fk.conname, r_fk.nspname, r_fk.relname, array_to_string(v_groupNames,','); END LOOP; RETURN; END; $_check_fk_groups$; CREATE OR REPLACE FUNCTION emaj._lock_groups(v_groupNames TEXT[], v_lockMode TEXT, v_multiGroup BOOLEAN) RETURNS VOID LANGUAGE plpgsql AS $_lock_groups$ -- This function locks all tables of a groups array. -- The lock mode is provided by the calling function. -- It only locks existing tables. It is calling function's responsability to handle cases when application tables are missing. -- Input: array of group names, lock mode, flag indicating whether the function is called to processed several groups DECLARE v_nbRetry SMALLINT = 0; v_nbTbl INT; v_ok BOOLEAN = FALSE; v_fullTableName TEXT; 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 'LOCK_GROUPS' ELSE 'LOCK_GROUP' END,'BEGIN', array_to_string(v_groupNames,',')); -- acquire lock on all tables -- in case of deadlock, retry up to 5 times WHILE NOT v_ok AND v_nbRetry < 5 LOOP BEGIN -- scan all tables currently belonging to the groups v_nbTbl = 0; FOR r_tblsq IN SELECT rel_priority, rel_schema, rel_tblseq FROM emaj.emaj_relation, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE rel_group = ANY (v_groupNames) AND rel_kind = 'r' AND upper_inf(rel_time_range) AND relnamespace = pg_namespace.oid AND nspname = rel_schema AND relname = rel_tblseq ORDER BY rel_priority, rel_schema, rel_tblseq LOOP -- lock the table v_fullTableName = quote_ident(r_tblsq.rel_schema) || '.' || quote_ident(r_tblsq.rel_tblseq); EXECUTE format('LOCK TABLE %s IN %s MODE', v_fullTableName, v_lockMode); 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 '_lock_groups: A deadlock has been trapped while locking tables of group "%".', v_groupNames; END; END LOOP; IF NOT v_ok THEN RAISE EXCEPTION '_lock_groups: Too many (5) deadlocks encountered while locking tables of group "%".',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 'LOCK_GROUPS' ELSE 'LOCK_GROUP' END, 'END', array_to_string(v_groupNames,','), v_nbTbl || ' tables locked, ' || v_nbRetry || ' deadlock(s)'); RETURN; END; $_lock_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 log 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 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 WARNING 'emaj_create_group: Although the group "%" is referenced into the emaj_group_def table, it is left empty.', v_groupName; END IF; -- performs various checks on the group's content described in the emaj_group_def table IF NOT v_is_empty THEN 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: error, %', 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; 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, group_nb_table, group_nb_sequence) VALUES (v_groupName, v_isRollbackable, v_timeId, FALSE, FALSE, NOT v_isRollbackable, 0, 0); -- populate the group IF NOT v_is_empty THEN -- create new E-Maj log 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) PERFORM emaj._create_tbl(grpdef_schema, grpdef_tblseq, grpdef_group, grpdef_priority, grpdef_log_dat_tsp, grpdef_log_idx_tsp, v_timeId, v_isRollbackable, FALSE) FROM ( SELECT grpdef_schema, grpdef_tblseq, grpdef_group, grpdef_priority, grpdef_log_dat_tsp, grpdef_log_idx_tsp 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 ) AS t; SELECT count(*) INTO v_nbTbl FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'r' AND upper_inf(rel_time_range); -- get and process all sequences of the group (in alphabetical order) PERFORM emaj._create_seq(grpdef_schema, grpdef_tblseq, grpdef_group, v_timeId) FROM ( SELECT grpdef_schema, grpdef_tblseq, grpdef_group, grpdef_priority 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_schema, grpdef_tblseq ) AS t; SELECT count(*) INTO v_nbSeq FROM emaj.emaj_relation WHERE rel_group = v_groupName AND rel_kind = 'S' AND upper_inf(rel_time_range); -- 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]); END IF; -- 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._drop_group(v_groupName TEXT, v_isForced BOOLEAN) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_drop_group$ -- This function effectively deletes the emaj objects for all tables of a group. -- It also drops log schemas that are not useful any more. -- Input: group name, and a boolean indicating whether the group's state has to be checked -- Output: number of processed tables and sequences -- The function is defined as SECURITY DEFINER so that log schemas can be dropped. DECLARE v_eventTriggers TEXT[]; v_timeId BIGINT; v_nbTb INT; r_rel emaj.emaj_relation%ROWTYPE; BEGIN -- get the time stamp of the operation SELECT emaj._set_time_stamp('D') INTO v_timeId; -- disable event triggers that protect emaj components and keep in memory these triggers name SELECT emaj._disable_event_triggers() INTO v_eventTriggers; -- delete the emaj objects and references for each table and sequences of the group FOR r_rel IN SELECT * FROM emaj.emaj_relation WHERE rel_group = v_groupName ORDER BY rel_priority, rel_schema, rel_tblseq, rel_time_range LOOP PERFORM CASE WHEN r_rel.rel_kind = 'r' THEN emaj._drop_tbl(r_rel, v_timeId) WHEN r_rel.rel_kind = 'S' THEN emaj._drop_seq(r_rel, v_timeId) END; END LOOP; -- drop the E-Maj log schemas that are now useless (i.e. not used by any other created group) PERFORM emaj._drop_log_schemas(CASE WHEN v_isForced THEN 'FORCE_DROP_GROUP' ELSE 'DROP_GROUP' END, v_isForced); -- delete group row from the emaj_group table. -- By cascade, it also deletes rows from emaj_mark DELETE FROM emaj.emaj_group WHERE group_name = v_groupName RETURNING group_nb_table + group_nb_sequence INTO v_nbTb; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); RETURN v_nbTb; END; $_drop_group$; CREATE OR REPLACE FUNCTION emaj._alter_plan(v_groupNames TEXT[], v_timeId BIGINT) RETURNS VOID LANGUAGE plpgsql AS $_alter_plan$ -- This function build the elementary steps that will be needed to perform an alter_groups operation. -- Looking at emaj_relation and emaj_group_def tables, it populates the emaj_alter_plan table that will be used by the _alter_exec() -- function. -- Input: group names array, timestamp id of the operation (it will be used to identify rows in the emaj_alter_plan table) BEGIN -- the plan is built using the same steps order than the coming execution -- determine the relations that do not belong to the groups anymore INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority) SELECT v_timeId, CAST(CASE WHEN rel_kind = 'r' THEN 'REMOVE_TBL' ELSE 'REMOVE_SEQ' END AS emaj._alter_step_enum), rel_schema, rel_tblseq, rel_group, rel_priority FROM emaj.emaj_relation WHERE rel_group = ANY (v_groupNames) AND upper_inf(rel_time_range) AND NOT EXISTS ( SELECT NULL FROM emaj.emaj_group_def WHERE grpdef_schema = rel_schema AND grpdef_tblseq = rel_tblseq AND grpdef_group = ANY (v_groupNames)); -- determine the tables that need to be "repaired" (damaged or out of sync E-Maj components) -- (normally, there should not be any REPAIR_SEQ - if any, the _alter_exec() function will produce an exception) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority, altr_new_group) SELECT v_timeId, CAST(CASE WHEN rel_kind = 'r' THEN 'REPAIR_TBL' ELSE 'REPAIR_SEQ' END AS emaj._alter_step_enum), rel_schema, rel_tblseq, rel_group, grpdef_priority, CASE WHEN rel_group <> grpdef_group THEN grpdef_group ELSE NULL END FROM ( -- all damaged or out of sync tables SELECT DISTINCT ver_schema, ver_tblseq FROM emaj._verify_groups(v_groupNames, FALSE) ) AS t, emaj.emaj_relation, emaj.emaj_group_def WHERE rel_schema = ver_schema AND rel_tblseq = ver_tblseq AND upper_inf(rel_time_range) AND rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND rel_group = ANY (v_groupNames) AND grpdef_group = ANY (v_groupNames) -- exclude relations that will have been removed in a previous step AND NOT EXISTS ( SELECT 0 FROM emaj.emaj_alter_plan WHERE altr_schema = rel_schema AND altr_tblseq = rel_tblseq AND altr_time_id = v_timeId AND altr_step IN ('REMOVE_TBL', 'REMOVE_SEQ')); -- determine the groups that will be reset (i.e. those in IDLE state) INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group) SELECT v_timeId, 'RESET_GROUP', '', '', group_name FROM emaj.emaj_group WHERE group_name = ANY (v_groupNames) AND NOT group_is_logging; -- determine the tables whose log data tablespace in emaj_group_def has changed INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority, altr_new_group) SELECT v_timeId, 'CHANGE_TBL_LOG_DATA_TSP', rel_schema, rel_tblseq, rel_group, grpdef_priority, CASE WHEN rel_group <> grpdef_group THEN grpdef_group ELSE NULL END FROM emaj.emaj_relation, emaj.emaj_group_def WHERE rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND grpdef_group = ANY (v_groupNames) AND rel_kind = 'r' AND coalesce(rel_log_dat_tsp,'') <> coalesce(grpdef_log_dat_tsp,'') -- exclude tables that will have been repaired in a previous step AND NOT EXISTS ( SELECT 0 FROM emaj.emaj_alter_plan WHERE altr_schema = rel_schema AND altr_tblseq = rel_tblseq AND altr_time_id = v_timeId AND altr_step = 'REPAIR_TBL'); -- determine the tables whose log data tablespace in emaj_group_def has changed INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority, altr_new_group) SELECT v_timeId, 'CHANGE_TBL_LOG_INDEX_TSP', rel_schema, rel_tblseq, rel_group, grpdef_priority, CASE WHEN rel_group <> grpdef_group THEN grpdef_group ELSE NULL END FROM emaj.emaj_relation, emaj.emaj_group_def WHERE rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND grpdef_group = ANY (v_groupNames) AND rel_kind = 'r' AND coalesce(rel_log_idx_tsp,'') <> coalesce(grpdef_log_idx_tsp,'') -- exclude tables that will have been repaired in a previous step AND NOT EXISTS ( SELECT 0 FROM emaj.emaj_alter_plan WHERE altr_schema = rel_schema AND altr_tblseq = rel_tblseq AND altr_time_id = v_timeId AND altr_step = 'REPAIR_TBL'); -- determine the tables or sequences that change their group ownership INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority, altr_new_group) SELECT v_timeId, CAST(CASE WHEN rel_kind = 'r' THEN 'MOVE_TBL' ELSE 'MOVE_SEQ' END AS emaj._alter_step_enum), rel_schema, rel_tblseq, rel_group, grpdef_priority, grpdef_group FROM emaj.emaj_relation, emaj.emaj_group_def WHERE rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND grpdef_group = ANY (v_groupNames) AND rel_group <> grpdef_group; -- determine the tables that change their priority level INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority) SELECT v_timeId, 'CHANGE_REL_PRIORITY', rel_schema, rel_tblseq, rel_group, grpdef_priority FROM emaj.emaj_relation, emaj.emaj_group_def WHERE rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND upper_inf(rel_time_range) AND rel_kind = 'r' AND rel_group = ANY (v_groupNames) AND grpdef_group = ANY (v_groupNames) AND ( (rel_priority IS NULL AND grpdef_priority IS NOT NULL) OR (rel_priority IS NOT NULL AND grpdef_priority IS NULL) OR (rel_priority <> grpdef_priority) ); -- determine the relations to add to the groups INSERT INTO emaj.emaj_alter_plan (altr_time_id, altr_step, altr_schema, altr_tblseq, altr_group, altr_priority) SELECT v_timeId, CAST(CASE WHEN relkind = 'r' THEN 'ADD_TBL' ELSE 'ADD_SEQ' END AS emaj._alter_step_enum), grpdef_schema, grpdef_tblseq, grpdef_group, grpdef_priority FROM emaj.emaj_group_def, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE grpdef_group = ANY (v_groupNames) AND NOT EXISTS ( SELECT NULL FROM emaj.emaj_relation WHERE rel_schema = grpdef_schema AND rel_tblseq = grpdef_tblseq AND upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames)) AND relnamespace = pg_namespace.oid AND nspname = grpdef_schema AND relname = grpdef_tblseq; -- set the altr_group_is_logging column value UPDATE emaj.emaj_alter_plan SET altr_group_is_logging = group_is_logging FROM emaj.emaj_group WHERE altr_group = group_name AND altr_time_id = v_timeId AND altr_group <> ''; -- set the altr_new_group_is_logging column value for the cases when the group ownership changes UPDATE emaj.emaj_alter_plan SET altr_new_group_is_logging = group_is_logging FROM emaj.emaj_group WHERE altr_new_group = group_name AND altr_time_id = v_timeId AND altr_new_group IS NOT NULL; -- and return RETURN; END; $_alter_plan$; CREATE OR REPLACE FUNCTION emaj._alter_exec(v_timeId BIGINT, v_multiGroup BOOLEAN) RETURNS VOID LANGUAGE plpgsql AS $_alter_exec$ -- This function executes the alter groups operation that has been planned by the _alter_plan() function. -- It looks at the emaj_alter_plan table and executes elementary step in proper order. -- Input: timestamp id of the operation DECLARE v_function TEXT; v_logDatTsp TEXT; v_logIdxTsp TEXT; v_isRollbackable BOOLEAN; r_plan emaj.emaj_alter_plan%ROWTYPE; r_rel emaj.emaj_relation%ROWTYPE; BEGIN v_function = CASE WHEN v_multiGroup THEN 'ALTER_GROUPS' ELSE 'ALTER_GROUP' END; -- scan the emaj_alter_plan table and execute each elementary item in the proper order FOR r_plan IN SELECT * FROM emaj.emaj_alter_plan WHERE altr_time_id = v_timeId ORDER BY altr_step, altr_priority, altr_schema, altr_tblseq, altr_group LOOP CASE r_plan.altr_step WHEN 'REMOVE_TBL' THEN -- remove a table from its group PERFORM emaj._remove_tbl(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, r_plan.altr_group_is_logging, v_timeId, v_function); -- WHEN 'REMOVE_SEQ' THEN -- remove a sequence from its group PERFORM emaj._remove_seq(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, r_plan.altr_group_is_logging, v_timeId, v_function); -- WHEN 'RESET_GROUP' THEN -- reset a group PERFORM emaj._reset_groups(ARRAY[r_plan.altr_group]); -- WHEN 'REPAIR_TBL' THEN IF r_plan.altr_group_is_logging THEN RAISE EXCEPTION 'alter_exec: Cannot repair the table %.%. Its group % is in LOGGING state.', r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group; ELSE -- remove the table from its group PERFORM emaj._drop_tbl(emaj.emaj_relation.*, v_timeId) FROM emaj.emaj_relation WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); -- get the is_rollbackable status of the related group SELECT group_is_rollbackable INTO v_isRollbackable FROM emaj.emaj_group WHERE group_name = r_plan.altr_group; -- and recreate it PERFORM emaj._create_tbl(grpdef_schema, grpdef_tblseq, grpdef_group, grpdef_priority, grpdef_log_dat_tsp, grpdef_log_idx_tsp, v_timeId, v_isRollbackable, r_plan.altr_group_is_logging) FROM emaj.emaj_group_def WHERE grpdef_group = coalesce (r_plan.altr_new_group, r_plan.altr_group) AND grpdef_schema = r_plan.altr_schema AND grpdef_tblseq = r_plan.altr_tblseq; END IF; -- WHEN 'REPAIR_SEQ' THEN RAISE EXCEPTION 'alter_exec: Internal error, trying to repair a sequence (%.%) is abnormal.', r_plan.altr_schema, r_plan.altr_tblseq; -- WHEN 'CHANGE_TBL_LOG_DATA_TSP' THEN -- get the table description from emaj_relation SELECT * INTO r_rel FROM emaj.emaj_relation WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); -- get the table description from emaj_group_def SELECT grpdef_log_dat_tsp INTO v_logDatTsp FROM emaj.emaj_group_def WHERE grpdef_group = coalesce (r_plan.altr_new_group, r_plan.altr_group) AND grpdef_schema = r_plan.altr_schema AND grpdef_tblseq = r_plan.altr_tblseq; -- then alter the relation, depending on the changes PERFORM emaj._change_log_data_tsp_tbl(r_rel.rel_schema, r_rel.rel_tblseq, r_rel.rel_log_schema, r_rel.rel_log_table, r_rel.rel_log_dat_tsp, v_logDatTsp, v_function); -- WHEN 'CHANGE_TBL_LOG_INDEX_TSP' THEN -- get the table description from emaj_relation SELECT * INTO r_rel FROM emaj.emaj_relation WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); -- get the table description from emaj_group_def SELECT grpdef_log_idx_tsp INTO v_logIdxTsp FROM emaj.emaj_group_def WHERE grpdef_group = coalesce (r_plan.altr_new_group, r_plan.altr_group) AND grpdef_schema = r_plan.altr_schema AND grpdef_tblseq = r_plan.altr_tblseq; -- then alter the relation, depending on the changes PERFORM emaj._change_log_index_tsp_tbl(r_rel.rel_schema, r_rel.rel_tblseq, r_rel.rel_log_schema, r_rel.rel_log_index, r_rel.rel_log_idx_tsp, v_logIdxTsp, v_function); -- WHEN 'MOVE_TBL' THEN -- move a table from one group to another group PERFORM emaj._move_tbl(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, r_plan.altr_group_is_logging, r_plan.altr_new_group, r_plan.altr_new_group_is_logging, v_timeId, v_function); -- WHEN 'MOVE_SEQ' THEN -- move a sequence from one group to another group PERFORM emaj._move_seq(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, r_plan.altr_group_is_logging, r_plan.altr_new_group, r_plan.altr_new_group_is_logging, v_timeId, v_function); -- WHEN 'CHANGE_REL_PRIORITY' THEN -- get the table description from emaj_relation SELECT * INTO r_rel FROM emaj.emaj_relation WHERE rel_schema = r_plan.altr_schema AND rel_tblseq = r_plan.altr_tblseq AND upper_inf(rel_time_range); -- update the emaj_relation table to report the priority change PERFORM emaj._change_priority_tbl(r_plan.altr_schema, r_plan.altr_tblseq, r_rel.rel_priority, r_plan.altr_priority, v_function); -- WHEN 'ADD_TBL' THEN -- add a table to a group PERFORM emaj._add_tbl(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, grpdef_priority, grpdef_log_dat_tsp, grpdef_log_idx_tsp, r_plan.altr_group_is_logging, v_timeId, v_function) FROM emaj.emaj_group_def WHERE grpdef_group = r_plan.altr_group AND grpdef_schema = r_plan.altr_schema AND grpdef_tblseq = r_plan.altr_tblseq; -- WHEN 'ADD_SEQ' THEN -- add a sequence to a group PERFORM emaj._add_seq(r_plan.altr_schema, r_plan.altr_tblseq, r_plan.altr_group, r_plan.altr_group_is_logging, v_timeId, v_function); -- END CASE; END LOOP; RETURN; END; $_alter_exec$; CREATE OR REPLACE FUNCTION emaj.emaj_sync_def_group(v_group TEXT) RETURNS INTEGER LANGUAGE plpgsql AS $emaj_sync_def_group$ -- The function re-synchronizes the content of the emaj_group_def table for one tables group -- based on the current groups content. -- Inputs: group name -- Outputs: number of tables and sequences of the tables group DECLARE v_nbTblSeq INT; BEGIN -- check that the group exists PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_group], v_mayBeNull := FALSE, v_lockGroups := FALSE, v_checkList := ''); -- in emaj_group_def, delete existing rows for the group DELETE FROM emaj.emaj_group_def WHERE grpdef_group = v_group; -- ... and insert the rows describing the current group content INSERT INTO emaj.emaj_group_def (grpdef_group, grpdef_schema, grpdef_tblseq, grpdef_priority, grpdef_log_dat_tsp, grpdef_log_idx_tsp) SELECT rel_group, rel_schema, rel_tblseq, rel_priority, rel_log_dat_tsp, rel_log_idx_tsp FROM emaj.emaj_relation WHERE rel_group = v_group AND upper_inf(rel_time_range); GET DIAGNOSTICS v_nbTblSeq = ROW_COUNT; -- reset the group_has_waiting_changes flag UPDATE emaj.emaj_group SET group_has_waiting_changes = FALSE WHERE group_name = v_group; -- take a trace in the emaj_hist table INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object) VALUES ('SYNC_DEF_GROUP', 'EMAJ_GROUP_DEF SYNCHRONIZED', v_group); RETURN v_nbTblSeq; END; $emaj_sync_def_group$; COMMENT ON FUNCTION emaj.emaj_sync_def_group(TEXT) IS $$Re-synchronize the content of the emaj_group_def table for one tables group based on its current content.$$; 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 SET search_path = pg_catalog, pg_temp 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_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 log 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_kind, quote_ident(rel_schema) || '.' || quote_ident(rel_tblseq) AS full_relation_name 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 IF r_tblsq.rel_kind = 'r' THEN -- if it is a table, enable the emaj log and truncate triggers EXECUTE format('ALTER TABLE %s ENABLE TRIGGER emaj_log_trg, ENABLE TRIGGER emaj_trunc_trg', r_tblsq.full_relation_name); END IF; 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 SET search_path = pg_catalog, pg_temp 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 IF r_tblsq.rel_kind = '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 format('ALTER TABLE %s DISABLE TRIGGER emaj_log_trg', v_fullTableName); 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 format('ALTER TABLE %s DISABLE TRIGGER emaj_trunc_trg', v_fullTableName); 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; END IF; 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._delete_before_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp 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 or which are linked to other groups) 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 OR upper_inf(rel_time_range) OR rel_group <> v_groupName) ORDER BY 1,2 LOOP EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r_rel.rel_log_schema, r_rel.rel_log_table); END LOOP; -- delete obsolete emaj_sequence -- (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; -- keep a trace of the relation group ownership history -- and finaly delete from the emaj_relation table the relation that ended before the new first mark WITH deleted AS ( DELETE FROM emaj.emaj_relation WHERE rel_group = v_groupName AND upper(rel_time_range) <= v_markTimeId RETURNING rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind ) INSERT INTO emaj.emaj_rel_hist (relh_schema, relh_tblseq, relh_time_range, relh_group, relh_kind) SELECT rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind FROM deleted; -- drop the E-Maj log 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 format('DELETE FROM %s WHERE emaj_gid <= $1', r_rel.log_table_name) USING v_markGlobalSeq; END LOOP; -- process emaj_seq_hole content -- delete all existing holes, if any, before the mark -- (it may delete holes for timeranges that do not belong to the group, if a table has been moved to another group, -- but is safe enough for rollbacks) 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; -- 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._rlbk_planning(v_rlbkId INT) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_rlbk_planning$ -- This function builds the rollback steps for a rollback operation. -- It stores the result into the emaj_rlbk_plan table. -- The function returns the effective number of tables to process. -- It is called in an autonomous dblink transaction, if possible. -- The function is defined as SECURITY DEFINER so that emaj_viwer role can write into rollback tables without having specific privileges -- to do it. DECLARE v_groupNames TEXT[]; v_mark TEXT; v_isLoggedRlbk BOOLEAN; v_markTimeId BIGINT; v_nbSession INT; v_ctrlStepName emaj._rlbk_step_enum; v_effNbTable INT; v_batchNumber INT; v_checks INT; v_estimDuration INTERVAL; v_estimMethod INT; v_estimDropFkDuration INTERVAL; v_estimDropFkMethod INT; v_estimSetFkDefDuration INTERVAL; v_estimSetFkDefMethod INT; v_avg_row_rlbk INTERVAL; v_avg_row_del_log INTERVAL; v_avg_fkey_check INTERVAL; v_fixed_step_rlbk INTERVAL; v_fixed_dblink_rlbk INTERVAL; v_sessionLoad INTERVAL[]; v_minSession INT; v_minDuration INTERVAL; v_nbStep INT; r_tbl RECORD; r_fk RECORD; r_batch RECORD; BEGIN -- get the rollback characteristics for the emaj_rlbk event SELECT rlbk_groups, rlbk_mark, rlbk_is_logged, rlbk_nb_session, CASE WHEN rlbk_is_dblink_used THEN 'CTRL+DBLINK'::emaj._rlbk_step_enum ELSE 'CTRL-DBLINK'::emaj._rlbk_step_enum END INTO v_groupNames, v_mark, v_isLoggedRlbk, v_nbSession, v_ctrlStepName FROM emaj.emaj_rlbk WHERE rlbk_id = v_rlbkId; -- get some mark attributes from emaj_mark SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] AND mark_name = v_mark; -- get all duration parameters that will be needed later from the emaj_param table, -- or get default values for rows that are not present in emaj_param table SELECT coalesce ((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'avg_row_rollback_duration'),'100 microsecond'::INTERVAL), coalesce ((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'avg_row_delete_log_duration'),'10 microsecond'::INTERVAL), coalesce ((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'avg_fkey_check_duration'),'5 microsecond'::INTERVAL), coalesce ((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'fixed_step_rollback_duration'),'2.5 millisecond'::INTERVAL), coalesce ((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'fixed_dblink_rollback_duration'),'4 millisecond'::INTERVAL) INTO v_avg_row_rlbk, v_avg_row_del_log, v_avg_fkey_check, v_fixed_step_rlbk, v_fixed_dblink_rlbk; -- insert into emaj_rlbk_plan a row per table currently belonging to the tables groups to process. INSERT INTO emaj.emaj_rlbk_plan (rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object) SELECT v_rlbkId, 'LOCK_TABLE', rel_schema, rel_tblseq, '' FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY(v_groupNames) AND rel_kind = 'r'; -- insert into emaj_rlbk_plan a row per table to effectively rollback. -- the numbers of log rows is computed using the _log_stat_tbl() function. -- a final check will be performed after tables will be locked to be sure no new table will have been updated INSERT INTO emaj.emaj_rlbk_plan (rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_target_time_id, rlbp_estimated_quantity) SELECT v_rlbkId, 'RLBK_TABLE', rel_schema, rel_tblseq, '', greatest(v_markTimeId, lower(rel_time_range)), emaj._log_stat_tbl(t, greatest(v_markTimeId, lower(rel_time_range)), NULL) FROM (SELECT * FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'r') AS t WHERE emaj._log_stat_tbl(t, greatest(v_markTimeId, lower(rel_time_range)), NULL) > 0; GET DIAGNOSTICS v_effNbTable = ROW_COUNT; -- -- group tables into batchs to process all tables linked by foreign keys as a batch -- v_batchNumber = 1; -- allocate tables with rows to rollback to batch number starting with the heaviest to rollback tables -- as reported by emaj_log_stat_group() function FOR r_tbl IN SELECT * FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' ORDER BY rlbp_estimated_quantity DESC LOOP -- is the table already allocated to a batch number (it may have been already allocated because of a fkey link) ? PERFORM 0 FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' AND rlbp_schema = r_tbl.rlbp_schema AND rlbp_table = r_tbl.rlbp_table AND rlbp_batch_number IS NULL; -- no, IF FOUND THEN -- allocate the table to the batch number, with all other tables linked by foreign key constraints PERFORM emaj._rlbk_set_batch_number(v_rlbkId, v_batchNumber, r_tbl.rlbp_schema, r_tbl.rlbp_table); v_batchNumber = v_batchNumber + 1; END IF; END LOOP; -- -- if unlogged rollback, register into emaj_rlbk_plan "disable log triggers", "deletes from log tables" -- and "enable log trigger" steps -- IF NOT v_isLoggedRlbk THEN -- compute the cost for each DIS_LOG_TRG step -- if DIS_LOG_TRG statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'DIS_LOG_TRG'; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- otherwise, use the fixed_step_rollback_duration parameter v_estimDuration = v_fixed_step_rlbk; v_estimMethod = 3; END IF; -- insert all DIS_LOG_TRG steps INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) SELECT v_rlbkId, 'DIS_LOG_TRG', rlbp_schema, rlbp_table, '', rlbp_batch_number, v_estimDuration, v_estimMethod FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE'; -- insert all DELETE_LOG steps. But the duration estimates will be computed later -- the estimated number of log rows to delete is set to the the estimated number of updates. This is underestimated -- in particular when SQL UPDATES are logged. But the collected statistics used for duration estimates are also -- based on the estimated number of updates. INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_target_time_id, rlbp_batch_number, rlbp_estimated_quantity ) SELECT v_rlbkId, 'DELETE_LOG', rlbp_schema, rlbp_table, '', rlbp_target_time_id, rlbp_batch_number, rlbp_estimated_quantity FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE'; -- compute the cost for each ENA_LOG_TRG step -- if DIS_LOG_TRG statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'ENA_LOG_TRG'; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- otherwise, use the fixed_step_rollback_duration parameter v_estimDuration = v_fixed_step_rlbk; v_estimMethod = 3; END IF; -- insert all ENA_LOG_TRG steps INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) SELECT v_rlbkId, 'ENA_LOG_TRG', rlbp_schema, rlbp_table, '', rlbp_batch_number, v_estimDuration, v_estimMethod FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE'; END IF; -- -- process application triggers -- -- compute the cost for each DIS_APP_TRG step -- if DIS_APP_TRG statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'DIS_APP_TRG'; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- otherwise, use the fixed_step_rollback_duration parameter v_estimDuration = v_fixed_step_rlbk; v_estimMethod = 3; END IF; -- insert all DIS_APP_TRG steps INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) SELECT v_rlbkId, 'DIS_APP_TRG', rlbp_schema, rlbp_table, tgname, rlbp_batch_number, v_estimDuration, v_estimMethod FROM emaj.emaj_rlbk_plan, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_trigger WHERE nspname = rlbp_schema AND relname = rlbp_table AND relnamespace = pg_namespace.oid AND tgrelid = pg_class.oid AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' AND NOT tgisinternal AND NOT tgenabled = 'D' AND tgname NOT IN ('emaj_trunc_trg','emaj_log_trg') AND NOT EXISTS (SELECT trg_name FROM emaj.emaj_ignored_app_trigger WHERE trg_schema = rlbp_schema AND trg_table = rlbp_table AND trg_name = tgname); -- compute the cost for each ENA_APP_TRG step -- if ENA_APP_TRG statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'ENA_APP_TRG'; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- otherwise, use the fixed_step_rollback_duration parameter v_estimDuration = v_fixed_step_rlbk; v_estimMethod = 3; END IF; -- insert all ENA_APP_TRG steps INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_object_def, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) SELECT v_rlbkId, 'ENA_APP_TRG', rlbp_schema, rlbp_table, rlbp_object, CASE WHEN tgenabled = 'A' THEN 'ALWAYS' WHEN tgenabled = 'R' THEN 'REPLICA' ELSE '' END, rlbp_batch_number, v_estimDuration, v_estimMethod FROM emaj.emaj_rlbk_plan, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_trigger WHERE nspname = rlbp_schema AND relname = rlbp_table AND relnamespace = pg_namespace.oid AND tgrelid = pg_class.oid AND tgname = rlbp_object AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'DIS_APP_TRG'; -- -- process foreign key to define which action to perform on them -- -- First compute the fixed duration estimates for each 'DROP_FK' and 'SET_FK_DEF' steps -- if DROP_FK statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimDropFkDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'DROP_FK'; v_estimDropFkMethod = 2; IF v_estimDropFkDuration IS NULL THEN -- if no statistics are available for this step, use the fixed_step_rollback_duration parameter v_estimDropFkDuration = v_fixed_step_rlbk; v_estimDropFkMethod = 3; END IF; -- if SET_FK_DEF statistics are available, compute an average cost SELECT sum(rlbt_duration) / sum(rlbt_quantity) INTO v_estimSetFkDefDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'SET_FK_DEF'; v_estimSetFkDefMethod = 2; IF v_estimSetFkDefDuration IS NULL THEN -- if no statistics are available for this step, use the fixed_step_rollback_duration parameter v_estimSetFkDefDuration = v_fixed_step_rlbk; v_estimSetFkDefMethod = 3; END IF; -- select all foreign keys belonging to or referencing the tables to process FOR r_fk IN SELECT c.oid AS conoid, c.conname, n.nspname, t.relname, t.reltuples, pg_get_constraintdef(c.oid) AS def, c.condeferrable, c.condeferred, c.confupdtype, c.confdeltype, r.rlbp_batch_number FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n, pg_catalog.pg_class t, emaj.emaj_rlbk_plan r WHERE c.contype = 'f' -- FK constraints only AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' -- tables to rollback AND c.conrelid = t.oid AND t.relnamespace = n.oid -- joins for table and namespace AND n.nspname = r.rlbp_schema AND t.relname = r.rlbp_table -- join on emaj_rlbk_plan table UNION SELECT c.oid AS conoid, c.conname, n.nspname, t.relname, t.reltuples, pg_get_constraintdef(c.oid) AS def, c.condeferrable, c.condeferred, c.confupdtype, c.confdeltype, r.rlbp_batch_number FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n, pg_catalog.pg_class t, pg_catalog.pg_namespace rn, pg_catalog.pg_class rt, emaj.emaj_rlbk_plan r WHERE c.contype = 'f' -- FK constraints only AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' -- tables to rollback AND c.conrelid = t.oid AND t.relnamespace = n.oid -- joins for table and namespace AND c.confrelid = rt.oid AND rt.relnamespace = rn.oid -- joins for referenced table and namespace AND rn.nspname = r.rlbp_schema AND rt.relname = r.rlbp_table -- join on emaj_rlbk_plan table ORDER BY nspname, relname, conname LOOP -- depending on the foreign key characteristics, record as 'to be dropped' or 'to be set deffered' or 'to just be reset immediate' IF NOT r_fk.condeferrable OR r_fk.confupdtype <> 'a' OR r_fk.confdeltype <> 'a' THEN -- non deferrable fkeys and deferrable fkeys with an action for UPDATE or DELETE other than 'no action' need to be dropped INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) VALUES ( v_rlbkId, 'DROP_FK', r_fk.nspname, r_fk.relname, r_fk.conname, r_fk.rlbp_batch_number, v_estimDropFkDuration, v_estimDropFkMethod ); INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_object_def, rlbp_estimated_quantity ) VALUES ( v_rlbkId, 'ADD_FK', r_fk.nspname, r_fk.relname, r_fk.conname, r_fk.rlbp_batch_number, r_fk.def, r_fk.reltuples ); ELSE -- other deferrable but not deferred fkeys need to be set deferred IF NOT r_fk.condeferred THEN INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_duration, rlbp_estimate_method ) VALUES ( v_rlbkId, 'SET_FK_DEF', r_fk.nspname, r_fk.relname, r_fk.conname, r_fk.rlbp_batch_number, v_estimSetFkDefDuration, v_estimSetFkDefMethod ); END IF; -- deferrable fkeys are recorded as 'to be set immediate at the end of the rollback operation' -- compute the number of fkey values to check at set immediate time SELECT (coalesce( -- get the number of rolled back rows in the referencing table, if any (SELECT rlbp_estimated_quantity FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' -- tables of the rollback event AND rlbp_schema = r_fk.nspname AND rlbp_table = r_fk.relname) -- referencing schema.table , 0)) + (coalesce( -- get the number of rolled back rows in the referenced table, if any (SELECT rlbp_estimated_quantity FROM emaj.emaj_rlbk_plan, pg_catalog.pg_constraint c, pg_catalog.pg_namespace rn, pg_catalog.pg_class rt WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' -- tables of the rollback event AND c.oid = r_fk.conoid -- constraint id AND c.confrelid = rt.oid AND rt.relnamespace = rn.oid -- joins for referenced schema.table AND rn.nspname = rlbp_schema AND rt.relname = rlbp_table) -- join on emaj_rlbk_plan , 0)) INTO v_checks; -- and record the SET_FK_IMM step INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_batch_number, rlbp_estimated_quantity ) VALUES ( v_rlbkId, 'SET_FK_IMM', r_fk.nspname, r_fk.relname, r_fk.conname, r_fk.rlbp_batch_number, v_checks ); END IF; END LOOP; -- -- Now compute the estimation duration for each complex step ('RLBK_TABLE', 'DELETE_LOG', 'ADD_FK', 'SET_FK_IMM') -- -- Compute the rollback duration estimates for the tables -- for each table with content to rollback FOR r_tbl IN SELECT * FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' LOOP -- first look at the previous rollback durations for the table and with similar rollback volume (same order of magnitude) SELECT sum(rlbt_duration) * r_tbl.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'RLBK_TABLE' AND rlbt_quantity > 0 AND rlbt_schema = r_tbl.rlbp_schema AND rlbt_table = r_tbl.rlbp_table AND rlbt_quantity / r_tbl.rlbp_estimated_quantity < 10 AND r_tbl.rlbp_estimated_quantity / rlbt_quantity < 10; v_estimMethod = 1; IF v_estimDuration IS NULL THEN -- if there is no previous rollback operation with similar volume, take statistics for the table with all available volumes SELECT sum(rlbt_duration) * r_tbl.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'RLBK_TABLE' AND rlbt_quantity > 0 AND rlbt_schema = r_tbl.rlbp_schema AND rlbt_table = r_tbl.rlbp_table; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- if there is no previous rollback operation, use the avg_row_rollback_duration from the emaj_param table v_estimDuration = v_avg_row_rlbk * r_tbl.rlbp_estimated_quantity + v_fixed_step_rlbk; v_estimMethod = 3; END IF; END IF; UPDATE emaj.emaj_rlbk_plan SET rlbp_estimated_duration = v_estimDuration, rlbp_estimate_method = v_estimMethod WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE' AND rlbp_schema = r_tbl.rlbp_schema AND rlbp_table = r_tbl.rlbp_table; END LOOP; -- Compute the log rows delete duration for the tables FOR r_tbl IN SELECT * FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'DELETE_LOG' LOOP -- first look at the previous rollback durations for the table and with similar rollback volume (same order of magnitude) SELECT sum(rlbt_duration) * r_tbl.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'DELETE_LOG' AND rlbt_quantity > 0 AND rlbt_schema = r_tbl.rlbp_schema AND rlbt_table = r_tbl.rlbp_table AND rlbt_quantity / r_tbl.rlbp_estimated_quantity < 10 AND r_tbl.rlbp_estimated_quantity / rlbt_quantity < 10; v_estimMethod = 1; IF v_estimDuration IS NULL THEN -- if there is no previous rollback operation with similar volume, take statistics for the table with all available volumes SELECT sum(rlbt_duration) * r_tbl.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'DELETE_LOG' AND rlbt_quantity > 0 AND rlbt_schema = r_tbl.rlbp_schema AND rlbt_table = r_tbl.rlbp_table; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- if there is no previous rollback operation, use the avg_row_rollback_duration from the emaj_param table v_estimDuration = v_avg_row_del_log * r_tbl.rlbp_estimated_quantity + v_fixed_step_rlbk; v_estimMethod = 3; END IF; END IF; UPDATE emaj.emaj_rlbk_plan SET rlbp_estimated_duration = v_estimDuration, rlbp_estimate_method = v_estimMethod WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'DELETE_LOG' AND rlbp_schema = r_tbl.rlbp_schema AND rlbp_table = r_tbl.rlbp_table; END LOOP; -- Compute the fkey recreation duration FOR r_fk IN SELECT * FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'ADD_FK' LOOP IF r_fk.rlbp_estimated_quantity = 0 THEN -- empty table (or table not analyzed) => duration = 0 v_estimDuration = '0 SECONDS'::INTERVAL; v_estimMethod = 3; ELSE -- non empty table and statistics (with at least one row) are available SELECT sum(rlbt_duration) * r_fk.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'ADD_FK' AND rlbt_quantity > 0 AND rlbt_schema = r_fk.rlbp_schema AND rlbt_table = r_fk.rlbp_table AND rlbt_object = r_fk.rlbp_object; v_estimMethod = 1; IF v_estimDuration IS NULL THEN -- non empty table, but no statistics with at least one row are available => take the last duration for this fkey, if any SELECT rlbt_duration INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'ADD_FK' AND rlbt_schema = r_fk.rlbp_schema AND rlbt_table = r_tbl.rlbp_table AND rlbt_object = r_fk.rlbp_object AND rlbt_rlbk_id = (SELECT max(rlbt_rlbk_id) FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'ADD_FK' AND rlbt_schema = r_fk.rlbp_schema AND rlbt_table = r_fk.rlbp_table AND rlbt_object = r_fk.rlbp_object); v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- definitely no statistics available, compute with the avg_fkey_check_duration parameter v_estimDuration = r_fk.rlbp_estimated_quantity * v_avg_fkey_check + v_fixed_step_rlbk; v_estimMethod = 3; END IF; END IF; END IF; UPDATE emaj.emaj_rlbk_plan SET rlbp_estimated_duration = v_estimDuration, rlbp_estimate_method = v_estimMethod WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'ADD_FK' AND rlbp_schema = r_fk.rlbp_schema AND rlbp_table = r_fk.rlbp_table AND rlbp_object = r_fk.rlbp_object; END LOOP; -- Compute the fkey checks duration FOR r_fk IN SELECT * FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'SET_FK_IMM' LOOP -- if fkey checks statistics are available for this fkey, compute an average cost SELECT sum(rlbt_duration) * r_fk.rlbp_estimated_quantity / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = 'SET_FK_IMM' AND rlbt_quantity > 0 AND rlbt_schema = r_fk.rlbp_schema AND rlbt_table = r_fk.rlbp_table AND rlbt_object = r_fk.rlbp_object; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- if no statistics are available for this fkey, use the avg_fkey_check parameter v_estimDuration = r_fk.rlbp_estimated_quantity * v_avg_fkey_check + v_fixed_step_rlbk; v_estimMethod = 3; END IF; UPDATE emaj.emaj_rlbk_plan SET rlbp_estimated_duration = v_estimDuration, rlbp_estimate_method = v_estimMethod WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'SET_FK_IMM' AND rlbp_schema = r_fk.rlbp_schema AND rlbp_table = r_fk.rlbp_table AND rlbp_object = r_fk.rlbp_object; END LOOP; -- -- Allocate batch number to sessions to spread the load on sessions as best as possible -- A batch represents all steps related to the processing of one table or several tables linked by foreign keys -- -- initialisation FOR v_session IN 1 .. v_nbSession LOOP v_sessionLoad [v_session] = '0 SECONDS'::INTERVAL; END LOOP; -- allocate tables batch to sessions, starting with the heaviest to rollback batch FOR r_batch IN SELECT rlbp_batch_number, sum(rlbp_estimated_duration) AS batch_duration FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_batch_number IS NOT NULL GROUP BY rlbp_batch_number ORDER BY sum(rlbp_estimated_duration) DESC LOOP -- compute the least loaded session v_minSession=1; v_minDuration = v_sessionLoad [1]; FOR v_session IN 2 .. v_nbSession LOOP IF v_sessionLoad [v_session] < v_minDuration THEN v_minSession = v_session; v_minDuration = v_sessionLoad [v_session]; END IF; END LOOP; -- allocate the batch to the session UPDATE emaj.emaj_rlbk_plan SET rlbp_session = v_minSession WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_batch_number = r_batch.rlbp_batch_number; v_sessionLoad [v_minSession] = v_sessionLoad [v_minSession] + r_batch.batch_duration; END LOOP; -- assign session 1 to all 'LOCK_TABLE' steps not yet affected UPDATE emaj.emaj_rlbk_plan SET rlbp_session = 1 WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_session IS NULL; -- Create the pseudo 'CTRL+DBLINK' or 'CTRL-DBLINK' step and compute its duration estimate -- get the number of recorded steps (except LOCK_TABLE) SELECT count(*) INTO v_nbStep FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step <> 'LOCK_TABLE'; IF v_nbStep > 0 THEN -- if CTRLxDBLINK statistics are available, compute an average cost SELECT sum(rlbt_duration) * v_nbStep / sum(rlbt_quantity) INTO v_estimDuration FROM emaj.emaj_rlbk_stat WHERE rlbt_step = v_ctrlStepName AND rlbt_quantity > 0; v_estimMethod = 2; IF v_estimDuration IS NULL THEN -- otherwise, use the fixed_step_rollback_duration parameter v_estimDuration = v_fixed_dblink_rlbk * v_nbStep; v_estimMethod = 3; END IF; -- insert the 'CTRLxDBLINK' pseudo step INSERT INTO emaj.emaj_rlbk_plan ( rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_object, rlbp_estimated_quantity, rlbp_estimated_duration, rlbp_estimate_method ) VALUES ( v_rlbkId, v_ctrlStepName, '', '', '', v_nbStep, v_estimDuration, v_estimMethod ); END IF; -- return the number of tables to effectively rollback RETURN v_effNbTable; END; $_rlbk_planning$; CREATE OR REPLACE FUNCTION emaj._rlbk_set_batch_number(v_rlbkId INT, v_batchNumber INT, v_schema TEXT, v_table TEXT) RETURNS VOID LANGUAGE plpgsql AS $_rlbk_set_batch_number$ -- This function updates the emaj_rlbk_plan table to set the batch_number for one table. -- It also looks for all tables that are linked to this table by foreign keys to force them to be allocated to the same batch number. -- The function is called by _rlbk_planning(). -- As those linked tables can also be linked to other tables by other foreign keys, the function has to be recursiley called. DECLARE v_fullTableName TEXT; r_tbl RECORD; BEGIN -- set the batch number to this application table (there is a 'LOCK_TABLE' step and potentialy a 'RLBK_TABLE' step) UPDATE emaj.emaj_rlbk_plan SET rlbp_batch_number = v_batchNumber WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_schema = v_schema AND rlbp_table = v_table; -- then look for all other application tables linked by foreign key relationships v_fullTableName = quote_ident(v_schema) || '.' || quote_ident(v_table); FOR r_tbl IN SELECT rlbp_schema, rlbp_table FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'LOCK_TABLE' AND rlbp_batch_number IS NULL -- not yet allocated AND (rlbp_schema, rlbp_table) IN ( -- list of (schema,table) linked to the original table by fkeys SELECT nspname, relname FROM pg_catalog.pg_constraint, pg_catalog.pg_class t, pg_catalog.pg_namespace n WHERE contype = 'f' AND confrelid = v_fullTableName::regclass AND t.oid = conrelid AND relnamespace = n.oid UNION SELECT nspname, relname FROM pg_catalog.pg_constraint, pg_catalog.pg_class t, pg_catalog.pg_namespace n WHERE contype = 'f' AND conrelid = v_fullTableName::regclass AND t.oid = confrelid AND relnamespace = n.oid ) LOOP -- recursive call to allocate these linked tables to the same batch_number PERFORM emaj._rlbk_set_batch_number(v_rlbkId, v_batchNumber, r_tbl.rlbp_schema, r_tbl.rlbp_table); END LOOP; RETURN; END; $_rlbk_set_batch_number$; 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_isDblinkUsed BOOLEAN; v_dblinkSchema TEXT; v_dbLinkCnxStatus INT; v_stmt TEXT; v_groupNames TEXT[]; v_nbRetry SMALLINT = 0; v_ok BOOLEAN = FALSE; v_nbTbl INT; r_tbl RECORD; BEGIN -- get the rollback characteristics from the emaj_rlbk table SELECT rlbk_is_dblink_used, rlbk_dblink_schema, rlbk_groups INTO v_isDblinkUsed, v_dblinkSchema, v_groupNames FROM emaj.emaj_rlbk WHERE rlbk_id = v_rlbkId; -- for dblink session > 1, open the connection (the session 1 is already opened) IF v_session > 1 THEN SELECT v_status INTO v_dbLinkCnxStatus FROM emaj._dblink_open_cnx('rlbk#'||v_session); IF v_dbLinkCnxStatus < 0 THEN RAISE EXCEPTION '_rlbk_session_lock: Error while opening the dblink session #% (Status of the dblink connection attempt = %' ' - see E-Maj documentation).', v_session, v_dbLinkCnxStatus; END IF; END IF; -- 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'; PERFORM emaj._dblink_sql_exec('rlbk#'||v_session, v_stmt, v_dblinkSchema); -- 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 format('LOCK TABLE %s IN EXCLUSIVE MODE', r_tbl.fullName); 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_start_mark(v_rlbkId INT, v_multiGroup BOOLEAN) RETURNS VOID LANGUAGE plpgsql AS $_rlbk_start_mark$ -- For logged rollback, it sets a mark that materialize the point in time just before the tables rollback. -- All concerned tables are already locked. -- Before setting the mark, it checks no update has been recorded between the planning step and the locks set -- for tables for which no rollback was needed at planning time. -- It also sets the rollback status to EXECUTING. DECLARE v_isDblinkUsed BOOLEAN; v_dblinkSchema TEXT; v_stmt TEXT; v_groupNames TEXT[]; v_mark TEXT; v_timeId BIGINT; v_isLoggedRlbk BOOLEAN; v_rlbkDatetime TIMESTAMPTZ; v_markTimeId BIGINT; v_markName TEXT; v_errorMsg TEXT; BEGIN -- get the dblink usage characteristics for the current rollback SELECT rlbk_is_dblink_used, rlbk_dblink_schema INTO v_isDblinkUsed, v_dblinkSchema FROM emaj.emaj_rlbk WHERE rlbk_id = v_rlbkId; -- get a time stamp for the rollback operation v_stmt = 'SELECT emaj._set_time_stamp(''R'')'; SELECT emaj._dblink_sql_exec('rlbk#1', v_stmt, v_dblinkSchema) INTO v_timeId; -- update the emaj_rlbk table to record the time stamp and adjust the rollback status v_stmt = 'UPDATE emaj.emaj_rlbk SET rlbk_time_id = ' || v_timeId || ', rlbk_status = ''EXECUTING''' || ' WHERE rlbk_id = ' || v_rlbkId || ' RETURNING 1'; PERFORM emaj._dblink_sql_exec('rlbk#1', v_stmt, v_dblinkSchema); -- get the rollback characteristics from the emaj_rlbk table SELECT rlbk_groups, rlbk_mark, rlbk_time_id, rlbk_is_logged, time_clock_timestamp INTO v_groupNames, v_mark, v_timeId, v_isLoggedRlbk, v_rlbkDatetime FROM emaj.emaj_rlbk, emaj.emaj_time_stamp WHERE rlbk_time_id = time_id AND rlbk_id = v_rlbkId; -- get some mark attributes from emaj_mark SELECT mark_time_id INTO v_markTimeId FROM emaj.emaj_mark WHERE mark_group = v_groupNames[1] AND mark_name = v_mark; -- check that no update has been recorded between planning time and lock time for tables that did not need to -- be rolled back at planning time. -- This may occur and cannot be avoided because tables cannot be locked before processing the rollback planning. -- (Sessions must lock the tables they will rollback and the planning processing distribute those tables to sessions.) PERFORM 1 FROM (SELECT * FROM emaj.emaj_relation WHERE upper_inf(rel_time_range) AND rel_group = ANY (v_groupNames) AND rel_kind = 'r' AND NOT EXISTS (SELECT NULL FROM emaj.emaj_rlbk_plan WHERE rlbp_schema = rel_schema AND rlbp_table = rel_tblseq AND rlbp_rlbk_id = v_rlbkId AND rlbp_step = 'RLBK_TABLE') ) AS t WHERE emaj._log_stat_tbl(t, greatest(v_markTimeId, lower(rel_time_range)), NULL) > 0; IF FOUND THEN v_errorMsg = 'the rollback operation has been cancelled due to concurrent activity at E-Maj rollback planning time on tables' || ' to process.'; PERFORM emaj._rlbk_error(v_rlbkId, v_errorMsg, 'rlbk#1'); RAISE EXCEPTION '_rlbk_start_mark: % Please retry.', v_errorMsg; END IF; IF v_isLoggedRlbk THEN -- If rollback is "logged" rollback, set a mark named with the pattern: -- 'RLBK__%_START', where % represents the rollback start time v_markName = 'RLBK_' || v_mark || '_' || substring(to_char(v_rlbkDatetime, 'HH24.MI.SS.US') from 1 for 13) || '_START'; PERFORM emaj._set_mark_groups(v_groupNames, v_markName, v_multiGroup, TRUE, NULL, v_timeId, v_dblinkSchema); 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_start_mark(): ' || SQLERRM, 'rlbk#1'); RAISE; END; $_rlbk_start_mark$; CREATE OR REPLACE FUNCTION emaj._rlbk_session_exec(v_rlbkId INT, v_session INT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp 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_dblinkSchema TEXT; v_isDblinkUsed BOOLEAN; 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 -- get the rollback characteristics from the emaj_rlbk table SELECT rlbk_groups, rlbk_mark, rlbk_time_id, rlbk_is_logged, rlbk_nb_session, rlbk_dblink_schema, rlbk_is_dblink_used, time_last_emaj_gid INTO v_groupNames, v_mark, v_rlbkTimeId, v_isLoggedRlbk, v_nbSession, v_dblinkSchema, v_isDblinkUsed, 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_object, rlbp_object_def, rlbp_target_time_id FROM emaj.emaj_rlbk_plan, (VALUES ('DIS_APP_TRG',1),('DIS_LOG_TRG',2),('DROP_FK',3),('SET_FK_DEF',4), ('RLBK_TABLE',5),('DELETE_LOG',6),('SET_FK_IMM',7),('ADD_FK',8), ('ENA_APP_TRG',9),('ENA_LOG_TRG',10)) 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_object 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_object = ' || quote_literal(r_step.rlbp_object) || ' RETURNING 1'; PERFORM emaj._dblink_sql_exec('rlbk#'||v_session, v_stmt, v_dblinkSchema); -- process the step depending on its type CASE r_step.rlbp_step WHEN 'DIS_APP_TRG' THEN -- process an application trigger disable EXECUTE format('ALTER TABLE %I.%I DISABLE TRIGGER %I', r_step.rlbp_schema, r_step.rlbp_table, r_step.rlbp_object); WHEN 'DIS_LOG_TRG' THEN -- process a log trigger disable EXECUTE format('ALTER TABLE %I.%I DISABLE TRIGGER emaj_log_trg', r_step.rlbp_schema, r_step.rlbp_table); WHEN 'DROP_FK' THEN -- process a foreign key deletion EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I', r_step.rlbp_schema, r_step.rlbp_table, r_step.rlbp_object); WHEN 'SET_FK_DEF' THEN -- set a foreign key deferred EXECUTE format('SET CONSTRAINTS %I.%I DEFERRED', r_step.rlbp_schema, r_step.rlbp_object); 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 format('SET CONSTRAINTS %I.%I IMMEDIATE', r_step.rlbp_schema, r_step.rlbp_object); WHEN 'ADD_FK' THEN -- process a foreign key creation EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I %s', r_step.rlbp_schema, r_step.rlbp_table, r_step.rlbp_object, r_step.rlbp_object_def); WHEN 'ENA_APP_TRG' THEN -- process an application trigger enable EXECUTE format('ALTER TABLE %I.%I ENABLE %s TRIGGER %I', r_step.rlbp_schema, r_step.rlbp_table, r_step.rlbp_object_def, r_step.rlbp_object); WHEN 'ENA_LOG_TRG' THEN -- process a log trigger enable EXECUTE format('ALTER TABLE %I.%I ENABLE TRIGGER emaj_log_trg', r_step.rlbp_schema, r_step.rlbp_table); 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_object = ' || quote_literal(r_step.rlbp_object) || ' RETURNING 1'; PERFORM emaj._dblink_sql_exec('rlbk#'||v_session, v_stmt, v_dblinkSchema); 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'; PERFORM emaj._dblink_sql_exec('rlbk#'||v_session, v_stmt, v_dblinkSchema); -- close the dblink connection, if any, for session > 1 IF v_isDblinkUsed AND v_session > 1 THEN PERFORM emaj._dblink_close_cnx('rlbk#'||v_session, v_dblinkSchema); 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_dblinkSchema TEXT; v_isDblinkUsed BOOLEAN; 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 -- get the rollback characteristics from the emaj_rlbk table SELECT rlbk_groups, rlbk_mark, rlbk_is_logged, rlbk_is_alter_group_allowed, rlbk_nb_table, rlbk_eff_nb_table, rlbk_dblink_schema, rlbk_is_dblink_used, time_clock_timestamp INTO v_groupNames, v_mark, v_isLoggedRlbk, v_isAlterGroupAllowed, v_nbTbl, v_effNbTbl, v_dblinkSchema, v_isDblinkUsed, 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 sequ_time_id <@ rel_time_range AND sequ_time_id <> lower(rel_time_range); 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'; PERFORM emaj._dblink_sql_exec('rlbk#1', v_stmt, v_dblinkSchema); -- 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_object,' || ' 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_object, rlbp_rlbk_id,' || ' rlbp_estimated_quantity, rlbp_duration' || ' FROM emaj.emaj_rlbk_plan' || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''RLBK_TABLE'',''DELETE_LOG'',''ADD_FK'',''SET_FK_IMM'') ' || ' UNION ALL ' || -- for 6 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' || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''DIS_APP_TRG'',''DIS_LOG_TRG'',''DROP_FK'',''SET_FK_DEF'',''ENA_APP_TRG'',''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' || ' WHERE rlbp_rlbk_id = ' || v_rlbkId || ' AND rlbp_step IN (''CTRL+DBLINK'',''CTRL-DBLINK'') ' || ' RETURNING 1'; PERFORM emaj._dblink_sql_exec('rlbk#1', v_stmt, v_dblinkSchema); -- 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_schema, rel_tblseq) as t; GET DIAGNOSTICS v_nbSeq = ROW_COUNT; -- if rollback is "logged" rollback, automatically 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 || '_' || substring(to_char(v_rlbkDatetime, 'HH24.MI.SS.US') from 1 for 13) || '_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 v_stmt = 'UPDATE emaj.emaj_rlbk SET rlbk_status = ''' || CASE WHEN v_isDblinkUsed THEN 'COMPLETED' ELSE 'COMMITTED' END || ''', rlbk_end_datetime = clock_timestamp(), rlbk_messages = ARRAY[' || v_messages || ']' || ' WHERE rlbk_id = ' || v_rlbkId || ' RETURNING 1'; PERFORM emaj._dblink_sql_exec('rlbk#1', v_stmt, v_dblinkSchema); -- close the dblink connection, if any IF v_isDblinkUsed THEN PERFORM emaj._dblink_close_cnx('rlbk#1', v_dblinkSchema); 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._cleanup_rollback_state() RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_cleanup_rollback_state$ -- This function effectively cleans the rollback states up. It is called by the emaj_cleanup_rollback_state() -- and by other emaj functions. -- The rollbacks whose transaction(s) is/are active are left as is. -- Among the others, those which are also visible in the emaj_hist table are set "COMMITTED", -- while those which are not visible in the emaj_hist table are set "ABORTED". -- Input: no parameter -- Output: number of updated rollback events DECLARE v_nbRlbk INT = 0; v_newStatus emaj._rlbk_status_enum; r_rlbk RECORD; BEGIN -- scan all pending rollback events having all their session transactions completed (either committed or rolled back) FOR r_rlbk IN SELECT rlbk_id, rlbk_status, rlbk_begin_hist_id, rlbk_nb_session, count(rlbs_txid) AS nbVisibleTx FROM emaj.emaj_rlbk LEFT OUTER JOIN emaj.emaj_rlbk_session ON ( rlbk_id = rlbs_rlbk_id -- main join condition AND txid_visible_in_snapshot(rlbs_txid,txid_current_snapshot()) -- only visible tx AND rlbs_txid <> txid_current() -- exclude the current tx ) WHERE rlbk_status IN ('PLANNING', 'LOCKING', 'EXECUTING', 'COMPLETED') -- only pending rollback events GROUP BY rlbk_id, rlbk_status, rlbk_begin_hist_id, rlbk_nb_session HAVING count(rlbs_txid) = rlbk_nb_session -- all sessions tx must be visible ORDER BY rlbk_id LOOP -- look at the emaj_hist to find the trace of the rollback begin event PERFORM 0 FROM emaj.emaj_hist WHERE hist_id = r_rlbk.rlbk_begin_hist_id; IF FOUND THEN -- if the emaj_hist rollback_begin event is visible, the rollback transaction has been committed. -- then set the rollback event in emaj_rlbk as "COMMITTED" v_newStatus = 'COMMITTED'; ELSE -- otherwise, set the rollback event in emaj_rlbk as "ABORTED" v_newStatus = 'ABORTED'; END IF; UPDATE emaj.emaj_rlbk SET rlbk_status = v_newStatus WHERE rlbk_id = r_rlbk.rlbk_id; INSERT INTO emaj.emaj_hist (hist_function, hist_object, hist_wording) VALUES ('CLEANUP_RLBK_STATE', 'Rollback id ' || r_rlbk.rlbk_id, 'set to ' || v_newStatus); v_nbRlbk = v_nbRlbk + 1; END LOOP; RETURN v_nbRlbk; END; $_cleanup_rollback_state$; 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 format('DELETE FROM %s WHERE emaj_gid > $1 AND emaj_gid <= $2', r_rel.log_table_name) USING v_firstMarkGlobalSeq, 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 sequ_time_id <@ rel_time_range AND sequ_time_id <> lower(rel_time_range); -- 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._reset_groups(v_groupNames TEXT[]) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $_reset_groups$ -- This function empties the log tables for all tables of a group, using a TRUNCATE, and deletes the sequences images. -- It is called by emaj_reset_group(), emaj_start_group() and emaj_alter_group() functions. -- Input: group names array -- Output: number of processed tables and sequences -- There is no check of the groups state (this is done by callers). -- The function is defined as SECURITY DEFINER so that an emaj_adm role can truncate log tables. DECLARE v_eventTriggers TEXT[]; 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; -- delete all marks for the groups from the emaj_mark table DELETE FROM emaj.emaj_mark WHERE mark_group = ANY (v_groupNames); -- delete emaj_sequence rows related to the tables of the groups DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE sequ_schema = rel_log_schema AND sequ_name = rel_log_sequence AND rel_group = ANY (v_groupNames) AND rel_kind = 'r' AND sequ_time_id <@ rel_time_range AND sequ_time_id <> lower(rel_time_range); -- delete all sequence holes for the tables of the groups -- (it may delete holes for timeranges that do not belong to the group, if a table has been moved to another group, -- but is safe enough for rollbacks) DELETE FROM emaj.emaj_seq_hole USING emaj.emaj_relation WHERE rel_schema = sqhl_schema AND rel_tblseq = sqhl_table AND rel_group = ANY (v_groupNames) AND rel_kind = 'r'; -- delete emaj_sequence rows related to the sequences of the groups DELETE FROM emaj.emaj_sequence USING emaj.emaj_relation WHERE rel_schema = sequ_schema AND rel_tblseq = sequ_name AND rel_group = ANY (v_groupNames) AND rel_kind = 'S'; -- drop obsolete log tables (but keep those linked to other groups) FOR r_rel IN SELECT DISTINCT rel_log_schema, rel_log_table FROM emaj.emaj_relation WHERE rel_group = ANY (v_groupNames) AND rel_kind = 'r' AND NOT upper_inf(rel_time_range) EXCEPT SELECT rel_log_schema, rel_log_table FROM emaj.emaj_relation WHERE rel_kind = 'r' AND (upper_inf(rel_time_range) OR NOT rel_group = ANY (v_groupNames)) ORDER BY 1,2 LOOP EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r_rel.rel_log_schema, r_rel.rel_log_table); END LOOP; -- keep a trace of the relation group ownership history -- and finaly delete the old versions of emaj_relation rows (those with a not infinity upper bound) WITH deleted AS ( DELETE FROM emaj.emaj_relation WHERE rel_group = ANY (v_groupNames) AND NOT upper_inf(rel_time_range) RETURNING rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind ) INSERT INTO emaj.emaj_rel_hist (relh_schema, relh_tblseq, relh_time_range, relh_group, relh_kind) SELECT rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind FROM deleted; -- truncate remaining log tables for application tables FOR r_rel IN SELECT rel_log_schema, rel_log_table, rel_log_sequence FROM emaj.emaj_relation WHERE rel_group = ANY (v_groupNames) AND rel_kind = 'r' ORDER BY rel_priority, rel_schema, rel_tblseq LOOP -- truncate the log table EXECUTE format('TRUNCATE %I.%I', r_rel.rel_log_schema, r_rel.rel_log_table); END LOOP; -- enable previously disabled event triggers PERFORM emaj._enable_event_triggers(v_eventTriggers); RETURN sum(group_nb_table)+sum(group_nb_sequence) FROM emaj.emaj_group WHERE group_name = ANY (v_groupNames); END; $_reset_groups$; CREATE OR REPLACE FUNCTION emaj._detailed_log_stat_groups(v_groupNames TEXT[], v_multiGroup BOOLEAN, v_firstMark TEXT, v_lastMark TEXT) RETURNS SETOF emaj._detailed_log_stat_type LANGUAGE plpgsql AS $_detailed_log_stat_groups$ -- This function effectively returns statistics on row updates executed between 2 marks as viewed through the log tables for one or several -- groups. -- It provides more information than emaj_log_stat_group but it needs to scan log tables in order to provide these data. -- So the response time may be much longer. -- The function is directly called by Emaj_web. -- Input: groups name array, a boolean indicating whether the calling function is a multi_groups function, -- the 2 mark names defining a range -- a NULL value or an empty string as first_mark indicates the first recorded mark -- a NULL value or an empty string as last_mark indicates the current situation -- The keyword 'EMAJ_LAST_MARK' can be used as first or last mark to specify the last set mark. -- Output: table of updates by user and table -- This function may be directly called by the Emaj_web client. DECLARE v_firstMarkTimeId BIGINT; v_lastMarkTimeId BIGINT; v_firstMarkTs TIMESTAMPTZ; v_lastMarkTs TIMESTAMPTZ; v_firstEmajGid BIGINT; v_lastEmajGid BIGINT; v_lowerBoundMark TEXT; v_lowerBoundMarkTs TIMESTAMPTZ; v_lowerBoundGid BIGINT; v_upperBoundMark TEXT; v_upperBoundMarkTs TIMESTAMPTZ; v_upperBoundGid BIGINT; v_stmt TEXT; r_tblsq RECORD; r_stat RECORD; BEGIN -- check the group name PERFORM emaj._check_group_names(v_groupNames := v_groupNames, v_mayBeNull := v_multiGroup, v_lockGroups := FALSE, v_checkList := ''); IF v_groupNames IS NOT NULL THEN -- check the marks range SELECT * FROM emaj._check_marks_range(v_groupNames, v_firstMark, v_lastMark) INTO v_firstMark, v_lastMark, v_firstMarkTimeId, v_lastMarkTimeId; -- if there is no first mark, return quickly IF v_firstMark IS NULL THEN RETURN; END IF; -- get additional data for both mark timestamps SELECT time_last_emaj_gid, time_clock_timestamp INTO v_firstEmajGid, v_firstMarkTs FROM emaj.emaj_time_stamp WHERE time_id = v_firstMarkTimeId; IF v_lastMark IS NOT NULL AND v_lastMark <> '' THEN SELECT time_last_emaj_gid, time_clock_timestamp INTO v_lastEmajGid, v_lastMarkTs FROM emaj.emaj_time_stamp WHERE time_id = v_lastMarkTimeId; END IF; -- for each table currently belonging to the group -- count the number of operations per type (INSERT, UPDATE and DELETE) and role FOR r_tblsq IN SELECT rel_priority, rel_schema, rel_tblseq, rel_group, rel_time_range, rel_log_schema, rel_log_table FROM emaj.emaj_relation WHERE rel_group = ANY(v_groupNames) AND rel_kind = 'r' -- tables belonging to the groups AND (upper_inf(rel_time_range) OR upper(rel_time_range) > v_firstMarkTimeId) -- at the requested time frame AND (v_lastMarkTimeId IS NULL OR lower(rel_time_range) < v_lastMarkTimeId) ORDER BY rel_schema, rel_tblseq, rel_time_range LOOP -- compute the lower bound for this table IF v_firstMarkTimeId >= lower(r_tblsq.rel_time_range) THEN -- usual case: the table belonged to the group at statistics start mark v_lowerBoundMark = v_firstMark; v_lowerBoundMarkTs = v_firstMarkTs; v_lowerBoundGid = v_firstEmajGid; ELSE -- special case: the table has been added to the group after the statistics start mark SELECT mark_name INTO v_lowerBoundMark FROM emaj.emaj_mark WHERE mark_time_id = lower(r_tblsq.rel_time_range) AND mark_group = r_tblsq.rel_group; IF v_lowerBoundMark IS NULL THEN -- the mark set at alter_group time may have been deleted v_lowerBoundMark = '[deleted mark]'; END IF; SELECT time_clock_timestamp, time_last_emaj_gid INTO v_lowerBoundMarkTs, v_lowerBoundGid FROM emaj.emaj_time_stamp WHERE time_id = lower(r_tblsq.rel_time_range); END IF; -- compute the upper bound for this table IF v_lastMarkTimeId IS NULL AND upper_inf(r_tblsq.rel_time_range) THEN -- no supplied end mark and the table has not been removed from its group => the current situation v_upperBoundMark = NULL; v_upperBoundMarkTs = NULL; v_upperBoundGid = NULL; ELSIF NOT upper_inf(r_tblsq.rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(r_tblsq.rel_time_range) < v_lastMarkTimeId) THEN -- special case: the table has been removed from its group before the statistics end mark SELECT mark_name INTO v_upperBoundMark FROM emaj.emaj_mark WHERE mark_time_id = upper(r_tblsq.rel_time_range) AND mark_group = r_tblsq.rel_group; IF v_upperBoundMark IS NULL THEN -- the mark set at alter_group time may have been deleted v_upperBoundMark = '[deleted mark]'; END IF; SELECT time_clock_timestamp, time_last_emaj_gid INTO v_upperBoundMarkTs, v_upperBoundGid FROM emaj.emaj_time_stamp WHERE time_id = upper(r_tblsq.rel_time_range); ELSE -- usual case: the table belonged to the group at statistics end mark v_upperBoundMark = v_lastMark; v_upperBoundMarkTs = v_lastMarkTs; v_upperBoundGid = v_lastEmajGid; END IF; -- build the statement v_stmt= 'SELECT ' || quote_literal(r_tblsq.rel_group) || '::TEXT AS stat_group, ' || quote_literal(r_tblsq.rel_schema) || '::TEXT AS stat_schema, ' || quote_literal(r_tblsq.rel_tblseq) || '::TEXT AS stat_table, ' || quote_literal(r_tblsq.rel_log_schema) || '::TEXT AS stat_log_schema, ' || quote_literal(r_tblsq.rel_log_table) || '::TEXT AS stat_log_table, ' || quote_literal(v_lowerBoundMark) || '::TEXT AS stat_first_mark, ' || quote_literal(v_lowerBoundMarkTs) || '::TIMESTAMPTZ AS stat_first_mark_datetime, ' || v_lowerBoundGid || '::BIGINT AS stat_first_mark_gid, ' || coalesce(quote_literal(v_upperBoundMark),'NULL') || '::TEXT AS stat_last_mark, ' || coalesce(quote_literal(v_upperBoundMarkTs),'NULL') || '::TIMESTAMPTZ AS stat_last_mark_datetime, ' || coalesce(v_upperBoundGid::text,'NULL') || '::BIGINT AS stat_last_mark_gid, ' || ' emaj_user AS stat_user,' || ' CASE emaj_verb WHEN ''INS'' THEN ''INSERT''' || ' WHEN ''UPD'' THEN ''UPDATE''' || ' WHEN ''DEL'' THEN ''DELETE''' || ' ELSE ''?'' END::VARCHAR(6) AS stat_verb,' || ' count(*) AS stat_rows' || ' FROM ' || quote_ident(r_tblsq.rel_log_schema) || '.' || quote_ident(r_tblsq.rel_log_table) || ' WHERE NOT (emaj_verb = ''UPD'' AND emaj_tuple = ''OLD'')' || ' AND emaj_gid > '|| v_lowerBoundGid || coalesce(' AND emaj_gid <= '|| v_upperBoundGid, '') || ' GROUP BY stat_group, stat_schema, stat_table, stat_user, stat_verb' || ' ORDER BY stat_user, stat_verb'; -- and execute the statement FOR r_stat IN EXECUTE v_stmt LOOP RETURN NEXT r_stat; END LOOP; END LOOP; END IF; RETURN; END; $_detailed_log_stat_groups$; CREATE OR REPLACE FUNCTION emaj._rollback_activity() RETURNS SETOF emaj.emaj_rollback_activity_type LANGUAGE plpgsql AS $_rollback_activity$ -- This function effectively builds the list of rollback operations currently in execution. -- It is called by the emaj_rollback_activity() function. -- This is a separate function to help in testing the feature (avoiding the effects of _cleanup_rollback_state()). -- The number of parallel rollback sessions is not taken into account here, -- as it is difficult to estimate the benefit brought by several parallel sessions. -- The times and progression indicators reported are based on the transaction timestamp (allowing stable results in regression tests). DECLARE v_ipsDuration INTERVAL; -- In Progress Steps Duration v_nyssDuration INTERVAL; -- Not Yes Started Steps Duration v_nbNyss INT; -- Number of Net Yes Started Steps v_ctrlDuration INTERVAL; v_currentTotalEstimate INTERVAL; r_rlbk emaj.emaj_rollback_activity_type; BEGIN -- retrieve all not completed rollback operations (ie in 'PLANNING', 'LOCKING' or 'EXECUTING' state) FOR r_rlbk IN SELECT rlbk_id, rlbk_groups, rlbk_mark, t1.time_clock_timestamp, rlbk_is_logged, rlbk_is_alter_group_allowed, rlbk_nb_session, rlbk_nb_table, rlbk_nb_sequence, rlbk_eff_nb_table, rlbk_status, t2.time_tx_timestamp, transaction_timestamp() - t2.time_tx_timestamp AS "elapse", NULL, 0 FROM emaj.emaj_rlbk JOIN emaj.emaj_time_stamp t1 ON (rlbk_mark_time_id = t1.time_id) LEFT OUTER JOIN emaj.emaj_time_stamp t2 ON (rlbk_time_id = t2.time_id) WHERE rlbk_status IN ('PLANNING', 'LOCKING', 'EXECUTING') ORDER BY rlbk_id LOOP -- compute the estimated remaining duration -- for rollback operations in 'PLANNING' state, the remaining duration is NULL IF r_rlbk.rlbk_status IN ('LOCKING', 'EXECUTING') THEN -- estimated duration of remaining work of in progress steps SELECT coalesce( sum(CASE WHEN rlbp_start_datetime + rlbp_estimated_duration - transaction_timestamp() > '0'::INTERVAL THEN rlbp_start_datetime + rlbp_estimated_duration - transaction_timestamp() ELSE '0'::INTERVAL END),'0'::INTERVAL) INTO v_ipsDuration FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = r_rlbk.rlbk_id AND rlbp_start_datetime IS NOT NULL AND rlbp_duration IS NULL; -- estimated duration and number of not yet started steps SELECT coalesce(sum(rlbp_estimated_duration),'0'::INTERVAL), count(*) INTO v_nyssDuration, v_nbNyss FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = r_rlbk.rlbk_id AND rlbp_start_datetime IS NULL AND rlbp_step NOT IN ('CTRL-DBLINK','CTRL+DBLINK'); -- estimated duration of inter-step duration for not yet started steps SELECT coalesce(sum(rlbp_estimated_duration) * v_nbNyss / sum(rlbp_estimated_quantity),'0'::INTERVAL) INTO v_ctrlDuration FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id = r_rlbk.rlbk_id AND rlbp_step IN ('CTRL-DBLINK','CTRL+DBLINK'); -- update the global remaining duration estimate r_rlbk.rlbk_remaining = v_ipsDuration + v_nyssDuration + v_ctrlDuration; END IF; -- compute the completion pct -- for rollback operations in 'PLANNING' or 'LOCKING' state, the completion_pct = 0 IF r_rlbk.rlbk_status = 'EXECUTING' THEN -- first compute the new total duration estimate, using the estimate of the remaining work SELECT transaction_timestamp() - time_tx_timestamp + r_rlbk.rlbk_remaining INTO v_currentTotalEstimate FROM emaj.emaj_rlbk, emaj.emaj_time_stamp WHERE rlbk_time_id = time_id AND rlbk_id = r_rlbk.rlbk_id; -- and then the completion pct IF v_currentTotalEstimate <> '0'::INTERVAL THEN SELECT 100 - (extract(epoch FROM r_rlbk.rlbk_remaining) * 100 / extract(epoch FROM v_currentTotalEstimate))::SMALLINT INTO r_rlbk.rlbk_completion_pct; END IF; END IF; RETURN NEXT r_rlbk; END LOOP; RETURN; END; $_rollback_activity$; CREATE OR REPLACE FUNCTION emaj.emaj_snap_group(v_groupName TEXT, v_dir TEXT, v_copyOptions TEXT) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $emaj_snap_group$ -- This function creates a file for each table and sequence belonging to the group. -- For tables, these files contain all rows sorted on primary key. -- For sequences, they contain a single row describing the sequence. -- To do its job, the function performs COPY TO statement, with all default parameters. -- For table without primary key, rows are sorted on all columns. -- There is no need for the group not to be logging. -- As all COPY statements are executed inside a single transaction: -- - the function can be called while other transactions are running, -- - the snap files will present a coherent state of tables. -- It's users responsability: -- - to create the directory (with proper permissions allowing the cluster to write into) before the emaj_snap_group function call, and -- - maintain its content outside E-maj. -- Input: group name, -- the absolute pathname of the directory where the files are to be created and the options to used in the COPY TO statements -- Output: number of processed tables and sequences -- The function is defined as SECURITY DEFINER so that emaj_adm role can use. DECLARE v_nbTb INT = 0; r_tblsq RECORD; v_fullTableName TEXT; v_colList TEXT; v_fileName TEXT; v_stmt TEXT; BEGIN -- insert begin in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('SNAP_GROUP', 'BEGIN', v_groupName, v_dir); -- check the group name PERFORM emaj._check_group_names(v_groupNames := ARRAY[v_groupName], v_mayBeNull := FALSE, v_lockGroups := FALSE, v_checkList := ''); -- check the supplied directory is not null IF v_dir IS NULL THEN RAISE EXCEPTION 'emaj_snap_group: The directory parameter cannot be NULL.'; END IF; -- check the copy options parameter doesn't contain unquoted ; that could be used for sql injection IF regexp_replace(v_copyOptions,'''.*''','') LIKE '%;%' THEN RAISE EXCEPTION 'emaj_snap_group: The COPY options parameter format is invalid.'; END IF; -- for each table/sequence of the emaj_relation table 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 = v_groupName ORDER BY rel_priority, rel_schema, rel_tblseq LOOP v_fileName = v_dir || '/' || translate(r_tblsq.rel_schema || '_' || r_tblsq.rel_tblseq || '.snap', E' /\\$<>*', '_______'); v_fullTableName = quote_ident(r_tblsq.rel_schema) || '.' || quote_ident(r_tblsq.rel_tblseq); CASE r_tblsq.rel_kind WHEN 'r' THEN -- if it is a table, -- first build the order by column list PERFORM 0 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 = r_tblsq.rel_schema AND relname = r_tblsq.rel_tblseq; IF FOUND THEN -- the table has a pkey, SELECT string_agg(quote_ident(attname), ',') INTO v_colList FROM ( SELECT attname 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) AS t; ELSE -- the table has no pkey SELECT string_agg(quote_ident(attname), ',') INTO v_colList FROM ( SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = v_fullTableName::regclass AND attnum > 0 AND attisdropped = FALSE) AS t; END IF; -- prepare the COPY statement v_stmt= 'COPY (SELECT * FROM ' || v_fullTableName || ' ORDER BY ' || v_colList || ') TO ' || quote_literal(v_fileName) || ' ' || coalesce (v_copyOptions, ''); WHEN 'S' THEN -- if it is a sequence, the statement has no order by IF emaj._pg_version_num() >= 100000 THEN v_stmt = 'COPY (SELECT sequencename, rel.last_value, start_value, increment_by, max_value, ' || 'min_value, cache_size, cycle, rel.is_called ' || 'FROM ' || v_fullTableName || ' rel, pg_sequences ' || 'WHERE schemaname = '|| quote_literal(r_tblsq.rel_schema) || ' AND sequencename = ' || quote_literal(r_tblsq.rel_tblseq) ||') TO ' || quote_literal(v_fileName) || ' ' || coalesce (v_copyOptions, ''); ELSE v_stmt = 'COPY (SELECT sequence_name, last_value, start_value, increment_by, max_value, ' || 'min_value, cache_value, is_cycled, is_called FROM ' || v_fullTableName || ') TO ' || quote_literal(v_fileName) || ' ' || coalesce (v_copyOptions, ''); END IF; END CASE; -- and finaly perform the COPY EXECUTE v_stmt; v_nbTb = v_nbTb + 1; END LOOP; -- create the _INFO file to keep general information about the snap operation EXECUTE 'COPY (SELECT ' || quote_literal('E-Maj snap of tables group ' || v_groupName || ' at ' || transaction_timestamp()) || ') TO ' || quote_literal(v_dir || '/_INFO'); -- insert end in the history INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('SNAP_GROUP', 'END', v_groupName, v_nbTb || ' tables/sequences processed'); RETURN v_nbTb; END; $emaj_snap_group$; COMMENT ON FUNCTION emaj.emaj_snap_group(TEXT,TEXT,TEXT) IS $$Snaps all application tables and sequences of an E-Maj group into a given directory.$$; 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 SET search_path = pg_catalog, pg_temp 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_