All jobs

Add Query Timeouts to a sqlc Project

Yes. An agent sets a statement timeout on the pgx pool behind your sqlc queries, bounds each transaction with a deadline, proves both with pg_sleep, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent starts with how the pool is opened. In a service laid out like techschool/simplebank, main.go calls pgxpool.New(ctx, config.DBSource) with the URL from app.env and nothing else, hands the pool to db.NewStore, and store.go wraps it in SQLStore with the sqlc-generated Queries; sqlc.yaml generates for pgx/v5 with emit_interface on. exec_tx.go begins a transaction with store.connPool.Begin(ctx), runs the callback, and commits or rolls back; TransferTx in tx_transfer.go runs five statements inside it, the last two AddAccountBalance updates ordered by account id to avoid deadlocks. Every query takes the ctx the gRPC or gateway handler passed in, and the asynq task processor passes its own, but no deadline is set on any of them and no statement_timeout is sent to Postgres, so a lock wait or a slow plan holds a connection until the client gives up. db/sqlc/main_test.go opens the same pool for the tests against the Postgres the test.yml workflow starts.

It sets the limit at the pool, once. main.go and main_test.go move from pgxpool.New to pgxpool.ParseConfig, set ConnConfig.RuntimeParams["statement_timeout"] from a new DB_STATEMENT_TIMEOUT in app.env and util.Config, and open with pgxpool.NewWithConfig. That makes Postgres itself cancel any statement that runs longer than the limit, on every connection the pool hands out, with the SQLSTATE 57014 that pgx surfaces as a *pgconn.PgError. The value is a time.Duration like the token durations already in Config, formatted to milliseconds for the parameter.

Transactions get a deadline as a whole. execTx wraps its ctx in context.WithTimeout from a second setting, DB_TX_TIMEOUT, so the five statements of a transfer share one budget rather than each getting the full statement timeout; on expiry pgx cancels the in-flight statement and the existing rollback path runs. db/sqlc/error.go gains an ErrorCode case for 57014 beside the unique-violation and foreign-key codes it already names, and gapi/error.go maps it to codes.DeadlineExceeded so a client sees a timeout rather than an internal error. The sqlc-generated files are not edited.

In its sandbox the agent starts Postgres with make postgres, make createdb and make migrateup, then adds TestStatementTimeout to db/sqlc/store_test.go: it opens a pool with a 200 ms timeout, runs SELECT pg_sleep(1) through connPool.Exec, and asserts the PgError code is 57014; a second test runs TransferTx with a 100 ms context against an account row another transaction holds locked and asserts the error and that the balances did not change. It runs make test, which is go test -v -cover -short ./..., and make sqlc to show the generated code is unchanged.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add query timeouts to this sqlc project. Read main.go, util/config.go, app.env, db/sqlc/store.go, db/sqlc/exec_tx.go, db/sqlc/tx_transfer.go, db/sqlc/error.go, db/sqlc/main_test.go, gapi/error.go and sqlc.yaml first. Add DB_STATEMENT_TIMEOUT and DB_TX_TIMEOUT to app.env and Config as durations. In main.go and main_test.go switch to pgxpool.ParseConfig and NewWithConfig and set statement_timeout in RuntimeParams from the first setting. In execTx wrap the context with context.WithTimeout from the second. Add the 57014 code to error.go and map it to codes.DeadlineExceeded in gapi/error.go. Do not edit sqlc-generated files. Start Postgres with the Makefile targets, write TestStatementTimeout using pg_sleep and a TransferTx test against a locked row, run make test and make sqlc, and quote the results in the pull request. Open a pull request.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request touching main.go, util/config.go, app.env, db/sqlc/exec_tx.go, db/sqlc/error.go, db/sqlc/main_test.go, db/sqlc/store_test.go and gapi/error.go. The description quotes the make test output with the two new tests and the package coverage lines, the make sqlc run showing no diff under db/sqlc/, and the exact PgError message Postgres returned for the cancelled pg_sleep.

The run's record keeps the Postgres container's log, where the canceling statement due to statement timeout line appears at the moment the test expected it, and the migration run from make migrateup. A reviewer does not have to take the error code on faith.

Before merging, a reviewer picks the two durations for production. A 5 s statement timeout and a 10 s transaction timeout suit this schema; a service with a long reporting query sets a longer value on that one call through a context deadline rather than raising the pool-wide limit.

Run it on every database alert

This is the job to run when the monitor reports connections piling up or a lock wait that never ends. In the Project for the repository, an automation with the prompt above and a webhook trigger takes the alert body from Grafana or the database provider; the handler sends the trigger's URL an Idempotency-Key made from the alert id, so a re-fired alert does not start a second run. The trigger adds the payload after the prompt, so the run reads which query or table the alert named.

The first run adds the timeouts. A later run on a repository that already has them reads the alert, reproduces the slow statement in the sandbox, and opens a pull request that gives that one call its own deadline or an index, each on its own branch and linked to the alert.

The use case this job follows, with its automation and trigger: Fix a bug from an alert.

Why run it on Rush?

The timeout is one runtime parameter and one WithTimeout, and the work is in proving they fire: a statement_timeout set on the wrong config object applies to nothing, a context deadline on a transaction that is already past Begin leaves the rollback path untested, and the error code that comes back has to be the one the gRPC layer maps. In a Rush sandbox the agent runs real Postgres, makes a statement sleep past the limit, holds a lock against a transfer, and quotes what came back.

The container log and the tests stay with the run, so the next person who raises the limit for one slow report can read what the default protects. Across many services on the same sqlc layout the job runs in parallel, one sandbox each, with the Postgres error line as the evidence in every pull request.

Frequently asked questions

Why set statement_timeout on the pool instead of a context per query?

Because a context deadline cancels on the client side and the server may keep running the statement until it notices; statement_timeout makes Postgres stop it. The pool setting covers every sqlc query at once, and a context deadline is still used for the transaction as a whole.

Does this change the generated sqlc code?

No. The generated Queries already take a context.Context on every method, and the timeout is applied by the pool and by execTx, both hand-written. make sqlc in the sandbox shows no diff under db/sqlc/.

What does a client see when a transfer times out?

A gRPC DeadlineExceeded from the mapping in gapi/error.go, and through the gateway an HTTP 504. The transaction is rolled back by the existing path in execTx, and the test asserts both account balances are unchanged.

Will the asynq worker's tasks be affected?

Yes, in the same way: the processor's queries go through the same pool and get the same statement timeout. A task that legitimately needs longer passes a context with its own deadline, which the pool setting does not override.

Can one query get a longer limit?

Yes. SET LOCAL statement_timeout inside a transaction raises it for that transaction only; say which query in the prompt and the agent adds it in a hand-written transaction wrapper beside TransferTx rather than in the generated code.

More sqlc jobs

The same job on other stacks

Keep reading