Thread: unique index problems

hello,

i encountered the following problem :


mail=# \d user_preferences
Table "public.user_preferences"
  Column  |  Type   | Modifiers
----------+---------+-----------
 up_uid   | integer |
 up_id    | integer |
 up_value | text    |
Indexes:
    "user_preferences_id_uid_uidx" unique, btree (up_id, up_uid)
    "user_preferences_uid" btree (up_uid)
Triggers:
    new_preferences BEFORE INSERT ON user_preferences FOR EACH ROW EXECUTE
PROCEDURE new_preferences()

mail=# reindex index user_preferences_id_uid_uidx;
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.



i even dropped the unique index and tried to create it again and this failed
too.

how could duplicate records appear when the unique index existed ?

even more interesting :

mail=# select * from user_preferences u where (select count(*) from
user_preferences where up_uid=u.up_uid and up_id=u.up_id)>1;

when the unique index existed this query returned nothing. after i dropped the
unique index this query returned 3 pairs of duplicates. i deleted them and
recreated the unique index and it seems to work now.

and some more: the trigger executes the following function :
    BEGIN
        delete from user_preferences where up_uid=NEW.up_uid and
up_id=NEW.up_id;
        return NEW;
    END;

so i had two methods of making sure no duplicate values could exist in this
table and both have failed somehow.



mail=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2


thank you,

.costin

Costin Grigoras <costing@cs.pub.ro> writes:
> how could duplicate records appear when the unique index existed ?

Good question.  I have a sneaking suspicion that your trigger might be a
contributing factor, but I'm not sure how.  Can you provide a
self-contained test sequence to reproduce the problem?

            regards, tom lane

Some time back, Costin Grigoras <costing@cs.pub.ro> wrote:
> how could duplicate records appear when the unique index existed ?

After re-reading this old bug report, I realized that all of the methods
you were applying would make use of the index itself to search for
duplicates --- the count(*) subquery and the trigger's delete would most
likely use indexscan plans, unless you were to take steps to prevent it.

That means that we only have to make one assumption to explain all the
symptoms: the index got corrupted in such a way that it would not find
certain rows.  Then it would be possible to insert new rows with the
same keys without the duplication being detected, either by the index
itself or by the queries you were using.

When you tried to reindex, the uncorrupted new index of course detected
the duplicates.

Of course the evidence is long gone about *how* the index got
corrupted.  Had you had any recent system crashes or hardware problems?

            regards, tom lane