BUG #2681: duplicate key violates unique constraint

The following bug has been logged online:

Bug reference: 2681
Logged by: Jean Tourrilhes
Email address: jt(at)hpl(dot)hp(dot)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

On 10/6/06, Jean Tourrilhes <jt(at)hpl(dot)hp(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2681
> Logged by: Jean Tourrilhes
> Email address: jt(at)hpl(dot)hp(dot)com
> PostgreSQL version: 7.4.7
> Operating system: Linux - Debian 3.1 (stable)
> Description: duplicate key violates unique constraint
> Details:
>

the first thing you have to do is to upgrade at least to 7.4.13 (the
last release in that branch) and try again. Maybe the bug was already
fixed.

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook