如何将VACUUM从使用pg_cron运行的清除例程中链接出来

Question

Postgres 13.4

我已经设置了一些pg_cron作业，以便定期从类似日志的文件中删除旧记录。我想做的是在执行清除后运行VACUUM ANALYZE。不幸的是，我无法在存储函数中找到如何做到这一点。我是不是错过了一个技巧？存储过程是否更合适？

举个例子，下面是我的一个清除程序

CREATE OR REPLACE FUNCTION dba.purge_event_log (
retain_days_in integer_positive default 14)
RETURNS int4
AS $BODY$
WITH  -- Use a CTE so that we've got a way of returning the count easily.
deleted AS (
-- Normal-looking code for this requires a literal:
-- where your_dts < now() - INTERVAL '14 days'
-- Don't want to use a literal, SQL injection, etc.
-- Instead, using a interval constructor to achieve the same result:
DELETE
FROM dba.event_log
WHERE dts < now() - make_interval (days => $1)
RETURNING *
),
----------------------------------------
-- Save details to a custom log table
----------------------------------------
logit AS (
insert into dba.event_log (name, details)
values ('purge_event_log(' || retain_days_in::text || ')',
'count = ' || (select count(*)::text from deleted)
)
)
----------------------------------------
-- Return result count
----------------------------------------
select count(*) from deleted;
$BODY$
LANGUAGE sql;
COMMENT ON FUNCTION dba.purge_event_log (integer_positive) IS
'Delete dba.event_log records older than the day count passed in, with a default retention period of 14 days.';

事实是，我真的不在乎这个例程的count(*)结果，在这种情况下。但我可能想要一个结果和在其他类似的上下文中的附加操作。正如您所看到的，例程删除记录，使用CTE将报告insert到另一个表中，然后返回结果。不管怎样，我认为这个例子是让我了解存储函数中的替代方案和选项的好方法。我想在这里实现的主要功能是删除记录，然后运行维护。如果这不适合存储函数或过程，我可以用表名为vacuum_list表写一个条目，并在该列表中运行另一个作业。

如果有一种更聪明的方法可以在没有额外费用的情况下接近vacuum，我当然对此感兴趣。但我也有兴趣了解PL/PgSQL例程中可以组合的操作的限制。

帕维尔·斯特胡勒的回答是正确和完整的。我决定在这里跟进一下，因为我喜欢深入研究我的代码中的错误、Postgres中的行为等，以更好地了解我正在处理的问题。我在下面附上了一些注释，供任何觉得有用的人使用。

COMMAND无法执行

引用"；VACUUM不能在事务块"内部执行；为我提供了一种更好的方法来搜索文档中类似的受限命令。下面的信息可能并不能涵盖所有内容，但这只是一个开始。

Command                Limitation
CREATE DATABASE
ALTER DATABASE         If creating a new table space.
DROP DATABASE
CLUSTER                Without any parameters.
CREATE TABLESPACE
DROP TABLESPACE
REINDEX                All in system catalogs, database, or schema.
CREATE SUBSCRIPTION    When creating a replication slot (the default behavior.)
ALTER SUBSCRIPTION     With refresh option as true.
DROP SUBSCRIPTION      If the subscription is associated with a replication slot.
COMMIT PREPARED
ROLLBACK PREPARED
DISCARD ALL
VACUUM

接受的答案表明，该限制与所使用的特定服务器端语言无关。我刚刚遇到一个旧的线程，它对存储的函数和事务有一些很好的解释和链接：

存储过程在Postgres的数据库事务中运行吗？

示例代码

我还想知道存储过程，因为它们可以控制事务。我在PG13中试用了它们，不，代码被视为存储函数，直到错误消息。

对于任何从事这类事情的人来说，以下是"；你好世界"；sQL和PL/PgSQL的示例存储函数和过程，以测试VACCUM在这些情况下的行为。剧透：它不起作用，正如广告中所说。

SQL函数

/*
select * from dba.vacuum_sql_function();
Fails:
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL function "vacuum_sql_function" statement 1. 0.000 seconds. (Line 13).
*/
DROP FUNCTION IF EXISTS dba.vacuum_sql_function();
CREATE FUNCTION dba.vacuum_sql_function()
RETURNS VOID
LANGUAGE sql
AS $sql_code$
VACUUM ANALYZE activity;
$sql_code$;
select * from dba.vacuum_sql_function(); -- Fails.

