rlm_sql: "NUMERIC VALUE OUT OF RANGE" error

[Phil Mayers]

Alan DeKok wrote:
> Vegard Svanberg wrote:
>> I have a NAS which sends a NAS-Port-Id attribute in the range
>> 2147483648..2164260863. PostgreSQL doesn't like the query Freeradius
>> performs. It's choking when trying to insert for instance
>> '2163214239::integer' into the radacct table.
>>
>> $ select 2163214239::integer;
>> ERROR:  integer out of range
> 
>   It's treating the number as a signed 32-bit integer, and the number is
> greater than 2^31.
> 
>   And the NASPortId field in the default schema is VARCHAR, not integer.

On that note - at least in recent versions of postgres, there is no 
performance benefit to using "varchar(n)" over "text". I would argue the 
default postgres schema should probably use "text" over "varchar", as 
I've been bitten by some packets with fields being larger than their 
schema types (but still legal) e.g.

NAS-Port-Id = GigabitEthernet1/0/1 i.e. 20 bytes

...with NASPortID being a varchar(15)


> 
>   Hmm... the default queries add a "::integer" to the NAS-Port-Id.  Why?

Confusingly, the default postgres accounting queries define a column 
called "NASPortID" then put the value of the "NAS-Port" (not 
NAS-Port-ID) into it. I guess this is why it uses the integer cast.

I tend to think it would be more helpful to store both i.e.

insert into radacct (.. nasportid, nasport, ...) values (
   ..
   %{%{NAS-Port}:-NULL}::integer,
   NULLIF('%{NAS-Port-Id}',''),
   ..
)

To be honest, I don't know why a lot of the typecasts are there; 
postgres will enforce types as necessary. Some of them e.g. the ones to 
"::interval" are needed of course.