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
-----------------------------------------------
--
https://mail.python.org/mailman/listinfo/python-list
