前幾天看到《PostgreSQL DDL變更的坑和巧妙方案》原文鏈接：http://m.sunline.cc/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進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
