Skip to Content
SQL ReferenceSystem Views

System Views

KalamDB exposes computed system.* views for metadata and runtime observability. This page focuses on the new session and transaction views added for explicit transaction support.

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

system.sessions

system.sessions shows active PostgreSQL gRPC bridge sessions tracked by the server.

It is intentionally pg-session-focused:

  • pg_kalam sessions appear here while they are active.
  • 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 pg bridge session identifier.
backend_pidParsed PostgreSQL backend PID when the session id uses the pg-<pid>-... form.
current_schemaCurrent schema reported by the pg extension 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 for the gRPC session.
transportTransport used by the session.
opened_atWhen the server first observed the session.
last_seen_atMost recent RPC 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 PostgreSQL bridge sessions only.
  • 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.
Last updated on
Query Data (SELECT & JOIN)FILE Datatype