From: | Alexander Pyhalov <alp(at)rsu(dot)ru> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | duplicate primary key |
Date: | 2017-11-22 11:05:23 |
Message-ID: | 310d960a-2ba1-8754-0848-457a61dae154@rsu.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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....
--
Best regards,
Alexander Pyhalov, 3152328995
system administrator of Southern Federal University IT department
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2017-11-22 11:31:08 | Re: backends stuck in "startup" |
Previous Message | Thomas Kellerer | 2017-11-22 11:04:45 | Re: Reset Sequence number |