Skip to Content
SQL ReferenceSystem Views

System Views

KalamDB exposes computed system.* views for metadata and runtime observability. Use session and transaction views to diagnose active clients and explicit transactions.

system.* access follows the SYSTEM table rules described in /docs/server/architecture/table-types.

system.sessions

system.sessions shows active native PGWire and extension bridge sessions tracked by the server.

Its origin distinguishes the connection transport:

  • Native PostgreSQL connections use wire_protocol; extension bridge sessions use extension_bridge.
  • Native /v1/api/sql requests do not create rows here.
  • The CLI \sessions command runs this view directly.

Common query

SQL
SELECT *FROM system.sessionsORDER BY last_seen_at DESC, session_id;

Active-transaction query

SQL
SELECT session_id, transaction_id, transaction_state, transaction_has_writesFROM system.sessionsWHERE transaction_id IS NOT NULLORDER BY last_seen_at DESC, session_id;

Columns

ColumnMeaning
session_idServer-side session identifier.
originwire_protocol or extension_bridge.
authenticated_user_idIdentity associated with the connection.
backend_pidParsed PostgreSQL backend PID when the session id uses the pg-<pid>-... form.
current_schemaCurrent schema associated with the session.
stateSession state, similar to PostgreSQL pg_stat_activity semantics.
transaction_idActive remote transaction id, if one is open.
transaction_stateCurrent remote transaction lifecycle state.
transaction_has_writesWhether the active transaction has staged writes.
client_addrObserved client socket address.
transportTransport used by the session.
opened_atWhen the server first observed the session.
last_seen_atMost recent session activity timestamp.
last_methodMost recent gRPC method observed for the session.

system.transactions

system.transactions shows active explicit transactions across all server origins.

Use it to observe transaction state regardless of whether the transaction came from pg_kalam or native KalamDB SQL.

  • Common origin values include PgRpc for the PostgreSQL extension and SqlBatch for native /v1/api/sql execution.
  • Rows disappear immediately after COMMIT, ROLLBACK, timeout cleanup, or request-end cleanup.

Common query

SQL
SELECT transaction_id, owner_id, origin, state, write_countFROM system.transactionsORDER BY origin, transaction_id;

Capacity-oriented query

SQL
SELECT transaction_id, origin, state, age_ms, idle_ms, write_bytes, touched_tables_countFROM system.transactionsORDER BY age_ms DESC;

Columns

ColumnMeaning
transaction_idCanonical explicit transaction identifier.
owner_idHuman-readable owner identifier, such as a pg session id or request-scoped SQL owner id.
originSource of the transaction, such as PgRpc or SqlBatch.
stateCurrent lifecycle state.
age_msTransaction age in milliseconds.
idle_msMilliseconds since the transaction last performed work.
write_countNumber of staged mutations currently buffered.
write_bytesApproximate in-memory size of the staged write set.
touched_tables_countNumber of tables referenced by the transaction.
snapshot_commit_seqCommitted snapshot boundary captured at BEGIN.

Key Distinction

  • system.sessions shows live native PGWire and extension bridge sessions.
  • system.transactions shows active explicit transactions across all supported origins.
  • A native /v1/api/sql transaction appears in system.transactions, but not in system.sessions.
Types & EnumsHTTP API