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