Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ian Barwick <barwick(at)gmail(dot)com>
Cc: 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-10 03:36:55
Message-ID: 16704.1273462615@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2010-05-10 06:27:44
Subject: Re: max_standby_delay considered harmful
Previous:From: Robert HaasDate: 2010-05-10 00:56:09
Subject: Re: max_standby_delay considered harmful

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group