On Wed, Nov 22, 2017 at 12:05 PM, Alexander Pyhalov <alp@rsu.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.
