Psycopg2 pool clarification

[israel]

Since I've gotten no replies to this, I was wondering if someone could 
at least confirm which behavior (my expected or my observed) is 
*supposed* to be the correct? Should a psycopg2 pool keep connections 
open when returned to the pool (if closed is False), or should it close 
them as long as there is more than minconn open? i.e is my observed 
behavior a bug or a feature?

On 2017-06-02 15:06, Israel Brewster wrote:
> I've been using the psycopg2 pool class for a while now, using code
> similar to the following:
> 
>>>> pool=ThreadedConnectionPool(0,5,<connection_args>)
>>>> conn1=pool.getconn()
>>>> <do whatever with conn1>
>>>> pool.putconn(conn1)
> .... repeat later, or perhaps "simultaneously" in a different thread.
> 
> and my understanding was that the pool logic was something like the 
> following:
> 
> - create a "pool" of connections, with an initial number of
> connections equal to the "minconn" argument
> - When getconn is called, see if there is an available connection. If
> so, return it. If not, open a new connection and return that (up to
> "maxconn" total connections)
> - When putconn is called, return the connection to the pool for
> re-use, but do *not* close it (unless the close argument is specified
> as True, documentation says default is False)
> - On the next request to getconn, this connection is now available and
> so no new connection will be made
> - perhaps (or perhaps not), after some time, unused connections would
> be closed and purged from the pool to prevent large numbers of only
> used once connections from laying around.
> 
> However, in some testing I just did, this doesn't appear to be the
> case, at least based on the postgresql logs. Running the following
> code:
> 
>>>> pool=ThreadedConnectionPool(0,5,<connection_args>)
>>>> conn1=pool.getconn()
>>>> conn2=pool.getconn()
>>>> pool.putconn(conn1)
>>>> pool.putconn(conn2)
>>>> conn3=pool.getconn()
>>>> pool.putconn(conn3)
> 
> produced the following output in the postgresql log:
> 
> 2017-06-02 14:30:26 AKDT LOG:  connection received: host=::1 port=64786
> 2017-06-02 14:30:26 AKDT LOG:  connection authorized: user=logger
> database=flightlogs
> 2017-06-02 14:30:35 AKDT LOG:  connection received: host=::1 port=64788
> 2017-06-02 14:30:35 AKDT LOG:  connection authorized: user=logger
> database=flightlogs
> 2017-06-02 14:30:46 AKDT LOG:  disconnection: session time:
> 0:00:19.293 user=logger database=flightlogs host=::1 port=64786
> 2017-06-02 14:30:53 AKDT LOG:  disconnection: session time:
> 0:00:17.822 user=logger database=flightlogs host=::1 port=64788
> 2017-06-02 14:31:15 AKDT LOG:  connection received: host=::1 port=64790
> 2017-06-02 14:31:15 AKDT LOG:  connection authorized: user=logger
> database=flightlogs
> 2017-06-02 14:31:20 AKDT LOG:  disconnection: session time:
> 0:00:05.078 user=logger database=flightlogs host=::1 port=64790
> 
> Since I set the maxconn parameter to 5, and only used 3 connections, I
> wasn't expecting to see any disconnects - and yet as soon as I do
> putconn, I *do* see a disconnection. Additionally, I would have
> thought that when I pulled connection 3, there would have been two
> connections available, and so it wouldn't have needed to connect
> again, yet it did. Even if I explicitly say close=False in the putconn
> call, it still closes the connection and has to open
> 
> What am I missing? From this testing, it looks like I get no benefit
> at all from having the connection pool, unless you consider an upper
> limit to the number of simultaneous connections a benefit? :-) Maybe a
> little code savings from not having to manually call connect and close
> after each connection, but that's easily gained by simply writing a
> context manager. I could get *some* limited benefit by raising the
> minconn value, but then I risk having connections that are *never*
> used, yet still taking resources on the DB server.
> 
> Ideally, it would open as many connections as are needed, and then
> leave them open for future requests, perhaps with an "idle" timeout.
> Is there any way to achieve this behavior?
> 
> -----------------------------------------------
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293
> -----------------------------------------------