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?