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