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

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