Thread: ERROR: index row size 2960 exceeds btree maximum

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  Hello,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  I’m developing an application that needs a different data type. So, I have implemented this new
datatype inside postgresql using C, as documentation shows to.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  Basically, the data type is a composition of two timestamps, like: (timestamp, timestamp) and it
iscalled ‘period’. So, almost everything is fine. Data type is ok, operators are ok, but the index doesn’t work
fine.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal" style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">CREATETABLE employee (</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">    emp_id
INT,</span></font><pclass="MsoNormal" style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">   emp_name VARCHAR(30),</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">   
contract_periodPERIOD,</span></font><p class="MsoNormal" style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">   CONSTRAINT pk_employee PRIMARY KEY (emp_id,
contract_period)</span></font><pclass="MsoNormal" style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">);</span></font><pclass="MsoNormal" style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">INSERT INTO
employeeVALUES (1, 'Roger', '(10-jan-2006 , 10-oct-2006)');</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">INSERT INTO
employeeVALUES (1, 'Roger', '(10-nov-2006 , 10-dec-2006)');</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">INSERT INTO
employeeVALUES (1, 'Roger', '(10-jan-2007 , 10-jul-2008)');</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">INSERT INTO
employeeVALUES (1, 'Roger', '(10-aug-2008 , 10-dec-2008)');</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"> </span></font><p
class="MsoNormal"style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">SELECT* FROM employee ORDER BY contract_period;</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  The table creation with contract_period as part of the PK (index) works ok! The first four insert
operationsworks ok! But, the last insert operation doesn’t work because the first timestamp is from year 2008. Very
strange,isn’t? </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  The select operation using the order by clause doesn’t work!</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Here is the error about SELECT:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">ERROR: cache lookup failed for operator 49158</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">SQL state: XX000</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Here is the error about last INSERT:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">ERROR: index row size 2960 exceeds btree maximum, 2713</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">SQL state: 54000</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Hint: Values larger than 1/3 of a buffer page cannot be indexed.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Consider a function index of an MD5 hash of the value, or use full text indexing.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  Any idea?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  Thanks!</span></font></div>

Ühel kenal päeval, K, 2007-05-30 kell 21:41, kirjutas Rodrigo Sakai:
>   Hello,
> 
>  
> 
>   I’m developing an application that needs a different data type. So,
> I have implemented this new data type inside postgresql using C, as
> documentation shows to.
> 
>  
> 
>   Basically, the data type is a composition of two timestamps, like:
> (timestamp, timestamp) and it is called ‘period’. So, almost
> everything is fine. Data type is ok, operators are ok, but the index
> doesn’t work fine.

...

> Here is the error about last INSERT:
> 
> ERROR: index row size 2960 exceeds btree maximum, 2713
> 
> SQL state: 54000
> 
> Hint: Values larger than 1/3 of a buffer page cannot be indexed.


It seems that your C datatype is buggy, generating binary representation
of Period that is bigger than 2713 bytes.

-------------
Hannu

Rodrigo Sakai wrote:

>   Basically, the data type is a composition of two timestamps, like:
> (timestamp, timestamp) and it is called 'period'. So, almost everything is
> fine. Data type is ok, operators are ok, but the index doesn't work fine.

Does type tinterval not suit you?  It is not very well documented but it
should work, or at least serve as a basis for developing your own types.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)