The following bug has been logged online:
Bug reference: 4307
Logged by: Oskars Ozols
Email address: oskars.ozols@gmail.com
PostgreSQL version: 8.3
Operating system: SuSE Linux Enterprise Server
Description: INSERT fails with primary key contraint
Details:
I have following table for event log:
CREATE TABLE event_log
(
id bigint NOT NULL DEFAULT
nextval(('public.event_log_id_seq'::text)::regclass),
user_id integer,
date_time timestamp(0) without time zone,
ip_address character varying(15) NOT NULL,
action_type character varying(500) NOT NULL,
severity integer NOT NULL,
parameters text,
web_address character varying(160),
server character(1),
CONSTRAINT event_log_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
During high load (processor load ~95%) of different SELECT/INSERT requests
this table starts to give following errors in db log:
2008-07-15 12:32:03 EEST ERROR: duplicate key value violates unique
constraint "event_log_pkey"
2008-07-15 12:32:03 EEST STATEMENT: INSERT INTO public.event_log
(date_time, ip_address, action_type, severity, parameters, web_address,
server, user_id, id) VALUES ('2008-07-15 12:28:50.000000',
'123.123.123.123', 'WebServices.SomeService:LogError', 70000, 'error text',
'http://123.123.123.123/WebServices/SomeService.asmx', '4', 75, 156112)
You may notice the difference in time when message was written to db log
(12:32:04) and actual time of event (12:28:50).
Currently there are ~3 million rows in event_log. Old records are regulary
deleted (autovacuum is on, too). During high peak it's possible that 20
events are finished to be written to event_log in 1 sec.
Current Start value for sequence event_log_id_seq is 8536444.
I have noticed that during high load Postgre starts to use old free sequence
values (like 156112 in my sample). Sometimes it's ok, but sometimes it fails
with error above.
