MCPDBWizard

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 catalogMCPDBWizard
A generated server, running your toolsThe 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:

KeyMeaning
DAO_POOL_MAX_SIZECeiling. Also your effective concurrency limit.
DAO_POOL_MIN_IDLEHow many stay connected when idle.
DAO_POOL_MAX_WAIT_MSHow long a call waits for a free factory before “Server busy”.
DAO_POOL_IDLE_TIMEOUT_MSHow long an idle factory survives before it is closed.
DAO_POOL_ON_RETURNCOMMIT 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.