# ADR 0052: The `postgres` package — wire protocol, connection actors, pool, `sql` effect **Status:** Proposed (2026-10-18, issue #145); needs engineering review (#44). ## Context DDD applications keep their state in PostgreSQL. The persistence layer must fit Louise's model: sockets are non-blocking and park only the actor that waits (ADR 0012), domain code reaches the database through effects (ports) that tests handle in memory, and a C binding (libpq) would run each call on the blocking pool (ADR 0005) — one OS thread per call in flight. ## Decision `packages/postgres` speaks the PostgreSQL wire protocol (3.0) in Louise, over `std/net` and `std/tls`: - **The codec is pure Louise** (`protocol.lse`, `binary.lse`): messages to and from `Bytes`, big-endian, length-checked before allocation. Unit-tested against recorded byte transcripts on both backends; no database needed. - **Extended query protocol only.** Values always travel as parameters (`$1`), binary per type where a codec exists, so the API has no way to build an injection. The `sql` tagged template (ADR 0048) writes real SQL with holes becoming parameters. Statements are prepared once per connection (Parse + Describe, cached by SQL text). - **Authentication:** trust, cleartext (over TLS or localhost only), and SCRAM-SHA-256 (RFC 5802/7677) over ADR 0050's primitives. MD5 is not supported, as PostgreSQL deprecates it. - **One actor per connection** owns its socket and protocol state, so a connection is never used by two requests at once. **A pool actor** hands connections out: `size`, `min_idle`, `idle_timeout`, `max_lifetime`, and a liveness check — a connection idle past `check_after` is pinged (a bare `Sync`, which also proves no transaction is open) before handout, so a stale connection is replaced instead of failing a borrower's first query. Timeouts are in milliseconds, evaluated on the pool's `tick`: the pool maintains itself when quiet, which is when reaping matters, and checks handouts and returns per message. Borrowers and connections are monitored: a dead borrower frees its connection (closed, not reused), a dead connection frees its slot, waiters queue with a caller-side acquire timeout. - **Transactions check a connection out exclusively**: `pg.transaction(pool, action)` sends BEGIN, runs `action`, COMMITs; on `Error` it ROLLBACKs. `pg.transaction_retry` re-runs serialization failures (`40001`) and deadlocks (`40P01`); `pg.savepoint` nests. `pg.cancel` sends a CancelRequest on a fresh connection with the backend key data from the handshake. `pg.listen`/`pg.unlisten` run a dedicated listener connection outside the pool for LISTEN/NOTIFY. - **The `sql` effect is the ambient API**, handled by `pg.with_connection` and `pg.transaction`: adapters are written against `sql` and tested with an in-memory handler; domain code sees only its own ports. ### Deviations from the issue's sketch, and why - **No `defer`-driven rollback.** Cleanups may not perform `proc` (E0506), so cleanup is explicit on the normal paths; an unwind crashes the borrower, which the pool's monitor sees (the connection is closed, not reused). A `final ctl` past the block is the documented hole. - **`row.int("id")` by name**, errors naming the column and the types; `@derive(FromRow)` waits on the producer derives #140 asks of ADR 0019. - **`numeric` reads as `String`** until std has a decimal type. - **TLS upgrade per `sslmode`** with ADR 0038's `std/tls` after the protocol's `SslRequest`; `require` refuses a plain answer. - The pool runs **under no supervisor**: supervise the caller and start it again on a crash, rather than a supervisor per pool. ### Naming wart, for the review The effect `sql` and the `sql` template function share a name in the package's root. Effect operations resolve as module items (`pg.query(...)`), so both are usable — but `import postgres.{sql}` brings the template function, and `import postgres.{type sql}` the effect, which reads badly at call sites. Renaming one (the effect to `db`?) is a review question; the wart is documented, not settled. ## Consequences - No C dependency; a parked query parks only its actor. - The package is live-tested against PostgreSQL in a container when `LOUISE_PG_URL` is set (skipped otherwise): authentication, the types and NULL, SQLSTATE errors, rollback on error and on a killed borrower, cancellation, LISTEN/NOTIFY, and the pool's reaping, recycling and stale-connection replacement. - `examples/event_store` is the reference consumer: the `event_store` effect with an in-memory handler (tests) and a PostgreSQL one (production), optimistic concurrency as `Conflict` from SQLSTATE `23505`, and the outbox in the same transaction with `pg_notify` waking the projection. - Not yet: `COPY`, pipelining, replication, the simple query protocol (BEGIN/COMMIT/ROLLBACK use extended), Unix-domain sockets. ## Alternatives considered - **libpq over FFI.** Rejected: one blocking-pool thread per call in flight, a C dependency, and C error shapes instead of Louise's. - **Wall-clock timeouts** (`std/time`) in the pool. The pool counts ticks instead: pool timeouts are coarse, the effect rows of `start_pool` and friends stay `` (no `clock`), and reaping matters exactly when the pool is quiet, which is when ticks fire. - **Pinging on every handout.** Rejected: a round trip per acquire on a hot pool, to catch a case (a long-idle stale connection) that `check_after` catches with one ping per long-idle connection.