Duplicate requests in a session

[Peter Nixon]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20060831/06ccc619/attachment.pgp>