Re: Duplicate key - Mailing list pgsql-admin

"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Gaetano Mendola" <mendola@bigfoot.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