BUG #2143: Indexes incorrectly created from database dump - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2143
Logged by:          Robert Osowiecki
Email address:      robson@cavern.pl
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 :)