Postgres compatibility

Where it stands (beta)

Chronos 0.2 is a beta. It aims to run what an application written for Postgres 16 sends, and to give the same answers; it isn't there yet, and this page says how far it is, as measured. Numbers are from October 2026, on the release this page ships with.

Measure	Result
Postgres 16's own regression suite (src/test/regress, 219 files run as scripts, output compared line by line with Postgres's expected output)	83.69% of the expected lines; 46 of 219 files exactly the same
Real databases moved in during the trials (chronos import and pg_dump replay), checked table by table with row counts and row checksums	7 of 7 with identical checksums: Chinook, Northwind, pgbench at 2M rows, Pagila (after 1 manual fix: DISTINCT inside a user-defined aggregate, since fixed), a synthetic schema of 50 constructs, a micro schema of 47 types, a Rails-style application, a multi-tenant schema with row-level security and LISTEN/NOTIFY
Differential tests against a real Postgres 16 (tests/pgdiff.rs)	468 tests, each statement giving the same rows or the same error class on both
Speed, same machine, pgbench-style	point reads at about 0.5× Postgres's rate; writes at a fraction of Postgres's rate. Not tuned yet; measure your own workload

Transactions: READ COMMITTED is the default, as in Postgres, and REPEATABLE READ is supported, with row locks that wait as Postgres's do. SERIALIZABLE is refused with 0A000 rather than given weaker guarantees under its name.

Biggest known gaps (each is an error, not a silent wrong answer, unless it says otherwise; the full list is in Known gaps):

