Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Upgrading old partitioning with ALTER TABLEs and PLPGSQL.
-- Upgrading old partitioning with ALTER TABLEs and PLPGSQL.
-- IN: _orig_table - master table which should be upgraded
-- IN: _partkey - column which used as partition key
-- IN: _seq_col - sequence column
CREATE OR REPLACE FUNCTION fn_upgrade_partitioning(_orig_table text, _partkey text, _seq_col text) RETURNS void AS
$function$
DECLARE
_new_table text = _orig_table ||'_new'; -- parent relation's name
_child_table text; -- child relation's name
_v_from timestamp without time zone;
_v_to timestamp without time zone;
_seq_name text = split_part(pg_get_serial_sequence(_orig_table, _seq_col), '.', 2);
BEGIN
-- create new table and attach existing sequence
EXECUTE format($$CREATE TABLE IF NOT EXISTS %I (LIKE %I) PARTITION BY RANGE (%I)$$, _new_table, _orig_table, _partkey);
EXECUTE format($$ALTER SEQUENCE %I OWNED BY %I.%I$$, _seq_name, _new_table, _seq_col);
EXECUTE format($$ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval('%I')$$, _new_table, _seq_col, _seq_name);
-- loop over partitions
FOR _child_table IN (SELECT c.relname FROM pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid) JOIN pg_class AS p ON (inhparent=p.oid) WHERE p.relname = _orig_table)
LOOP
-- detach partition
EXECUTE format('ALTER TABLE %I NO INHERIT %I', _child_table, _orig_table);
-- calculate FROM and TO values and attach partition
SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone INTO _v_from;
SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone + interval '1 month' INTO _v_to;
EXECUTE format($$ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM ('%s') TO ('%s')$$, _new_table, _child_table, _v_from, _v_to);
RAISE NOTICE '% reattached from % to %', _child_table, _orig_table, _new_table;
END LOOP;
-- drop old parent table and rename new one
EXECUTE format('DROP TABLE %I', _orig_table);
EXECUTE format('ALTER TABLE %I RENAME TO %I', _new_table, _orig_table);
RAISE NOTICE 'partitioning for % has been upgraded.', _orig_table;
END;
$function$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.