On Thu 31 Aug 2006 18:19, Guy Fraser wrote:
On Thu, 2006-08-31 at 12:31 +0300, Peter Nixon wrote:
Good question. Does anyone have anything against changing this?
-Peter
On Thu 31 Aug 2006 10:11, Santiago Balaguer García wrote:
Thanks James, I don't figure out to use primary key solves the problem of duplicate keys. I had in radacct as primary key <<radacctid>> but now I am going to have <<acctuniqueid>>.
This proble cause a new thread: why radacctid is the primary key of radacct table instead od acctuniqueid?
I used a slightly different solution in my PostgreSQL implementation :
ALTER TABLE ONLY radacct ADD CONSTRAINT radacct_unique_session UNIQUE ( username, nasipaddress, nasportid, acctsessionid );
NOTE: When duplicate records come in you will see errors in the log file like these :
Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): failed after re-connect Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): Couldn't insert SQL accounting START record - ERROR: duplicate key violates unique constraint "radacct_unique_session"
These errors are mostly informational, because when the insert fails, rlm_sql will use the alternate "update" method and will succeed.
This is the same method I used on a customized Cistron server I used for over 5 years and had no problems.
For some reason acctuniqueid was not unique in the duplicate packets, so my initial attempts at using it were unsuccessful.
PostgreSQL can have a primary key that spans multiple columns, and would look like this {IIRC} :
ALTER TABLE ONLY radacct ADD CONSTRAINT radacct_pkey_session PRIMARY KEY ( username, nasipaddress, nasportid, acctsessionid );
Yes. Infact the pgsql-voip.conf setup does almost exactly that. The problem with this is that because the sql module detects it as a sql failure it disconnects and reconnects the socket which is very wastefull on resources. This should really be solved in the postgres driver (When I wrote the reconnect code for rlm_sql_postgresql there were not actually sufficient return codes from the progresql library so it was either that, on not reconnect on some other (bad) error conditions). In the end I moved the whole query inside a stored procedure and hid the " ERROR: duplicate key violates unique constraint" errors from radiusd however I didnt commit this to cvs as stored procedures are a bit complex for the default FR config. (Just understanding how sql works seems to stump a significant number of our users.) -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc