pool
– Connection pool implementations¶
The package contains two connection pool implementations. A connection pool creates and maintains a limited amount of PostgreSQL connections and allows a larger number of users to use them. See Connection pools for more details and usage pattern.
There package implement two connection pools: ConnectionPool
is a
synchronous connection pool yielding Connection
objects and can be
used by multithread applications. AsyncConnectionPool
has a similar
interface, but with asyncio
functions replacing blocking functions, and
yields AsyncConnection
instances.
The intended use (but not mandatory) is to create a single connection pool, as
a global object exposed by a module in your application, and use the same
instance from the rest of the code (especially the
connection()
method.
The ConnectionPool
class¶
-
class
psycopg3.pool.
ConnectionPool
(conninfo, *, **arguments)¶ This class implements a connection pool serving
Connection
instances (or subclasses). The constructor has alot of arguments, but only conninfo and min_size are the fundamental ones, all the other arguments have meaningful defaults and can probably be tweaked later, if required.- Parameters
conninfo (
str
) – The connection string. Seeconnect()
for details.min_size (
int
, default: 4) – The minimum number of connection the pool will hold. The pool will actively try to create new connections if some are lost (closed, broken) and will try to never go below min_sizemax_size (
int
, default:None
) – The maximum number of connections the pool will hold. IfNone
, or equal to min_size, the pool will not grow or shrink. If larger than min_size, the pool can grow if more than min_size connections are requested at the same time and will shrink back after the extra connections have been unused for more than max_idle seconds.kwargs (
dict
) – Extra arguments to pass toconnect()
. Note that this is one dict argument of the pool constructor, which is expanded asconnect()
keyword parameters.connection_class (
type
, default:Connection
) – The class of the connections to serve. It should be aConnection
subclass.configure (
Callable[[Connection], None]
) – A callback to configure a connection after creation. Useful, for instance, to configure its adapters. If the connection is used to run internal queries (to inspect the database) make sure to close an eventual transaction before leaving the function.reset (
Callable[[Connection], None]
) – A callback to reset a function after it has been returned to the pool. The connection is guaranteed to be passed to the reset() function in “idle” state (no transaction). When leaving the reset() function the connection must be left in idle state, otherwise it is discarded.name (
str
) – An optional name to give to the pool, useful, for instance, to identify it in the logs if more than one pool is used. if not specified pick a sequential name such aspool-1
,pool-2
, etc.timeout (
float
, default: 30 seconds) – The default maximum time in seconts that a client can wait to receive a connection from the pool (usingconnection()
orgetconn()
). Note that these methods allow to override the timeout default.max_waiting (
int
, default: 0) – Maximum number of requests that can be queued to the pool. Requesting will fail, raisingTooManyRequests
. 0 means no queue limit.max_lifetime (
float
, default: 1 hour) – The maximum lifetime of a connection in the pool, in seconds. Connections used for longer get closed and replaced by a new one. The amount is reduced by a random 10% to avoid mass eviction.max_idle (
float
, default: 10 minutes) – Maximum time, in seconds, that a connection can stay unused in the pool before being closed, and the pool shrunk. This only happens to connections more than min_size, if max_size allowed the pool to grow.reconnect_timeout (
float
, default: 5 minutes) – Maximum time, in seconds, the pool will try to create a connection. If a connection attempt fails, the pool will try to reconnect a few times, using an exponential backoff and some random factor to avoid mass attempts. If repeated attempts fail, after reconnect_timeout second the connection attempt is aborted and the reconnect_failed callback invoked.reconnect_failed (
Callable[[ConnectionPool], None]
) – Callback invoked if an attempt to create a new connection fails for more than reconnect_timeout seconds. The user may decide, for instance, to terminate the program (executingsys.exit()
). By default don’t do anything: restart a new connection attempt (if the number of connection fell below min_size).num_workers (
int
, default: 3) – Number of background worker threads used to maintain the pool state. Background workers are used for example to create new connections and to clean up connections when they are returned to the pool.
-
wait
(timeout: float = 30.0)¶ Wait for the pool to be full (with
min_size
connections) after creation.Raise
PoolTimeout
if not ready within timeout sec.Calling this method is not mandatory: you can try and use the pool immediately after its creation. The first client will be served as soon as a connection is ready. You can use this method if you prefer your program to terminate in case the environment is not configured properly, rather than trying to stay up the harder it can.
-
connection
(timeout: Optional[float] = None) → Iterator[psycopg3.Connection]¶ Context manager to obtain a connection from the pool.
Returned the connection immediately if available, otherwise wait up to timeout or
self.timeout
seconds and throwPoolTimeout
if a connection is not available in time.Upon context exit, return the connection to the pool. Apply the normal connection context behaviour (commit/rollback the transaction in case of success/error). If the connection is no more in working state replace it with a new one.
with my_pool.connection() as conn: conn.execute(...) # the connection is now back in the pool
-
close
(timeout: float = 5.0)¶ Close the pool and make it unavailable to new clients.
All the waiting and future client will fail to acquire a connection with a
PoolClosed
exception. Currently used connections will not be closed until returned to the pool.Wait timeout for threads to terminate their job, if positive. If timeout expires the pool is closed anyway, although it may raise some warnings on exit.
Note
The pool can be used as context manager too, in which case it will be closed at the end of the block:
with ConnectionPool(...) as pool: # code using the pool
-
property
min_size
¶
-
property
max_size
¶ The current minimum and maximum size of the pool. Use
resize()
to change them at runtime.
-
check
()¶ Verify the state of the connections currently in the pool.
Test each connection: if it works return it to the pool, otherwise dispose of it and create a new one.
-
pop_stats
() → Dict[str, int]¶ Return current stats about the pool usage.
After the call, all the counters are reset to zero.
See Pool stats for the metrics returned.
Functionalities you may not need
-
getconn
(timeout: Optional[float] = None) → psycopg3.Connection¶ Obtain a contection from the pool.
You should preferrably use
connection()
. Use this function only if it is not possible to use the connection as context manager.After using this function you must call a corresponding
putconn()
: failing to do so will deplete the pool. A depleted pool is a sad pool: you don’t want a depleted pool.
-
putconn
(conn: psycopg3.Connection)¶ Return a connection to the loving hands of its pool.
Use this function only paired with a
getconn()
. You don’t need to use it if you use the much more comfortableconnection()
context manager.
-
class
psycopg3.pool.
PoolTimeout
¶ The pool couldn’t provide a connection in acceptable time.
Subclass of
OperationalError
-
class
psycopg3.pool.
PoolClosed
¶ Attempt to get a connection from a closed pool.
Subclass of
OperationalError
-
class
psycopg3.pool.
TooManyRequests
¶ Too many requests in the queue waiting for a connection from the pool.
Subclass of
OperationalError
The AsyncConnectionPool
class¶
AsyncConnectionPool
has a very similar interface to the ConnectionPool
class but its blocking method are implemented as async
coroutines. It
returns AsyncConnection
instances, or its subclasses if specified so in the
connection_class parameter.
Only the function with different signature from ConnectionPool
are
listed here.
-
class
psycopg3.pool.
AsyncConnectionPool
(conninfo, *, **arguments)¶ All the other parameters are the same.
- Parameters
connection_class (
type
, default:AsyncConnection
) – The class of the connections to serve. It should be anAsyncConnection
subclass.configure (
async Callable[[AsyncConnection], None]
) – A callback to configure a connection after creation.reset (
async Callable[[AsyncConnection], None]
) – A callback to reset a function after it has been returned to the pool.
-
connection
(timeout: Optional[float] = None) → AsyncIterator[psycopg3.AsyncConnection]¶ async with my_pool.connection() as conn: await conn.execute(...) # the connection is now back in the pool
-
async
close
(timeout: float = 5.0)¶ Note
The pool can be used as context manager too, in which case it will be closed at the end of the block:
async with AsyncConnectionPool(...) as pool: # code using the pool
-
async
check
()¶
-
async
getconn
(timeout: Optional[float] = None) → psycopg3.AsyncConnection¶
-
async
putconn
(conn: psycopg3.AsyncConnection)¶