could not find pathkey item to sort - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi All,</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">Since upgrading to PG 8.2, I’m getting this error on a nightly (bash) script that calls psql (it’s
purposeis to find stock codes that share an EAN Barcode with another code – it used to work, now
doesn’t):</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">ERROR:could not find pathkey item to sort</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">SQL state:
XX000</span></font><pclass="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 error is because of the ORDER BY clause of this query:</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">SELECT  code,
description,ean</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">FROM   $TMPTABLE</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">WHERE   ean
<>''</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> AND   ean IN (SELECT ean FROM $TMPTABLE GROUP BY ean HAVING count(ean) >
1)</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">ORDERBY ean</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">The $TMPTABLE is created as per:</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">CREATE TEMP TABLE $TMPTABLE</span></font><p
class="MsoNormal"style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">(</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">  code varchar(6),</span></font><p
class="MsoNormal"style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> description varchar(38),</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  grp
varchar(4),</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> brand text,</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  style
text,</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> supplier varchar(6),</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  supplier_code
text,</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> wholesale numeric(10,2),</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">  retail
numeric(10,2),</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"> ean varchar(13)</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">)</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">WITHOUT OIDS;</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">-- Import the
filefrom WB Database</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">COPY$TMPTABLE FROM '$TMPFILE';</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’ve tried creating an index and doing a reindex on the temp table because the select to no
avail.</span></font><pclass="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 help would be great – I’ve “fixed” it at the moment by commenting the ORDER BY clause, but it
wouldbe nice to have it sorted properly, and also in case it happens in any other tables.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-p</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">Phillip Smith<br /> IT Coordinator<br /> Weatherbeeta P/L<br /></span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family: Arial">8 Moncrief Rd</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"><br/> Nunawading, VIC, 3131<br /></span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family: Arial">AUSTRALIA</span></font><font face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial"><br />  <br /> E. <a
href="mailto:phillips@weatherbeeta.com.au">phillips@weatherbeeta.com.au</a></span></font><pclass="MsoNormal"><font
face="TimesNew Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for
direct,indirect or consequential loss arising from transmission of this message or any attachments <br />