index row size 2720 exceeds maximum 2712 for index "xxx" ,Values larger than 1/3 of a buffer page cannot be indexed.

记录一个bug情况：

我有个表NewTable,复合主键（slaveid，resid，owner）

CREATE TABLE "public"."NewTable" (

"slaveid" varchar() NOT NULL,

"resid" varchar() NOT NULL,

"owner" varchar(2) NOT NULL,

"a" bool,

"b" int4,

"c" text,

"ddd" varchar(),

"e" timestamp(),

"f" timestamp(),

PRIMARY KEY ("owner", "resid", "slaveid")

)

WITH (OIDS=FALSE)

;

存数据进去的时候，slaveid，resid，owner三个字段都特别长，达到几万字节，其他字段长度的只有几十

那么问题来了,当入参足够长的时候，会报错如下

[Err] ERROR:  index row size  exceeds maximum  for index "pk_t_portal_slave_extend"

HINT:  Values larger than / of a buffer page cannot be indexed.

Consider a function index of an MD5 hash of the value, or use full text indexing.

原因：报错说超过缓存1/3大小就不予检索，也就是说

length(slaveid + resid +owner) / length(所有入参字节总和) > 1/3

则认为主键长度太大，就数据库就放弃检索复合主键是否唯一，就报错不处理了。

解决办法，正在尝试，参看：

Consider a function index of an MD5 hash of the value, or use full text indexing.

巴特西

index row size 2720 exceeds maximum 2712 for index "xxx" ,Values larger than 1/3 of a buffer page cannot be indexed.

最新文章

热门文章