• No C code: functions in C or any language but SQL and PL/pgSQL, and extensions that ship C code, aren't available. The extensions Chronos has are built in (vector, PostGIS points, citext, hstore, ltree, pg_trgm, fuzzystrmatch, unaccent, pgcrypto and the others listed).
• No custom index access methods (CREATE ACCESS METHOD); hash and brin indexes are btrees here, and GIN indexes find rows only on tsvector.
• EXPLAIN doesn't show Postgres's plans except for simple ones; don't parse it.
• Function signatures show text for varchar parameters in the catalog (\df, pg_get_function_arguments), and enum columns reach clients typed text (Types on the wire).
• An equality filter on a column with no B-tree index of its own (a UNIQUE constraint's column included) is answered from an in-memory search index of the table, built on first use and kept: it holds the table about 85 times over (150,000 rows of 18.5 MB of text took 1.57 GB), and up to 4 versions per table are kept as rows change, so a busy server can grow to several GB. Give columns you filter on a B-tree index (CREATE INDEX), or lower CHRONOS_WORK_MEM, which caps the tables it is built for. A real cap is planned for after the beta; see operations.
• ALTER TABLE ... RENAME TO and ALTER TABLE ... SET SCHEMA rewrite every row of the table, about 6.5 s per million rows, where Postgres changes only its catalog: run them in a maintenance window on big tables. A fix is designed for after the beta. (RENAME COLUMN, ADD/DROP COLUMN and most other ALTER TABLE changes write only the catalog.)
• Text sorts by bytes (as COLLATE "C") unless a column has an ICU collation; libc locales are mapped to ICU's, which order a few strings differently.
• History has a cost: Chronos keeps old row versions for time travel and undo. A new database keeps 1 hour of them (alter system set history_retention = '30 days' keeps more); one made by an earlier release keeps the 30 days it had.
• No replication (physical or logical), no pg_hba.conf, no tablespaces.

Moving a database in, and checking it came over: Migrating from Postgres.

How it's tested

chronos serve speaks the Postgres wire protocol, so PostgreSQL clients and drivers connect to it: psql, the Rust postgres crate, psycopg, pgvector's Python helpers. Chronos is not Postgres. It has its own SQL parser and executor (SQL), and the surface is what that page lists, no more. What it does cover is checked against a real Postgres by a differential suite, tests/pgdiff.rs. Its 468 tests run statement by statement on a fresh Chronos database and on a fresh schema in Postgres, and each statement must give the same rows, or fail on both with the same SQLSTATE class. Cells compare as text, except that numbers compare as numbers and t/f as true/false (the numeric tests compare the text exactly). Three more suites work the same way:

• tests/schemas.rs: schemas, regclass, the catalog, and the reflection queries SQLAlchemy 2.0 and Prisma send.
• tests/pgcompat.rs: 240 random app-style queries over 4,000 users and 12,000 orders. They must return the same rows, and where Postgres reads a table through an index, Chronos must not scan all of it.
• tests/copy.rs same_as_postgres: psql scripts with COPY data inline must print the same on both.

What that covers: the SQL. On the Chronos side pgdiff and pgcompat run in process, so the wire protocol is checked by the client tests below, not by the diff. Worlds and concurrency aren't diffed, because Postgres has neither to compare against. The suites need a Postgres at host=/tmp and skip when there's none. CI doesn't start one, so they run on developers' machines; the project runs them against Postgres 16. Postgres 16's own regression suite runs too (tests/regress_expected.sh, nightly in .github/workflows/regress.yml): the figures in Where it stands come from it.

Connecting

chronos serve mydb                                  # HTTP on 7070, Postgres protocol on 127.0.0.1:5433
psql postgres://127.0.0.1:5433/main               # the database name is the world

• Address: --pg 127.0.0.1:5433 is the default, and --pg off turns the port off. Any address but loopback needs --token (or CHRONOS_TOKEN); the server won't start without one.
• Database name = world. main, or any live world's name: postgres://127.0.0.1:5433/agent_7. An empty name, or one equal to the user name, means main, so a bare psql -h 127.0.0.1 -p 5433 lands on main. A world that doesn't exist fails with 3D000. main@2026-09-20 10:00 (or main@-1 hour) is main as it was then, read only (25006 on writes): psql "host=127.0.0.1 port=5433 dbname='main@-1 hour'". psql's \l lists the worlds as databases.
• User and password:

Server started with	User	Password
no --token (loopback only)	any name but a role with a password	none asked
--token T	any name but a role or an agent	T (28P01 otherwise)
any	an agent's name	that agent's token, proved with SCRAM: the session acts as the agent (agents). The server's token isn't an agent's password
any	a role made with CREATE ROLE ... PASSWORD	the role's password, on loopback without a token too (but see below): the session has the role's privileges; a NOLOGIN role is 28000 (once the password checks out), one past its VALID UNTIL 28P01, one without CONNECT on the world 42501, one past its CONNECTION LIMIT 53300
--token T	a role without a password	refused (28P01), as Postgres refuses it; without a token it needs none
--safe	not an agent	anything (with --token, T): the session is the agent guest (reads, forks, changes only its own worlds)
--safe --admin-token A	not an agent, nor a role with a password	A: the database's own user

A name that is no role made with CREATE ROLE (every login before roles existed, and every agent's) is a superuser, as it always was (Roles and privileges). So without --token, loopback is trust for every name: a role's password guards only logins under that role's own name, and any other name logs in without a password as a superuser that may SET SESSION AUTHORIZATION or SET ROLE to any role. For role passwords to protect anything, start the server with --token.

• Authentication method: chosen from the user name, as no pg_hba.conf exists:
  • SCRAM-SHA-256 (AuthenticationSASL, as Postgres 16 runs it) for a role with a password, against its verifier; for an agent, against the verifier CREATE AGENT made of its token (with the session's scram_iterations); and with --token for any other name, against verifiers the server makes in memory from the server's and admin tokens with a salt of that name's. No token crosses the wire. Over TLS SCRAM-SHA-256-PLUS is offered too, bound to the certificate (tls-server-end-point, hashed with the certificate's signature hash, SHA-256 for SHA-1 and MD5), so libpq's and tokio-postgres's channel_binding=require work. A certificate signed with EdDSA, whose signature has no hash of its own, gets plain SCRAM only (Postgres offers SCRAM-SHA-256-PLUS there too, and can't compute the binding). A client that says it could bind but thinks the server can't (y) while PLUS is offered is refused, as in Postgres.
  • MD5 (AuthenticationMD5Password) for a role whose password is kept as an md5 hash, as Postgres's md5 method does.
  • Cleartext (AuthenticationCleartextPassword) for an agent made before agents had SCRAM verifiers (by a Chronos before this one; only its token's hash is kept, so nothing could be made from it), once: its first login with its token keeps a verifier of it, and from then on it logs in with SCRAM. There's no token rotation; an agent made now has a verifier from the start. Also, without --token, with --admin-token (any password goes; the admin token lifts --safe). Without TLS such a password crosses the network in the clear, so use TLS off loopback.
  • Which names exist doesn't show. With --token, every name is asked for SCRAM with a 16-byte salt of its own, the same each time, and an iteration count: a role's or an agent's are its verifier's (4096 unless scram_iterations said otherwise when it was made), and any other name's (a name nobody has, the database's own user, a role without a password or past its VALID UNTIL) are 4096 and a salt made from the name and a secret the server draws when it starts, as Postgres's scram_mock_salt makes it. A disabled agent keeps its verifier's. A login that can't succeed goes through the whole exchange, as Postgres's mock authentication does, and gets 28P01. Nothing slow happens before the server's first reply; a name that logs in with a token has its verifiers made (a PBKDF2 per token) only once it has sent its proof, a few at a time and never kept, and every SCRAM login waits at that step as long as making them takes. So the time a login takes tells neither which names exist nor which logged in lately, but for differences of microseconds in reading the catalog before the first reply, as Postgres has too. What still shows: an agent made before verifiers is asked for a cleartext password until its first login, and a role kept as an md5 hash for MD5 (as under Postgres's md5 method); and Postgres keeps its mock secret in pg_control, where Chronos draws a new one at each start, so a name with no verifier gets a new salt after a restart where a role keeps its.
  • Refusals are Postgres's, sent as FATAL: 28P01 password authentication failed for user "..." (a role without a password, or past its VALID UNTIL, and a disabled agent go through SCRAM and get the same), 08P01 for a malformed SCRAM message, 28000 for a channel binding that doesn't match. No GSSAPI, SSPI, LDAP or client certificates.
  • An agent with a role of its name logs in as the agent, with its token (the role lends it privileges, not a password).
• TLS: chronos serve mydb --tls-cert cert.pem --tls-key key.pem makes every Postgres connection use TLS (1.2 or 1.3). sslmode=prefer (psql's default), require, verify-ca and verify-full use it (tested with verify-full): psql "postgres://localhost:5433/main?sslmode=verify-full&sslrootcert=cert.pem". sslmode=disable is refused with 28000 (this server requires TLS). Without the flags the server declines TLS, so prefer carries on in plain text and require fails. TLS starts only through the usual SSLRequest; Postgres 17's sslnegotiation=direct isn't supported. See operations.
• Worlds inside a connection: SWITCH WORLD name (or USE WORLD, USE BRANCH, or USE WORLD '<id>') moves this connection to another world. current_database() names the world the session is in. See Chronos-only SQL.

Clients

Evidence levels used below:

• Tested: run by the repo's test suite against a real chronos serve.
• Harness: run end to end by a benchmark harness in the repo, not by the tests.
• Queries tested: the library's own SQL is replayed in the tests, but the library itself wasn't run.
• Reported: the CHANGELOG says it ran, and nothing in the repo runs it.
• Protocol only: should work, given the protocol features below. Not tried.

psql

Tested: tests/pg.rs psql, psql_shows_views_and_sequences, a_query_stops_when_its_client_disconnects; tests/copy.rs psql_round_trip_is_byte_identical, a_failed_copy_leaves_nothing, same_as_postgres; tests/tls.rs https_and_postgres_over_tls.

psql postgres://127.0.0.1:5433/main

create table t (id int primary key, note text);
insert into t values (1, 'one'), (2, 'two');
\dt
\d t
\copy t to 't.csv' csv header
create world trial;
switch world trial;           -- this connection now works on trial
update t set note = 'uno' where id = 1;
diff;
merge world trial;           -- into main; the connection moves back to main

\d, \dt, \dv, \dm, \ds, \dn, \d name, \d+ view and \l work: Chronos recognises the catalog queries psql 16 sends (psql's commands). Ctrl-C sends a cancel request, which stops the statement (57014).

Rust: postgres crate

Tested: tests/pg.rs postgres_driver (extended protocol, binary parameters and results, prepared statements, SQLSTATEs), transactions_and_branches_over_the_wire, dates_over_the_wire, arrays_numeric_and_time_over_the_wire, token_is_the_password, agents_log_in_with_their_token, timeouts_and_cancel_requests_stop_a_query, listen_and_notify_over_the_wire; tests/copy.rs driver_copy_in_and_out; tests/safe.rs safe_mode_over_http_and_postgres. The benchmarks in examples/sql.rs use it too.

use postgres::{Client, NoTls};

let mut c = Client::connect("host=127.0.0.1 port=5433 user=me dbname=main", NoTls)?;
c.batch_execute("create table kv (k text primary key, v bigint)")?;
c.execute("insert into kv values ($1, $2)", &[&"x", &1i64])?;
let ids = c.query("select v from kv where k = any($1)", &[&vec!["x", "y"]])?;

// a world: switch this connection, or connect to it by name
c.batch_execute("create world trial; switch world trial; update kv set v = 99")?;
let mut on_trial = Client::connect("host=127.0.0.1 port=5433 user=me dbname=trial", NoTls)?;
let changes = c.query("diff", &[])?;          // table, id, change, before, after
c.batch_execute("merge world trial")?;

tokio-postgres is the same protocol code under the postgres crate, but only the postgres crate is tested.

Python: psycopg 3 with pgvector

Harness: bench/vector_dbs.py loads and queries 76,424 OpenAI embeddings through psycopg 3, with vectors sent in binary through pgvector's register_vector (the benchmarks). Not in the test suite.

import json, numpy as np, psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("postgresql://me@127.0.0.1:5433/main", autocommit=True)
conn.execute("create extension if not exists vector")    # accepted: vectors are built in
register_vector(conn)                                     # vectors travel in binary

conn.execute("create table if not exists items (id int primary key, embedding vector(3), payload jsonb)")
conn.execute("create index on items using hnsw (embedding vector_cosine_ops)")
conn.execute("insert into items values (%s, %s, %s)",
             (1, np.array([1, 0, 0], dtype=np.float32), json.dumps({"user_id": "u1"})))
rows = conn.execute(
    "select id from items where payload->>'user_id' = %s order by embedding <=> %s::vector limit 5",
    ("u1", np.array([1, 0.1, 0], dtype=np.float32))).fetchall()

conn.execute("create world trial")
conn.execute("switch world trial")                        # needs autocommit: see below

autocommit=True matters: without it psycopg opens a transaction before the first statement, and world statements are refused inside one (25001). register_vector looks the vector type up in pg_type, and Chronos answers that lookup (the type's id is 90000).

Mem0's pgvector store (psycopg 2 or 3)

Reported and queries tested: the CHANGELOG says Mem0's pgvector store runs unchanged with psycopg 2 and 3. tests/sql.rs pgvector replays the SQL it sends (information_schema.tables, CREATE EXTENSION, USING hnsw, ARRAY[...] vectors as psycopg2 inlines them, payload->>'user_id' filters), but nothing in the repo runs Mem0 itself.

from mem0 import Memory

memory = Memory.from_config({
    "vector_store": {
        "provider": "pgvector",
        "config": {"host": "127.0.0.1", "port": 5433, "dbname": "main", "user": "mem0",
                   "password": "", "collection_name": "memories", "embedding_model_dims": 1536, "hnsw": True},
    },
    # plus Mem0's own llm and embedder settings
})

Point dbname at a world to give each agent run its own memory. With hnsw, searches come from Chronos's search index (Vectors).

SQLAlchemy and Prisma

Queries tested: tests/schemas.rs sqlalchemy_introspection runs SQLAlchemy 2.0's Postgres reflection queries (tables, primary keys, unique constraints, foreign keys, checks, indexes, columns), and prisma_introspection runs Prisma's schema describer (namespaces, tables, constraints, columns, foreign keys, indexes, sequences, views). Both are diffed against Postgres. Neither ORM has been run against Chronos, so its connection setup, migrations and generated queries are unchecked.

from sqlalchemy import create_engine, inspect, text

engine = create_engine("postgresql+psycopg://me@127.0.0.1:5433/main")
with engine.connect() as c:
    print(inspect(c).get_table_names())
    c.execute(text("select * from orders where id = :id"), {"id": 1})

datasource db {
  provider = "postgresql"
  url      = "postgresql://me@127.0.0.1:5433/main"
}

Watch out for: CREATE EXTENSION of an extension that isn't built in, SERIALIZABLE (0A000), and enum columns, which reach clients typed text.

Node: pg (node-postgres)

Protocol only: not tested. The repo's TypeScript client (clients/typescript) uses the HTTP API, not the Postgres port.

import pg from "pg";

const client = new pg.Client("postgres://me@127.0.0.1:5433/main");
await client.connect();
const { rows } = await client.query("select id, name from users where id = any($1)", [[1, 2]]);
await client.query("create world trial");
await client.query("switch world trial");

node-postgres sends arrays as array text ('{1,2}'), which = ANY($1) takes (ANY, SOME and ALL).

Others

JDBC, Go (pgx, lib/pq), asyncpg, .NET and GUI tools: no evidence either way. Check the table below against what the client needs (SCRAM-SHA-256 authentication, the queries it sends on connect).

Protocol features

Feature	Status	Evidence
Simple query	Yes, text results	tests/pg.rs psql
Several statements in one simple Query	Yes, but each commits on its own: no implicit transaction (see Differences)	src/pg.rs simple
Extended query (Parse, Bind, Describe, Execute, Close, Sync, Flush)	Yes; named statements and portals	tests/pg.rs postgres_driver
Several statements in one Parse	Refused with 42601, as in Postgres	src/pg.rs parse
Parameter types	Inferred from use, or taken from Parse's type ids	tests/pg.rs postgres_driver
Binary values	Parameters and results: int2/4/8, float4/8, bool, text, json/jsonb, date, time, timestamp(tz), interval, numeric, vector, and 1-D arrays of those, in both directions (an array of arrays is sent as text). tsvector/tsquery results in binary, parameters as text only	tests/pg.rs postgres_driver, dates_over_the_wire, arrays_numeric_and_time_over_the_wire
Execute's row limit	Yes: that many rows, then PortalSuspended; the next Execute goes on (Cursors)	tests/cursors.rs
SQL cursors: DECLARE, FETCH, MOVE, CLOSE	Yes, over a snapshot kept in memory; psql's FETCH_COUNT and psycopg's named cursors use them	tests/pgdiff.rs cursors, tests/cursors.rs
SQL PREPARE / EXECUTE / DEALLOCATE	Yes, sharing names with the protocol's statements	tests/copy.rs driver_copy_in_and_out
COPY ... FROM STDIN / TO STDOUT	Text and CSV; FORMAT binary refused (0A000), server files and programs refused (42501)	tests/copy.rs
LISTEN / NOTIFY	Yes; idle connections are told at once	tests/pg.rs listen_and_notify_over_the_wire
Cancel requests	Yes, 57014	tests/pg.rs timeouts_and_cancel_requests_stop_a_query
Client disconnects mid-query	The statement stops	tests/pg.rs a_query_stops_when_its_client_disconnects
TLS (SSLRequest)	Yes, required on every connection once configured	tests/tls.rs
GSSAPI encryption	Declined; the client carries on in plain text	src/pg.rs startup
Authentication	SCRAM-SHA-256 (and SCRAM-SHA-256-PLUS over TLS), agents' tokens too, MD5 for a role kept as an md5 hash, cleartext once for an agent made before agents had verifiers; trust on loopback without a token (but under a role's name that has a password)	tests/scram.rs, tests/pg.rs token_is_the_password, agents_log_in_with_their_token
Transactions and savepoints	BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE, ROLLBACK TO; ReadyForQuery reports idle, in a transaction and failed	tests/pg.rs transactions_and_branches_over_the_wire
ParameterStatus	Sent once at startup: server_version 16.15, server_encoding and client_encoding UTF8, DateStyle ISO, MDY, TimeZone (UTC, or the zone the client asked for), integer_datetimes on, standard_conforming_strings on. TimeZone is sent again after a SET TIME ZONE changes it; the others aren't sent again	src/pg.rs startup
BackendKeyData	Yes, used by cancel requests	src/pg.rs startup
Errors and notices	ErrorResponse carries severity, SQLSTATE, message and, where there is one, DETAIL, HINT, POSITION (a syntax error's, so psql draws its caret) and the schema, table, column and constraint a violation names; notices carry severity, code and message	tests/pg.rs syntax_errors_carry_their_position, constraint_violations_carry_their_fields
RowDescription	Column name, type and type modifier; table oid and column number are always 0	src/pg.rs row_description
FunctionCall	Not supported (08P01)	src/pg.rs serve
Replication, CopyBoth	Not supported	src/pg.rs serve

SQL surface

Area	Status	See
SELECT, joins (inner, left, right, full, cross, USING), GROUP BY/HAVING, DISTINCT, ORDER BY/LIMIT/OFFSET	Supported	Joins, grouping and functions
Subqueries, UNION/INTERSECT/EXCEPT, WITH, WITH RECURSIVE, LATERAL, ANY/ALL	Supported	Subqueries, UNION and WITH
Window functions	Partial: no DISTINCT in window aggregates	Window functions
INSERT/UPDATE/DELETE, RETURNING, ON CONFLICT, UPDATE ... FROM, DELETE ... USING, MERGE INTO (Postgres 16's), TRUNCATE	Supported	Upserts and CASE
Types	Partial: see Known gaps	What works now
Constraints: primary keys (one or several columns), UNIQUE, CHECK, NOT NULL, foreign keys	Partial: foreign keys to primary keys and UNIQUE columns (they can be deferrable, with every ON DELETE / ON UPDATE action), no MATCH FULL, no deferrable UNIQUE; EXCLUDE works but for a partitioned table's or an INITIALLY DEFERRED one	Unique constraints and foreign keys
Inheritance and partitioning	Supported: INHERITS, ONLY, PARTITION BY RANGE/LIST/HASH, PARTITION OF, routing, row moves, ATTACH/DETACH, keys and indexes on the parent, pg_dump round trip; compared with Postgres 16	Inheritance and partitioning, tests/partitions.rs, tests/pgdiff.rs partitions_*
ALTER TABLE	Supported for columns, defaults, NOT NULL, constraints, renames (RENAME CONSTRAINT of every kind), adding a primary key (<table>_pkey, or <table>_pkey1, ... when the name is taken); a partitioned table's foreign key takes a partition's same foreign key as its copy, and is <partition>_<columns>_fkey in a partition that has a constraint of its name	Changing tables
Indexes	Partial: btree (expressions, partial, unique, operator classes that only compare, INCLUDE; hash and brin as btrees), GIN on tsvector, GIN and GiST trigram indexes (pg_trgm), GiST on points, hnsw/ivfflat accepted	Indexes
Schemas and search_path	Supported, with owners and privileges (USAGE, CREATE)	Schemas
Views, materialized views	Partial: WITH CHECK OPTION on automatically updatable views; no ON CONFLICT through a view	Views
Sequences, serial, identity	Supported; counters shared by every world	Sequences
Generated columns	STORED supported (no VIRTUAL, which is Postgres 18's)	Numbering rows
Enums	Supported, except in primary keys	Enum types
Collations	Supported: C, POSIX, default, ucs_basic, Postgres 16's ICU collations (unicode, de-x-icu, ...; 869 of its 908), CREATE/ALTER/DROP COLLATION, COLLATE on columns, expressions and indexes, Postgres's collation derivation; through ICU4X; nondeterministic ones for comparisons and unique constraints (what's refused); compared with Postgres 16 and ICU4C 78	Collations, tests/pgdiff.rs icu_collations_order_text and after it
Temporary tables	Partial: no ON COMMIT DROP/DELETE ROWS	Temporary tables
Functions and triggers, LANGUAGE sql and plpgsql	Partial	Functions and triggers
Full-text search (tsvector, @@, ts_rank)	Supported, but not ts_headline or ts_rank_cd	Joins, grouping and functions
pgvector	Supported: vector(n), <=>, <->, <#>, USING hnsw with vector_cosine_ops, vector_l2_ops, vector_ip_ops (a graph per distance)	Vectors
PostGIS	Points only	Joins, grouping and functions
information_schema and pg_catalog	Partial: the tables introspection reads	The catalog
Transactions	READ COMMITTED (the default) and REPEATABLE READ as Postgres has them, with row locks that wait; SERIALIZABLE refused	Transactions
EXPLAIN [ANALYZE]	Text only, no costs	EXPLAIN
CREATE DATABASE, DROP DATABASE	A database is a world: CREATE DATABASE makes an empty one, TEMPLATE forks one	Databases
Roles, GRANT/REVOKE, owners	Supported: roles, membership, SET ROLE, privileges on tables, columns, views, sequences, schemas, functions and the database, checked as Postgres checks them; beside agents	Roles and privileges, tests/pgdiff.rs roles_*, *_privileges, tests/roles.rs
Row-level security	Supported: ENABLE/FORCE ROW LEVEL SECURITY, CREATE/ALTER/DROP POLICY, applied to every read and write as Postgres 16 applies them	Row-level security, tests/pgdiff.rs row_security_*, tests/rls.rs

Roles and privileges

Roles, membership and privileges are Postgres 16's, checked against it statement by statement (tests/pgdiff.rs roles_are_created_altered_and_dropped, role_membership_and_set_role, createrole_needs_admin_option_and_attributes_it_has, table_privileges_are_granted_checked_and_revoked, column_and_view_privileges, sequence_privileges, function_and_schema_privileges, owners_drop_owned_and_drop_role_dependencies, default_privileges_are_set_per_role_and_schema, default_privileges_apply_to_new_objects, default_privileges_in_drop_role_drop_owned_and_drop_schema, type_and_domain_privileges_are_granted_and_checked, predefined_roles_read_and_write_all_data, aclexplode_and_makeaclitem; a refusal's SQLSTATE and message are compared exactly).

• Roles: CREATE ROLE | USER | GROUP with SUPERUSER, LOGIN, INHERIT, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT, VALID UNTIL, [ENCRYPTED] PASSWORD 'x' | NULL, IN ROLE, ROLE, ADMIN; ALTER ROLE (the same, RENAME TO, SET name = value and RESET name | ALL, whose settings a session logging in as the role starts with, before the client's own startup options); DROP ROLE [IF EXISTS], refused with 2BP01 while the role owns or holds privileges on anything in any world. Making, changing and dropping roles needs a superuser or CREATEROLE with Postgres 16's rules (the maker gets ADMIN OPTION on the role it made; attributes it hasn't can't be given). Names starting pg_, public and none are 42939.
• Passwords are kept as Postgres 16 keeps them: a password given in the clear becomes a SCRAM-SHA-256 verifier (SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>, a random 16-byte salt, scram_iterations iterations), of the password after SASLprep (RFC 4013, as Postgres runs it: a password outside ASCII is normalized with NFKC, and one SASLprep prohibits is hashed as typed, so libpq's and tokio-postgres's logins match), or with SET password_encryption = 'md5' Postgres's md5 hash; one given already hashed is kept as given. pg_authid.rolpassword and pg_shadow.passwd show it, pg_roles ********. A role with one logs in with it (see Connecting), whether Chronos or the client made it: psql's \password (a verifier made by libpq) and a verifier copied from Postgres's pg_authid both work.
• Membership: GRANT role TO role [WITH ADMIN | INHERIT | SET OPTION | TRUE | FALSE] [GRANTED BY role] and REVOKE [ADMIN | INHERIT | SET OPTION FOR] role FROM role [CASCADE], each grant with its grantor (a superuser's is the bootstrap superuser, as in Postgres 16); a circular grant is 0LP01. INHERIT decides whose privileges a role has, SET what it may SET ROLE to.
• Who a session is: SET [LOCAL] ROLE, RESET ROLE, SET [LOCAL] SESSION AUTHORIZATION; current_user, current_role, session_user, SHOW role and SHOW session_authorization follow them, ROLLBACK undoes them and SET LOCAL ROLE ends with its transaction. SET ROLE to a role the session user can't set is 42501, and to none 22023.
• Privileges: GRANT | REVOKE [GRANT OPTION FOR] ... ON [TABLE] | SEQUENCE | SCHEMA | FUNCTION | PROCEDURE | ROUTINE | DATABASE | TYPE | DOMAIN | ALL TABLES | SEQUENCES | FUNCTIONS | PROCEDURES | ROUTINES IN SCHEMA ... TO | FROM role | PUBLIC [WITH GRANT OPTION] [GRANTED BY role] [CASCADE | RESTRICT], with column lists for SELECT, INSERT, UPDATE and REFERENCES. A grant option is passed on in its holder's name (a column's, from the column's grant option or the table's), and revoking it takes what was granted with it under CASCADE (2BP01 without); a table's REVOKE takes its columns' too. DROP OWNED revokes what the objects' owners granted the role, and what the role granted on from those grant options, as Postgres does. The maker of an object owns it and holds every privilege on it; ALTER TABLE | VIEW | MATERIALIZED VIEW | SEQUENCE | SCHEMA | FUNCTION | PROCEDURE | TYPE | DOMAIN ... OWNER TO hands it over with its grants (a table's serial columns' sequences with it), REASSIGN OWNED and DROP OWNED act on the session's world. relacl, attacl, nspacl, proacl, typacl and datacl are written as Postgres writes them (alice=arwdDxt/owner, =r/owner for PUBLIC); a null one is the default; acldefault, aclexplode, makeaclitem, pg_get_userbyid and ::regrole work; pg_dump writes the grants and owners, and they restore. A type's or domain's GRANT is on one made with CREATE TYPE or CREATE DOMAIN (a built-in type's, by any of its names, and a table's row type's are 0A000; an array type, _mood or _int4, is 0LP01, as in Postgres). A GRANT or REVOKE that gives or takes nothing (a warning) writes the object's ACL out all the same, as Postgres does.
• Default privileges: ALTER DEFAULT PRIVILEGES [FOR ROLE | USER role, ...] [IN SCHEMA schema, ...] GRANT | REVOKE [GRANT OPTION FOR] ... ON TABLES | SEQUENCES | FUNCTIONS | ROUTINES | TYPES | SCHEMAS TO | FROM role | PUBLIC [WITH GRANT OPTION] [CASCADE | RESTRICT], for roles whose privileges the running role has (a superuser: any), as Postgres 16 keeps them: a role's own entry starts from the built-in defaults and replaces them, a schema's starts empty and adds to them, and an entry back where it started is no row. What a role makes starts with them, combined as Postgres combines them: tables, views, materialized views, CREATE TABLE AS and SELECT INTO, partitions, sequences and serial and identity columns' sequences (one ALTER TABLE adds, ADD GENERATED ... AS IDENTITY too: its table's owner's), functions and procedures (CREATE OR REPLACE of one there keeps its ACL), types, domains and schemas (CREATE SCHEMA ... AUTHORIZATION role: that role's). pg_default_acl has them (defaclobjtype r, S, f, T, n), psql's \ddp lists them, and pg_dump writes them as ALTER DEFAULT PRIVILEGES, which restore. DROP ROLE refuses a role that has them or is named in them (2BP01, owner of default privileges on new relations belonging to role r in schema s), DROP OWNED drops a role's and takes it out of others' (dropping one left as it started), REASSIGN OWNED leaves them, and DROP SCHEMA drops the schema's.
• Checks, 42501 with Postgres's messages (permission denied for table t, ... for schema s, ... for sequence s, ... for function f, must be owner of table t): SELECT (the columns a query reads, in its select list, WHERE, joins, ORDER BY, subqueries; any column for count(*)), INSERT and UPDATE (their columns, and SELECT on what WHERE and RETURNING read), DELETE, TRUNCATE, COPY (before any data is sent), FOR UPDATE (UPDATE on some column); USAGE on a schema to name what's in it (a schema without it isn't searched), CREATE to make something in it, CREATE on the database for CREATE SCHEMA and TEMP for temporary tables; nextval (USAGE or UPDATE, also a serial column's default; an identity column's needs none), currval (USAGE or SELECT) and setval (UPDATE); EXECUTE on functions (PUBLIC has it until revoked); USAGE on a type or domain made here (PUBLIC has it until revoked) to make a column of it (CREATE TABLE, the columns LIKE, PARTITION OF and INHERITS copy, ALTER TABLE ... ADD COLUMN | ALTER COLUMN TYPE, a view's, a materialized view's and CREATE TABLE AS's columns), a domain over it or a function taking or returning it (a cast to it needs none, as in Postgres); ownership for DROP, ALTER, CREATE INDEX, REFRESH MATERIALIZED VIEW and CREATE OR REPLACE, and TRIGGER on the table and EXECUTE on the function for CREATE TRIGGER. CREATE EXTENSION pgcrypto (a trusted extension) needs CREATE on the database, and the role that runs it owns the extension (pg_extension.extowner): DROP EXTENSION and ALTER EXTENSION need that owner, SET SCHEMA CREATE on the schema too, DROP OWNED drops it and DROP ROLE lists it. DROP ROLE also lists, and refuses over, grants on the database. A type's or domain's messages name it with its schema where the search_path doesn't find it (must be owner of type s.mood). A materialized view's query is checked as it is made (not WITH NO DATA), and REFRESH reads it as the view's owner, whoever runs it. A view reads its tables with its owner's privileges, whoever reads it (a superuser too), and a SECURITY DEFINER function runs as its owner. A superuser skips every check of its own. pg_read_all_data gives its members SELECT on every table, view and sequence, pg_write_all_data INSERT, UPDATE and DELETE, and either USAGE on every schema, as in Postgres 16 (the other predefined roles give no privileges on objects).
• Logins and agents: a login whose name is a role has that role's privileges; any other name is a superuser, acting as the bootstrap superuser chronos (oid 10, owner of whatever no role made), as every login was before roles existed, so a database that never makes a role is unchanged. postgres, and the session's own login name, are names of it in OWNER TO and the has_*_privilege functions. A role of a name a login uses would turn that login into the role from its next connection, so CREATE ROLE and RENAME TO refuse (42710, role "x" already exists, as Postgres refuses a taken name) chronos, postgres, the session's own login name, and the name of any open connection that logged in as no role and isn't an agent; make a role for a superuser's login name while it isn't connected, from another login. An agent keeps its capabilities; a role of its name adds that role's privileges on top (both must allow), and making roles or granting them needs the admin capability.
• Worlds: roles are the database's, like agents: one row on main, so every world sees the same roles; CREATE, ALTER and DROP ROLE and role grants take effect at once and a ROLLBACK doesn't undo them (in Postgres it does). Owners, grants and default privileges are the world's, like its tables: a fork has its parent's, a merge brings the fork's changes (both sides changing one object's grants, or one role's default privileges for one kind and schema, is a conflict), AS OF and RESTORE read and put them back as they were, and DIFF doesn't show them. A role that isn't a superuser can't make, merge, restore or drop worlds, read DIFF, history or audits, manage agents or merge policies, or ALTER SYSTEM (42501): roles' privileges are on objects; give a program an agent for worlds.
• What differs: DROP ROLE's detail lists the schemas first, then default privileges (in the order they were made), then the other objects by kind, where Postgres lists them all in the order they were made. An array of a type made here (a mood[] or pos[] column, a domain over mood[], a function's mood[] argument or result; kept as text[] and the base type's array here) doesn't need USAGE on it, and neither does a view's or CREATE TABLE AS's column that reads a domain column through a subquery (a domain column read as it is, select p from t, does). A role in pg_read_all_data still can't read pg_authid and pg_shadow (42501), which Postgres 16 lets it: they hold password hashes. A temporary table doesn't take default privileges (Postgres gives it the role's own entry's). Functions can't make objects here, so nothing a function makes takes default privileges.
• Not yet: information_schema's privilege views (table_privileges, role_table_grants, column_privileges, usage_privileges, routine_privileges, applicable_roles, enabled_roles); MERGE needs SELECT on some column of its target even where Postgres needs none; LOCK TABLE, COMMENT ON, VACUUM and REINDEX aren't checked; ALTER ROLE ... IN DATABASE (0A000).

Row-level security

Row-level security is Postgres 16's, checked against it statement by statement (tests/pgdiff.rs row_security_policies_are_created_altered_and_dropped, row_security_filters_reads_and_checks_writes, row_security_owner_force_bypassrls_and_row_security_off, row_security_on_conflict_and_merge, row_security_views_subqueries_and_settings, row_security_on_partitioned_and_inherited_tables, row_security_reaches_every_read, row_security_policies_bind_their_names_when_made, row_security_follows_columns_and_defaults, row_security_reads_the_tables_it_bound, row_security_joins_keep_their_policies_first, row_security_prepared_statements_see_policy_changes; the refusals of reads and writes are compared word for word, and tests/rls.rs compares the policy statements' and checks two LOGIN tenants over real connections, psql, pg_dump and worlds).

• Statements: ALTER TABLE ... ENABLE | DISABLE | FORCE | NO FORCE ROW LEVEL SECURITY (the table's own: a partitioned table's partitions keep theirs); CREATE POLICY name ON table [AS PERMISSIVE | RESTRICTIVE] [FOR ALL | SELECT | INSERT | UPDATE | DELETE] [TO role | PUBLIC | CURRENT_USER | CURRENT_ROLE | SESSION_USER, ...] [USING (...)] [WITH CHECK (...)], ALTER POLICY ... RENAME TO | TO | USING | WITH CHECK, DROP POLICY [IF EXISTS] ... [CASCADE | RESTRICT]; the table's owner alone (or a superuser) may run them, with Postgres's errors and messages.
• What they do: who is subject (not superusers, BYPASSRLS roles, or the owner and its members unless FORCE), default deny, permissive policies ORed and restrictive ones ANDed, USING as a filter for SELECT, UPDATE and DELETE and WITH CHECK (or USING) as a 42501 check of the rows INSERT and UPDATE write, SELECT's policies for statements that read their target (WHERE, SET reading it, RETURNING, ON CONFLICT DO UPDATE) and as a check of the rows they write, FOR UPDATE's UPDATE policies, ON CONFLICT's and MERGE's checks of the row they change ((USING expression) and target row messages), row_security = off, COPY ... TO through the policies and COPY ... FROM refused (0A000). A view's reads are its owner's (with current_user the caller's), a SECURITY DEFINER function's its owner's, and a policy's subqueries are checked and filtered as the role reading; one policy reading another that reads it back is 42P17. A partitioned or inherited table's policies apply when it is read, a partition's or child's own when that one is. They reach every read in a statement: WITH RECURSIVE's parts, a FROM function's arguments, either side of IN, LIMIT, OFFSET, DISTINCT ON, window clauses, PL/pgSQL's expressions, UPDATE ... FROM's and DELETE ... USING's join, FOR UPDATE OF and tableoid through a join; CALL's arguments can't have subqueries (0A000), as in Postgres. A policy reading its own table is no loop where what it reads there has no subquery, as in Postgres. A materialized view refreshes as its owner. What a policy reads is bound when it is made: names its maker's search_path finds outside public are written with their schema in its condition, and the condition is read with the other names public's and a built-in function's name the built-in, whatever the search_path of whoever reads the table; the tables it reads are the world's, never a temporary table of the name. Renaming a column it reads, wherever it reads it (its own table's, in a subquery, from another table's policy), or a table or view it reads, renames it in its condition; dropping a column, table or view it reads is 2BP01, and CASCADE drops the policy, with Postgres's messages. ADD COLUMN ... DEFAULT current_user gives the rows there the role adding it.
• Cost: a policy's condition is parsed once, and a statement's rewrite (its views and policies put in) is kept with it, with the plan of what it became, while the session state it was made under (the role and its memberships, row_security, the search_path) and everything it read of the world (policies, row security, owners, views, roles) are the same: a prepared statement plans once, and a change to any of them rewrites it at its next run, as Postgres plans again after CREATE/ALTER/DROP POLICY or ENABLE/DISABLE. A point read by primary key of a table with a tenant policy costs about what one of a plain table does (release build, pgbench -M prepared: 33 against 32 µs of server CPU, where it was 48). A table with policies joined to others stays a table, read through its indexes for the join's conditions, where its policies can go first in the WHERE (or in its LEFT JOIN's ON) and no condition of the query on it could show its values before them; else it is a subquery of the rows it may see, as before.
• Catalog: pg_class.relrowsecurity and relforcerowsecurity, pg_tables.rowsecurity, pg_policy (polqual and polwithcheck hold the condition as pg_get_expr writes it, which pg_get_expr gives back) and pg_policies; psql's \d (Policies:) and \dp; pg_dump writes ENABLE/FORCE ROW LEVEL SECURITY and CREATE POLICY, and the dump restores.
• Worlds: a table's row security and each policy are rows of its world (_sonos_rls/<table>, _sonos_policy/<table>/<name>): they fork, merge (two worlds' policies on one table merge without a conflict), show in DIFF (AS SQL too) and come back with RESTORE. A read AS OF a moment gets the policies the table had then, and those its name has now.
• What differs: a condition is shown as Postgres writes it back where Chronos's deparser knows its parts (columns, constants, comparisons, AND/OR/NOT, IN lists, LIKE, IS NULL, CURRENT_USER and its kin, current_setting(...)::type), and otherwise as it was written (renames done in it), in parentheses (a subquery, a function call); the tables and views a policy reads are bound by their stored names, which renames keep up to date and drops can't take from under it, where Postgres binds them by oid (to the same effect), but functions and types it names are bound by schema and name when it is made, not followed through a rename or kept from a drop; CREATE POLICY is refused (0A000) where a name it calls is ambiguous on the maker's search_path (a function in two of its schemas, or a built-in function's name that a function made with CREATE FUNCTION has too: write it with its schema); a view a policy reads has its names read as the policy's are; renaming a column a policy reads through a subquery's *, a NATURAL join or a USING join is refused (0A000: drop the policy, rename, make it again) where Postgres follows the rename, and a dropped column counts as read where a policy reads it that way or by an unqualified name more than one table of its subquery has; a view's security_invoker and security_barrier options aren't parsed (a view always reads as its owner, and always as a barrier); a table with policies joined to others is still a subquery of the rows it may see (read whole, without its index for the join's conditions) where its policy has a subquery, a RIGHT or FULL join pads it, the query has a LATERAL or function source or a USING or NATURAL join, or a condition on it is anything but comparisons of columns, parameters and constants that cast none of its values (a function call, arithmetic, a cast, text compared with a number), where Postgres still pushes its leakproof conditions down; Postgres keeps a prepared statement's plan through a change of its role's memberships or BYPASSRLS until something else makes it plan again, where Chronos sees the change at the next run; the HTTP and MCP row API (get, find, put, delete, batch, diff, merge) works on rows as they are, so an agent whose role (one of its name) a policy limits on a table is refused it there (403, 42501) and uses SQL, which applies the policies; chronos import doesn't bring a Postgres database's policies over.

Left after parity batch 2

Batch 2 (PRs #90 to #106) built roles and privileges, row-level security, SCRAM-SHA-256 login, ICU collations, pgcrypto, pg_proc rows for the built-in functions, regproc[]/regprocedure[], and oid and smallint as their own types on the wire. What it left, by area (each is in the sections above with its details):

• Roles and privileges: information_schema's privilege views; GRANT/REVOKE on languages, large objects, foreign servers, tablespaces and parameters, and ALTER ROLE ... IN DATABASE (0A000); USAGE on a type made here isn't needed for its arrays; GRANT ON TYPE of a table's row type (0A000); role statements aren't transactional; DIFF doesn't show owner and grant changes; collations have no owners and CREATE/ALTER/DROP COLLATION aren't privilege-checked; a superuser login name isn't remembered once it disconnects (a role made under it then takes that login over).
• Login: no pg_hba.conf (the method follows from the name; loopback without a token is trust but for a role with a password); an agent made before agents had SCRAM verifiers logs in once with its token in the clear (until then its name shows), and a role kept as an md5 hash is asked for MD5; the secret mock salts are made from is drawn at each start, not kept as Postgres keeps it; every SCRAM login with --token waits at its last step about twice as long as making the tokens' verifiers takes (a few milliseconds), where Postgres's takes no extra time; no SCRAM-SHA-256-PLUS for EdDSA certificates. (Batch 3 gave agents SCRAM verifiers, hid which names exist, and made scram_iterations, SASLprep and chronos import's channel binding work.)
• Row-level security: functions and types a policy names are bound by name, not followed through renames or kept from drops; renaming a column a policy reads through a *, NATURAL or USING join is refused (0A000); a joined table whose policy has a subquery, that a RIGHT or FULL join pads, or whose values a condition could show is read whole as a subquery; views' security_invoker and security_barrier aren't parsed; the HTTP/MCP row API refuses, rather than applies, policies; chronos import doesn't bring policies over. (Batch 3 parsed policies once and kept each statement's rewrite, bound the tables a policy reads by identity (temporary tables, renames in subqueries and of tables, DROP TABLE), and kept joined tables tables where safe.)
• Collations: in a nondeterministic collation, GROUP BY, DISTINCT, set operations, primary and foreign keys and a subquery's rows (0A000); ICU rules and 39 of Postgres's ICU locales (0A000); initcap under ICU; ordering comparisons of ICU text with ANY (array) or a subquery's rows, and ordering arrays and rows of it (0A000); COLLATE on domains, array columns and PL/pgSQL variables; range partitioning in an ICU collation; a DEFAULT naming a collation made here; DROP COLLATION ... CASCADE reaching a materialized view or a primary key's column (0A000); pg_describe_object; a merge doesn't see collations used only in stored SQL; DROP SCHEMA ... CASCADE leaves the schema's collations behind; pg_collation_is_visible and ::regcollation don't follow the search_path's order; ideographs outside the Basic Multilingual Plane sort as ICU4X does, not ICU4C.
• pgcrypto: pgp_pub_encrypt/pgp_pub_decrypt (RSA/Elgamal) and pgp_armor_headers; SHA-3, BLAKE2, SM3 and SHA-512/t digests; DROP EXTENSION and DROP SCHEMA ... CASCADE don't track what calls its functions; ALTER EXTENSION ... ADD/DROP/OWNER.
• Catalog and types: pg_type has 55 of Postgres's 465 built-in types, so format_type of the rest is a number; the catalog's own tables aren't rows of pg_class/pg_attribute; no pg_description for the built-ins, and information_schema.routines lists only functions made here; arrays compare and sort as their text (array[10] < array[9]); name, "char", float4, xid, aclitem[], pg_node_tree, real and varchar(n) columns reach clients as Chronos's types (text, double precision, bigint, text[]); xid/cid columns are bigints; a kept regclass, regnamespace, or regtype or regcollation of an object made here follows its object's name, not the object: renaming the object, or dropping it and making another of its name, changes what it names (its oid is worked out from the name); generate_series of smallints is an integer series (Postgres: 42725, ambiguous); an oid[] in json has numbers where Postgres has strings; an oid compared with a subquery's integer column given by * is refused (0A000) rather than converted; int4[] and int8[] literals keep their elements' text unchecked ('{1.5}'::int[]); integer (int4) arithmetic is checked to 32 bits only where its result is used; pg_typeof of a window call is text; CREATE INDEX ... INCLUDE (c) doesn't count c in pg_index.indnatts.

Known gaps

Each is an error (0A000 for known features, 42601 for statements Chronos doesn't parse), not a wrong answer.

• Partitioning: no foreign tables as partitions. A condition on the partition key leaves out the partitions it can't match, as Postgres's pruning does (EXPLAIN shows it for one-table plans).
• Statements not parsed: ALTER DATABASE other than OWNER TO (0A000); GRANT/REVOKE on languages, large objects, foreign servers, tablespaces and parameters, and on built-in types (0A000).
• Clauses: GROUPING SETS together with DISTINCT or a window function, hypothetical-set aggregates (rank(x) WITHIN GROUP), COPY ... WHERE, DISTINCT ON together with GROUP BY, aggregates or window functions. ^ on numbers with decimals (2.5 ^ 2) is refused; on integers and doubles it works, as double precision.
• Types: composite types, range types, domains, time with time zone, money, bit, xml, hstore, json (oid 114) and jsonb are there. Enum columns reach clients typed text, and a function's varchar parameters and result are listed as text in the catalog (\df, pg_get_function_arguments, pg_proc).
• SHOW, current_setting() and pg_settings: all 363 of Postgres 16's settings (src/sql/settings.tsv, made from a real Postgres 16: type, unit, range, allowed values, context, description), each at Postgres's default unless Chronos has its own value: server_version 16.15, server_version_num 160015, TimeZone (the session's, UTC unless set), transaction_isolation read committed (the transaction's own level), lc_collate C (text sorts by bytes), max_connections. Values are written as Postgres writes them (64MB, 5min, on). pg_settings and SHOW ALL list them all (except is_superuser, lc_collate and lc_ctype, which Postgres does not list either) with setting in the base unit, source (default or session), boot_val and reset_val. Chronos's own settings have their own SHOWs (SHOW synchronous_commit is the database's mode: full, normal or off). Any other name is 42704, as Postgres says of one it doesn't know; current_setting(name, true) gives NULL instead.
• SET: every setting is checked as Postgres checks it: a boolean in any of its spellings (on, t, yes, 0), an integer or real in its unit ('64MB', '1.5s', a bare number in the base unit) and range, an enum by its allowed values; an invalid value is 22023, a name nobody has is 42704, and a setting a session may not change (max_connections, server_version, wal_level, ...) is 55P02. SET, SET LOCAL, set_config(name, value, is_local), RESET name, RESET ALL and SET name TO DEFAULT keep what was set for the session (or its transaction) and read it back with SHOW, current_setting() and pg_settings; a SET is undone by ROLLBACK, ROLLBACK TO SAVEPOINT and a failed transaction, as in Postgres, and a custom setting (app.org_id) that was set once stays, empty, as in Postgres. search_path, statement_timeout, TIME ZONE / TimeZone, IntervalStyle (all four styles), hnsw.ef_search, application_name and client_min_messages take effect. The settings with nothing to act on here (work_mem, enable_seqscan, jit, random_page_cost, the autovacuum, WAL and logging settings, ...) are kept and read back and change nothing. Those Postgres honors and Chronos does not, where a silent no-op would change an answer, take only the value Chronos implements and refuse the rest with 0A000: DateStyle (ISO, MDY), standard_conforming_strings and array_nulls (on), quote_all_identifiers and transform_null_equals (off), bytea_output (hex), extra_float_digits (1 to 3), client_encoding (UTF8), session_replication_role (origin), lc_monetary/lc_numeric/lc_time (C) and default_text_search_config (english, simple). SERIALIZABLE, asked for in SET TRANSACTION, SET SESSION CHARACTERISTICS or SET default_transaction_isolation, is 0A000; the other isolation levels are accepted and ignored. set_config('search_path' | 'statement_timeout' | 'TimeZone', ...) returns the value and does not change the session's (use SET).
• Functions: no write-ahead-log functions (pg_current_wal_lsn and the like) or txid_current_snapshot. pg_cancel_backend and pg_terminate_backend stop (or close) another connection by its pid, with Postgres 16's rules for a role that isn't a superuser: a superuser's connection (one logged in as no role, too) is 42501, and another role's needs that role's privileges or pg_signal_backend's; an agent can't (42501). has_table_privilege and the other has_*_privilege functions, and pg_has_role, answer from the roles, owners and grants (Roles and privileges); an object given by an oid that names none is null, as in Postgres; and are refused with 0A000 in an agent's session, whose rights are capabilities that depend on the world.
• Extensions: CREATE EXTENSION (and DROP EXTENSION) accepts vector and postgis (built in), plpgsql (in every database, as in Postgres), btree_gin and btree_gist (operator classes for indexes that are btrees here anyway), citext, hstore and ltree (their types, operators and functions are built in: citext, hstore, ltree), pg_trgm, fuzzystrmatch and unaccent (their functions and operators are built in, in every database: similarity, word_similarity, show_trgm, %, <%, <->, levenshtein, soundex, dmetaphone, unaccent, ...; a gin or gist index with gin_trgm_ops or gist_trgm_ops is an index of trigrams that %, LIKE '%x%' and ILIKE use, as in Postgres (similarity(a, b) > x compares every row, as it does there: Indexes); the statement doesn't have to come first, and DROP EXTENSION of one doesn't remove the functions or care about an index that uses its operator class), uuid-ossp, which only gives what's built in, uuid_generate_v4() (a notice says so), and pgcrypto (next). Any other, tablefunc and intarray among them, is 0A000: it would change what SQL means, and not having it is better than a quiet difference.
• pgcrypto: every function of pgcrypto 1.3 but the public-key ones (pgp_pub_encrypt, pgp_pub_decrypt and their _bytea forms) and pgp_armor_headers is here, giving what Postgres 16 gives: digest, hmac, crypt and gen_salt (bcrypt $2a$ and $2x$, MD5 $1$, extended and traditional DES), gen_random_bytes, gen_random_uuid, encrypt, decrypt, encrypt_iv, decrypt_iv (aes, bf, cast5, des and 3des; CBC, ECB, and bf-cfb; PKCS padding or none), pgp_sym_encrypt, pgp_sym_decrypt and their _bytea forms with every option, armor, dearmor and pgp_key_id (tests/pgdiff.rs; random results both ways in tests/pgcrypto.rs: a hash or OpenPGP message either makes, the other reads). Like pg_trgm's, the functions are built in, in every database, before CREATE EXTENSION pgcrypto too. That statement records the extension in the world: pg_extension lists it (version 1.3, in the schema it was made in), a second CREATE EXTENSION is 42710 and DROP EXTENSION of one not made 42704, ALTER EXTENSION pgcrypto UPDATE [TO '1.3'] says it's installed and SET SCHEMA moves it, DROP SCHEMA of its schema needs CASCADE (which drops it, with a notice), and the record forks, merges and shows in DIFF (create extension, after the schema it is in) with the world. What is different:
  • Its functions are found by any schema's name (public.crypt(...), as pg_dump writes them), whether or not the extension was made there, so a view, function or default that calls one so keeps working after DROP EXTENSION or DROP SCHEMA ... CASCADE: Postgres refuses the first (2BP01) while something uses its functions, and drops what uses them with the second.
  • bf, cast5 and des (in encrypt and its kin, and cipher-algo=bf or cast5 in pgp_sym_encrypt) work as pgcrypto computes them with OpenSSL's legacy provider. Postgres built against OpenSSL 3 without that provider refuses them in encrypt (39000, Cipher cannot be initialized), and its pgp_sym_encrypt with them writes the data unencrypted.
  • digest and hmac take md5, sha1, sha224, sha256, sha384, sha512 and ripemd160, by OpenSSL's names, aliases and OIDs for them; the other digests OpenSSL has (SHA-3, SHAKE, BLAKE2, SM3, SHA-512/224 and /256) are 0A000, and md4, mdc2 and whirlpool give the error Postgres on OpenSSL 3 gives.
  • pgp_sym_encrypt with sess-key=0 and an s2k-cipher-algo encrypts the data with that cipher, the one its session key packet names. pgcrypto encrypts it with cipher-algo instead, so not even Postgres reads that message back (39000).
  • A compressed message is compressed by miniz_oxide here and zlib in Postgres, so its bytes differ; each reads the other's.
  • A salt crypt cuts in the middle of a character (traditional DES keeps its first 2 bytes, extended DES 9, MD5 8) shows that character as U+FFFD in the hash; Postgres writes the raw bytes, which aren't UTF-8. ALTER EXTENSION pgcrypto ADD and DROP (of a member) are 0A000: the functions are built in.
  • The expect-* and debug options are accepted and do nothing (in Postgres they make decryption say in a NOTICE what it found otherwise). pgp_sym_decrypt of a message encrypted with a public key is 39000 with a message saying so, where Postgres's says pgcrypto bug.
• Maintenance statements: VACUUM, ANALYZE, REINDEX and CLUSTER succeed and do nothing (no dead rows to reclaim, indexes always current, no statistics kept), after checking their names and where they may run as Postgres does. COMMENT ON on a function, type, extension, trigger or database says the comment isn't kept (tables, views, sequences, indexes, columns, constraints and schemas keep theirs: Comments). LOCK TABLE and DISCARD are described under Maintenance, DISCARD and LOCK.
• Catalog: the tables listed in the catalog have rows, the world's functions, enum types, triggers and dependencies among them; every other Postgres 16 catalog table exists with its columns and is empty (the built-in operators aren't listed in pg_operator; pg_proc lists Postgres 16's built-in functions, see regproc below). pg_type has each table's, view's and materialized view's row type and its array type (_t, or _t_1 where a type of the schema has that name, as Postgres names it), which pg_class.reltype names, and a relation and a type (an enum, a domain) can't share a name (42710, as in Postgres); pg_attribute lists the system columns (tableoid, ctid, xmin, ...) of tables, materialized views and sequences, a sequence's columns and an index's (an expression's named as Postgres names it, lower, coalesce, expr, typed as it is); a sequence's and a view's relreplident is n and a view's and a partitioned table's relfilenode 0, as in Postgres; and the catalog's array columns are arrays (reloptions, proargnames and proconfig text[], proallargtypes oid[], proargmodes "char"[]). Differences: a catalog column of a type Chronos keeps as another reaches clients as that one (xid as bigint, name, "char" and pg_node_tree as text, float4 as double precision, aclitem[] as text[]; its oid and int2 columns are oid and smallint); relpages and reltuples are 0 and -1 for every table and index, as for one never analyzed (Chronos keeps no statistics; a sequence's are 1 and 1, as in Postgres), where Postgres counts an index's when it builds it; there are no TOAST tables (reltoastrelid 0) and relfrozenxid and relminmxid are 0; array types are named in the order of their enums and then their relations' names, where Postgres names them as they are made (it renames one, _z to __z, when a table named _z comes after a table z); and the catalog's own tables and views aren't rows of pg_class and pg_attribute (psql's \d pg_class finds nothing). pg_stat_activity lists the open connections (who, which world, what they are running or ran last, in a transaction or not, and what they wait for: Lock/transactionid for a row another transaction holds, Lock/advisory for an advisory lock or a sequence; pg_blocking_pids(pid) gives the connections holding it up) and pg_stat_database each world's connection count; Chronos keeps no other statistics: the per-table, per-index and per-transaction statistics views (pg_stat_user_tables, pg_stat_user_indexes, pg_statio_*, pg_stat_xact_*) list the tables and indexes with their counters at 0, pg_stat_database_conflicts has a row per world, and the views Postgres always has one row of (pg_stat_bgwriter, pg_stat_wal, pg_stat_archiver, pg_stat_recovery_prefetch) have it, counters 0; every other pg_stat* view of Postgres 16 exists with its columns and is empty (one node: no replication, WAL receiver, subscriptions or SSL). pg_prepared_statements lists the session's prepared statements (SQL PREPARE's and a driver's named ones, with parameter_types and result_types as regtype[], arrays of the types' oids, so 'integer'::regtype = any (parameter_types), parameter_types[1]::oid, format_type(parameter_types[1], null) and parameter_types::oid[] give Postgres's answers; a type Chronos keeps as a wider one shows as that one: real as double precision, varchar as text), pg_cursors its open cursors (statement is the query string each came in: a simple query's whole text, or the text of a driver's Parse; a cursor's own query sees it), read directly, through a view or in a function's body, and pg_locks the advisory locks held and waited for in the database it is asked in (Chronos takes no other locks a client can see), each with the pid of the connection holding or waiting for it, as pg_stat_activity numbers it (a session of a program embedding Chronos is no connection: other sessions see its locks with a null pid). pg_roles, pg_authid, pg_user, pg_shadow and pg_auth_members list the roles (Roles and privileges). pg_database lists the worlds (and postgres, template0, template1 unless a world is named so), with the columns tools read. NOT NULL constraints aren't rows of table_constraints, and pg_opclass is empty.
• regproc and regprocedure: both are types (pg_typeof says so, a column can be declared one, and a driver gets them as type 24 and 2202, in binary as oids). A regproc is its function's oid, and each value knows it is a regproc, so it is shown as its name when a result leaves the statement and wherever it is made text inside one (::text, ::varchar(n), ||, a function of any type such as concat, format or quote_literal, json, a row, a column or PL/pgSQL variable of text it is written to by any statement, VALUES, a default, RETURNING, a LATERAL subquery, a window function's result, a function returning text or rows of text, a PL/pgSQL $1, a value EXECUTE ... USING passes, RAISE's % and options), as the session's search_path shows it: bare where its name finds it alone, else with its schema (pg_catalog.sum, and pg_catalog.array_in when a function array_in is made on the path). A function of text or numbers (lower, substr, md5, abs, hashtext), one made here whose parameter is text or a number, an aggregate of numbers, booleans or text (sum, avg, string_agg), arithmetic, || with anything but text, and LIKE, ILIKE, SIMILAR TO and ~ take no regproc (42883); it casts only to oid, integer, bigint, text (and varchar or char) and regprocedure, any other cast being 42846; and PL/pgSQL's RETURN QUERY of a regproc for a column of another type is 42804, as in Postgres. ALTER COLUMN ... TYPE of a regproc column converts its values as it runs, by USING's casts (using p::oid::text writes the oids' digits, plain type text the names), and rebuilds its indexes and unique constraints from them. A string is read as an oid only when it is all digits, as Postgres's oidin reads one ('00042' is octal, namein; '-1' and ' 42' are names, 42883; past 32 bits is 22003), and an integer cast to a regproc is an oid (a negative int4 wraps around, (-1)::regproc::oid is 4294967295; past 32 bits is 22003). A foreign key on a regproc column refers by the oid, so a reference written under one search_path holds its parent under another. A built-in function's name or oid reads both ways from the oids of Postgres 16.13's 3,286 built-in functions (src/sql/builtin_procs.tsv): 'now'::regproc::oid is 1299, 42::regproc is int4in, 'substring(text,int,int)'::regprocedure is "substring"(text,integer,integer), a name several have is 42725, and an overload stays the one it is ('abs(int)'::regprocedure::regproc::oid is 1397). pg_catalog is searched first unless the search_path names it later, so a function made here with a built-in one's name and argument types is hidden behind it, as in Postgres. The built-in functions are rows of pg_proc too (pronamespace 11), from the same file, with Postgres's prokind, prorettype, proargtypes, proallargtypes, proargmodes, proargnames, provolatile, proisstrict, proretset, prosrc, probin, proacl and the rest, so a catalog's regproc column joins to pg_proc.oid, psql's \df pg_catalog.*, \df+ and \sf list and show them, and pg_get_function_arguments, pg_get_function_identity_arguments, pg_get_function_result and pg_get_functiondef give Postgres's text for them (pg_get_functiondef of an aggregate is 42809); pg_init_privs has the privileges initdb gives the ones that aren't everyone's (lo_import's), so pg_dump leaves them out as it does in Postgres. regproc[] and regprocedure[] are arrays of them (_regproc and _regprocedure, 1008 and 2207): ARRAY[...], array_agg and ARRAY(subquery) of regprocs make one, a column can be one, and a driver gets one as type 1008 or 2207 (in binary, an array of the functions' oids, of element type 24 or 2202). It holds its functions' oids, so it reads names and oids ('{now,int4in}'::regproc[], of several dimensions and with bounds too), shows names, casts to and from oid[] and the integer arrays as the oids and to and from text[] as the names, is equal to another by its oids, and gives regprocs back (a[1], unnest, = ANY, array_append, ||); a string literal beside one (compared with it, @>, IN, CASE, COALESCE, NULLIF) is read as one; where it is made text (::text, concat, format, json, a column of text, text[] or varchar[] it is written to or altered to, RETURNING, RAISE's %, a violation's DETAIL) it is an array of the names; and an operator, function or cast Postgres hasn't for one (text || regproc[], regproc[] = text[], array_remove(regproc[], text), regproc[]::regproc) is 42883 or 42846. pg_function_is_visible says whether the search_path finds a function by its name and argument types (pg_catalog's first unless the path names it later), as Postgres says. The catalog's regproc columns (typinput, amhandler, ...) are regprocs, and regprocs compare with numbers and each other, and order (ORDER BY, windows, an aggregate's ORDER BY, GROUP BY, DISTINCT, UNION, min and max), by oid; comparing one with a string literal (typinput = 'int4in') reads the string as an oid and fails (22P02), and with text (p = '1299'::text, a text column or PL/pgSQL variable) is 42883, as in Postgres. CASE and COALESCE of a regproc and an integer sort by oid with the integer among them. Beside a string literal or an integer in CASE, COALESCE, GREATEST, LEAST, UNION or VALUES a regproc stays one (the literal read as one, the integer an oid), and beside text it is 42804, as in Postgres. A table's schema keeps a regproc default, ADD COLUMN value and partition bound as the oid, as its rows and unique keys do, so they read back without the catalog and an overload stays the one it is. EXECUTE's arguments and a driver's text parameters (Bind) are read in the session's world, so a regproc parameter names a function made here. A function made here gets an oid of its own when it is made (OR REPLACE keeps it), from a counter every world shares (from 2^31 up), and keeps it, so a regproc kept in a table names the same function after other objects are made or dropped, in every world and after worlds merge (a merge and DIFF ... AS SQL, which writes the oid, carry it); one made before oids were kept has one worked out from its name and argument types (from 2^30 up). Differences: hnsw's amhandler (pgvector's hnswhandler) has an oid of Chronos's own, 16301, and no pg_proc row; extensions' functions (pg_trgm's, pgvector's) and information_schema's aren't among the built-in ones, nor rows of pg_proc; the built-in rows have no proargdefaults or prosqlbody (null: pg_get_function_arguments and pg_get_functiondef give their text all the same), no comments in pg_description, and the oids initdb gives (past 10000: plpgsql_call_handler's and the snowball dictionary's) are Postgres 16.13's, which another build may number otherwise; format_type and regtype of a type Chronos has no pg_type row for (anyarray, internal) give its oid as a number, so prorettype::regtype of a built-in function may be one; PL/pgSQL's RETURN QUERY takes text for a regproc column, read as one, where Postgres takes only a regproc; a string written to a regproc column is read along the search_path when it is written (a name found in no way at that point is 42883), as Postgres does; a regproc[] compares and sorts as its text, as every array here does, so <, ORDER BY, min and max of regproc[]s go by their oids' digits, where Postgres's go oid by oid; and a text[] written to a regproc[] column is read as the names it holds, where Postgres refuses one (42804).
• regclass, regtype and the other reg types: regclass, regtype, regnamespace, regrole, regoper, regoperator, regconfig, regdictionary and regcollation are types as regproc is (a driver gets them as 2205, 2206, 4089, 4096, 2203, 2204, 3734, 3769 and 4191, in binary as oids, and their arrays, regclass[] and the rest, as 2210, 2211, 4090, ..., arrays of oids, as regproc[] is). A value is its object's oid and knows its type, so it compares, orders (ORDER BY, GROUP BY, DISTINCT, UNION; min and max give the oid, as in Postgres), joins (c.oid = 'orders'::regclass, pg_attribute.attrelid = 'orders'::regclass) and is kept by the oid, and it is shown as its object's name where a result leaves the statement and wherever it is made text, as the session's search_path shows it: bare where the path finds it by that name, else with its schema, each part quoted where it needs it, keywords too (app.orders, "My Table", "user", an index app.orders_pkey). The HTTP and MCP row API and the CLI read a reg column written as a name (or an oid) and give it back as its name. Each reads a name as Postgres's input function reads it (quoted or not, schema-qualified, spaces around the dot, along the search_path schema by schema with a temporary table first; int, char, "char", float(10), character varying(10), interval day, _int4, integer[], int4[][] and a table's row type for a regtype; a type name that doesn't parse is 42601) or an oid in digits, - being 0 (0 for regoper and regoperator, whose names - is one of); a name that names nothing is 42P01 for a regclass, 42704 for a regtype, regrole, regconfig, regdictionary or regcollation, 3F000 for a regnamespace and 42883 for an operator, where to_regclass, to_regtype, to_regnamespace, to_regrole, to_regcollation, to_regoper, to_regoperator, to_regproc and to_regprocedure give NULL. A reg value casts to and from oid, int4 (by its bits), int8 and text, and to no other reg type but the one it pairs with (regproc and regprocedure, regoper and regoperator; 42846); it takes no arithmetic, aggregate of numbers or function of text (42883), a string literal beside one is read as its oid ('pg_class'::regclass = 'pg_class' and = any ('{pg_class}') are 22P02, as in Postgres), the to_reg* functions take text alone (to_regclass(1259) is 42883), and beside an integer or an oid in CASE, COALESCE, GREATEST, LEAST, UNION or VALUES it stays one (text there is 42804), as regproc's do. pg_typeof gives a regtype (pg_typeof(1) = 'int4'::regtype), pg_sequences.data_type is one, pg_prepared_statements' type lists are regtype[], pg_partition_tree, pg_partition_ancestors and pg_partition_root give regclasses, and the functions Postgres gives a regclass (nextval, currval, setval, pg_relation_size and the other sizes, pg_get_indexdef, obj_description, has_table_privilege, ...) or a regconfig (to_tsvector, to_tsquery and their kin) take one. A column, a default, an ADD COLUMN value and a unique key of one keep the oid. regoper and regoperator read and show Postgres 16's 799 built-in operators (src/sql/builtin_opers.tsv: '||/'::regoper is 597, 551::regoperator is +(integer,integer), and a name several have, +, is shown pg_catalog.+ and is 42725 to read), regconfig and regdictionary its 29 built-in text search configurations and dictionaries ('english'::regconfig is 13639), regcollation the collations and regrole the roles. Differences: Chronos has no operators, text search configurations or dictionaries of its own, so those reg types know only the built-in ones (which aren't rows of pg_operator, pg_ts_config or pg_ts_dict); the oid of a relation, index, constraint, schema, enum type or domain, collation or row type made here is worked out from its kind and its name (see the catalog), so it is the same in every world and in a restored dump, and a kept value (in a table, a default, a key, a partition bound, a materialized view or a held cursor) names its object whatever else is made or dropped; but renaming the object, or dropping it and making another of its name, is as if the name were kept: the value then names the object of the name (or, after a rename, none, shown as its number), where Postgres's oid stays with the object it named (a built-in object's, a function's and a role's oid stays with it here too); '1259'::text::regclass reads the digits as an oid, where Postgres's text-to-regclass cast reads them as a name (42P01); a regclass of a catalog view ('pg_tables'::regclass) is pg_class's oid, and regtype knows the row types of pg_type, pg_attribute, pg_proc and pg_class alone among the catalog's; and a database made before this version keeps a column it made as regclass as the text it was kept as, while a regtype[] column holding type names (a copy of pg_prepared_statements) reads them as regtype does.
• void: a function returning void (pg_sleep, pg_notify, pg_advisory_lock, pg_advisory_xact_lock, pg_advisory_unlock_all, setseed, a PL/pgSQL function RETURNS void) gives the void value, as in Postgres: shown as nothing, not NULL (IS NULL is false), pg_typeof says void, and a driver gets type 2278 with an empty value (in binary too). A SQL-language function returning void gives NULL, as Postgres's does when it runs one, and what its body gives (the void value of the function it calls) where Postgres inlines it, as it does a body that is one SELECT of one void value with no FROM, WHERE, WITH, subquery or other clause (select pg_notify('c', m), select pg_sleep(0)), no more volatile than the function, strict throughout and reading every parameter if the function is STRICT, and not an IMMUTABLE one called with constants. Differences: a SECURITY DEFINER one is inlined, where Postgres runs it (NULL); an IMMUTABLE one in FROM is taken as called with constants (NULL) even beside the columns of a table before it, where Postgres inlines it; and an argument a body reads twice is not looked at, where Postgres runs the function (NULL) when that argument is volatile or costly. void has no operators but || with text, and nothing groups, sorts, tells apart (DISTINCT, or DISTINCT ON a void expression: a void column beside the ON expressions is fine) or combines (but UNION ALL) by it (42883); a table or view can't have a void column (42P16), though CREATE TABLE AS or CREATE MATERIALIZED VIEW IF NOT EXISTS over a relation already there skips before looking at its columns, as in Postgres. ''::void and a column or parameter declared void aren't read (42704).
• Indexes: a GIN index finds rows only on a tsvector; one on a jsonb or an array column (jsonb_ops and jsonb_path_ops too) is made, listed and dumped, and holds nothing, so @>, ? and && read the rows. GiST on a point column, and on a tsvector (kept as a GIN) or a tsquery column (kept as a btree, so @> and <@ read the rows); no btree on a whole jsonb column, no ON CONFLICT (expression). tsvector_update_trigger and tsvector_update_trigger_column are built in (a BEFORE ROW trigger of INSERT and UPDATE, with Postgres's messages for their arguments); on UPDATE the vector is made again when one of its text columns is assigned.
• Collations: a nondeterministic one (deterministic = false) compares, sorts and keeps unique constraints as Postgres's does, and refuses what it can't yet do by its keys (the list): GROUP BY, DISTINCT, UNION, count(DISTINCT), PARTITION BY, a subquery's rows, primary, foreign and partition keys. ICU rules and the keywords kr (reordering scripts) and kb (backwards secondary order) are refused by CREATE COLLATION (0A000). So are the 39 of Postgres's ICU collations whose languages ICU4X has no rules for, or other ones (Azerbaijani and Uzbek in Cyrillic, Uzbek and Punjabi in Arabic script, Hindi in Latin script, Konkani, Marathi, Tibetan, Hawaiian, Kalaallisut, Lakota, Lingala, Northern and Inari Sami, Yiddish: se-x-icu and the like), rather than sorted otherwise; pg_collation lists 869 ICU collations where Postgres 16 with ICU4C 78 has 908. CREATE COLLATION takes only those locales, region aliases checked to order the same (zh-TW), and the collation types ICU4X has as ICU4C does (not search, searchjl or Chinese standard); others are 0A000, and a keyword's value ICU doesn't know is XX000, as in Postgres. Also 0A000: COLLATE on a domain, a DEFAULT naming a collation made with CREATE COLLATION, an ICU collation on a PL/pgSQL variable, range partitioning by text in an ICU collation, text in one ordered against a subquery's rows (ANY/ALL, a row) or ANY (array) (x < ANY ('a', 'b') and x < (SELECT ...) work), arrays and rows of such text ordered, initcap() of text in one (ICU finds words its own way), DROP COLLATION ... CASCADE of a materialized view or of a primary key's column (Postgres drops the column and its key), and RENAME of a collation a view, CHECK, generated column or index expression names. A view, CHECK, generated column or index expression keeps a collation it names without its schema qualified with the schema it was found in (pg_get_constraintdef and pg_dump show collate a.c where Postgres writes COLLATE c while the search_path finds it). An array column can't have a collation (42804). pg_describe_object isn't there, chronos import doesn't bring a column's collation over, and a version mismatch warns but doesn't yet mark indexes for rebuilding.
• Functions and triggers: only LANGUAGE sql and plpgsql (no C, PL/Python, PL/Perl, PL/v8, ...). TRUNCATE triggers are refused (0A000); row and statement triggers, INSTEAD OF triggers on views, constraint triggers and transition tables work. PL/pgSQL has cursors, FOREACH, labels, %TYPE/%ROWTYPE, OUT and VARIADIC parameters, RETURNS record, DDL, and COMMIT in procedures (PL/pgSQL).
• Overloaded functions: a call's plain name means every function of that name on the search_path, chosen among by argument types as Postgres chooses: those the arguments fit (as they are or converted implicitly), then the most exact matches, then the most conversions to the preferred type (double precision, oid, timestamptz), then for unknown arguments (a quoted literal, NULL) the category each such place takes against all the candidates at once (text's if any takes text there), so m('1', '1') between m(int, text) and m(text, int) is 42725; a function in an earlier schema hides only a later one whose parameters taking the call's arguments have the same types (whatever defaults either takes for the rest, as Postgres decides, so f(x int) first hides f(x int, y int default 0) for f(1)), so f(int) in the first schema doesn't hide f(text) in the second; named arguments, a parameter ($1 of its declared type, a SQL or PL/pgSQL function's parameter; one a prepared statement or a driver declared no type for is unknown, as a quoted literal is), a window function's result, a cast in INSERT's VALUES and a call nested in another choose by their types too, and a set-returning function in FROM by its arguments as written; a subquery where no query binds the call (VALUES, PL/pgSQL) chooses by its value's type; a tie is 42725 and nothing that fits 42883. CALL and DROP FUNCTION find them the same way (DROP FUNCTION f and DROP PROCEDURE p without argument types only routines of their kind, and with argument types one of the other kind is 42809), and a function's body finds them when it runs, so an overload made after it is called from it. A function made here with a built-in's name (abs(text), lower(int), length(text) before pg_catalog on the path) is called where it fits the arguments better than every built-in of that name, or as well from a schema before pg_catalog, with any search_path (the default one too), in a query and wherever else a call is (VALUES, LIMIT, a default, a generated column, PL/pgSQL, ON CONFLICT DO UPDATE); one that ties with a built-in is 42725 (format('%s', 1) with a format(text, bigint)), a VARIADIC built-in (concat, format) taking as many arguments as the call gives; COALESCE, NULLIF, GREATEST and LEAST are syntax, always the built-in. Differences: real is double precision here, and varchar is text: a function of varchar is the same as one of text (making both in one schema is 42723), so one in an earlier schema hides the other in a later one where Postgres keeps both and takes the text one for 'x'; a built-in with a parameter Chronos can't weigh (an array, oid, a reg type) and one Chronos implements with syntax of its own (aggregates, window functions) is always called over a function made here; and a CHECK constraint, a generated column or an index expression where a function made here wins over the built-in is refused as any function made here is there (0A000 when a row is checked, 42P17 when the column or index is made), where Postgres calls it.
• Column defaults: a default Postgres works out afresh for each row (random(), now() + interval '7 days', timezone('utc', now()), a function of your own) is run for each row, as there. Two things differ: ALTER TABLE ... ADD COLUMN with one that gives each row a value of its own (random(), a sequence's value, a function) is refused (0A000) on a table that has rows, where Postgres fills them; and the catalog shows such a default as it was written (now() + interval '7 days'), where Postgres writes it back its own way ((now() + '7 days'::interval)). Both restore from a dump.
• Temporary tables: ROLLBACK TO SAVEPOINT doesn't take back their changes; CREATE TEMP TABLE (LIKE ...), partitioned or inheriting temp tables, MERGE and writable WITH on one are 0A000.
• Views: WITH CHECK OPTION works on automatically updatable views; no ON CONFLICT through a view, no ALTER VIEW ... RENAME COLUMN, and a table or view another view reads can't be renamed (0A000: views name what they read).
• Also: to_char's EEEE, RN, TH and V, PostGIS lines, polygons and ST_Transform, and enums in primary keys (Not yet).

Tools that work

• pg_dump (plain scripts, --schema-only, --data-only, tables and schemas by name, and the directory format in parallel, -Fd -j N) reads the catalog and writes a script that restores, into Postgres and into Chronos, to the same database (Dumping with pg_dump). Parallel dumps rely on pg_export_snapshot() and SET TRANSACTION SNAPSHOT: a transaction that imports another's snapshot reads exactly what that one reads (rows committed before it began, not its own writes), until it ends. One difference from Postgres: such a transaction only reads (a write, nextval or NOTIFY in it is 25006, as in a READ ONLY one), and its COMMIT changes nothing.
• pgbench (-i, the TPC-B transaction, -S, -N, in simple, extended and prepared mode) runs; with several clients on the same rows, retry with --max-tries (Transactions).

Chronos-only SQL

Worlds, time travel, agents and simulations are SQL statements too (Branches in SQL, Time travel, Agents, Simulations):

create world agent_7 with (owner = 'claude', task = 42);
switch world agent_7;
show worlds;                               -- name, id, parent, depth, created, version, meta, ...
diff world agent_7;                        -- table, id, change, before, after, columns
merge world agent_7 dry run;
merge world agent_7;
select * from orders as of '-1 hour';
create agent bot with (can = 'read,fork,write_own');   -- one row: its token

• To a driver they're ordinary statements. Send them through the simple or the extended protocol (tests/pg.rs runs diff and create agent through the driver's query). The ones that answer (SHOW WORLDS, DIFF, MERGE ... DRY RUN, SHOW AUDIT, SIMULATE, CREATE AGENT) return an ordinary result set, with jsonb columns for metadata and rows (meta, before, after). The others return a command tag.
• Parameters work where a value goes: select * from orders for system_time as of $1.
• Not inside a transaction: world statements fail with 25001 inside BEGIN. Drivers that open a transaction for you (psycopg and psycopg2 by default, JDBC with autocommit off) need autocommit for them.
• Names: world names with dashes need double quotes (create world "agent-7"), as identifiers do.

Differences that bite

• A multi-statement simple Query isn't atomic. Postgres runs insert ...; insert ...; sent as one Query as one implicit transaction. Chronos commits each statement as it runs, so when the second fails, the first stays. Wrap them in BEGIN ... COMMIT.

• Isolation is READ COMMITTED (the default) or REPEATABLE READ. At READ COMMITTED each statement reads what was committed before it began, plus the transaction's own writes; at REPEATABLE READ the transaction reads the database as it was at BEGIN. Reads aren't checked for conflicts, so write skew can happen, as under Postgres's REPEATABLE READ. READ ONLY (in BEGIN, SET TRANSACTION, SET SESSION CHARACTERISTICS, default_transaction_read_only, transaction_read_only) is enforced as Postgres enforces it: what would change the database (DML on tables that aren't temporary, DDL, TRUNCATE, GRANT, COMMENT, COPY FROM, SELECT ... FOR UPDATE, nextval/setval, and writes made by functions and triggers) is 25006, and rows of temporary tables may change; SET TRANSACTION READ WRITE after a query is 25001. SERIALIZABLE is refused with 0A000 rather than quietly giving less.

• Row locks wait, as Postgres's do. UPDATE, DELETE and SELECT ... FOR UPDATE | NO KEY UPDATE | SHARE | KEY SHARE hold the rows they change or lock until the transaction ends, and a statement that meets a row another transaction holds in a conflicting mode waits for it (lock_timeout, statement_timeout and a cancel end the wait; a cycle is one 40P01). It then reads the row as the other left it: at READ COMMITTED the statement runs again on what is committed (a changed row is re-checked against the WHERE, as EvalPlanQual does; it never fails with 40001), at REPEATABLE READ it is 40001 at the statement. "Lock the unit, check it's free, hold it" makes the second of two racing transactions wait and then see the unit taken. Outside a transaction FOR UPDATE holds its rows for the statement. SKIP LOCKED and NOWAIT look at what other sessions hold now: a row held in a mode that conflicts is left out of the result, before LIMIT counts rows, or is 55P03 under NOWAIT; so the FOR UPDATE SKIP LOCKED LIMIT n job queue works, in a transaction per worker, in a subquery or a WITH query too. A table without a primary key is locked by finding its rows again by their values: identical rows are told apart only by which of them is held. An error lets go of the transaction's row locks at once (with savepoints, of those taken since the innermost one), as Postgres's abort does, though the transaction still needs its ROLLBACK. Differences: a row is held at its statement's end (not as the executor reaches it), at READ COMMITTED a statement that waited runs again on a fresh snapshot, so its WHERE can also pick rows that came to match while it waited (Postgres re-checks only the rows it had found: Hermitage's PMP write-predicate case, in tests/hermitage.rs), a transaction holding more than 10,000 rows holds their tables, a foreign key check takes no FOR KEY SHARE (a child inserted under a parent another transaction deleted fails at COMMIT with 23503; Postgres makes the deleter wait), two inserts of one key meet at the next statement or COMMIT of the second (23505) rather than waiting at the INSERT, and a merged world or a write through the API takes no row locks (a transaction that wrote the row fails with 40001).

• Some conflicts still surface at COMMIT. Two transactions inserting the same primary key or unique value: Postgres makes the second wait at its INSERT, Chronos fails the second to commit with 23505 (at its next statement or at COMMIT). A row inserted under a parent another transaction deleted fails at COMMIT with 23503. What takes no row locks (a merged world, an ALTER TABLE of a table both changed) can fail COMMIT with 40001. Retry loops must wrap the whole transaction, COMMIT included (Transactions).

• The world is connection state. After SWITCH WORLD, the connection stays there until it switches back, so a pooled connection hands the next borrower whatever world it was left in. Put the world in the connection string (one pool per world), or switch back before returning the connection.

• Number counters belong to the database, not the world. serial, identity columns and sequences share one counter across all worlds, so numbers taken in a fork never collide at merge. main sees gaps where forks took numbers, setval and RESTART move the counter for every world, and time travel and RESTORE don't rewind it (Numbering rows). A transaction that restarts a serial counter (its table dropped and made again, or TRUNCATE ... RESTART IDENTITY) numbers its own rows from the start, as Postgres's new sequence does, and the counter moves past them at COMMIT; ROLLBACK leaves the old counter as it was. A writing statement's nextval (insert ... select nextval('s') from ...) reserves values for its session ahead; once another session's restart of the counter commits (ALTER SEQUENCE ... RESTART, setval, TRUNCATE ... RESTART IDENTITY, a serial table made again), those are dropped and the session's next nextval continues from the restart, as Postgres's does with no cache. As in Postgres, where sequences aren't transactional, a transaction's nextval counts by the sequence's definition as last committed: an ALTER SEQUENCE ... INCREMENT BY or MAXVALUE another session commits after the transaction began counts from its next nextval, and so does SELECT last_value FROM the sequence, also after the transaction's own TRUNCATE ... RESTART IDENTITY (which restarts it at the START last committed), while its own ALTER SEQUENCE, not yet committed, it counts by itself; that ALTER SEQUENCE changes the definition as last committed, and its COMMIT keeps it. Once a transaction has taken a value of a sequence (nextval, setval, currval) or altered it, another session's ALTER SEQUENCE of it waits for that transaction to end, as Postgres's waits for its lock, and another session's nextval, setval and currval wait for a transaction's uncommitted ALTER SEQUENCE; two that wait for each other, also through an advisory lock, fail one with 40P01. Unlike Postgres, pg_sequences.increment_by and the other options read as the transaction's snapshot has them, and a sequence another session drops stays usable to a transaction that began before; a session holding values of a sequence reserved ahead hands them out after another session drops it and makes it again under the same name, where Postgres's new sequence starts over.

• NOTIFY stays in its world. Only listeners in the same world hear it, a merge sends nothing, and a LISTEN inside a transaction takes effect at once, not at COMMIT (LISTEN and NOTIFY).

• Time zones need a tz database. Named zones with daylight saving time (America/New_York, Europe/London) work as in Postgres, read at runtime from the system's tz database (/usr/share/zoneinfo, or TZDIR; install tzdata in a minimal container). Without one, only UTC, fixed offsets and zones with one offset all year (Asia/Kolkata) are known, and another name fails with 22023, at SET or when a client asks for it at connect (JDBC sends the JVM's zone). Zone data is the OS's, so a few old dates can differ from Postgres's own copy of tzdata (the legacy CET, EET, MET and WET zones before 1980, for one). Not built: timetz and time AT TIME ZONE, SET timezone_abbreviations, datestyle's DMY input order (Dates and times).

• Text sorts by bytes unless it has a collation, as COLLATE "C" does: the database's collation is C. A Postgres database with en_US.UTF-8 orders text differently; give the columns an ICU collation (text collate "en-US-x-icu") to sort as a language does (Text order).

• ICU is ICU4X. ICU collations sort through ICU4X (pure Rust, its data compiled in), where Postgres links ICU4C. On the locales Chronos has they order text the same, except ideographs outside the Basic Multilingual Plane (CJK Extension B and later), which ICU4X sorts after the other Han ideographs and ICU4C before them; their collversion and pg_collation_actual_version() say icu4x 2.3.1, not ICU4C's 153.136, and the -x-icu collations' oids are Chronos's own (Postgres numbers them at initdb). The libc collations Postgres finds in the operating system (en_US.UTF-8, ...) are the ICU collations of their locales (en-US): glibc and ICU order a few strings differently (punctuation, spaces), and a locale ICU4X has no rules for is refused (0A000). Names earlier versions read as bytes (collate c, posix, "C.utf8", any case) still are.

• Types on the wire. real, varchar(n), char(n), json, uuid, smallint, oid, point and time with time zone reach clients as their own types, with their type modifiers; enum columns reach them typed text. RowDescription's table oid and column number are always 0, so a driver that reads them (to look a column's table up) gets nothing there.

