The following bug has been logged online: Bug reference: 4307 Logged by: Oskars Ozols Email address: [EMAIL PROTECTED] 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs