Thread: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Ian Barwick
Date:
Hi

I've just compiled the 9.0 beta1 source tarball and am testing my
custom application against it (which has been running on PostgreSQL
since 7.3 or so).

The below statement results in the following error message:
 "ERROR:  btree index keys must be ordered by attribute"

evidently in relation to the subselect. The statement works fine on
previous versions up to 8.4.3. I can provide more details later if
required:
   SELECT o.object_id     FROM object o       INNER JOIN class c               ON (o.class_id = c.class_id)       INNER
JOINobject_version ov               ON (o.object_id = ov.object_id)       INNER JOIN site               ON
(o.site_id=site.site_id)           WHERE o.object_id = '3143'              AND ov.version = '0'              AND
o.site_id= '2'              AND ov.object_status_id = (SELECT MAX(ov1.object_status_id)                   FROM
object_versionov1                  WHERE o.object_id=ov1.object_id                    AND ov1.version = ov.version
             AND ov1.lang = ov.lang                 )            AND ov.lang = 'en'
 


SELECT version():
PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
4.3.2-1ubuntu12) 4.3.2, 32-bit
Ubuntu 8.10 running on a "VIA C7-M" Processor  (netbook).

Regards

Ian Barwick


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
David Fetter
Date:
On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote:
> Hi
> 
> I've just compiled the 9.0 beta1 source tarball and am testing my
> custom application against it (which has been running on PostgreSQL
> since 7.3 or so).
> 
> The below statement results in the following error message:
> 
>   "ERROR:  btree index keys must be ordered by attribute"
> 
> evidently in relation to the subselect. The statement works fine on
> previous versions up to 8.4.3. I can provide more details later if
> required:

A self-contained way to reproduce this, ideally small, would be
fantastic :)

Cheers,
David.
> 
>     SELECT o.object_id
>       FROM object o
>         INNER JOIN class c
>                 ON (o.class_id = c.class_id)
>         INNER JOIN object_version ov
>                 ON (o.object_id = ov.object_id)
>         INNER JOIN site
>                 ON (o.site_id=site.site_id)
>              WHERE o.object_id = '3143'
>                AND ov.version = '0'
>                AND o.site_id = '2'
>                AND ov.object_status_id = (SELECT MAX(ov1.object_status_id)
>                     FROM object_version ov1
>                    WHERE o.object_id=ov1.object_id
>                      AND ov1.version = ov.version
>                      AND ov1.lang = ov.lang
>                   )
>              AND ov.lang = 'en'
> 
> 
> SELECT version():
> PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
> 4.3.2-1ubuntu12) 4.3.2, 32-bit
> Ubuntu 8.10 running on a "VIA C7-M" Processor  (netbook).
> 
> Regards
> 
> Ian Barwick
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Ian Barwick
Date:
2010/5/9 David Fetter <david@fetter.org>:
> On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote:
>> Hi
>>
>> I've just compiled the 9.0 beta1 source tarball and am testing my
>> custom application against it (which has been running on PostgreSQL
>> since 7.3 or so).
>>
>> The below statement results in the following error message:
>>
>>   "ERROR:  btree index keys must be ordered by attribute"
>>
>> evidently in relation to the subselect. The statement works fine on
>> previous versions up to 8.4.3. I can provide more details later if
>> required:
>
> A self-contained way to reproduce this, ideally small, would be
> fantastic :)

Unfortunately I'm a bit pressed for time right now :(, however
in the meantime I have confirmed the same error crops up on OS X 10.5
and also with the query pruned to reference just one table:
  SELECT ov.object_id    FROM object_version ov   WHERE ov.object_id = '3143'     AND ov.version = '0'     AND
ov.object_status_id= (     SELECT MAX(ov1.object_status_id)       FROM object_version ov1      WHERE
ov1.object_id=ov.object_id       AND ov1.version = ov.version        AND ov1.lang = ov.lang       )     AND ov.lang =
'en';

PostgreSQL 9.0beta1 on i386-apple-darwin9.8.0, compiled by GCC
i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465),
32-bit

I will see if I can knock together a reproducible test case, might
take a day or so. At the moment all I can report is that creating a reduced
version of the "object_version" table in a fresh DB with no data does
not reproduce the error when running the above query.


Ian Barwick


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Tom Lane
Date:
Ian Barwick <barwick@gmail.com> writes:
> 2010/5/9 David Fetter <david@fetter.org>:
>> A self-contained way to reproduce this, ideally small, would be
>> fantastic :)

s/fantastic/absolutely required to do anything with this report/

> I will see if I can knock together a reproducible test case, might
> take a day or so. At the moment all I can report is that creating a reduced
> version of the "object_version" table in a fresh DB with no data does
> not reproduce the error when running the above query.

It probably depends on a specific plan being chosen for the query,
and with no data loaded you'd most likely not get the same plan.
        regards, tom lane


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Ian Barwick
Date:
Hi

2010/5/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Ian Barwick <barwick@gmail.com> writes:
>> 2010/5/9 David Fetter <david@fetter.org>:
>>> A self-contained way to reproduce this, ideally small, would be
>>> fantastic :)
>
> s/fantastic/absolutely required to do anything with this report/

Yes, I appreciate that :) I am a bit pressed for time and as googling
the error message didn't produce any kind of result I thought
it better to at least give a heads-up on the offchance someone
might be able to do something with it as is, and / or events
overtake me and I never end up doing anything about it at all.

"Luckily" this is easy to reproduce with a stripped-down version
of the original table and minimal data set:

CREATE TABLE object_version ( object_version_id   SERIAL, object_id           INT NOT NULL, version             INT NOT
NULLDEFAULT 0, object_status_id    INT NOT NULL, parent_id           INT DEFAULT NULL, owner_id            INT NOT
NULL,created             TIMESTAMP(0) NOT NULL DEFAULT NOW(), lang                CHAR(2) NOT NULL, PRIMARY KEY
(object_version_id),UNIQUE      (object_id, version, object_status_id, lang)
 
);

INSERT INTO object_version VALUES
(DEFAULT, 1, 0, 0, NULL, 1, DEFAULT,'en'),
(DEFAULT, 1, 0, -1, NULL, 1, DEFAULT,'en'),
(DEFAULT, 1, 1, -1, NULL, 1, DEFAULT,'en');

SELECT ov.object_id   FROM object_version ov  WHERE ov.object_id = 1    AND ov.version ='0    AND ov.object_status_id =
(   SELECT MAX(ov1.object_status_id)      FROM object_version ov1     WHERE ov1.object_id=ov.object_id       AND
ov1.version= ov.version       AND ov1.lang = ov.lang      )    AND ov.lang = 'en';
 


SELECT version();PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit


HTH

Ian Barwick


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Ian Barwick
Date:
2010/5/10 Ian Barwick <barwick@gmail.com>:
> SELECT ov.object_id
>    FROM object_version ov
>   WHERE ov.object_id = 1
>     AND ov.version ='0
>     AND ov.object_status_id = (
>     SELECT MAX(ov1.object_status_id)
>       FROM object_version ov1
>      WHERE ov1.object_id=ov.object_id
>        AND ov1.version = ov.version
>        AND ov1.lang = ov.lang
>       )
>     AND ov.lang = 'en';

Apologies, slight c&p error; correct version of query:

SELECT ov.object_id  FROM object_version ov WHERE ov.object_id = 1   AND ov.version =0   AND ov.object_status_id = (
SELECTMAX(ov1.object_status_id)     FROM object_version ov1    WHERE ov1.object_id=ov.object_id      AND ov1.version =
ov.version     AND ov1.lang = ov.lang     )   AND ov.lang = 'en'; 

FWIW the test case works fine in 8.4.3


Ian Barwick


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Tom Lane
Date:
Ian Barwick <barwick@gmail.com> writes:
> Apologies, slight c&p error; correct version of query:

> SELECT ov.object_id
>    FROM object_version ov
>   WHERE ov.object_id = 1
>     AND ov.version =0
>     AND ov.object_status_id = (
>     SELECT MAX(ov1.object_status_id)
>       FROM object_version ov1
>      WHERE ov1.object_id=ov.object_id
>        AND ov1.version = ov.version
>        AND ov1.lang = ov.lang
>       )
>     AND ov.lang = 'en';

Ah, I see it:
                  ->  Index Scan Backward using object_version_object_id_version_object_status_id_lang_key on
object_versionov1  (cost=0.00..8.27 rows=1 width=4)                        Index Cond: ((object_id = $0) AND (version =
$1)AND (lang = $2) AND (object_status_id IS NOT NULL))
 

where

regression=# \d object_version_object_id_version_object_status_id_lang_key
Index "public.object_version_object_id_version_object_status_id_lang_key"     Column      |     Type     |
Definition   
 
------------------+--------------+------------------object_id        | integer      | object_idversion          |
integer     | versionobject_status_id | integer      | object_status_idlang             | character(2) | lang
 
unique, btree, for table "public.object_version"

The index-based-max code is throwing in the IS NOT NULL condition
without thought for where it has to go in the index condition order.
Will look into fixing this tomorrow.
        regards, tom lane


Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Bruce Momjian
Date:
Tom Lane wrote:
> Ian Barwick <barwick@gmail.com> writes:
> > Apologies, slight c&p error; correct version of query:
> 
> > SELECT ov.object_id
> >    FROM object_version ov
> >   WHERE ov.object_id = 1
> >     AND ov.version =0
> >     AND ov.object_status_id = (
> >     SELECT MAX(ov1.object_status_id)
> >       FROM object_version ov1
> >      WHERE ov1.object_id=ov.object_id
> >        AND ov1.version = ov.version
> >        AND ov1.lang = ov.lang
> >       )
> >     AND ov.lang = 'en';
> 
> Ah, I see it:
> 
>                    ->  Index Scan Backward using object_version_object_id_version_object_status_id_lang_key on
object_versionov1  (cost=0.00..8.27 rows=1 width=4)
 
>                          Index Cond: ((object_id = $0) AND (version = $1) AND (lang = $2) AND (object_status_id IS
NOTNULL))
 
> 
> where
> 
> regression=# \d object_version_object_id_version_object_status_id_lang_key
> Index "public.object_version_object_id_version_object_status_id_lang_key"
>       Column      |     Type     |    Definition    
> ------------------+--------------+------------------
>  object_id        | integer      | object_id
>  version          | integer      | version
>  object_status_id | integer      | object_status_id
>  lang             | character(2) | lang
> unique, btree, for table "public.object_version"
> 
> The index-based-max code is throwing in the IS NOT NULL condition
> without thought for where it has to go in the index condition order.
> Will look into fixing this tomorrow.

FYI, this no longer throws an error in current CVS so was fixed by Tom.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + None of us is going to be here forever. +



Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"

From
Ian Barwick
Date:
2010/6/1 Bruce Momjian <bruce@momjian.us>:
> Tom Lane wrote:
(...)
>> The index-based-max code is throwing in the IS NOT NULL condition
>> without thought for where it has to go in the index condition order.
>> Will look into fixing this tomorrow.
>
> FYI, this no longer throws an error in current CVS so was fixed by Tom.

Thanks for the update, I can confirm the issue  no longer occurs in
beta2.

Regards

Ian Barwick