Cannot create index whose evaluation cannot be enforced to remote nodes
原创Cannot create index whose evaluation cannot be enforced to remote nodes
原创
dumpcat
修改于 2021-09-02 14:18:08
修改于 2021-09-02 14:18:08
华为GaussDB 200创建索引出现ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes错误，跟华为售后沟通后，确认是没有加分布列造成的报错，具体过程如下：
1. 在test_1表的xh字段上创建唯一索引
testdb=> create unique index "rule_test_1" on "public"."test_1" using btree("xh");
ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes
testdb=>2. 查看test_1的分布列，test_1的分布列为id
testdb=> \d+ public.test_1
Table "public.test_1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
xh | character varying(128) | | extended | |
Indexes:
"test_1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no3. 创建增加分布列的唯一索引
testdb=> create unique index "rule_test_1" on "public"."test_1" using btree("xh","id");
CREATE INDEX4. 查看创建的索引，xh的唯一索引创建成功
testdb=> \d+ public.test_1
Table "public.test_1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
xh | character varying(128) | | extended | |
Indexes:
"test_1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
"rule_test_1" UNIQUE, btree (xh, id) TABLESPACE pg_default
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no5. 唯一索引创建成功后对唯一索引进行测试，发现唯一索引所在的列并没有生效，是因为使用的分布列（id）所创建的唯一索引是组合索引，组合索引生效的条件是组合值唯一，所以无法在单列上生效
testdb=> select * from test_1
testdb-> ;
id | xh
----+-----
2 | 1s
1 | 2s
(2 rows)
testdb=> insert into test_1 values(3,'dd');
INSERT 0 1
testdb=> insert into test_1 values(4,'dd');
INSERT 0 1
testdb=> insert into test_1 values(5,'dd');
INSERT 0 1
testdb=>
testdb=> select * from test_1;
id | xh
----+-----
1 | 2s
5 | dd
3 | dd
2 | 1s
4 | dd
5 | dd
(6 rows)6. 单列唯一索引不生效的处理方法，创建表时必须指定分布列，将需要建立唯一索引的列指定为分布列
testdb=> create table tablename(id int,xh character(50)) DISTRIBUTE BY HASH(xh);原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录