/* * Function to undo id-based partitioning created by this extension */ CREATE 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 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_part_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_id_partition')); IF v_adv_lock = 'false' THEN RAISE NOTICE 'undo_id_partition already running.'; RETURN 0; END IF; SELECT part_interval::bigint , control , jobmon INTO v_part_interval , v_control , v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table AND (type = 'id-static' OR type = 'id-dynamic'); IF v_part_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 show_partitions AS child_table FROM @extschema@.show_partitions(p_parent_table) LOOP SELECT count(*) INTO v_sub_count FROM pg_catalog.pg_inherits WHERE inhparent::regclass = v_row.child_table::regclass; 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.child_table; 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 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); END IF; IF p_batch_interval IS NULL THEN p_batch_interval := v_part_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_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, v_parent_schema, '_part_trig_func', FALSE); SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t WHERE tgname = v_trig_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 'LOCK TABLE ONLY '||p_parent_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 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 'DROP TRIGGER IF EXISTS '||v_trig_name||' ON '||p_parent_table; END IF; -- END trigger IF v_lock_obtained := FALSE; -- reset for reuse later EXECUTE 'DROP FUNCTION IF EXISTS '||v_function_name||'()'; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); END IF; <> WHILE v_batch_loop_count < p_batch_count LOOP SELECT n.nspname||'.'||c.relname INTO v_child_table FROM pg_inherits i JOIN pg_class c ON i.inhrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass 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, 'Removing child partition: '||v_child_table); END IF; EXECUTE 'SELECT min('||v_control||') FROM '||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 'LOCK TABLE ONLY '||v_child_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 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 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; IF p_keep_table = false THEN EXECUTE 'DROP TABLE '||v_child_table; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_child_loop_total||' rows to parent'); END IF; ELSE IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Moved '||v_child_loop_total||' rows to parent'); END IF; END IF; v_undo_count := v_undo_count + 1; CONTINUE outer_child_loop; 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 'SELECT * FROM ' || v_child_table || ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count)) ||' FOR UPDATE NOWAIT'; 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 := 'WITH move_data AS (DELETE FROM '||v_child_table|| ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count))||' RETURNING *) INSERT INTO '||p_parent_table||' SELECT * FROM move_data'; 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', 'Moved '||v_child_loop_total||' rows to parent.'); 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; 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; IF v_batch_loop_count < p_batch_count 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'); 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', 'Copied '||v_total||' row(s) to the parent. Removed '||v_undo_count||' partitions.'); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM close_job(v_job_id); EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; END IF; RETURN v_total; EXCEPTION WHEN OTHERS THEN IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN UNDO PARTITIONING: '||p_parent_table||''')' INTO v_job_id; EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; END IF; EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; END IF; RAISE EXCEPTION '%', SQLERRM; END $$;