Re: duplicate primary key

Am 22.11.17 um 12:05 schrieb Alexander Pyhalov:
> Hello.
>
> I'm a bit shocked. During import/export of our database we've found a
> duplicate primary key.
>
> # \d player
>
> Table "public.player"
>        Column       |            Type             |
>                             Modifiers
> --------------------+-----------------------------+------------------------------------------------------------------------------------------------------------
>
>  id                 | integer                     | not null default
> nextval('player_id_seq'::regclass)
> ...
> Indexes:
>     "pk_id" PRIMARY KEY, btree (id)
> ...
>
> # select * from pg_indexes where indexname='pk_id';
>  schemaname | tablename | indexname | tablespace |  indexdef
> ------------+-----------+-----------+------------+------------------------------------------------------
>
>  public     | player    | pk_id     |            | CREATE UNIQUE INDEX
> pk_id ON player USING btree (id)
>
> # select * from pg_constraint where conname='pk_id';
> -[ RECORD 1 ]-+------
> conname       | pk_id
> connamespace  | 2200
> contype       | p
> condeferrable | f
> condeferred   | f
> convalidated  | t
> conrelid      | 18319
> contypid      | 0
> conindid      | 18640
> confrelid     | 0
> confupdtype   |
> confdeltype   |
> confmatchtype |
> conislocal    | t
> coninhcount   | 0
> connoinherit  | t
> conkey        | {1}
> confkey       |
> conpfeqop     |
> conppeqop     |
> conffeqop     |
> conexclop     |
> conbin        |
> consrc        |
>
> # select count(*) from player where id=122224875;
> -[ RECORD 1 ]
> count | 2
>
> The records are identical, besides ctid,xmin,xmax
>
> # select tableoid,ctid,id,xmin,xmax from player where id=122224875;
>  tableoid |     ctid     |    id     |    xmin    |    xmax
> ----------+--------------+-----------+------------+------------
>     18319 | (9982129,2)  | 122224875 | 3149449600 | 3152681810
>     18319 | (9976870,49) | 122224875 | 3149448769 | 3152328995
>
>
>
> I don't understand how this could have happened....
>
Hi Alex,

we got this with a broken index. Fix data and rebuild them. And check
your system/storage

Thomas