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

On 31/08/2011 1:28 AM, Peter Warasin wrote:
> Hi
>
> thank you for answering!
>
> On 30/08/11 18:56, Scott Ribe wrote:
>>> 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...
> freeradius is not supplying an id.
> that field is left away as it should be in order to use nextval
>
> that renders the whole thing strange.. also because it happens only from
> time to time.
> it must be related to load. when the system is under heavy load
> something goes wrong and 2 concurrent transactions get the same unique
> id. but how??
>
That *really* should not be possible. Sequences are tested extremely
heavily on a daily basis by almost every Pg user. It's not impossible
there's a bug there, but it's exceedingly unlikely.

Even if the queries that are failing use nextval(), DEFAULT, or omit the
RadAcctId field entirely, that doesn't prevent some other prior query
from inserting a record with a RadAcctId that is above the current
sequence value. If that happens, then everything will seem fine until
the sequence counts up to the inserted value, at which point the
statement that uses the sequence will fail to insert. For example:

regress=> CREATE TABLE x ( id bigserial primary key, y integer );
NOTICE: CREATE TABLE will create implicit sequence "x_id_seq" for
serial column "x.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
for table "x"
CREATE TABLE
regress=> insert into x (y) values (1),(2),(3);
INSERT 0 3
regress=> insert into x (id, y) values (nextval('x_id_seq'), 4);
INSERT 0 1
regress=> select max(id) from x;
max
-----
4
(1 row)

regress=> insert into x (id, y) values ( (select max(id) from x)+1, 5);
-- Wrong!!!!
INSERT 0 1
regress=> insert into x (y) values (6);
ERROR: duplicate key value violates unique constraint "x_pkey"

Before doing much else, I suggest you temporarily enable logging of DML
so you can capture some of the queries freeradius is sending to the
server. Find the ones that touch the `radacct' table and post them after
Xing out any important usernames/passwords etc. Search the logs for any
queries that mention the RadAcctId column (use "grep -i" or some other
case-insensitive search) and see if any other queries might be modifying it.

Also, you mentioned the use of dblink. Where does it come into play?

--
Craig Ringer