Re: Could pgsql jdbc support pool reauthentication?

On 31/10/2017 11:20, Vladimir Sitnikov wrote:
> Achilleas>I know, but we still risk having our max_connections exceeded.
>
> You should limit the work at thread pool level, not at connection pool level.
> That is you should not accept new tasks for execution if they might block on a "getConnection" call.
>
Problem is one single java thread might use all 5 connections, depending on the app. And as each connection is closed, it is returned to the private user's pool, where it will stay until idle-timeout
expires and kills the backend . Till that happens, no one else can use this, which is clearly a poor management of resources.
> Imagine a case:
> 1) Thread A starts a transaction, it calls the DB
> 2) Thread B starts processing another request, it locks some Java lock and calls .getConnection. Unfortunately, there's not enough connections, sot it just waits.
> 3) Thread A completes the DB call, it continues and tries to take the same Java lock.
>
> Now we have a deadlock: A is waiting for the Java lock and it holds a DB connection (with uncommitted transaction), while B is holding a Java lock and it is trying to get a connection.
A buggy application will manifest its problems sooner or later
>
> That is, it is not connection pool's task to limit the number of concurrent requests.
>
> Achilleas>And this is not scaleable.
>
> Can you elaborate?
As described in the first sentence above, this design is not flexible. One should lower idle-timeout to effectively zero, solving the problem of poor management, but effectively loosing the benefits
of having a connection pool in the first place.

That's why reauth plugins was introduced in JCA spec.

>
> Vladimir

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt