Lab 6: Read pg_stat_activity properly, then cancel and terminate on purpose

Objective

pg_stat_activity is the first thing anybody looks at during a database incident and one of the easiest views to misread. Two of its states show the same wait event and mean opposite things. Two of its administrative functions have similar names and very different consequences.

By the end of this lab you will have created each state deliberately, looked at what each one is holding, and applied both pg_cancel_backend and pg_terminate_backend to each — recording what the client on the other end saw every time.

You will finish able to answer the question that actually gets asked at two in the morning: this session has been there for forty minutes — does it matter, and what happens if I kill it?

Architecture

Four concurrent sessions against one database, deliberately arranged so that each occupies a different state, plus an observer session running the queries.

flowchart TD
    O["observer session\nruns pg_stat_activity"] --> V["pg_stat_activity\npg_locks"]
    A["session A\nSELECT pg_sleep(600)"] --> S["PostgreSQL 18"]
    B["session B\nBEGIN; UPDATE; then waits"] --> S
    C["session C\nconnected, no transaction"] --> S
    S --> V
    A -.-> SA["state = active\nwait = Timeout:PgSleep"]
    B -.-> SB["state = idle in transaction\nwait = Client:ClientRead\nHOLDS locks and an xid"]
    C -.-> SC["state = idle\nwait = Client:ClientRead\nholds nothing"]

Requirements

• A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab06.
• The ability to run several sessions at once. The lab as executed launches background psql processes inside a container with docker exec -d; three terminal windows work equally well.
• Nothing in this lab is destructive beyond its own database, but it does terminate sessions, so do not run it where somebody else is connected.

Scenario

Monitoring has raised “long-running session on the primary”. Somebody has already suggested killing it. Before anybody types anything, you want to know which of three quite different situations this is, because the right response to each is different and one of them is “leave it alone”.

Tasks

Task 1 — See what an idle cluster looks like

LAB="$HOME/rbpg-lab-06"
mkdir -p "$LAB"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, backend_type, state, wait_event_type, wait_event
  FROM pg_stat_activity ORDER BY pid;"

$ psql -X -c "SELECT pid, backend_type, state, wait_event_type, wait_event FROM pg_stat_activity ORDER BY pid;"

 pid  |         backend_type         | state  | wait_event_type |     wait_event      
------+------------------------------+--------+-----------------+---------------------
9077 | io worker                    |        | Activity        | IoWorkerMain
9078 | io worker                    |        | Activity        | IoWorkerMain
9079 | io worker                    |        | Activity        | IoWorkerMain
9080 | checkpointer                 |        | Activity        | CheckpointerMain
9081 | background writer            |        | Activity        | BgwriterMain
9083 | walwriter                    |        | Activity        | WalWriterMain
9084 | autovacuum launcher          |        | Activity        | AutovacuumMain
9085 | logical replication launcher |        | Activity        | LogicalLauncherMain
9145 | client backend               | active |                 | 
(9 rows)

Two things to fix in your mental model straight away.

pg_stat_activity is not a list of client sessions. It includes every background process, and those have a NULL state — state is only meaningful for client backends. Filtering with WHERE backend_type = 'client backend' is almost always what you want.

The background processes all show wait event type Activity. That is the wait event class meaning “this process is idle, waiting for work of its own kind”. An Activity wait is never a problem; it is the normal resting state. Alerting on “processes with a wait event” catches all of these and tells you nothing.

Task 2 — Create three sessions in three states

docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
CREATE DATABASE lab06;
SQL

docker exec -i -u postgres rbpg-lab01 psql -X -d lab06 <<'SQL'
CREATE TABLE ledger(id int PRIMARY KEY, amount numeric);
INSERT INTO ledger SELECT g, g*1.5 FROM generate_series(1,1000) g;
SQL

# Session A: an active statement.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab06 -c 'SELECT pg_sleep(600);'\""

# Session B: a transaction left open between statements.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab06 <<'SQL'
BEGIN;
UPDATE ledger SET amount = amount + 1 WHERE id = 1;
\\\\! sleep 600
SQL\""

# Session C: connected, no transaction.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab06 <<'SQL'
SELECT 1;
\\\\! sleep 600
SQL\""

sleep 4

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, wait_event_type, wait_event,
         now()-state_change AS in_state,
         now()-xact_start   AS xact_age,
         left(query,40) AS last_query
  FROM pg_stat_activity
  WHERE backend_type='client backend' AND datname='lab06'
  ORDER BY pid;" | tee "$LAB/session-states.txt"

$ psql -X -c "SELECT pid, state, wait_event_type, wait_event, now()-state_change AS in_state, now()-xact_start AS xact_age, left(query,40) AS last_query FROM pg_stat_activity WHERE backend_type='client backend' AND datname='lab06' ORDER BY pid;"

 pid  |        state        | wait_event_type | wait_event |    in_state     |    xact_age     |                last_query                
------+---------------------+-----------------+------------+-----------------+-----------------+------------------------------------------
9163 | active              | Timeout         | PgSleep    | 00:00:19.070553 | 00:00:19.070554 | SELECT pg_sleep(600);
9183 | idle                | Client          | ClientRead | 00:00:19.013013 |                 | SELECT 1;
9212 | idle in transaction | Client          | ClientRead | 00:00:04.029039 | 00:00:04.029803 | UPDATE ledger SET amount = amount + 1 WH
(3 rows)

Read the middle two rows carefully. idle and idle in transaction have identical wait events: Client:ClientRead. Both sessions are doing exactly the same thing — waiting for the client to send something. The wait event cannot tell them apart.

What separates them is xact_age. Session 9183 has none, because it has no open transaction. Session 9212 does.

Task 3 — Look at what each session is holding

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, backend_xid, backend_xmin
  FROM pg_stat_activity
  WHERE backend_type='client backend' AND datname='lab06'
  ORDER BY pid;" | tee "$LAB/what-is-held.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab06 -c "
  SELECT l.pid, l.locktype, l.mode, l.granted, c.relname
  FROM pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
  WHERE l.pid = 9212 ORDER BY l.locktype;" | tee -a "$LAB/what-is-held.txt"

$ a query over pg_stat_activity, then a query over pg_locks for the idle-in-transaction pid

 pid  |        state        | backend_xid | backend_xmin 
------+---------------------+-------------+--------------
9163 | active              |             |          785
9183 | idle                |             |             
9212 | idle in transaction |         786 |             

pid  |   locktype    |       mode       | granted |   relname   
------+---------------+------------------+---------+-------------
9212 | relation      | RowExclusiveLock | t       | ledger
9212 | relation      | RowExclusiveLock | t       | ledger_pkey
9212 | transactionid | ExclusiveLock    | t       | 
9212 | virtualxid    | ExclusiveLock    | t       | 
(4 rows)

The idle session holds nothing at all — no transaction id, no snapshot, no locks. It is a connection and a few hundred kilobytes of backend memory, and it is not blocking anything.

The idle-in-transaction session holds four locks and transaction id 786, and has done since it ran the UPDATE. It will hold them until it commits, rolls back, or is terminated. Nobody is going to make that happen, because the client is not talking to the server.

Task 4 — Cancel an active statement

pg_cancel_backend sends SIGINT. It asks the backend to abandon the statement it is running. The session stays connected.

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_cancel_backend(9163);"

To see that the session survives, the client needs something else to do afterwards. A psql -c exits as soon as its one statement fails, which makes a cancel look like a disconnection. Use a session with a second statement queued:

docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab06 <<'SQL'
SELECT pg_sleep(600);
SELECT 'session survived the cancel' AS note, pg_backend_pid();
\\\\! sleep 600
SQL\" > /tmp/sessA2.out 2>&1"

sleep 3
APID=$(docker exec -u postgres rbpg-lab01 psql -X -tAc \
  "SELECT pid FROM pg_stat_activity WHERE query LIKE 'SELECT pg_sleep(600)%' AND datname='lab06' LIMIT 1;")

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_cancel_backend($APID);"
sleep 2
docker exec rbpg-lab01 cat /tmp/sessA2.out
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = $APID;"

$ pg_cancel_backend against an active session, then read the client output and pg_stat_activity

ERROR:  canceling statement due to user request
          note             | pg_backend_pid 
-----------------------------+----------------
session survived the cancel |           9270
(1 row)

pid  | state | wait_event_type | wait_event 
------+-------+-----------------+------------
9270 | idle  | Client          | ClientRead
(1 row)

The client got an ERROR, not a FATAL, carried on to its next statement, and reported the same PID. Any transaction the session had open is now in the aborted state and will need a rollback, but the connection, its prepared statements, its temporary tables and its session settings are all intact.

This is the gentler of the two tools and should always be the first one tried.

Task 5 — Cancel something that is not running a statement

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, now()-xact_start AS xact_age FROM pg_stat_activity WHERE pid = 9212;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_cancel_backend(9212);"
sleep 2
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, now()-xact_start AS xact_age FROM pg_stat_activity WHERE pid = 9212;"
docker exec -u postgres rbpg-lab01 psql -X -d lab06 -c "
  SELECT count(*) AS locks_held FROM pg_locks WHERE pid = 9212;"

$ pg_cancel_backend against an idle in transaction session

 pg_cancel_backend 
-------------------
t
(1 row)

pid  |        state        |    xact_age    
------+---------------------+----------------
9212 | idle in transaction | 00:00:55.38785
(1 row)

locks_held 
------------
        4
(1 row)

This is the trap the lab exists to set. pg_cancel_backend returned true — it successfully delivered the signal — and the session is completely unaffected. There was no statement to cancel.

Task 6 — Terminate, and see what the client gets

pg_terminate_backend sends SIGTERM. The backend rolls back whatever transaction is open, closes the connection and exits.

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_terminate_backend(9212);"
sleep 2
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT count(*) AS still_there FROM pg_stat_activity WHERE pid = 9212;"
docker exec -u postgres rbpg-lab01 psql -X -d lab06 -c "SELECT id, amount FROM ledger WHERE id = 1;"

$ pg_terminate_backend, then check pg_stat_activity and the table

 pg_terminate_backend 
----------------------
t
(1 row)

still_there 
-------------
         0
(1 row)

id | amount 
----+--------
1 |    1.5
(1 row)

The uncommitted UPDATE is gone. That is correct and it is the entire risk: any work the transaction had done and not committed is discarded, and the application will find out by having its connection closed rather than by receiving an error it can handle.

Now capture the message a client sees when it is terminated mid-query:

docker exec -d rbpg-lab01 bash -c \
  "su - postgres -c \"psql -X -d lab06 -c 'SELECT pg_sleep(600);'\" > /tmp/sessD.out 2>&1"
sleep 3
DPID=$(docker exec -u postgres rbpg-lab01 psql -X -tAc \
  "SELECT pid FROM pg_stat_activity WHERE query LIKE 'SELECT pg_sleep(600)%' AND state='active' AND datname='lab06' LIMIT 1;")

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_terminate_backend($DPID);"
sleep 2
docker exec rbpg-lab01 cat /tmp/sessD.out

$ pg_terminate_backend against an active session, then read the client's output

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

docker exec rbpg-lab01 grep -E "canceling statement|terminating connection" \
  /var/log/postgresql/postgresql-18-main.log | tail -5 | tee "$LAB/cancel-vs-terminate.txt"

$ grep the cluster log for cancellation and termination messages

2026-08-28 00:33:51.476 UTC [9176] postgres@lab06 FATAL:  terminating connection due to administrator command
2026-08-28 00:34:07.224 UTC [9163] postgres@lab06 ERROR:  canceling statement due to user request
2026-08-28 00:34:33.045 UTC [9270] postgres@lab06 ERROR:  canceling statement due to user request
2026-08-28 00:34:47.013 UTC [9212] postgres@lab06 FATAL:  terminating connection due to administrator command
2026-08-28 00:35:05.503 UTC [9368] postgres@lab06 FATAL:  terminating connection due to administrator command

Summarising all four combinations:

Task 7 — The five timeouts, which are the real fix

Manual termination is an incident response. The maintainable answer is to bound these situations before they need a human.

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, context FROM pg_settings
  WHERE name IN ('statement_timeout','lock_timeout',
                 'idle_in_transaction_session_timeout',
                 'idle_session_timeout','transaction_timeout')
  ORDER BY name;" | tee "$LAB/timeouts.txt"

$ psql -X -c "SELECT name, setting, unit, context FROM pg_settings WHERE name IN (...) ORDER BY name;"

                name                 | setting | unit | context 
-------------------------------------+---------+------+---------
idle_in_transaction_session_timeout | 0       | ms   | user
idle_session_timeout                | 0       | ms   | user
lock_timeout                        | 0       | ms   | user
statement_timeout                   | 0       | ms   | user
transaction_timeout                 | 0       | ms   | user
(5 rows)

All five are user context, so they can be set per session, per role or per database — which is what makes them practical. A reporting role can carry a generous statement_timeout while the application role carries a strict one.

Watch idle_in_transaction_session_timeout do the job you did by hand in Task 6:

docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab06 <<'SQL'
SET idle_in_transaction_session_timeout = '5s';
BEGIN;
UPDATE ledger SET amount = amount + 1 WHERE id = 2;
\\\\! sleep 30
SELECT 'never reached';
SQL\""

sleep 4
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, state, now()-state_change AS in_state FROM pg_stat_activity
  WHERE datname='lab06' AND state='idle in transaction';"

sleep 8
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT count(*) AS idle_in_xact FROM pg_stat_activity
  WHERE datname='lab06' AND state='idle in transaction';"

docker exec rbpg-lab01 grep "idle-in-transaction" \
  /var/log/postgresql/postgresql-18-main.log | tail -1 | tee -a "$LAB/timeouts.txt"

$ observe the session before and after the 5 second timeout, then read the log

 pid  |        state        |    in_state     
------+---------------------+-----------------
9415 | idle in transaction | 00:00:04.030441
(1 row)

idle_in_xact 
--------------
          0
(1 row)

2026-08-28 00:35:24.606 UTC [9415] postgres@lab06 FATAL:  terminating connection due to idle-in-transaction timeout

Note the log message names the cause. That is the difference between a timeout and a manual kill: six months later the log still says why.

Task 8 — statement_timeout is not transaction_timeout

These two are routinely confused. transaction_timeout arrived in PostgreSQL 17 and behaves quite differently from the older setting.

docker exec -i -u postgres rbpg-lab01 psql -X -d lab06 <<'SQL'
SET transaction_timeout = '3s';
BEGIN;
SELECT pg_sleep(2);
SELECT pg_sleep(2);
SELECT 'never reached' AS note;
COMMIT;
SQL

$ a transaction of two 2-second statements under transaction_timeout = 3s

 pg_sleep 
----------

(1 row)

FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

docker exec -i -u postgres rbpg-lab01 psql -X -d lab06 <<'SQL'
SET statement_timeout = '3s';
BEGIN;
SELECT pg_sleep(2);
SELECT pg_sleep(2);
SELECT 'transaction ran to completion' AS note;
COMMIT;
SQL

$ the same transaction under statement_timeout = 3s

SET
BEGIN
pg_sleep 
----------

(1 row)

pg_sleep 
----------

(1 row)

           note              
-------------------------------
transaction ran to completion
(1 row)

COMMIT

Same transaction, same two statements, same three-second limit, opposite outcomes. The differences are worth stating precisely:

Task 9 — The triage query

Put it together into the query to run first when a session is reported:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, usename, datname, state,
         wait_event_type || ':' || coalesce(wait_event,'-') AS waiting_on,
         now()-xact_start   AS xact_age,
         now()-query_start  AS query_age,
         now()-state_change AS in_state,
         left(query,30) AS query
  FROM pg_stat_activity
  WHERE backend_type='client backend' AND pid <> pg_backend_pid()
  ORDER BY xact_start NULLS LAST;"

$ the triage query over pg_stat_activity

 pid  | usename  | datname | state |    waiting_on     | xact_age |    query_age    |    in_state     |             query              
------+----------+---------+-------+-------------------+----------+-----------------+-----------------+--------------------------------
9270 | postgres | lab06   | idle  | Client:ClientRead |          | 00:01:36.85496  | 00:01:36.854845 | SELECT 'session survived the c
9183 | postgres | lab06   | idle  | Client:ClientRead |          | 00:02:33.355567 | 00:02:33.355451 | SELECT 1;
(2 rows)

ORDER BY xact_start NULLS LAST puts the sessions that can be holding something back at the top and the ones that cannot at the bottom, which is the order you want to read them in.

Three ages, three meanings:

• xact_age — how long a transaction has been open. Non-null and large is the one that costs you.
• query_age — how long since the current or last statement started.
• in_state — how long the session has been in the state shown. For idle in transaction, this is how long nobody has touched it.

Validation

test -s "$LAB/session-states.txt"     && echo "OK session-states"
test -s "$LAB/what-is-held.txt"       && echo "OK what-is-held"
test -s "$LAB/cancel-vs-terminate.txt" && echo "OK cancel-vs-terminate"
test -s "$LAB/timeouts.txt"           && echo "OK timeouts"

grep -q "idle in transaction" "$LAB/session-states.txt" && echo "OK all three states captured"
grep -q "RowExclusiveLock"    "$LAB/what-is-held.txt"   && echo "OK locks captured"
grep -q "ERROR:  canceling"   "$LAB/cancel-vs-terminate.txt" && echo "OK cancel logged"
grep -q "FATAL:  terminating" "$LAB/cancel-vs-terminate.txt" && echo "OK terminate logged"
grep -q "idle-in-transaction timeout" "$LAB/timeouts.txt"    && echo "OK timeout fired"

Questions to answer without looking anything up:

1. Two sessions both show Client:ClientRead. Which column tells you which of them you should care about?
2. pg_cancel_backend returned true and the session is unchanged. Is that a bug?
3. What does the query column mean for a session whose state is idle?
4. An application reports “server closed the connection unexpectedly”. Name three server-side causes.
5. A transaction runs ten statements of half a second each with no idle time. Which of statement_timeout = 3s and transaction_timeout = 3s kills it, and what does the client see?

Expected Outcome

You have created each session state deliberately, established what each holds, applied both signalling functions to each, and read the client and server messages for every combination.

The query to carry away is the triage query in Task 9. The judgement to carry away is smaller and more useful:

• idle with no transaction: not your problem.
• active: cancel first, terminate only if the cancel does not take.
• idle in transaction: cancel does nothing. Terminate, and then go and fix whatever left it there, because it will happen again.

And the durable fix for the third case is a timeout, not a person.

Troubleshooting

The three sessions in Task 2 all show as idle. The background docker exec commands finished before you looked. Each needs something holding it open — a pg_sleep, or an open transaction with no commit. Re-run them and query within the window.

pg_cancel_backend returns t and nothing happens. It returns true when the signal was sent, not when it had an effect. A session that is idle in transaction is not running a statement, so there is nothing to cancel — this is Task 5’s whole point, and terminate is the only lever.

pg_terminate_backend returns f. The PID no longer exists, or it belongs to a backend your role may not signal. A non-superuser can signal only its own role’s backends unless granted pg_signal_backend.

The client sees no error after a terminate. Many drivers reconnect transparently and only surface the failure on the next statement. Check the server log, which records the termination regardless of what the client chose to show.

statement_timeout does not fire on an idle-in-transaction session. Correct, and it is the distinction Task 8 exists to draw. A session that is holding a transaction open is not executing a statement. idle_in_transaction_session_timeout is the setting for that state, and transaction_timeout bounds the whole transaction regardless of state.

A timeout set with SET disappears. Session scope lasts for the session. Set it on the role or the database — ALTER ROLE app SET idle_in_transaction_session_timeout = '5min' — for it to survive.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab06' AND pid <> pg_backend_pid();"

docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab06;"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT count(*) AS client_backends FROM pg_stat_activity
  WHERE backend_type='client backend';"

The final count should be 1 — the session running the query.

Production notes

• Cancel before terminate, always. A cancel ends the statement and leaves the transaction and the connection intact; a terminate drops the connection and rolls back whatever was open. They are not two strengths of the same action.
• Terminating an idle in transaction session treats the symptom. The session got there because an application opened a transaction and then did something slow, and it will do it again tonight. The durable fix is idle_in_transaction_session_timeout, applied to the role.
• Set the timeouts on roles rather than globally. A batch role and an interactive reporting role want different values, and a global setting is chosen for whichever of them complains first.
• Never terminate every session to clear a problem. It rolls back everybody’s work to treat one offender, and the triage query in Task 9 exists precisely so you can name the one that matters.
• Record what you terminated and why before you do it. The session disappears from pg_stat_activity the moment it dies, and that row is the only evidence of what it was doing.

What You Learned

• idle and idle in transaction are unrelated problems. The first is a connection doing nothing; the second is a transaction holding locks and pinning the vacuum horizon.
• pg_cancel_backend returning true means the signal was sent, not that anything was cancelled.
• Cancel does nothing to a session that is not running a statement. For idle in transaction, terminate is the only lever.
• A terminate rolls the transaction back and drops the connection, and many drivers hide that from the application until its next statement.
• Five timeouts, five different scopes. statement_timeout bounds a statement; idle_in_transaction_session_timeout bounds idling inside a transaction; transaction_timeout bounds the whole transaction; lock_timeout bounds waiting for a lock; authentication_timeout bounds connecting.
• Timeouts are the fix; signalling is the response to an incident. One of them stops the next occurrence.