Postgres Pro
Downloads
Home   >   mailing lists

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

duplicate key violates unique on a nextval() field

From
Peter Warasin
Date:
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

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

From
"Daniel Verite"
Date:
    Peter Warasin wrote:

> The message tells me furthermore that freeradius tries to insert a
> record with a radacctid which already exists.

No, the message you quoted tells about the other unique constraint, the one
named radacct_unique. It's not related to the bigserial primary key.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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

From
Peter Warasin
Date:
hi

On 30/08/11 19:43, Daniel Verite wrote:
>> The message tells me furthermore that freeradius tries to insert a
>> record with a radacctid which already exists.
>
> No, the message you quoted tells about the other unique constraint, the one
> named radacct_unique. It's not related to the bigserial primary key.

aah, stupid me. thank you!
ok, then back to coova-chilli :)

peter

--
:: e n d i a n
:: security with passion

:: peter warasin
:: http://www.endian.com   :: peter@endian.com