freeradius postgresql sql query glitch

[Josip Rodin]

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 at 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.