Last active
November 3, 2020 16:58
-
-
Save marcocitus/6b0fb4ffbb38d52456283162b2679506 to your computer and use it in GitHub Desktop.
Functions for altering access method
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
$$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
Example usage: