-- Fixed bug in partition_data_time() that would cause an infinite loop when moving data would require creating a new partition newer than the newest one. This would only occur when using a custom time interval. Infinite loop would also occur when using the partition_data.py python script (Github Issue #83). -- Properly handle the special PUBLIC role when granting/revoking privileges on child tables (Github Issue #66). -- Fixed python exception in reapply_indexes.py if the parent table has no indexes (Github Issue #86). -- Fixed all undo_partition functions to remove the child table in the same batch session when its rowcount reaches zero. Previously an extra batch may have been required to remove all child tables and remove config information after the last row was moved to the parent. -- Fixed undo_partition functions to be more strict on the triggers they drop and ensure they only drop the trigger on the target parent table. -- Fixed undo_partition() to properly remove entries from the custom_time_partitions config table if necessary. Also greatly simplified code in this function. -- Consolidated privilege management into new apply_privileges() internal function. -- Improved performance when applying parent privileges to child tables. Most noticable on larger partition sets with many grants. (Github Issue #78) -- Added a sort to the Makefile when creating the sql extension file. Allows more predictable output between builds (Github Push Request #16 from Mimeo extension). /* * Apply privileges that exist on a given parent to the given child table */ CREATE FUNCTION apply_privileges(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text, p_job_id bigint DEFAULT NULL) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_child_grant record; v_child_owner text; v_grantees text[]; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_match boolean; v_parent_grant record; v_parent_owner text; v_revoke text; v_row_revoke record; v_sql text; v_step_id bigint; BEGIN SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_schema ||'.'|| p_parent_tablename; IF v_jobmon IS NULL THEN RAISE EXCEPTION 'Given table is not managed by this extention: %.%', p_parent_schema, p_parent_tablename; END IF; SELECT tableowner INTO v_parent_owner FROM pg_catalog.pg_tables WHERE schemaname = p_parent_schema AND tablename = p_parent_tablename; SELECT tableowner INTO v_child_owner FROM pg_tables WHERE schemaname = p_child_schema AND tablename = p_child_tablename; IF v_parent_owner IS NULL THEN RAISE EXCEPTION 'Given parent table does not exist: %.%', v_parent_schema, v_parent_tablename; END IF; IF v_child_owner IS NULL THEN RAISE EXCEPTION 'Given child table does not exist: %.%', v_child_schema, v_child_tablename; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; END IF; IF v_jobmon_schema IS NOT NULL THEN IF p_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN APPLYING PRIVILEGES TO CHILD TABLE: %s.%s', p_child_schema, p_child_tablename)) INTO v_job_id; ELSE v_job_id := p_job_id; END IF; EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, format('Setting new child table privileges for %s.%s', p_child_schema, p_child_tablename)) INTO v_step_id; END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.update_step(%L, %L, %L)' , v_jobmon_schema , v_step_id , 'PENDING' , format('Applying privileges on child partition: %s.%s' , p_child_schema , p_child_tablename) ); END IF; FOR v_parent_grant IN SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types , grantee FROM information_schema.table_privileges WHERE table_schema = p_parent_schema AND table_name = p_parent_tablename GROUP BY grantee LOOP -- Compare parent & child grants. Don't re-apply if it already exists v_match := false; v_sql := NULL; FOR v_child_grant IN SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types , grantee FROM information_schema.table_privileges WHERE table_schema = p_child_schema AND table_name = p_child_tablename GROUP BY grantee LOOP IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN v_match := true; END IF; END LOOP; IF v_match = false THEN IF v_parent_grant.grantee = 'PUBLIC' THEN v_sql := 'GRANT %s ON %I.%I TO %s'; ELSE v_sql := 'GRANT %s ON %I.%I TO %I'; END IF; EXECUTE format(v_sql , array_to_string(v_parent_grant.types, ',') , p_child_schema , p_child_tablename , v_parent_grant.grantee); v_sql := NULL; SELECT string_agg(r, ',') INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; IF v_revoke IS NOT NULL THEN IF v_parent_grant.grantee = 'PUBLIC' THEN v_sql := 'REVOKE %s ON %I.%I FROM %s CASCADE'; ELSE v_sql := 'REVOKE %s ON %I.%I FROM %I CASCADE'; END IF; EXECUTE format(v_sql , v_revoke , p_child_schema , p_child_tablename , v_parent_grant.grantee); v_sql := NULL; END IF; END IF; v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); END LOOP; -- Revoke all privileges from roles that have none on the parent IF v_grantees IS NOT NULL THEN FOR v_row_revoke IN SELECT role FROM ( SELECT DISTINCT grantee::text AS role FROM information_schema.table_privileges WHERE table_schema = p_child_schema AND table_name = p_child_tablename EXCEPT SELECT unnest(v_grantees)) x LOOP IF v_row_revoke.role IS NOT NULL THEN IF v_row_revoke.role = 'PUBLIC' THEN v_sql := 'REVOKE ALL ON %I.%I FROM %s'; ELSE v_sql := 'REVOKE ALL ON %I.%I FROM %I'; END IF; EXECUTE format(v_sql , p_child_schema , p_child_tablename , v_row_revoke.role); END IF; END LOOP; END IF; IF v_parent_owner <> v_child_owner THEN EXECUTE format('ALTER TABLE %I.%I OWNER TO %I' , p_child_schema , p_child_tablename , v_parent_owner); END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_id, 'OK', 'Done'); IF p_job_id IS NULL THEN EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); END IF; END IF; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Function to re-apply ownership & privileges on all child tables in a partition set using parent table as reference */ CREATE OR REPLACE FUNCTION reapply_privileges(p_parent_table text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_parent_schema text; v_parent_tablename text; v_row record; v_step_id bigint; BEGIN SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_jobmon IS NULL THEN RAISE EXCEPTION 'Given table is not managed by this extention: %', p_parent_table; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; END IF; SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; IF v_parent_tablename IS NULL THEN RAISE EXCEPTION 'Given parent table does not exist: %', p_parent_table; END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s', p_parent_table)) INTO v_job_id; END IF; FOR v_row IN SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') LOOP PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_row.partition_schemaname, v_row.partition_tablename, v_job_id); END LOOP; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); END IF; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Populate the child table(s) of a time-based partition set with old data from the original parent */ CREATE OR REPLACE FUNCTION partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0, p_order text DEFAULT 'ASC') RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_control text; v_datetime_string text; v_current_partition_name text; v_epoch boolean; v_last_partition text; v_lock_iter int := 1; v_lock_obtained boolean := FALSE; v_max_partition_timestamp timestamp; v_min_partition_timestamp timestamp; v_parent_schema text; v_parent_tablename text; v_partition_interval interval; v_partition_suffix text; v_partition_timestamp timestamp[]; v_quarter text; v_rowcount bigint; v_sql text; v_start_control timestamp; v_time_position int; v_total_rows bigint := 0; v_type text; v_year text; BEGIN SELECT partition_type , partition_interval::interval , control , datetime_string , epoch INTO v_type , v_partition_interval , v_control , v_datetime_string , v_epoch FROM @extschema@.part_config WHERE parent_table = p_parent_table AND (partition_type = 'time' OR partition_type = 'time-custom'); IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN p_batch_interval := v_partition_interval; END IF; SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; FOR i IN 1..p_batch_count LOOP IF v_epoch = false THEN IF p_order = 'ASC' THEN EXECUTE format('SELECT min(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSIF p_order = 'DESC' THEN EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSE RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; END IF; ELSE IF p_order = 'ASC' THEN EXECUTE format('SELECT to_timestamp(min(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSIF p_order = 'DESC' THEN EXECUTE format('SELECT to_timestamp(max(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSE RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; END IF; END IF; IF v_start_control IS NULL THEN EXIT; END IF; IF v_type = 'time' THEN CASE WHEN v_partition_interval = '15 mins' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control) + '15min'::interval * floor(date_part('minute', v_start_control) / 15.0); WHEN v_partition_interval = '30 mins' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control) + '30min'::interval * floor(date_part('minute', v_start_control) / 30.0); WHEN v_partition_interval = '1 hour' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control); WHEN v_partition_interval = '1 day' THEN v_min_partition_timestamp := date_trunc('day', v_start_control); WHEN v_partition_interval = '1 week' THEN v_min_partition_timestamp := date_trunc('week', v_start_control); WHEN v_partition_interval = '1 month' THEN v_min_partition_timestamp := date_trunc('month', v_start_control); WHEN v_partition_interval = '3 months' THEN v_min_partition_timestamp := date_trunc('quarter', v_start_control); WHEN v_partition_interval = '1 year' THEN v_min_partition_timestamp := date_trunc('year', v_start_control); END CASE; ELSIF v_type = 'time-custom' THEN v_time_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; v_min_partition_timestamp := to_timestamp(substring(v_last_partition from v_time_position), v_datetime_string); v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; LOOP IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN EXIT; ELSE BEGIN IF v_start_control > v_max_partition_timestamp THEN -- Keep going forward in time, checking if child partition time interval encompasses the current v_start_control value v_min_partition_timestamp := v_max_partition_timestamp; v_max_partition_timestamp := v_max_partition_timestamp + v_partition_interval; ELSE -- Keep going backwards in time, checking if child partition time interval encompasses the current v_start_control value v_max_partition_timestamp := v_min_partition_timestamp; v_min_partition_timestamp := v_min_partition_timestamp - v_partition_interval; END IF; EXCEPTION WHEN datetime_field_overflow THEN RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range. Unable to create partition with interval before timestamp % ', v_min_partition_interval; END; END IF; END LOOP; END IF; v_partition_timestamp := ARRAY[v_min_partition_timestamp]; IF p_order = 'ASC' THEN -- Ensure batch interval given as parameter doesn't cause maximum to overflow the current partition maximum IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_partition_interval) THEN v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; ELSE v_max_partition_timestamp := v_start_control + p_batch_interval; END IF; ELSIF p_order = 'DESC' THEN -- Must be greater than max value still in parent table since query below grabs < max v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; -- Ensure batch interval given as parameter doesn't cause minimum to underflow current partition minimum IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN v_min_partition_timestamp = v_start_control - p_batch_interval; END IF; ELSE RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; END IF; -- do some locking with timeout, if required IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN IF v_epoch = false THEN v_sql := format('SELECT * FROM ONLY %I.%I WHERE %I >= %L AND %I < %L FOR UPDATE NOWAIT' , v_parent_schema , v_parent_tablename , v_control , v_min_partition_timestamp , v_control , v_max_partition_timestamp); ELSE v_sql := format('SELECT * FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L FOR UPDATE NOWAIT' , v_parent_schema , v_parent_tablename , v_control , v_min_partition_timestamp , v_control , v_max_partition_timestamp); END IF; EXECUTE v_sql; v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RETURN -1; END IF; END IF; PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp); -- This suffix generation code is in create_partition_time() as well v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string); v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); IF v_epoch = false THEN v_sql := format('WITH partition_data AS ( DELETE FROM ONLY %I.%I WHERE %I >= %L AND %I < %L RETURNING *) INSERT INTO %I.%I SELECT * FROM partition_data' , v_parent_schema , v_parent_tablename , v_control , v_min_partition_timestamp , v_control , v_max_partition_timestamp , v_parent_schema , v_current_partition_name); ELSE v_sql := format('WITH partition_data AS ( DELETE FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L RETURNING *) INSERT INTO %I.%I SELECT * FROM partition_data' , v_parent_schema , v_parent_tablename , v_control , v_min_partition_timestamp , v_control , v_max_partition_timestamp , v_parent_schema , v_current_partition_name); END IF; EXECUTE v_sql; GET DIAGNOSTICS v_rowcount = ROW_COUNT; v_total_rows := v_total_rows + v_rowcount; IF v_rowcount = 0 THEN EXIT; END IF; END LOOP; PERFORM @extschema@.create_function_time(p_parent_table); RETURN v_total_rows; END $$; /* * Function to create id partitions */ CREATE OR REPLACE FUNCTION create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_analyze boolean := FALSE; v_control text; v_exists text; v_grantees text[]; v_hasoids boolean; v_id bigint; v_inherit_fk boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_old_search_path text; v_parent_grant record; v_parent_owner text; v_parent_schema text; v_parent_tablename text; v_parent_tablespace text; v_partition_interval bigint; v_partition_created boolean := false; v_partition_name text; v_revoke text; v_row record; v_sql text; v_step_id bigint; v_sub_id_max bigint; v_sub_id_min bigint; v_unlogged char; BEGIN SELECT control , partition_interval , inherit_fk , jobmon INTO v_control , v_partition_interval , v_inherit_fk , v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table AND partition_type = 'id'; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); END IF; END IF; -- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); END IF; FOREACH v_id IN ARRAY p_partition_ids LOOP -- Do not create the child table if it's outside the bounds of the top parent. IF v_sub_id_min IS NOT NULL THEN IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN CONTINUE; END IF; END IF; v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); -- If child table already exists, skip creation SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; IF v_exists IS NOT NULL THEN CONTINUE; END IF; -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped v_analyze := TRUE; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_id||' to '||(v_id + v_partition_interval)-1); END IF; SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename AND n.nspname = v_parent_schema; v_sql := 'CREATE'; IF v_unlogged = 'u' THEN v_sql := v_sql || ' UNLOGGED'; END IF; v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename AND n.nspname = v_parent_schema; IF v_hasoids IS TRUE THEN v_sql := v_sql || ' WITH (OIDS)'; END IF; EXECUTE v_sql; IF v_parent_tablespace IS NOT NULL THEN EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); END IF; EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %s AND %I < %s )' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_control , v_id , v_control , v_id + v_partition_interval); EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I', v_parent_schema, v_partition_name, v_parent_schema, v_parent_tablename); PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); IF v_inherit_fk THEN PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; -- Will only loop once and only if sub_partitioning is actually configured -- This seemed easier than assigning a bunch of variables then doing an IF condition FOR v_row IN SELECT sub_parent , sub_control , sub_partition_type , sub_partition_interval , sub_constraint_cols , sub_premake , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_use_run_maintenance , sub_epoch , sub_optimize_trigger , sub_optimize_constraint , sub_jobmon FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table LOOP IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); END IF; v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_type := %L , p_interval := %L , p_constraint_cols := %L , p_premake := %L , p_use_run_maintenance := %L , p_inherit_fk := %L , p_epoch := %L , p_jobmon := %L )' , v_parent_schema||'.'||v_partition_name , v_row.sub_control , v_row.sub_partition_type , v_row.sub_partition_interval , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_use_run_maintenance , v_row.sub_inherit_fk , v_row.sub_epoch , v_row.sub_jobmon); EXECUTE v_sql; UPDATE @extschema@.part_config SET retention_schema = v_row.sub_retention_schema , retention_keep_table = v_row.sub_retention_keep_table , retention_keep_index = v_row.sub_retention_keep_index , optimize_trigger = v_row.sub_optimize_trigger , optimize_constraint = v_row.sub_optimize_constraint WHERE parent_table = v_parent_schema||'.'||v_partition_name; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END LOOP; -- end sub partitioning LOOP v_partition_created := true; END LOOP; -- v_analyze is a local check if a new table is made. -- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. IF v_analyze AND p_analyze THEN IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); END IF; EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN IF v_partition_created = false THEN v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; PERFORM close_job(v_job_id); END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END IF; RETURN v_partition_created; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Function to create a child table in a time-based partition set */ CREATE OR REPLACE FUNCTION create_partition_time(p_parent_table text, p_partition_times timestamp[], p_analyze boolean DEFAULT true) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_analyze boolean := FALSE; v_control text; v_datetime_string text; v_exists text; v_epoch boolean; v_grantees text[]; v_hasoids boolean; v_inherit_fk boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_old_search_path text; v_parent_grant record; v_parent_owner text; v_parent_schema text; v_parent_tablename text; v_partition_created boolean := false; v_partition_name text; v_partition_suffix text; v_parent_tablespace text; v_partition_interval interval; v_partition_timestamp_end timestamp; v_partition_timestamp_start timestamp; v_quarter text; v_revoke text; v_row record; v_sql text; v_step_id bigint; v_step_overflow_id bigint; v_sub_timestamp_max timestamp; v_sub_timestamp_min timestamp; v_trunc_value text; v_time timestamp; v_type text; v_unlogged char; v_year text; BEGIN SELECT partition_type , control , partition_interval , epoch , inherit_fk , jobmon , datetime_string INTO v_type , v_control , v_partition_interval , v_epoch , v_inherit_fk , v_jobmon , v_datetime_string FROM @extschema@.part_config WHERE parent_table = p_parent_table AND partition_type = 'time' OR partition_type = 'time-custom'; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); END IF; END IF; -- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix SELECT sub_min::timestamp, sub_max::timestamp INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time'); SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); END IF; FOREACH v_time IN ARRAY p_partition_times LOOP v_partition_timestamp_start := v_time; BEGIN v_partition_timestamp_end := v_time + v_partition_interval; EXCEPTION WHEN datetime_field_overflow THEN RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation after time % skipped', v_time; v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); CONTINUE; END; -- Do not create the child table if it's outside the bounds of the top parent. IF v_sub_timestamp_min IS NOT NULL THEN IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN CONTINUE; END IF; END IF; -- This suffix generation code is in partition_data_time() as well v_partition_suffix := to_char(v_time, v_datetime_string); v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; IF v_exists IS NOT NULL THEN CONTINUE; END IF; -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped v_analyze := TRUE; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s' , v_parent_schema , v_partition_name , v_partition_timestamp_start , v_partition_timestamp_end-'1sec'::interval)); END IF; SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename AND n.nspname = v_parent_schema; v_sql := 'CREATE'; IF v_unlogged = 'u' THEN v_sql := v_sql || ' UNLOGGED'; END IF; v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename AND n.nspname = v_parent_schema; IF v_hasoids IS TRUE THEN v_sql := v_sql || ' WITH (OIDS)'; END IF; EXECUTE v_sql; IF v_parent_tablespace IS NOT NULL THEN EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); END IF; IF v_epoch = false THEN EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_control , v_partition_timestamp_start , v_control , v_partition_timestamp_end); ELSE EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (to_timestamp(%I) >= %L AND to_timestamp(%I) < %L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_control , v_partition_timestamp_start , v_control , v_partition_timestamp_end); END IF; EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); -- If custom time, set extra config options. IF v_type = 'time-custom' THEN INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); END IF; PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); IF v_inherit_fk THEN PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; -- Will only loop once and only if sub_partitioning is actually configured -- This seemed easier than assigning a bunch of variables then doing an IF condition FOR v_row IN SELECT sub_parent , sub_control , sub_partition_type , sub_partition_interval , sub_constraint_cols , sub_premake , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_use_run_maintenance , sub_epoch , sub_optimize_trigger , sub_optimize_constraint , sub_jobmon FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table LOOP IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name)); END IF; v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_type := %L , p_interval := %L , p_constraint_cols := %L , p_premake := %L , p_use_run_maintenance := %L , p_inherit_fk := %L , p_epoch := %L , p_jobmon := %L )' , v_parent_schema||'.'||v_partition_name , v_row.sub_control , v_row.sub_partition_type , v_row.sub_partition_interval , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_use_run_maintenance , v_row.sub_inherit_fk , v_row.sub_epoch , v_row.sub_jobmon); EXECUTE v_sql; UPDATE @extschema@.part_config SET retention_schema = v_row.sub_retention_schema , retention_keep_table = v_row.sub_retention_keep_table , retention_keep_index = v_row.sub_retention_keep_index , optimize_trigger = v_row.sub_optimize_trigger , optimize_constraint = v_row.sub_optimize_constraint WHERE parent_table = v_parent_schema||'.'||v_partition_name; END LOOP; -- end sub partitioning LOOP v_partition_created := true; END LOOP; -- v_analyze is a local check if a new table is made. -- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. IF v_analyze AND p_analyze THEN IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); END IF; EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN IF v_partition_created = false THEN v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s. Attempted intervals: %s', p_parent_table, p_partition_times)); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; IF v_step_overflow_id IS NOT NULL THEN PERFORM fail_job(v_job_id); ELSE PERFORM close_job(v_job_id); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END IF; RETURN v_partition_created; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Function to undo partitioning. * Will actually work on any parent/child table set, not just ones created by pg_partman. */ CREATE OR REPLACE FUNCTION undo_partition(p_parent_table text, p_batch_count int DEFAULT 1, p_keep_table boolean DEFAULT true, p_jobmon boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_adv_lock boolean; v_batch_loop_count bigint := 0; v_child_count bigint; v_child_table text; v_copy_sql text; v_function_name text; v_job_id bigint; v_jobmon_schema text; v_lock_iter int := 1; v_lock_obtained boolean := FALSE; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_partition_interval interval; v_rowcount bigint; v_step_id bigint; v_total bigint := 0; v_trig_name text; v_type text; v_undo_count int := 0; BEGIN v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition')); IF v_adv_lock = 'false' THEN RAISE NOTICE 'undo_partition already running.'; RETURN 0; END IF; IF p_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); END IF; -- Stops new time partitons from being made as well as stopping child tables from being dropped if they were configured with a retention period. UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; -- Stop data going into child tables and stop new id partitions from being made. SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid WHERE tgname = v_trig_name AND c.relname = v_parent_tablename; SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; IF v_trig_name IS NOT NULL THEN -- lockwait for trigger drop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; RETURN -1; END IF; END IF; -- END p_lock_wait IF EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); END IF; -- END trigger IF v_lock_obtained := FALSE; -- reset for reuse later IF v_function_name IS NOT NULL THEN EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); END IF; IF v_jobmon_schema IS NOT NULL THEN IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); ELSE PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); END IF; END IF; WHILE v_batch_loop_count < p_batch_count LOOP -- Get ordered list of child table in set. Store in variable one at a time per loop until none are left. -- Not using show_partitions() so it can work on non-pg_partman partition sets WITH parent_info AS ( SELECT c1.oid FROM pg_catalog.pg_class c1 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid WHERE c1.relname = v_parent_tablename AND n1.nspname = v_parent_schema ) SELECT c.relname INTO v_child_table FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON i.inhrelid = c.oid JOIN parent_info p ON i.inhparent = p.oid ORDER BY i.inhrelid ASC; EXIT WHEN v_child_table IS NULL; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); END IF; -- lockwait timeout for table drop v_lock_obtained := FALSE; -- reset for reuse in loop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; RETURN -1; END IF; END IF; -- END p_lock_wait IF v_copy_sql := format('INSERT INTO %I.%I SELECT * FROM %I.%I' , v_parent_schema , v_parent_tablename , v_parent_schema , v_child_table); EXECUTE v_copy_sql; GET DIAGNOSTICS v_rowcount = ROW_COUNT; v_total := v_total + v_rowcount; EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' , v_parent_schema , v_child_table , v_parent_schema , v_parent_tablename); IF p_keep_table = false THEN EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_rowcount)); END IF; ELSE IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Copied %s rows to parent', v_rowcount)); END IF; END IF; SELECT partition_type INTO v_type FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_type = 'time-custom' THEN DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_parent_schema||'.'||v_child_table; END IF; v_batch_loop_count := v_batch_loop_count + 1; v_undo_count := v_undo_count + 1; END LOOP; -- v_batch_loop_count IF v_undo_count = 0 THEN -- FOR loop never ran, so there's no child tables left. DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Removing config from pg_partman (if it existed)'); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; RAISE NOTICE 'Copied % row(s) from % child table(s) to the parent: %', v_total, v_undo_count, p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Final stats'); PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) from %s child table(s) to the parent', v_total, v_undo_count)); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM close_job(v_job_id); EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END IF; RETURN v_total; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Function to undo time-based partitioning created by this extension */ CREATE OR REPLACE FUNCTION undo_partition_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_adv_lock boolean; v_batch_loop_count int := 0; v_child_min timestamptz; v_child_loop_total bigint := 0; v_child_table text; v_control text; v_epoch boolean; v_function_name text; v_inner_loop_count int; v_lock_iter int := 1; v_lock_obtained boolean := FALSE; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_move_sql text; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_partition_interval interval; v_row record; v_rowcount bigint; v_step_id bigint; v_sub_count int; v_total bigint := 0; v_trig_name text; v_type text; v_undo_count int := 0; BEGIN v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition_time')); IF v_adv_lock = 'false' THEN RAISE NOTICE 'undo_partition_time already running.'; RETURN 0; END IF; SELECT partition_type , partition_interval::interval , control , jobmon , epoch INTO v_type , v_partition_interval , v_control , v_jobmon , v_epoch FROM @extschema@.part_config WHERE parent_table = p_parent_table AND (partition_type = 'time' OR partition_type = 'time-custom'); IF v_partition_interval IS NULL THEN RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; END IF; -- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. -- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first -- before multi-level undo can be performed safely. FOR v_row IN SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table) LOOP SELECT count(*) INTO v_sub_count FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON i.inhparent = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_row.partition_tablename AND n.nspname = v_row.partition_schemaname; IF v_sub_count > 0 THEN RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.partition_schemaname||'.'||v_row.partition_tablename; END IF; END LOOP; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); END IF; IF p_batch_interval IS NULL THEN p_batch_interval := v_partition_interval; END IF; -- Stops new time partitons from being made as well as stopping child tables from being dropped if they were configured with a retention period. UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; -- Stop data going into child tables. SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid WHERE tgname = v_trig_name AND c.relname = v_parent_tablename; SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; IF v_trig_name IS NOT NULL THEN -- lockwait for trigger drop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; RETURN -1; END IF; END IF; -- END p_lock_wait IF EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); END IF; -- END trigger IF v_lock_obtained := FALSE; -- reset for reuse later IF v_function_name IS NOT NULL THEN EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); END IF; IF v_jobmon_schema IS NOT NULL THEN IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); ELSE PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); END IF; END IF; <> LOOP SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC'); EXIT outer_child_loop WHEN v_child_table IS NULL; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); END IF; IF v_epoch = false THEN EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; ELSE EXECUTE format('SELECT to_timestamp(min(%I)) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; END IF; IF v_child_min IS NULL THEN -- No rows left in this child table. Remove from partition set. -- lockwait timeout for table drop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; RETURN -1; END IF; END IF; -- END p_lock_wait IF v_lock_obtained := FALSE; -- reset for reuse later EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' , v_parent_schema , v_child_table , v_parent_schema , v_parent_tablename); IF p_keep_table = false THEN EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_child_loop_total)); END IF; ELSE IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Moved %s rows to parent', v_child_loop_total)); END IF; END IF; IF v_type = 'time-custom' THEN DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_parent_schema||'.'||v_child_table; END IF; v_undo_count := v_undo_count + 1; EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached CONTINUE outer_child_loop; -- skip data moving steps below END IF; v_inner_loop_count := 1; v_child_loop_total := 0; <> LOOP -- do some locking with timeout, if required IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('SELECT * FROM %I.%I WHERE %I <= %L FOR UPDATE NOWAIT' , v_parent_schema , v_child_table , v_control , v_child_min + (p_batch_interval * v_inner_loop_count)); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; RETURN -1; END IF; END IF; -- Get everything from the current child minimum up to the multiples of the given interval IF v_epoch = false THEN v_move_sql := format('WITH move_data AS ( DELETE FROM %I.%I WHERE %I <= %L RETURNING *) INSERT INTO %I.%I SELECT * FROM move_data' , v_parent_schema , v_child_table , v_control , v_child_min + (p_batch_interval * v_inner_loop_count) , v_parent_schema , v_parent_tablename); ELSE v_move_sql := format('WITH move_data AS ( DELETE FROM %I.%I WHERE to_timestamp(%I) <= %L RETURNING *) INSERT INTO %I.%I SELECT * FROM move_data' , v_parent_schema , v_child_table , v_control , v_child_min + (p_batch_interval * v_inner_loop_count) , v_parent_schema , v_parent_tablename); END IF; EXECUTE v_move_sql; GET DIAGNOSTICS v_rowcount = ROW_COUNT; v_total := v_total + v_rowcount; v_child_loop_total := v_child_loop_total + v_rowcount; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Moved %s rows to parent.', v_child_loop_total)); END IF; EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty v_inner_loop_count := v_inner_loop_count + 1; v_batch_loop_count := v_batch_loop_count + 1; -- Check again if table is empty and go to outer loop again to drop it if so IF v_epoch = false THEN EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; ELSE EXECUTE format('SELECT to_timestamp(min(%I)) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; END IF; CONTINUE outer_child_loop WHEN v_child_min IS NULL; EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached END LOOP inner_child_loop; END LOOP outer_child_loop; SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1; IF v_child_table IS NULL THEN DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Final stats'); PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) to the parent. Removed %s partitions.', v_total, v_undo_count)); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM close_job(v_job_id); EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END IF; RETURN v_total; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; /* * Function to undo id-based partitioning created by this extension */ CREATE OR REPLACE FUNCTION undo_partition_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval bigint DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_adv_lock boolean; v_batch_loop_count int := 0; v_child_loop_total bigint := 0; v_child_min bigint; v_child_table text; v_control text; v_exists int; v_function_name text; v_inner_loop_count int; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_lock_iter int := 1; v_lock_obtained boolean := FALSE; v_move_sql text; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_partition_interval bigint; v_row record; v_rowcount bigint; v_step_id bigint; v_sub_count int; v_trig_name text; v_total bigint := 0; v_undo_count int := 0; BEGIN v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition_id')); IF v_adv_lock = 'false' THEN RAISE NOTICE 'undo_partition_id already running.'; RETURN 0; END IF; SELECT partition_interval::bigint , control , jobmon INTO v_partition_interval , v_control , v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table AND partition_type = 'id'; IF v_partition_interval IS NULL THEN RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; END IF; -- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. -- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first -- before multi-level undo can be performed safely. FOR v_row IN SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table) LOOP SELECT count(*) INTO v_sub_count FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON i.inhparent = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_row.partition_tablename AND n.nspname = v_row.partition_schemaname; IF v_sub_count > 0 THEN RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.partition_schemaname||'.'||v_row.partition_tablename; END IF; END LOOP; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); END IF; IF p_batch_interval IS NULL THEN p_batch_interval := v_partition_interval; END IF; -- Stops new time partitons from being made as well as stopping child tables from being dropped if they were configured with a retention period. UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; -- Stop data going into child tables and stop new id partitions from being made. SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid WHERE tgname = v_trig_name AND c.relname = v_parent_tablename; SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; IF v_trig_name IS NOT NULL THEN -- lockwait for trigger drop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; RETURN -1; END IF; END IF; -- END p_lock_wait IF EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); END IF; -- END trigger IF v_lock_obtained := FALSE; -- reset for reuse later IF v_function_name IS NOT NULL THEN EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); END IF; IF v_jobmon_schema IS NOT NULL THEN IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); ELSE PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); END IF; END IF; <> LOOP -- Get ordered list of child table in set. Store in variable one at a time per loop until none are left or batch count is reached. -- This easily allows it to loop over same child table until empty or move onto next child table after it's dropped SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC'); EXIT WHEN v_child_table IS NULL; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); END IF; EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; IF v_child_min IS NULL THEN -- No rows left in this child table. Remove from partition set. -- lockwait timeout for table drop IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; RETURN -1; END IF; END IF; -- END p_lock_wait IF v_lock_obtained := FALSE; -- reset for reuse later EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' , v_parent_schema , v_child_table , v_parent_schema , v_parent_tablename); IF p_keep_table = false THEN EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_child_loop_total)); END IF; ELSE IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Moved %s rows to parent', v_child_loop_total)); END IF; END IF; v_undo_count := v_undo_count + 1; EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached CONTINUE outer_child_loop; -- skip data moving steps below END IF; v_inner_loop_count := 1; v_child_loop_total := 0; <> LOOP -- lockwait timeout for row batches IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('SELECT * FROM %I.%I WHERE %I <= %s FOR UPDATE NOWAIT' , v_parent_schema , v_child_table , v_control , v_child_min + (p_batch_interval * v_inner_loop_count)); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; RETURN -1; END IF; END IF; -- Get everything from the current child minimum up to the multiples of the given interval v_move_sql := format('WITH move_data AS ( DELETE FROM %I.%I WHERE %I <= %s RETURNING *) INSERT INTO %I.%I SELECT * FROM move_data' , v_parent_schema , v_child_table , v_control , v_child_min + (p_batch_interval * v_inner_loop_count) , v_parent_schema , v_parent_tablename); EXECUTE v_move_sql; GET DIAGNOSTICS v_rowcount = ROW_COUNT; v_total := v_total + v_rowcount; v_child_loop_total := v_child_loop_total + v_rowcount; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Moved %s rows to parent.', v_child_loop_total)); END IF; EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty v_inner_loop_count := v_inner_loop_count + 1; v_batch_loop_count := v_batch_loop_count + 1; -- Check again if table is empty and go to outer loop again to drop it if so EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; CONTINUE outer_child_loop WHEN v_child_min IS NULL; EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached END LOOP inner_child_loop; END LOOP outer_child_loop; SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1; IF v_child_table IS NULL THEN DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Final stats'); PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) to the parent. Removed %s partitions.', v_total, v_undo_count)); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM close_job(v_job_id); EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END IF; RETURN v_total; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$;