Cannot create index whose evaluation cannot be enforced to remote nodes

华为GaussDB 200创建索引出现ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes错误，跟华为售后沟通后，确认是没有加分布列造成的报错，具体过程如下：

1. 在test_1表的xh字段上创建唯一索引

代码语言：javascript复制

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

代码语言：javascript复制

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=no

3. 创建增加分布列的唯一索引

代码语言：javascript复制

testdb=>  create unique index "rule_test_1" on "public"."test_1" using btree("xh","id");
CREATE INDEX

4. 查看创建的索引，xh的唯一索引创建成功

代码语言：javascript复制

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=no

5. 唯一索引创建成功后对唯一索引进行测试，发现唯一索引所在的列并没有生效，是因为使用的分布列（id）所创建的唯一索引是组合索引，组合索引生效的条件是组合值唯一，所以无法在单列上生效

代码语言：javascript复制

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. 单列唯一索引不生效的处理方法，创建表时必须指定分布列，将需要建立唯一索引的列指定为分布列

代码语言：javascript复制

testdb=> create table tablename(id int,xh character(50)) DISTRIBUTE BY HASH(xh);

evaluation gaussdb nodes 华为 索引

0 人点赞