pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701
2+ messages / 2 participants
[nested] [flat]
* ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701
@ 2024-04-24 16:40 Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
0 siblings, 1 reply; 2+ messages in thread
From: Vitale, Anthony, Sony Music @ 2024-04-24 16:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701
@ 2024-04-24 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-04-24 16:57 UTC (permalink / raw)
To: Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Vitale, Anthony, Sony Music" <anthony.vitale@sonymusic.com> writes:
> 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.
I think you're not accounting for overhead. The reported index row
size would include the index tuple header (8 bytes) as well as
alignment padding.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-24 16:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-24 16:40 ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701 Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
2024-04-24 16:57 ` Re: ERROR: index row size 2720 exceeds btree version 4 maximum 2704 for index in V14 but pg_column_size calculates 2701 Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox