前几天看到《PostgreSQL DDL变更的坑和巧妙方案》原文链接：https://www.modb.pro/db/1946417163296845824?utm_source=index_ori，里面提到几种情况下会重写表，因为图里面主要是关于DDL 表变更的，里面涉及的DDL 场景比较多，单独总结一下看哪些DDL情况下会重写表并验证一番，增强记忆。 测试基于PG 16
- 列的数据类型变化
更改现有列的类型通常会导致整个表及其索引被重写，除非在更改列的类型时，更改后的数据类型没有更改列的内容，旧类型对新类型是二进制强制（binary coercible）或在新类型上不受约束的域。
但是，索引仍然会被重建，除非系统可以验证新索引在逻辑上等价于现有的。例如列的排序规则改变，那么需要重建索引，因为新的排序顺序可能会有不同。但是，比如我们在将text 类型改成varchar，这种没有排序规则更改的情况下，可以不用重建索引。
这里比较绕的是二进制强制转换，什么是二进制强制转换？参考 https://developer.aliyun.com/article/228271
二进制强制转换是指两种类型的转换是“免费的“，转换过程中不需要调用任何函数，比如text 和varchr. 但是二进制转换不是对称关系，比如xml to text，是“免费的”，但是text to xml 则需要额外的检查。
那么哪些数据类型转换是二进制强制的呢？
可以使用\dC+ 查看
下面测试一下：
ivanyu=# drop table t1;
DROP TABLE
ivanyu=# create table t1 as select * from pg_class;
SELECT 413
ivanyu=# create index on t1(relowner);
CREATE INDEX
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24607
(1 row)
ivanyu=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid | oid | | |
relname | name | | |
relnamespace | oid | | |
reltype | oid | | |
reloftype | oid | | |
relowner | oid | | |
relam | oid | | |
relfilenode | oid | | |
reltablespace | oid | | |
relpages | integer | | |
reltuples | real | | |
relallvisible | integer | | |
reltoastrelid | oid | | |
relhasindex | boolean | | |
relisshared | boolean | | |
relpersistence | "char" | | |
relkind | "char" | | |
relnatts | smallint | | |
relchecks | smallint | | |
relhasrules | boolean | | |
relhastriggers | boolean | | |
relhassubclass | boolean | | |
relrowsecurity | boolean | | |
relforcerowsecurity | boolean | | |
relispopulated | boolean | | |
relreplident | "char" | | |
relispartition | boolean | | |
relrewrite | oid | | |
relfrozenxid | xid | | |
relminmxid | xid | | |
relacl | aclitem[] | | |
reloptions | text[] | C | |
relpartbound | pg_node_tree | C | |
Indexes:
"t1_relowner_idx" btree (relowner)
ivanyu=# select pg_relation_filepath('t1_relowner_idx');
pg_relation_filepath
----------------------
base/16388/24612
(1 row)
ivanyu=# alter table t1 alter column relowner type integer;
ALTER TABLEivanyu=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid | oid | | |
relname | name | | |
relnamespace | oid | | |
reltype | oid | | |
reloftype | oid | | |
relowner | integer | | | << 字段类型被更改
relam | oid | | |
relfilenode | oid | | |
reltablespace | oid | | |
relpages | integer | | |
reltuples | real | | |
relallvisible | integer | | |
reltoastrelid | oid | | |
relhasindex | boolean | | |
relisshared | boolean | | |
relpersistence | "char" | | |
relkind | "char" | | |
relnatts | smallint | | |
relchecks | smallint | | |
relhasrules | boolean | | |
relhastriggers | boolean | | |
relhassubclass | boolean | | |
relrowsecurity | boolean | | |
relforcerowsecurity | boolean | | |
relispopulated | boolean | | |
relreplident | "char" | | |
relispartition | boolean | | |
relrewrite | oid | | |
relfrozenxid | xid | | |
relminmxid | xid | | |
relacl | aclitem[] | | |
reloptions | text[] | C | |
relpartbound | pg_node_tree | C | |
Indexes:
"t1_relowner_idx" btree (relowner)
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24607 <<file id没有改变，不会导致重写
(1 row)
ivanyu=# select pg_relation_filepath('t1_relowner_idx');
pg_relation_filepath
----------------------
base/16388/24613 << 索引被重写了
(1 row)
--oid 转换到bigint 需要int8 函数转换，会导致表重写ivanyu=# alter table t1 alter column reltablespace type bigint;
ALTER TABLE
ivanyu=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid | oid | | |
relname | name | | |
relnamespace | oid | | |
reltype | oid | | |
reloftype | oid | | |
relowner | integer | | |
relam | oid | | |
relfilenode | oid | | |
reltablespace | bigint | | |
relpages | integer | | |
reltuples | real | | |
relallvisible | integer | | |
reltoastrelid | oid | | |
relhasindex | boolean | | |
relisshared | boolean | | |
relpersistence | "char" | | |
relkind | "char" | | |
relnatts | smallint | | |
relchecks | smallint | | |
relhasrules | boolean | | |
relhastriggers | boolean | | |
relhassubclass | boolean | | |
relrowsecurity | boolean | | |
relforcerowsecurity | boolean | | |
relispopulated | boolean | | |
relreplident | "char" | | |
relispartition | boolean | | |
relrewrite | oid | | |
relfrozenxid | xid | | |
relminmxid | xid | | |
relacl | aclitem[] | | |
reloptions | text[] | C | |
relpartbound | pg_node_tree | C | |
Indexes:
"t1_relowner_idx" btree (relowner)
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24614 《〈
(1 row)
ivanyu=# select pg_relation_filepath('t1_relowner_idx');
pg_relation_filepath
----------------------
base/16388/24619 《〈
(1 row)
-- 表和索引被重建。- 加非空列
加非空列会导致表重写，但是也有例外情况，比如使用虚拟列带virtual参数（PG18 才有），虚拟生成列基于原来的列，所以不会导致表重写, 以及默认值为非易失，比如常量，now(),current_timestamp,数学运算函数,时间虽然是变化，但是在一个事务中，是确定的。 常见的易失性函数为 random,currval(),timeofday(),setval, nextval(),clock_tiimestamp。
可以参见
select proname,provolatile from pg_proc where provolatile='v';
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24614
(1 row)
ivanyu=# alter table t1 add column xml_text xml;
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24614 《〈可 null 值不会
ivanyu=# alter table t1 add column test_notnull_date date default now();
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24614 《now 不会
(1 row)
ivanyu=# alter table t1 add column generated_col bigint generated always as (relowner+1) stored;
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24623 《stored 虚拟列会ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24623
(1 row)
ivanyu=# alter table t1 add column col_current_timestamp timestamptz default current_timestamp;
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24623 《〈current_timestamp 不会重写
(1 row)
ivanyu=#
ivanyu=# alter table t1 add column col_clock_timestamp timestamptz default clock_timestamp();
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24632 《〈 clock_timestamp 会重写
(1 row)
ivanyu=# create sequence t1_seq;
CREATE SEQUENCE
ivanyu=# alter table t1 add column col_nextval bigint default nextval('t1_seq');
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24640 《〈nextval 会重写
(1 row)
ivanyu=# alter table t1 add column col_curval bigint default currval('t1_seq');
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24647。《〈currval 会重写
(1 row)
- 修改表为logged or unlogged (SET LOGGED / SET UNLOGGED)
ivanyu=# select pg_relation_filepath('t1'); pg_relation_filepath
----------------------
base/16388/24647
(1 row)
ivanyu=# alter table t1 set unlogged;
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24653. <<重写
(1 row)
ivanyu=# alter table t1 set logged;
ALTER TABLE
ivanyu=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16388/24659 《重写
(1 row)
- 改变表的访问方法 set access method
由于表的底层存储格式和访问方式的变化，所以必须重写表。懒得创建新的access method，所有没有测试了。
- 添加或者修改 stored 属性生成列的表达式也会导致表重写
在PG 18 上可以测试。
表重写是一个很值得小心的操作，因为他会锁表，需要多一倍表大小的空间，还会重建索引。 数据库中有table_rewrite 事件，可以用来创建触发器以监控，或者控制表重写。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
