Who is blocking whom
This PostgreSQL 18.6 schedule has one idle blocker and one waiter. A holds a row lock after writing balance 200; B's conflicting update waits. The query combines pg_stat_activity with pg_blocking_pids() to identify them, then a permitted monitoring session terminates A.
A updates a row and then… goes to lunch. The transaction stays open.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…Someone pages you: 'updates are hanging'. This is the query you paste:
M> SELECT waiter.application_name AS waiter,
waiter.query AS waiting_query,
blocker.application_name AS blocker,
blocker.state AS blocker_state,
blocker.query AS blocker_last_query
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock'
ORDER BY waiter.application_name, blocker.application_name;
waiter | waiting_query | blocker | blocker_state | blocker_last_query
--------+------------------------------------------------+---------+---------------------+------------------------------------------------
B | UPDATE accounts SET balance = 300 WHERE id = 1 | A | idle in transaction | UPDATE accounts SET balance = 200 WHERE id = 1
(1 row)M has permission to signal A. A true return means the signal was sent, not that termination has finished:
M> SELECT pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity WHERE application_name = 'A';
terminated
------------
t
(1 row)A's transaction dies and rolls back; B gets the lock and finishes at last.
⏵ B resumes:
UPDATE 1
M> SELECT balance FROM accounts WHERE id = 1; -- A's uncommitted 200 is absent; B's 300 is not yet committed
balance
---------
100
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- B sees its own uncommitted update
balance
---------
300
(1 row)
B> COMMIT;
COMMIT
M> SELECT balance FROM accounts WHERE id = 1;
balance
---------
300
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Reading the answer
The asserted result names B, A, both statements, and A's idle in transaction state. For an idle backend, query reports its last query; for an active backend, it reports the current one. The activity-view contract also limits other sessions' details by role. The demo uses superuser access; a production operator may need pg_read_all_stats or suitable privileges.
The blocking-function contract includes conflicting holders and earlier conflicting waiters in a lock queue. Prepared transactions can appear as PID zero, so this inner join to live sessions cannot report every blocker. Parallel queries can produce duplicate client PIDs. Neither case is executed here; use pg_locks and pg_prepared_xacts when a session join is incomplete. Restrict a production query to the relevant database and scope before acting.
PostgreSQL 18's signaling contract says pg_cancel_backend "Cancels the current query" and pg_terminate_backend "Terminates the session". Cancellation has no running query to stop in this idle-blocker case. Ending the transaction from its application is another option; termination is not the only way to release its locks. Signaling requires permission. Only a superuser can cancel or terminate an ordinary superuser session. As a documented exception, roles with pg_signal_autovacuum_worker privileges can cancel or terminate autovacuum workers, which are otherwise considered superuser backends. This Scenario does not exercise that exception.
With the default zero timeout argument, a true termination result confirms signal delivery, not completed termination. The resumed B update supplies the completion observation here. M reads balance 100 while B's 300 is uncommitted, which checks that A's 200 did not commit. After B commits, M reads 300.
Operational advice: confirm the owner and business impact before terminating a backend. The session-end contract rolls back an open transaction, but that can discard needed work and cannot reverse already completed external effects. This schedule does not establish that killing an arbitrary production blocker is safe.