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

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