Multiple bugs and enhancement requests for DBAPI backend mode

[isc-tdyar]

iris-pgwire Bug & Enhancement Report

Context

During the development of a Next.js application using Drizzle ORM and postgres.js with an InterSystems IRIS Community Edition backend (via iris-pgwire), several critical issues were identified in the dbapi backend mode.

Core Issues Identified

1. DBAPIExecutor API Incompleteness

The DBAPIExecutor is missing several methods expected by the ProtocolHandler when running in dbapi mode:

• test_connection()
• set_session_namespace(namespace, *args, **kwargs)
• close_session(*args, **kwargs)
• has_returning_clause(statement_params)
• get_returning_columns(statement_params)

2. Signature Mismatch in execute_query

The ProtocolHandler attempts to pass session_id as a keyword argument to execute_query. However, the DBAPIExecutor.execute_query implementation only accepts (self, sql, params=None) and does not handle **kwargs, causing a TypeError.

3. Return Format Incompatibility

The bridge's ProtocolHandler.handle_query logic expects execute_query to return a dict containing at least a 'rows' key. The DBAPIExecutor currently returns a raw list of tuples, which causes indexing errors in the handler.

4. Naive vs Aware Datetime Crash

In dbapi_connection_pool.py, the age_seconds calculation fails:

(datetime.datetime.now(UTC) - self.created_at).total_seconds()

self.created_at is initialized as a naive datetime, while now(UTC) is aware, resulting in a TypeError: can't subtract offset-naive and offset-aware datetimes.

5. Metadata Discovery Bottleneck (License Exhaustion)

The handle_describe_message implementation in protocol.py triggers an internal SQL discovery query. For IRIS Community Edition (limited to 1 external connection), this attempt to open a secondary connection for metadata results in an "Unable to allocate a license" error and a silent hang of the bridge.

---

Proposed Enhancements

1. Automatic Placeholder Translation

Implement native support for translating Postgres-style placeholders ($1, $2, etc.) to IRIS-style positional placeholders (?) within the dbapi backend. Currently, this must be handled via middleware or monkey-patches.

2. Strict Single-Connection Mode

Add a configuration flag (e.g., --strict-single-connection) that:

• Prevents the bridge from ever opening more than one internal connection to IRIS.
• Disables or serializes metadata discovery queries to stay within the 1-connection limit of Community Edition licenses.

---

Current Workaround (Monkey-Patch)

We are currently using a base64-encoded bootstrap script in our docker-compose.yml to monkey-patch these fixes at runtime. While this works for local development, a permanent fix in the iris-pgwire core would be greatly preferred.