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

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

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

2010/5/9 David Fetter <david(at)fetter(dot)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

Ian Barwick <barwick(at)gmail(dot)com> writes:
> 2010/5/9 David Fetter <david(at)fetter(dot)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

Hi

2010/5/10 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Ian Barwick <barwick(at)gmail(dot)com> writes:
>> 2010/5/9 David Fetter <david(at)fetter(dot)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 NULL DEFAULT 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

2010/5/10 Ian Barwick <barwick(at)gmail(dot)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 = (
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';

FWIW the test case works fine in 8.4.3

Ian Barwick

Ian Barwick <barwick(at)gmail(dot)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_version ov1 (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_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.

regards, tom lane

Tom Lane wrote:
> Ian Barwick <barwick(at)gmail(dot)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_version ov1 (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_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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

2010/6/1 Bruce Momjian <bruce(at)momjian(dot)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