ERROR: index row size 2960 exceeds btree maximum - Mailing list pgsql-hackers

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