BUG #11141: Duplicate primary key values corruption - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      11141
Logged by:          Gerd Behrmann
Email address:      behrmann@ndgf.org
PostgreSQL version: 9.3.4
Operating system:   Ubuntu 14.04
Description:

We have observed two rows with the same primary key in one of our production
databases. We don't know how to reproduce this problem, but given that this
looks like a corruption bug, I figure you will want to know about this.

The table description is:

spacemanager=# \d srmlinkgroup
                                         Table "public.srmlinkgroup"
        Column         |           Type           |
Modifiers
-----------------------+--------------------------+-----------------------------------------------------------
 id                    | bigint                   | not null default
nextval('srmlinkgroup_id_seq'::regclass)
 name                  | character varying(32672) | not null
 availablespaceinbytes | bigint                   | not null
 lastupdatetime        | bigint                   |
 onlineallowed         | integer                  |
 nearlineallowed       | integer                  |
 replicaallowed        | integer                  |
 outputallowed         | integer                  |
 custodialallowed      | integer                  |
 reservedspaceinbytes  | bigint                   | not null
Indexes:
    "srmlinkgroup_pkey" PRIMARY KEY, btree (id)
    "srmlinkgroup_name_unique" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "srmlinkgroupvos" CONSTRAINT "fk_srmlinkgroupvos_l" FOREIGN KEY
(linkgroupid) REFERENCES srmlinkgroup(id) ON DELETE RESTRICT
    TABLE "srmspace" CONSTRAINT "fk_srmspace_l" FOREIGN KEY (linkgroupid)
REFERENCES srmlinkgroup(id)

Note the primary key on the id field and the unique constraint on the name
field.

We now have this content in the table:

spacemanager=# select * from srmlinkgroup;
    id    |          name          | availablespaceinbytes | lastupdatetime
| onlineallowed | nearlineallowed | replicaallowed | outputallowed |
custodialallowed | reservedspaceinbytes

----------+------------------------+-----------------------+----------------+---------------+-----------------+----------------+---------------+------------------+----------------------
        4 | ops-group              |       193697853978554 |  1404265701756
|             1 |               1 |              1 |             1 |
       1 |           8609513666
        1 | atlas-disk-group       |       227082050145530 |  1407745149142
|             1 |               0 |              1 |             0 |
       0 |      516957429039712
        3 | alice-disk-group       |       489413225195319 |  1407745149142
|             1 |               0 |              1 |             0 |
       0 |                    0
        4 | ops-group              |       169808990342525 |  1407745149142
|             1 |               1 |              1 |             1 |
       1 |             -1395777
 30041585 | atlas-disk-dk-group    |         1018807348216 |  1407745149142
|             1 |               0 |              1 |             0 |
       0 |       20257443592787
        2 | alice-tape-group       |                     0 |  1407745149142
|             0 |               1 |              0 |             0 |
       1 |                    0
  6021392 | biogrid-disk-group     |                     0 |  1407745149142
|             1 |               0 |              1 |             0 |
       0 |                    0
        0 | atlas-tape-write-group |        -9137070489748 |  1407745149142
|             0 |               1 |              0 |             0 |
       1 |       21228972097684
(8 rows)

Note how we got two rows with the key value 4 and both have the same name.

Just to verify we didn't misunderstand some corner case semantics somewhere,
we tried to dump the database and restore it under a different name. That
resulted in

psql:/tmp/spacemanager.sql:31528493: ERROR:  there is no unique constraint
matching given keys for referenced table "srmlinkgroup"
ALTER TABLE
psql:/tmp/spacemanager.sql:31528509: ERROR:  there is no unique constraint
matching given keys for referenced table "srmlinkgroup"
ALTER TABLE

errors as expected.

How could we possibly have ended up with two rows with the same primary key?