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

七月 1, 2024

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

目录

表重写简介

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

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

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

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

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

表重写场景 v10 v11 v12 v13 v14 v15 v16 v17
ALTER TABLE ADD COLUMN INTEGER
ALTER TABLE ADD COLUMN INTEGER NULL
ALTER TABLE ADD COLUMN INTEGER NOT NULL
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NULL
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NOT NULL
ALTER TABLE ADD COLUMN TEXT COLLATE german
ALTER TABLE ADD COLUMN TEXT COLLATE german NULL
ALTER TABLE ADD COLUMN TEXT COLLATE german NOT NULL
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’ NULL
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’ NOT NULL
ALTER TABLE DROP COLUMN
ALTER TABLE ALTER COLUMN TYPE VARCHAR(1000) -- 二进制兼容 (Varchar(M) -> Varchar(N))
ALTER TABLE ALTER COLUMN TYPE TEXT -- 二进制兼容 (Varchar -> Text)
ALTER TABLE ALTER COLUMN TYPE TEXT -- 二进制不兼容 (Int -> Text)
ALTER TABLE ALTER COLUMN TYPE BIGINT -- 很常见的场景(例如 Int -> Bigint)
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- 之前列类型为 Int
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- 之前列类型为 Text
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- 之前列类型为 Varchar()
ALTER TABLE ALTER COLUMN SET DEFAULT 10000
ALTER TABLE ALTER COLUMN DROP DEFAULT
ALTER TABLE ALTER COLUMN SET NOT NULL
ALTER TABLE ALTER COLUMN DROP NOT NULL
ALTER TABLE ALTER COLUMN ADD GENERATED ALWAYS AS IDENTITY
ALTER TABLE ALTER COLUMN DROP IDENTITY
ALTER TABLE ALTER COLUMN SET STATISTICS -1 -- 从非默认值更改
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- 从非默认值更改
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- 从默认值更改
ALTER TABLE ALTER COLUMN SET (n_distinct=100)
ALTER TABLE ALTER COLUMN RESET (n_distinct)
ALTER TABLE ALTER COLUMN SET STORAGE MAIN
ALTER TABLE ALTER COLUMN SET STORAGE PLAIN
ALTER TABLE ALTER COLUMN SET STORAGE EXTERNAL
ALTER TABLE ALTER COLUMN SET STORAGE EXTENDED
ALTER TABLE CLUSTER ON;
ALTER TABLE SET WITHOUT CLUSTER
ALTER TABLE SET WITH OIDS [1] [1] [1] [1] [1] [1]
ALTER TABLE SET WITHOUT OIDS [2] [2] [2] [2] [2] [2]
ALTER TABLE SET TABLESPACE tmp_tblspc
ALTER TABLE SET LOGGED
ALTER TABLE SET UNLOGGED
ALTER TABLE SET (FILLFACTOR=10)
ALTER TABLE RESET (FILLFACTOR)
ALTER TABLE INHERIT
ALTER INDEX set tablespace tmp_tblspc
DELETE FROM
CLUSTER
COMMENT ON TABLE IS ’testing’
TRUNCATE TABLE
VACUUM FULL

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

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

禁止引发表重写的 DDL 命令

PostgreSQL 支持table_rewrite事件。table_rewrite事件在表被ALTER TABLEALTER TYPE命令的某些动作重写之前发生。虽然其他控制语句(例如CLUSTERVACUUM)也会引发重写表,但是它们不会触发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