SQL

#Overview

Chronos runs SQL itself, with its own parser and executor, and speaks the Postgres 16 wire protocol, so psql, drivers, ORMs and GUI tools connect as to Postgres. It aims to give the same answers and the same SQLSTATE codes; what it can’t do yet is an error (0A000 for a known feature, 42601 for syntax it doesn’t parse), never a silent wrong answer.

#Statements only Chronos has

#What works

• Statements: CREATE/ALTER/DROP TABLE, indexes (btree, unique, partial, expression, gin, gist, hnsw), INSERT (multi-row, … SELECT, ON CONFLICT DO NOTHING | DO UPDATE), SELECT, UPDATE, DELETE, TRUNCATE, RETURNING, COPY, EXPLAIN [ANALYZE], PREPARE/EXECUTE, views and materialized views, sequences, schemas and search_path, enums and domains, temporary tables, LISTEN/NOTIFY, CREATE TABLE AS, SELECT INTO.
• Queries: inner, left, right, full and cross joins, USING, NATURAL, lateral subqueries, WITH and WITH RECURSIVE, window functions, GROUP BY with ROLLUP, CUBE and GROUPING SETS, DISTINCT ON, FILTER, ordered-set aggregates, FETCH FIRST … WITH TIES, row comparisons for keyset pagination.
• Constraints: primary keys of one or several columns, NOT NULL, DEFAULT, CHECK, UNIQUE (incl. NULLS NOT DISTINCT), EXCLUDE, foreign keys with every ON DELETE/ON UPDATE action, deferrable foreign keys. All are checked again at every merge, on the merged result.
• Code: CREATE FUNCTION in SQL and PL/pgSQL, triggers (row and statement, BEFORE/AFTER/INSTEAD OF), DO blocks, RAISE, exception handling.
• Security: roles and privileges (GRANT, REVOKE, SET ROLE), row-level security policies.
• Functions: Postgres’s text, math, date, JSON/jsonb, array, regular expression (its own engine, checked on thousands of random patterns), formatting and statistical functions, with Postgres’s names, null rules and edge cases.

#Types

integer, bigint, smallint, double precision, real, numeric(p, s) (exact, up to 38 digits), text, varchar(n), char, uuid, boolean, bytea, json, jsonb, date, time, timestamp, timestamptz, interval, arrays of these, enums, domains, vector(n), tsvector/tsquery, inet/cidr/macaddr, citext, hstore, ltree, and PostGIS geometry/geography points. Any other type name is 42704, naming these.

Serial numbers and worlds

serial, identity and sequence numbers come from counters that belong to the database, not a world: rows inserted in two worlds never collide when both merge, and main may see gaps where a world took numbers. Use gen_random_uuid() for keys if gaps matter to you.

#Extensions

Extensions with C code can’t be loaded; these are built in, and CREATE EXTENSION for them is accepted:

#Transactions

• READ COMMITTED by default; REPEATABLE READ with row locks that wait as Postgres’s do; SERIALIZABLE refused (0A000).
• Savepoints, SELECT … FOR UPDATE | SHARE, advisory locks.
• World statements can’t run inside a transaction (25001). A transaction’s COMMIT isn’t a merge: merge checks and policies don’t run for it.
• Outside a transaction each statement commits on its own.

#Differences that bite

• Filtering on an unindexed column is answered from an in-memory search index of the table, which can take a lot of memory on big tables. Give columns you filter on a btree index.
• `ALTER TABLE … RENAME TO` and `SET SCHEMA` rewrite every row (about 6.5 s per million). Renaming a column, adding and dropping columns write only the catalog.
• Text sorts by bytes (as COLLATE "C") unless a column has an ICU collation.
• Enum columns reach clients typed `text`, and function signatures in the catalog show text for varchar parameters.
• `EXPLAIN` doesn’t show Postgres’s plans except for simple ones: don’t parse it.
• `hash` and `brin` indexes are btrees, and GIN indexes find rows only on tsvector and trigram expressions.
• `ADD CONSTRAINT … NOT VALID` checks existing rows where Postgres adds it unchecked.

#Known gaps

Each is an error, not a wrong answer:

• functions in C or any language but SQL and PL/pgSQL; extensions with C code beyond the built-in ones;
• CREATE ACCESS METHOD and custom operator classes;
• SERIALIZABLE isolation;
• replication (physical or logical), tablespaces, pg_hba.conf;
• ts_headline, ts_rank_cd; PostGIS beyond points; ST_Transform;
• MATCH PARTIAL foreign keys; deferrable unique constraints.

# in the dashboard's SQL editor: create world schema_test;psql "$(echo "$DATABASE_URL" | sed 's#/main?#/schema_test?#')" -f schema.sql 2>&1 | grep ERROR# then discard schema_test: main never saw it