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 | [EMAIL PROTECTED] | 2
app=# select unit_id,email from leads10 where unit_id = 77212 and email
= '[EMAIL PROTECTED]';
unit_id | email
---------+------------------------
77212 | [EMAIL PROTECTED]
(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
= '[EMAIL PROTECTED]';
unit_id | email
---------+------------------------
77212 | [EMAIL PROTECTED]
77212 | [EMAIL PROTECTED]
(2 rows)
app=# select lead_id,unit_id,email from leads10 where unit_id = 77212
and email = '[EMAIL PROTECTED]';
lead_id | unit_id | email
----------+---------+------------------------
35867251 | 77212 | [EMAIL PROTECTED]
35864333 | 77212 | [EMAIL PROTECTED]
(2 rows)
Thoughts?
Joshua D. Drake
P.S. Should this go to -bugs?
---------------------------(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
