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:

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

Current helper functions:

1SELECT kalam_version();2SELECT kalam_compiled_mode();3SELECT kalam_user_id();4SELECT kalam_user_id_guc_name();5SELECT SNOWFLAKE_ID();

kalam_compiled_mode() should currently return remote.

Foreign Server Syntax

Create the PostgreSQL foreign server that points at KalamDB:

1CREATE SERVER IF NOT EXISTS kalam_server2  FOREIGN DATA WRAPPER pg_kalam3  OPTIONS (4    host '127.0.0.1',5    port '2910',6    auth_header 'Bearer <token>'7  );

Update it with standard PostgreSQL FDW server syntax:

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

Drop it when you no longer need the bridge:

1DROP 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:

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

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

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

User table example

1SET kalam.user_id = 'user-alice';2 3CREATE TABLE app.profiles (4  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),5  name TEXT,6  age INTEGER7) USING kalamdb WITH (8  type = 'user'9);

Stream table example

1CREATE TABLE app.events (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  event_type TEXT NOT NULL,4  payload TEXT,5  created_at TIMESTAMP DEFAULT NOW()6) USING kalamdb WITH (7  type = 'stream',8  ttl_seconds = '3600'9);

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:

1CREATE SERVER kalam_eu2  FOREIGN DATA WRAPPER pg_kalam3  OPTIONS (4    host '10.0.0.25',5    port '2910',6    auth_header 'Bearer <token>'7  );8 9CREATE FOREIGN TABLE app.audit_log (10  id BIGINT,11  action TEXT,12  payload JSONB13) SERVER kalam_eu14OPTIONS (15  table_type 'shared'16);

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.

1CREATE TABLE app.events (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  payload JSONB NOT NULL4) USING kalamdb WITH (5  type = 'shared'6);7 8SELECT payload->>'kind' AS kind9FROM app.events10WHERE 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:

1SELECT kalam_exec($$2  SELECT3    payload->>'kind' AS kind,4    payload->'metadata' AS metadata5  FROM app.events6  WHERE payload ? 'kind'7$$);

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.

1CREATE TABLE app.assets (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  attachment FILE4) USING kalamdb WITH (5  type = 'shared'6);7 8SELECT attachment->>'name', attachment->>'mime'9FROM 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:

1SET kalam.user_id = 'user-alice';2 3INSERT INTO app.profiles (id, name, age)4VALUES (SNOWFLAKE_ID(), 'Alice', 30);5 6INSERT INTO app.profiles (id, name, age, _userid)7VALUES (SNOWFLAKE_ID(), 'Bob', 41, 'user-bob');8 9SELECT id, name, _userid, _seq10FROM app.profiles11ORDER 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(...):

1SELECT kalam_exec('ALTER TABLE app.orders ADD COLUMN priority INTEGER DEFAULT 0');2 3SELECT kalam_exec('DROP TABLE app.orders');4 5SELECT 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

1SELECT id, title, value2FROM app.shared_items3WHERE value >= 104ORDER BY id;

Insert

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

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

Update

1UPDATE app.shared_items2SET value = 253WHERE id = 2;

Delete

1DELETE FROM app.shared_items2WHERE id = 1;

User-scoped DML

1SET kalam.user_id = 'user-alice';2 3INSERT INTO app.profiles (id, name, age)4VALUES ('p1', 'Alice', 30);5 6INSERT INTO app.profiles (id, name, age, _userid)7VALUES ('p2', 'Bob', 41, 'user-bob');8 9SELECT id, name, age, _userid, _seq10FROM app.profiles;11 12UPDATE app.profiles13SET age = 3114WHERE id = 'p1';15 16DELETE FROM app.profiles17WHERE id = 'p1';18 19RESET 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:

1BEGIN;2START TRANSACTION;3 4COMMIT;5COMMIT WORK;6 7ROLLBACK;8ROLLBACK WORK;

Commit example

1BEGIN;2 3INSERT INTO app.shared_items (id, title, value)4VALUES (SNOWFLAKE_ID(), 'Alpha', 10);5 6UPDATE app.shared_items7SET value = 258WHERE title = 'Alpha';9 10COMMIT;

Read-your-writes and rollback example

1BEGIN;2 3INSERT INTO app.shared_items (id, title, value)4VALUES (SNOWFLAKE_ID(), 'Draft', 99);5 6SELECT id, title, value7FROM app.shared_items8WHERE title = 'Draft';9 10ROLLBACK;

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:

1SELECT kalam_exec(2  'SELECT session_id, transaction_id, transaction_state, transaction_has_writes3   FROM system.sessions4   WHERE transaction_id IS NOT NULL5   ORDER BY last_seen_at DESC, session_id'6);

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

1SELECT kalam_exec(2  'SELECT transaction_id, owner_id, origin, state, write_count3   FROM system.transactions4   ORDER BY origin, transaction_id'5);

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:

1IMPORT 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