Hi,
a table in our database with about 3 million rows ended up in a state
where its seems to have duplicated entries (duplicated primary key values):
# \d some_table;
Table "public.some_table"
Column | Type | Modifiers
--------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('some_table_id_seq'::regclass)
field1 | character varying(40) |
field2 | character varying(128) |
ts | timestamp without time zone |
Indexes:
"some_table_pkey" PRIMARY KEY, btree (id)
"ix_some_table_field1" btree (field1)
"ix_some_table_field2" btree (field2)
"ix_some_table_ts" btree (ts)
# select id, field1, field2 from some_table where field1 is null and field2 is not null;
id | field1 | field2
---------+--------+----------------------------------
2141750 | | some_value2
(1 row)
# select id, field1, field2 from some_table where id = 2141750;
id | field1 | field2
---------+-------------+----------------------------------
2141750 | some_value1 | some_value2
(1 row)
Another way this manifests itself it that running this:
# update some_table
set field2 = field1
where
id = 2141750;
works perfectly fine (but doesn't change the result of the first two queries above),
but this results in an error:
# update some_table
set field2 = field1
where
field1 is not null
and field2 is null
and ts between '2015-12-01' and '2015-12-02';
ERROR: duplicate key value violates unique constraint "some_table_pkey"
DETAIL: Key (id)=(2141750) already exists.
Do you have any idea what could be happening and what measures should be
undertaken to fix this issue? Thanks.