BUG #2681: duplicate key violates unique constraint - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2681
Logged by:          Jean Tourrilhes
Email address:      jt@hpl.hp.com
PostgreSQL version: 7.4.7
Operating system:   Linux - Debian 3.1 (stable)
Description:        duplicate key violates unique constraint
Details:

Hi,

       Debian 3.1 default Postgresql 7.4.7 install, standard configuration.
       Databases have been running for a few months. 7 of them, same
structure, different data. It is hit pretty much constantly with new data,
machine load ~0.50.
       After a few months, I tend to have internal database corruptions that
autovacuum can't fix (/var/lib/postgres inflates to huge size). In those
cases I just dump the content of the tables, drop the database, restart
Postgresql, recreate the database, and put back the content in it.
       Now, all the sudden, I get the errors in the 3 largest of my
databases. So, I do the backup/restore procedure. But, I immediately get
back the errors.
       Note that the table where I get the error is the biggest, with in one
instance 76911 records (backup file is 20MB). Casual inspection of the
backup file looks good, but you can bet I did not check each record
individually...

    So, this is the complete error :
------------------------------------------------
# psql -U sdc_sophia sdc_sophia
sdc_sophia=> INSERT INTO server_history (rack_id, server_id, u, name,
time_added, time_lastseen, mac_address) VALUES
('7af135b7-faca-4e14-9b64-cbdfd848fea6', 'E01690010800E847', 37, NULL,
'2006-10-07T00:13:22Z ', '2006-10-07T00:13:22Z ', '');
ERROR:  duplicate key violates unique constraint "server_history_pkey"
------------------------------------------------

     The offending table was created with :
---------------------------------
CREATE TABLE server_history (
    record_n        serial                        NOT NULL    PRIMARY KEY,
    rack_id            character varying(36)        NOT NULL    REFERENCES rack ON DELETE
CASCADE,
    server_id        character varying(36)        NOT NULL,
    name            character varying(15)        DEFAULT NULL,
    u                integer                        NOT NULL,
    time_added        timestamp with time zone    NOT NULL,
    time_lastseen    timestamp with time zone    NOT NULL,
    time_removed    timestamp with time zone    DEFAULT NULL,
    mac_address        character varying(12)        DEFAULT NULL
);
---------------------------------

      The only other thing about that table is that there are a bunch of
indexes attached to it.
-------------------------------------
CREATE INDEX index_server_history_time_added ON server_history USING btree
(time_added);
CREATE INDEX index_server_history_time_removed ON server_history USING btree
(time_removed);
CREATE INDEX index_server_history_server_id ON server_history USING btree
(server_id);
CREATE INDEX index_server_history_rack_id ON server_history USING btree
(rack_id);
CREATE INDEX index_server_history_rack_id_u ON server_history (rack_id, u);
-------------------------------------

      From the look at it, I don't think the issue is in my code. Which is
why I defer to you guys...
      Note that I don't have too much time to spend on this issue, and my
plan is just to delete the history...

       Regards,

      Jean