Driver Cancellation and Context Deadlines in Go SQL

Every Context method in database/sql makes a promise: if the context ends, the call stops. It is simple to say and hides several different mechanisms: the client has to stop waiting, the connection has to be left in a usable state, and the server has to stop working on a query nobody will read. This article runs the common cases against PostgreSQL 18.6 with pgx v5.11.0 on Go 1.25.6, and records what each one does to the call, the connection, the server and the transaction. The results are specific to this driver. The contract is general.

It belongs to the database cornerstone and deepens query timeouts. The pgx side is covered in pgx context cancellation.

The lab

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	_ "github.com/jackc/pgx/v5/stdlib"
)

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

func backendsRunning(admin *sql.DB, like string) (n int) {
	_ = admin.QueryRow("select count(*) from pg_stat_activity where application_name = 'cancel631' and query like $1 and state = 'active'", like).Scan(&n)
	return
}

func main() {
	ctx := context.Background()
	admin, _ := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	defer admin.Close()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(2)

	// 1. A client deadline shorter than the query.
	var pidBefore int
	_ = db.QueryRowContext(ctx, "select pg_backend_pid()").Scan(&pidBefore)
	short, cancel := context.WithTimeout(ctx, 200*time.Millisecond)
	start := time.Now()
	var n int
	err = db.QueryRowContext(short, "select 1 from pg_sleep(5) /* sleeper631 */").Scan(&n)
	took := time.Since(start).Round(10 * time.Millisecond)
	cancel()
	time.Sleep(100 * time.Millisecond)
	var pidAfter int
	_ = db.QueryRowContext(ctx, "select pg_backend_pid()").Scan(&pidAfter)
	fmt.Printf("1. deadline 200ms on a 5s query: returned after %v with %q\n", took, err)
	fmt.Println("   deadline error:", errors.Is(err, context.DeadlineExceeded), "| still running on the server:", backendsRunning(admin, "%sleeper631%"))
	fmt.Println("   next call used the same backend session:", pidBefore == pidAfter)

	// 2. The server's own statement_timeout gives a different error.
	conn, _ := db.Conn(ctx)
	_, _ = conn.ExecContext(ctx, "set statement_timeout = '150ms'")
	err = conn.QueryRowContext(ctx, "select 1 from pg_sleep(5)").Scan(&n)
	var pgErr *pgconn.PgError
	fmt.Print("2. statement_timeout = 150ms: ")
	if errors.As(err, &pgErr) {
		fmt.Printf("SQLSTATE %s %q, context error: %v\n", pgErr.Code, pgErr.Message, errors.Is(err, context.DeadlineExceeded))
	}
	_, _ = conn.ExecContext(ctx, "reset statement_timeout")
	_ = conn.Close()

	// 3. Cancelling in the middle of a large result set: about 200MB, so rows arrive while the query runs.
	rctx, rcancel := context.WithCancel(ctx)
	rows, err := db.QueryContext(rctx, "select g, repeat('x', 2000) /* bigscan631 */ from generate_series(1, 100000) g")
	if err != nil {
		log.Fatal(err)
	}
	got := 0
	for rows.Next() {
		var g int
		var pad string
		if err := rows.Scan(&g, &pad); err != nil {
			break
		}
		if got++; got == 1000 {
			rcancel()
		}
	}
	fmt.Printf("3. cancelled after reading 1000 rows of 100,000: read %d, rows.Err() = %v\n", got, rows.Err())
	rows.Close()
	time.Sleep(100 * time.Millisecond)
	fmt.Println("   the big query is still running on the server:", backendsRunning(admin, "%bigscan631%"))

	// 4. A cancelled context rolls a transaction back.
	tctx, tcancel := context.WithCancel(ctx)
	tx, _ := db.BeginTx(tctx, nil)
	_, _ = tx.ExecContext(tctx, "create temp table t631 (id int)")
	tcancel()
	time.Sleep(100 * time.Millisecond)
	_, err = tx.ExecContext(ctx, "insert into t631 values (1)")
	fmt.Println("4. statement on a transaction whose context was cancelled:", err)
	fmt.Println("   Commit returns:", tx.Commit())

	// 5. Cancelling after the work is done changes nothing.
	dctx, dcancel := context.WithCancel(ctx)
	err = db.QueryRowContext(dctx, "select 42").Scan(&n)
	dcancel()
	fmt.Println("5. query finished before the cancel: value", n, "error", err)
	s := db.Stats()
	fmt.Printf("   pool at the end: open=%d inUse=%d idle=%d\n", s.OpenConnections, s.InUse, s.Idle)
}

1. deadline 200ms on a 5s query: returned after 200ms with "timeout: context deadline exceeded"
   deadline error: true | still running on the server: 0
   next call used the same backend session: false
2. statement_timeout = 150ms: SQLSTATE 57014 "canceling statement due to statement timeout", context error: false
3. cancelled after reading 1000 rows of 100,000: read 1028, rows.Err() = context canceled
   the big query is still running on the server: 0
4. statement on a transaction whose context was cancelled: sql: transaction has already been committed or rolled back
   Commit returns: sql: transaction has already been committed or rolled back
5. query finished before the cancel: value 42 error <nil>
   pool at the end: open=1 inUse=0 idle=1

The program uses a pool of at most two connections with a distinctive application_name, so a second connection can ask pg_stat_activity whether a query is still running.

A deadline shorter than the query

A query ran pg_sleep(5) under a context with a 200 millisecond deadline. The call returned after 200ms with the message timeout: context deadline exceeded, and errors.Is(err, context.DeadlineExceeded) was true. The standard way to test for a deadline therefore works, and the message carries a prefix from the driver, so never compare strings.

Two things happened that the return value does not show.

The query stopped on the server. Counting active sessions that were running the sleeping statement gave 0. The work was cancelled, not abandoned to run for five seconds. That is what makes a client timeout a real protection: the database stops burning CPU and releasing locks for a request nobody is waiting on. Not every driver does this; some give up locally and let the statement finish. With pgx the server side work ended.

The connection was replaced. The program asked for its backend process id before and after. They differed: the next call ran on a different backend session. pgx does not trust a connection whose operation was interrupted, because the protocol state is uncertain, so the connection was discarded and the pool opened a fresh one. That has a cost: a burst of timeouts is also a burst of reconnects, each of which costs a TCP handshake, authentication and session setup, and each lost session loses prepared statements and session settings. A service whose queries routinely hit their deadline will see connection churn on top of the errors.

Server side timeouts look different

The second case sets statement_timeout = '150ms' on one connection and runs the same sleep. The error is SQLSTATE 57014, "canceling statement due to statement timeout", from PostgreSQL itself, and errors.Is(err, context.DeadlineExceeded) is false. There was no context error because the client's context was fine. The server ended the statement.

This matters for error handling. A handler that maps context.DeadlineExceeded to a 504 will let a server side timeout fall through as a 500. Check both:

var pgErr *pgconn.PgError
timedOut := errors.Is(err, context.DeadlineExceeded) ||
    (errors.As(err, &pgErr) && pgErr.Code == "57014")

SQLSTATE 57014 is also what a user initiated cancel reports, so it means "cancelled" without saying who cancelled. The lab resets the setting before returning the connection, because a set made on a pooled connection stays with it for the next caller: the first version of this lab did not, and the next case failed with the statement timeout it had left behind.

Cancelling in the middle of a result

The third case reads from a query that returns 100,000 rows of about 2KB, roughly 200MB, so rows arrive while the server is still producing them. After 1,000 rows the program cancels the context. The loop stopped having read 1,028 rows (the driver had a few more buffered), rows.Err() returned context canceled, and the big query was no longer running on the server.

Three lessons. Always check rows.Err() after the loop: a cancelled context ends iteration exactly like running out of rows, and without that check a partial result looks complete. A few rows past the cancel can still arrive from buffers, so do not treat the cancel point as exact. And cancelling is how you stop a runaway export or a client that disconnected: pass the request's context, and a closed browser tab ends the query. The failure of that, a long running query that outlives its request, is a common cause of connection leaks.

Transactions

The fourth case begins a transaction with a context, runs a statement, cancels the context and waits briefly. Then both a statement and Commit returned sql: transaction has already been committed or rolled back. database/sql watches the context passed to BeginTx and rolls the transaction back when it ends. The transaction is finished from the pool's point of view and its connection is released, which is why the later calls fail with the "already done" error and not a network error.

Two consequences. The context passed to BeginTx governs the whole transaction, so do not pass a short lived one and expect to keep using the Tx: use the request context, and use per statement timeouts inside. And a deferred tx.Rollback() after cancellation returns sql.ErrTxDone, which you should ignore, as you do after a successful commit; the pattern is in transactions and deferred rollback.

Cancelling too late

The last case cancels a context after select 42 has returned. The value arrived, there was no error, and the pool at the end showed one connection, idle: open=1 inUse=0 idle=1. Cancelling a finished call changes nothing, which is why defer cancel() is always safe and should be written next to every WithTimeout.

What to do with this

• Pass the request context to every call. It is how a disconnected client or a deadline reaches the database.
• Give calls their own, shorter deadline when the request has several steps, so one slow query cannot spend the whole budget.
• Set statement_timeout on the server (per role or per connection) as a second line of defence for code that forgets a context, and handle 57014.
• Treat a deadline as a connection loss in capacity planning: after a burst of timeouts expect reconnects, and size the pool and the server's connection limit with that in mind.
• Check rows.Err() and the commit error on every path.
• Test it. A test that runs a pg_sleep under a short deadline and asserts on the error, the elapsed time and the server's active count, as this lab does, catches a driver or version change that alters the behaviour.

When the context ends during a call that has already been sent, the client cannot know whether the server finished. A context deadline exceeded on an insert does not say the row was not written, and a retry may insert it twice. Make such writes idempotent, with a unique key and on conflict, before retrying them. The contract is documented in the database/sql package and in the context package, and PostgreSQL's behaviour in its page on statement_timeout.