Обсуждение: 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 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