Search Postgresql Archives

Re: duplicate primary key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux