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

The following bug has been logged on the website:

Bug reference:      6351
Logged by:          Christian Rudolph
Email address:      christian_oederan@gmx.de
PostgreSQL version: 9.0.6
Operating system:   openSUSE
Description:=20=20=20=20=20=20=20=20

Hi,

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 =3D 0 AND b =3D 0;

ERROR:  XX000: btree index keys must be ordered by attribute
LOCATION:  _bt_preprocess_keys, nbtutils.c:322

For me the error occured without inserting any rows.

The database was compiled from source using the switches --with-tcl
--with-perl and --with-python.

SELECT version();
PostgreSQL 9.0.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE
Linux), 32-bit

Thanks,
Christian

christian_oederan@gmx.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

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

I didn't recognized the exclusion constraint feature before. But it looks interesting.

Thank you for your effort.
Christian
--
NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
Jetzt informieren: http://www.gmx.net/de/go/freephone