pg_stat_activity and pg_stat_statements

See who is connected and what runs now (pg_stat_activity, query_activity(), GET /api/sessions), stop a statement or close a connection (pg_cancel_backend, pg_terminate_backend, CANCEL QUERY, DELETE /api/sessions/<pid>), close idle and vanished clients (idle_session_timeout, tcp_keepalives_*), and see the totals of every statement shape (pg_stat_statements).

• Category: Postgres protocol
• Keywords: query_activity, CANCEL QUERY, cancel_query, pg_sleep, idle_session_timeout, client_connection_check_interval, tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count, keepalive, 57P01, 57P05, 57014, /api/sessions, CLIENT LIST, CLIENT KILL, kill session, pg_stat_activity, pg_stat_statements, pg_stat_statements_reset, pg_cancel_backend, pg_terminate_backend, pg_backend_pid, running queries, running statements, idle in transaction, wait_event, wait_event_type, kill query, kill connection, terminate connection, statement statistics, query statistics, slow statements, query totals, --stat-statements, KDB_STAT_STATEMENTS, queryid, mean_exec_time, total_exec_time, 42501
• See also: Protection - timeouts, limits, safe updates, read-only (cancel), EXPLAIN (explain-analyze), Health, info, check and backup over HTTP (slow-queries), Monitor panel (memory, processor, caches), PostgreSQL protocol (port 8832), Sessions and cursors over HTTP

These are the views and functions PostgreSQL tools use to watch a server. They work the same over the Postgres port and over HTTP (POST /exec), and they show both kinds of client.

kdb sql and kdb shell have no server around them: there the views are empty and the functions answer false.

pg_stat_activity

Syntax:

SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
[WHERE pid <> pg_backend_pid()]

The columns are PostgreSQL 14's, in its order.

• Rights: an admin (and the --token) sees every row in full. Other users see every row, but only their own rows' details: for another user's backend query is <insufficient privilege> and the times, state, address and wait columns are NULL, as in PostgreSQL.
• Notes:
  • An HTTP session (POST /session) is one backend from its start to its close. An HTTP request without a session is listed only while it runs.
  • datid and usesysid are the oids pg_database and pg_roles show; use datname and usename.
  • The same list, as JSON, is in GET /api/stats (running), which the console's Monitor panel shows.

Example: Without a server (kdb sql) nobody else is connected

SELECT count(*) AS others FROM pg_stat_activity WHERE pid <> pg_backend_pid();

Result:

(1 row)

pg_cancel_backend and pg_terminate_backend

Syntax:

pg_cancel_backend(pid)
pg_terminate_backend(pid [, timeout])

pg_cancel_backend stops the statement the backend runs now; the connection stays. pg_terminate_backend also closes it: a Postgres connection gets FATAL: terminating connection due to administrator command (SQLSTATE 57P01) and is closed at once (an open transaction is rolled back), an HTTP session is closed as DELETE /session closes it.

• Returns: true when a backend has that pid (also when it is idle and there was nothing to stop), false when none has, NULL for a NULL pid.
• Rights: an admin may stop every backend; other users only their own. Otherwise: must be a member of the role whose query is being canceled or member of pg_signal_backend (SQLSTATE 42501, HTTP 403).
• Notes:
  • The stopped statement gets canceling statement due to user request (SQLSTATE 57014, HTTP 408), as with a cancel request. It stops within milliseconds: the scans look at the stop flag once per batch of rows.
  • timeout is accepted and not waited for.
  • Over TLS a terminated idle connection is closed without the 57P01 message (two threads must not write one TLS session).

Example: No backend has this pid

SELECT pg_cancel_backend(12345) AS cancelled, pg_terminate_backend(12345) AS terminated;

Result:

(1 row)

Syntax:

-- Stop everything user etl has run for more than 5 minutes
SELECT pid, pg_cancel_backend(pid) FROM pg_stat_activity
WHERE usename = 'etl' AND state = 'active' AND query_start < now() - INTERVAL '5 minutes';

-- Close the connections left idle in a transaction
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction';

query_activity, CANCEL QUERY and cancel_query

Syntax:

SELECT query_id, worker_id, worker_pool, username, query_start, state_change, state, is_wal, query
FROM query_activity()
CANCEL QUERY query_id
cancel_query(query_id)

QuestDB's way to see and stop running statements. query_activity() has one row per statement running now, over HTTP and the Postgres port, itself included.

