Skip to main content

Administration Statements

DELETE​

DELETE FROM <table> WHERE entity = '<name>';

Mutable tables only. Writes a tombstone (versioned, timestamped now): the entity disappears from reads immediately and its versions are physically dropped at the next merge. Re-inserting the entity later with a newer ts resurrects it.

Exactly the WHERE entity = ... form is supported — no ranged or predicate deletes. On immutable tables, data removal is retention, not DELETE.

On a rows table the verb is DELETE FROM <table> WHERE xid = '...' [AND x_rev = N] and it is soft: the row leaves every normal read but its trace stays for the sync feed until VACUUM purges it.

On a kv table the verb is DELETE FROM <table> WHERE key = '...' — DEL: the key leaves every read immediately, and the tombstone is physically dropped at the next merge.

CHECKPOINT​

CHECKPOINT;

Forces the delta into columnar, seals segments, drops a CHECKPOINT marker file. The backup contract: after it returns, a plain file copy of the database directory is a consistent snapshot.

DROP TABLE​

DROP TABLE telemetry;

Removes the table — declaration and all its data — for either kind. The table id is never reused, so a later table with the same name starts empty. Any user of the database can run it (the same rule as CREATE); the console asks for confirmation.

TRUNCATE TABLE​

TRUNCATE TABLE dtc;   -- TABLE is optional

Empties a table's data while keeping its declaration — any family. The schema stays; snapshots stay too (their hard links keep a pre-truncate copy alive, so a snapshot taken before the TRUNCATE still restores). On a rows table the sync sequence and the vacuum horizon restart, so feed clients resync in full. This is the truncate-and-load idiom: empty, bulk-INSERT, and the table is rebuilt without ever dropping its schema.

PROFILE​

PROFILE SELECT avg(speed) FROM telemetry WHERE entity = 'veh-1' SAMPLE BY 5 m;
-- level | span       | elapsed_ms | counters

Wraps any single statement: runs it with stage spans on and answers with the span tree (depth-first; level is the indentation) instead of the statement's rows. The inner statement's permissions and effects apply unchanged — a PROFILE INSERT really inserts. Independently, any statement slower than the config's slow_statement threshold (default 1 s, 0 = off) logs as a WARN with its duration, user and statement head — the outlier flags itself, PROFILE explains it.

SHOW TABLES​

SHOW TABLES;   -- table, kind

One row per table with its kind (timeseries, rows or kv). There is no pg_catalog; SHOW TABLES and SHOW COLUMNS are the honest substitute.

As an admin, name the database instead:

SHOW TABLES FROM acme;   -- table, kind (admin-only)

Table names are catalog metadata — management surface, so the admin console can browse a node without borrowing tenant credentials.

To work inside a database, an admin steps into it explicitly — the pg-wire takes the startup database parameter (psql -U xcon -d acme) — and has full rights there: DDL, writes, reads, the migration journal. An unbound admin session stays node-scope and refuses data statements. Database users never cross: naming a foreign database is refused at the door.

SHOW COLUMNS​

SHOW COLUMNS FROM telemetry;   -- column, type, fill, staleness_ms, implicit

The table's shape: the implicit key (entity+ts; xid on a rows table, key on a kv table) plus every column with its read-time mechanics.

SHOW LOGS / SHOW SESSIONS (admin)​

SHOW LOGS LIMIT 200;   -- ts, level, message
SHOW SESSIONS;         -- id, protocol, user, database, address, connected_at, queries, last_query

The operator's window, admin-only. SHOW LOGS reads the node's in-memory event ring (~1000 entries): connections, failed logins, every mutating management statement as an audit line stamped with the acting user, dropped ILP lines. The same events live in two more places — durably in <root>/node.log (append-only, rotated once at 8 MB, copied along with backups), and live over SSE at GET /logs/stream?since=<seq> (admin basic auth; replays the ring past your cursor, then pushes). SHOW SESSIONS lists live pg-wire and ILP connections. The console follows the stream in a pinned tab (server right-click → Server logs) and polls Sessions on an interval.

Node management​

Two management scopes, two bits — no role system:

  • Admins manage databases, other admins and every database user. The first admin comes from the config file (the postgres/sa pattern); further admins are created over SQL.
  • Owners are database users that additionally manage the users of their own database — delegation for the tenant, invisible to others.

As an admin (the session is bound to no database):

SHOW DATABASES;
SHOW TABLES FROM acme;                            -- a database's catalog, not its data
CREATE DATABASE acme;                             -- names: [a-z0-9][a-z0-9_-]{0,63}
DROP DATABASE acme;                               -- deletes the data too

SHOW USERS;                     -- user, database, admin, owner, active, createdb, dropdb
CREATE USER boss PASSWORD 's3cret' DATABASE acme OWNER;
                                -- user names: [a-z0-9][a-z0-9_]{0,63}; upper case folds to lower
CREATE USER acme_rw PASSWORD 's3cret' DATABASE acme;
CREATE USER ops PASSWORD 's3cret' ADMIN CREATEDB; -- safety switches: CREATEDB, DROPDB
ALTER USER acme_rw PASSWORD 'rotated';
ALTER USER acme_rw DISABLE;                       -- keeps the account, refuses its logins
ALTER USER acme_rw ENABLE;
ALTER USER ops DROPDB;                            -- flip a switch (NOCREATEDB/NODROPDB revoke)
DROP USER acme_rw;

CREATE DATABASE needs the admin's CREATEDB switch, DROP DATABASE its DROPDB switch — accident guards, not a privilege hierarchy: any admin can flip another admin's switch, and the config-file admin holds both, always.

As an owner, connected to the own database (which is therefore implicit):

CREATE USER dashboard PASSWORD 'p' OWNER;
CREATE USER device17 PASSWORD 'p';
SHOW USERS;                                       -- this database only
ALTER USER device17 PASSWORD 'p2';
ALTER USER device17 DISABLE;                      -- e.g. a leaked device credential
DROP USER device17;

Any database user's SHOW DATABASES answers "what can I see": its own database, one row.

Guards: CREATE USER requires the database to exist and refuses an existing name (no silent overwrite); DROP DATABASE closes the database first, in-flight queries on it fail; nobody drops or disables themselves; any user may ALTER USER its own password; the config-file admin is out of SQL's reach entirely (the config file manages it, and would silently reapply itself at the next boot anyway).