Bug: PostgresConnector never rolls back after a failed query, poisoning the shared connection for the rest of the process

[P4tk01337]

Bug: PostgresConnector never rolls back after a failed query, poisoning the shared connection for the rest of the process

Summary

wren/connector/postgres.py opens a single, long-lived psycopg connection per PostgresConnector instance and never calls .rollback() after a failed query. Since WrenEngine._get_connector() caches this connector for the engine's lifetime, and ServeContext.engine in the MCP server is a single instance shared across the whole server process, one failing query from any client leaves the shared Postgres connection in an aborted-transaction state for every subsequent query from every client, until the process is restarted.

Version

wrenai[postgres]==0.13.2 (latest on PyPI at time of filing)

Where

• wren/connector/postgres.py — PostgresConnector.__init__ creates one psycopg.connect(...) and stores it as self.connection. query() and dry_run() catch exceptions and re-raise as WrenError, but never call self.connection.rollback().
• wren/engine.py — _get_connector() caches the connector: if self._connector is None: self._connector = get_connector(...), reused for the lifetime of the WrenEngine instance.
• wren/mcp_server.py — ServeContext is documented in-code as "Shared state captured once at startup and used by every tool handler", i.e. one Engine/connector for the whole MCP server process.

Steps to reproduce

1. Start wren serve mcp against a Postgres data source.
2. Call run_sql (or dry_run) with SQL that fails at execution time on Postgres itself (not at Wren's SQL-planning stage), e.g. a query referencing a column that doesn't exist:

   SELECT nonexistent_column FROM some_table

   This returns the expected error.
3. Immediately call run_sql again with a completely valid query, e.g.:

   SELECT COUNT(*) FROM some_table

Expected

Step 3 succeeds normally.

Actual

Step 3 fails with:

[GENERIC_USER_ERROR] current transaction is aborted, commands ignored until end of transaction block

Every subsequent query on that server process fails the same way — from any client, any session — until the container/process is restarted.

Why this matters

Because the connector/engine is shared per-process (not per-request, per-session, or pooled), this isn't a caller-side mistake to recover from — a single bad query from one MCP client degrades the server for every other concurrent client.

Suggested fix

Roll back on failure before re-raising, e.g. in PostgresConnector.query() / dry_run():

except Exception as e:
    try:
        self.connection.rollback()
    except Exception:
        pass
    raise WrenError(...) from e

A more robust fix would move to short-lived connections or an actual connection pool (e.g. psycopg_pool) with reset-on-return semantics, so a single connector instance doesn't hold one Postgres session's transaction state indefinitely.

Environment

• Reproduced via wren serve mcp --transport http, connecting with an MCP client (mcp Python SDK, streamablehttp_client / ClientSession).
• Postgres backend (local dev instance).

[P4tk01337]

Test trought Claude Code:

❯ Run this SQL exactly:
SELECT definitely_missing_column FROM bank_transactions;

Called wren

That query failed as expected — definitely_missing_column doesn't exist on bank_transactions. The error came back as:

[GENERIC_USER_ERROR] current transaction is aborted, commands ignored until end of transaction block phase=SQL_EXECUTION

That's a Postgres-style "transaction aborted" message rather than a clean "column does not exist" — it suggests the connection is a shared/pooled session that's now sitting in a broken transaction state (likely left over from the invalid query, and other queries on this same connection may fail until it resets). Let me know if you want me to retry a valid query to confirm the session has recovered.

✻ Cooked for 11s

❯ Run this SQL exactly:
SELECT COUNT(*) AS count FROM bank_transactions;Called wrenConfirmed — the session is stuck. This valid, simplrror:[GENERIC_USER_ERROR] current transaction is abortedof transaction block phase=SQL_EXECUTIONThis means the earlier failed query left the underln aborted-transaction state, and every subsequentquery on that same connection will fail (even valid a ROLLBACK or the connection is reset. That resetisn't something I can trigger via run_sql — it need, the Wren service recycling/resetting the pooledconnection).I'd flag this as a bug worth reporting on the Wren query shouldn't be able to poison the whole sessionfor subsequent queries. Let me know if you'd like muery_cube) to see if they're on a separate connection
and still work.

[P4tk01337]

Confirmed workaround

Tested this on our end and it resolves the issue: setting autocommit=True
on the connection avoids the poisoned-transaction state entirely. Since
this connector is read-only (run_sql / dry_run / query_cube are all
readOnlyHint=True), there's no multi-statement transaction to preserve,
so autocommit is safe here.

We're patching it at Docker build time (patching the installed
wren/connector/postgres.py after pip install) until this lands
upstream:

import wren.connector.postgres as m
from pathlib import Path

path = Path(m.__file__)
text = path.read_text()

old = "        self._closed = False"
new = "        self.connection.autocommit = True\n        self._closed = False"

if old not in text:
    raise RuntimeError("Could not patch Wren Postgres connector: anchor line not found")
text = text.replace(old, new, 1)
path.write_text(text)

Verified with:

1. SELECT nonexistent_column FROM some_table → fails as expected
2. SELECT COUNT(*) FROM some_table immediately after → now succeeds
   (previously failed with current transaction is aborted, commands ignored until end of transaction block)

[nirabo]

Reproduced the same mechanism independently; confirming your read and adding
three observations that narrow where the fix has to live.

NOTE: AGENT GENERATED FEEDBACK!

1. The poison is confined to the SQL_EXECUTION phase. Planning-phase
failures leave the connection healthy; only execution-stage errors abort the
transaction. Step-isolated with a fresh MCP session per probe:

SELECT 1                        -> ok
SELECT * FROM no_such_table     -> [INVALID_SQL]  phase=SQL_PLANNING, connection healthy
SELECT 2                        -> ok
SELECT 1/0                      -> GENERIC_USER_ERROR  phase=SQL_EXECUTION
SELECT 3                        -> "current transaction is aborted"  (poisoned)

2. The defensive tool is a vector too. dry_run executes on the backend
and poisons exactly like run_sql; only dry_plan (pure MDL expansion, no
connection) is safe. So the recommended "validate, then execute" agent pattern
kills the server from the validation step, before any real query runs.

3. The backend state matches, and killing it doesn't help. The held
connection sits in pg_stat_activity as idle in transaction (aborted);
terminating the backend does not heal the connector — it then returns the connection is closed indefinitely. A process restart is the only recovery.

Your autocommit=True workaround is consistent with the mechanism (the
connector is read-only, so there is no multi-statement transaction to
preserve). On the fix side: rollback-before-re-raise is the minimal change —
the transaction is already aborted when the failure surfaces, so the fix is
simply not to reuse it; a pool with reset-on-return (your psycopg_pool
suggestion) is the robust version. Both are small.