Re: BUG #4307: INSERT fails with primary key contraint

"Oskars Ozols" <oskars(dot)ozols(at)gmail(dot)com> writes:

> id bigint NOT NULL DEFAULT nextval(('public.event_log_id_seq'::text)::regclass),

> 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)

There's something strange here. Your SQL statement includes the id as a
literal constant 156112. This isn't the normal way to write this query. This
is defeating the point of the DEFAULT you see in the table definition.

Postgres guarantees that the nextval() function will only return each value
once. But it's not clear from this log how your application is generating the
156112 value which it is explicitly putting in the query. If it's getting it
by calling nextval() then it's somehow using it twice.

It's also possible someone has written code to pick primary key values by
calling "select max(id)+1". That is guaranteed to have race conditions like
this.

The safest thing to do is to just leave out the id column from your INSERT
statement. Just let the DEFAULT expression generate a value for you. Then you
can use curval('event_log_id_seq') to find out what value it generated.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!