PostgreSQL Extension SQL Syntax

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

The key implementation detail is that the extension exposes two different table-definition paths:

1. CREATE TABLE ... USING kalamdb The shorthand authoring path backed by the DDL hook. This currently assumes the default server name kalam_server.

2. CREATE FOREIGN TABLE ... SERVER <name> The explicit PostgreSQL FDW path. Use this when you need a non-default server name or want to mirror a relation directly.

Extension Metadata

Install and verify the extension:

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

Current helper functions:

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

kalam_compiled_mode() should currently return remote.

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.

Point host and port at KalamDB’s shared RPC listener, not the HTTP API port.

CREATE TABLE ... USING kalamdb

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. creates the local foreign table surface and 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.
• This shorthand path currently depends on the DDL preload hook and the default server name kalam_server.
• The resulting PostgreSQL object is a foreign table, so follow-up DDL should use ALTER FOREIGN TABLE and DROP FOREIGN TABLE.

CREATE FOREIGN TABLE ... SERVER ...

Use explicit foreign tables when you need a non-default server or want to mirror a remote relation directly:

CREATE SERVER kalam_eu
  FOREIGN DATA WRAPPER pg_kalam
  OPTIONS (
    host '10.0.0.25',
    port '9188',
    auth_header 'Bearer <token>'
  );
 
CREATE FOREIGN TABLE app.audit_log (
  id BIGINT,
  action TEXT,
  payload JSONB
) SERVER kalam_eu
OPTIONS (
  table_type 'shared'
);

Current mapping rules:

• The local PostgreSQL schema is the remote namespace.
• The local PostgreSQL relation name is the remote table name.
• OPTIONS (namespace, table) are not the runtime source of truth for the current scan and modify paths.
• If a remote column is FILE, declare the local PostgreSQL column as JSONB.

JSON and JSONB

pg_kalam maps PostgreSQL JSON and JSONB authoring types to KalamDB JSON. On the CREATE TABLE ... USING kalamdb path, the local PostgreSQL column keeps the type you declared, so use JSONB when you want PostgreSQL’s richer local JSON operator surface. FILE is the special case: it is always mirrored locally as JSONB.

CREATE TABLE app.events (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  payload JSONB NOT NULL
) USING kalamdb WITH (
  type = 'shared'
);
 
SELECT payload->>'kind' AS kind
FROM app.events
WHERE payload ? 'kind';

That syntax compatibility is the contract; PostgreSQL may still evaluate the JSONB expression locally on the mirrored value unless you explicitly route the statement through kalam_exec(...).

When you do send SQL through kalam_exec(...), KalamDB itself supports the same common PostgreSQL-style JSON operators:

SELECT kalam_exec($$
  SELECT
    payload->>'kind' AS kind,
    payload->'metadata' AS metadata
  FROM app.events
  WHERE payload ? 'kind'
$$);

KalamDB-side JSON helper functions are also available, including json_get, json_as_text, json_contains, json_length, json_object_keys, and typed extractors such as json_get_int, json_get_float, and json_get_bool.

Current scope:

• KalamDB directly plans ->, ->>, and ? today.
• Do not assume full PostgreSQL jsonb operator parity for operators such as #>, #>>, or @> yet.

FILE

FILE is the main PostgreSQL extension-specific datatype shortcut.

CREATE TABLE app.assets (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  attachment FILE
) USING kalamdb WITH (
  type = 'shared'
);
 
SELECT attachment->>'name', attachment->>'mime'
FROM app.assets;

Current FILE behavior:

• The remote KalamDB schema keeps the FILE logical type.
• The local PostgreSQL surface rewrites that column to JSONB.
• PostgreSQL reads and writes the serialized FileRef JSON representation.
• Binary upload and file lifecycle usually happen through KalamLink or another file-aware KalamDB client, not through a raw PostgreSQL BYTEA upload path.

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.
• _deleted is reserved but is not auto-injected into the current PostgreSQL relation 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.
• It uses the default foreign server kalam_server.
• It is revoked from PUBLIC.
• It does not currently mirror PostgreSQL search_path or current schema automatically, so schema-qualify the statement.

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;

In practice, kalam.user_id is a PostgreSQL SUSET setting, so the executing role must be allowed to change SUSET parameters.

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.

Row Identity for UPDATE and DELETE

The current FDW modify path uses the first non-system column as its row-identity heuristic.

Practical guidance:

• Keep the primary key as the first user column in the table definition.
• If you are mirroring an existing remote table and expect heavy UPDATE or DELETE use, validate the behavior against your exact schema before depending on it in production.

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
• Data Type Conversions
• Status & Limits