pg-cancel

Aborting a promise does not stop a Postgres query.

const controller = new AbortController();
const query = pool.query('SELECT pg_sleep(30)');
controller.abort();          // the caller gives up
// ...the backend is still running it, still holding a pooled connection

That is not a bug in pg. There is no way to tell a busy connection to stop using that connection — a backend executing a query does not read new messages until it finishes. Postgres has a separate mechanism for this, and almost nothing in Node uses it.

Measured, not asserted:

before             : 0 slow queries running
while running      : 1
caller gives up    : (promise abandoned — all a timeout usually does)
4s after giving up : 1   <-- still running
after a real cancel: 0

That is the first test in this repository. If it ever fails because pg started cancelling on its own, this package is unnecessary and its README is wrong.

Use

npm install pg-cancel

import { Pool } from 'pg';
import { cancellable } from 'pg-cancel';

const db = cancellable(new Pool());

// Stops the backend when the HTTP request goes away.
await db.query('SELECT ...', [id], { signal: req.signal });

// Or on a deadline.
await db.query('SELECT ...', [id], { timeoutMs: 2000 });

Cancelled queries reject with QueryCancelledError, which carries SQLSTATE 57014 and says which of the two reasons it was.

Why a separate socket, not pg_cancel_backend

The obvious implementation is SELECT pg_cancel_backend($pid) from the pool. It fails exactly when you need it.

The reason you are cancelling is usually that slow queries have taken every connection. Asking that pool for one more connection — to cancel the queries holding them — is asking it for the thing it has run out of. Your cancel waits behind the queries it was supposed to cancel.

This sends the real cancel request the way libpq does: a fresh TCP socket, no authentication, no pooled connection, no privileges. The backend's secret key is the authorisation.

Int32  16
Int32  80877102        cancel request
Int32  processID
Int32  secretKey

There is a test for precisely that: a pool with max: 1, that one connection busy, idleCount asserted to be zero, and the cancel still lands.

It sets statement_timeout too, and that is not redundant

The two do different jobs and neither replaces the other.

| | stops a runaway query | needs your process alive | precise | |---|---|---|---| | client-side cancel | yes | yes | to the millisecond | | statement_timeout | yes | no | to the server's granularity |

If the container is killed mid-query, nothing sends a cancel and the backend runs to completion. statement_timeout survives that because the server enforces it. So timeoutMs sets both by default, and resets the session setting afterwards so the next borrower of that connection does not inherit it.

Pass statementTimeoutMs: false if you are managing it yourself.

A cancelled connection is not a broken one

The pool client is released, not destroyed. A backend whose query was cancelled answered with an error and is ready for the next statement — destroying it would turn every timeout into a reconnect, which costs more than the query did. Asserted in the tests.

Details that are easy to get wrong

The secret key is signed. It is 32 random bits, so roughly half of all connections have the high bit set. Writing it with writeUInt32BE throws for those, which makes half your connections silently uncancellable and the failure look intermittent. This writes it signed.

TLS. Postgres does not speak TLS on connect — it is asked to upgrade. If the original connection used TLS, the cancel socket must do the same handshake, or the server drops the packet and the query keeps running with nothing reported. Handled, including Unix sockets.

Racing. A cancel that arrives after the query finished does nothing, and that is normal rather than an error. A cancel that cannot open a socket is swallowed: the query is left to finish, which is what would have happened anyway, and failing the caller with a networking error about a socket they never asked for would be worse than the problem.

What it does not do

It does not roll anything back. Cancelling a statement inside a transaction leaves the transaction open and in a failed state. That is Postgres behaviour and your ROLLBACK is still your job.

It cannot cancel work that already finished. A backend that has computed its result and is writing rows will not notice. Cancellation is a request.

It is not a query timeout for everything. pg already has connectionTimeoutMillis and query_timeout; query_timeout rejects the promise and leaves the backend running, which is the thing this exists to fix, and the two can be used together.

Tests

npm install
npm run pg:up
npm test          # 14 tests against a real Postgres
npm run pg:down

Every test checks pg_stat_activity from a second connection rather than trusting the promise. A rejected promise and a stopped query are different events, and this package only exists because they are.

Built with Claude

Claude wrote most of this code. The design is mine, and the reason it exists is a measurement: the probe at the top of this README was run before any of it was written, because a library for a problem nobody has is worse than no library.

Licence

MIT.