Re: duplicate key violates unique on a nextval() field

On Aug 30, 2011, at 10:19 AM, Peter Warasin wrote:

> The message tells me furthermore that freeradius tries to insert a
> record with a radacctid which already exists.
>
> But how can that happen when it is bigserial?

Postgres only assigns the value if it is not explicitly provided. Any client, freeradius included, could be assigning
idsand could have bugs. Allowing pg to assign the value is safe, using nextval is safe--I'd look for client code that
triesto get ranges ahead of time & cache... 

> The error message posted above tells me that the insert statement does
> even not succeed after the reconnect. (failed after reconnect)

If freeradius is trying to insert a record with an id that already exists, after a re-connect there's no reason at all
toassume that the previously existing record is gone and that the insert with the same id will now succeed. 

That DDL is also kind of nasty... Why the big effort to set the sequence to 1 immediately after creating the table? Why
thecreation of a unique index when the "primary key" attribute already causes a unique index to be created on the id?
Ugh.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice