Skip site navigation (1) Skip section navigation (2)

unique index problems

From: Costin Grigoras <costing(at)cs(dot)pub(dot)ro>
To: pgsql-bugs(at)postgresql(dot)org
Subject: unique index problems
Date: 2004-01-12 06:45:53
Message-ID: 200401120845.53532.costing@cs.pub.ro (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Denis N. StepanovDate: 2004-01-12 08:27:22
Subject: Re: BUG #1044: snprintf() shipped with PostgreSQL is not
Previous:From: Tom LaneDate: 2004-01-11 20:09:20
Subject: Re: data loss after vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group