Re: duplicate primary key

On Wed, Nov 22, 2017 at 12:05 PM, Alexander Pyhalov <alp(at)rsu(dot)ru> wrote:

> 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....
>
>
What is your postgres version, and what's the "version history" of upgrades
from it (talking pg_upgrade upgrades, not dump/reload upgrades). This might
be fallout from old bugs thaat have been known to cause this type of
problem.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>