Freeradius Postgres and Pap problem

[Jack Allan]

Phil Mayers wrote:
> That is very odd.
>
> I note you are running custom SQL queries against a union of a 
> password table and a stored procedure - it is possible something 
> occurs inside the stored proc that causes a backend to lock up or 
> start behaving abnormally.
>
> What happens if you execute:
>
> SELECT -1 AS id, username,'User-Password' AS attribute, password AS 
> value, '==' AS op ??FROM raduser WHERE  username = 'jack'
> UNION
> SELECT * FROM radius_authorise_check('jack','skyrove_bree') ORDER BY id;
>
> ...repeatedly inside "psql" (with a connection using the same 
> parameters at the FreeRadius driver)?
>
> Note that you are incorrect about the connections being "dropped", and 
> that you should not expect round-robin behaviour. The server will open 
> a certain number of sockets. When a request comes in, the server grabs 
> an unused socket from the list, uses it, and returns it. Which socket 
> you get cannot be easily predicted.
>
>
>> rlm_sql (auth): Reserving sql socket id: 0
>> rlm_sql (auth): Released sql socket id: 0
>
>> Sending Access-Accept of id 38 to 165.146.6.102 port 2322
>
>> rlm_sql (auth): Reserving sql socket id: 0
>> rlm_sql (auth): Released sql socket id: 0
>
>> Sending Access-Reject of id 39 to 165.146.6.102 port 2323
>
>
> That is, both request went to the same socket (and thus postmaster 
> backend).
>
> I would suggest bumping the logging on postgres and seeing if you can 
> determine why a query against the same backend, on the same 
> connection, fails on subsequent calls.
> - List info/subscribe/unsubscribe? See 
> http://www.freeradius.org/list/users.html
>
Hi Phil

Thanks to your advice, I have solved the problem. Although it had 
nothing to do with the user functions that I had created. The culprit 
was the UNION statement. I don't know why this is the case, but after 
removing the UNION statement, everything works perfectly. I plan to 
investigate this and post to you guys so that the community will have a 
knowledge base. But you can do a simple test. Just union together two 
SQL statements. Make these statements static (for the test) so that you 
do not access a table. You will get the same problem that I have. Have 
you any idea why?

Thanks so much for the help. Have a good day.
Jack