# postgres.pool

> A module of postgres's API.

`postgres.pool` — connections shared safely between threads.

A `ConnectionPool` keeps a small set of open sessions and hands them out one at
a time.  It is built for a **long-lived service**: one pool for the process
lifetime, several threads checking connections out of it, and connections
recycled underneath so a failover or a nightly restart is survivable without a
restart of your own.

```mojo
from postgres.pool import ConnectionPool, PoolConfig

var cfg = PoolConfig(max_size=8, acquire_timeout_ms=2000)
var pool = ConnectionPool("postgresql://localhost/app?connect_timeout=5", cfg)

with pool.lease() as lease:
    var res = lease.connection().query("SELECT id FROM t WHERE id = $1",
                                       Params().int64(1))
    for row in res:
        print(row.int64("id"))
```

This module is **not** re-exported from `postgres`.  Import it by name, as
above: it pulls in the `threads` tin for its mutex and condition variable, and
a single-threaded caller should not have to carry that.

## The one invariant

`Connection` shares its `PGconn` with every `connection.Statement`,
`connection.Transaction`, `copy.CopyIn` and `copy.CopyOut` made from it, so
that a handle can keep the session open after the `Connection` value is gone.
That is deliberate, documented, and exactly what makes pooling delicate:
libpq forbids two threads touching one `PGconn`, and breaking that rule
corrupts the protocol stream silently rather than crashing.

So the pool's invariant is:

> **A connection goes back into the pool only when nothing else is holding
> it.**

`Lease` enforces it rather than asking for it.  When the lease ends it reads
`Connection._shares`; anything above one means a handle outlived the block, and
the connection is **closed instead of pooled** and counted in
`PoolStats.escaped`.  The escapee then gets SQLSTATE ``08006`` from its next
call, which is loud, local and immediate.

The pool's promise is therefore the same shape as `connection.Transaction`'s:
*the failure mode of losing track is "you lost a connection", never "two
threads share one".*

## Scope the lease with `with`

```mojo
with pool.lease() as lease:
    _ = lease.connection().execute("...")
```

Leaving the block ends the lease, however it is left -- off the end, by
`return`, or by a raise.  Mojo destroys a value after its last *use* rather
than at the end of its scope, so a lease held in a plain `var` also ends
earlier than the indentation suggests; `with` is what makes the extent
visible, and it is the supported form.

A transaction is expected to begin and end inside one lease.  That is not an
honour system either: a `connection.Transaction` still alive when the lease
ends *is* a share, so the connection is discarded rather than handed on with a
block open.

## What happens on return

In order, and only when nothing escaped:

1. If `PQtransactionStatus` is not idle, ``ROLLBACK``.  A lease that left a
   block open, or left it failed, does not leak that into the next caller.
2. With `PoolConfig.on_return` set to `ON_RETURN_DISCARD_ALL`, ``DISCARD
   ALL``.  Opt-in, because it is safer *and* throws away the prepared
   statements a service reissuing the same queries is paying to keep.
3. If the session is older than `PoolConfig.max_lifetime_ms`, or no longer
   alive, it is discarded rather than pooled.

## What happens on checkout

An idle connection is taken most-recently-returned first, then checked:
expired by `PoolConfig.max_lifetime_ms` or `PoolConfig.max_idle_ms`, or no
longer alive.  A connection that fails the check is **recycled in place** with
`Connection.reset` -- one blocking reconnect on a `PGconn` that is already
allocated -- and only closed outright if that fails too.

The liveness check is `Connection.is_alive`: a non-blocking read of what the
server has already sent, with no round trip.  It is not `Connection.ping`,
which is a ``SELECT 1`` per checkout, and it is deliberately *not*
`Connection.is_open`, which cannot see this at all -- `PQstatus` reports a
cached opinion, and a backend killed by ``pg_terminate_backend`` still reads
`CONNECTION_OK` until a command fails on it.

No local check can be certain; a connection can die in the microsecond after
it passes.  The point is not certainty, it is that a *known*-dead connection is
never handed out, and that a connection which dies in a caller's hands is
discarded on return instead of going back in the pool.

## Growth, waiting and reaping

The pool grows lazily to `PoolConfig.max_size`.  `PoolConfig.min_idle` is
opened at construction and kept warm; everything above that is opened when
somebody actually waits for it, because opening blocks and pre-opening the
whole pool would turn a typo in the conninfo into a slow startup failure.

A caller that finds every connection busy blocks on a real
``pthread_cond_t`` -- no spin, no poll -- until one comes back or
`PoolConfig.acquire_timeout_ms` elapses, at which point `ConnectionPool.lease`
raises SQLSTATE ``53300``.

**Reaping is lazy, plus an explicit `ConnectionPool.reap`.**  Idle connections
are aged out when they are next picked up, and `ConnectionPool.reap` does the
same sweep on demand, closing anything past `PoolConfig.max_idle_ms` (never
below `PoolConfig.min_idle`) and reopening back up to `PoolConfig.min_idle`.
There is deliberately no reaper thread: a thread inside a library owes its
caller a lifecycle -- when it starts, how it stops, what happens if the pool is
dropped while it sleeps -- and a detached thread waiting on a condition
variable that is about to be destroyed is precisely the use-after-free this
module exists to rule out.  A service that must not pin backends overnight
already has a timer; `ConnectionPool.reap` is one call on it, and it is the
same code the checkout path runs.

## Threads

`ConnectionPool` is the owner.  `ConnectionPool.ref` hands out a `PoolRef`,
which is copyable, shares the same state, and is what travels into a worker
thread's context.  Every `PoolRef` and every outstanding `Lease` holds a share
of the pool's state, so the state cannot be destroyed underneath a thread that
is still using it.

**A lease may outlive the pool value**, and routinely does: Mojo destroys a
value at its last *use*, so a `ConnectionPool` whose final mention is
`pool.lease()` is gone before the lease it produced, leaving that lease
holding the last share.  Returning the connection then means returning it to a
pool nobody else can see, which is fine and is what the share is for -- but it
also means the pool's mutex is destroyed by the lease that is using it, so
every critical section in this module lives in a function that takes the state
as a *borrowed* argument.  A borrowed argument cannot be destroyed during the
call, which is what keeps the mutex alive across its own unlock; see
`_put_back`.

`ConnectionPool.__init__` checks `PQisthreadsafe` and refuses to build a pool
on a libpq that was compiled without thread safety, because nothing here --
or anywhere else -- is safe on one.

## Structs

### `PoolConfig`

```mojo
struct PoolConfig
```

How big the pool gets, how long a caller waits, and when to recycle.

Every default is chosen for a long-lived service rather than a script: a
small pool, a bounded wait, and connections that age out on their own so a
server restarted underneath the process is recovered from rather than
hoarded through.

Example:

```mojo
var cfg = PoolConfig(
    max_size=16,
    min_idle=2,
    acquire_timeout_ms=2000,
    max_lifetime_ms=900_000,
)
```

Implements: `Copyable`, `ImplicitlyCopyable`, `Movable`

Fields:

- `max_size: Int`: The most connections that may exist at once, idle and busy together.
- `min_idle: Int`: Connections opened at construction and kept warm.
- `acquire_timeout_ms: Int`: How long `ConnectionPool.lease` waits for a connection before raising.
- `max_lifetime_ms: Int`: Retire a connection this long after it was opened.  ``0`` disables it.
- `max_idle_ms: Int`: Close a connection idle this long.  ``0`` disables it.
- `on_return: Int`: `ON_RETURN_ROLLBACK` or `ON_RETURN_DISCARD_ALL`; see both.
- `validate_on_checkout: Bool`: Check `Connection.is_alive` before handing a connection out.

#### `__init__`

