--
-- E-Maj: migration from 4.0.1 to 4.1.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_txid TEXT;
v_nbNoError INT;
v_nbWarning INT;
v_groupList TEXT;
BEGIN
-- The emaj version registered in emaj_param must be '4.0.1'.
SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version';
IF v_emajVersion <> '4.0.1' THEN
RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 4.0.1',v_emajVersion;
END IF;
-- The installed postgres version must be at least 9.5.
IF current_setting('server_version_num')::int < 90500 THEN
RAISE EXCEPTION 'E-Maj upgrade: the current PostgreSQL version (%) is not compatible with the new E-Maj version. The PostgreSQL version should be at least 9.5.', current_setting('server_version');
END IF;
-- Check the E-Maj environment state, if not yet done by a previous upgrade in the same transaction.
SELECT current_setting('emaj.upgrade_verify_txid', TRUE) INTO v_txid;
IF v_txid IS NULL OR v_txid <> txid_current()::TEXT THEN
BEGIN
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);
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;
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;
IF v_nbWarning IS NOT NULL THEN
PERFORM set_config('emaj.upgrade_verify_txid', txid_current()::TEXT, TRUE);
END IF;
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 4.1.0', 'Upgrade from 4.0.1 started');
-- Lock emaj_group table to avoid any concurrent E-Maj activity.
LOCK TABLE emaj.emaj_group IN EXCLUSIVE MODE;
-- Disable the event triggers 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 --
-- --
------------------------------------
------------------------------------
-- --
-- emaj functions --
-- --
------------------------------------
-- Recreate functions that have been previously dropped in the tables structure upgrade step and will not be recreated later in this script.
--<begin_functions> 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._verify_groups(p_groups TEXT[], p_onErrorStop BOOLEAN)
RETURNS SETOF emaj._verify_groups_type LANGUAGE plpgsql AS
$_verify_groups$
-- The function verifies the consistency of a tables groups array.
-- Input: - tables groups array,
-- - a boolean indicating whether the function has to raise an exception in case of detected unconsistency.
-- If onErrorStop boolean is false, it returns a set of _verify_groups_type records, one row per detected unconsistency, including
-- the faulting schema and table or sequence names and a detailed message.
-- If no error is detected, no row is returned.
-- This function may be directly called by the Emaj_web client.
DECLARE
v_hint CONSTANT TEXT = 'You may use "SELECT * FROM emaj.emaj_verify_all()" to look for other issues.';
r_object RECORD;
BEGIN
-- Note that there is no check that the supplied groups exist. This has already been done by all calling functions.
-- Let's start with some global checks that always raise an exception if an issue is detected.
-- Check the postgres version: E-Maj needs postgres 9.5+.
IF emaj._pg_version_num() < 90500 THEN
RAISE EXCEPTION '_verify_groups: The current postgres version (%) is not compatible with this E-Maj version.'
' It should be at least 9.5.', version();
END IF;
-- OK, now look for groups unconsistency.
-- Unlike emaj_verify_all(), there is no direct check that application schemas exist.
-- Check that all application relations referenced in the emaj_relation table still exist.
FOR r_object IN
SELECT t.rel_schema, t.rel_tblseq, r.rel_group,
'In group "' || r.rel_group || '", the ' ||
CASE WHEN t.rel_kind = 'r' THEN 'table "' ELSE 'sequence "' END ||
t.rel_schema || '"."' || t.rel_tblseq || '" does not exist any more.' AS msg
FROM
( SELECT rel_schema, rel_tblseq, rel_kind
FROM emaj.emaj_relation
WHERE rel_group = ANY (p_groups)
AND upper_inf(rel_time_range)
EXCEPT -- all relations known by postgres
SELECT nspname, relname, relkind::TEXT
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE relkind IN ('r','S')
) AS t, emaj.emaj_relation r -- join with emaj_relation to get the group name
WHERE t.rel_schema = r.rel_schema
AND t.rel_tblseq = r.rel_tblseq
AND upper_inf(r.rel_time_range)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (1): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that the log table for all tables referenced in the emaj_relation table still exist.
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the log table "' ||
rel_log_schema || '"."' || rel_log_table || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_log_schema
AND relname = rel_log_table
)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (2): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that the log function for each table referenced in the emaj_relation table still exists.
FOR r_object IN
-- the schema and table names are rebuilt from the returned function name
SELECT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the log function "' || rel_log_schema || '"."' || rel_log_function || '" is not found.'
AS msg
FROM emaj.emaj_relation
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r' AND upper_inf(rel_time_range)
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_proc
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pronamespace)
WHERE nspname = rel_log_schema
AND proname = rel_log_function
)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (3): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that log and truncate triggers for all tables referenced in the emaj_relation table still exist.
-- Start with the log trigger
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the log trigger "emaj_log_trg" on table "' ||
rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_class ON (pg_class.oid = tgrelid)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND tgname = 'emaj_log_trg'
)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (4): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Then the truncate trigger.
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the truncate trigger "emaj_trunc_trg" on table "' ||
rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_class ON (pg_class.oid = tgrelid)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND tgname = 'emaj_trunc_trg'
)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (5): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that all log tables have a structure consistent with the application tables they reference
-- (same columns and same formats). It only returns one row per faulting table.
FOR r_object IN
WITH cte_app_tables_columns AS -- application table's columns
(SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE attnum > 0
AND attisdropped = FALSE
AND rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
),
cte_log_tables_columns AS -- log table's columns
(SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_log_table)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_log_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE attnum > 0
AND NOT attisdropped
AND attnum < rel_emaj_verb_attnum
AND rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range))
SELECT DISTINCT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the structure of the application table "' ||
rel_schema || '"."' || rel_tblseq || '" is not coherent with its log table ("' ||
rel_log_schema || '"."' || rel_log_table || '").' AS msg
FROM
(
( -- application table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_app_tables_columns
EXCEPT -- minus log table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_log_tables_columns
)
UNION
( -- log table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_log_tables_columns
EXCEPT -- minus application table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_app_tables_columns
)
) AS t
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (6): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that all tables have their primary key if they belong to a rollbackable group.
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" has no primary key any more.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND group_is_rollbackable
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
JOIN pg_catalog.pg_constraint ON (connamespace = pg_namespace.oid AND conrelid = pg_class.oid)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND contype = 'p'
)
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (7): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- For rollbackable groups, check that no table has been altered as UNLOGGED or dropped and recreated as TEMP table after the tables
-- groups creation.
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" is UNLOGGED or TEMP.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND group_is_rollbackable
AND relpersistence <> 'p'
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (8): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- For rollbackable groups, with PG 11-, check that no table has been altered as WITH OIDS after tables groups creation.
IF emaj._pg_version_num() < 120000 THEN
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" is declared WITH OIDS.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND group_is_rollbackable
AND relhasoids
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (9): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
END IF;
-- Check that the primary key structure of all tables belonging to rollbackable groups is unchanged.
FOR r_object IN
SELECT rel_schema, rel_tblseq, rel_group,
'In rollbackable group "' || rel_group || '", the primary key of the table "' ||
rel_schema || '"."' || rel_tblseq || '" has changed (' ||
rel_sql_rlbk_pk_columns || ' => ' || current_pk_columns || ').' AS msg
FROM
(SELECT rel_schema, rel_tblseq, rel_group, rel_sql_rlbk_pk_columns,
string_agg(quote_ident(attname), ',' ORDER BY attnum) AS current_pk_columns
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
JOIN pg_catalog.pg_index ON (indrelid = pg_class.oid)
JOIN pg_catalog.pg_attribute ON (pg_attribute.attrelid = pg_index.indrelid)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND group_is_rollbackable
AND attnum = ANY (indkey)
AND indisprimary
AND attnum > 0
AND NOT attisdropped
GROUP BY rel_schema, rel_tblseq, rel_group, rel_sql_rlbk_pk_columns
) AS t
WHERE rel_sql_rlbk_pk_columns <> current_pk_columns
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (10): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
-- Check that all log tables have the 6 required technical columns. It only returns one row per faulting table.
FOR r_object IN
SELECT DISTINCT rel_schema, rel_tblseq, rel_group,
'In group "' || rel_group || '", the log table "' ||
rel_log_schema || '"."' || rel_log_table || '" miss some technical columns (' ||
string_agg(attname,', ') || ').' AS msg
FROM
( SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname
FROM emaj.emaj_relation,
(VALUES ('emaj_verb'), ('emaj_tuple'), ('emaj_gid'), ('emaj_changed'), ('emaj_txid'), ('emaj_user')) AS t(attname)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_log_schema
AND relname = rel_log_table
)
EXCEPT
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_log_table)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_log_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE rel_group = ANY (p_groups)
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
AND attnum > 0
AND attisdropped = FALSE
AND attname IN ('emaj_verb', 'emaj_tuple', 'emaj_gid', 'emaj_changed', 'emaj_txid', 'emaj_user')
) AS t2
GROUP BY rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table
ORDER BY 1,2,3
LOOP
IF p_onErrorStop THEN RAISE EXCEPTION '_verify_groups (11): % %',r_object.msg,v_hint; END IF;
RETURN NEXT r_object;
END LOOP;
--
RETURN;
END;
$_verify_groups$;
CREATE OR REPLACE FUNCTION emaj._estimate_rlbk_step_duration(p_step emaj._rlbk_step_enum, p_schema TEXT, p_table TEXT,
p_object TEXT, p_estimatedQuantity BIGINT,
p_defaultFixedCost INTERVAL, p_defaultVariableCost INTERVAL,
OUT p_estimateMethod INT, OUT p_estimatedDuration INTERVAL)
LANGUAGE plpgsql AS
$_estimate_rlbk_step_duration$
-- This function reads the rollback statistics in order to compute the duration estimate for elementary steps.
-- The function is called by _rlbk_planning().
-- The cost model depends on the step.
-- Input: step name, the schema, table and object names when it is relevant, the expected volume for the step,
-- the default fixed cost and the default variable cost from the emaj parameters
-- Output: the estimate method (1, 2 or 3), the duration estimate
BEGIN
-- Initialize the output data.
p_estimatedDuration = NULL;
-- Compute the duration estimate depending on the step.
CASE
WHEN p_step IN ('RLBK_TABLE', 'DELETE_LOG') THEN
-- For RLBK_TBL and DELETE_LOG, the estimate takes into account the estimated number of log rows to revert.
-- First look at the previous rollback durations for the table and with similar rollback volume (same order of magnitude).
SELECT sum(rlbt_duration) * (p_estimatedQuantity::float / sum(rlbt_quantity)), 1
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_quantity > 0
AND rlbt_schema = p_schema
AND rlbt_table = p_table
AND rlbt_quantity / p_estimatedQuantity < 10
AND p_estimatedQuantity / rlbt_quantity < 10;
IF p_estimatedDuration 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) * (p_estimatedQuantity::float / sum(rlbt_quantity)), 2
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_quantity > 0
AND rlbt_schema = p_schema
AND rlbt_table = p_table;
IF p_estimatedDuration IS NULL THEN
-- No statistics found for the step, so use supplied E-Maj parameters.
p_estimatedDuration = p_defaultVariableCost * p_estimatedQuantity + p_defaultFixedCost;
p_estimateMethod = 3;
END IF;
END IF;
--
WHEN p_step = 'ADD_FK' THEN
IF p_estimatedQuantity = 0 THEN
-- Empty table (or table not yet analyzed).
p_estimatedDuration = p_defaultFixedCost;
p_estimateMethod = 3;
ELSE
-- Non empty table and statistics (with at least one row) are available.
SELECT sum(rlbt_duration) * (p_estimatedQuantity::float / sum(rlbt_quantity)), 1
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_quantity > 0
AND rlbt_schema = p_schema
AND rlbt_table = p_table
AND rlbt_object = p_object;
IF p_estimatedDuration IS NULL THEN
-- Non empty table, but no statistic with at least one row is available => take the last duration for this fkey, if any.
SELECT rlbt_duration, 2
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_schema = p_schema
AND rlbt_table = p_table
AND rlbt_object = p_object
AND rlbt_rlbk_id =
(SELECT max(rlbt_rlbk_id)
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_schema = p_schema
AND rlbt_table = p_table
AND rlbt_object = p_object
);
IF p_estimatedDuration IS NULL THEN
-- Definitely no statistics available, compute with the supplied default parameters.
p_estimatedDuration = p_estimatedQuantity * p_defaultVariableCost + p_defaultFixedCost;
p_estimateMethod = 3;
END IF;
END IF;
END IF;
--
WHEN p_step = 'SET_FK_IMM' THEN
-- If fkey checks statistics are available for this fkey, compute an average cost.
SELECT sum(rlbt_duration) * (p_estimatedQuantity::float / sum(rlbt_quantity)), 2
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step
AND rlbt_quantity > 0
AND rlbt_schema = p_schema
AND rlbt_table = p_table
AND rlbt_object = p_object;
IF p_estimatedDuration IS NULL THEN
-- No statistics are available for this fkey, so use the supplied E-Maj parameters.
p_estimatedDuration = p_estimatedQuantity * p_defaultVariableCost + p_defaultFixedCost;
p_estimateMethod = 3;
END IF;
--
ELSE
-- For other steps, there is no volume to consider.
-- Read statistics, if any, and compute an average cost.
SELECT sum(rlbt_duration) / sum(rlbt_quantity), 2
INTO p_estimatedDuration, p_estimateMethod
FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = p_step;
IF p_estimatedDuration IS NULL THEN
-- No statistics found for the step, so use the supplied E-Maj parameters.
p_estimatedDuration = p_defaultFixedCost;
p_estimateMethod = 3;
END IF;
END CASE;
--
RETURN;
END;
$_estimate_rlbk_step_duration$;
CREATE OR REPLACE FUNCTION emaj._verify_all_groups()
RETURNS SETOF TEXT LANGUAGE plpgsql AS
$_verify_all_groups$
-- The function verifies the consistency of all E-Maj groups.
-- It returns a set of error or warning messages for discovered discrepancies.
-- If no error is detected, no row is returned.
BEGIN
--
-- Errors detection.
--
-- Check that the postgres version at groups creation time is compatible (i.e. >= 8.4).
RETURN QUERY
SELECT 'Error: The group "' || group_name || '" has been created with a non compatible postgresql version (' ||
group_pg_version || '). It must be dropped and recreated.' AS msg
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
ORDER BY msg;
-- Check that all application schemas referenced in the emaj_relation table still exist.
RETURN QUERY
SELECT 'Error: The application schema "' || rel_schema || '" does not exist any more.' AS msg
FROM
( SELECT DISTINCT rel_schema
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
EXCEPT
SELECT nspname
FROM pg_catalog.pg_namespace
) AS t
ORDER BY msg;
-- Check that all application relations referenced in the emaj_relation table still exist.
RETURN QUERY
SELECT 'Error: In the group "' || r.rel_group || '", the ' ||
CASE WHEN t.rel_kind = 'r' THEN 'table "' ELSE 'sequence "' END ||
t.rel_schema || '"."' || t.rel_tblseq || '" does not exist any more.' AS msg
FROM -- all expected application relations
( SELECT rel_schema, rel_tblseq, rel_kind
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
EXCEPT -- minus relations known by postgres
SELECT nspname, relname, relkind::TEXT
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE relkind IN ('r','S')
) AS t
JOIN emaj.emaj_relation r ON (t.rel_schema = r.rel_schema AND t.rel_tblseq = r.rel_tblseq AND upper_inf(r.rel_time_range))
ORDER BY t.rel_schema, t.rel_tblseq, 1;
-- Check that the log table for all tables referenced in the emaj_relation table still exist.
RETURN QUERY
SELECT 'Error: In the group "' || rel_group || '", the log table "' ||
rel_log_schema || '"."' || rel_log_table || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_log_schema
AND relname = rel_log_table
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Check that the log sequence for all tables referenced in the emaj_relation table still exist.
RETURN QUERY
SELECT 'Error: In the group "' || rel_group || '", the log sequence "' ||
rel_log_schema || '"."' || rel_log_sequence || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_log_schema AND relname = rel_log_sequence
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Check the log function for each table referenced in the emaj_relation table still exist.
RETURN QUERY
SELECT 'Error: In the group "' || rel_group || '", the log function "' ||
rel_log_schema || '"."' || rel_log_function || '" is not found.'
AS msg
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_proc
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pronamespace)
WHERE nspname = rel_log_schema
AND proname = rel_log_function
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Check log and truncate triggers for all tables referenced in the emaj_relation table still exist.
-- Start with log triggers.
RETURN QUERY
SELECT 'Error: In the group "' || rel_group || '", the log trigger "emaj_log_trg" on table "' ||
rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_class ON (pg_class.oid = tgrelid)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND tgname = 'emaj_log_trg'
)
-- do not issue a row if the application table does not exist,
-- this case has been already detected
AND EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Then truncate triggers.
RETURN QUERY
SELECT 'Error: In the group "' || rel_group || '", the truncate trigger "emaj_trunc_trg" on table "' ||
rel_schema || '"."' || rel_tblseq || '" is not found.' AS msg
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_class ON (pg_class.oid = tgrelid)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND tgname = 'emaj_trunc_trg'
)
-- do not issue a row if the application table does not exist,
-- this case has been already detected
AND EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Check that all log tables have a structure consistent with the application tables they reference
-- (same columns and same formats). It only returns one row per faulting table.
RETURN QUERY
SELECT msg FROM
(WITH cte_app_tables_columns AS -- application table's columns
(SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE attnum > 0
AND attisdropped = FALSE
AND upper_inf(rel_time_range)
AND rel_kind = 'r'
),
cte_log_tables_columns AS -- log table's columns
(SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_log_table)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_log_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE attnum > 0
AND attisdropped = FALSE
AND attnum < rel_emaj_verb_attnum
AND upper_inf(rel_time_range)
AND rel_kind = 'r'
)
SELECT DISTINCT rel_schema, rel_tblseq,
'Error: In the group "' || rel_group || '", the structure of the application table "' ||
rel_schema || '"."' || rel_tblseq || '" is not coherent with its log table ("' ||
rel_log_schema || '"."' || rel_log_table || '").' AS msg
FROM
( -- application table's columns
( SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_app_tables_columns
EXCEPT -- minus log table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_log_tables_columns
)
UNION -- log table's columns
( SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_log_tables_columns
EXCEPT -- minus application table's columns
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname, atttypid, attlen, atttypmod
FROM cte_app_tables_columns
)
) AS t
-- do not issue a row if the log or application table does not exist,
-- these cases have been already detected
WHERE (rel_log_schema, rel_log_table) IN
(SELECT nspname, relname
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
)
AND (rel_schema, rel_tblseq) IN
(SELECT nspname, relname
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
)
ORDER BY 1,2,3
) AS t;
-- Check that all tables of rollbackable groups have their primary key.
RETURN QUERY
SELECT 'Error: In the rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" has no primary key any more.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND group_is_rollbackable
AND NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
JOIN pg_catalog.pg_constraint ON (connamespace = pg_namespace.oid AND conrelid = pg_class.oid)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND contype = 'p'
)
-- do not issue a row if the application table does not exist,
-- this case has been already detected
AND EXISTS
(SELECT NULL
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
)
ORDER BY rel_schema, rel_tblseq, 1;
-- For rollbackable groups, check that no table has been altered as UNLOGGED or dropped and recreated as TEMP table after
-- tables groups creation.
RETURN QUERY
SELECT 'Error: In the rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" is UNLOGGED or TEMP.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND group_is_rollbackable
AND relpersistence <> 'p'
ORDER BY rel_schema, rel_tblseq, 1;
-- With PG 11-, check that all tables are WITHOUT OIDS (i.e. have not been altered as WITH OIDS after their tables group creation).
IF emaj._pg_version_num() < 120000 THEN
RETURN QUERY
SELECT 'Error: In the rollbackable group "' || rel_group || '", the table "' ||
rel_schema || '"."' || rel_tblseq || '" is WITH OIDS.' AS msg
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
WHERE upper_inf(rel_time_range)
AND rel_kind = 'r'
AND group_is_rollbackable
AND relhasoids
ORDER BY rel_schema, rel_tblseq, 1;
END IF;
-- Check the primary key structure of all tables belonging to rollbackable groups is unchanged.
RETURN QUERY
SELECT 'Error: In the rollbackable group "' || rel_group || '", the primary key of the table "' ||
rel_schema || '"."' || rel_tblseq || '" has changed (' ||
rel_sql_rlbk_pk_columns || ' => ' || current_pk_columns || ').' AS msg
FROM
(SELECT rel_schema, rel_tblseq, rel_group, rel_sql_rlbk_pk_columns,
string_agg(quote_ident(attname), ',' ORDER BY attnum) AS current_pk_columns
FROM emaj.emaj_relation
JOIN emaj.emaj_group ON (group_name = rel_group)
JOIN pg_catalog.pg_class ON (relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_schema)
JOIN pg_catalog.pg_index ON (indrelid = pg_class.oid)
JOIN pg_catalog.pg_attribute ON (pg_attribute.attrelid = pg_index.indrelid)
WHERE rel_kind = 'r'
AND upper_inf(rel_time_range)
AND group_is_rollbackable
AND attnum = ANY (indkey)
AND indisprimary
AND attnum > 0
AND attisdropped = FALSE
GROUP BY rel_schema, rel_tblseq, rel_group, rel_sql_rlbk_pk_columns
) AS t
WHERE rel_sql_rlbk_pk_columns <> current_pk_columns
ORDER BY rel_schema, rel_tblseq, 1;
-- Check the array of triggers to ignore at rollback time only contains existing triggers.
RETURN QUERY
SELECT 'Error: In the rollbackable group "' || rel_group || '", the trigger "' || trg_name || '" for table "'
|| rel_schema || '"."' || rel_tblseq || '" is missing. '
|| 'Use the emaj_modify_table() function to adjust the list of application triggers that should not be'
|| ' automatically disabled at rollback time.'
AS msg
FROM
(SELECT rel_group, rel_schema, rel_tblseq, unnest(rel_ignored_triggers) AS trg_name
FROM emaj.emaj_relation
WHERE upper_inf(rel_time_range)
AND rel_ignored_triggers IS NOT NULL
) AS t
WHERE NOT EXISTS
(SELECT NULL
FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_class ON (pg_class.oid = tgrelid)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace)
WHERE nspname = rel_schema
AND relname = rel_tblseq
AND tgname = trg_name
)
ORDER BY rel_schema, rel_tblseq, 1;
-- Check all log tables have the 6 required technical columns.
RETURN QUERY
SELECT msg FROM
(SELECT DISTINCT rel_schema, rel_tblseq,
'Error: In the group "' || rel_group || '", the log table "' ||
rel_log_schema || '"."' || rel_log_table || '" miss some technical columns (' ||
string_agg(attname,', ') || ').' AS msg
FROM
( SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname
FROM emaj.emaj_relation,
(VALUES ('emaj_verb'), ('emaj_tuple'), ('emaj_gid'), ('emaj_changed'), ('emaj_txid'), ('emaj_user')) AS t(attname)
WHERE rel_kind = 'r'
AND upper_inf(rel_time_range)
AND EXISTS
(SELECT NULL
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_class ON (relnamespace = pg_namespace.oid)
WHERE nspname = rel_log_schema
AND relname = rel_log_table
)
EXCEPT
SELECT rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table, attname
FROM emaj.emaj_relation
JOIN pg_catalog.pg_class ON (relname = rel_log_table)
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace AND nspname = rel_log_schema)
JOIN pg_catalog.pg_attribute ON (attrelid = pg_class.oid)
WHERE attnum > 0
AND attisdropped = FALSE
AND attname IN ('emaj_verb', 'emaj_tuple', 'emaj_gid', 'emaj_changed', 'emaj_txid', 'emaj_user')
AND rel_kind = 'r'
AND upper_inf(rel_time_range)
) AS t2
GROUP BY rel_group, rel_schema, rel_tblseq, rel_log_schema, rel_log_table
ORDER BY 1,2,3
) AS t;
--
-- Warnings detection.
--
-- Detect all sequences associated to a serial or a "generated as identity" column have their related table in the same group.
RETURN QUERY
SELECT msg FROM
(WITH serial_dependencies AS
(SELECT rs.rel_group AS seq_group, rs.rel_schema AS seq_schema, rs.rel_tblseq AS seq_name,
rt.rel_group AS tbl_group, nt.nspname AS tbl_schema, ct.relname AS tbl_name
FROM emaj.emaj_relation rs
JOIN pg_catalog.pg_class cs ON (cs.relname = rel_tblseq)
JOIN pg_catalog.pg_namespace ns ON (ns.oid = cs.relnamespace AND ns.nspname = rel_schema)
JOIN pg_catalog.pg_depend ON (pg_depend.objid = cs.oid)
JOIN pg_catalog.pg_class ct ON (ct.oid = pg_depend.refobjid)
JOIN pg_catalog.pg_namespace nt ON (nt.oid = ct.relnamespace)
LEFT OUTER JOIN emaj.emaj_relation rt ON (rt.rel_schema = nt.nspname AND rt.rel_tblseq = ct.relname
AND (rt.rel_time_range IS NULL OR upper_inf(rt.rel_time_range)))
WHERE rs.rel_kind = 'S'
AND upper_inf(rs.rel_time_range)
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'
)
)
SELECT DISTINCT seq_schema, seq_name,
'Warning: In the group "' || seq_group || '", the sequence "' || seq_schema || '"."' || seq_name ||
'" is linked to the table "' || tbl_schema || '"."' || tbl_name ||
'" but this table does not belong to any tables group.' AS msg
FROM serial_dependencies
WHERE tbl_group IS NULL
UNION ALL
SELECT DISTINCT seq_schema, seq_name,
'Warning: In the group "' || seq_group || '", the sequence "' || seq_schema || '"."' || seq_name ||
'" is linked to the table "' || tbl_schema || '"."' || tbl_name ||
'" but this table belongs to another tables group (' || tbl_group || ').' AS msg
FROM serial_dependencies
WHERE tbl_group <> seq_group
ORDER BY 1,2,3
) AS t;
-- Detect tables linked by a foreign key but not belonging to the same tables group.
RETURN QUERY
SELECT msg FROM
(WITH fk_dependencies AS -- all foreign keys that link 2 tables at least one of both belongs to a tables group
(SELECT n.nspname AS tbl_schema, t.relname AS tbl_name, c.conname, nf.nspname AS reftbl_schema, tf.relname AS reftbl_name,
r.rel_group AS tbl_group, g.group_is_rollbackable AS tbl_group_is_rollbackable,
rf.rel_group AS reftbl_group, gf.group_is_rollbackable AS reftbl_group_is_rollbackable
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class t ON (t.oid = c.conrelid)
JOIN pg_catalog.pg_namespace n ON (n.oid = t.relnamespace)
JOIN pg_catalog.pg_class tf ON (tf.oid = c.confrelid)
JOIN pg_catalog.pg_namespace nf ON (nf.oid = tf.relnamespace)
LEFT OUTER JOIN emaj.emaj_relation r ON (r.rel_schema = n.nspname AND r.rel_tblseq = t.relname
AND upper_inf(r.rel_time_range))
LEFT OUTER JOIN emaj.emaj_group g ON (g.group_name = r.rel_group)
LEFT OUTER JOIN emaj.emaj_relation rf ON (rf.rel_schema = nf.nspname AND rf.rel_tblseq = tf.relname
AND upper_inf(rf.rel_time_range))
LEFT OUTER JOIN emaj.emaj_group gf ON (gf.group_name = rf.rel_group)
WHERE contype = 'f' -- FK constraints only
AND (r.rel_group IS NOT NULL OR rf.rel_group IS NOT NULL) -- at least the table or the referenced table belongs to
-- a tables group
AND t.relkind = 'r' -- only constraint linking true tables, ie. excluding
AND tf.relkind = 'r' -- partitionned tables
)
SELECT tbl_schema, tbl_name,
'Warning: In the group "' || tbl_group || '", the foreign key "' || conname ||
'" on the table "' || tbl_schema || '"."' || tbl_name ||
'" references the table "' || reftbl_schema || '"."' || reftbl_name || '" that does not belong to any group.' AS msg
FROM fk_dependencies
WHERE tbl_group IS NOT NULL
AND tbl_group_is_rollbackable
AND reftbl_group IS NULL
UNION ALL
SELECT tbl_schema, tbl_name,
'Warning: In the group "' || reftbl_group || '", the table "' || reftbl_schema || '"."' || reftbl_name ||
'" is referenced by the foreign key "' || conname ||
'" of the table "' || tbl_schema || '"."' || tbl_name || '" that does not belong to any group.' AS msg
FROM fk_dependencies
WHERE reftbl_group IS NOT NULL
AND reftbl_group_is_rollbackable
AND tbl_group IS NULL
UNION ALL
SELECT tbl_schema, tbl_name,
'Warning: In the group "' || tbl_group || '", the foreign key "' || conname ||
'" on the table "' || tbl_schema || '"."' || tbl_name ||
'" references the table "' || reftbl_schema || '"."' || reftbl_name || '" that belongs to another group ("' ||
reftbl_group || '")' AS msg
FROM fk_dependencies
WHERE tbl_group IS NOT NULL
AND reftbl_group IS NOT NULL
AND tbl_group <> reftbl_group
AND (tbl_group_is_rollbackable OR reftbl_group_is_rollbackable)
ORDER BY 1,2,3
) AS t;
--
RETURN;
END;
$_verify_all_groups$;
--<end_functions> 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 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.1.0' 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.1.0', 'Upgrade from 4.0.1 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;