解决PostgreSQL函数执行VACUUM报错问题，实现旧表数据自动清理

报错原因 #

PostgreSQL不允许在PL/pgSQL函数的事务上下文内执行VACUUM命令——VACUUM属于直接操作数据库存储层的维护命令，无法嵌套在函数的事务块中运行，因此会抛出ERROR: VACUUM cannot be executed from a function错误。

可行解决方案 #

方案一：拆分删除与清理操作

将数据删除和VACUUM清理拆分为两个独立步骤，避免在函数中混合执行：

1. 修改原函数，仅保留数据删除逻辑：

CREATE OR REPLACE FUNCTION sales.fn_clear_old_data_from_sales_tables()
RETURNS boolean
LANGUAGE plpgsql
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    DELETE FROM sales.sales_messages_table b
        USING sales.sales_table a
        WHERE a.sales_id = b.sales_id
        AND a.transaction_timestamp < CURRENT_DATE - INTERVAL '6 MONTH';

    DELETE FROM sales.sales_table WHERE transaction_timestamp < CURRENT_DATE - INTERVAL '6 MONTH';
    
    RETURN true;
END;
$BODY$;

ALTER FUNCTION sales.fn_clear_old_data_from_sales_tables() OWNER TO salesdev;

2. 单独执行VACUUM：
   可以将这两步整合到同一个定时任务（如crontab）中，确保顺序执行：

# 先执行数据删除函数
psql -U salesdev -d your_database_name -c "SELECT sales.fn_clear_old_data_from_sales_tables();"
# 再执行清理命令
psql -U salesdev -d your_database_name -c "VACUUM ANALYZE sales.sales_messages_table; VACUUM ANALYZE sales.sales_table;"

方案二：通过dblink扩展在独立事务中执行VACUUM

如果必须将操作封装在一个逻辑单元中，可以使用dblink扩展创建独立事务执行VACUUM：

1. 先安装dblink扩展（需超级用户权限）：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 修改函数，通过dblink执行VACUUM：

CREATE OR REPLACE FUNCTION sales.fn_clear_old_data_from_sales_tables()
RETURNS boolean
LANGUAGE plpgsql
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_conn_str text := 'dbname=' || current_database();
BEGIN
    -- 删除旧数据
    DELETE FROM sales.sales_messages_table b
        USING sales.sales_table a
        WHERE a.sales_id = b.sales_id
        AND a.transaction_timestamp < CURRENT_DATE - INTERVAL '6 MONTH';

    DELETE FROM sales.sales_table WHERE transaction_timestamp < CURRENT_DATE - INTERVAL '6 MONTH';
    
    -- 通过dblink在独立事务中执行VACUUM
    PERFORM dblink_exec(v_conn_str, 'VACUUM ANALYZE sales.sales_messages_table;');
    PERFORM dblink_exec(v_conn_str, 'VACUUM ANALYZE sales.sales_table;');
    
    RETURN true;
END;
$BODY$;

ALTER FUNCTION sales.fn_clear_old_data_from_sales_tables() OWNER TO salesdev;

注意：需确保执行函数的salesdev用户拥有连接数据库及执行VACUUM的权限。

方案三：依赖PostgreSQL自动清理（autovacuum）

如果你的核心需求是回收删除后的存储空间，PostgreSQL默认启用的autovacuum进程会自动处理表的清理工作。可以通过调整以下配置优化自动清理效率：

• autovacuum_vacuum_threshold：触发自动清理的最小删除行数
• autovacuum_vacuum_scale_factor：触发自动清理的表行数比例
• autovacuum_analyze_threshold/autovacuum_analyze_scale_factor：触发自动分析的阈值

无需手动执行VACUUM，autovacuum会根据配置自动完成清理和分析。

内容的提问来源于stack exchange，提问作者runnerpaul