Обсуждение: duplicate primary key
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
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
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.
On 11/22/17 07:24 PM, Magnus Hagander wrote: > 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. > It's PostgreSQL 9.5.10 (64bit), running on Ubuntu 16.04. Last major update was done as full dump/restore. -- Best regards, Alexander Pyhalov, system administrator of Southern Federal University IT department
On Thu, Nov 23, 2017 at 1:28 AM, Alexander Pyhalov <alp@rsu.ru> wrote: > On 11/22/17 07:24 PM, Magnus Hagander wrote: >> 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. >> > > It's PostgreSQL 9.5.10 (64bit), running on Ubuntu 16.04. > Last major update was done as full dump/restore. You may as well be facing what is called the freeze-the-dead bug, where a VACUUM FREEZE brings back dead tuples: https://www.postgresql.org/message-id/E5711E62-8FDF-4DCA-A888-C200BF6B5742@amazon.com There is a patch in the works for it that should land in the next round of minor releases. -- Michael