• smallint and oid: a smallint is int2 on the wire (21; two bytes in binary, as a parameter too): arithmetic of two smallints is a smallint, checked to 16 bits (22003, smallint out of range) and << wrapped around to 16 bits, as Postgres's are; of a smallint and an integer it is an integer (mod too), sum a bigint, avg a numeric, min, max, abs, -, ~ and the bit operators smallints; CASE, COALESCE, GREATEST, LEAST, ARRAY[...], UNION, VALUES and lag's and lead's default widen it as Postgres does (ARRAY[1::int2, 70000] is an integer[]); a function of a smallint parameter is the exact match among overloads, which an integer argument doesn't fit (42883); an int2[] reads its elements as smallints (22003 past 16 bits); a PL/pgSQL smallint variable's arithmetic is a smallint's; and an index on a smallint expression past 16 bits refuses the row, and CREATE INDEX, with 22003. An oid is an unsigned 32-bit integer (26; four bytes in binary): it reads digits as Postgres's oidin does ('-1' wraps to 4294967295, 0x10 is hex, a leading 0 octal, past 32 bits is 22003), an int4 cast to one wraps around and a negative int8 is 22003, it compares and orders unsigned, has no arithmetic and no sum or avg (42883), casts to int4 by its bits (4294967295::oid::int4 is -1), to int8 by its value and to nothing else (42846), is text in json ("42"), and a column, min, max, tableoid, regclass::oid, a function's oid parameter (which takes a regproc, an integer or a string) and the catalog's oid columns are oids. An integer beside an oid is one wherever two values meet, as Postgres converts it: compared with it (oid_column = -1 is 4294967295, an int4 parameter too), in IN, = ANY and <> ALL of a list, an array or a subquery (whose column is converted), a correlated EXISTS, a join, IS DISTINCT FROM, NULLIF and a simple CASE, and among the results of CASE, COALESCE, GREATEST, LEAST, ARRAY[...], UNION and VALUES, which are then oids (with a regproc, the first of the two in Postgres's order, the ELSE first for CASE, an oid staying one as its category's preferred type; with a numeric or float 42846, with text 42804); a bigint is an oid there only from 0 to 2^32 - 1 (22003 OID out of range), and so when written to an oid column, by UPDATE, ON CONFLICT or ALTER COLUMN ... TYPE oid. An oid[] reads its elements as oids ('{-1,0x10}'::oid[] is {4294967295,16}). Among overloads an oid is its category's preferred type (f(1::int2) between f(oid) and f(int) is the oid one), and a function of text or of another number (abs, lower, length, generate_series, one made here) takes no oid (42883), nor does PL/pgSQL's arithmetic of an oid variable. Differences: generate_series of smallints is an integer series where Postgres finds it ambiguous (42725); an oid compared with a subquery's integer column given by * (o IN (SELECT * FROM t)) is refused (0A000), where Postgres converts it; and a database made before this version keeps its columns as they were made: an oid column a bigint, and a smallserial column or a CREATE TABLE AS column of a smallint expression an integer. RowDescription carries no table oid or type modifier, so tools that map result columns back to tables, or read varchar(n) and numeric(p, s) from it, get nothing.

