Functions for altering access method
CREATE OR REPLACE FUNCTION alter_table_access_method(table_name regclass, access_method name) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
compressed_table_name name := table_name || '_cmp'; | |
original_table_name name := table_name::name; | |
table_relkind char; | |
parent_table_name regclass; | |
partition_boundaries text; | |
BEGIN | |
SELECT relkind INTO table_relkind | |
FROM pg_class WHERE oid = table_name; | |
IF table_relkind <> 'r' THEN | |
RAISE 'can only alter access method of regular tables'; | |
END IF; | |
EXECUTE format('LOCK %s IN EXCLUSIVE MODE', table_name); | |
EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING COMMENTS INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING STATISTICS) USING %s', compressed_table_name, table_name, access_method); | |
IF EXISTS (SELECT 1 FROM pg_dist_partition WHERE logicalrelid = table_name) THEN | |
PERFORM create_distributed_table(compressed_table_name::regclass, column_to_column_name(logicalrelid, partkey), colocate_with := table_name::text) | |
FROM pg_dist_partition WHERE logicalrelid = table_name; | |
END IF; | |
EXECUTE format('INSERT INTO %s SELECT * FROM %s', compressed_table_name, table_name); | |
SELECT parentrelid INTO parent_table_name | |
FROM pg_partition_tree(table_name); | |
IF parent_table_name IS NOT NULL THEN | |
SELECT pg_get_expr(relpartbound, oid) INTO partition_boundaries | |
FROM pg_class WHERE oid = table_name; | |
IF partition_boundaries IS NULL THEN | |
partition_boundaries := 'DEFAULT'; | |
END IF; | |
EXECUTE format('ALTER TABLE %s DETACH PARTITION %s', parent_table_name, table_name); | |
EXECUTE format('ALTER TABLE %s ATTACH PARTITION %s %s', parent_table_name, compressed_table_name, partition_boundaries); | |
END IF; | |
EXECUTE format('DROP TABLE %s', table_name); | |
EXECUTE format('ALTER TABLE %s RENAME TO %s', compressed_table_name, original_table_name); | |
END; | |
$$; |
DROP VIEW IF EXISTS time_partitions; | |
CREATE VIEW time_partitions AS | |
SELECT parentrelid, relid, range[1]::timestamptz AS start_time, range[2]::timestamptz AS end_time, amname AS access_method | |
FROM ( | |
SELECT parentrelid, c.oid::regclass AS relid, regexp_matches(pg_get_expr(relpartbound, c.oid), $$FOR VALUES FROM \('(.*)'\) TO \('(.*)'\)$$) AS range, amname | |
FROM pg_class c, pg_partition_tree(c.oid) p, pg_am a where relpartbound is not null and a.oid = c.relam | |
) partitions; | |
CREATE OR REPLACE PROCEDURE alter_old_partitions_access_method(parent_table_name regclass, older_than timestamptz, new_access_method name) | |
LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
r record; | |
BEGIN | |
FOR r IN SELECT * FROM time_partitions WHERE parentrelid = parent_table_name AND end_time <= older_than AND access_method <> new_access_method ORDER BY end_time | |
LOOP | |
RAISE NOTICE 'converting % with start time % and end time %', r.relid, r.start_time, r.end_time; | |
PERFORM alter_table_access_method(r.relid, new_access_method); | |
COMMIT; | |
END LOOP; | |
END; | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Example usage: