BUG #2143: Indexes incorrectly created from database dump

On 1/4/06, Robert Osowiecki <robson@cavern.pl> wrote:

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 :)

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 ;)

"Robert Osowiecki" <robson@cavern.pl> writes:

BUT when on "where ar_code = 'FOO'" unique_code_i index is used and query
returns NO ROWS!

Could you be more specific? Which values of 'FOO' does this happen for?
What is the datatype of ar_code? If it's a string type, what locale and
encoding are you using? You have not given nearly enough information to
let anyone else reproduce the problem.

regards, tom lane

Tom Lane napisaďż˝(a):

"Robert Osowiecki" <robson@cavern.pl> writes:

BUT when on "where ar_code = 'FOO'" unique_code_i index is used and query
returns NO ROWS!

Could you be more specific? Which values of 'FOO' does this happen for?

I haven't checked for everyone. I'll be doing another dump:restore soon
so I'll be able to check that.

What is the datatype of ar_code? If it's a string type, what locale

ar_code is varchar(20)

and
encoding are you using?

locale is pl_PL: at least it sorts polish letters correctly. Database
encoding set to LATIN2

You have not given nearly enough information to
let anyone else reproduce the problem.

I'll be happy to answer any future questions, this is a critical issue
for me.

Robson.

Tom Lane napisal:

Robert Osowiecki <robson@cavern.pl> writes:

Hm, are you using any plperl functions? This could be the same problem
already identified with plperl messing up the locale settings.

Yes, I am. Where can I read about that other problem, especially: does
plperl spoil locale with each pgperl function call or only when creating
language?

Robson.

Robert Osowiecki <robson@cavern.pl> writes:

Yes, I am. Where can I read about that other problem, especially: does
plperl spoil locale with each pgperl function call or only when creating
language?

It was discussed a week or two ago. We're still testing a patch, but
in the meantime you can work around it by making sure that the
postmaster is started with environment variables LC_COLLATE and LC_CTYPE
matching the settings used in the database.

regards, tom lane

Tom Lane napisaďż˝(a):

It was discussed a week or two ago. We're still testing a patch, but
in the meantime you can work around it by making sure that the
postmaster is started with environment variables LC_COLLATE and LC_CTYPE
matching the settings used in the database.

It seems to work. Thanks a lot! :)

R.