Thread: 8.0.5 Bug in unique indexes?

8.0.5 Bug in unique indexes?

From
"Joshua D. Drake"
Date:
Hello,

Odd problem with unique indexes:

8.0.5 64 bit (Quad Opteron)

100 tables, each table has same layout, 1 million rows per table. The 
problem persists within multiple tables
but only within the set of 100 tables.

I have a composite unique key on each table:

"uniq1" UNIQUE, btree (unit_id, email)

Performing a query like the following:

app=# select unit_id, email, count(*) as cnt from leads10 group by 
unit_id, email having count(*) > 1;unit_id |         email          | cnt
---------+------------------------+-----  77212 | robob@foo.com |   2

app=# select unit_id,email from leads10 where unit_id = 77212 and email 
= 'robob@foo.com';unit_id |         email
---------+------------------------  77212 | robob@foo.com
(1 row)

app=# reindex index "uniq1";
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
app=#

I have verified that we have not overrun the fsm pages and that vacuums 
are running daily (actually twice a day).
I have also ran a vacuum full on the various tables to no avail, no 
error but the situation does not improve.

app=# set enable_indexscan = off;
SET
app=# select unit_id,email from leads10 where unit_id = 77212 and email 
= 'robob@foo.com';unit_id |         email
---------+------------------------  77212 | robob@foo.com  77212 | robob@foo.com
(2 rows)

app=# select lead_id,unit_id,email from leads10 where unit_id = 77212 
and email = 'robob@foo.com';lead_id  | unit_id |         email
----------+---------+------------------------35867251 |   77212 | robob@foo.com35864333 |   77212 | robob@foo.com
(2 rows)


Thoughts?

Joshua D. Drake

P.S. Should this go to -bugs?


Re: 8.0.5 Bug in unique indexes?

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Odd problem with unique indexes:

What's the database's locale?  This could be the same problem fixed in
8.0.6, if the locale has weird ideas about what string equality means.
        regards, tom lane


Re: 8.0.5 Bug in unique indexes?

From
"Joshua D. Drake"
Date:
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>   
>> Odd problem with unique indexes:
>>     
>
> What's the database's locale?  This could be the same problem fixed in
> 8.0.6, if the locale has weird ideas about what string equality means.
>   
lc_collate                     | Clc_ctype                       | Clc_messages                    |
en_US.UTF-8lc_monetary                   | en_US.UTF-8lc_numeric                     | en_US.UTF-8lc_time
        | en_US.UTF-8
 

Sincerely,

Joshua D. Drake
>             regards, tom lane
>   


-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/



Re: 8.0.5 Bug in unique indexes?

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> What's the database's locale?  This could be the same problem fixed in
>> 8.0.6, if the locale has weird ideas about what string equality means.

>  lc_collate                     | C
>  lc_ctype                       | C

OK, scratch that theory.  Don't suppose you can create a reproducible
test case ;-)
        regards, tom lane


Re: 8.0.5 Bug in unique indexes?

From
"Joshua D. Drake"
Date:
>>  lc_collate                     | C
>>  lc_ctype                       | C
>
> OK, scratch that theory.  Don't suppose you can create a reproducible
> test case ;-)
That may be a bit tough... What really struck me is that the
duplication only occurs in this set of 100 tables and the
duplication is always violating the same index. We currently
have 4-5 tables that are in violation.

Let me see what I can do to duplicate this.

Sincerely,

Joshua D. Drake

>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/



Re: 8.0.5 Bug in unique indexes?

From
"Michael Paesold"
Date:
Joshua D. Drake wrote:

> Tom Lane wrote:
>> What's the database's locale?  This could be the same problem fixed in
>> 8.0.6, if the locale has weird ideas about what string equality means.

> lc_collate                     | C
> lc_ctype                       | C

You don't user pl/perl, do you -- i.e. I guess you read the latest release 
notes and the thread here before that?

Best Regards,
Michael 




Re: 8.0.5 Bug in unique indexes?

From
"Joshua D. Drake"
Date:
Michael Paesold wrote:
> Joshua D. Drake wrote:
> 
>> Tom Lane wrote:
>>> What's the database's locale?  This could be the same problem fixed in
>>> 8.0.6, if the locale has weird ideas about what string equality means.
> 
>> lc_collate                     | C
>> lc_ctype                       | C
> 
> You don't user pl/perl, do you -- i.e. I guess you read the latest 
> release notes and the thread here before that?

Yes I did. I didn't know that the person was running plPerl. I have 
verified that they are. We are now going to check if upgrading to 8.0.6
with a deletion of the duplicates and a reindex resolves the issue.

Sincerely,

Joshua D. Drake


> 
> Best Regards,
> Michael
> 
> 
> ---------------------------(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


-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: 8.0.5 Bug in unique indexes?

From
"Michael Paesold"
Date:
Joshua D. Drake wrote:
> Michael Paesold wrote:
>> You don't user pl/perl, do you -- i.e. I guess you read the latest 
>> release notes and the thread here before that?
> 
> Yes I did. I didn't know that the person was running plPerl. I have 
> verified that they are. We are now going to check if upgrading to 8.0.6
> with a deletion of the duplicates and a reindex resolves the issue.

I thought I'd ask because this sound so familiar...

Best Regards,
Michael Paesold