Re: Table with seemingly duplicated primary key values

[Melvin Davidson <melvin6925@xxxxxxxxx>]

Please. Always, ALWAYS, give the PostgreSQL version and O/S when reporting a problem.

First, WHAT IS THE POSTGRESQL VERSION?????
       WHAT IS THE O/S?????

       Then try this:
select a.ctid, a.id, a.field1,
          b.ctid, b.id, b.field1
  from some_table a,
         some_table b
 WHERE a.ctid <> b.ctid
   AND a.id = b.id;

On Tue, Dec 22, 2015 at 4:03 AM, Aleksander Łukasz <allllllx@xxxxxxxxx> wrote:

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.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.