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

记录一个bug情况：

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

CREATE TABLE "public"."NewTable" (
"slaveid" varchar(150000) NOT NULL,
"resid" varchar(150000) NOT NULL,
"owner" varchar(20) NOT NULL,
"a" bool,
"b" int4,
"c" text,
"ddd" varchar(150000),
"e" timestamp(6),
"f" timestamp(6),
PRIMARY KEY ("owner", "resid", "slaveid")
)
WITH (OIDS=FALSE)
;

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

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

[Err] ERROR:  index row size 2720 exceeds maximum 2712 for index "pk_t_portal_slave_extend"
HINT:  Values larger than 1/3 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.

 

转载于:https://www.cnblogs.com/xiaoliu66007/p/11170446.html