duplicate primary key

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.

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

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:
/messages/by-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