Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?
14
votes
1
answer
14537
views
We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it:
> ERROR: index row size xxxx exceeds maximum 2712 for index "foo_idx"
> 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.
Examples:
- https://dba.stackexchange.com/questions/69161/character-varying-index-overhead-length-limit/69164
- https://dba.stackexchange.com/questions/25138/index-max-row-size-error
Etc.
Now, a_horse_with_no_name demonstrated a case with much larger
text
values (10000 characters) that still seems to work with a UNIQUE
index in Postgres 9.6. Quoting his test case:
create table tbl (col text);
create unique index on tbl (col);
insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));
select length(val) from x; -- 10000
No error, and the column value indeed tested with a length of 10000 characters.
Have there been recent changes or how is this possible?
Asked by Erwin Brandstetter
(185747 rep)
Sep 8, 2018, 03:56 PM
Last activity: Jul 21, 2024, 02:05 AM
Last activity: Jul 21, 2024, 02:05 AM