```mojo
def __init__(out self, max_size: Int = Int(10), min_idle: Int = Int(0), acquire_timeout_ms: Int = Int(5000), max_lifetime_ms: Int = Int(1800000), max_idle_ms: Int = Int(600000), on_return: Int = Int(0), validate_on_checkout: Bool = True)
```

Build a configuration, taking the defaults for what you do not set.

Args:

- `max_size`: See `PoolConfig.max_size`; ten by default.
- `min_idle`: See `PoolConfig.min_idle`; none by default, so nothing is
    opened until something is leased.
- `acquire_timeout_ms`: See `PoolConfig.acquire_timeout_ms`; five
    seconds by default.
- `max_lifetime_ms`: See `PoolConfig.max_lifetime_ms`; thirty minutes
    by default.
- `max_idle_ms`: See `PoolConfig.max_idle_ms`; ten minutes by default.
- `on_return`: See `PoolConfig.on_return`; `ON_RETURN_ROLLBACK` by
    default.
- `validate_on_checkout`: See `PoolConfig.validate_on_checkout`; on by
    default.

### `PoolStats`

```mojo
struct PoolStats
```

A snapshot of what the pool has been doing.  Cheap; takes the lock.

The counters are cumulative since the pool was built; `PoolStats.idle` and
`PoolStats.busy` are instantaneous.

Implements: `Copyable`, `ImplicitlyCopyable`, `Movable`, `Writable`

Fields:

- `idle: Int`: Connections in the pool right now, ready to hand out.
- `busy: Int`: Connections checked out right now, plus slots reserved by a caller that is in the middle of opening one.
- `opened: Int`: Connections opened, ever -- at construction, on growth, and by `ConnectionPool.reap` topping back up to `PoolConfig.min_idle`.
- `recycled: Int`: Connections reconnected in place by `Connection.reset` after failing a checkout validation.  A rising count is a server that is dropping sessions, not a bug in the pool.
- `discarded: Int`: Connections closed rather than pooled: expired, dead beyond recycling, escaped, or returned after `ConnectionPool.close`.  Includes `PoolStats.escaped`.
- `escaped: Int`: Leases that ended with something still holding the connection.
- `waits: Int`: Times a caller blocked because every connection was busy.
- `timeouts: Int`: Times that block ran out of `PoolConfig.acquire_timeout_ms` and `ConnectionPool.lease` raised ``53300``.

#### `write_to`

```mojo
def write_to(self, mut writer: T)
```

Render as one line, for a log.

Args:

- `writer`: The sink to write to.

### `Lease`

```mojo
struct Lease
```

One connection, checked out, for the extent of a `with` block.

Produced by `ConnectionPool.lease`.  Reach the connection through
`Lease.connection`:

```mojo
with pool.lease() as lease:
    var res = lease.connection().query("SELECT 1")
    print(res.num_rows())
```

**The lease ends when this value is destroyed**, which for a `with` block
is when the block ends -- off the end, by `return`, or by a raise -- and
for a plain `var` is at its last *use*, which is usually earlier than it
looks.  Prefer `with`; it is the form that makes the extent match the
indentation.

Ending the lease does one of two things.  If nothing else is holding the
connection, the session is cleaned -- rolled back if a block was left open
-- and it goes back into the pool.  If something *is* holding it -- a
`connection.Statement`, a `connection.Transaction`, a `copy.CopyIn` or a
`copy.CopyOut` that outlived the block -- the connection is **closed**, and
`PoolStats.escaped` records it.

That second branch is the whole reason this type exists.  Those handles
share the `PGconn` on purpose, so one of them can outlive the `Connection`
value; pooled and handed to a second thread, that sharing becomes two
threads on one `PGconn`, which libpq forbids and which corrupts the
protocol stream without crashing.  Closing costs one connection and reports
itself.  Pooling would cost correctness and say nothing.

Not copyable: a lease is exclusive use, and two of them would not be.

Implements: `Movable`

#### `__init__`

```mojo
def __init__(out self, state: ArcPointer[_PoolState], var conn: Connection, opened_at_ms: Int)
```

Take a checked-out connection.  `ConnectionPool.lease` builds these.

Args:

- `state`: The pool's shared state.
- `conn`: The connection, already counted in `PoolStats.busy`.
- `opened_at_ms`: From `_now_ms`, when the session began.

#### `__deinit__`

```mojo
def __deinit__(deinit self)
```

End the lease: return the connection, or discard it.

The refcount check is here rather than in an `__exit__`, because Mojo
refuses a type that has both a consuming `__enter__` and an `__exit__`
-- and a consuming `__enter__` is what makes `with` transfer the guard
into the block.  `connection.Transaction` is shaped the same way for
the same reason.  It is also the stronger of the two: a `Lease` that
never reaches a `with` block at all still ends here.

#### `__enter__`

```mojo
def __enter__(deinit self) -> Self
```

Hand the lease to ``with pool.lease() as lease:``.

The lease is transferred into the block rather than borrowed, so it is
destroyed -- and the connection therefore returned or discarded -- when
the block ends, however it ends.

#### `connection`

```mojo
def connection(mut self) -> ref[self._conn] Connection
```

The leased connection.

#### `backend_pid`

```mojo
def backend_pid(self) -> Int
```

The backend serving this lease, for logging and for tests.

### `PoolRef`

```mojo
struct PoolRef
```

A copyable handle on a pool, for the threads that share it.

A worker thread reaches its pool through one of these -- put it in the
context struct `threads.parallel_for` or `threads.pool.TypedPool` hands
around, and every worker leases from the same pool:

```mojo
@fieldwise_init
struct Ctx(Copyable, Movable):
    var pool: PoolRef

def worker(i: Int, mut ctx: Ctx) -> None:
    try:
        with ctx.pool.lease() as lease:
            _ = lease.connection().execute("SELECT 1")
    except:
        pass

parallel_for[worker](64, ctx, num_workers=8)
```

It holds a share of the pool's state, so the state stays alive for as long
as any thread can still reach it.  What it does *not* carry is ownership of
the pool's lifecycle: `ConnectionPool.close` remains the owner's call.

Implements: `Copyable`, `ImplicitlyCopyable`, `Movable`

#### `lease`

```mojo
def lease(self) -> Lease
```

Check a connection out.  See `ConnectionPool.lease`.

Raises: Error: A `sqlstate.PostgresError`; see `ConnectionPool.lease`.

#### `stats`

```mojo
def stats(self) -> PoolStats
```

A snapshot of the pool's counters.  See `ConnectionPool.stats`.

#### `reap`

```mojo
def reap(self)
```

Age out idle connections.  See `ConnectionPool.reap`.

### `ConnectionPool`

```mojo
struct ConnectionPool
```

A pool of connections, safe to lease from several threads.

Build one per process and keep it for the process lifetime; see the module
docstring for the invariant it enforces and how.

Example:

```mojo
from postgres.pool import ConnectionPool, PoolConfig

var pool = ConnectionPool(
    "postgresql://localhost/app?connect_timeout=5",
    PoolConfig(max_size=8, min_idle=1, acquire_timeout_ms=2000),
)

with pool.lease() as lease:
    _ = lease.connection().execute("DELETE FROM sessions WHERE expired")
```

Not copyable -- `ConnectionPool.ref` is how a second holder is made, and it
is deliberately not an owner.

Implements: `Movable`

#### `__init__`

```mojo
def __init__(out self, conninfo: String, config: PoolConfig = PoolConfig(Int(10), Int(0), Int(5000), Int(1800000), Int(600000), Int(0), True))
```

Build a pool over `conninfo`, opening `PoolConfig.min_idle` now.

Args:

- `conninfo`: A libpq URI or keyword string, exactly as
    `connection.Connection` takes it.  **Set ``connect_timeout``**:
    a pool whose opens can block for the OS TCP timeout blocks
    every waiter behind them.
- `config`: The pool's configuration.

Raises: Error: If libpq was built without thread safety -- a pool is not
safe on such a build and this refuses to pretend otherwise --
if `PoolConfig.max_size` is below one or `PoolConfig.min_idle`
is above it, or if one of the `PoolConfig.min_idle`
connections could not be opened.  Opening those eagerly is
what turns a wrong conninfo into a failure here rather than a
failure in whichever thread happens to lease first.

#### `ref`

```mojo
def ref(self) -> PoolRef
```

A copyable handle on this pool, for a worker thread's context.

#### `lease`

```mojo
def lease(self) -> Lease
```

Check a connection out, blocking until one is free.

Takes the most recently returned idle connection, or opens a new one if
the pool is below `PoolConfig.max_size`, or blocks on the pool's
condition variable until somebody returns one.  An idle connection that
has expired or is no longer alive is recycled with `Connection.reset`
before it is handed out, and closed if that fails.

Waiters are not queued.  A returning connection wakes one of them, and
a thread arriving at that moment may take it first -- so a waiter can
be passed over, and `PoolConfig.acquire_timeout_ms` rather than its
place in a line is what bounds how long it waits.  That is the usual
trade for a pool (a fair queue costs a handoff per return and starves
throughput under load), and it is worth knowing before reading a
latency histogram with a long tail on it.

Raises: Error: A `sqlstate.PostgresError` with SQLSTATE ``53300`` if
`PoolConfig.acquire_timeout_ms` elapsed with every connection
busy; ``08003`` if `ConnectionPool.close` has run; or ``08001``
if a new connection was needed and could not be opened.

#### `stats`

```mojo
def stats(self) -> PoolStats
```

A snapshot of the pool's counters.

Takes the lock, so it is consistent with itself; it is a snapshot all
the same, and another thread may have changed it before you read it.

#### `reap`

```mojo
def reap(self)
```

Close idle connections that have aged out, and top back up.

Closes every idle connection past `PoolConfig.max_idle_ms` or
`PoolConfig.max_lifetime_ms`, never going below `PoolConfig.min_idle`,
and then opens whatever it takes to get back *to*
`PoolConfig.min_idle`.  Safe to call from any thread, at any time, as
often as you like.

This is the pool's maintenance tick, and it is deliberately yours to
call rather than a thread of the pool's own; the module docstring says
why.  A service that leases at least occasionally does not need it --
the same ageing happens on checkout -- but one that goes quiet
overnight does, or it holds its backends until morning.

Never raises: a connection it could not reopen leaves the pool smaller,
and the next `ConnectionPool.lease` will try again.

#### `close`

```mojo
def close(self)
```

Close every idle connection and refuse every later lease. Idempotent.

Safe to call with leases outstanding, and it does not touch them: a
connection another thread is using is that thread's, and reaching into
it is the exact race this module exists to prevent.  Each outstanding
lease finds the pool closed when it ends and closes its connection
instead of pooling it, so the pool drains as its leases come home.
`PoolStats.busy` is how many are still out.

Waiters blocked in `ConnectionPool.lease` are woken and raise ``08003``.

## Aliases

### `ON_RETURN_ROLLBACK`

```mojo
comptime ON_RETURN_ROLLBACK = Int(0)
```

Roll back an open block and pool the connection.  The default.

Cheap -- a ``ROLLBACK`` only when `PQtransactionStatus` says one is needed, so
the common case of a clean lease costs nothing -- and it keeps the session's
prepared statements, temp tables and session GUCs, which is what a service
reissuing the same queries is paying for.

### `ON_RETURN_DISCARD_ALL`

```mojo
comptime ON_RETURN_DISCARD_ALL = Int(1)
```

Issue ``DISCARD ALL`` on every return, on top of the rollback.

Safer and more expensive: it resets the session to its just-connected state,
which also throws away every prepared statement, temp table, cursor,
``SET``-in-session and advisory lock.  Worth it when leases cannot be trusted
to leave the session alone; not worth it otherwise.