Set PostgreSQL lock_timeout Below statement_timeout

The reproduced PostgreSQL migration failed in two different ways against the same blocker. With lock_timeout=1200ms and statement_timeout=4s, the server returned canceling statement due to lock timeout. After the limits were reversed, it returned canceling statement due to statement timeout. The shorter active clock owned the failure.

That distinction matters during schema changes. lock_timeout limits only time spent acquiring a lock. statement_timeout limits the whole statement, including useful execution after a lock is granted. Set the lock limit below the statement limit when the operational decision is “leave this queue quickly, but allow the change longer to run once admitted.”

This guide gives PostgreSQL operators one marker-owned, disposable acceptance lab. It uses a real conflicting relation lock, both timeout orders, an unlocked counterexample, transaction-local settings, successful DDL after release and exact cleanup. Run it first on a disposable PostgreSQL cluster or maintenance clone, not on a production database.

Two PostgreSQL Timeout Clocks Measure Different Boundaries

Current PostgreSQL client-configuration documentation defines statement_timeout as elapsed time from command arrival through completion. It defines lock_timeout more narrowly: the clock runs only while a statement waits to acquire a heavyweight lock. The manual explicitly notes that a nonzero lock limit equal to or greater than the statement limit is pointless because the statement limit fires first.

Together, the settings answer separate review questions:

A short lock limit does not make DDL safe by itself. PostgreSQL explicit-locking documentation shows that table commands acquire different modes and that ACCESS EXCLUSIVE conflicts with every table lock mode. A queued exclusive DDL request can also affect work arriving behind it, which is why Xata’s explanation of schema changes and exclusive locks recommends a short admission wait plus retry/backoff rather than one long queue. Compare MariaDB metadata-lock ownership when the neighboring incident uses that engine; the owner diagnosis is relevant, but the server variables and exact failure semantics are not interchangeable.

Keeping one controlled blocker constant makes the observed error attributable to timeout precedence, not to two different workloads.

Build One Marker-Owned PostgreSQL Fixture

Run every input in one continuous Bash session. You need local superuser access to a disposable PostgreSQL cluster, runuser, psql, GNU date, and the default postgres maintenance database. The first block refuses an existing path or schema, creates a marker table and installs a fail-closed cleanup trap.

set -Eeuo pipefail

LAB=/tmp/voxfor-postgresql-timeout-151-lab
MARKER=voxfor-postgresql-timeout-151-owned
SCHEMA=voxfor_timeout_151
BLOCKER_APP=voxfor_timeout_151_blocker
RECEIPT="$LAB/receipt.txt"
MAIN_BASHPID=$BASHPID

pg() {
  runuser -u postgres -- env PGAPPNAME=voxfor_timeout_151_control \
    psql -X -v ON_ERROR_STOP=1 -d postgres "$@"
}
pg_at() { pg -Atq "$@"; }
terminate_blocker() {
  pg_at -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name='$BLOCKER_APP' AND pid<>pg_backend_pid();" >/dev/null || true
}
cleanup() {
  [[ "$BASHPID" == "$MAIN_BASHPID" ]] || return 0
  terminate_blocker
  if [[ "$(pg_at -c "SELECT count(*) FROM pg_namespace WHERE nspname='$SCHEMA';")" == 1 ]]; then
    token=$(pg_at -c "SELECT token FROM $SCHEMA.lab_marker LIMIT 1;" 2>/dev/null || true)
    [[ "$token" == "$MARKER" ]] || { printf 'refusing_unowned_schema=%s\n' "$SCHEMA" >&2; return 70; }
    pg -c "DROP SCHEMA $SCHEMA CASCADE;" >/dev/null
  fi
  if [[ -e "$LAB" ]]; then
    [[ "$LAB" == /tmp/voxfor-postgresql-timeout-151-lab ]]
    [[ "$(cat "$LAB/.marker")" == "$MARKER" ]]
    rm -rf -- "$LAB"
  fi
}
trap cleanup EXIT

[[ ! -e "$LAB" ]]
[[ "$(pg_at -c "SELECT count(*) FROM pg_namespace WHERE nspname='$SCHEMA';")" == 0 ]]
install -d -m 0700 "$LAB"
printf '%s\n' "$MARKER" > "$LAB/.marker"
pg <<SQL >/dev/null
CREATE SCHEMA $SCHEMA;
CREATE TABLE $SCHEMA.lab_marker(token text PRIMARY KEY);
INSERT INTO $SCHEMA.lab_marker VALUES ('$MARKER');
CREATE TABLE $SCHEMA.orders(id bigint PRIMARY KEY, state text NOT NULL);
INSERT INTO $SCHEMA.orders VALUES (1,'queued'),(2,'running');
SQL
VERSION=$(pg_at -c "SELECT current_setting('server_version');")
DEFAULTS=$(pg_at -F '|' -c "SELECT current_setting('lock_timeout'),current_setting('statement_timeout');")
[[ "$DEFAULTS" == '0|0' ]]

Each PGAPPNAME value makes its connection identifiable without relying on shell PIDs. Cleanup will not drop a same-named schema unless its marker token matches. Do not copy the pattern into a shared production database without first changing all identifiers, creating a database backup and recording which exact object owner approved removal.

Hold One Real Blocker Before Testing Either Limit

A plain SELECT takes AccessShareLock on orders. The background transaction then sleeps while keeping that lock. ALTER TABLE needs a conflicting table lock, so it waits. The loop does not proceed until pg_locks and pg_stat_activity prove the expected granted lock exists.

runuser -u postgres -- env PGAPPNAME="$BLOCKER_APP" \
  psql -X -v ON_ERROR_STOP=1 -d postgres >"$LAB/blocker.log" 2>&1 <<SQL &
BEGIN;
SELECT count(*) FROM $SCHEMA.orders;
SELECT pg_sleep(60);
ROLLBACK;
SQL
BLOCKER_SHELL_PID=$!

for _ in $(seq 1 80); do
  BLOCKER_LOCKS=$(pg_at -c "
    SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a USING(pid)
    WHERE a.application_name='$BLOCKER_APP'
      AND l.relation='$SCHEMA.orders'::regclass
      AND l.mode='AccessShareLock' AND l.granted;")
  [[ "$BLOCKER_LOCKS" == 1 ]] && break
  sleep 0.05
done
[[ "$BLOCKER_LOCKS" == 1 ]]

run_timeout_case() {
  local label=$1 lock_value=$2 statement_value=$3 expected=$4 column_name=$5
  local app="voxfor_timeout_151_${label}" log="$LAB/${label}.log"
  local started ended elapsed status wait_row error_line
  started=$(date +%s%3N)
  set +e
  runuser -u postgres -- env PGAPPNAME="$app" \
    psql -X -v ON_ERROR_STOP=1 -d postgres >"$log" 2>&1 <<SQL &
SET lock_timeout='$lock_value';
SET statement_timeout='$statement_value';
ALTER TABLE $SCHEMA.orders ADD COLUMN $column_name integer;
SQL
  local shell_pid=$!
  set -e
  wait_row=''
  for _ in $(seq 1 40); do
    wait_row=$(pg_at -F '|' -c "SELECT coalesce(wait_event_type,''),coalesce(wait_event,''),cardinality(pg_blocking_pids(pid)) FROM pg_stat_activity WHERE application_name='$app';")
    [[ "$wait_row" == 'Lock|relation|1' ]] && break
    sleep 0.03
  done
  [[ "$wait_row" == 'Lock|relation|1' ]]
  set +e; wait "$shell_pid"; status=$?; set -e
  ended=$(date +%s%3N); elapsed=$((ended-started))
  [[ "$status" -ne 0 ]]
  error_line=$(grep -F "canceling statement due to $expected timeout" "$log" | tail -n 1)
  [[ -n "$error_line" ]]
  [[ "$(pg_at -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='$SCHEMA' AND table_name='orders' AND column_name='$column_name';")" == 0 ]]
  printf '%s_wait=Lock/relation blockers=1 elapsed_ms=%s error=%s\n' "$label" "$elapsed" "${error_line#ERROR:  }"
}

PostgresAI’s detailed lock-timeout and retry migration analysis uses multiple sessions for the same reason: a migration receipt needs both the waiting DDL and the connection that owns the conflicting lock. A generic “query timed out” log line cannot establish that relationship.

Let lock_timeout Win First

Run the candidate DDL with the lock limit at 1.2 seconds and the whole-statement ceiling at four seconds. The helper requires Lock|relation|1 while it waits, a nonzero psql exit, the exact lock-timeout error and absence of the proposed column.

LOCK_FIRST=$(run_timeout_case lock_first 1200ms 4s lock lock_first)
grep -F 'error=canceling statement due to lock timeout' <<<"$LOCK_FIRST"

This is the desired fail-fast admission behavior: the migration leaves the queue quickly and makes the lock-specific reason observable. pganalyze associates this server condition with SQLSTATE 55P03 and lock-timeout cancellation. Record the SQLSTATE in application or migration-tool telemetry when the driver exposes it; user-facing messages alone are weaker because middleware can rewrite them.

Reverse the Pair and Let statement_timeout Win

Change no table, blocker or DDL shape. Reverse only the two values. A 1.2-second statement ceiling now expires before the four-second lock ceiling.

STATEMENT_FIRST=$(run_timeout_case statement_first 4s 1200ms statement statement_first)
grep -F 'error=canceling statement due to statement timeout' <<<"$STATEMENT_FIRST"

Unlike the first error, this failure says the broad ceiling won before PostgreSQL could report the narrower admission boundary. If a retry policy treats only lock contention as transient, reversing these values can change both alert classification and retry behavior. Test the exact driver and migration tool because some frameworks wrap database errors or automatically abort a larger batch.

Prove lock_timeout Ignores Unlocked Work

A common wrong model treats lock_timeout=200ms as a 200-millisecond query limit. The following session sleeps for roughly 650 milliseconds without waiting on a heavyweight lock. It must finish before the three-second statement ceiling.

UNLOCKED_STARTED=$(date +%s%3N)
UNLOCKED_RESULT=$(runuser -u postgres -- env PGAPPNAME=voxfor_timeout_151_unlocked \
  psql -X -Atq -v ON_ERROR_STOP=1 -d postgres <<SQL
SET lock_timeout='200ms';
SET statement_timeout='3s';
SELECT pg_sleep(0.65);
SELECT 'completed_without_lock_wait';
SQL
)
UNLOCKED_RESULT=$(tail -n 1 <<<"$UNLOCKED_RESULT")
UNLOCKED_ELAPSED=$(( $(date +%s%3N)-UNLOCKED_STARTED ))
[[ "$UNLOCKED_RESULT" == completed_without_lock_wait ]]
(( UNLOCKED_ELAPSED >= 600 && UNLOCKED_ELAPSED < 3000 ))

This counterexample establishes the configuration boundary more clearly than a definition alone. lock_timeout is not a workload runtime budget. A statement can wait on several locks at different points, and the lock timer applies to each acquisition attempt rather than serving as one overall migration stopwatch. Keep statement_timeout as the broader ceiling when indefinite execution is also unacceptable.

Scope Both Settings to the Migration Transaction

PostgreSQL supports session settings, role/database defaults and transaction-local settings. For a discrete migration, SET LOCAL inside an explicit transaction reduces leakage: the changed values disappear at transaction end. The next input records both defaults, the values inside the transaction and the restored values after commit.

LOCAL_SCOPE=$(runuser -u postgres -- env PGAPPNAME=voxfor_timeout_151_local \
  psql -X -Atq -v ON_ERROR_STOP=1 -d postgres <<SQL
SELECT 'before='||current_setting('lock_timeout')||'/'||current_setting('statement_timeout');
BEGIN;
SET LOCAL lock_timeout='250ms';
SET LOCAL statement_timeout='2s';
SELECT 'inside='||current_setting('lock_timeout')||'/'||current_setting('statement_timeout');
COMMIT;
SELECT 'after='||current_setting('lock_timeout')||'/'||current_setting('statement_timeout');
SQL
)
grep -Fx 'before=0/0' <<<"$LOCAL_SCOPE"
grep -Fx 'inside=250ms/2s' <<<"$LOCAL_SCOPE"
grep -Fx 'after=0/0' <<<"$LOCAL_SCOPE"

This is especially important behind a connection pool. Review PgBouncer PostgreSQL pooling design because transaction pooling may return a server connection to a pool or move the next transaction to another backend. Do not depend on an earlier session-level SET being present later. Put SET LOCAL in the same explicit transaction as the supported transactional DDL, or configure the migration tool’s per-transaction hooks.

Some operations cannot be placed in a transaction block. CREATE INDEX CONCURRENTLY is the familiar example, and a canceled concurrent index build can leave an invalid index that needs deliberate inspection. Follow PostgreSQL invalid-index recovery workflow rather than assuming the timeout removed every artifact.

Release the Blocker and Require Successful DDL

Timeouts prove rejection paths, not that the migration is valid. Terminate only the connection with the exact lab application name, wait for its shell, require the blocker to disappear, then repeat a transaction-local DDL and verify the resulting catalog entry.

[[ "$(pg_at -c "SELECT count(*) FROM pg_stat_activity WHERE application_name='$BLOCKER_APP';")" == 1 ]]
terminate_blocker
set +e; wait "$BLOCKER_SHELL_PID"; set -e
[[ "$(pg_at -c "SELECT count(*) FROM pg_stat_activity WHERE application_name='$BLOCKER_APP';")" == 0 ]]

FINAL_RESULT=$(runuser -u postgres -- env PGAPPNAME=voxfor_timeout_151_final \
  psql -X -Atq -v ON_ERROR_STOP=1 -d postgres <<SQL
BEGIN;
SET LOCAL lock_timeout='1200ms';
SET LOCAL statement_timeout='4s';
ALTER TABLE $SCHEMA.orders ADD COLUMN accepted_at timestamptz;
COMMIT;
SELECT column_name||':'||data_type FROM information_schema.columns
WHERE table_schema='$SCHEMA' AND table_name='orders' AND column_name='accepted_at';
SQL
)
[[ "$FINAL_RESULT" == 'accepted_at:timestamp with time zone' ]]

{
  printf 'postgresql=%s\n' "$VERSION"
  printf 'session_defaults=lock_timeout:%s statement_timeout:%s\n' "${DEFAULTS%%|*}" "${DEFAULTS##*|}"
  printf 'blocker=AccessShareLock granted count=%s\n' "$BLOCKER_LOCKS"
  printf '%s\n' "$LOCK_FIRST" "$STATEMENT_FIRST"
  printf 'unlocked_execution=%s elapsed_ms=%s lock_timeout=200ms statement_timeout=3s\n' "$UNLOCKED_RESULT" "$UNLOCKED_ELAPSED"
  printf 'local_scope=%s\n' "$(paste -sd, <<<"$LOCAL_SCOPE")"
  printf 'blocker_release=absent\nfinal_ddl=%s\n' "$FINAL_RESULT"
} > "$RECEIPT"
cat "$RECEIPT"

The catalog check is necessary but not sufficient for a live release. After real DDL, run representative reads and writes, observe latency and lock queues, and validate the application code that expects the new shape. Review PostgreSQL HOT and fillfactor workload test for the stronger pattern: accept a database change with workload evidence, not only a catalog or configuration value.

Read the Receipt Before Approving a Migration Policy

Debian 13 with PostgreSQL 17.10 produced this representative receipt from the exact inputs. Millisecond values will vary by scheduler; the error class, wait relationship, scope values and final column are the stable acceptance facts.

postgresql=17.10 (Debian 17.10-0+deb13u1)
session_defaults=lock_timeout:0 statement_timeout:0
blocker=AccessShareLock granted count=1
lock_first_wait=Lock/relation blockers=1 elapsed_ms=1245 error=canceling statement due to lock timeout
statement_first_wait=Lock/relation blockers=1 elapsed_ms=1250 error=canceling statement due to statement timeout
unlocked_execution=completed_without_lock_wait elapsed_ms=708 lock_timeout=200ms statement_timeout=3s
local_scope=before=0/0,inside=250ms/2s,after=0/0
blocker_release=absent
final_ddl=accepted_at:timestamp with time zone

Approve the timeout boundary only when one visible blocker exists before each contested test; the shorter lock limit produces the lock-specific error; the reversed pair produces the statement-specific error; neither failed DDL leaves its proposed column; unlocked work outlives the lock limit; SET LOCAL restores both prior values; the named blocker disappears; and the final DDL creates the expected column. For production, also require a retry budget with backoff and jitter, blocker-owner escalation, representative application acceptance, monitoring and a reviewed rollback.

Citus Data’s broader Postgres lock guidance recommends short lock waits for migrations, but the number is workload-specific. Start with a value short enough not to trap an application queue, then measure. A 500-millisecond boundary can be too aggressive across a high-latency control plane; five seconds can be far too long on a saturated write path.

Finally, remove only the lab scope. The rollback terminates the exact named session, confirms the schema marker, drops that schema, verifies the fixed path marker and proves both resources absent. If any marker differs, the block exits without deleting that object.

terminate_blocker
if [[ "$(pg_at -c "SELECT count(*) FROM pg_namespace WHERE nspname='$SCHEMA';")" == 1 ]]; then
  [[ "$(pg_at -c "SELECT token FROM $SCHEMA.lab_marker LIMIT 1;")" == "$MARKER" ]]
  pg -c "DROP SCHEMA $SCHEMA CASCADE;" >/dev/null
fi
[[ "$LAB" == /tmp/voxfor-postgresql-timeout-151-lab ]]
[[ "$(cat "$LAB/.marker")" == "$MARKER" ]]
rm -rf -- "$LAB"
trap - EXIT
[[ "$(pg_at -c "SELECT count(*) FROM pg_namespace WHERE nspname='$SCHEMA';")" == 0 ]]
[[ ! -e "$LAB" ]]
printf 'cleanup=schema_absent files_absent\n'

A production rollback is a different operation. Stop the deployment wave, preserve the exact server error and blocker graph, and follow the migration tool’s reviewed reversal. Do not terminate an unknown transaction or drop a partially created object merely because its name resembles the lab. If a failed operation was transactional, roll back that transaction; if the operation has nontransactional artifacts, identify their state and consumers first.

For more database operations and recovery receipts, use Database guide library. The reusable standard is the same: name the owner, reproduce the boundary, retain the observed state, verify application behavior and remove only what the test created.