pgx Context Cancellation Behavior and What Really Happens

Go programmers rely on context to bound work, and a database call is where an unbounded wait hurts most. What happens when the context ends is specific to the driver: whether the server stops working, what state the connection is left in, and what becomes of a transaction. The standard library's behaviour is measured in driver cancellation; this article does the same for native pgx on Go 1.25.6 and PostgreSQL 18.6, and one result differs in a way that matters. It belongs to the database cornerstone, and the timeouts discussed here are the client side of query timeouts.

What the lab shows

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable&application_name=ctx678"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	cfg, err := pgxpool.ParseConfig(dsn)
	must(err)
	cfg.MaxConns = 2
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	must(err)
	defer pool.Close()
	admin, err := pgxpool.New(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	must(err)
	defer admin.Close()
	running := func(like string) (n int) {
		_ = admin.QueryRow(ctx, "select count(*) from pg_stat_activity where application_name = 'ctx678' and state = 'active' and query like $1", like).Scan(&n)
		return
	}
	pid := func() (p int) {
		_ = pool.QueryRow(ctx, "select pg_backend_pid()").Scan(&p)
		return
	}

	// 1. A deadline during a query.
	before := pid()
	short, cancel := context.WithTimeout(ctx, 200*time.Millisecond)
	start := time.Now()
	var n int
	err = pool.QueryRow(short, "select 1 from pg_sleep(5) /* sleeper678 */").Scan(&n)
	took := time.Since(start).Round(10 * time.Millisecond)
	cancel()
	time.Sleep(150 * time.Millisecond)
	fmt.Printf("1. 200ms deadline on a 5s query: returned after %v with %q\n", took, err)
	fmt.Println("   context.DeadlineExceeded:", errors.Is(err, context.DeadlineExceeded), "| pgconn.Timeout(err):", pgconn.Timeout(err), "| still running on the server:", running("%sleeper678%"))
	fmt.Printf("   the pool replaced the connection: the backend process changed: %v\n", before != pid())

	// 2. A context that is already cancelled: nothing is sent and the connection stays.
	cctx, ccancel := context.WithCancel(ctx)
	ccancel()
	p1 := pid()
	err = pool.QueryRow(cctx, "select 1").Scan(&n)
	p2 := pid()
	fmt.Printf("2. already cancelled: %q, the same backend afterwards: %v, SafeToRetry: %v\n", err, p1 == p2, pgconn.SafeToRetry(err))

	// 3. Cancelling in the middle of a large result.
	rctx, rcancel := context.WithCancel(ctx)
	rows, err := pool.Query(rctx, "select g, repeat('x', 2000) /* bigscan678 */ from generate_series(1, 100000) g")
	must(err)
	got := 0
	for rows.Next() {
		if got++; got == 1000 {
			rcancel()
		}
	}
	rowsErr := rows.Err()
	rows.Close()
	time.Sleep(150 * time.Millisecond)
	fmt.Printf("3. cancelled after 1000 of 100000 rows: read %d, rows.Err() = %v, still running on the server: %d\n", got, rowsErr, running("%bigscan678%"))

	// 4. A transaction whose context ends.
	tctx, tcancel := context.WithTimeout(ctx, 300*time.Millisecond)
	tx, err := pool.Begin(tctx)
	must(err)
	_, err = tx.Exec(tctx, "create temp table t678 (id int)")
	must(err)
	time.Sleep(400 * time.Millisecond)
	_, err = tx.Exec(ctx, "insert into t678 values (1)")
	fmt.Println("4. a statement after the transaction's context ended:", err)
	fmt.Println("   Rollback returns:", tx.Rollback(ctx))
	tcancel()

	// 5. A server side statement_timeout is a PgError, not a context error.
	_, err = pool.Exec(ctx, "set statement_timeout = '150ms'")
	must(err)
	err = pool.QueryRow(ctx, "select 1 from pg_sleep(2)").Scan(&n)
	var pg *pgconn.PgError
	fmt.Printf("5. statement_timeout: PgError %v (code %s), context error %v\n", errors.As(err, &pg), pg.Code, errors.Is(err, context.DeadlineExceeded))
	_, _ = pool.Exec(ctx, "reset statement_timeout")

	// 6. Acquire respects the deadline as well.
	a, _ := pool.Acquire(ctx)
	b, _ := pool.Acquire(ctx)
	wctx, wcancel := context.WithTimeout(ctx, 200*time.Millisecond)
	_, err = pool.Acquire(wctx)
	wcancel()
	a.Release()
	b.Release()
	fmt.Println("6. every connection held, Acquire with a deadline:", err)
	_ = pgx.ErrNoRows
}

1. 200ms deadline on a 5s query: returned after 200ms with "timeout: context deadline exceeded"
   context.DeadlineExceeded: true | pgconn.Timeout(err): true | still running on the server: 0
   the pool replaced the connection: the backend process changed: true
2. already cancelled: "context canceled", the same backend afterwards: true, SafeToRetry: false
3. cancelled after 1000 of 100000 rows: read 2012, rows.Err() = context canceled, still running on the server: 0
4. a statement after the transaction's context ended: <nil>
   Rollback returns: <nil>
5. statement_timeout: PgError true (code 57014), context error false
6. every connection held, Acquire with a deadline: context deadline exceeded

The program runs against a pool with a distinctive application_name, so that a second connection can ask pg_stat_activity whether a query is still running.

A deadline during a query

A query that sleeps five seconds ran under a 200 millisecond deadline. It returned after 200ms with timeout: context deadline exceeded. errors.Is(err, context.DeadlineExceeded) was true, and so was pgconn.Timeout(err), a pgx helper that is true for any timeout whether it came from a context, a socket deadline or the server. Use that in a retry or error mapping layer, since the exact error value can vary with where the deadline struck.

On the server, the count of active sessions running the sleeping statement was 0: the work was cancelled and did not run on for five seconds. pgx stops the query by opening a second connection and sending a cancel request, and by closing the connection that was interrupted. The pool shows it: the backend process id changed afterwards. A connection whose operation was interrupted has unknown protocol state, so pgx does not reuse it; it is destroyed and the pool opens a replacement on demand.

This has a cost to plan for. Deadlines that fire often, because a database is slow or a limit is too tight, cause connection churn: each cancelled call costs a TCP connection, authentication and session setup for its replacement, and loses prepared statements and session settings. A burst of timeouts is therefore also a burst of reconnections, and can push a struggling database further over.

A context that is already cancelled

When the context was cancelled before the call, the result was context canceled, and the connection was untouched: the same backend process served the next call. pgx checks the context before it writes anything, so nothing was sent and nothing needed repairing. That is the cheap case, and it is what happens when a handler's request context ended while it waited in a queue. (pgconn.SafeToRetry returned false for it, since the error is the context's and not a network failure; the statement was not sent, and retrying with a live context is safe in practice.)

Cancelling in the middle of a result

The program started a query for 100,000 rows of about 2KB, roughly 200MB so that rows arrive while the server is still producing them, and cancelled after reading 1,000. The loop read 2,012 rows in total, because rows already buffered were delivered, then stopped: rows.Err() returned context canceled, and the large query was no longer running on the server (count 0). As in the standard library, always check rows.Err() after the loop; CollectRows returns it for you.

A transaction does not end with its context

This is the difference. The program began a transaction with a context that had a 300 millisecond timeout, created a temporary table in it, waited 400 milliseconds, and then ran an insert on the transaction using the ordinary context. The result: <nil>. The insert succeeded, and tx.Rollback(ctx) returned nil too: the transaction was still open and healthy.

In database/sql, the context passed to BeginTx governs the whole transaction: when it ends, the package rolls the transaction back. In pgx native, the context passed to Begin governs only the BEGIN statement itself. A transaction lives until you commit or roll it back, whatever happened to the context it started with. The consequences:

• A transaction held across a request can outlive the request. If the handler's context is cancelled and the code forgets to roll back, the transaction stays open, holding locks and a connection, until something closes it. defer tx.Rollback(ctx) with a context that is not the cancelled one is therefore essential: Rollback(ctx) with an already cancelled context fails to send the rollback, and the connection is closed instead, which does end the transaction, at the cost of the connection.
• Use a context with a deadline for each statement inside the transaction, since the one passed to Begin does not bound them.
• Set idle_in_transaction_session_timeout on the server as a safety net for transactions that are abandoned.

pgx.BeginFunc makes this safer, since it rolls back on any error the function returns, including one caused by a cancelled context in a statement. Pass the request context to the statements inside and let the helper own the rollback.

Server side timeouts

The fifth block sets statement_timeout = '150ms' and runs a two second sleep. The result was a *pgconn.PgError with code 57014, and errors.Is(err, context.DeadlineExceeded) was false. The server ended the statement; the client's context was fine. Map both in your error handling: a deadline from the context and 57014 from the server are both "took too long", and they arrive as different types. Note that 57014 is also the code for a user cancel, so it means cancelled without saying by whom. The session setting was reset afterwards: on a pooled connection, a SET stays with the connection, as connection lifecycle shows.

Acquiring a connection

The last block holds every connection of a pool of two and calls Acquire with a deadline: it returned context deadline exceeded. The same deadline bounds the wait for a free connection as well as the query, so a request waiting in an overloaded pool fails at its deadline instead of queueing without limit; see pgxpool settings.

Rules

1. Pass the request context to every call, and give each call or step its own deadline.
2. Use pgconn.Timeout(err) and check for 57014 to recognise timeouts, and errors.Is(err, context.Canceled) for cancellation.
3. Always end a transaction yourself, with BeginFunc or a deferred rollback, and do not rely on the Begin context to do it.
4. Expect a reconnection after each deadline that fires during a query, and watch the pool's NewConnsCount for churn.
5. Check rows.Err(), and treat a cancelled streaming query as an incomplete result.
6. Make writes safe to retry before retrying after a timeout: the outcome of a cancelled insert is unknown.

The behaviour is documented in the pgconn package (see Timeout and SafeToRetry) and the cancellation protocol in the PostgreSQL manual's page on cancelling queries in progress.