On 1/4/06, Robert Osowiecki <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2143 > Logged by: Robert Osowiecki > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.1 > Operating system: Linux 2.6.14-gentoo-r5 #2 SMP Thu Dec 22 11:58:01 CET > 2005 i686 Intel(R) Xeon(TM) CPU 3.20GHz GenuineIntel GNU/Linux > Description: Indexes incorrectly created from database dump > Details: > > I've got this indexes on my table: > primary key > "unique_code_i" UNIQUE, btree (ar_code, ... 6 int fields) > "pattern_i" btree (ar_code varchar_pattern_ops) > > Immediately after restoring from SQL dump with pg_sql, unique_code_i index > is buggy. When I read: > > select * from my_table where ar_code like 'FOO' > > postgres uses pattern_i and returns all requested rows. > > BUT when on "where ar_code = 'FOO'" unique_code_i index is used and query > returns NO ROWS! > > The bug dissapears after REINDEX and does not apper when doing data-only > restore on empty database structure. > > Please, help. I'll gladly provide any additional information as sonn as I > know where to look. > > Robert > > PS. Spotting that kind of bug on production database (as it was i my case) > can really spoil a day :) >
Last year come up an issue with similar behaviour (maybe the same problem)... http://archives.postgresql.org/pgsql-general/2005-12/msg00740.php IRC, there was a patch made for this... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match