Re: Duplicate key

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Gaetano Mendola" <mendola(at)bigfoot(dot)com> writes:
> > I'm running Postgres 7.3.3 on a Linux Box
> > I know that seems impossible,
> > that I can not replicate the bug but
> > today without hardware failure, power down etc etc
> > I had a duplicate primary key + a duplicate unique index
> > on one table. I already had this "kind" of problem in another
> > table and I solved the problem not reindexing anymore that
> > table, now this table have 12 index and is eavely updated/inserted
> > so I must reindex this table once in a day.
>
> What command have you been issuing, exactly? Also, let's see psql's \d
> output for the table that's now got duplicate indexes, plus the pg_class
> rows for the duplicate indexes.

The table is used by hundred of clients so I don't know exactly the sequence
of
command that was causing the problem; the only think that I can say is that
I use
the table ua_user_data_exp like a "materialized view" so are only trigger on
other
tables that are modifing the table.
Here your request:

db=# \d ua_user_data_exp
Table "public.ua_user_data_exp"
Column | Type | Modifiers
------------------+--------------------------+-----------
id_user | integer |
id_provider | integer |
login | character varying |
password | character varying(20) |
lastname | character varying(64) |
firstname | character varying(64) |
email | character varying(64) |
phone | character varying(64) |
fax | character varying(64) |
street_address | character varying(64) |
zipcode | character varying(10) |
city | character varying(64) |
country | text |
country_descr | text |
occupation | text |
occupation_descr | text |
company | character varying(64) |
os_type | text |
os_type_descr | text |
orbital_ptns | character varying |
card | text |
card_descr | text |
class | character varying(20) |
class_descr | character varying(64) |
creation_date | timestamp with time zone |
mac_address | text |
pid | integer |
status | text |
status_descr | text |
bytes_traffic | integer |
ip_address | text |
provider | character varying(64) |
platform | character varying(20) |
transponder | character varying(50) |
active | text |
stickers | text |
contracts | text |
connected | text |
connections | integer |
login_time | text |
total_traffic | bigint |
Indexes: ua_user_data_exp_id_user_key unique btree (id_user),
ua_user_data_exp_login_key unique btree (login),
exp_card btree (card),
exp_ci_email btree (lower(email)),
exp_ci_lastname btree (lower(lastname)),
exp_ci_login btree (lower(login)),
exp_country btree (country),
exp_email btree (email),
exp_id_provider btree (id_provider),
exp_lastname btree (lastname),
exp_mac_address btree (lower(mac_address)),
exp_mac_address_normal btree (mac_address),
exp_orbital_ptns btree (orbital_ptns),
exp_os_type btree (os_type),
exp_pid btree (pid),
exp_provider btree (provider) WHERE ((status = 'Active'::text) OR
(status = 'Suspended'::text)),
exp_status btree (status),
exp_stickers btree (stickers)

db=# select * from pg_class where relname = 'ua_user_data_exp_id_user_key';
-[ RECORD 1 ]--+-----------------------------
relname | ua_user_data_exp_id_user_key
relnamespace | 2200
reltype | 0
relowner | 100
relam | 403
relfilenode | 3005981
relpages | 52
reltuples | 11566
reltoastrelid | 0
reltoastidxid | 0
relhasindex | f
relisshared | f
relkind | i
relnatts | 1
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |

db=# select * from pg_class where relname = 'ua_user_data_exp_login_key';
-[ RECORD 1 ]--+---------------------------
relname | ua_user_data_exp_login_key
relnamespace | 2200
reltype | 0
relowner | 100
relam | 403
relfilenode | 3005982
relpages | 81
reltuples | 11566
reltoastrelid | 0
reltoastidxid | 0
relhasindex | f
relisshared | f
relkind | i
relnatts | 1
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |

I had one row duplicated with the same login and the same id_user,
was failing was the update of that row complaining about the duplicated
key.

Regards
Gaetano Mendola