KingbaseES变更表结构表重写问题
在实际项目使用数据库的过程中修改字段类型这类需求比较常见。
一、修改表字段类型需要知道：
1.修改表结构可能会导致表进行重写（表OID发生变化）。
2.修改表结构带有索引或者字段类型长度或者精度操作时，会触发索引重建。
3.重建索引操作，对于大表需要耗时数个小时或更长，在这个过程中会发生锁表操作，造成业务无法进行或业务中断。
4.修改表结构会占用大量系统资源内存、磁盘空间等，可能会导致数据库core宕机或者OOM。
二、添加字段
使用ADD COLUMN给数据表添加具有volatile属性默认值的列需要重写整个表及其索引。
KingbaseES三态参考：https://www.cnblogs.com/kingbase/p/17003012.html
test=# create table t01(id numeric,dat date);
CREATE TABLE
test=# select sys_relation_filenode('t01');
SYS_RELATION_FILENODE
-----------------------
190997
(1 row)
test=# \x
Expanded display is on.
test=# select * from sys_attribute where attrelid='t01'::regclass and attname='id';
-[ RECORD 1 ]-+--------------
ATTRELID | 190997
ATTNAME | id
ATTTYPID | 1700
ATTSTATTARGET | -1
ATTLEN | -1
ATTNUM | 1
ATTNDIMS | 0
ATTCACHEOFF | -1
ATTTYPMOD | -1
ATTBYVAL | f
ATTSTORAGE | m
ATTALIGN | i
ATTNOTNULL | f
ATTHASDEF | f
ATTHASMISSING | f
ATTIDENTITY |
ATTGENERATED |
ATTISDROPPED | f
ATTISLOCAL | t
ATTINHCOUNT | 0
ATTCOLLATION | 0
ATTACL |
ATTOPTIONS | {column_id=1}
ATTFDWOPTIONS |
ATTMISSINGVAL |
1.数据表添加volatile属性的默认值列：
test=# select sys_relation_filenode('t01');
SYS_RELATION_FILENODE
-----------------------
190997
(1 row)
test=# alter table t01 add column tid numeric default random();
ALTER TABLE
# 数据表添加volatile DEFAULT列，可以看到t01数据表 filenode 已经发生改变
test=# select sys_relation_filenode('t01');
SYS_RELATION_FILENODE
-----------------------
191005
(1 row)
test=# select * from sys_attribute where attrelid='t01'::regclass and attname='tid';
-[ RECORD 1 ]-+--------------
ATTRELID | 190997
ATTNAME | tid
ATTTYPID | 1700
ATTSTATTARGET | -1
ATTLEN | -1
ATTNUM | 3
ATTNDIMS | 0
ATTCACHEOFF | -1
ATTTYPMOD | -1
ATTBYVAL | f
ATTSTORAGE | m
ATTALIGN | i
ATTNOTNULL | f
ATTHASDEF | t
ATTHASMISSING | f
ATTIDENTITY |
ATTGENERATED |
ATTISDROPPED | f
ATTISLOCAL | t
ATTINHCOUNT | 0
ATTCOLLATION | 0
ATTACL |
ATTOPTIONS | {column_id=3}
ATTFDWOPTIONS |
ATTMISSINGVAL |
# PROVOLATILE列V代表volatile
test=# select proname,provolatile from pg_proc where proname='random';
-[ RECORD 1 ]-------
PRONAME | random
PROVOLATILE | v
**新增volatile属性默认值列例如 random()会导致表的重写。ATTHASMISSING = 'f' ，也就是该列的数据不存在缺失（每行值在alter 的同时更新）。
2.数据表添加非volatile属性默认值的列：
test=# alter table t01 add con varchar2(10) default 'kes';
ALTER TABLE
# 数据表添加非volatile DEFAULT列表OID没有发生变化
test=# select sys_relation_filenode('t01');
-[ RECORD 1 ]---------+-------
SYS_RELATION_FILENODE | 191005
test=# select * from sys_attribute where attrelid='t01'::regclass and attname='con';
-[ RECORD 1 ]-+--------------
ATTRELID | 190997
ATTNAME | con
ATTTYPID | 1043
ATTSTATTARGET | -1
ATTLEN | -1
ATTNUM | 4
ATTNDIMS | 0
ATTCACHEOFF | -1
ATTTYPMOD | 14
ATTBYVAL | f
ATTSTORAGE | x
ATTALIGN | i
ATTNOTNULL | f
ATTHASDEF | t
ATTHASMISSING | t
ATTIDENTITY |
ATTGENERATED |
ATTISDROPPED | f
ATTISLOCAL | t
ATTINHCOUNT | 0
ATTCOLLATION | 100
ATTACL |
ATTOPTIONS | {column_id=4}
ATTFDWOPTIONS |
ATTMISSINGVAL | {kes}
新增非volatile属性默认值列原理就是在表sys_attribute 中添加两个字段：atthasmissing = 't' 和 attmissingval。该值存储到sys_attribute 表对应的 attmissingval 列中，并且将 atthasmissing 的值设置为 true。因此不需要重写表。
3.对数据表添加stable或者immutable属性默认值的列：
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
dat | date | | |
tid | numeric | | |
con | character varying(10 char) | | |
test=# select sys_relation_filepath('t01');
SYS_RELATION_FILEPATH
-----------------------
base/12176/191062
(1 row)
test=#
test=# alter table t01 alter column dat set default now();
ALTER TABLE
# 数据表添加非volatile DEFAULT列表 filenode 没有发生变化
test=# select sys_relation_filepath('t01');
SYS_RELATION_FILEPATH
-----------------------
base/12176/191062
(1 row)
test=# \x
Expanded display is on.
test=# select * from sys_attribute where attrelid='t01'::regclass and attname='dat';
-[ RECORD 1 ]-+--------------
ATTRELID | 190997
ATTNAME | dat
ATTTYPID | 8020
ATTSTATTARGET | -1
ATTLEN | 8
ATTNUM | 2
ATTNDIMS | 0
ATTCACHEOFF | -1
ATTTYPMOD | -1
ATTBYVAL | t
ATTSTORAGE | p
ATTALIGN | d
ATTNOTNULL | f
ATTHASDEF | t
ATTHASMISSING | f
ATTIDENTITY |
ATTGENERATED |
ATTISDROPPED | f
ATTISLOCAL | t
ATTINHCOUNT | 0
ATTCOLLATION | 0
ATTACL |
ATTOPTIONS | {column_id=2}
ATTFDWOPTIONS |
ATTMISSINGVAL | {"2023-04-11 19:53:58"}
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
dat | date | | | now()
tid | numeric | | |
con | character varying(10 char) | | |
# PROVOLATILE列s代表stable
test=# select proname,provolatile from pg_proc where proname='now';
PRONAME | PROVOLATILE
---------+-------------
now | s
(1 row)
test=# alter table t01 add sdate timestamp default now();
ALTER TABLE
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
dat | date | | | now()
tid | numeric | | |
con | character varying(10 char) | | |
sdate | timestamp without time zone | | | now()
test=# select sys_relation_filepath('t01');
SYS_RELATION_FILEPATH
-----------------------
base/12176/191062
(1 row)
新增字段默认值是stable或者immutable类型的函数，不需要重写表。
4.对数据表进行VACUUM FULL操作：
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+----------------
id | numeric | | |
dat | date | | |
tid | numeric | | | random()
con | character varying(10 char) | | | 'kes'::varchar
test=# vacuum FULL t01;
VACUUM
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+----------------
id | numeric | | |
dat | date | | |
tid | numeric | | | random()
con | character varying(10 char) | | | 'kes'::varchar
test=# \x
Expanded display is off.
test=# select sys_relation_filepath('t01');
SYS_RELATION_FILEPATH
-----------------------
base/12176/191012
(1 row)
test=# \x
Expanded display is on.
test=# select * from sys_attribute where attrelid='t01'::regclass and attname='con';
-[ RECORD 1 ]-+--------------
ATTRELID | 190997
ATTNAME | con
ATTTYPID | 1043
ATTSTATTARGET | -1
ATTLEN | -1
ATTNUM | 4
ATTNDIMS | 0
ATTCACHEOFF | -1
ATTTYPMOD | 14
ATTBYVAL | f
ATTSTORAGE | x
ATTALIGN | i
ATTNOTNULL | f
ATTHASDEF | t
ATTHASMISSING | f
ATTIDENTITY |
ATTGENERATED |
ATTISDROPPED | f
ATTISLOCAL | t
ATTINHCOUNT | 0
ATTCOLLATION | 100
ATTACL |
ATTOPTIONS | {column_id=4}
ATTFDWOPTIONS |
ATTMISSINGVAL |
执行 VACUUM FULL TABLE 操作（VACUUM FULL也会重写表），数据表相应的 atthasmissing 和 attmissingval 属性将会被清除，因为系统不再需要这些值。
添加字段总结：
新增带默认值的字段可以不用重写表，通过sys_attribute中的atthasmissing和attmissingval来标识，分以下情况：
新增的默认值假如是一个常量，不需要重写，比如alter table t01 add column info text default 'kes' not null; 不管是否有not null 限制，都不需要重写表（更新每行记录）
新增的默认值假如是stable或者immutable类型的函数，不需要重写表，比如alter table t01 add column t_time timestamp default now() not null; 不管是否有not null 限制，都不需要重写表（更新每行记录）。 这是因为，所有已有的行的now() 值都相同，可以直接用ATTMISSINGVAL
新增的默认值假如是volatile类型的函数，需要重写表，比如alter table t01 add column id int default random() not null; 不管是否有not null 限制，都要重写表（更新每行记录）。这是因为 volatile 类型的函数，不同执行点返回的值不同。
执行VACUUM FULL TABLE 操作需要重写，并且数据表相应的 atthasmissing 和 attmissingval 属性将会被清除。
三、修改字段：
根据文档所说更改数据表现有列类型时，使用USING子句不更改列内容，并且旧类型对新类型是二进制兼容的，不需要重写表；但受影响列上的任何索引仍必须重建。
对于大表，表或索引重建需要花费大量时间，并且需要临时占用表或索引大小两倍的磁盘空间。
1.数据表字段类型长度或者精度由小变大且新类型与旧类型二进制兼容
test=# create table t03(id numeric(5,2),sdate date,info varchar(10));
CREATE TABLE
test=# \d t03
Table "public.t03"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | numeric(5,2) | | |
sdate | date | | |
info | character varying(10 char) | | |
test=# create index t03_id_idx on t03(id);
CREATE INDEX
test=# create index t03_info_idx on t03(info);
CREATE INDEX
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191107 | 191110 | 191111
(1 row)
test=# set client_min_messages TO debug5;
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
test=# alter table t03 alter column id type numeric(6,2);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 333998/1/6
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191107 | 191110 | 191111
(1 row)
test=# alter table t03 alter column info type varchar(20);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 333999/1/6
ALTER TABLE
test=# \d t03
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
Table "public.t03"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | numeric(6,2) | | |
sdate | date | | |
info | character varying(20 char) | | |
Indexes:
"t03_id_idx" btree (id)
"t03_info_idx" btree (info)
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191107 | 191110 | 191111
(1 row)
test=# alter table t03 alter info type text;
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: building index "pg_toast_191107_index" on table "pg_toast_191107" serially
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334000/1/10
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191107 | 191110 | 191111
(1 row)
数据表字段类型长度或者精度由小变大的操作，不需要rewrite重写表。
不修改列内容且旧类型与新类型二进制兼容（binary coercible），不需要重写表。
2.数据表字段类型长度或者精度由大变小或新类型与旧类型不二进制兼容：
test=# create table t03(id int,sdate date,info varchar(10));
CREATE TABLE
test=# create index t03_id_idx on t03(id);
CREATE INDEX
test=# create index t03_info_idx on t03(info);
CREATE INDEX
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191118 | 191121 | 191122
(1 row)
test=# set client_min_messages = debug5;
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
test=# alter table t03 alter column id type bigint;
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: rewriting table "t03"
DEBUG: building index "t03_info_idx" on table "t03" serially
DEBUG: building index "t03_id_idx" on table "t03" serially
DEBUG: drop auto-cascades to type pg_temp_191118
DEBUG: drop auto-cascades to type pg_temp_191118[]
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334005/1/15
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191124 | 191128 | 191127
(1 row)
test=# alter table t03 alter column info type varchar(20);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334006/1/6
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191124 | 191128 | 191127
(1 row)
test=# alter table t03 alter column info type varchar2(20);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334007/1/6
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191124 | 191128 | 191127
(1 row)
test=# alter table t03 alter column info type varchar2(30);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334008/1/6
ALTER TABLE
test=# alter table t03 alter column info type varchar(10);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: rewriting table "t03"
DEBUG: building index "t03_id_idx" on table "t03" serially
DEBUG: building index "t03_info_idx" on table "t03" serially
DEBUG: drop auto-cascades to type pg_temp_191118
DEBUG: drop auto-cascades to type pg_temp_191118[]
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334009/1/15
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191133 | 191136 | 191137
(1 row)
test=#
数据表字段类型长度或者精度由大变小或新类型与旧类型不二进制兼容会发生重写。
3.数据表发生重写，索引也会发生重写：
对数据表进行vacuum操作
test=# \d t03
Table "public.t03"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | numeric(5,2) | | |
sdate | date | | |
info | character varying(10 char) | | |
Indexes:
"t03_id_idx" btree (id)
"t03_info_idx" btree (info)
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191139 | 191143 | 191142
(1 row)
test=# set client_min_messages = debug5;
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
test=# alter table t03 alter column id type numeric(6,3);
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: rehashing catalog cache id 68 for sys_recyclebin; 17 tups, 8 buckets
DEBUG: rewriting table "t03"
DEBUG: building index "t03_info_idx" on table "t03" serially
DEBUG: building index "t03_id_idx" on table "t03" serially
DEBUG: drop auto-cascades to type pg_temp_191118
DEBUG: drop auto-cascades to type pg_temp_191118[]
DEBUG: EventTriggerInvoke 13761
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334012/1/15
ALTER TABLE
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191145 | 191149 | 191148
(1 row)
test=# vacuum full t03;
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: vacuuming "public.t03"
DEBUG: "t03": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
DEBUG: building index "t03_info_idx" on table "t03" serially
DEBUG: building index "t03_id_idx" on table "t03" serially
DEBUG: drop auto-cascades to type pg_temp_191118
DEBUG: drop auto-cascades to type pg_temp_191118[]
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 334013/1/11
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
VACUUM
test=# select sys_relation_filenode('t03'),sys_relation_filenode('t03_id_idx'),sys_relation_filenode('t03_info_idx');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SYS_RELATION_FILENODE | SYS_RELATION_FILENODE | SYS_RELATION_FILENODE
-----------------------+-----------------------+-----------------------
191150 | 191154 | 191153
(1 row)
对数据表进行vacuum full操作，数据表索引均会进行重写。
4.使用USING子句：
4.1 修改字段类型、长度（表无数据）：
test=# create table t02(id int,sdate date,info varchar(10));
CREATE TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | integer | | |
sdate | date | | |
info | character varying(10 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191021
(1 row)
test=# create table t02(id int,sdate date,info varchar(10));
CREATE TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | integer | | |
sdate | date | | |
info | character varying(10 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191021
(1 row)
# 修改表字段类型int为bigint，数据表未重写
test=# alter table t02 alter column id type bigint;
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191024
(1 row)
# 修改表字段类型date为timestamp，数据表未重写
test=# alter table t02 alter column sdate type timestamp;
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191024
(1 row)
# 修改表字段类型varchar(10)为varchar(20)，数据表未重写
test=# alter table t02 alter column info type varchar(20);
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191024
(1 row)
# 修改表字段类型varchar(20)为text，数据表未重写
test=# alter table t02 alter column info type text;
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191024
(1 row)
# 修改表字段类型text为varchar2(40)，数据表发生重写
test=# alter table t02 alter column info type varchar2(40);
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191030
(1 row)
# 修改表字段类型varchar2(40)为varchar2(50)，数据表未重写
test=# alter table t02 alter column info type varchar2(50);
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191030
(1 row)
# 修改表字段类型varchar2(50)为text，数据表未重写
test=# alter table t02 alter column info type text;
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191030
(1 row)
# 修改表字段类型text为varchar2(50)，数据表发生重写
test=# alter table t02 alter column info type varchar2(50);
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191036
(1 row)
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | |
sdate | timestamp without time zone | | |
info | character varying(50 char) | | |
# 修改表字段类型varchar2(50)为varchar2(30)，数据表发生重写
test=# alter table t02 alter column info type varchar2(30);
ALTER TABLE
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191039
(1 row)
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
通过以上过程可以发现：
修改表字段类型varchar(x) 到varchar(y) ，y大于等于x时，修改字段不会发生重写。
修改表字段类型varchar、varchar2到text类型，数据表不会发生重写。
修改表字段类型date到timestamp类型，数据表不会发生重写。
修改表字段类型text到varchar、varchar2，数据表会发生重写。
修改表字段类型int到bigint，数据表会发生重写。
总结：
数据表字段类型长度或者精度由小变大的操作，不需要rewrite重写表。
数字类型int4到int8这种更改，需要重写数据表，主要是由于底层存储不一样。
不修改列内容且旧类型与新类型二进制兼容（binary coercible），不需要重写表。
二进制可兼容表示该转换可以被“免费”执行而不用调用任何函数。要求相应的值使用同样的内部表示。
二进制兼容不要求必须是对称关系（两种类型双向都二进制值兼容的类型也被称作二进制兼容）。
4.2 使用using关键字进行数据类型修改：
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191039
(1 row)
test=# alter table t02 alter column id type timestamp;
ERROR: column "id" cannot be cast automatically to type timestamp without time zone
HINT: You might need to specify "USING id::timestamp without time zone".
# 使用using关键字方式本质上就是使用旧值重新计算了一次，所以也会发生重写
test=# alter table t02 alter column id type date using(sysdate +id * interval '1 min');
ALTER TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | date | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191042
(1 row)
test=# alter table t02 alter column id type int using extract(epoch from id)::integer;
ALTER TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191045
(1 row)
使用using关键字方式本质上就是把表中字段已有的值重新计算了一次，所以也会发生重写。
使用自定义cast进行类型转换：
表中无数据进行修改列：
# 创建自定义转换
create cast (integer as timestamp) with inout as assignment;
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+----------------
id | integer | | |
dat | date | | |
tid | numeric | | | random()
con | character varying(10 char) | | | 'kes'::varchar
test=#
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191051
(1 row)
test=# create cast (integer as timestamp) with inout as assignment;
CREATE CAST
# 进行数据类型修改
test=# alter table t02 alter column id type timestamp;
ALTER TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# select sys_relation_filenode('t02');
SYS_RELATION_FILENODE
-----------------------
191068
(1 row)
表中有数据进行修改列：结合function进行修改
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# insert into t02 values (1,sysdate,'123123112');
INSERT 0 1
# 表种有数据，使用cast提示无效的timestamp
test=# alter table t02 alter column id type timestamp;
ERROR: invalid input syntax for type timestamp: "1"
test=#
# 创建自定义函数
create or replace function cast_int_to_timestamp(int) returns timestamp as $$
select sysdate + $1 * interval '1 min'
$$ language sql strict ;
create cast (int as timestamp) with function cast_int_to_timestamp as assignment;
# 创建自定义函数、cast进行数据类型修改
test=# create or replace function cast_int_to_timestamp(int) returns timestamp as $$
test$# select sysdate + $1 * interval '1 min'
test$# $$ language sql strict ;
CREATE FUNCTION
test=# create cast (int as timestamp) with function cast_int_to_timestamp as assignment;
ERROR: cast from type integer to type timestamp without time zone already exists
test=# drop cast(int as timestamp);
DROP CAST
test=# create cast (int as timestamp) with function cast_int_to_timestamp as assignment;
CREATE CAST
test=# alter table t02 alter column id type timestamp;
ALTER TABLE
test=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
sdate | timestamp without time zone | | |
info | character varying(30 char) | | |
test=# select * from t02;
ID | SDATE | INFO
---------------------+---------------------+-----------
2023-03-31 14:45:23 | 2023-03-31 14:40:26 | 123123112
(1 row)
四、删除字段：
删除字段在KingbaseES的操作是最快的，KingbaseES删除字段只是将字段在系统表中设为不可见。
test=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | timestamp without time zone | | |
dat | date | | | now()
tid | numeric | | |
con | character varying(10 char) | | |
sdate | timestamp without time zone | | | now()
test=# alter table t01 drop column sdate;
ALTER TABLE
test=# \d+ t01
Table "public.t01"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | timestamp without time zone | | | | plain | |
dat | date | | | now() | plain | |
tid | numeric | | | | main | |
con | character varying(10 char) | | | | extended | |
Access method: heap
test=# select * from sys_attribute where attrelid = 't01'::regclass and attname not in ('tableoid','cmax','xmax','cmin','xmin','ctid','id');
ATTRELID | ATTNAME | ATTTYPID | ATTSTATTARGET | ATTLEN | ATTNUM | ATTNDIMS | ATTCACHEOFF | ATTTYPMOD | ATTBYVAL | ATTSTORAGE | ATTALIGN | ATTNOTNULL | ATTHASDEF | ATTHASMISSING | ATTIDENTITY | ATTGENERATED | ATTISDROPPED | ATTISLOCAL | ATTINHCOUNT | ATTCOLLATION | ATTACL | ATTOPTIONS | ATTFDWOPTIONS | ATTMISSINGVAL
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+---------------+---------------+---------------
190997 | dat | 8020 | -1 | 8 | 2 | 0 | -1 | -1 | t | p | d | f | t | f | | | f | t | 0 | 0 | | {column_id=2} | |
190997 | tid | 1700 | -1 | -1 | 3 | 0 | -1 | -1 | f | m | i | f | f | f | | | f | t | 0 | 0 | | {column_id=3} | |
190997 | con | 1043 | -1 | -1 | 4 | 0 | -1 | 14 | f | x | i | f | f | f | | | f | t | 0 | 100 | | {column_id=4} | |
190997 | ........kb.dropped.5........ | 0 | 0 | 8 | 5 | 0 | -1 | -1 | t | p | d | f | f | f | | | t | t | 0 | 0 | | {column_id=5} | |
(4 rows)
KingbaseES删除字段后，空间不会马上释放，而是随着更新，空间会逐渐的被回收。可以手动直接执行vacuum full或者cluster释放空间。
KINGBASE研究院
