-- -- E-Maj: migration from 4.3.0 to 4.3.1 -- -- 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_nbWarning INT; BEGIN -- The emaj version registered in emaj_param must be '4.3.0'. SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version'; IF v_emajVersion <> '4.3.0' THEN RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 4.3.0',v_emajVersion; END IF; -- The installed postgres version must be at least 11. IF current_setting('server_version_num')::int < 110000 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 11.', current_setting('server_version'); END IF; -- Check E-Maj environment state. SELECT count(msg) FILTER (WHERE msg = 'No error detected'), count(msg) FILTER (WHERE msg LIKE 'Warning:%') INTO v_nbNoError, v_nbWarning FROM emaj.emaj_verify_all() AS t(msg); 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.'; END IF; IF v_nbWarning > 0 THEN RAISE WARNING 'E-Maj upgrade: the E-Maj environment health check reports warning. You may execute "SELECT * FROM ' 'emaj.emaj_verify_all();" to get more details.'; 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 4.3.1', 'Upgrade from 4.3.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 during the upgrade operation. SELECT emaj._disable_event_triggers(); ---------------------------------------------- -- -- -- emaj enums, tables, views and sequences -- -- -- ---------------------------------------------- -- -- Add created or recreated tables and sequences to the list of content to save by pg_dump. -- ------------------------------------ -- -- -- emaj types -- -- -- ------------------------------------ CREATE TYPE emaj.emaj_sequence_stat_type AS ( stat_group TEXT, -- group name owning the schema.table stat_schema TEXT, -- schema name stat_sequence TEXT, -- sequence name stat_first_mark TEXT, -- mark representing the lower bound of the time range stat_first_mark_datetime TIMESTAMPTZ, -- clock timestamp of the mark representing the lower bound of the time range stat_last_mark TEXT, -- mark representing the upper bound of the time range stat_last_mark_datetime TIMESTAMPTZ, -- clock timestamp of the mark representing the upper bound of the time range stat_increments BIGINT, -- number of sequence increments for this sequence during the time interval stat_has_structure_changed BOOLEAN -- TRUE if any property other than last_value has changed ); COMMENT ON TYPE emaj.emaj_sequence_stat_type IS $$Represents the structure of rows returned by the emaj_sequence_stat_group() function.$$; ------------------------------------ -- -- -- 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 -- ------------------------------------------------------------------ ------------------------------------------------------------------ -- create new or modified functions -- ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION emaj._build_alter_seq(ref_seq_rec emaj.emaj_sequence, trg_seq_rec emaj.emaj_sequence) 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: 2 emaj_sequence records representing the reference and the 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 ref_seq_rec.sequ_last_val <> trg_seq_rec.sequ_last_val OR ref_seq_rec.sequ_is_called <> trg_seq_rec.sequ_is_called THEN IF trg_seq_rec.sequ_is_called THEN v_stmt = v_stmt || ' RESTART ' || trg_seq_rec.sequ_last_val + trg_seq_rec.sequ_increment; ELSE v_stmt = v_stmt || ' RESTART ' || trg_seq_rec.sequ_last_val; END IF; END IF; IF ref_seq_rec.sequ_start_val <> trg_seq_rec.sequ_start_val THEN v_stmt = v_stmt || ' START ' || trg_seq_rec.sequ_start_val; END IF; IF ref_seq_rec.sequ_increment <> trg_seq_rec.sequ_increment THEN v_stmt = v_stmt || ' INCREMENT ' || trg_seq_rec.sequ_increment; END IF; IF ref_seq_rec.sequ_min_val <> trg_seq_rec.sequ_min_val THEN v_stmt = v_stmt || ' MINVALUE ' || trg_seq_rec.sequ_min_val; END IF; IF ref_seq_rec.sequ_max_val <> trg_seq_rec.sequ_max_val THEN v_stmt = v_stmt || ' MAXVALUE ' || trg_seq_rec.sequ_max_val; END IF; IF ref_seq_rec.sequ_cache_val <> trg_seq_rec.sequ_cache_val THEN v_stmt = v_stmt || ' CACHE ' || trg_seq_rec.sequ_cache_val; END IF; IF ref_seq_rec.sequ_is_cycled <> trg_seq_rec.sequ_is_cycled THEN IF trg_seq_rec.sequ_is_cycled = '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._sequence_stat_seq(r_rel emaj.emaj_relation, p_beginTimeId BIGINT, p_endTimeId BIGINT, OUT p_increments BIGINT, OUT p_hasStructureChanged BOOLEAN) LANGUAGE plpgsql AS $_sequence_stat_seq$ -- This function compares the state of a single sequence between 2 time stamps or between a time stamp and the current state. -- It is called by the _sequence_stat_group() function. -- Input: row from emaj_relation corresponding to the appplication sequence to proccess, -- the time stamp ids defining the time range to examine (a end time stamp id set to NULL indicates the current state). -- Output: number of sequence increments between both time stamps for the sequence -- a boolean indicating whether any structure property has been modified between both time stamps. DECLARE r_beginSeq emaj.emaj_sequence%ROWTYPE; r_endSeq emaj.emaj_sequence%ROWTYPE; BEGIN -- Get the sequence characteristics at begin time id. SELECT * INTO r_beginSeq FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq AND sequ_time_id = p_beginTimeId; -- Get the sequence characteristics at end time id. IF p_endTimeId IS NOT NULL THEN SELECT * INTO r_endSeq FROM emaj.emaj_sequence WHERE sequ_schema = r_rel.rel_schema AND sequ_name = r_rel.rel_tblseq AND sequ_time_id = p_endTimeId; ELSE SELECT * INTO r_endSeq FROM emaj._get_current_sequence_state(r_rel.rel_schema, r_rel.rel_tblseq, NULL); END IF; -- Compute the statistics p_increments = (r_endSeq.sequ_last_val - r_beginSeq.sequ_last_val) / r_beginSeq.sequ_increment - CASE WHEN r_endSeq.sequ_is_called THEN 0 ELSE 1 END + CASE WHEN r_beginSeq.sequ_is_called THEN 0 ELSE 1 END; p_hasStructureChanged = r_beginSeq.sequ_start_val <> r_endSeq.sequ_start_val OR r_beginSeq.sequ_increment <> r_endSeq.sequ_increment OR r_beginSeq.sequ_max_val <> r_endSeq.sequ_max_val OR r_beginSeq.sequ_min_val <> r_endSeq.sequ_min_val OR r_beginSeq.sequ_is_cycled <> r_endSeq.sequ_is_cycled; RETURN; END; $_sequence_stat_seq$; CREATE OR REPLACE FUNCTION emaj.emaj_log_stat_group(p_groupName TEXT, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_log_stat_type LANGUAGE plpgsql AS $emaj_log_stat_group$ -- This function returns statistics about logged data changes between 2 marks or between a mark and the current state for a single group. -- It is used to quickly get simple statistics of changes logged between 2 marks. -- Input: group name, the 2 mark names defining a range -- Output: set of stat rows by table (including tables without any data change) BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_table, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_rows FROM emaj._log_stat_groups(ARRAY[p_groupName], FALSE, p_firstMark, p_lastMark); END; $emaj_log_stat_group$; COMMENT ON FUNCTION emaj.emaj_log_stat_group(TEXT,TEXT,TEXT) IS $$Returns global statistics about logged data changes between 2 marks for a single group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_log_stat_groups(p_groupNames TEXT[], p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_log_stat_type LANGUAGE plpgsql AS $emaj_log_stat_groups$ -- This function returns statistics about logged data changes between 2 marks or between a mark and the current state for a groups array. -- It is used to quickly get simple statistics of changes logged between 2 marks. -- Input: group names array, the 2 mark names defining a range -- Output: set of stat rows by table (including tables without any data change) BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_table, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_rows FROM emaj._log_stat_groups(p_groupNames, TRUE, p_firstMark, p_lastMark); END; $emaj_log_stat_groups$; COMMENT ON FUNCTION emaj.emaj_log_stat_groups(TEXT[],TEXT,TEXT) IS $$Returns global statistics about logged data changes between 2 marks for several groups.$$; CREATE OR REPLACE FUNCTION emaj._log_stat_groups(p_groupNames TEXT[], p_multiGroup BOOLEAN, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj._log_stat_type LANGUAGE plpgsql AS $_log_stat_groups$ -- This function effectively returns statistics about logged data changes between 2 marks or between a mark and the current state for 1 -- or several groups. -- These statistics are computed using the serial id of log tables and holes is sequences recorded into emaj_seq_hole at rollback time. -- 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 last_mark indicates the current state -- Use a NULL or an empty string as last_mark to know the number of rows to rollback to reach the mark specified by the first_mark -- parameter. -- The keyword 'EMAJ_LAST_MARK' can be used as first or last mark to specify the last set mark for the groups. -- Output: set of stat rows by table (including tables without any data change) DECLARE v_firstMarkTimeId BIGINT; v_lastMarkTimeId BIGINT; v_firstMarkTs TIMESTAMPTZ; v_lastMarkTs TIMESTAMPTZ; v_firstEmajGid BIGINT; v_lastEmajGid BIGINT; BEGIN -- Check the groups name. SELECT emaj._check_group_names(p_groupNames := p_groupNames, p_mayBeNull := p_multiGroup, p_lockGroups := FALSE) INTO p_groupNames; IF p_groupNames IS NOT NULL THEN -- Check the marks range and get some data about both marks. SELECT * INTO p_firstMark, p_lastMark, v_firstMarkTimeId, v_lastMarkTimeId, v_firstMarkTs, v_lastMarkTs, v_firstEmajGid, v_lastEmajGid FROM emaj._check_marks_range(p_groupNames := p_groupNames, p_firstMark := p_firstMark, p_lastMark := p_lastMark); -- For each table of the group, get the number of log rows and return the statistics. -- Shorten the timeframe if the table did not belong to the group on the entire requested time frame. RETURN QUERY SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, CASE WHEN v_firstMarkTimeId IS NULL THEN NULL WHEN v_firstMarkTimeId >= lower(rel_time_range) THEN p_firstMark ELSE coalesce( (SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = lower(rel_time_range) AND mark_group = rel_group ),'[deleted mark]') END AS stat_first_mark, CASE WHEN v_firstMarkTimeId IS NULL THEN NULL WHEN v_firstMarkTimeId >= lower(rel_time_range) THEN v_firstMarkTs ELSE (SELECT time_clock_timestamp FROM emaj.emaj_time_stamp WHERE time_id = lower(rel_time_range) ) END AS stat_first_mark_datetime, CASE WHEN v_firstMarkTimeId IS NULL THEN NULL WHEN v_firstMarkTimeId >= lower(rel_time_range) THEN v_firstEmajGid ELSE (SELECT time_last_emaj_gid FROM emaj.emaj_time_stamp WHERE time_id = lower(rel_time_range) ) END AS stat_first_mark_gid, CASE WHEN v_lastMarkTimeId IS NULL AND upper_inf(rel_time_range) THEN NULL WHEN NOT upper_inf(rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(rel_time_range) < v_lastMarkTimeId) THEN coalesce( (SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = upper(rel_time_range) AND mark_group = rel_group ),'[deleted mark]') ELSE p_lastMark END AS stat_last_mark, CASE WHEN v_lastMarkTimeId IS NULL AND upper_inf(rel_time_range) THEN NULL WHEN NOT upper_inf(rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(rel_time_range) < v_lastMarkTimeId) THEN (SELECT time_clock_timestamp FROM emaj.emaj_time_stamp WHERE time_id = upper(rel_time_range) ) ELSE v_lastMarkTs END AS stat_last_mark_datetime, CASE WHEN v_lastMarkTimeId IS NULL AND upper_inf(rel_time_range) THEN NULL WHEN NOT upper_inf(rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(rel_time_range) < v_lastMarkTimeId) THEN (SELECT time_last_emaj_gid FROM emaj.emaj_time_stamp WHERE time_id = upper(rel_time_range) ) ELSE v_lastEmajGid END AS stat_last_mark_gid, CASE WHEN v_firstMarkTimeId IS NULL THEN 0 -- group just created but without any mark ELSE emaj._log_stat_tbl(emaj_relation, CASE WHEN v_firstMarkTimeId >= lower(rel_time_range) THEN v_firstMarkTimeId ELSE lower(rel_time_range) END, CASE WHEN NOT upper_inf(rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(rel_time_range) < v_lastMarkTimeId) THEN upper(rel_time_range) ELSE v_lastMarkTimeId END) END AS nb_rows FROM emaj.emaj_relation WHERE rel_group = ANY(p_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; ELSE RETURN; END IF; END; $_log_stat_groups$; CREATE OR REPLACE FUNCTION emaj.emaj_detailed_log_stat_group(p_groupName TEXT, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_detailed_log_stat_type LANGUAGE plpgsql AS $emaj_detailed_log_stat_group$ -- This function returns statistics on logged data changes between 2 marks as viewed through the log tables for one tables group. -- It provides much precise 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. -- Input: group name, the 2 marks names defining a range -- Output: set of stat rows by table, user and SQL type BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_table, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_role, stat_verb, stat_rows FROM emaj._detailed_log_stat_groups(ARRAY[p_groupName], FALSE, p_firstMark, p_lastMark); END; $emaj_detailed_log_stat_group$; COMMENT ON FUNCTION emaj.emaj_detailed_log_stat_group(TEXT,TEXT,TEXT) IS $$Returns detailed statistics about logged data changes between 2 marks for a single group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_detailed_log_stat_groups(p_groupNames TEXT[], p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_detailed_log_stat_type LANGUAGE plpgsql AS $emaj_detailed_log_stat_groups$ -- This function returns statistics on logged data changes between 2 marks as viewed through the log tables for several tables groups. -- It provides much precise 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. -- Input: group names array, the 2 marks names defining a range -- Output: set of stat rows by table, user and SQL type BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_table, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_role, stat_verb, stat_rows FROM emaj._detailed_log_stat_groups(p_groupNames, TRUE, p_firstMark, p_lastMark); END; $emaj_detailed_log_stat_groups$; COMMENT ON FUNCTION emaj.emaj_detailed_log_stat_groups(TEXT[],TEXT,TEXT) IS $$Returns detailed statistics about logged data changes between 2 marks for several groups.$$; CREATE OR REPLACE FUNCTION emaj._detailed_log_stat_groups(p_groupNames TEXT[], p_multiGroup BOOLEAN, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj._detailed_log_stat_type LANGUAGE plpgsql AS $_detailed_log_stat_groups$ -- This function effectively returns statistics on logged data changes executed between 2 marks as viewed through the log tables for one -- or several groups. -- It provides muche precise 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. -- 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 last_mark indicates the current state -- The keyword 'EMAJ_LAST_MARK' can be used as first or last mark to specify the last set mark. -- Output: set of stat rows by table, user and SQL type 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(p_groupNames := p_groupNames, p_mayBeNull := p_multiGroup, p_lockGroups := FALSE); IF p_groupNames IS NOT NULL THEN -- Check the marks range and get some data about both marks. SELECT * INTO p_firstMark, p_lastMark, v_firstMarkTimeId, v_lastMarkTimeId, v_firstMarkTs, v_lastMarkTs, v_firstEmajGid, v_lastEmajGid FROM emaj._check_marks_range(p_groupNames := p_groupNames, p_firstMark := p_firstMark, p_lastMark := p_lastMark); -- 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(p_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 = p_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 state. 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 = p_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'; -- Execute the statement. FOR r_stat IN EXECUTE v_stmt LOOP RETURN NEXT r_stat; END LOOP; END LOOP; END IF; -- Final return. RETURN; END; $_detailed_log_stat_groups$; CREATE OR REPLACE FUNCTION emaj.emaj_sequence_stat_group(p_groupName TEXT, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_sequence_stat_type LANGUAGE plpgsql AS $emaj_sequence_stat_group$ -- This function returns statistics on sequences changes recorded between 2 marks or between a mark and the current state -- for a single group. -- Input: group name, both mark names defining a range -- Output: set of stats by sequence (including unchanged sequences) BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_sequence, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_increments, stat_has_structure_changed FROM emaj._sequence_stat_groups(ARRAY[p_groupName], FALSE, p_firstMark, p_lastMark); END; $emaj_sequence_stat_group$; COMMENT ON FUNCTION emaj.emaj_sequence_stat_group(TEXT,TEXT,TEXT) IS $$Returns global statistics about recorded sequences changes between 2 marks for a single group.$$; CREATE OR REPLACE FUNCTION emaj.emaj_sequence_stat_groups(p_groupNames TEXT[], p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_sequence_stat_type LANGUAGE plpgsql AS $emaj_sequence_stat_groups$ -- This function returns statistics on sequences changes recorded between 2 marks or between a mark and the current state -- for a groups array. -- Input: group names array, both mark names defining a range -- Output: set of stats by sequence (including unchanged sequences) BEGIN RETURN QUERY SELECT stat_group, stat_schema, stat_sequence, stat_first_mark, stat_first_mark_datetime, stat_last_mark, stat_last_mark_datetime, stat_increments, stat_has_structure_changed FROM emaj._sequence_stat_groups(p_groupNames, TRUE, p_firstMark, p_lastMark); END; $emaj_sequence_stat_groups$; COMMENT ON FUNCTION emaj.emaj_sequence_stat_groups(TEXT[],TEXT,TEXT) IS $$Returns global statistics about recorded sequences changes between 2 marks for several groups.$$; CREATE OR REPLACE FUNCTION emaj._sequence_stat_groups(p_groupNames TEXT[], p_multiGroup BOOLEAN, p_firstMark TEXT, p_lastMark TEXT) RETURNS SETOF emaj.emaj_sequence_stat_type LANGUAGE plpgsql AS $_sequence_stat_groups$ -- This function effectively returns statistics on sequences changes recorded between 2 marks or between a mark and the current state for -- one or several groups. -- Input: group names array, a boolean indicating whether the calling function is a multi_groups function, both mark names defining a -- range -- a NULL value or an empty string as last_mark indicates the current state -- The keyword 'EMAJ_LAST_MARK' can be used as first or last mark to specify the last set mark for the groups. -- Output: set of stats by sequence (including unchanged sequences). DECLARE v_firstMarkTimeId BIGINT; v_lastMarkTimeId BIGINT; v_firstMarkTs TIMESTAMPTZ; v_lastMarkTs TIMESTAMPTZ; v_lowerTimeId BIGINT; v_upperTimeId BIGINT; r_seq emaj.emaj_relation%ROWTYPE; r_stat emaj.emaj_sequence_stat_type%ROWTYPE; BEGIN -- Check the groups name. SELECT emaj._check_group_names(p_groupNames := p_groupNames, p_mayBeNull := p_multiGroup, p_lockGroups := FALSE) INTO p_groupNames; IF p_groupNames IS NOT NULL THEN -- Check the marks range and get some data about both marks. SELECT checked.p_firstMark, checked.p_lastMark, p_firstMarkTimeId, p_lastMarkTimeId, p_firstMarkTs, p_lastMarkTs INTO p_firstMark, p_lastMark, v_firstMarkTimeId, v_lastMarkTimeId, v_firstMarkTs, v_lastMarkTs FROM emaj._check_marks_range(p_groupNames := p_groupNames, p_firstMark := p_firstMark, p_lastMark := p_lastMark) AS checked; -- For each sequence of the group, get and return the statistics. FOR r_seq IN SELECT * FROM emaj.emaj_relation WHERE rel_group = ANY(p_groupNames) AND rel_kind = 'S' -- sequences 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 r_stat.stat_group = r_seq.rel_group; r_stat.stat_schema = r_seq.rel_schema; r_stat.stat_sequence = r_seq.rel_tblseq; r_stat.stat_first_mark = p_firstMark; r_stat.stat_first_mark_datetime = v_firstMarkTs; r_stat.stat_last_mark = p_lastMark; r_stat.stat_last_mark_datetime = v_lastMarkTs; v_lowerTimeId = v_firstMarkTimeId; v_upperTimeId = v_lastMarkTimeId; -- Shorten the time frame if the sequence did not belong to the group on the entire requested time frame. IF v_firstMarkTimeId < lower(r_seq.rel_time_range) THEN v_lowerTimeId = lower(r_seq.rel_time_range); r_stat.stat_first_mark = coalesce( (SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = v_lowerTimeId AND mark_group = r_seq.rel_group ),'[deleted mark]'); r_stat.stat_first_mark_datetime = (SELECT time_clock_timestamp FROM emaj.emaj_time_stamp WHERE time_id = v_lowerTimeId ); END IF; IF NOT upper_inf(r_seq.rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(r_seq.rel_time_range) < v_lastMarkTimeId) THEN v_upperTimeId = upper(r_seq.rel_time_range); r_stat.stat_last_mark = coalesce( (SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = v_upperTimeId AND mark_group = r_seq.rel_group ),'[deleted mark]'); r_stat.stat_last_mark_datetime = (SELECT time_clock_timestamp FROM emaj.emaj_time_stamp WHERE time_id = v_upperTimeId ); END IF; -- Get the stats for the sequence. SELECT p_increments, p_hasStructureChanged INTO r_stat.stat_increments, r_stat.stat_has_structure_changed FROM emaj._sequence_stat_seq(r_seq, v_lowerTimeId, v_upperTimeId); -- Return the stat row for the sequence. RETURN NEXT r_stat; END LOOP; END IF; RETURN; END; $_sequence_stat_groups$; CREATE OR REPLACE FUNCTION emaj._gen_sql_dump_changes_group(p_groupName TEXT, p_firstMark TEXT, INOUT p_lastMark TEXT, p_optionsList TEXT, p_tblseqs TEXT[], p_genSqlOnly BOOLEAN, OUT p_nbStmt INT, OUT p_copyOptions TEXT, OUT p_noEmptyFiles BOOLEAN, OUT p_isPsqlCopy BOOLEAN) LANGUAGE plpgsql AS $_gen_sql_dump_changes_group$ -- This function returns SQL statements that read log tables and sequences states to show the data changes recorded between 2 marks for -- a group. -- It is called by both emaj_gen_sql_dump_changes_group() and emaj_dump_changes_group() functions to prepare the SQL statements to be -- stored or executed. -- The function checks the supplied parameters, including the options that may be common or specific to both calling functions. -- Input: group name, 2 mark names defining the time range, -- options (a comma separated options list), -- array of schema qualified table and sequence names to process (NULL to process all relations), -- a boolean indentifying the calling function. -- Output: the number of generated SQL statements, excluding comments, but including SET or RESET statements, if any. -- the COPY_OPTIONS and NO_EMPTY_FILES options needed by the emaj_dump_changes_group() function, -- a flag for generated psql \copy meta-commands needed by the emaj_gen_sql_dump_changes_group() function. DECLARE v_firstMarkTimeId BIGINT; v_lastMarkTimeId BIGINT; v_firstMarkTs TIMESTAMPTZ; v_lastMarkTs TIMESTAMPTZ; v_firstEmajGid BIGINT; v_lastEmajGid BIGINT; v_optionsList TEXT; v_options TEXT[]; v_option TEXT; v_colsOrder TEXT; v_consolidation TEXT; v_copyOptions TEXT; v_psqlCopyDir TEXT; v_psqlCopyOptions TEXT; v_emajColumnsList TEXT; v_isPsqlCopy BOOLEAN = FALSE; v_noEmptyFiles BOOLEAN = FALSE; v_orderBy TEXT; v_sequencesOnly BOOLEAN = FALSE; v_sqlFormat TEXT = 'RAW'; v_tablesOnly BOOLEAN = FALSE; v_tableWithoutPkList TEXT; v_nbStmt INT = 0; v_relFirstMark TEXT; v_relLastMark TEXT; v_relFirstEmajGid BIGINT; v_relLastEmajGid BIGINT; v_relFirstTimeId BIGINT; v_relLastTimeId BIGINT; v_stmt TEXT; v_comment TEXT; v_copyOutputFile TEXT; r_rel RECORD; BEGIN -- Check the group name. PERFORM emaj._check_group_names(p_groupNames := ARRAY[p_groupName], p_mayBeNull := FALSE, p_lockGroups := FALSE); -- Check the marks range and get some data about both marks. SELECT * INTO p_firstMark, p_lastMark, v_firstMarkTimeId, v_lastMarkTimeId, v_firstMarkTs, v_lastMarkTs, v_firstEmajGid, v_lastEmajGid FROM emaj._check_marks_range(p_groupNames := ARRAY[p_groupName], p_firstMark := p_firstMark, p_lastMark := p_lastMark, p_finiteUpperBound := TRUE); -- Analyze the options parameter. IF p_optionsList IS NOT NULL THEN v_optionsList = p_optionsList; IF NOT p_genSqlOnly THEN -- Extract the COPY_OPTIONS list, if any, before removing spaces. v_copyOptions = (regexp_match(v_optionsList, 'COPY_OPTIONS\s*?=\s*?\((.*?)\)', 'i'))[1]; IF v_copyOptions IS NOT NULL THEN v_optionsList = replace(v_optionsList, v_copyOptions, ''); END IF; END IF; IF p_genSqlOnly THEN -- Extract the PSQL_COPY_DIR and PSQL_COPY_OPTIONS options, if any, before removing spaces. v_psqlCopyDir = (regexp_match(v_optionsList, 'PSQL_COPY_DIR\s*?=\s*?\((.*?)\)', 'i'))[1]; IF v_psqlCopyDir IS NOT NULL THEN v_optionsList = replace(v_optionsList, v_psqlCopyDir, ''); END IF; v_psqlCopyOptions = (regexp_match(v_optionsList, 'PSQL_COPY_OPTIONS\s*?=\s*?\((.*?)\)', 'i'))[1]; IF v_psqlCopyOptions IS NOT NULL THEN v_optionsList = replace(v_optionsList, v_psqlCopyOptions, ''); END IF; END IF; -- Remove spaces, tabs and newlines from the options list. v_optionsList = regexp_replace(v_optionsList, '\s', '', 'g'); -- Extract the option values list, if any. v_emajColumnsList = (regexp_match(v_optionsList, 'EMAJ_COLUMNS\s*?=\s*?\((.*?)\)', 'i'))[1]; IF v_emajColumnsList IS NOT NULL THEN v_optionsList = replace(v_optionsList, v_emajColumnsList, ''); END IF; -- Process each option from the comma separated list. v_options = regexp_split_to_array(upper(v_optionsList), ','); FOREACH v_option IN ARRAY v_options LOOP CASE WHEN v_option LIKE 'COLS_ORDER=%' THEN CASE WHEN v_option = 'COLS_ORDER=LOG_TABLE' THEN v_colsOrder = 'LOG_TABLE'; WHEN v_option = 'COLS_ORDER=PK' THEN v_colsOrder = 'PK'; ELSE RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The COLS_ORDER option only accepts ' 'LOG_TABLE or PK values).', v_option; END CASE; WHEN v_option LIKE 'CONSOLIDATION=%' THEN CASE WHEN v_option = 'CONSOLIDATION=NONE' THEN v_consolidation = 'NONE'; WHEN v_option = 'CONSOLIDATION=PARTIAL' THEN v_consolidation = 'PARTIAL'; WHEN v_option = 'CONSOLIDATION=FULL' THEN v_consolidation = 'FULL'; ELSE RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The CONSOLIDATION option only accepts ' 'NONE or PARTIAL or FULL values).', v_option; END CASE; WHEN v_option LIKE 'COPY_OPTIONS=%' AND NOT p_genSqlOnly THEN IF v_option <> 'COPY_OPTIONS=()' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The COPY options must be set between ().', v_option; END IF; -- Check the copy options parameter doesn't contain unquoted semicolon that could be used for sql injection. IF regexp_replace(v_copyOptions,'''.*''','') LIKE '%;%' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: Unquoted semi-column in COPY options is illegal.'; END IF; v_copyOptions = '(' || v_copyOptions || ')'; WHEN v_option LIKE 'EMAJ_COLUMNS=%' THEN CASE WHEN v_option = 'EMAJ_COLUMNS=ALL' THEN v_emajColumnsList = '*'; WHEN v_option = 'EMAJ_COLUMNS=MIN' THEN v_emajColumnsList = 'MIN'; WHEN v_option = 'EMAJ_COLUMNS=()' THEN IF v_emajColumnsList NOT ILIKE '%emaj_tuple%' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: In the EMAJ_COLUMN option, the "emaj_tuple" column must be part ' 'of the columns list.'; END IF; ELSE RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The EMAJ_COLUMNS option only accepts ' 'ALL or MIN values or a (columns list).', v_option; END CASE; WHEN v_option = 'NO_EMPTY_FILES' AND NOT p_genSqlOnly THEN v_noEmptyFiles = TRUE; WHEN v_option LIKE 'ORDER_BY=%' THEN CASE WHEN v_option = 'ORDER_BY=PK' THEN v_orderBy = 'PK'; WHEN v_option = 'ORDER_BY=TIME' THEN v_orderBy = 'TIME'; ELSE RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The ORDER_BY option only accepts ' 'PK or TIME values.', v_option; END CASE; WHEN v_option LIKE 'PSQL_COPY_DIR%' AND p_genSqlOnly THEN v_isPsqlCopy = TRUE; IF v_option <> 'PSQL_COPY_DIR=()' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The directory name must be set between ().', v_option; END IF; WHEN v_option LIKE 'PSQL_COPY_OPTIONS=%' AND p_genSqlOnly THEN IF v_option <> 'PSQL_COPY_OPTIONS=()' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The COPY options list must be set between ().', v_option; END IF; WHEN v_option = 'SEQUENCES_ONLY' THEN v_sequencesOnly = TRUE; WHEN v_option LIKE 'SQL_FORMAT=%' AND p_genSqlOnly THEN CASE WHEN v_option = 'SQL_FORMAT=RAW' THEN v_sqlFormat = 'RAW'; WHEN v_option = 'SQL_FORMAT=PRETTY' THEN v_sqlFormat = 'PRETTY'; ELSE RAISE EXCEPTION '_gen_sql_dump_changes_group: Error on the option "%". The SQL_FORMAT option only accepts ' 'RAW or PRETTY values.', v_option; END CASE; WHEN v_option = 'TABLES_ONLY' THEN v_tablesOnly = TRUE; ELSE IF v_option <> '' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: The option "%" is unknown.', v_option; END IF; END CASE; END LOOP; -- Validate the relations between options. -- SEQUENCES_ONLY and TABLES_ONLY are not compatible. IF v_sequencesOnly AND v_tablesOnly THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: SEQUENCES_ONLY and TABLES_ONLY options are mutually exclusive.'; END IF; -- PSQL_COPY_OPTIONS needs a PSQL_COPY_DIR to be set; IF v_psqlCopyOptions IS NOT NULL AND NOT v_isPsqlCopy THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: the PSQL_COPY_OPTIONS option needs a PSQL_COPY_DIR option to be set.'; END IF; -- PSQL_COPY_DIR and FORMAT=PRETTY are not compatible (for a psql \copy, the statement must be one a single line). IF v_isPsqlCopy AND v_sqlFormat = 'PRETTY' THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: PSQL_COPY_DIR and FORMAT=PRETTY options are mutually exclusive.'; END IF; -- When one or several options need PRIMARY KEYS, check that all selected tables have a PK. IF v_consolidation IN ('PARTIAL', 'FULL') OR v_colsOrder = 'PK' OR v_orderBy = 'PK' THEN SELECT string_agg(table_name, ', ') INTO v_tableWithoutPkList FROM ( SELECT rel_schema || '.' || rel_tblseq AS table_name FROM emaj.emaj_relation WHERE rel_group = p_groupName AND rel_kind = 'r' AND NOT v_sequencesOnly AND (p_tblseqs IS NULL OR rel_schema || '.' || rel_tblseq = ANY (p_tblseqs)) AND rel_time_range && int8range(v_firstMarkTimeId, v_lastMarkTimeId,'[)') AND rel_pk_cols IS NULL ORDER BY rel_schema, rel_tblseq, rel_time_range ) AS t; IF v_tableWithoutPkList IS NOT NULL THEN RAISE EXCEPTION '_gen_sql_dump_changes_group: A CONSOLIDATION level set to PARTIAL or FULL or a COLS_ORDER set to PK or an ' 'ORDER_BY set to PK cannot support tables without primary key. And no primary key is defined for tables "%"', v_tableWithoutPkList; END IF; END IF; -- Reject the empty files removing if the adminpack extension is not installed. IF v_noEmptyFiles THEN PERFORM 1 FROM pg_catalog.pg_extension WHERE extname = 'adminpack'; IF NOT FOUND THEN RAISE WARNING 'emaj_dump_changes_group: the NO_EMPTY_FILES option cannot be satisfied because the adminpack extension is not ' 'installed.'; v_noEmptyFiles = FALSE; END IF; END IF; END IF; -- If table/sequence names to filter are supplied, check them. IF p_tblseqs IS NOT NULL THEN p_tblseqs = emaj._check_tblseqs_filter(p_tblseqs, ARRAY[p_groupName], v_firstMarkTimeId, v_lastMarkTimeId, FALSE); END IF; -- End of checks. -- Set options default values. v_consolidation = coalesce(v_consolidation, 'NONE'); v_copyOptions = coalesce(v_copyOptions, ''); v_colsOrder = coalesce(v_colsOrder, CASE WHEN v_consolidation = 'NONE' THEN 'LOG_TABLE' ELSE 'PK' END); v_emajColumnsList = coalesce(v_emajColumnsList, CASE WHEN v_consolidation = 'NONE' THEN '*' ELSE 'emaj_tuple' END); v_orderBy = coalesce(v_orderBy, CASE WHEN v_consolidation = 'NONE' THEN 'TIME' ELSE 'PK' END); -- Resolve the MIN value for the EMAJ_COLUMNS option, depending on the final consolidation level. IF v_emajColumnsList = 'MIN' THEN v_emajColumnsList = CASE WHEN v_consolidation = 'NONE' THEN 'emaj_gid,emaj_tuple' ELSE 'emaj_tuple' END; END IF; -- Set the ORDER_BY clause if not explicitely done in the supplied options. v_orderBy = coalesce(v_orderBy, CASE WHEN v_consolidation = 'NONE' THEN 'TIME' ELSE 'PK' END); -- Create a temporary table to hold the SQL statements. DROP TABLE IF EXISTS emaj_temp_sql CASCADE; CREATE TEMP TABLE emaj_temp_sql ( sql_stmt_number INT, -- SQL statement number sql_line_number INT, -- line number for the statement (0 for an initial comment) sql_rel_kind TEXT, -- either "table" or "sequence" sql_schema TEXT, -- the application schema sql_tblseq TEXT, -- the table or sequence name sql_first_mark TEXT, -- the first mark name sql_last_mark TEXT, -- the last mark name sql_group TEXT, -- the group name sql_nb_changes BIGINT, -- the estimated number of changes to process (NULL for sequences) sql_file_name_suffix TEXT, -- the file name suffix to use to build the output file name if the statement -- has to be executed by a COPY statement or a \copy meta-command sql_text TEXT, -- the generated sql text sql_result BIGINT -- a column available for caller usage (if needed, some other can be added by the -- caller with an ALTER TABLE) ); -- Add an initial comment reporting the supplied options. v_comment = format('-- Generated SQL for dumping changes in tables group "%s" %sbetween marks "%s" and "%s"%s', p_groupName, CASE WHEN p_tblseqs IS NOT NULL THEN '(subset) ' ELSE '' END, p_firstMark, p_lastMark, CASE WHEN p_optionsList IS NOT NULL AND p_optionsList <> '' THEN ' using options: ' || p_optionsList ELSE '' END); INSERT INTO emaj_temp_sql (sql_stmt_number, sql_line_number, sql_text) VALUES (0, 0, v_comment); -- If the requested consolidation level is FULL, then add a SET statement to disable nested-loop nodes in the execution plan. -- This solves a performance issue with the generated SQL statements for log tables analysis. IF v_consolidation = 'FULL' THEN v_nbStmt = v_nbStmt + 1; INSERT INTO emaj_temp_sql (sql_stmt_number, sql_line_number, sql_text) VALUES (v_nbStmt, 1, 'SET enable_nestloop = FALSE;'); END IF; -- Process each log table or sequence from the emaj_relation table that enters in the marks range, starting with tables. FOR r_rel IN SELECT rel_schema, rel_tblseq, rel_time_range, rel_group, rel_kind, rel_log_schema, rel_log_table, rel_emaj_verb_attnum, rel_pk_cols, CASE WHEN rel_kind = 'r' THEN 'table' ELSE 'sequence' END AS kind, count(*) OVER (PARTITION BY rel_schema, rel_tblseq) AS nb_time_range, row_number() OVER (PARTITION BY rel_schema, rel_tblseq ORDER BY rel_time_range) AS time_range_rank, CASE WHEN rel_kind = 'S' THEN NULL ELSE emaj._log_stat_tbl(emaj_relation, CASE WHEN v_firstMarkTimeId >= lower(rel_time_range) THEN v_firstMarkTimeId ELSE lower(rel_time_range) END, CASE WHEN NOT upper_inf(rel_time_range) AND (v_lastMarkTimeId IS NULL OR upper(rel_time_range) < v_lastMarkTimeId) THEN upper(rel_time_range) ELSE v_lastMarkTimeId END) END AS nb_changes FROM emaj.emaj_relation WHERE rel_group = p_groupName AND ((rel_kind = 'r' AND NOT v_sequencesOnly) OR (rel_kind = 'S' AND NOT v_tablesOnly)) AND (p_tblseqs IS NULL OR rel_schema || '.' || rel_tblseq = ANY (p_tblseqs)) AND rel_time_range && int8range(v_firstMarkTimeId, v_lastMarkTimeId,'[)') ORDER BY rel_kind DESC, rel_schema, rel_tblseq, rel_time_range LOOP -- Compute the real mark and gid range for the relation (the relation time range can be shorter that the requested mark range). IF lower(r_rel.rel_time_range) <= v_firstMarkTimeId THEN v_relFirstMark = p_firstMark; v_relFirstEmajGid = v_firstEmajGid; v_relFirstTimeId = v_firstMarkTimeId; ELSE v_relFirstMark = coalesce((SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = lower(r_rel.rel_time_range) AND mark_group = r_rel.rel_group ),'[deleted mark]'); SELECT time_last_emaj_gid INTO STRICT v_firstEmajGid FROM emaj.emaj_time_stamp WHERE time_id = lower(r_rel.rel_time_range); v_relFirstTimeId = lower(r_rel.rel_time_range); END IF; IF upper_inf(r_rel.rel_time_range) OR upper(r_rel.rel_time_range) >= v_lastMarkTimeId THEN v_relLastMark = p_lastMark; v_relLastEmajGid = v_lastEmajGid; v_relLastTimeId = v_lastMarkTimeId; ELSE v_relLastMark = coalesce((SELECT mark_name FROM emaj.emaj_mark WHERE mark_time_id = upper(r_rel.rel_time_range) AND mark_group = r_rel.rel_group ),'[deleted mark]'); SELECT time_last_emaj_gid INTO STRICT v_lastEmajGid FROM emaj.emaj_time_stamp WHERE time_id = upper(r_rel.rel_time_range); v_relLastTimeId = upper(r_rel.rel_time_range); END IF; v_nbStmt = v_nbStmt + 1; -- Generate the comment and the statement for the table or sequence. IF r_rel.rel_kind = 'r' THEN v_comment = format('-- Dump changes for table %s.%s between marks "%s" and "%s" (%s changes)', r_rel.rel_schema, r_rel.rel_tblseq, v_relFirstMark, v_relLastMark, r_rel.nb_changes); v_stmt = emaj._gen_sql_dump_changes_tbl(r_rel.rel_log_schema, r_rel.rel_log_table, r_rel.rel_emaj_verb_attnum, r_rel.rel_pk_cols, v_relFirstEmajGid, v_relLastEmajGid, v_consolidation, v_emajColumnsList, v_colsOrder, v_orderBy); ELSE v_comment = format('-- Dump changes for sequence %s.%s between marks "%s" and "%s"', r_rel.rel_schema, r_rel.rel_tblseq, v_relFirstMark, v_relLastMark); v_stmt = emaj._gen_sql_dump_changes_seq(r_rel.rel_schema, r_rel.rel_tblseq, v_relFirstTimeId, v_relLastTimeId, v_consolidation); END IF; -- If the output is a psql script, build the output file name for the \copy command. IF v_isPsqlCopy THEN -- As several files may be generated for a single table or sequence, add a "_nn" to the file name suffix. v_copyOutputFile = v_psqlCopyDir || '/' || translate( r_rel.rel_schema || '_' || r_rel.rel_tblseq || CASE WHEN r_rel.nb_time_range > 1 THEN '_' || r_rel.time_range_rank ELSE '' END || '.changes', E' /\\$<>*', '_______'); v_stmt = '\copy (' || v_stmt || ') TO ' || quote_literal(v_copyOutputFile) || coalesce(' (' || v_psqlCopyOptions || ')', ''); ELSE IF p_genSqlOnly THEN v_stmt = v_stmt || ';'; END IF; END IF; -- Record the comment on line 0. INSERT INTO emaj_temp_sql VALUES (v_nbStmt, 0, r_rel.kind, r_rel.rel_schema, r_rel.rel_tblseq, v_relFirstMark, v_relLastMark, r_rel.rel_group, r_rel.nb_changes, NULL, v_comment); -- Record the statement on 1 or several rows, depending on the SQL_FORMAT option. -- In raw format, newlines and consecutive spaces are removed. IF v_sqlFormat = 'RAW' THEN v_stmt = replace(v_stmt, E'\n', ' '); v_stmt = regexp_replace(v_stmt, '\s{2,}', ' ', 'g'); END IF; INSERT INTO emaj_temp_sql SELECT v_nbStmt, row_number() OVER (), r_rel.kind, r_rel.rel_schema, r_rel.rel_tblseq, v_relFirstMark, v_relLastMark, r_rel.rel_group, r_rel.nb_changes, CASE WHEN r_rel.nb_time_range > 1 THEN '_' || r_rel.time_range_rank ELSE '' END || '.changes', line FROM regexp_split_to_table(v_stmt, E'\n') AS line; END LOOP; -- If the requested consolidation level is FULL, then add a RESET statement to revert the previous 'SET enable_nestloop = FALSE'. IF v_consolidation = 'FULL' THEN v_nbStmt = v_nbStmt + 1; INSERT INTO emaj_temp_sql (sql_stmt_number, sql_line_number, sql_text) VALUES (v_nbStmt, 1, 'RESET enable_nestloop;'); END IF; -- Return output parameters. p_nbStmt = v_nbStmt; p_copyOptions = v_copyOptions; p_noEmptyFiles = v_noEmptyFiles; p_isPsqlCopy = v_isPsqlCopy; RETURN; END; $_gen_sql_dump_changes_group$; -- pattern used by the tool that extracts and insert the functions definition ------------------------------------------ -- -- -- event triggers and related functions -- -- -- ------------------------------------------ ------------------------------------ -- -- -- emaj roles and rights -- -- -- ------------------------------------ REVOKE ALL ON ALL FUNCTIONS IN SCHEMA emaj FROM PUBLIC; GRANT EXECUTE ON FUNCTION emaj._sequence_stat_seq(r_rel emaj.emaj_relation, p_beginTimeId BIGINT, p_endTimeId BIGINT, OUT p_increments BIGINT, OUT p_hasStructureChanged BOOLEAN) TO emaj_viewer; GRANT EXECUTE ON FUNCTION emaj._get_current_sequence_state(p_schema TEXT, p_sequence TEXT, p_timeId BIGINT) TO emaj_viewer; GRANT EXECUTE ON FUNCTION emaj.emaj_sequence_stat_group(p_groupName TEXT, p_firstMark TEXT, p_lastMark TEXT) TO emaj_viewer; GRANT EXECUTE ON FUNCTION emaj.emaj_sequence_stat_groups(p_groupNames TEXT[], p_firstMark TEXT, p_lastMark TEXT) TO emaj_viewer; GRANT EXECUTE ON FUNCTION emaj._sequence_stat_groups(p_groupNames TEXT[], p_multiGroup BOOLEAN, p_firstMark TEXT, p_lastMark TEXT) TO emaj_viewer; GRANT ALL ON ALL TABLES IN SCHEMA emaj TO emaj_adm; GRANT ALL ON ALL SEQUENCES IN SCHEMA emaj TO emaj_adm; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA emaj TO emaj_adm; GRANT SELECT ON ALL TABLES IN SCHEMA emaj TO emaj_viewer; GRANT SELECT ON ALL SEQUENCES IN SCHEMA emaj TO emaj_viewer; REVOKE SELECT ON TABLE emaj.emaj_param FROM emaj_viewer; ------------------------------------ -- -- -- Complete the upgrade -- -- -- ------------------------------------ -- Enable the event triggers. DO $tmp$ DECLARE v_event_trigger_array TEXT[]; BEGIN -- Build the event trigger names array from the pg_event_trigger table. SELECT coalesce(array_agg(evtname),ARRAY[]::TEXT[]) INTO v_event_trigger_array FROM pg_catalog.pg_event_trigger WHERE evtname LIKE 'emaj%' AND evtenabled = 'D'; -- Call the _enable_event_triggers() function. PERFORM emaj._enable_event_triggers(v_event_trigger_array); END; $tmp$; -- Set comments for all internal functions, by directly inserting a row in the pg_description table for all emaj functions -- that do not have yet a recorded comment. INSERT INTO pg_catalog.pg_description (objoid, classoid, objsubid, description) SELECT pg_proc.oid, pg_class.oid, 0 , 'E-Maj internal function' FROM pg_catalog.pg_proc, pg_catalog.pg_class WHERE pg_class.relname = 'pg_proc' AND pg_proc.oid IN -- list all emaj functions that do not have yet a comment in pg_description (SELECT pg_proc.oid FROM pg_catalog.pg_proc JOIN pg_catalog.pg_namespace ON (pronamespace=pg_namespace.oid) LEFT OUTER JOIN pg_catalog.pg_description ON (pg_description.objoid = pg_proc.oid AND classoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_proc') AND objsubid = 0) WHERE nspname = 'emaj' AND (proname LIKE E'emaj\\_%' OR proname LIKE E'\\_%') AND pg_description.description IS NULL ); -- Update the version id in the emaj_param table. ALTER TABLE emaj.emaj_param DISABLE TRIGGER emaj_param_change_trg; UPDATE emaj.emaj_param SET param_value_text = '4.3.1' WHERE param_key = 'emaj_version'; ALTER TABLE emaj.emaj_param ENABLE TRIGGER emaj_param_change_trg; -- Insert the upgrade end record in the operation history. INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording) VALUES ('EMAJ_INSTALL','END','E-Maj 4.3.1', 'Upgrade from 4.3.0 completed'); -- Post installation checks. DO $tmp$ DECLARE BEGIN -- Check the max_prepared_transactions GUC value. IF current_setting('max_prepared_transactions')::int <= 1 THEN RAISE WARNING 'E-Maj upgrade: as the max_prepared_transactions parameter value (%) on this cluster is too low, no parallel rollback ' 'is possible.', current_setting('max_prepared_transactions'); END IF; END; $tmp$; RESET default_tablespace; SET client_min_messages TO default;