• Errors carry Postgres's fields (SQLSTATE, DETAIL, HINT, position, and the schema, table and constraint a violation names), but some messages say more than Postgres's: a unique violation's is duplicate key value violates unique constraint "t_pkey": (id)=(1) already exists, where Postgres's stops after the name (the key is in DETAIL on both). Match on the SQLSTATE and the constraint field, not on the message text.

• server_version says 16.15. Clients that switch behavior on the version see 16. The differential suite runs against 16.

• Views look names up when they're read, along the reading session's search_path, and select * in a view picks up columns added later (Views).

• Network address types: inet, cidr, macaddr and macaddr8 work as columns, literals, casts and in the operators and functions Postgres has for them (<<, >>=, host(), netmask(), set_masklen(), ...); see sql.md.

• SQL/JSON path: jsonb_path_* and @?/@@ work as Postgres 16's do (see sql.md); jsonpath is not a distinct type (text).

• Partitioning and inheritance, known gaps: index names are database-wide rather than per schema (so \di, regclass of an index, and DROP INDEX on a partition's index differ outside public); ADD CONSTRAINT name PRIMARY KEY ignores the name (the key is <table>_pkey); PARTITION OF column lists take only NOT NULL, CHECK and COLLATE (which, as in Postgres, keeps the parent's collation); pg_partitioned_table.partclass/partcollation are 0; temporary partitioned tables and children, ALTER TABLE ... SET (fillfactor) on a partitioned table, ON CONFLICT ON CONSTRAINT <parent key> on a partitioned table, and COPY error CONTEXT lines are not supported; pg_depend lacks the partition-to-parent row. SQLSTATEs match Postgres in the cases compared; some message wording differs.

