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.