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.com
35864333 | 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 | C
lc_ctype | C
lc_messages | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_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 | COK, 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 | CYou 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