• Returns: CANCEL QUERY answers true or fails with query to cancel not found in registry [id=...] (SQLSTATE 42704) when no statement runs with that id. cancel_query(id) answers true when it stopped one, false when none runs with that id, NULL for NULL.
• Rights: an admin sees and stops every statement; other users see only their own and may stop only their own (otherwise SQLSTATE 42501, HTTP 403).
• Notes: the stopped statement ends with canceling statement due to user request (57014); its connection stays. pg_stat_activity's own query_id column is PostgreSQL's (a number for the statement's shape) and stays NULL.

Example: Without a server (kdb sql) nothing else runs

SELECT count(*) AS running FROM query_activity();

Result:

(1 row)

Example: No statement runs with this id

SELECT cancel_query(123456) AS cancelled;

Result:

(1 row)

Syntax:

-- Stop every statement that has run for more than a minute
SELECT query_id, cancel_query(query_id) FROM query_activity()
WHERE query_start < now() - INTERVAL '1 minute' AND query NOT LIKE '%query_activity%';

pg_sleep

Syntax:

pg_sleep(seconds)

Waits that many seconds (a fraction is fine), then returns an empty value, as PostgreSQL's void. A cancel, pg_terminate_backend, CANCEL QUERY or the statement timeout stops it within about 10 ms. NULL or 0 returns at once.

Example: A short wait

SELECT pg_sleep(0.01) IS NOT NULL AS waited;

Result:

(1 row)

Idle and vanished clients

Syntax:

SET idle_session_timeout = '10min'             -- 0 = off (the default)
SET client_connection_check_interval = '5s'    -- 0 = off (the default)
SET tcp_keepalives_idle = 60                   -- seconds; 0 = the server's
SET tcp_keepalives_interval = 10
SET tcp_keepalives_count = 5
kdb serve --tcp-keepalives-idle 60 --tcp-keepalives-interval 10 --tcp-keepalives-count 5

PostgreSQL 14's settings, for one Postgres connection.

• Notes:
  • Keepalive is on for every HTTP and Postgres connection, as in PostgreSQL, so a client that vanished without closing (a laptop shut, a NAT that forgot it) is noticed and its locks are given back. The server flags set the timings for both ports.
  • A wrong value is refused with SQLSTATE 22023 and changes nothing. Idle and interval go up to 32767 s, the count up to 127.
  • The connections the server closed are in the activity log (GET /api/activity, kind session).
  • These are settings of the Postgres port. SHOW idle_session_timeout there answers 0 until it is set, then as PostgreSQL writes it (2s, 1min).

GET /api/sessions and DELETE /api/sessions/<pid>

Syntax:

GET /api/sessions
GET /api/activity?sessions=1
DELETE /api/sessions/<pid>

GET /api/sessions is pg_stat_activity as JSON: every Postgres connection, HTTP session and running HTTP request, with pid, protocol (pg or http), user, database, application_name, client_addr, state, wait_event_type, wait_event, query, the times in Unix milliseconds (backend_start_ms, xact_start_ms, query_start_ms, state_change_ms) and query_id (the running statement's, for CANCEL QUERY; null when idle). GET /api/activity?sessions=1 adds the same list to the activity log's answer, so one poll shows what happened and what runs now.

DELETE /api/sessions/<pid> is pg_terminate_backend(<pid>): the statement stops, an open transaction rolls back, a Postgres client gets 57P01. It answers {"pid":..,"terminated":true}, 404 when no session has that pid, 403 when it is another user's and you are not an admin, 400 for a pid that is not a number.

Syntax:

curl -H "Authorization: Bearer $KDB_TOKEN" http://localhost:9900/api/sessions
curl -X DELETE -H "Authorization: Bearer $KDB_TOKEN" http://localhost:9900/api/sessions/42

pg_stat_statements

Syntax:

SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10
pg_stat_statements_reset([userid, dbid, queryid])
kdb serve --stat-statements on|off          (env KDB_STAT_STATEMENTS; default on)

The totals of every statement shape the server ran: the statement with each literal replaced by $1, $2, ..., so WHERE id = 5 and WHERE id = 7 count together. Spacing, comments and the case of words do not make a new shape. No extension needs to be created.

• Returns: one row per user, database and shape.
• Notes:
  • Only statements that ended without an error count, as in PostgreSQL. The console's own polling does not count.
  • One HTTP request is one statement, even when it holds several.
  • At most 5000 shapes are kept (PostgreSQL's pg_stat_statements.max). When a new one comes, the 5% least-called make room. The totals are kept in memory: a restart starts them again. Statements a procedure or trigger runs count in the CALL or write that ran them (pg_stat_statements.track is top).
  • It costs a few hundred nanoseconds per statement: the first 16 KB of the text are read once for the shape. --stat-statements off switches it off; the view is then empty.
  • Other users' shapes show <insufficient privilege> instead of the text to a user who is not an admin.
  • pg_stat_statements_reset() needs an admin. With a queryid (the third argument, 0 = all) only that shape starts again.

Example: Without a server (kdb sql) nothing is counted

SELECT count(*) AS shapes FROM pg_stat_statements;

Result:

(1 row)