Table with seemingly duplicated primary key values

From: Aleksander Łukasz <allllllx(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table with seemingly duplicated primary key values
Date: 2015-12-22 09:03:21
Message-ID: CAGTk8=MCu9FDSrdnvXGdWZ5dSnnpNCB9_XOwkeRcZFuEZjD=xQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2015-12-22 12:27:00 Re: Session Identifiers
Previous Message Amit Bondwal 2015-12-22 09:00:31 Re: BDR error while adding 3rd node to cluster