PostgreSQL 教程: 引发表重写的 DDL 命令

摘要：在本教程中，您将了解哪些 DDL 命令会导致 PostgreSQL 表的重写。

目录

表重写简介

在实际情况中，引发表重写的 DDL 命令非常危险，它们可能会导致不确定的停机时间，在某些情况下可能需要 ~ 2 倍的磁盘空间。

通常，引发表重写的 DDL 命令，换句话说，也就是导致 relfilenode （表文件号）更改的 DDL 命令，通常会阻塞并发的工作负载。

简要介绍一下背景信息，PostgreSQL 中的每个常规表都将数据存储在一个或多个文件中，这些文件在系统表中使用一个 relfilenode 进行引用。检查当前实现是否会创建/引用另一个副本（文件）的一种简单方法是，检查 relfilenode 是否发生了更改。TRUNCATE 在这里较为特殊，按照设计，它会清除表数据，因此尽管 relfilenode 也会发生更改，但总的来说，它显然不会消耗接近 2 倍的磁盘空间。

验证表重写

让我们来看看修改表时会发生什么情况。为此，我们将使用可靠的 pgbench_accounts 表，该表在您运行 pgbench -i 时创建。我们可以通过将我们的语句放入一个事务中，来模拟长时间运行的表重写的效果。作为第一个示例，让我们向表添加一个新列，并查看持有了哪些锁：

BEGIN;

ALTER TABLE pgbench_accounts ADD extraid int;

SELECT locktype, mode FROM pg_locks
WHERE relation::regclass::text = 'pgbench_accounts';
 locktype |        mode
----------+---------------------
 relation | AccessExclusiveLock

COMMIT;

有一个 AccessExclusiveLock 在整个表上，这是一个非常非常强的锁，它几乎会阻止其他进程对表的所有其他访问。但是，我们如何确定表是否被重写了呢？

PostgreSQL 中的每个表都会映射到 PostgreSQL 数据目录中的一个或多个物理文件。我们可以使用 pg_relation_filenode 函数，来查看表的文件。如果 filenode 发生改变，则我们知道它已被重写。首先，让我们来更改一个现有列的数据类型，这应该会导致表的重写：

SELECT pg_relation_filenode('pgbench_accounts');
 pg_relation_filenode
----------------------
              1495140

ALTER TABLE pgbench_accounts ALTER COLUMN bid TYPE bigint;

SELECT pg_relation_filenode('pgbench_accounts');
 pg_relation_filenode
----------------------
              1495144

请注意，我们的 filenode 已改变了。

哪些 DDL 命令会引发表重写？

下表显示了哪些 DDL 会引发表重写。此表可以帮助您，为当前支持的所有 PostgreSQL 版本，做出一些与并发/磁盘使用相关的决策。

[1] 在此版本中该 SQL 已废弃。

[2] 为 WITHOUT OIDs 标记，纯粹是出于谨慎考虑。重写仅在 WITH OIDs 的表进行主版本升级期间发生。

禁止引发表重写的 DDL 命令

PostgreSQL 支持table_rewrite事件。table_rewrite事件在表被ALTER TABLE和 ALTER TYPE命令的某些动作重写之前发生。虽然其他控制语句（例如CLUSTER和VACUUM）也会引发重写表，但是它们不会触发table_rewrite事件。

得益于table_rewrite事件的存在，我们可以实现一种表重写策略，直接禁止这些 DDL 命令。下面是实现这种策略的一个例子。

CREATE OR REPLACE FUNCTION forbid_table_rewrites()
  RETURNS event_trigger
AS $$
BEGIN
  RAISE EXCEPTION 'command % prohibited', tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER forbid_rewrites
  ON table_rewrite
  EXECUTE FUNCTION forbid_table_rewrites();

ALTER TABLE foo ALTER COLUMN id TYPE BIGINT;
ERROR:  command ALTER TABLE prohibited
CONTEXT:  PL/pgSQL function forbid_table_rewrites() line 3 at RAISE

了解更多

PostgreSQL 教程