• Refused, and not listed above (0A000 unless noted): CREATE CONSTRAINT TRIGGER and CREATE EVENT TRIGGER; an EXCLUDE constraint on a partitioned table or INITIALLY DEFERRED; CREATE SCHEMA ... <objects>; EXPLAIN with FORMAT json, xml or yaml, and EXPLAIN of MERGE or EXECUTE; MERGE with INSERT ... OVERRIDING, on a view or a temp table, or without a primary key; DECLARE BINARY cursors; array_fill with lower bounds or several dimensions; ADD COLUMN serial on a table with rows; ALTER TABLE ... TYPE ... USING an expression other than the column or a cast of it; ALTER TYPE and CREATE TYPE beyond enums; a parenthesized join after an outer join or USING; ordered-set aggregates with several ORDER BY keys or OVER; DISTINCT in a two-argument aggregate. These are 42601 (not parsed): CREATE, ALTER and DROP of AGGREGATE, CAST, CONVERSION, FOREIGN TABLE, FOREIGN DATA WRAPPER, SERVER, LANGUAGE, OPERATOR, PUBLICATION, SUBSCRIPTION, RULE, TABLESPACE, TEXT SEARCH ..., STATISTICS, ALTER DOMAIN, ALTER EXTENSION (but pgcrypto's UPDATE and SET SCHEMA), SECURITY LABEL, LOAD and a bare CHECKPOINT.