On Mon, Dec 07, 2009 at 01:16:02PM -0700, Guy Fraser wrote:
sql trace log indicates that this is the offending query:
UPDATE radacct SET AcctStopTime = ('2009-12-07 13:19:01'::timestamp - '6'::interval), AcctSessionTime = CASE WHEN '' = '' THEN (EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE - '6'::INTERVAL)))::BIGINT ELSE '' END, AcctInputOctets = (('0'::bigint << 32) + '0'::bigint), AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint), AcctTerminateCause = 'User-Request', AcctStopDelay = 0, FramedIPAddress = NULLIF('4.3.2.1', '')::inet, ConnectInfo_stop = '' WHERE AcctSessionId = '57fc9e4821466d86' AND UserName = 'our@user.name' AND NASIPAddress = '1.2.3.4' AND AcctStopTime IS NULL;
I have been using that query for years and have never had a problem.
The exact failing query is right there pasted from the log - you can reproduce it yourself if you have PostgreSQL 8.1, either by supplying it fake data or by trivially modifying the query.
"select CASE WHEN '' = '' THEN" does not care what is in the empty strings because they are not designated as integers.
Your problem is elsewhere.
Yes, it's not there, it's in the else case, like I said.
Have you checked to see if AcctStartTime has valid data?
Try :
select AcctStartTime from radacct where AcctSessionId = '57fc9e4821466d86';
If it contains data that looks like an integer then try :
\d raddact
Make sure AcctStartTime is a time stamp.
It's fine, that subexpression returns a valid number. The problem is that the PostgreSQL server rejects the else case. Please read the rest of my previous message again :)
Why is your stop record broken? Stop records are supposed to have a valid Acct-Session-Time, your would appear to be missing.
I don't know why that particular record didn't have Acct-Session-Time, but the default query format already compensates for that, that's why the whole CASE is there. If the stop record doesn't carry Acct-Session-Time, it calculates it from the current timestamp (that's what %S expands into), minus the start time, minus the delay time. And that code is correct, because the session time attribute is not mandatory in accounting packets, per RFC 2866 - section 5.13 lists it as "0-1", meaning "Zero or one instance of this attribute MAY be present." -- 2. That which causes joy or happiness.