Обсуждение: duplicate key violates unique on a nextval() field
hi guys (and hopefully also ladies)
I use postgresql as a backend for freeradius with a coova-chilli hotspot
we have an installation with plenty of concurrent users with a lot of
traffic, however the database is not under that huge load.
Normally all is working fine, but from time to time i get this error
message:
-----------------------------------
Tue Aug 30 13:53:18 2011 : Error: rlm_sql (sql): failed after re-connect
Tue Aug 30 13:53:18 2011 : Error: rlm_sql (sql): Couldn't insert SQL
accounting STOP record - ERROR: duplicate key violates unique
constraint "radacct_unique"
-----------------------------------
which causes that at the end of the day i have inconsistencies and
missing data, which makes my customers sad (and me also).
Can you imagine how this error possibly can happen?
the table looks like this:
------------------------------------
CREATE TABLE radacct (
RadAcctId BIGSERIAL PRIMARY KEY,
AcctSessionId VARCHAR(32) NOT NULL,
AcctUniqueId VARCHAR(32) NOT NULL,
UserName VARCHAR(253),
...
);
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('radacct',
'radacctid'), 1, false);
CREATE UNIQUE INDEX radacct_unique ON radacct USING btree (acctuniqueid)
WHERE (radacctid > 0);
------------------------------------
After investigation in freeradius code i found that freeradius is trying
to insert the record, when that fails, the db link will be closed and
reconnected and the sql statement is retried again.
The error message posted above tells me that the insert statement does
even not succeed after the reconnect. (failed after reconnect)
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?
Is bigserial not using a transaction in order to calculate a unique id?
postgres version is 8.1.5. I know it is a bit old, but we are stuck with
it right now.
Any idea?
Would be nice,. Thank you in advance
Peter
--
:: e n d i a n
:: security with passion
:: peter warasin
:: http://www.endian.com :: peter@endian.com
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
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 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... 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?? peter -- :: e n d i a n :: security with passion :: peter warasin :: http://www.endian.com :: peter@endian.com
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
assigningids and could have bugs. Allowing pg to assign the value is safe, using nextval is safe--I'd look for client
codethat 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