Re: freeradius postgresql sql query glitch - Mailing list pgsql-general

----- "Josip Rodin" <joy@entuzijast.net> wrote:
> Hi,
>
> I've observed an SQL logging problem with FreeRADIUS (2.x) and
> PostgreSQL
> (8.1), on several different installations I occasionally get these
> errors:
>
> Mon Dec  7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> accounting STOP record - ERROR:  invalid input syntax for integer: ""
>
>
> 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'm using the default unchanged sql/postgresql/dialup.conf setting:
>
> accounting_stop_query = "UPDATE ${acct_table2} \
>   SET AcctStopTime = ('%S'::timestamp -
> '%{%{Acct-Delay-Time}:-0}'::interval), \
>   AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
>
>   (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> AcctStartTime::TIMESTAMP WITH TIME ZONE \
>   - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> '%{Acct-Session-Time}' END, \
>   AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
> '%{%{Acct-Input-Octets}:-0}'::bigint), \
>   AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32)
> + '%{%{Acct-Output-Octets}:-0}'::bigint), \
>   AcctTerminateCause = '%{Acct-Terminate-Cause}', \
>   AcctStopDelay = 0, \
>   FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
>   ConnectInfo_stop = '%{Connect-Info}' \
>   WHERE AcctSessionId = '%{Acct-Session-Id}' \
>   AND UserName = '%{SQL-User-Name}' \
>   AND NASIPAddress = '%{NAS-IP-Address}' \
>   AND AcctStopTime IS NULL"
>
> Looks like the code wants to use CASE to check whether
> %{Acct-Session-Time}
> exists among the internal FreeRADIUS variables, while the return value
> of
> the whole SQL CASE construct is supposed to be a bigint.
>
> This is a reduced failing case:
>
> radiustmobile=# select 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 from radacct where AcctSessionId = '57fc9e4821466d86';
> ERROR:  invalid input syntax for integer: ""
>
> In the else case, this fallback return value comes into PostgreSQL as
> just
> an empty string, which causes it to trip over - it sees that there's
> a
> possibility to write an empty string into a bigint field, which
> provokes
> the syntax error, even if the problem won't actually happen with this
> particular setup of input data.
>
> I'm not sure what to do... can the query be rewritten in a manner that
> would
> allow for both use cases?

If I understand correctly the below may work. If %{Acct-Session-Time} is an empty string it will return NULL otherwise
itwill return %{Acct-Session-Time}. 

 ELSE
NULLIF('%{Acct-Session-Time}','') END,

See here details:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12697

Adrian Klaver
aklaver@comcast.net