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 ids and could have bugs. Allowing pg to assign the value is safe, using nextval is safe--I'd look for client code that tries to 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 to assume 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 the creation 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(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice