[BUG] graphile-worker PostgreSQL pool has no error handler — crashes entire backend process on connection termination

[Erasure5959]

Bug Description

Summary

When the PostgreSQL server terminates a connection held by the pg.Pool used for the graphile-worker queue backend (e.g. during a database restart, maintenance, or host reboot), the resulting error event has no registered listener. Since this is an unhandled EventEmitter 'error' event, Node.js's default behavior is to throw and crash the entire process — taking down the whole backend/worker service, not just the affected connection.

Where

packages/backend/src/queue/queue-factory.ts, in the PostgreSQL branch of the queue backend initialization:

this.pgPool = new Pool({
  connectionString: config.databaseUrl,
  max: 10, // Separate pool for queue operations
});
console.log('[QueueSystem] Using graphile-worker (PostgreSQL) backend');

No .on('error', ...) is ever registered on this.pgPool anywhere in the file (confirmed via grep -n "pgPool" queue-factory.ts — it appears only at declaration, creation, the getter, and cleanup). This is inconsistent with the Redis branch of the same function, a few lines above, which does register full event handling via setupRedisEventHandlers():

this.redisConnection.on('error', (err) => {
  console.error('[Redis:queue] Error:', err.message);
});

There's no equivalent setupPgPoolEventHandlers() (or similar) for the Postgres path.

For comparison, the other two Postgres connections elsewhere in the codebase both handle this correctly:

• packages/backend/src/database/connection.ts — the main app pool registers pool.on('error', ...) and logs cleanly ([Database Pool] Unexpected error on idle client: ...).
• packages/backend/src/modules/streaming/notification-manager.ts — the dedicated LISTEN/NOTIFY client also registers .on('error', ...) before connecting, explicitly commented "Setup error handler before connecting".

So this appears to be one specific gap, not a systemic pattern — the graphile-worker pool is the outlier.

Reproduction

1. Run the backend/worker in PostgreSQL-queue mode (i.e. without redisUrl configured, so queue-factory.ts takes the graphile-worker branch).
2. Restart or otherwise terminate connections on the PostgreSQL server while the worker has an active/idle connection in this pool (simplest repro: reboot the host, or SELECT pg_terminate_backend(pid) against one of the worker's connections; also reproducible any time the Postgres container itself restarts, e.g. via podman restart, an image update, or OOM).
3. Backend/worker process crashes with an unhandled exception (see stack trace below) and exits.

We hit this organically on a routine host reboot in production.

Observed crash

Preceding log lines:

[Server] Shutting down gracefully...
[Database Pool] Unexpected error on idle client: terminating connection due to administrator command
[Database Pool] Unexpected error on idle client: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command
[WorkerUtils] ERROR: PostgreSQL active client generated error: terminating connection due to administrator command

(Note: WorkerUtils doesn't appear anywhere in the repo's own .ts source — this prefix is coming from inside graphile-worker itself, wrapping/logging the error on the pool it was handed. It doesn't change the underlying issue: the app-supplied pool has no listener of its own.)

Then the actual unhandled crash:

node:events:502
      throw er; // Unhandled 'error' event
      ^

error: terminating connection due to administrator command
    at Parser.parseErrorMessage (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/parser.js:285:98)
    at Parser.handlePacket (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/parser.js:122:29)
    at Parser.parse (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/parser.js:35:38)
    at Socket.<anonymous> (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
Emitted 'error' event on BoundPool instance at:
    at Client.idleListener (/app/node_modules/.pnpm/pg-pool@3.10.1_pg@8.16.3/node_modules/pg-pool/index.js:62:10)
    at Client.emit (node:events:536:35)
    at Client._handleErrorEvent (/app/node_modules/.pnpm/pg@8.16.3/node_modules/pg/lib/client.js:350:10)
    at Client._handleErrorMessage (/app/node_modules/.pnpm/pg@8.16.3/node_modules/pg/lib/client.js:361:12)
    at Connection.emit (node:events:524:28)
    at /app/node_modules/.pnpm/pg@8.16.3/node_modules/pg/lib/connection.js:116:12
    at Parser.parse (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/parser.js:36:17)
    at Socket.<anonymous> (/app/node_modules/.pnpm/pg-protocol@1.10.3/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12) {
  length: 116,
  severity: 'FATAL',
  code: '57P01',
  file: 'postgres.c',
  line: '3354',
  routine: 'ProcessInterrupts'
}

Node.js v20.20.2

Process then exits and gets restarted by the process supervisor (Restart=always in our systemd unit); confirmed independently via systemd's own unit-failure record: logtide-backend.service: Failed with result 'exit-code'.

Downstream side-effect (how we found this)

Because the crash+restart console output gets self-ingested by LogTide's own log pipeline (it monitors its own container stdout), the full stack trace and restart banner end up batched into a subsequent bulk INSERT into the logs table. When that batch happens to be large, PostgreSQL's slow-query logging (which includes bound parameters) produces an oversized single log line — large enough that our local rsyslog (relaying container journal output to /var/log/messages) hits its message too long limit and truncates it. That symptom is what led us to trace this back to the actual crash.

Environment

• PostgreSQL: 18.4 (TimescaleDB image docker.io/timescale/timescaledb:latest-pg18)
• Backend image: docker.io/logtide/backend:latest
• Node.js: v20.20.2
• pg: 8.16.3, pg-pool: 3.10.1 (via pnpm)
• Deployment: rootless Podman via systemd --user Quadlet units, backend/worker/postgres as separate containers in the same pod
• Queue backend: PostgreSQL (graphile-worker), not Redis

Suggested fix

Register an error handler on this.pgPool immediately after construction in queue-factory.ts, mirroring the existing Redis handling and the pattern already used correctly in connection.ts and notification-manager.ts — e.g.:

this.pgPool = new Pool({
  connectionString: config.databaseUrl,
  max: 10,
});
this.pgPool.on('error', (err) => {
  console.error('[QueueSystem] Unexpected error on idle PG queue client:', err.message);
});

This should prevent a routine database restart/maintenance event from taking down the entire backend/worker process.

---

Environment

• LogTide Version: 1.2.0
• Deployment Type: Self-hosted
• OS: AlmaLinux 10
• Browser (if applicable): Edge 151
• Docker Version (if self-hosted): Running in Podman

Logs/Screenshots

Paste relevant logs here

Additional Context

I run Logtide in rootless Podman Quadlets. Not saying it is related, but differs from the "standard" deployment model.

Contribution

• I would like to work on fixing this bug