首页 > 其他分享 >KingbaseES 分区表修改字段类型

KingbaseES 分区表修改字段类型

时间：2023-05-09 19:45:23浏览次数：34  
标签：00 name tb 修改 state 分区表 DEBUG INPROGRESS KingbaseES

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回去，跟直接改区别不大，可以避免业务中断。需要根据业务提前准备好脚本。

标签：00,name,tb,修改,state,分区表,DEBUG,INPROGRESS,KingbaseES
From： https://www.cnblogs.com/kingbase/p/17370088.html

相关文章

  • KingbaseES 使用sys_bulkload远程导入
    前言sys_bulkload常见场景是本地导入数据，也可以在远程运行sys_bulkload，对数据库上的CSV文件进行导入。远程导入数据时候需要注意，csv文件和ctl文件所在服务器。以下举例展示整个远程导入的过程。测试环境V8R6C7演示目的将数据从IP2所在服务器导入到IP3远程服务器上。IP3......
  • KingbaseES 复制冲突之锁类型冲突
    背景昨天遇到客户现场的一个有关复制冲突的问题备库报错：ERROR:cancelingstatementduetoconflictwithrecovery，userwasholdingarelationlockfortoolong现场情景是备库执行逻辑备份过程中出现的报错，逻辑备份相当于备库查询语句，snapshot，这时主库业务繁忙，对备库查询......
  • KingbaseES V8R6 等待事件之LWLock Buffer_IO
    等待事件含义当进程同时尝试访问相同页面时，等待其他进程完成其输入/输出（I/O）操作时，会发生LWLock:BufferIO等待事件。其目的是将同一页读取到共享缓冲区中。每个共享缓冲区都有一个与LWLock:BufferIO等待事件相关联的I/O锁，每次都必须在共享缓冲区外部检索页。此锁用于处理多个会......
  • KingbaseES V8R3 集群运维系列 -- failover切换后集群自动恢复
    ​案例说明：KingbaseESV8R3集群默认在触发failover切换后，为保证数据安全，原主库需要通过人工介入后，恢复为新的备库加入到集群。在无人值守的现场环境，需要在触发failover切换后，主库可以自动恢复为新备考加入集群，提升架构的高可用性。适用版本：KingbaseESV8R3集群架构：node_id......
  • rocky linux: 修改sshd的默认端口(Rocky Linux 9.1)
    一，修改防火墙，允许指定的新端口访问[root@img~]#firewall-cmd--zone=public--add-port=31234/tcp--permanentsuccess[root@img~]#firewall-cmd--reloadsuccess[root@img~]#more/etc/firewalld/zones/public.xml<?xmlversion="1.0"encoding="utf-8"......
  • rocky linux:修改hostname(Rocky Linux 9.1)
    一，修改hostname:1,通过hostnamectl命令修改hostname[root@blog~]#more/etc/hostnameblog[root@blog~]#hostnamectlset-hostnameimg[root@blog~]#more/etc/hostnameimg可以看到原本保存在/etc/hostname中的值在用hostnamectl命令处理后发生了变化所以我们也可以......
  • uniapp 中的 rich-text 富文本 怎样编辑修改 样式
    1.首先在HTML中 <rich-text:nodes="formatRichText(newObjc)"></rich-text>2.在 methods中写入方法formatRichText(html){//html就是你要传进来地富文本参数//去掉img标签里的style、width、height属性......
  • flutter系列之:做一个修改组件属性的动画
    目录简介flutter中的动画widgetAnimatedContainers使用举例总结简介什么是动画呢？动画实际上就是不同的图片连续起来形成的。flutter为我们提供了一个AnimationController来对动画进行详尽的控制，不过直接是用AnimationController是比较复杂的，如果只是对一个widget的属性进行修改，......
  • 黑群晖忘记登录密码如何找回修改
    一、问题简述大家都知道，白群晖上有RESET按钮，如果忘记密码，只要按住四秒钟直到哔声响将会重设管理者密码，我们很多人装的是黑群晖，根本没有reset键，如果你也没有设置找回邮箱，那忘记密码怎么办？这里我分享一种解决办法，说说原理：修改/etc/shadow文件，此文件也是linux系统上保存账户密码文......
  • ZLMediaKit报错，程序启动失败，请修改配置文件中端口号后重试!
    报错信息如下：2023-05-0910:33:22.819W[MediaServer][13423-MediaServer]sockutil.cpp:421bind_sock6|Bindsocketfailed:addressalreadyinuse2023-05-0910:33:22.819W[MediaServer][13423-MediaServer]main.cpp:366start_main|端口占用或无权限:Listenon:......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99