金仓数据库KingbaseES 分区表修改字段类型

最新推荐文章于 2024-09-17 21:19:19 发布
最新推荐文章于 2024-09-17 21:19:19 发布
阅读量1.9k 收藏
点赞数
文章标签： 金仓数据库 kingbase 数据库 人大金仓 DBA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/arthemis_14/article/details/130620753
版权

KingbaseES普通表修改表结构请参考：KingbaseES变更表结构表重写问题

数据类型转换重写与不重写：

  • varchar(x) 转换到 varchar(y) 当 y>=x，不需要重写。
  • numeric(x,z) 转换到 numeric(y,z) 当 y>=x，或者不指定精度类型，不需要重写。
  • numeric(x,c) 转换到 numeric(y,z) 当 y=x c>z，当numeric数据类型标度不一致时，需要重写。
  • varbit(x) 转换到 varbit(y) 当 y>=x，不需要重写。
  • timestamp(x) 转换到 timestamp(y) 当 y>=x，或者转换为timestamp，不需要重写。
  • timestamptz(x) 转换到 timestamptz(y) 当 y>=x，或者转换为timestamptz，不需要重写。
  • interval(x) 转换到 interval(y) 当 y>=x ，或者转换为interval，不需要重写。
  • timestamp 转换到 text、varchar、varchar(n)，char(n)，需要重写。
  • timestamp(x)转换到 text、varchar、varchar(n)、char(n)，n>=x，需要重写。
  • text 转换到 char、char(x)、varchar(n)，需要重写。
  • text 转换到 varchar，不需要重写。
  • numeric(x) 转换到 numeric(y)，y>=x，不需要重写。
  • numeric(x) 转换到 numeric，不需要重写。
  • numeric(x,y) 转换到 numeric，不需要重写。

一、普通表的修改：

普通表数据类型长度或者精度由小改大表不会重写，索引也不会重写。

test=# create table t01(id int,name varchar(10));
CREATE TABLE
test=# insert into t01 select generate_series(1,10),substr(md5(random()::text),1,10);
INSERT 0 10
test=# create index on t01 (name);
CREATE INDEX
test=# \d t01
                          Table "public.t01"
 Column |            Type            | Collation | Nullable | Default 
--------+----------------------------+-----------+----------+---------
 id     | integer                    |           |          | 
 name   | character varying(10 char) |           |          | 
Indexes:
    "t01_name_idx" btree (name)

test=# 
test=# select sys_relation_filenode('t01'),sys_relation_filenode('t01_name_idx');
 SYS_RELATION_FILENODE | SYS_RELATION_FILENODE 
-----------------------+-----------------------
                289043 |                289046
(1 row)

# 设置客户端消息级别client_min_messages=debug5
test=# set client_min_messages=debug5;

# 修改t01表name字段长度varchar(10)为varchar(15)
test=# alter table t01 alter column name type varchar(15);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  rehashing catalog cache id 68 for sys_recyclebin; 33 tups, 16 buckets
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428092/1/7
ALTER TABLE

test=# select sys_relation_filenode('t01'),sys_relation_filenode('t01_name_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 
-----------------------+-----------------------
                289043 |                289046
(1 row)

test=#

分区表修改表结构是否跟普通表一样？

二、分区表的修改：

分区表分区键无法修改数据类型，本测试只针对非分区键进行测试。

create table tb(id bigint,pdate date,info varchar2(10)) partition by range(pdate) INTERVAL ('1 MONTH'::INTERVAL)
(
PARTITION tb_p0 VALUES LESS THAN ('2023-01-01'),
PARTITION tb_p1 VALUES LESS THAN ('2023-02-01'),
PARTITION tb_p2 VALUES LESS THAN ('2023-03-01'),
PARTITION tb_p3 VALUES LESS THAN ('2023-04-01')
);
CREATE TABLE
insert into tb select generate_series(1,100),'2023-01-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100
insert into tb select generate_series(101,200),'2023-02-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100
insert into tb select generate_series(201,300),'2023-03-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100

2.1.分区表非索引列数据类型的修改：

修改非索引列的类型：由小改大，表不会发生重写，索引自然也没有发生重写。

修改非索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# set client_min_messages =debug5;
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
test=# select oid,relname from sys_class where relname='tb';      
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
  OID   | RELNAME 
--------+---------
 289063 | tb
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p0';
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
  OID   | RELNAME  
--------+----------
 289066 | tb_tb_p0
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p1';
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
  OID   | RELNAME  
--------+----------
 289069 | tb_tb_p1
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p2';
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
  OID   | RELNAME  
--------+----------
 289072 | tb_tb_p2
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p3';
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
  OID   | RELNAME  
--------+----------
 289075 | tb_tb_p3
(1 row)

# 修改tb分区表info列为varchar(20)
test=# alter table tb alter column info type varchar(20);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428111/1/10
ALTER TABLE

test=# select oid,relname from sys_class where relname='tb';      
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
  OID   | RELNAME 
--------+---------
 289063 | tb
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p0';
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
  OID   | RELNAME  
--------+----------
 289066 | tb_tb_p0
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p1';
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
  OID   | RELNAME  
--------+----------
 289069 | tb_tb_p1
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p2';
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
  OID   | RELNAME  
--------+----------
 289072 | tb_tb_p2
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p3';
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
  OID   | RELNAME  
--------+----------
 289075 | tb_tb_p3
(1 row)

test=# alter table tb alter column info type varchar(30);                 
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428112/1/10
ALTER TABLE
test=# select oid,relname from sys_class where relname='tb';      
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
  OID   | RELNAME 
--------+---------
 289063 | tb
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p0';
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
  OID   | RELNAME  
--------+----------
 289066 | tb_tb_p0
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p1';
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
  OID   | RELNAME  
--------+----------
 289069 | tb_tb_p1
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p2';
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
  OID   | RELNAME  
--------+----------
 289072 | tb_tb_p2
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p3';
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
  OID   | RELNAME  
--------+----------
 289075 | tb_tb_p3
(1 row)

分区表修改非索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

2.2.分区表全局(GLOBAL)索引列数据类型的修改：

修改全局(GLOBAL)索引列的类型：由小改大，表不会发生重写，索引也没有发生重写。