PL/PgSQL函数

/*
select * from dba.vacuum_plpgsql_function();
Fails:
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "VACUUM ANALYZE activity"
PL/pgSQL function vacuum_plpgsql_function() line 4 at SQL statement. 0.000 seconds. (Line 22).
*/
DROP FUNCTION IF EXISTS dba.vacuum_plpgsql_function();
CREATE FUNCTION dba.vacuum_plpgsql_function()
RETURNS VOID
LANGUAGE plpgsql
AS $plpgsql_code$
BEGIN
VACUUM ANALYZE activity;
END
$plpgsql_code$;
select * from dba.vacuum_plpgsql_function();

SQL过程

/*
call dba.vacuum_sql_procedure();
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL function "vacuum_sql_procedure" statement 1. 0.000 seconds. (Line 20).
*/
DROP PROCEDURE IF EXISTS dba.vacuum_sql_procedure();
CREATE PROCEDURE dba.vacuum_sql_procedure()
LANGUAGE SQL
AS $sql_code$
VACUUM ANALYZE activity;
$sql_code$;
call dba.vacuum_sql_procedure();

PL/PgSQL过程

/*
call dba.vacuum_plpgsql_procedure();
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "VACUUM ANALYZE activity"
PL/pgSQL function vacuum_plpgsql_procedure() line 4 at SQL statement. 0.000 seconds. (Line 23).
*/
DROP PROCEDURE IF EXISTS dba.vacuum_plpgsql_procedure();
CREATE PROCEDURE dba.vacuum_plpgsql_procedure()
LANGUAGE plpgsql
AS $plpgsql_code$
BEGIN
VACUUM ANALYZE activity;
END
$plpgsql_code$;
call dba.vacuum_plpgsql_procedure();

其他选项

很多。据我所知，在Postgres中运行的服务器端代码不支持VACUUM和其他一些命令。因此，您的代码需要从其他地方开始。可以是：

• 无论您的服务器操作系统中有什么cron
• 任何你喜欢的外部客户
• CCD_ 10

当我们部署在RDS上时，我会考虑最后两个选项。还有一个：

• 让AUTOVACCUM和偶尔的VACCUM做他们的事情

这很容易做到，而且似乎可以满足我们的大部分需求。

另一个想法

如果你确实想要更多的控制和一些自定义日志记录，我想象一个这样的表：

CREATE TABLE IF NOT EXISTS dba.vacuum_list (
database_name   text,
schema_name     text,
table_name      text,
run             boolean,
run_analyze     boolean,
run_full        boolean,
last_run_dts    timestamp)
ALTER TABLE dba.vacuum_list ADD CONSTRAINT
vacuum_list_pk
PRIMARY KEY (database_name, schema_name, table_name);

这只是一个草图。想法是这样的：

• 当表需要抽真空时，至少就您而言，您将INSERT转换为vacuum_list。

• 在我的情况下，这将是一个UPSERT，因为我不需要一个完整的类似日志的表，只需要每个感兴趣的表有一行，其中包含最后的结果和/或挂起的状态。

• 根据记录中指定的选项，远程客户端等会定期连接、读取表并执行每个指定的VACUUM。

• 外部客户端使用上次运行时间戳以及行中包含的其他内容更新行。

• 可选地，您可以在清除前或清除后包含持续时间和关系大小变化的字段。

最后一个选项是我感兴趣的。我们的VACUUM呼叫在很长一段时间内都没有工作，因为有一个来自服务器端的长达数月的死连接。VACUUM似乎运行良好，在这种情况下，它无法删除大量行。(因为超旧的"打开"事务ID、可见性映射等)查看这类事情的唯一方法似乎是VACUUM VERBOSE并研究输出。或者记录真空时间，更重要的是，记录关系大小的变化，以标记似乎什么都没有发生的情况，当它看起来应该发生的时候。

VACUUM是"顶级"；命令它不能从PL/pgSQL或任何其他PL执行。