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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Ian Barwick <barwick(at)gmail(dot)com>, David Fetter <david(at)fetter(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 9.0b1: "ERROR: btree index keys must be ordered by attribute"
Date: 2010-05-31 17:05:52
Message-ID: 201005311705.o4VH5qh15720@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jesper Krogh 2010-05-31 18:12:51 Re: tsvector pg_stats seems quite a bit off.
Previous Message Bruce Momjian 2010-05-31 16:58:12 Re: Adding xpath_exists function