I'm having some problems with Alive messages that follow immediately
after Start messages. Sometimes this causes two entries with the same
acctuniqeid to be entered into the database, the only thing separating
the two being a few hundreds of a second on the acctstarttime field. I
find this behaviour rather annoying, and as I mentioned in an earlier
mail, that was confirmed by Alan DeKok, it is cause by the following:
1) Receives a Start packet and inserts a new entry/session in the db
2) Receives Alive packet _immediately_ after the Start packet, and
queries the database to see if the unique-session-id already exists.
3) The query doesn't return anything, since postgresql hasn't had time
to complete the INSERT-query for the Start packet, and
accounting_update_query_alt is thus run.
I'm not at all experienced with databases, but I was thinking I could
lock the accounting table while inserting the Start message, so that the
query in pnt. 2 could not be performed until the Insert in pnt. 1 is
finished. Wouldn't this elminate the problem in pnt. 3?
How does freeradius react to a situation like this.. does it keep trying
to perform the SELECT query in 2 until it succeds, or is the Alive
packed just dropped?
If the latter is the case, does anyone here know if I can make
postgresql queue requests that get blocked because of the lock? Anyone
else had this problem, and if so, how did you get around it, if you got
around it at all?
Roger Kristiansen <[hidden email]> wrote:
> I'm not at all experienced with databases, but I was thinking I could
> lock the accounting table while inserting the Start message, so that the
> query in pnt. 2 could not be performed until the Insert in pnt. 1 is
> finished. Wouldn't this elminate the problem in pnt. 3?
It would slow the server down considerably.
If you're willing to wait for SQL updates, you can use "rlm_sql_log"
in the CVS head. It avoids these issues by post-processing the logs,
and not running the SQL statements in a live server.