Re: BUG #6351: ERROR: btree index keys must be ordered by attribute

christian_oederan(at)gmx(dot)de writes:
> our OR-Mapper accidently created an index that contained the same column
> twice, with the result that some SELECTs could not be executed anymore and
> gave the error message "btree index keys must be ordered by attribute".

> The problem can be reproduced with a new table every time:

> CREATE TABLE tab (id SERIAL, a INTEGER, b INTEGER);
> CREATE INDEX tab123 ON tab (a, b, a);
> SELECT a, b FROM tab WHERE a = 0 AND b = 0;

> ERROR: XX000: btree index keys must be ordered by attribute

Hmm. This didn't use to be allowed at all before 9.0; previous releases
would reject the index definition:

regression=# CREATE INDEX tab123 ON tab (a, b, a);
ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnam_index"

But now that we allow it it'd be nice if it actually worked :-(.
Looking into the archives I see that we were mainly thinking about
the possibility of exclusion constraints naming the same column
more than once, which may explain why nobody tried using such an
index for queries. I'll see about fixing this in HEAD, although
my initial guess is that it will be too invasive to back-patch.

regards, tom lane