rc.11 still hangs on direct (non-pooler) connections for tables with enum columns; 4 stuck workers then block ping/connect for every connection

Summary

On a direct PostgreSQL connection (no pooler, Pooler Mode not set), plugin v1.0.0-rc.11 hangs when a table has a custom enum column and the result is larger than a few KB. This is the same hang as #160/#163, but the rc.11 fix (#164) only runs when Pooler Mode = "Transaction". Direct connections still use the old path.

There is a second effect. The plugin process has WORKER_POOL_SIZE = 4, and the host holds each hung call until the 120 s plugin-call timeout. After 4 such calls, every other RPC waits behind them, for every connection, not just the one with the hung query. That includes ping, connect and metadata. The user sees:

A database connection was lost: Ping timed out
new connections stuck on "loading", even to a different database

Switching the same connections back to the built-in postgres driver makes everything fast again.

Environment

Tabularis 0.29.0 (macOS, Apple Silicon), PostgreSQL plugin 1.0.0-rc.11
PostgreSQL 17.11 in Docker (direct TCP), and AWS RDS PostgreSQL reached through an SSM port-forward (direct, no pooler)
Pooler Mode not set; result page size 500

Minimal reproduction

CREATE TYPE mood AS ENUM ('happy', 'sad');
CREATE TABLE repro_enum_wide  AS SELECT g AS id, 'happy'::mood AS m, repeat('x', 500) AS pad FROM generate_series(1, 600) g;
CREATE TABLE repro_plain_wide AS SELECT g AS id, 'happy'::text AS m, repeat('x', 500) AS pad FROM generate_series(1, 600) g;

I drove the installed plugin binary directly over JSON-RPC (execute_query, page: 1, a fresh plugin process per call, 20 s client-side timeout):

query	limit	result
`SELECT * FROM repro_plain_wide`	500	OK, 0.02 s
`SELECT * FROM repro_enum_wide`	5	OK, 0.02 s
`SELECT * FROM repro_enum_wide`	50	no response after 20 s
`SELECT * FROM repro_enum_wide`	500	no response after 20 s
`SELECT id, m::text AS m, pad FROM repro_enum_wide`	500	OK, 0.03 s

The same enum table without the pad column (narrow rows, about 10 KB for 500 rows) returns normally at limit 500. The hang seems to need a response bigger than a few KB.

Worker starvation. In a single plugin process I sent 4 of the hanging execute_query calls. Then I sent a ping and an execute_query of SELECT 1 to a different database. Neither got a response within 20 s.

What I see in the rc.11 source

handlers/query.rs, exec_query, direct path: runs pg_client.query_typed(&final_query, &[]), the unnamed protocol from fix: use unnamed-statement protocol for pooler (PgBouncer) safety #149.
- For a non-builtin result column type, tokio-postgres resolves the type inside query_typed after RowDescription (get_type, then the internal typeinfo_statement). That lookup is pipelined behind the portal that is still streaming rows.
- My guess is that the client stops reading the socket while it waits for the typeinfo reply, once the earlier response is large enough. That would deadlock with no pooler at all, and it would explain the size threshold above. I have not confirmed this at the tokio-postgres level; please correct me if the mechanism is different.
ConnectionParams::is_transaction_pooler() gates the pinned pre-resolve path from fix: pin backend for transaction-pooler result queries on non-builtin column types (#160, #163) #164, so direct connections never get it.

Suggestions

Pre-resolve result column types on direct connections too: either the fix: pin backend for transaction-pooler result queries on non-builtin column types (#160, #163) #164 path, or a named prepare before execute like rc.8 did.
Give ping its own lane or worker, so stuck queries can't starve health checks and connects.

Workarounds

Use the built-in postgres driver.
Stay on plugin rc.8.
In ad-hoc SQL, cast enum columns to text.

Reported with the help of Claude Code (an AI coding assistant). The reproduction above was run locally against the installed rc.11 plugin binary.

Confirming this on a second setup, and the worker starvation makes it close to unusable for us.

Environment

Tabularis 0.29.0 (macOS), PostgreSQL plugin 1.0.0-rc.11
Direct connection to a self-hosted PostgreSQL (no pooler, Pooler Mode not set)

What happens

The table has 3 custom enum columns (e.g. type, fiscalStatus, matchSource) plus two wide jsonb columns, about 34k rows.
SELECT on it with a WHERE and LIMIT 100..300 never returns: Plugin call 'execute_query' timed out after 120s.
Since each hung call holds a worker for the full 120 s, after a few retries every other query on the same connection times out as well, including SELECT count(*) on the same table, which had returned fine a few minutes earlier. This matches the WORKER_POOL_SIZE = 4 starvation described above.
Retrying only makes it worse. Even a query that casts the enum columns to text and does not select the wide columns times out once the workers are stuck, so the workaround from the issue only helps on a fresh plugin process.
Only quitting Tabularis completely frees the workers.

Two suggestions on top of the ones in the issue:

Cancel the server-side statement and release the worker when the host call-timeout fires (related to Cancel the server-side statement when a host call-timeout notification arrives #126), so a hung query does not keep a worker for the whole timeout.
Let ping and cheap metadata calls bypass the worker pool.

Stay on plugin rc.8.

I downgraded to rc.8 to work

@aesslinger

@aesslinger can you check?

looking

@ductrihn

@ductrihn @albertorizzi @debba

First — thank you, sincerely, for the detail in this report (and @albertorizzi for the second-environment confirmation). The reproduction matrix (the size threshold, the m::text workaround, the worker-starvation observation, and the source-level mechanism guess) was exact and made confirming this fast. Reports of this quality are genuinely valuable.

What the research found

We reproduced the hang exactly as described, against a live direct Postgres (no pooler, Pooler Mode unset) driving the rc.11 binary over JSON-RPC:

query	limit	result
plain `text` table, wide	500	OK, <0.01s
enum table, wide	5	OK (small response fits the buffer)
enum table, wide	50	hangs past 25s
enum cast to `text`	500	OK

You were right that the rc.11 fix (#164) only runs when Pooler Mode = "Transaction", so direct connections still hit the old path. And you were right about the mechanism: tokio-postgres's query_typed resolves non-builtin column types (get_type → the internal typeinfo_statement) inside the RowDescription loop, mid-stream, over the same channel that's delivering the query's DataRows. The typeinfo reply is queued behind the rows, but query_typed won't read the rows until get_type returns → deadlock once the row data fills the channel buffer (hence the size threshold). This is not pooler-specific — it deadlocks on any connection once the response is large enough. Our #164 framing ("Problem B = pooler reassignment") was incomplete; the reassignment orphan (42P05 on PgBouncer) is a separate failure the pooler adds on top, but the hang is the interleave.

@albertorizzi — your worker-starvation account matches the code exactly: WORKER_POOL_SIZE = 4 with a shared queue, so 4 stuck calls exhaust all lanes and a ping/metadata call (even to a different database) waits behind them. And your note that the m::text workaround stops helping once the workers are stuck (because it's a worker-layer starvation, not a per-query issue) is correct — that's why only quitting Tabularis frees them.

The plan

A fix is coming. We've confirmed the direction empirically (not just by reading source): pre-resolve the result column types via a prepare(query) on the shared cached client before the query_typed data fetch, for direct connections. prepare runs Parse+Describe+Sync with no Execute/no DataRows, so its type resolution has an empty channel and completes cleanly, warming tokio-postgres's client-side type cache. The subsequent query_typed then hits that cache and never fires typeinfo_statement mid-drain → no deadlock, regardless of result size. This is exactly what rc.8 did, which is why rc.8 worked.

Crucially this keeps the #164 pooler path intact (poolers keep the pinned one-shot transaction, since a plain prepare would collide across backend reassignment — the thing that killed the earlier pre-warm attempt). And it preserves connection-pool reuse for direct connections: measured cost is ~39ms/call vs ~37ms baseline, with effectively zero overhead on builtin column types (the common case).

The plan covers four things:

The fix — direct-connection prepare pre-resolve in the shared exec_query_on_client (covers single, batch, and explain queries; the pooler path is untouched).
A regression test — the exact wide-enum repro (600 rows × 500-byte pad, enum column, limit 50, assert <30s). The existing enum tests use a single tiny row and pass on the broken path, which is the coverage gap that let this ship — we're closing that.
Worker starvation / ping isolation — @albertorizzi's suggestions here are good. Fixing the hang removes the trigger, but the structural gap (a slow query shouldn't starve health checks regardless of cause) is real. We'll open a separate issue for that so it gets its own design rather than being bolted onto this fix.
Correcting the fix: pin backend for transaction-pooler result queries on non-builtin column types (#160, #163) #164 framing in our code comments and the Bug: Regression in plugin v1.0.0-rc.10 table hangs while opening but works in v1.0.0-rc.8 #160/Query timeout followed by health-check disconnect after repeated cancellations #163 responses — the root cause is the tokio-postgres interleave, not pooler reassignment, and the next person reading our comments shouldn't be misled the way we were.

We'll update this issue as the fix lands. Thanks again for the thorough report — it's the reason this got pinned down quickly.

fix: bump ctutils 0.4.2 -> 0.4.5 to clear RUSTSEC-2026-0385

docs: fix pre-resolve comment imprecision (Statement lifetime, rc.8 c…

test: add batch-path wide-enum regression test (#166)

fix: pre-resolve column types on direct connections to stop enum-tabl…

@albertorizzi

The direct-connection hang is fixed and merged in #168 (will ship in the next release). The fix pre-resolves result column types via prepare on the shared cached client for direct connections, so query_typed's mid-stream get_type hits the cache instead of firing typeinfo_statement mid-drain — no deadlock, regardless of result size.

Verified locally against a direct Postgres (no pooler): the wide enum table at limit 50 (the hanging case) now returns in 0.34s, where rc.11 hung past 25s. A live regression test (wide_enum_table_returns_rows_on_direct_connection, 600 rows × 500-byte pad, limit 50, assert <30s) is added so this can't silently regress. @albertorizzi's batch-path case is also covered by a second live test.

Three follow-up issues were opened from the pre-handoff review of #168 — these are not regressions from this fix, they're pre-existing gaps the review surfaced:

Batch/session path on a transaction pooler still deadlocks on non-builtin column types #169 — the batch/session path on a transaction pooler (interactive transactions with session_id) still has the same hang on non-builtin column types. fix: pin backend for transaction-pooler result queries on non-builtin column types (#160, #163) #164's pinned path only covered non-session single queries; the batch/session path was never covered. This is the same hang class on a different path.
Direct-path pre-resolve .ok() swallow can reintroduce the hang; no plugin-level timeout (asymmetry with pooler path) #170 — the direct path has no plugin-level timeout (unlike the pooler path's 90s bound), so a rare prepare failure for a non-builtin type could still hang to the host's opaque 120s. Strictly better than rc.11 (which hangs always), but the asymmetry is tracked.
Stale composite type cache after ALTER TYPE on a recycled pooled connection #171 — a stale composite type cache after ALTER TYPE on a recycled connection (a pre-existing tokio-postgres cache-invalidation gap widened by the per-query warming).

If you still see the direct-connection hang after the next release, please open a new issue — but the wide-enum repro from this report now returns cleanly. Thanks again for the thorough report.

fix: pre-resolve column types on batch/session pooler path (#169) (#172)

fix: address pre-handoff-review findings on #173 classifier and routing

fix: keep bare [SET/CREATE TEMP, SELECT] batch on the shared client o…

fix: route ping/connect/cancel to dedicated lanes so stuck queries do…

chore: release 1.0.0-rc.12 (#178)

v1.0.0-rc.12 is out: https://github.com/TabularisDB/tabularis-postgresql-plugin/releases/tag/v1.0.0-rc.12

It should resolve both things reported here:

The direct-connection enum/custom-type hang itself, for result sets large enough to fill the response channel (fix: pre-resolve column types on direct connections to stop enum-table hang (#166) #168) — the fix you suggested: pre-resolve result column types via prepare before the data fetch, mirroring the pooler path from fix: pin backend for transaction-pooler result queries on non-builtin column types (#160, #163) #164.
The worker-starvation effect, where 4 stuck calls blocked ping/connect/metadata for every connection (fix: route ping/connect/cancel to dedicated lanes so stuck queries don't starve them (#167) #176) — cancel, ping/test_connection/initialize/shutdown, and general queries now run on separate lanes, so a few hung queries can no longer starve health checks or new connections.

Could you retest against rc.12 with your repro_enum_wide case (and the 4-hung-workers + ping scenario) and let us know if it's resolved? If so we can close this out.

Retested on v1.0.0-rc.12 (macOS, Tabularis 0.29.0), direct connection with no pooler, PostgreSQL on a self-hosted server.

The table that used to hang is companies. It has a custom enum column (relation, CompanyRelation) and a large jsonb column (raw_data).

SELECT * FROM companies with limit 500 returns 5,448 rows (about 325 KB) immediately, with no hang.
An aggregation over the enum column (SELECT relation, count(*) ... GROUP BY relation) returns immediately.
describe_table also works.

So the direct-connection enum hang (#168) looks fixed for us.

I didn't retest the exact repro_enum_wide table, and I didn't retest the 4-hung-workers + ping scenario (#176). That one needs a hung query, which I can't produce now that the hang is fixed.

@aesslinger

Retested on v1.0.0-rc.12 (macOS arm64, Tabularis 0.29.0), direct connection, no pooler, PostgreSQL 17 — driving the plugin binary over JSON-RPC, same harness run against rc.11 as a control.

repro_enum_wide (600 rows × 500-byte pad, enum column):

query	rc.11	rc.12
limit 5	OK	OK
limit 50 / 500 / page 2	hangs	OK (1–17 ms)
`m::text`, limit 500	OK	OK
composite + domain + range table, limit 500	hangs	OK (6 ms)
`execute_query_batch` (wide enum + `GROUP BY` enum)	hangs	OK (3 ms)

Also OK on 6 real enum-heavy tables from our app DB (limit 500, ≤ 19 ms).

4-hung-workers + ping (#176): 4× SELECT pg_sleep(60) to occupy the general lane, then ping → 16 ms, test_connection to a different database → 2 ms. A cancel notification for each sleeper freed all 4 within ~8 ms (57014 canceling statement due to user request), and a query queued behind them ran immediately after.

Both issues look resolved for me — fine to close. Thanks @aesslinger for the quick and thorough fix!

Navigation Menu

Repository navigation

rc.11 still hangs on direct (non-pooler) connections for tables with enum columns; 4 stuck workers then block ping/connect for every connection #166

Summary

Environment

Minimal reproduction

What I see in the rc.11 source

Suggestions

Workarounds

What the research found

The plan

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

rc.11 still hangs on direct (non-pooler) connections for tables with enum columns; 4 stuck workers then block ping/connect for every connection #166

Description

Summary

Environment

Minimal reproduction

What I see in the rc.11 source

Suggestions

Workarounds

Activity

albertorizzi commented on Oct 23, 2026

albertorizzi commented on Oct 23, 2026

debba commented on Oct 23, 2026

aesslinger commented on Oct 23, 2026

aesslinger commented on Oct 23, 2026

What the research found

The plan

aesslinger commented on Oct 23, 2026

aesslinger commented on Oct 23, 2026

albertorizzi commented on Oct 23, 2026

ductrihn commented on Oct 24, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions