Cancel or terminate · PostgreSQL

Two functions, and the choice between them is made under time pressure with incomplete information. The commonly repeated summary — “cancel stops the query, terminate kills the connection” — is true and does not tell you what you need to know, which is what each one releases.

That was measured rather than assumed, and one of the results contradicts the folklore.

Case 1: the session is running a statement

pg_cancel_backend(pid) sends a signal asking the backend to stop what it is doing. The statement ends with an error.

Service impact possiblecancelling a session that is mid-statement inside a transaction— Captured on 18.6. The session had run an UPDATE and was executing pg_sleep when the cancel arrived.

$ psql -U postgres -At -c "SELECT pg_cancel_backend(1876)"

-- client output of the cancelled session:
BEGIN
UPDATE 1
ERROR:  canceling statement due to user request

-- its state five seconds later:
pid  |             state             | backend_xid | xmin_age
------+-------------------------------+-------------+----------
1876 | idle in transaction (aborted) |             |

-- locks it still holds on the table:
(0 rows)

Read the last two blocks carefully, because this is the part that is usually stated wrongly.

The cancel aborted the transaction. backend_xid is NULL, backend_xmin is NULL, and no relation locks remain. The session is in idle in transaction (aborted) — it exists, it holds a connection slot, and it holds nothing else.

Verified again with a session holding a REPEATABLE READ snapshot:

Read-only / Safethe same test against a held snapshot— Captured on 18.6. The session had taken a REPEATABLE READ snapshot before the cancel.

$ psql -U postgres -c "SELECT pid, state, age(backend_xmin) AS xmin_age FROM pg_stat_activity WHERE pid=1913"

before cancel:  1913 | active                        | xmin_age 0
after cancel:   1913 | idle in transaction (aborted) | xmin_age NULL

So cancelling an active session does release its hold on the vacuum horizon. If the goal is to free locks or unblock vacuum, and the session is running something, cancel is sufficient.

Case 2: the session is idle in transaction

This is the case that matters, because it is the one causing the damage in lesson VII-05.

Service impact possiblecancelling a session that is idle in transaction— Captured on 18.6. The session had opened a REPEATABLE READ transaction, run one query, and gone quiet.

$ psql -U postgres -At -c "SELECT pg_cancel_backend(1950)"

before:                        1950 | idle in transaction | xmin_age 0
pg_cancel_backend returns:     t
after cancel:                  1950 | idle in transaction | xmin_age 0

The cancel did nothing. It returned true, because the signal was delivered successfully — but there was no statement to cancel, so the session is unchanged: same state, same snapshot, same locks.

pg_terminate_backend is the only verb that works here.

Destructiveterminating the same session— Real output from 18.6. The client sees a FATAL and the connection closes.

$ psql -U postgres -At -c "SELECT pg_terminate_backend(1950)"

-- returns: t

-- the client:
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly

-- afterwards:
SELECT count(*) FROM pg_stat_activity WHERE pid=1950;  -->  0

The decision, from the state column

`state`	Cancel	Terminate	Use
`active`	Ends the statement, aborts the transaction, releases locks and snapshot	Also closes the connection	Cancel first. It is reversible from the application’s point of view
`idle in transaction`	Returns true and does nothing	Ends it	Terminate. Cancel is not an option here
`idle in transaction (aborted)`	Nothing to cancel	Ends it	Terminate, though it holds only a slot
`idle`	Nothing to cancel	Closes the connection	Usually leave it. It holds nothing

The rule that follows: check state before choosing the verb. Reaching for cancel on an idle in transaction session is the most common wasted action in this whole area, and it looks like it worked because the function returns true.

Deciding whether it is safe

Before either verb, three questions.

What is it? Read application_name, usename, client_addr and query. A session from the analytics host running a long aggregate is a different decision from one belonging to the payment service.

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start   AS xact_age,
       now() - state_change AS in_state_for,
       backend_type,
       left(query, 100)     AS query
  FROM pg_stat_activity
 WHERE pid = 12345;

What will be lost? An active session in a transaction loses everything since BEGIN. If it is a four-hour data migration, that is four hours. If it is idle in transaction, it is doing nothing and losing nothing matters less.

Will it come back? Many clients reconnect and retry immediately. If the session is a runaway query from a service that will re-issue it, you have bought seconds. Fix the source, or the loop continues.

Never terminate these without deliberate thought

An anti-wraparound autovacuum worker. Its query contains (to prevent wraparound). Killing it discards all its progress and it restarts from the beginning with the age still climbing. Lesson VIII-05 covers this in full.

A CREATE INDEX CONCURRENTLY. Terminating it leaves an invalid index behind that the planner ignores and every write maintains. Clean up afterwards:

SELECT indexrelid::regclass AS index_name FROM pg_index WHERE NOT indisvalid;

A backup process. pg_basebackup or a pgBackRest backup session being killed means the backup is incomplete. Check whether one is running before terminating anything holding a replication slot.

The startup or checkpointer process. These are not client backends, and pg_terminate_backend against a background process can cause the postmaster to restart the whole cluster to guarantee consistency. Filtering to backend_type = 'client backend' avoids this.

Never use kill -9 on a backend. The postmaster treats an abnormal backend exit as a possible shared memory corruption and restarts every backend in the cluster — turning one problem session into a full disconnection event. pg_terminate_backend sends the right signal and is the supported path.

Bulk termination

Occasionally you need to clear a class of sessions rather than one.

-- everything idle in transaction for over ten minutes
SELECT pid, application_name, now() - state_change AS idle_for,
       pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
   AND now() - state_change > interval '10 minutes'
   AND backend_type = 'client backend'
   AND pid <> pg_backend_pid();

Three guards in that query are not optional: backend_type excludes background processes, pid <> pg_backend_pid() stops it terminating itself partway through, and the state filter restricts it to sessions that are doing nothing.

Run the SELECT without the pg_terminate_backend call first. Look at what it returns. Then add the call.

If you are doing this regularly, set the timeout instead

Terminating idle-in-transaction sessions by hand is a symptom. The server does it for you:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Better still, per role, so the reporting role and the application role can differ:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE analytics SET statement_timeout = '30min';

The full set worth having on a production cluster:

Setting	Bounds
`statement_timeout`	Any single statement
`idle_in_transaction_session_timeout`	A session idle inside a transaction
`idle_session_timeout`	A session idle with no transaction
`lock_timeout`	Waiting for a lock

All four are user context, so all four can be set per role, which is almost always better than one cluster-wide value.

Why a cancel can be ignored, and what to do when it is

pg_cancel_backend sets a flag and sends SIGINT. The backend notices at its next interrupt check point — a place in the code where it is safe to abandon what it is doing. Those points are frequent in query execution and are not everywhere.

Three situations where a cancel takes a long time or never lands.

Blocked in a system call. A backend waiting on a read from a network filesystem that has stopped responding cannot be interrupted until the call returns.

Inside certain C functions. An extension function that does not check for interrupts runs to completion. Well-written extensions call CHECK_FOR_INTERRUPTS() in their loops; not all do.

Some phases of large operations. Parts of index building and sorting have longer gaps between checks than ordinary execution.

The practical sequence: cancel, wait about five seconds, and if state and query have not changed, escalate to terminate. Terminate is handled at the same interrupt points, so it can be ignored in the same situations — but it also closes the socket, which frequently unblocks a backend stuck on client I/O.

If neither works, the backend is genuinely stuck in a system call and the problem is below PostgreSQL. That is a storage or network investigation, and killing processes will not help.

Note also who may do this. A non-superuser may cancel or terminate only their own sessions. The pg_signal_backend predefined role grants the ability against other users’ sessions without granting superuser, which is the right way to equip an operations role:

GRANT pg_signal_backend TO oncall_operator;

Members of pg_signal_backend still cannot signal a superuser’s backend.

What to take from this

Check state before choosing the verb. It determines which one works.
Cancel on an active session aborts the transaction and releases its locks and snapshot.
Cancel on an idle in transaction session returns true and does nothing. Terminate is the only option there.
Never kill -9 a backend; the postmaster restarts the cluster.
Anti-wraparound vacuum, concurrent index builds and backups deserve thought before termination.
Doing this by hand regularly means a timeout is missing.

Cross-course references

Linux for Production Sysadmins — Part VI (Processes) covers the signal semantics underneath pg_cancel_backend and pg_terminate_backend, and why kill -9 is a different act entirely.
Observability for Production Sysadmins — Part CIX (Incident investigation workflows) covers recording what was cancelled and why, which is the part most often skipped under pressure.