无法从函数或多命令字符串中执行VACUUM。

Question

我用PL/pgSQL编写了一个脚本，在pgAdmin III中运行。该脚本会删除现有的数据库内容，然后添加一些“示例”数据以进行所需的测试场景（通常是各种类型的负载测试）。加载完数据后，我想要对受影响的表执行“vacuum analyze”，既可以从已删除记录中恢复空间，也可以准确地反映新内容。

我可以使用各种变通方法（例如手动执行VACUUM ANALYZE，将各种结构的drop/create语句包含在脚本中等），但我真正想做的是：

DO $$
BEGIN
  -- parent table
  FOR i IN 1..10000 LOOP
    INSERT INTO my_parent_table( ... ) VALUES ...;
  END LOOP;

  VACUUM ANALYZE my_parent_table;

  -- child table
  FOR i IN 1..50000 LOOP
    INSERT INTO my_child_table( ... ) VALUES ...;
  END LOOP;

  VACUUM ANALYZE my_child_table;
END;
$$;

运行此代码后，我得到了：

ERROR:  VACUUM cannot be executed from a function or multi-command string

于是我尝试将吸尘器语句移动到末尾，像这样：

DO $$
BEGIN
  -- parent table
  FOR i IN 1..10000 LOOP
    INSERT INTO my_parent_table( ... ) VALUES ...;
  END LOOP;

  -- child table
  FOR i IN 1..50000 LOOP
    INSERT INTO my_child_table( ... ) VALUES ...;
  END LOOP;
END;
$$;

VACUUM ANALYZE my_parent_table;
VACUUM ANALYZE my_child_table;

这给了我同样的错误。有没有办法将vacuum分析纳入到添加数据的同一脚本中？

我正在使用PostgreSQL v 9.2。

- Robert N

---

一个大批量的插入操作之后并不需要进行真空处理。只有在删除或更新大量数据时才需要真空处理（因为它会回收不再需要的空间）。在您的情况下，分析本身应该就足够了。 - user330315

谢谢，但我确实想要“VACUUM ANALYZE”；在添加所需的测试场景的新数据之前，脚本首先会删除所有现有数据。我已经编辑了问题以明确这一点。 - Robert N

如果你的脚本首先要删除所有现有数据，或许你应该使用截断表(truncate)而不是使用vacuum，这样可以避免需要执行vacuum。 - xzilla

除了 jjanes 的回答之外，在 insert 之前执行 vacuum analyze，就在 delete（或 truncate）之后立即执行，这样可以使其瞬间完成。 - Clodoaldo Neto

在dba.stackexchange上，这个答案解决了我的问题：在您的函数中调用ANALYZE，但将其留给自动清理来优雅地释放空间（只需确保为大型表配置了自动清理阈值）。 - Duncanmoo

Answer 1

如果您在pgAdmin3查询窗口中使用“执行查询”按钮运行此脚本，则会将整个脚本作为一个字符串发送到服务器。

如果您使用查询窗口中的“执行pgScript”按钮执行它，那么会单独发送命令，因此可以工作，但不支持匿名块的DO语法。您需要创建一个执行当前匿名工作的函数，然后使用类似以下内容的“execute pgScript”调用该函数：

select inserts_function();
VACUUM ANALYZE my_parent_table;
VACUUM ANALYZE my_child_table;

- jjanes

---

在pgAdmin4中没有“执行pgScript”按钮。 - Josh Noe

当时还不存在pgAdmin4。 - jjanes

Answer 2

我最终实现的解决方案是根据a_horse_with_no_name和xzilla在评论中提出的建议组合而成：

• 使用TRUNCATE而不是DELETE可以避免需要VACUUM
• ANALYZE可以在脚本内联使用，按需使用

- Robert N