In temBoard's Activity view, how do you find the PostgreSQL session that is blocking others and end it, and what runs when you terminate it?
answer
- three tabs with counters
- Waiting first, then Blocking
- idle in transaction holding locks
- terminate, not cancel
basics
~20 sOpen the Waiting tab to see the ungranted locks, then the Blocking tab for sessions holding what they want, usually an old idle-in-transaction one; Terminate makes the agent run pg_terminate_backend(pid), ending that session and rolling back its transaction.
solid answer
~40 stemBoard's Activity page has **Running**, **Waiting** and **Blocking** tabs, refreshed every 2 seconds, with counters that turn amber when sessions wait. Waiting lists ungranted `pg_locks` entries with lock type, mode and relation; Blocking lists sessions holding a granted lock on the same transaction ID or relation. The usual culprit is the oldest row there, often `idle in transaction`: a client opened a transaction, took locks and went quiet. Check its user, client and query, and fix it at the source if you can. Selecting it and pressing Terminate makes the agent run `SELECT pg_terminate_backend(pid)` as its superuser, which ends the whole backend, rolls back its transaction and releases its locks; temBoard has no cancel-query button. The agent logs `Backend <pid> terminated` with your user name.
code
sql · 7 lines-- what temBoard's Terminate button runs, as the agent's superuser
SELECT pg_terminate_backend(48213) AS killed;
-- cross-check the exact blockers of a waiting backend before clicking
SELECT pid, state, xact_start, left(query, 60) AS query
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(48291));go deeper
Know the three tabs, Running, Waiting and Blocking, and that the Terminate button ends the selected session.
Explain where the columns come from (pg_stat_activity, pg_locks, /proc) and that Terminate means pg_terminate_backend, which rolls back the session's transaction.
Show judgment: treat the Blocking tab as a lead, confirm with pg_blocking_pids, find the owner of an idle in transaction session, and fix the cause rather than terminating forever.
Decide who may press Terminate across the fleet: Environment membership grants it, so access design is the real control over a destructive button.
## What the Activity page is For one instance, temBoard's **Activity** page shows the server's backends in three tabs: **Running**, **Waiting** and **Blocking**. It refreshes every **2 seconds**; you can pause it, filter by text, and hide states such as `idle`. The Waiting and Blocking tabs carry a counter badge that turns amber when it is not zero, which is usually the first sign of a lock problem. The agent caps each list at the **300** longest-running rows (since 8.2), so a storm of thousands of sessions does not flood the page. All of this comes from the agent on the database host, queried live when the page is open; none of it is stored in the repository. ## What each tab shows | Tab | Built from | Columns that matter | |---|---|---| | Running | `pg_stat_activity` (minus the agent's own backend) plus `/proc/<pid>` | pid, database, user, client, duration, state, a wait flag (Y when `wait_event_type` is `Lock`), CPU, memory, read/s, write/s, I/O wait, query | | Waiting | `pg_locks` rows with `granted = false`, joined to the session | lock type, mode, relation, duration, query | | Blocking | granted `pg_locks` rows on the same transaction ID, or on the same relation, that a waiter asked for | lock type, mode, relation, duration, state, query | The CPU, memory and I/O columns are temBoard's own addition: the agent reads each backend's `/proc` entry, which is one reason it must run on the database host. What each `pg_stat_activity` column and lock mode means belongs to the PostgreSQL monitoring and locking topics; here the point is how the console assembles them. ## Finding the blocker 1. The **Waiting** badge goes amber. Open the tab and note the lock type: `transactionid` usually means two sessions want the same row; `relation` usually means a table-level lock, typical of DDL or maintenance. 2. Open **Blocking** and sort by duration. The longest-held row is the usual suspect. 3. Look at its **state**. The UI highlights `idle in transaction`: a client began a transaction, took locks, and is now doing nothing, so the locks stay held until it commits, rolls back or disconnects. 4. Read the query text, user and client address to find the owner. In a public-library network that might be a catalogue-import job on one branch server that crashed between statements. 5. Prefer fixing the source (the job, the connection handling). If users are stuck now, select the session and press **Terminate**. ## What Terminate actually does - The UI sends the selected pids to the agent's `/activity/kill` endpoint; the agent runs `SELECT pg_terminate_backend(pid)` for each, as its superuser. - That ends the **whole backend process**: the client's connection is closed, its open transaction is rolled back, and its locks are released, so the waiters proceed. - temBoard offers **no cancel-query button**; to stop only the current statement and keep the session, you would run `pg_cancel_backend` yourself in psql. - The agent records an audit notification, `Backend <pid> terminated`, with the UI user's name. ## Reading it with care - The **Blocking** tab is a heuristic. Its relation match does not compare lock modes, so a session holding a harmless `ACCESS SHARE` lock on the same table can appear next to the real blocker. Before terminating anything important, confirm the chain with `pg_blocking_pids()` on the waiting pid. - **Duration** is measured from `query_start`. For an `idle in transaction` session that is the time since its last statement began, not the age of the transaction (`xact_start`). - A 2-second refresh means short waits flicker in and out; a counter that stays amber across several refreshes is the signal. - Terminating a connection that belongs to a pooler ends a server connection, not one end user; the pooler simply opens another. ## Who can press Terminate The button is available to any user who can manage the instance, which in temBoard 10 means a member of the instance's **Environment**. There is no separate, narrower permission for destructive actions, and since 8.0 there is no second login on the agent. For a small team that is convenient; for a larger one it means Environment membership is the real control over who may end sessions on a production server. The audit trail is temBoard's own notification list, which names the UI user for every terminated backend; PostgreSQL itself only sees the agent's superuser role ending the process. If you need a record outside temBoard, keep the server log's connection and disconnection lines as well.
- Why might temBoard's Blocking tab list a session that is not actually blocking anyone?For relation-level locks the agent matches any granted lock on the same table that a waiter wants, without checking whether the two lock modes conflict. A reader holding ACCESS SHARE can appear beside the real blocker. Confirm with pg_blocking_pids() on the waiting pid before you terminate.
- You only want to stop a runaway query, not drop the connection; can temBoard do that?No. The Activity page's only action is Terminate, which runs pg_terminate_backend and ends the session. To cancel just the running statement and keep the connection, run pg_cancel_backend(pid) from psql or another client.
- temBoard's Terminate button only cancels the current query and keeps the connection.
- Every row in the Blocking tab is guaranteed to block a waiting session.
- The duration column of an idle in transaction session shows the transaction's age.
- The Activity page reads its data from the history stored in the temBoard repository.
- An idle in transaction session holds no locks because it is not running anything.