world-postgres: stream readers can stall after LISTEN disconnects or missed NOTIFY event

[Pom4H]

Bug

@workflow/world-postgres currently relies on PostgreSQL LISTEN/NOTIFY for live stream chunk delivery.

This is fragile: NOTIFY is only a wake-up signal for currently connected listeners, not a durable backlog. If the dedicated LISTEN workflow_event_chunk client disconnects, or if a notification is missed during reconnect, chunks can still be written to the streams table successfully while active readers stop receiving live updates indefinitely.

In other words: the streams table is the source of truth, and LISTEN/NOTIFY should only be used to wake readers up to re-query chunks newer than their last delivered chunk_id.

Symptoms

In production, after the dedicated LISTEN client is dropped:

• writeToStream(...) continues inserting chunk rows successfully.
• pg_notify(...) continues executing successfully.
• readFromStream(...) readers may receive the initial query batch, then never receive subsequent chunks.
• Restarting the pod restores delivery until the next LISTEN disconnect.

This silently halts live in-process delivery in the affected process, while persisted stream rows remain intact.

Proposed fix

This needs two layers:

1. Make listenChannel resilient:

   • attach error and end handlers to the dedicated pg.Client
   • reconnect with bounded exponential backoff
   • re-run LISTEN workflow_event_chunk after reconnect
   • stop reconnect attempts on close()

2. Make readFromStream resilient to missed notifications:

   • keep a per-reader lastChunkId
   • load initial chunks from the streams table
   • on notification, query streams WHERE chunk_id > lastChunkId
   • periodically run the same query as a polling fallback
   • dedupe/order by chunk_id
   • stop polling on EOF, cancel, or controller close

This makes world-postgres stream delivery durable even when the LISTEN connection is interrupted.

Relation to other work

This is compatible with #1847, but it is a lower-level world-postgres reliability issue. Core-level stream reconnect cannot recover notifications that PostgreSQL never delivered to a disconnected LISTEN client. The Postgres world still needs to treat the table as the durable source of truth.

[Pom4H]

Update: I have a small reference implementation for this failure mode here:

Pom4H#2

The patch keeps LISTEN/NOTIFY as the fast path, but makes stream delivery resilient to a dropped dedicated pg.Client and to notifications missed during the reconnect gap.

What it changes:

1. listenChannel reconnects with bounded exponential backoff (250ms → 30s cap). The initial connection still fails fast; later reconnects are best-effort and logged.
2. streams.get adds a periodic polling safety net: SELECT ... WHERE chunk_id > lastChunkId, deduped by the existing chunk ordering guard. This is needed because PostgreSQL notifications emitted while the LISTEN client is disconnected are not recoverable from PostgreSQL itself.
3. The polling interval is configurable via PostgresWorldConfig.streamPollIntervalMs, defaults to 5000ms, and can be set to 0 to disable polling in controlled tests.

The PR also includes integration coverage against testcontainers Postgres:

• polling fallback delivers chunks inserted without NOTIFY;
• reader still receives chunks after the LISTEN backend is killed with pg_terminate_backend;
• low-level listenChannel reconnects and receives notifications after its backend is killed;
• startIndex skip remains idempotent across polling ticks.

Local test plan:

• pnpm exec tsc --noEmit
• pnpm exec biome check
• pnpm exec vitest run test/streamer.test.ts

If this direction looks acceptable, I can open the same patch as an upstream PR against vercel/workflow.