Thread: 8.0.5 Bug in unique indexes?
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?
"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
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/
"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
>> 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/
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
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/
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