Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute" - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"
Date
Msg-id 201005311705.o4VH5qh15720@momjian.us
Whole thread Raw
In response to Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"
List pgsql-hackers
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. +



pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Adding xpath_exists function
Next
From: Jesper Krogh
Date:
Subject: Re: tsvector pg_stats seems quite a bit off.