Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
$$;
@marcocitus

This comment has been minimized.

Copy link
Owner Author

@marcocitus marcocitus commented Oct 16, 2020

Example usage:

call alter_table_access_method('events_p2020_11_03', 'cstore_tableam');
call alter_old_partitions_access_method('events', now() - interval '7 days', 'cstore_tableam');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment