-- -- E-Maj: migration from 2.3.0 to 2.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_groupList TEXT; BEGIN -- check the current role is a superuser PERFORM 0 FROM pg_roles WHERE rolname = current_user AND rolsuper; IF NOT FOUND THEN RAISE EXCEPTION 'E-Maj upgrade: the current user (%) is not a superuser.', current_user; END IF; -- the emaj version registered in emaj_param must be '2.3.0' SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version'; IF v_emajVersion <> '2.3.0' THEN RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 2.3.0',v_emajVersion; END IF; -- the installed postgres version must be at least 9.2 IF current_setting('server_version_num')::int < 90200 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.2.', current_setting('server_version'); 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... -- lock emaj_group table to avoid any concurrent E-Maj activity LOCK TABLE emaj.emaj_group IN EXCLUSIVE MODE; -- disable the event triggers SELECT emaj._disable_event_triggers(); ---------------------------------------------- -- -- -- emaj enums, tables, views and sequences -- -- -- ---------------------------------------------- -- -- add created or recreated tables and sequences to the list of content to save by pg_dump -- ------------------------------------ -- -- -- emaj types -- -- -- ------------------------------------ ------------------------------------ -- -- -- emaj functions -- -- -- ------------------------------------ -- recreate functions that have been previously dropped in the tables structure upgrade step and will not be recreated later in this script -- pattern used by the tool that extracts and insert the functions definition ------------------------------------------------------------------ -- drop obsolete functions or functions with modified interface -- ------------------------------------------------------------------ ------------------------------------------------------------------ -- create new or modified functions -- ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION emaj._create_tbl(r_grpdef emaj.emaj_group_def, v_timeId BIGINT, v_isRollbackable BOOLEAN) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $_create_tbl$ -- This function creates all what is needed to manage the log and rollback operations for an application table -- Input: the emaj_group_def row related to the application table to process, the time id of the operation, a boolean indicating whether the group is rollbackable -- Are created in the log schema: -- - the associated log table, with its own sequence -- - the function that logs the tables updates, defined as a trigger -- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of the application table. DECLARE v_emajSchema TEXT = 'emaj'; v_schemaPrefix TEXT = 'emaj'; v_emajNamesPrefix TEXT; v_baseLogTableName TEXT; v_baseLogIdxName TEXT; v_baseLogFnctName TEXT; v_baseSequenceName TEXT; v_logSchema TEXT; v_fullTableName TEXT; v_logTableName TEXT; v_logIdxName TEXT; v_logFnctName TEXT; v_sequenceName TEXT; v_dataTblSpace TEXT; v_idxTblSpace TEXT; v_colList TEXT; v_pkColList TEXT; v_pkCondList TEXT; v_alter_log_table_param TEXT; v_stmt TEXT; v_triggerList TEXT; BEGIN -- the checks on the table properties are performed by the calling functions -- build the prefix of all emaj object to create, by default _ v_emajNamesPrefix = coalesce(r_grpdef.grpdef_emaj_names_prefix, r_grpdef.grpdef_schema || '_' || r_grpdef.grpdef_tblseq); -- build the name of emaj components associated to the application table (non schema qualified and not quoted) v_baseLogTableName = v_emajNamesPrefix || '_log'; v_baseLogIdxName = v_emajNamesPrefix || '_log_idx'; v_baseLogFnctName = v_emajNamesPrefix || '_log_fnct'; v_baseSequenceName = v_emajNamesPrefix || '_log_seq'; -- build the different name for table, trigger, functions,... v_logSchema = coalesce(v_schemaPrefix || r_grpdef.grpdef_log_schema_suffix, v_emajSchema); v_fullTableName = quote_ident(r_grpdef.grpdef_schema) || '.' || quote_ident(r_grpdef.grpdef_tblseq); v_logTableName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseLogTableName); v_logIdxName = quote_ident(v_baseLogIdxName); v_logFnctName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseLogFnctName); v_sequenceName = quote_ident(v_logSchema) || '.' || quote_ident(v_baseSequenceName); -- prepare TABLESPACE clauses for data and index v_dataTblSpace = coalesce('TABLESPACE ' || quote_ident(r_grpdef.grpdef_log_dat_tsp),''); v_idxTblSpace = coalesce('TABLESPACE ' || quote_ident(r_grpdef.grpdef_log_idx_tsp),''); -- Build some pieces of SQL statements that will be needed at table rollback time -- build the tables's columns list SELECT string_agg(col_name, ',') INTO v_colList FROM ( SELECT 'tbl.' || quote_ident(attname) AS col_name FROM pg_catalog.pg_attribute WHERE attrelid = v_fullTableName::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum) AS t; -- build the pkey columns list and the "equality on the primary key" conditions SELECT string_agg(col_pk_name, ','), string_agg(col_pk_cond, ' AND ') INTO v_pkColList, v_pkCondList FROM ( SELECT quote_ident(attname) AS col_pk_name, 'tbl.' || quote_ident(attname) || ' = keys.' || quote_ident(attname) AS col_pk_cond FROM pg_catalog.pg_attribute, pg_catalog.pg_index WHERE pg_attribute.attrelid = pg_index.indrelid AND attnum = ANY (indkey) AND indrelid = v_fullTableName::regclass AND indisprimary AND attnum > 0 AND attisdropped = FALSE ORDER BY attnum) AS t; -- create the log table: it looks like the application table, with some additional technical columns EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName; EXECUTE 'CREATE TABLE ' || v_logTableName || ' (LIKE ' || v_fullTableName || ') ' || v_dataTblSpace; EXECUTE 'ALTER TABLE ' || v_logTableName || ' ADD COLUMN emaj_verb VARCHAR(3),' || ' ADD COLUMN emaj_tuple VARCHAR(3),' || ' ADD COLUMN emaj_gid BIGINT NOT NULL DEFAULT nextval(''emaj.emaj_global_seq''),' || ' ADD COLUMN emaj_changed TIMESTAMPTZ DEFAULT clock_timestamp(),' || ' ADD COLUMN emaj_txid BIGINT DEFAULT txid_current(),' || ' ADD COLUMN emaj_user VARCHAR(32) DEFAULT session_user,' || ' ADD COLUMN emaj_user_ip INET DEFAULT inet_client_addr(),' || ' ADD COLUMN emaj_user_port INT DEFAULT inet_client_port()'; -- adjust the log table structure with the alter_log_table parameter, if set SELECT param_value_text INTO v_alter_log_table_param FROM emaj.emaj_param WHERE param_key = ('alter_log_table'); IF v_alter_log_table_param IS NOT NULL AND v_alter_log_table_param <> '' THEN EXECUTE 'ALTER TABLE ' || v_logTableName || ' ' || v_alter_log_table_param; END IF; -- create the index on the log table EXECUTE 'CREATE UNIQUE INDEX ' || v_logIdxName || ' ON ' || v_logTableName || ' (emaj_gid, emaj_tuple) ' || v_idxTblSpace; -- set the index associated to the primary key as cluster index. It may be useful for CLUSTER command. EXECUTE 'ALTER TABLE ONLY ' || v_logTableName || ' CLUSTER ON ' || v_logIdxName; -- remove the NOT NULL constraints of application columns. -- They are useless and blocking to store truncate event for tables belonging to audit_only tables SELECT string_agg(action, ',') INTO v_stmt FROM ( SELECT ' ALTER COLUMN ' || quote_ident(attname) || ' DROP NOT NULL' AS action FROM pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace WHERE relnamespace = pg_namespace.oid AND attrelid = pg_class.oid AND nspname = v_logSchema AND relname = v_baseLogTableName AND attnum > 0 AND attnotnull AND attisdropped = FALSE AND attname NOT LIKE E'emaj\\_%') AS t; IF v_stmt IS NOT NULL THEN EXECUTE 'ALTER TABLE ' || v_logTableName || v_stmt; END IF; -- create the sequence associated to the log table EXECUTE 'CREATE SEQUENCE ' || v_sequenceName; -- create the log function and the log trigger PERFORM emaj._create_log_trigger(v_fullTableName, v_logTableName, v_sequenceName, v_logFnctName); -- Deactivate the log trigger (it will be enabled at emaj_start_group time) EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_log_trg'; -- creation of the trigger that manage any TRUNCATE on the application table -- But the trigger is not immediately activated (it will be at emaj_start_group time) EXECUTE 'DROP TRIGGER IF EXISTS emaj_trunc_trg ON ' || v_fullTableName; IF v_isRollbackable THEN -- For rollbackable groups, use the common _forbid_truncate_fnct() function that blocks the operation EXECUTE 'CREATE TRIGGER emaj_trunc_trg' || ' BEFORE TRUNCATE ON ' || v_fullTableName || ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._forbid_truncate_fnct()'; ELSE -- For audit_only groups, use the common _log_truncate_fnct() function that records the operation into the log table EXECUTE 'CREATE TRIGGER emaj_trunc_trg' || ' BEFORE TRUNCATE ON ' || v_fullTableName || ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._log_truncate_fnct()'; END IF; EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER emaj_trunc_trg'; -- register the table into emaj_relation INSERT INTO emaj.emaj_relation (rel_schema, rel_tblseq, rel_time_range, rel_group, rel_priority, rel_log_schema, rel_log_dat_tsp, rel_log_idx_tsp, rel_kind, rel_log_table, rel_log_index, rel_log_sequence, rel_log_function, rel_sql_columns, rel_sql_pk_columns, rel_sql_pk_eq_conditions) VALUES (r_grpdef.grpdef_schema, r_grpdef.grpdef_tblseq, int8range(v_timeId, NULL, '[)'), r_grpdef.grpdef_group, r_grpdef.grpdef_priority, v_logSchema, r_grpdef.grpdef_log_dat_tsp, r_grpdef.grpdef_log_idx_tsp, 'r', v_baseLogTableName, v_baseLogIdxName, v_baseSequenceName, v_baseLogFnctName, v_colList, v_pkColList, v_pkCondList); -- -- check if the table has (neither internal - ie. created for fk - nor previously created by emaj) trigger SELECT string_agg(tgname, ', ' ORDER BY tgname) INTO v_triggerList FROM ( SELECT tgname FROM pg_catalog.pg_trigger WHERE tgrelid = v_fullTableName::regclass AND tgconstraint = 0 AND tgname NOT LIKE E'emaj\\_%\\_trg') AS t; -- if yes, issue a warning (if a trigger updates another table in the same table group or outside) it could generate problem at rollback time) IF v_triggerList IS NOT NULL THEN RAISE WARNING '_create_tbl: The table "%" has triggers (%). Verify the compatibility with emaj rollback operations (in particular if triggers update one or several other tables). Triggers may have to be manualy disabled before rollback.', v_fullTableName, v_triggerList; END IF; -- grant appropriate rights to both emaj roles EXECUTE 'GRANT SELECT ON TABLE ' || v_logTableName || ' TO emaj_viewer'; EXECUTE 'GRANT ALL PRIVILEGES ON TABLE ' || v_logTableName || ' TO emaj_adm'; EXECUTE 'GRANT SELECT ON SEQUENCE ' || v_sequenceName || ' TO emaj_viewer'; EXECUTE 'GRANT ALL PRIVILEGES ON SEQUENCE ' || v_sequenceName || ' TO emaj_adm'; RETURN; END; $_create_tbl$; CREATE OR REPLACE FUNCTION emaj._gen_sql_tbl(r_rel emaj.emaj_relation, v_conditions TEXT) RETURNS BIGINT LANGUAGE plpgsql SECURITY DEFINER SET standard_conforming_strings = ON AS $_gen_sql_tbl$ -- This function generates SQL commands representing all updates performed on a table between 2 marks -- or beetween a mark and the current situation. These command are stored into a temporary table created -- by the _gen_sql_groups() calling function. -- Input: row from emaj_relation corresponding to the appplication table to proccess, -- sql conditions corresponding to the marks range to process -- Output: number of generated SQL statements DECLARE v_fullTableName TEXT; v_logTableName TEXT; v_valList TEXT; v_setList TEXT; v_pkCondList TEXT; v_unquotedType TEXT[] = array['smallint','integer','bigint','numeric','decimal', 'int2','int4','int8','serial','bigserial', 'real','double precision','float','float4','float8','oid']; v_rqInsert TEXT; v_rqUpdate TEXT; v_rqDelete TEXT; v_rqTruncate TEXT; v_nbSQL BIGINT; r_col RECORD; BEGIN -- build schema specified table name and log table name v_fullTableName = quote_ident(r_rel.rel_schema) || '.' || quote_ident(r_rel.rel_tblseq); v_logTableName = quote_ident(r_rel.rel_log_schema) || '.' || quote_ident(r_rel.rel_log_table); -- retrieve from pg_attribute all columns of the application table and build : -- - the VALUES list used in the INSERT statements -- - the SET list used in the UPDATE statements v_valList = ''; v_setList = ''; FOR r_col IN SELECT attname, format_type(atttypid,atttypmod) FROM pg_catalog.pg_attribute WHERE attrelid = v_fullTableName ::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum LOOP -- test if the column format (up to the parenthesis) belongs to the list of formats that do not require any quotation (like numeric data types) IF regexp_replace(r_col.format_type,E'\\(.*$','') = ANY(v_unquotedType) THEN -- literal for this column can remain as is v_valList = v_valList || ''' || coalesce(o.' || quote_ident(r_col.attname) || '::TEXT,''NULL'') || '', '; v_setList = v_setList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || coalesce(n.' || quote_ident(r_col.attname) || ' ::TEXT,''NULL'') || '', '; ELSE -- literal for this column must be quoted v_valList = v_valList || ''' || quote_nullable(o.' || quote_ident(r_col.attname) || ') || '', '; v_setList = v_setList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_nullable(n.' || quote_ident(r_col.attname) || ') || '', '; END IF; END LOOP; -- suppress the final separators v_valList = substring(v_valList FROM 1 FOR char_length(v_valList) - 2); v_setList = substring(v_setList FROM 1 FOR char_length(v_setList) - 2); -- retrieve all columns that represents the pkey and build the "pkey equal" conditions set that will be used in UPDATE and DELETE statements -- (taking column names in pg_attribute from the table's definition instead of index definition is mandatory -- starting from pg9.0, joining tables with indkey instead of indexrelid) v_pkCondList = ''; FOR r_col IN SELECT attname, format_type(atttypid,atttypmod) FROM pg_catalog.pg_attribute, pg_catalog.pg_index WHERE pg_attribute.attrelid = pg_index.indrelid AND attnum = ANY (indkey) AND indrelid = v_fullTableName::regclass AND indisprimary AND attnum > 0 AND NOT attisdropped LOOP -- test if the column format (at least up to the parenthesis) belongs to the list of formats that do not require any quotation (like numeric data types) IF regexp_replace (r_col.format_type,E'\\(.*$','') = ANY(v_unquotedType) THEN -- literal for this column can remain as is -- v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || o.' || quote_ident(r_col.attname) || ' || ''::' || r_col.format_type || ' AND '; v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || o.' || quote_ident(r_col.attname) || ' || '' AND '; ELSE -- literal for this column must be quoted -- v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_literal(o.' || quote_ident(r_col.attname) || ') || ''::' || r_col.format_type || ' AND '; v_pkCondList = v_pkCondList || quote_ident(replace(r_col.attname,'''','''''')) || ' = '' || quote_literal(o.' || quote_ident(r_col.attname) || ') || '' AND '; END IF; END LOOP; -- suppress the final separator v_pkCondList = substring(v_pkCondList FROM 1 FOR char_length(v_pkCondList) - 5); -- prepare sql skeletons for each statement type v_rqInsert = '''INSERT INTO ' || replace(v_fullTableName,'''','''''') || ' VALUES (' || v_valList || ');'''; v_rqUpdate = '''UPDATE ONLY ' || replace(v_fullTableName,'''','''''') || ' SET ' || v_setList || ' WHERE ' || v_pkCondList || ';'''; v_rqDelete = '''DELETE FROM ONLY ' || replace(v_fullTableName,'''','''''') || ' WHERE ' || v_pkCondList || ';'''; v_rqTruncate = '''TRUNCATE ' || replace(v_fullTableName,'''','''''') || ';'''; -- now scan the log table to process all statement types at once EXECUTE 'INSERT INTO emaj_temp_script ' || 'SELECT o.emaj_gid, 0, o.emaj_txid, CASE ' || ' WHEN o.emaj_verb = ''INS'' THEN ' || v_rqInsert || ' WHEN o.emaj_verb = ''UPD'' AND o.emaj_tuple = ''OLD'' THEN ' || v_rqUpdate || ' WHEN o.emaj_verb = ''DEL'' THEN ' || v_rqDelete || ' WHEN o.emaj_verb = ''TRU'' THEN ' || v_rqTruncate || ' END ' || ' FROM ' || v_logTableName || ' o' || ' LEFT OUTER JOIN ' || v_logTableName || ' n ON n.emaj_gid = o.emaj_gid' || ' AND (n.emaj_verb = ''UPD'' AND n.emaj_tuple = ''NEW'') ' || ' WHERE NOT (o.emaj_verb = ''UPD'' AND o.emaj_tuple = ''NEW'')' || ' AND ' || v_conditions; GET DIAGNOSTICS v_nbSQL = ROW_COUNT; RETURN v_nbSQL; END; $_gen_sql_tbl$; -- 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; ------------------------------------ -- -- -- extension configuration -- -- -- ------------------------------------ SELECT pg_catalog.pg_extension_config_dump('emaj_hist','WHERE hist_id > 1'); SELECT pg_catalog.pg_extension_config_dump('emaj_schema','WHERE sch_name <> ''emaj'''); ------------------------------------ -- -- -- complete the upgrade -- -- -- ------------------------------------ -- enable the event triggers DO $tmp$ DECLARE v_event_trigger_array TEXT[]; BEGIN IF emaj._pg_version_num() >= 90300 THEN -- 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 IF; 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 UPDATE emaj.emaj_param SET param_value_text = '2.3.1' WHERE param_key = 'emaj_version'; -- insert the upgrade record in the operation history INSERT INTO emaj.emaj_hist (hist_function, hist_object, hist_wording) VALUES ('EMAJ_INSTALL','E-Maj 2.3.1', 'Upgrade from 2.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;