首页 > 其他分享 >KingbaseES修改表结构-表的重写

KingbaseES修改表结构-表的重写

时间:2023-04-06 18:47:54浏览次数:54  
标签:name 修改 KingbaseES sys DEBUG t03 test 重写 id

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数据表OID已经发生改变
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()会导致表的重写。对于表中已经存在的行,查询时直接返回 attmissingval 属性的值。插入新的数据行(包括更新已有的行)时,使用用户提供的值或者默认值进行填充,查询时不需要使用 attmissingval。

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 和 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列表OID没有发生变化

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 | 

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;

新增的默认值假如是stable或者immutable类型的函数,不需要重写表,比如alter table t01 add column t_time timestamp default now() not null;

新增的默认值假如是volatile类型的函数,需要重写表,比如alter table t01 add column id int default random() not null;

执行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释放空间。

标签:name,修改,KingbaseES,sys,DEBUG,t03,test,重写,id
From: https://www.cnblogs.com/nwwhile/p/17293755.html

相关文章

  • advisor复合电源模型。 采用新增构型方法修改的复合电源模型
    advisor复合电源模型。采用新增构型方法修改的复合电源模型,比advisor书上那种在纯电基础上修改好很多,因为保留了自带的纯电模型,所以可方便比较有无超级电容的影响。模型运行完全正常 无报错。YID:8899615835728630......
  • suse12sp5主机passwd修改密码不生效排查
    问题现象修改账号密码报错passwd:Authenticationtokenmanipulationerror  解决方案:1、查看磁盘空间dfTH与df-ih2、查看文件是否有隐藏权限lsatter/etc/passwd/etc/shadow3、查看messages日志无明显报错4、最后将required放最后一行解决  required栈成......
  • 关于修改前端请求原生request加锁问题
    1正常情况后端获取前端的request请求:(原生已加锁,修改失效校验)Map<String,String[]>parameterMap=req.getParameterMap();......报错:错误:java.lang.IllegalStateException:NomodificationsareallowedtoalockedParameterMapatorg.apache.catalina.util.Paramet......
  • 直播平台软件开发,简单易修改的弹框组件
    直播平台软件开发,简单易修改的弹框组件弹窗组件适用框架vue,uniapp使用再uniapp框架中可简单修改标签与尺寸单位后使用px与rpx <!--vue--><template><divv-show="ishide"@touchmove.stop.prevent><!--遮罩--><divclass="mask":style="maskStyle">......
  • 直播网站源码,修改el-input边框颜色
    直播网站源码,修改el-input边框颜色.el-input{ --el-input-hover-border-color:#f56c6c; --el-input-focus-border-color:#f56c6c;}​以上就是直播网站源码,修改el-input边框颜色,更多内容欢迎关注之后的文章 ......
  • js 修改当前的css
    CSSStyleSheet.insertRule()CSSStyleSheet.insertRule(".red::before{color:lightgray;content:"测试"}",0)CSSStyleSheet.addRule()已经移除废弃stylesheet.deleteRule(index)CSSStyleSheet.replace()详情见官网https://developer.mozilla.org/......
  • Ubuntu 修改root密码(初始密码)
    转发自文章:Ubuntu设置修改root密码(初始密码)ubuntu默认的root用户是没有固定密码的,它的密码是随机产生并且动态改变的,即每次开机都有一个新的root密码,如果想查看root密码,那么直接设置的root密码即可。使用安装时候的用户登录进入终端在终端输入命令sudopasswdroot#回车会显......
  • Calibre修改保存电子书为中文
    已知路径肯定是英文的,,,保存到文件夹的时候可以有中文。3张图解决问题......
  • mysql8.0修改root密码
    修改密码按大部分教程的操作结束,进行登录的时候还是会出现ERROR1045(28000):Accessdeniedforuser‘root’@‘localhost’(usingpassword:YES)经过多次尝试,终于解决这个问题。以下是操作过程记录。关键为第二步。1.确保mysqlserver已经停止运行查询mysqlserver......
  • 读取和修改GPIO配置参数的ADB命令
    1、读取每个GPIO的参数adbshellcat/sys/devices/virtual/misc/mtgpio/pin打印出来的参数从左到右分别是Pin:modepullseldindoutpullendirdinv(inversion) 2、修改GPIO的参数adbshellecho-w=[pin]:[mode][pullsel][din][dout][pull......