|
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 |