修改全局(GLOBAL)索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# \d+ tb                          
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(10 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')

# 添加主键
test=# alter table tb add constraint tb_pk primary key (info);
ALTER TABLE
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(10 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')

test=# select sys_relation_filepath('tb_pk');                     
 SYS_RELATION_FILEPATH 
-----------------------
 base/12176/289111
(1 row)

test=# select oid,relname from sys_class where relname='tb';      
  OID   | RELNAME 
--------+---------
 289063 | tb
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p0';
  OID   | RELNAME  
--------+----------
 289066 | tb_tb_p0
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p1';
  OID   | RELNAME  
--------+----------
 289069 | tb_tb_p1
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p2';
  OID   | RELNAME  
--------+----------
 289072 | tb_tb_p2
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p3';
  OID   | RELNAME  
--------+----------
 289075 | tb_tb_p3
(1 row)

test=# alter table tb alter info type varchar(20);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428121/1/19
ALTER TABLE
test=# select sys_relation_filepath('tb_pk');                     
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_FILEPATH 
-----------------------
 base/12176/289111
(1 row)

test=# select oid,relname from sys_class where relname='tb';      
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
  OID   | RELNAME 
--------+---------
 289063 | tb
(1 row)

test=# select oid,relname from sys_class where relname='tb_tb_p2';
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
  OID   | RELNAME  
--------+----------
 289072 | tb_tb_p2
(1 row)

# 添加全局索引
test=# create unique index on tb(id,info) global;
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  building index "tb_id_info_idx" on table "tb" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428122/1/4
CREATE INDEX
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(20 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL 
    "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')

test=# select sys_relation_filepath('tb_pk');                     
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_FILEPATH 
-----------------------
 base/12176/289111
(1 row)

test=# select sys_relation_filepath('tb_id_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_FILEPATH 
-----------------------
 base/12176/289115
(1 row)

test=# alter table tb alter info type varchar(30);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428123/1/27
ALTER TABLE
test=# select sys_relation_filepath('tb_pk');                     
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_FILEPATH 
-----------------------
 base/12176/289111
(1 row)

test=# select sys_relation_filepath('tb_id_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_FILEPATH 
-----------------------
 base/12176/289115
(1 row)

分区表修改全局(GLOBAL)索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

2.3.分区表本地(LOCAL)索引列数据类型的修改：

修改本地(LOCAL)索引列的类型：由小改大，表不会发生重写，索引发生重写。

修改本地(LOCAL)索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(30 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL 
    "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')

# 创建本地索引
test=# create index on tb(pdate,info);        
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  building index "tb_tb_p0_pdate_info_idx" on table "tb_tb_p0" serially
DEBUG:  building index "tb_tb_p1_pdate_info_idx" on table "tb_tb_p1" serially
DEBUG:  building index "tb_tb_p2_pdate_info_idx" on table "tb_tb_p2" serially
DEBUG:  building index "tb_tb_p3_pdate_info_idx" on table "tb_tb_p3" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428125/1/12
CREATE INDEX
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(30 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL 
    "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
    "tb_pdate_info_idx" btree (pdate, info)
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')


test=# select sys_relation_filepath('tb_pk'),sys_relation_filepath('tb_id_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_FILEPATH | SYS_RELATION_FILEPATH 
-----------------------+-----------------------
 base/12176/289111     | base/12176/289115
(1 row)

test=# select oid,relname from sys_class where relname='tb_pdate_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
  OID   |      RELNAME      
--------+-------------------
 289119 | tb_pdate_info_idx
(1 row)

# 修改本地索引依赖info列
test=# alter table tb alter info type varchar(40);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_tb_p0_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_tb_p1_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_tb_p2_pdate_info_idx
DEBUG:  rehashing catalog cache id 68 for sys_recyclebin; 33 tups, 16 buckets
DEBUG:  drop auto-cascades to index tb_tb_p3_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  building index "tb_tb_p0_pdate_info_idx" on table "tb_tb_p0" serially
DEBUG:  building index "tb_tb_p1_pdate_info_idx" on table "tb_tb_p1" serially
DEBUG:  building index "tb_tb_p2_pdate_info_idx" on table "tb_tb_p2" serially
DEBUG:  building index "tb_tb_p3_pdate_info_idx" on table "tb_tb_p3" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428127/1/51
ALTER TABLE
test=# select sys_relation_filepath('tb_pk'),sys_relation_filepath('tb_id_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_FILEPATH | SYS_RELATION_FILEPATH 
-----------------------+-----------------------
 base/12176/289111     | base/12176/289115
(1 row)

test=# select oid,relname from sys_class where relname='tb_pdate_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
  OID   |      RELNAME      
--------+-------------------
 289127 | tb_pdate_info_idx
(1 row)

test=#

分区表本地(LOCAL)索引列修改字段长度或者是精度由小变大，表、全局索引不需要重写，但是本地索引需要重写。

2.4.分区表修改表结构总结：

  • 分区表修改非索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

  • 分区表修改全局(GLOBAL)索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

  • 分区表本地(LOCAL)索引列修改字段长度或者是精度由小变大，表、全局索引不需要重写，但是本地索引需要重写。

三、分区表变更表结构的思考：

通过以上测试可知：分区表修改本地索引依赖字段长度或者标度会导致索引重写。

如果分区表数据量大、子分区多、字段列依赖的索引多，修改分区表(本地索引)列字段长度会发生如下问题：

修改字段长度由小到大：分区表所有的子表不会发生重写，但是索引会发生重写，索引越多阻塞时间越长。

目前想到的解决方法：删除被修改列依赖的所有本地索引，避免长时间的AccessExclusiveLock，修改完成后子表使用concurrently的方式创建。

修改字段长度由大到小：分区表所有的子表都会发生重写，只要表发生重写所有的索引都需要重写，此过程会导致业务不可用(影响太大)。

对分区表使用detach？分区表要求所有子分区的表结构必须跟父表一致，所有的子分区全部修改完成后再attach回去，跟直接改区别不大，可以避免业务中断。需要根据业务提前准备好脚本。 更多信息，参见https://help.kingbase.com.cn/v8/index.html

沉舟侧畔千帆过_
关注
KingbaseEs V8R3（人大金仓）常用sql命令（超全命令汇集）
多喝清晨的粥
01-24 3万+
KingbaseEs V8R3（人大金仓）常用命令（超详细）
金仓数据库字段_mysql迁移到金仓数据库（V8R3）
weixin_29838501的博客
12-23 1502
mysql迁移到金仓数据库(V8R3)方案​ 本次迁移为停机停服迁移，无需保障服务不中断，不存在在线数据实时迁移问题。为了实现mysql数据库迁移到金仓数据库，我们需要从以下几方面考虑解决方案金仓与mysql数据库差异/字段类型/语法差别文档(无官方文档，自己梳理)mysql迁移到金仓数据抽取&转换过程(金仓已提供)数据一致性校验工具(金仓迁移工具提供数据量对比)sql自动检查...
金仓数据库 KingbaseES参考手册 (8. 函数（十））
最新发布
wu526985的博客
09-17 935
要删除 中的所有字符from_string，请将另一个字符连接到开头from_string并将此字符指定为to_string. 例如，TRANSLATE( expr, ' x0123456789', ' x') 从 中删除所有数字expr。expr可以是计算结果为CHAR类型字符串的任何表达式，VARCHAR、NCHAR或NVARCHAR，类型为NUMBER、BINARY_FLOAT或BINARY_DOUBL的数值或null 的任何表达式。使用可选的数字格式fmt将n转换为VARCHAR数据类型的值。
PostgreSQL KingBase人大金仓 迁移数据库用到的SQL 修改字段名、字段类型
Xiaoweidumpb
05-25 1170
【代码】PostgreSQL KingBase人大金仓 迁移数据库用到的SQL 修改字段名、字段类型。
kingbase批量修改表字段类型
点赞不迷路
03-23 570
【代码】kingbase批量修改表字段类型。
KingbaseES 分区表修改字段类型
lyu1026的博客
05-09 1443
KingbaseES普通表修改表结构请参考：KingbaseES变更表结构表重写问题 数据类型转换重写与不重写： varchar(x) 转换到 varchar(y) 当 y>=x，不需要重写。 numeric(x,z) 转换到 numeric(y,z) 当 y>=x，或者不指定精度类型，不需要重写。 numeric(x,c) 转换到 numeric(y,z) 当 y=x c>...
金仓数据库创建表、修改（表名称、字段名称、字段类型）、删除表，添加或者删除字段
m0_75101712的博客
10-19 2452
test=# \dt修改表名修改列名修改字段类型添加字段删除字段删除表DROP TABLE。
人大金仓V8.0数据库全套使用手册 ，kingbaseEs v8.0
04-08
人大金仓数据库是一款由中国人民大学信息学院开发的国产关系型数据库管理系统，其最新版本为V8.0，命名为KingbaseES。本套使用手册详细涵盖了该数据库系统的各个方面，旨在帮助用户全面了解并熟练掌握KingbaseES V...
金仓数据库KingbaseES SYS_BULKLOAD工具的使用
Kingbase_的博客
02-22 1266
介绍 sys_bulkload是KingbaseES提供的快速加载数据的命令行工具。用户使用sys_bulkload工具能够把一定格式的文本数据简单、快速的加载到KingbaseES数据库中，或将KingbaseES数据库中的数据快速导出到CSV文件中。 使用前需要用户手动创建sys_bulkload插件，需要说明的是sys_bulkload工具是随数据库版本一同发布的，使用时请使用对应发布的数据库版本，其他数据版本则不保证能正常工作。 架构图 sys_bulkload主要包括两个模块：reader和wri
KingbaseES V8R2 开发规范
10-26
人大金仓数据库KingbaseES V8R2的开发规范为数据库开发人员提供了细致的指南，涵盖了命名规范、设计规范、索引使用、数据类型选择等多个方面，旨在提高数据库开发效率、性能和维护性。下面对KingbaseES V8R2开发规范...
迁移Oracle到KingbaseES | KingbaseES数据库配置
Kingbase_的博客
12-21 1028
一、 迁移前的准备 在使用EasyTransfer Tool迁移Oracle数据库之前，应先做如下准备工作：（本文档以Oracle 12c（源库）迁移到KingbaseES V8R6（目标库）案例为蓝本。） 1.1 获取源库Oracle基本信息  IP地址；  实例名；  网络服务端口号；  用户名/密码。 1.2 在目标库KingbaseES上执行一下操作  创建与源Oracle用户（如scott）同名的用户（scott）；  创建与源Oracle（如prod）同名的数据库（prod），属主
脚本修改字段名、数据类型
03-30
用SQL脚本修改数据库的表字段名、数据类型、是否为空
金仓SQL语法详解
04-12
本资源为金仓数据SQL语法详解文档，kingbase SQL 欢迎大家下载
Kingbase_database_金仓数据库入门_以及基础应用
01-08
Kingbase_database_金仓数据库入门_以及基础应用
人大金仓数据库系统表
01-30
国产人大金仓数据库系统表说明文档。是4.1版本的。虽然现在的版本都到6了，应该没什么太大变化。
人大金仓数据库kingbaseESV6SQL参考手册
08-13
人大金仓数据库kingbaseESV6SQL参考手册,包含安装手册和数据库使用手册，如常见函数，存储过程，自带函数的使用方法。
KingbaseES CTID 与 Oracle ROWID
lyu1026的博客
04-26 1121
熟悉oracle的人都知道ROWID可用于快速的数据访问，KingbaseES 由于自身MVCC机制的原因，ctid 作为 oracle rowid 的替代方案不合适，但currtid 还是基本可以满足rowid 的功能的。本文向大家介绍如何通过currtid 实现rowid 的功能。 一、Oracle ROWID Oracle ROWID 记录了tuple的物理存储位置，通过ROWID可以非常...
人大金仓数据库表修改某个字段为自增长总结
壮乡码农
06-25 4822
人大金仓数据库 修改字段类型 不支持改为serial ，且人大金仓数据修改字段类型要在字段后加一个 TYPE 标识 人大金仓数据库字段有值后， 只要字段唯一不重复，可以添加主键ALTER TABLE CPT_DocTpl ADD PRIMARY KEY(id); 人大金仓数据库不支持直接修改为serial，可通过自定义序列后设置字段默认值实现字段自增长: CREATE SEQUENCE 序列名称 START 34; //创建一个序列CPT_DocTpl_id_SEQ ，从34开始自增，默认为正增长，.
kingbase人大金仓数据库修改(增加)表字段
为谁伫立在风中
07-22 4347
kingbase中更新列更新列的属性的语法是：ALTER TABLE [SchemaName.]TableName ALTER [ COLUMN ] ColumnName TYPE
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值