freeradius postgresql sql query glitch - Mailing list pgsql-general

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
syntaxfor 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::TIMESTAMPWITH 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?

(Mailing list users, please Cc: responses for those of us who may not be
subscribed. TIA.)

--
     2. That which causes joy or happiness.