在Azure Postgres弹性服务器中，如何用pg_cron调用函数后执行VACUUM？

问题根源 #

pg_cron默认会将调度的任务脚本包裹在一个事务块中执行，而VACUUM ANALYZE不允许在事务块内运行，因此触发ERROR: VACUUM cannot run inside a transaction block错误。

可行解决方案 #

方案1：拆分为两个独立的pg_cron任务 #

将删除旧数据和VACUUM操作分成两个单独的定时任务，pg_cron对单命令任务不会包裹事务，可避开报错：

1. 创建删除旧数据的定时任务（示例为每天凌晨2点执行）：

SELECT cron.schedule(
    'delete-old-data-daily',
    '0 2 * * *',
    'SELECT myschema.delete_old_data();'
);

2. 创建VACUUM ANALYZE的定时任务（安排在删除任务之后，示例为凌晨2点10分）：

SELECT cron.schedule(
    'vacuum-my-table-daily',
    '10 2 * * *',
    'VACUUM ANALYZE myschema.my_table;'
);

方案2：使用dblink在独立会话中执行VACUUM #

如果希望保持任务的连贯性（确保删除完成后再执行VACUUM），可通过dblink在当前事务外启动独立会话执行VACUUM：

1. 先确认dblink扩展已安装：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 创建封装两个操作的函数：

CREATE OR REPLACE FUNCTION myschema.run_maintenance()
RETURNS void AS $$
BEGIN
    -- 执行旧数据删除
    PERFORM myschema.delete_old_data();
    
    -- 通过dblink在独立会话执行VACUUM ANALYZE
    PERFORM dblink_connect('dbname=' || current_database());
    PERFORM dblink_exec('VACUUM ANALYZE myschema.my_table;');
    PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

3. 调度该函数的定时任务：

SELECT cron.schedule(
    'run-db-maintenance-daily',
    '0 2 * * *',
    'SELECT myschema.run_maintenance();'
);

注意事项 #

• 确保执行pg_cron的用户拥有对应权限：包括pg_cron角色权限、执行delete_old_data()的权限、VACUUM ANALYZE权限，以及dblink使用权限（若用方案2）。
• Azure Postgres弹性服务器中，dblink连接当前数据库属于本地访问，默认防火墙规则允许该操作。

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