Documentation · PostgreSQL
Sessions and pooling on Postgres
A generated Postgres server is always pooled, and each tool call is one transaction. This page is the Postgres counterpart of Session management and Connection pooling, which are written for Oracle.
Finding the sessions
Both halves of the product set application_name, which pg_stat_activity shows. Postgres has no
DBMS_APPLICATION_INFO, so this is where a module name goes.
application_name | |
|---|---|
| The generator, reading your catalog | MCPDBWizard |
| A generated server, running your tools | The DAO factory’s class name: DaoFactory unless you renamed it |
So a running server identifies itself as your factory class, DAO_FACTORY_NAME in the config, not
as MCPDBWizard. Give each config its own factory name and you can tell its sessions apart from
every other application using the same login. Postgres keeps at most 63 characters of the name.
SELECT pid, usename, application_name, state,
now() - query_start AS running, left(query, 80) AS query
FROM pg_stat_activity
WHERE application_name IN ('MCPDBWizard', 'DaoFactory') -- use YOUR factory name
ORDER BY query_start;
query is the statement the session ran last, with $1, $2 where the values were bound: a
generated server never puts a value into SQL text. An idle pooled session shows state = 'idle'.
To stop a call, SELECT pg_cancel_backend(pid) cancels its statement and the call fails;
pg_terminate_backend(pid) ends the session, and the pool replaces it.
Pooling is always on
The console’s pooling switch is fixed on for Postgres, and the generator refuses a Postgres MCP
server with DAO_POOL=NO. An unpooled server shares one connection that never commits or rolls
back, and on Postgres one failed statement aborts the transaction: every later call would then fail
with SQLSTATE 25P02 until the connection was rolled back.
Pooled, each concurrent call borrows its own DAO factory, with its own connection and its own already-prepared statements. The pool settings are the same as on Oracle, and each can be overridden by an environment variable of the same name without regenerating:
| Key | Meaning |
|---|---|
DAO_POOL_MAX_SIZE | Ceiling. Also your effective concurrency limit. |
DAO_POOL_MIN_IDLE | How many stay connected when idle. |
DAO_POOL_MAX_WAIT_MS | How long a call waits for a free factory before “Server busy”. |
DAO_POOL_IDLE_TIMEOUT_MS | How long an idle factory survives before it is closed. |
DAO_POOL_ON_RETURN | COMMIT or ROLLBACK after a call that succeeded. |
Sizing
Size the maximum against the server’s max_connections, not against how many CPUs you have.
Each factory in the pool is one Postgres connection, and so one backend process with its own
memory. max_connections is 100 by default, of which superuser_reserved_connections (3 by
default) are kept back. If you run several generated servers against one database, it is the
total across them, plus everything else that connects, that has to fit.
Postgres has no counterpart of Oracle’s OPEN_CURSORS: the number of statements a session keeps
prepared has no limit to run into. What they cost is memory. A factory keeps every statement it
has used, and after five executions the driver prepares one on the server, where its plan stays for
the life of the connection. Measured on Postgres 18: 500 simple statements held on one connection
took about 2 MB of plans. Heavier statements take more, so a large config, a large pool and several
servers multiply: allow for it in the server’s memory, since nothing refuses a statement when it
runs short. Open cursors do not accumulate, because each call’s transaction closes them.
One call, one transaction
When a call succeeds, its transaction is committed (or rolled back, with DAO_POOL_ON_RETURN=ROLLBACK).
When it fails, it is rolled back, so the next caller of that connection starts clean. A COMMIT
inside a procedure commits the call’s work up to that point, as it would anywhere: a later failure
cannot undo what came before it.
Context Pinning values live for the same transaction. Each is set with
set_config(..., true), transaction-local, so it reverts by itself when the call’s transaction
ends and never reaches the next caller.
Runaway calls
DAO_QUERY_TIMEOUT_SECONDS caps how long one statement may run. Past it, the driver cancels the
statement, Postgres raises SQLSTATE 57014 (“canceling statement due to user request”), the call
fails with that in its error, and the connection goes back to the pool. A rate limit bounds how
often calls start, not how long one runs, so this is the setting that protects the database.
Watching the pool
A pooled server logs a POOL-STATS line every 15 seconds, which the Runtime page reads. The same
numbers are Prometheus gauges under mcpdbwizard_mcp_pool_* if you have
metrics on. Server busy: all database connections are in use means
the pool is at its ceiling: load, not a fault.