ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701

From: "Vitale, Anthony, Sony Music" <anthony(dot)vitale(at)sonymusic(dot)com>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701
Date: 2024-04-24 16:40:56
Message-ID: AS8PR02MB10078358D14082084A46AC94299102@AS8PR02MB10078.eurprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Folks

I am trying to figure out how to calculate a column size in my UTF8 encoded Postgresql V14 instance in order to avoid the error exceeds btree version 4 maximum 2704 and I am obviously not doing it correctly using pg_column_size as when I do I get 2701 which is lower than 2704.

What am I doing wrong in the select. I think I saw something in an internet page that a setting was added that reduced the 2704 down by 8 bytes to make it 2696 as a max but I am not sure.

Can you tell me if there is a configuration property that removes this 8 byte removal as I am taking dumps from another db and loading it here and failing.

See here

SELECT id,length(name) charlength_name,PG_COLUMN_SIZE(name) as column_size
FROM tony
order by 2 desc;

id charlength_name column_size
-------- --------------- -----------
37960058 3403 2701
37913542 2955 48
31834662 2481 1993
(3 rows)

create index tony1 on tony (name);

ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index "tony1"
DETAIL: Index row references tuple (0,3) in relation "tony".
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.

delete from tony where id = 37960058;
DELETE 1

create index tony1 on tony (name);
CREATE INDEX

SELECT id,length(name) charlength_name,PG_COLUMN_SIZE(name) as column_size
FROM tony
order by 2 desc;

id charlength_name column_size
-------- --------------- -----------
37913542 2955 48
31834662 2481 1993
(2 rows)

Thanks
Anthony Vitale

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2024-04-24 16:57:03 Re: ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701
Previous Message Serkan Akdemir 2024-04-23 07:56:12 Re: Values Logged in Logfile of postgres logs