PostgreSQL Extension SQL Syntax

This page documents the current SQL surface for the pg_kalam PostgreSQL extension.

Extension Metadata

Install and verify the extension:

CREATE EXTENSION IF NOT EXISTS pg_kalam;
 
SELECT kalam_version(), kalam_compiled_mode();

Foreign Server Syntax

Create the PostgreSQL foreign server that points at KalamDB:

CREATE SERVER IF NOT EXISTS kalam_server
  FOREIGN DATA WRAPPER pg_kalam
  OPTIONS (
    host '127.0.0.1',
    port '9188',
    auth_header 'Bearer <token>'
  );

Update it with standard PostgreSQL FDW server syntax:

ALTER SERVER kalam_server OPTIONS (SET host 'kalamdb.internal', SET port '9188');
ALTER SERVER kalam_server OPTIONS (ADD auth_header 'Bearer <replacement-token>');
ALTER SERVER kalam_server OPTIONS (SET timeout '5000');
ALTER SERVER kalam_server OPTIONS (DROP auth_header);

Drop it when you no longer need the bridge:

DROP SERVER kalam_server CASCADE;

Supported Server Options

If you set either client_cert or client_key, you must set both.

Table Creation Syntax

The public table-definition syntax is CREATE TABLE ... USING kalamdb:

CREATE TABLE app.orders (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  customer_id TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

This does three things:

1. resolves the PostgreSQL schema and table name into the mirrored KalamDB namespace and table,
2. creates the target namespace and KalamDB table if needed,
3. injects the PostgreSQL-visible system columns that pg_kalam manages locally.

Shared table example

CREATE TABLE app.shared_items (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  title TEXT NOT NULL,
  value INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

User table example

SET kalam.user_id = 'user-alice';
 
CREATE TABLE app.profiles (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  name TEXT,
  age INTEGER
) USING kalamdb WITH (
  type = 'user'
);

Stream table example

CREATE TABLE app.events (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  event_type TEXT NOT NULL,
  payload TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'stream',
  ttl_seconds = '3600'
);

Notes for USING kalamdb

• USING kalamdb selects the extension-managed PostgreSQL authoring path.
• WITH (type = 'shared' | 'user' | 'stream') selects the KalamDB table type.
• PRIMARY KEY and DEFAULT SNOWFLAKE_ID() are preserved in KalamDB.
• SERIAL, BIGSERIAL, SMALLSERIAL, and GENERATED ... AS IDENTITY are normalized into integer columns with DEFAULT SNOWFLAKE_ID() before the KalamDB DDL is sent.
• Additional WITH (...) options are forwarded to the mirrored KalamDB CREATE TABLE statement.

System Columns

pg_kalam manages system columns itself. Do not declare them explicitly in CREATE TABLE ... USING kalamdb.

Current rules:

• CREATE TABLE ... USING kalamdb (..., _seq BIGINT, ...) is rejected. The same applies to _userid and _deleted.
• _seq is a read-only metadata column. Query it, sort by it, or return it to clients, but do not treat it as an application-managed field.
• For user tables, omitting _userid on INSERT uses the current kalam.user_id session value.
• For user tables, including _userid on INSERT overrides the session value for that inserted row batch.
• In a multi-row INSERT, all rows must use the same _userid value.
• UPDATE and DELETE continue to use the session’s kalam.user_id scope. Reassigning ownership with UPDATE ... SET _userid = ... is not in the supported surface.

Example:

SET kalam.user_id = 'user-alice';
 
INSERT INTO app.profiles (id, name, age)
VALUES (SNOWFLAKE_ID(), 'Alice', 30);
 
INSERT INTO app.profiles (id, name, age, _userid)
VALUES (SNOWFLAKE_ID(), 'Bob', 41, 'user-bob');
 
SELECT id, name, _userid, _seq
FROM app.profiles
ORDER BY _seq;

Forwarded Table Options

Any supported KalamDB table option can be authored from PostgreSQL with the table WITH (...) clause.

That means PostgreSQL can act as the entry point for KalamDB-native table definitions as long as the resulting table options are valid for KalamDB once the extension forwards them.

Common forwarded WITH (...) values

Direct Statement Execution

To pass any statement directly to KalamDB, use kalam_exec(...):

SELECT kalam_exec('ALTER TABLE app.orders ADD COLUMN priority INTEGER DEFAULT 0');
 
SELECT kalam_exec('DROP TABLE app.orders');
 
SELECT kalam_exec('SELECT * FROM system.tables');

kalam_exec(...) sends the SQL string to the KalamDB server as-is.

• For SELECT statements, it returns a JSON array string.
• For DDL and DML statements, it returns a JSON string message.

If you were expecting a schema-qualified helper such as kalam.exec(...), the currently exposed PostgreSQL function name is kalam_exec(...).

Query and Mutation Syntax

Select

SELECT id, title, value
FROM app.shared_items
WHERE value >= 10
ORDER BY id;

Insert

INSERT INTO app.shared_items (id, title, value)
VALUES (SNOWFLAKE_ID(), 'Alpha', 10), (SNOWFLAKE_ID(), 'Beta', 20);

_seq is visible for reads but is not a writable application column.

Update

UPDATE app.shared_items
SET value = 25
WHERE id = 2;

Delete

DELETE FROM app.shared_items
WHERE id = 1;

User-scoped DML

SET kalam.user_id = 'user-alice';
 
INSERT INTO app.profiles (id, name, age)
VALUES ('p1', 'Alice', 30);
 
INSERT INTO app.profiles (id, name, age, _userid)
VALUES ('p2', 'Bob', 41, 'user-bob');
 
SELECT id, name, age, _userid, _seq
FROM app.profiles;
 
UPDATE app.profiles
SET age = 31
WHERE id = 'p1';
 
DELETE FROM app.profiles
WHERE id = 'p1';
 
RESET kalam.user_id;

Explicit Transaction Syntax

The PostgreSQL extension supports explicit transaction control for Kalam-backed tables:

BEGIN;
START TRANSACTION;
 
COMMIT;
COMMIT WORK;
 
ROLLBACK;
ROLLBACK WORK;

Commit example

BEGIN;
 
INSERT INTO app.shared_items (id, title, value)
VALUES (SNOWFLAKE_ID(), 'Alpha', 10);
 
UPDATE app.shared_items
SET value = 25
WHERE title = 'Alpha';
 
COMMIT;

Read-your-writes and rollback example

BEGIN;
 
INSERT INTO app.shared_items (id, title, value)
VALUES (SNOWFLAKE_ID(), 'Draft', 99);
 
SELECT id, title, value
FROM app.shared_items
WHERE title = 'Draft';
 
ROLLBACK;

Within the same PostgreSQL transaction, reads see the staged writes from that transaction. On COMMIT, the staged writes are made durable in KalamDB. On ROLLBACK, they are discarded.

Transaction observability

Use direct KalamDB SQL or kalam_exec(...) when you want to inspect the server-side session and transaction views from PostgreSQL.

Example via kalam_exec(...) for active pg bridge sessions:

SELECT kalam_exec(
  'SELECT session_id, transaction_id, transaction_state, transaction_has_writes
   FROM system.sessions
   WHERE transaction_id IS NOT NULL
   ORDER BY last_seen_at DESC, session_id'
);

Example via kalam_exec(...) for active explicit transactions across pg and native KalamDB SQL:

SELECT kalam_exec(
  'SELECT transaction_id, owner_id, origin, state, write_count
   FROM system.transactions
   ORDER BY origin, transaction_id'
);

Current limits inside explicit transactions

• Shared and user table DML are supported.
• Stream-table writes are rejected inside explicit transactions.
• DDL inside explicit transactions is not supported.
• Savepoints and PostgreSQL subtransactions are not yet supported.

Extension Helper Functions

Session and metadata helpers

SELECT kalam_version();
SELECT kalam_compiled_mode();
SELECT kalam_user_id();
SELECT kalam_user_id_guc_name();

Snowflake ID generation

SELECT SNOWFLAKE_ID();

Use SNOWFLAKE_ID() when you want PostgreSQL-side inserts to generate KalamDB-compatible IDs.

Not Yet Supported

IMPORT FOREIGN SCHEMA is not yet supported in the current remote mode:

IMPORT FOREIGN SCHEMA app FROM SERVER kalam_server INTO app;

For now, use CREATE TABLE ... USING kalamdb for PostgreSQL-side authoring or kalam_exec(...) for direct server execution.

Related

• Getting Started
• Architecture
• Status & Limits