Thread: BUG #2143: Indexes incorrectly created from database dump
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 :)
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 =3D 'FOO'" unique_code_i index is used and que= ry > 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.