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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.




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

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.


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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org