Functions for altering access method

alter_table_access_method.sql

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;
$$;

compress_old_partitions.sql

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;
$$;

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');