Chronos/DBWaitlist

Changelog / Chronos DB

Chronos DB 0.2.0

The first public beta: agents' changes to your Postgres data, reviewed and checked before they land

Beta518 changesDownloads

The first public beta: agents' changes to your Postgres data, reviewed and checked before they land. Chronos is not Postgres and not yet used in production; this release is for trying it on a staging copy of your own data. It can now work beside the Postgres you already run, or take your data in.

New since the 2026-10-16 notes below:

  • Attach: work beside your existing Postgres. chronos attach <new folder> --tables a,b (or --schema s) copies tables of a Postgres database (CHRONOS_SOURCE_URL) in one consistent read. Agents work in worlds as anywhere; merging a world into main writes its rows to Postgres first, in one transaction, after checking each row is still as main holds it. A row changed in Postgres meanwhile comes back as a conflict, nothing is written, and main takes Postgres's row. Values a Postgres trigger or default sets come back into main. Main takes no direct writes, no table changes shape, and an agent's merge with no merge policy of its own waits for a person. A crash on either side of COMMIT is settled at the next merge or start. Tested on Neon, straight and through its pooler (BENCHMARKS.md §14). Limits: staging copies, at most 10,000 rows per merge, no refresh yet. See Working beside your Postgres.
  • Serial and identity ids under attach come from Postgres's own sequence, reserved 1,000 at a time and kept in the folder, so an id a world gives a row is never given to another by Postgres and is the id written at merge. An id typed by hand into such a column is refused at merge.
  • The engine's source is public under the Business Source License 1.1 (free to use, including in production, except to offer Chronos as a hosted database service; each version becomes Apache 2.0 after four years), at github.com/Abhishekxdg/chronosdb.
  • How merges end: SHOW MERGE OUTCOMES [BY POLICY], and the HTTP op merge_outcomes. Each agent's worlds within the history window, counted once by how they ended (merged on the rules alone, merged with no policy, approved, rejected, dropped, open, partly merged), with how often their merges were held, conflicted, read rows that changed, or broke a merge check, and rules_rate: the share of decided worlds that merged on the rules alone. A refused merge now leaves a count in its world's metadata (_sonos_refusals, set only by the database), so the counts come from history and survive a restart; a merge that goes through does no new work. For the database's own users only. Merges later undone still count as merged. See SQL.
  • MCP over HTTP: chronos serve answers MCP at POST /mcp, as the agent whose token is sent (the guest under --safe), so an agent in a container or on another machine connects with its token alone, never the folder. See MCP over HTTP and agents in containers.
  • No false deadlocks between row waits: a statement waiting for a row could fail with 40P01 when the transaction it waited for committed, began another and waited for a row the first one held: the deadlock check followed it as if it still held the row. Seen as a rare failure of a TPC-B loop from eight clients.
  • The TypeScript client is on npm: npm install @chronosdb/client (clients).

As measured for the 2026-10-16 notes:

  • Postgres 16's regression suite: 83.69% of its expected lines, 46 of its 219 files exactly the same.
  • Real databases moved in during the migration trials, with chronos import or a pg_dump replay, checked with row counts and row checksums: 7 of 7 identical (Chinook, Northwind, pgbench at 2M rows, Pagila after one manual fix since made unnecessary, a 50-construct synthetic schema, a 47-type micro schema, a Rails-style application, a multi-tenant schema with row-level security and NOTIFY).
  • Speed on the same machine as Postgres: point reads at about half Postgres's rate, writes at a fraction of it.

What changed most:

  • Transactions behave as Postgres's: READ COMMITTED is the default and REPEATABLE READ is there, row locks wait (FOR UPDATE, SKIP LOCKED, NOWAIT), READ ONLY is enforced, and SERIALIZABLE is refused (0A000) rather than given less under its name.
  • Moving a database in: chronos import postgres://... carries types (enum, composite, range, domains), partitions and inheritance, identity columns, exclusion constraints, SQL and PL/pgSQL functions and procedures, aggregates, triggers, rules and comments, and reports what it brings in part or not at all.
  • Postgres's SQL, much further: roles and privileges, row-level security, SCRAM-SHA-256 logins, ICU collations, pgcrypto, citext, hstore, ltree, pg_trgm, all 363 of Postgres 16's settings, the catalog's built-in functions and reg types, PL/pgSQL cursors and records, multi-dimensional arrays, json as its own type.
  • Reliability fixes from the trials: sequences never repeat a value after a crash, max_connections is enforced honestly, oversize wire messages fail the statement instead of the connection, idle timeouts are enforced.
  • Release: a draft release with static Linux builds (x86_64 and aarch64, musl), macOS builds (Apple silicon and Intel; they need macOS 13 or later, where 0.1.x ran on macOS 11), SHA-256 sums and cosign signatures; a Dockerfile (non-root, data in a volume); new docs: Quickstart, Migrating from Postgres, and where the beta stands.
  • Shared servers say when the token is too strong: chronos serve on an address other machines can reach, without --safe and with protect_main off, warns that its token changes main directly, with no world and no review; the hardened deployment checklist now says to keep server and admin tokens out of agents' environments and to turn protect_main on.

Upgrading from 0.1.x:

  • 0.1.x may not open a database 0.2.0 has written: the write-ahead log has new record kinds (RebaseTx for READ COMMITTED statements, Rebase for REBASE WORLD), and a database whose log holds one can't be opened by an earlier version. Take a backup before opening an existing database with 0.2.0 if you may want to go back.
  • History: a database made by 0.2.0 keeps 1 hour of history (alter system set history_retention = '30 days' keeps more); one made by 0.1.x keeps the 30 days it had.
  • char(n): rows a database already holds with trailing blanks keep them until they are rewritten (update t set c = c); new rows are stored without them.
  • Known gaps worth planning for: ALTER TABLE ... RENAME TO and SET SCHEMA rewrite every row (about 6.5 s per million rows); C-language functions and extensions, custom access methods and EXPLAIN's plan shapes are not there; function signatures with varchar show text. The list: Postgres compatibility.

Everything in detail:

Studio screens for 0.2.0 (cluster K2)

  • Import from Postgres (File → Import from Postgres…, or ⌘K): the import chronos import postgres://... runs, into the open database, from a form (host, port, database, user, a masked password, sslmode, the schemas and tables to bring over, a dry run, and the plaintext-password allowance with its warning). It runs on the server as a job the page polls, with tables and rows as they come, a Cancel, and the report and per-table rows at the end. A database that has tables already gets the import in a new world, to review in Changes and merge. The password goes only in the request's body and is wiped once connected; it is never kept, logged or sent back.
  • Sessions (View → Sessions): the Postgres port's connections, refreshed every 2 s, with whose transaction a waiting session waits for and on which table, long and idle-in-transaction ones marked, and Cancel query and Terminate (pg_cancel_backend, pg_terminate_backend, with their checks).
  • pg_blocking_pids(pid) gives the backends holding up a session's wait for a row, an advisory lock or a sequence, and pg_stat_activity's wait_event_type/wait_event say Lock/transactionid (a row) or Lock/advisory; they were always null.
  • Settings → History: history_retention, what it buys and costs, changed there (ALTER SYSTEM SET, a shorter window confirmed first), with the disk split as live data, history, log and reclaimable (SHOW DISK).
  • Agents: an agent made before agents had SCRAM verifiers is marked "needs one HTTP login before Postgres logins" (the agents op's needs_http_login).

Studio and the HTTP API (a click-through of Studio)

  • API change: numeric values in sql results are JSON strings with their scale ("15.00", "16.50"), as Postgres shows them; they were JSON numbers, which clients read as doubles (15, 16.5, and digits lost past about 15). Each result also has types, its columns' Postgres type names. See values in sql results.
  • real (float4) values come with float4's shortest digits (0.1) in sql results and in the rows of get, find and diff; they had a double's (0.10000000149011612). What is stored is unchanged.
  • History and the audit trail count a write's table rows: an UPDATE of 6,666 rows said 19,998, counting the entries behind its table's indexes and constraints. Events already in the log from big statements (past CHRONOS_WORK_MEM) keep the count they had.
  • Studio lists views and materialized views under Tables (an eye icon), and opens them read only.
  • Studio's worldline, As of picker and History tab say how far back history goes (history_retention, 1 hour for a new database) and grey out earlier moments with the reason, instead of failing with 22023.

Last fixes before the beta (cluster J1)

  • (outer.c).f in a LATERAL or other subquery, for an outer row whose composite column is NULL, is null as in Postgres; it failed with 42809 column notation .f applied to type text.
  • chronos import creates a materialized view Postgres holds unpopulated (WITH NO DATA, such as Pagila's rental_by_category) unpopulated: reading it is 55000 until REFRESH MATERIALIZED VIEW, as in Postgres. It was filled.
  • Docs: what the search index behind column = value on an unindexed column really holds in memory (measured: 1.57 GB for a 150,000-row table, four versions per table kept), and how to bound it; mimalloc's lazy return of freed memory (operations).

Authentication and network hardening (code review of the auth and network code)

  • Tokens: chronos serve refuses to start with a server or admin token shorter than 24 characters, or with change-me (the placeholder the examples used): make one with openssl rand -hex 24 and pass it as CHRONOS_TOKEN, not --token, which other users see in ps. A server started with a short token before must be given a new one.
  • TLS for remote listens: an address that isn't loopback needs --tls-cert and --tls-key, or --insecure-no-tls (a warning at start) where a VPN, a TLS proxy or a loopback-only published port protects the connection. The Dockerfile's command passes --insecure-no-tls, and its examples publish the ports on 127.0.0.1 only.
  • Failed logins wait: after 10 wrong passwords or tokens from one address (the Postgres port and the HTTP API together), the address waits 1 s before its next try, doubling to 60 s; a try meanwhile is refused at once (28P01, HTTP 429). CHRONOS_LOGIN_FREE_TRIES changes the 10.
  • chronos import postgres://... never sends a password in the clear or as MD5 over a connection that isn't TLS: a source configured for password or md5 is refused unless the connection is TLS (the default sslmode=prefer takes TLS where offered) or --allow-plaintext-password is given; SCRAM-SHA-256 is unaffected. It now gives up connecting after connect_timeout (30 s by default), drops a source silent for 10 minutes, and refuses a malformed or oversize message instead of panicking.
  • Server files take Postgres's privileges: with CHRONOS_ALLOW_SERVER_COPY=1, COPY ... FROM '/path' needs the privileges of pg_read_server_files and COPY ... TO '/path' those of pg_write_server_files (42501 permission denied to COPY from a file, as Postgres says it); lo_import/lo_export a superuser; a file_fdw table's filename pg_read_server_files, to make it and here to read it. Agents and the guest never may (an agent that is no role used to count as a superuser there). Before, the variable alone let any session read and write the server's files.
  • A session's transaction world is its own: an agent's sql, simulate and replay HTTP ops, a session opened on a _tx_… world by name (or name@when), and USE WORLD of one are refused (404, 3D000), as the other ops already were.
  • The Postgres port's FunctionCall (libpq's large-object fast path) parses its integer arguments (22P02, 22P03, 08P01) instead of pasting them into SQL.
  • psql's catalog queries answered by the Postgres port get an agent's and a role's checks first; cancel keys come from the system's CSPRNG; HTTP responses carry X-Content-Type-Options: nosniff, Cache-Control: no-store and, over TLS, HSTS.
  • An agent made before agents had SCRAM verifiers is asked for SCRAM like any name on a server with --token (so the exchange no longer shows it is an agent): it gets its verifier from its first HTTP API request with its token, and logs in over the Postgres port from then on. On loopback without a token it still sends its token in the clear once.

A new database keeps one hour of history, not 30 days

  • History (time travel, UNDO AGENT, UNDO MERGE, the audit trail) keeps old pages and log for its whole window, so a busy database's disk grew with it (10 to 29 GB in 91 minutes for 2.2 GB of live data at 30 days). A database made now keeps 1 hour (at one day a soak still grew 5 to 35 GB in 77 minutes of heavy writes); alter system set history_retention = '30 days' keeps more. A database made by an earlier release keeps the 30 days it had: an upgrade drops no history.

Big transactions don't hold their rows in memory (reliability trials)

  • A COPY, INSERT, UPDATE or DELETE of millions of rows inside BEGIN ... COMMIT (pg_restore -1, psql --single-transaction, pgbench -i, Rails and Django migrations), and CREATE TABLE AS, spilled as it does outside a transaction, but its COMMIT then merged every row into the world one by one in memory and left them there until the next checkpoint: about 400 bytes a row, 7.8 GB for 20 million rows by COPY. COMMIT now hands the transaction's state to its world whole when nothing was committed there since the transaction last read it; when other sessions committed meanwhile, it first brings the transaction up to what they committed, as a READ COMMITTED statement does, then checks its rows on the result a part at a time. The same 20 million rows peak at 684 MB, as outside a transaction (see operations). What a transaction sees, what others see before its COMMIT, what fails it, and what is stored are as before.

An aborted transaction lets go of its locks at the error (reliability trials)

  • An error in a transaction lets go of its row locks and transaction-level advisory locks at once, as Postgres's AbortTransaction does; with savepoints, only what was locked since the innermost one (AbortSubTransaction). An aborted transaction (a failed statement, a deadlock victim, a lock or statement timeout, a constraint, an error in a function, a trigger, a DO block or an extended-protocol message) used to hold its rows until the client's ROLLBACK, so other sessions waited until their lock_timeout, or forever. A RELEASEd savepoint's row locks now pass to its parent, so a later ROLLBACK TO no longer drops them.
  • A client past max_connections gets its SSLRequest answered and FATAL 53300 after its startup message, as Postgres does (psql said "server sent an error response during SSL exchange"); a cancel request is served then too.

ALTER TABLE changes that Postgres makes in its catalog alone cost no row writes (migration trials)

  • Every ALTER TABLE on a table with an index, a unique constraint, a foreign key or a CHECK used to take every entry kept behind them out and make it again (a 1M-row table with one index: RENAME COLUMN 16 s, DROP COLUMN 24 s). Now only what the change touches is written: RENAME COLUMN, ADD COLUMN (a constant, now(), current_date or current_user default is kept once, not per row), DROP COLUMN (the entries of the indexes, keys and foreign keys on that column go, no other), SET/DROP DEFAULT, SET/DROP NOT NULL, ADD ... NOT VALID, VALIDATE CONSTRAINT (reads the rows, writes none), DROP CONSTRAINT, COMMENT, and ALTER COLUMN TYPE to a type that holds what the column does (varchar(n) to text or a longer varchar, numeric(p,s) to a wider one, integer to bigint with nothing indexed on it) write the table's schema and nothing else. ADD FOREIGN KEY and CREATE INDEX read the rows once. A renamed index, unique or foreign key constraint has its entries moved (written, not made again from the rows: no row is read); a renamed table moves its rows, and its entries with them. Nothing stored changes shape: entries and rows written before are read as they were.
  • ALTER TABLE ... ADD COLUMN ... DEFAULT now() (or current_timestamp, current_date) on a table with rows was refused (0A000); the rows there take the value it has when the statement runs, as in Postgres.

Reliability trials: sequences after a crash, connections, limits (round 2)

  • A sequence value is handed to a client only after the log record that covers its block is durable, whichever caller reserved the block. A caller taking values from a block another had just reserved could return before that record was on disk: a crash then gave the same numbers again (nextval 80129 after a restart, with 80147 already handed out). Gaps after a crash remain, as in Postgres; repeats do not.
  • max_connections is honest: the process's open-file soft limit is raised to the hard limit at start (a connection holds about 5 descriptors, was 7), connections past what the descriptors hold get 53300 sorry, too many clients already instead of being dropped, and SHOW max_connections says the number enforced.
  • A wire message past the 64 MB limit is read and dropped, and answered 54000 (the connection and its transaction state stay as after any failed statement) instead of resetting the connection; a length Postgres refuses (over 1 GB) is FATAL 08P01 invalid message length.
  • idle_in_transaction_session_timeout (FATAL 25P03) and idle_session_timeout (FATAL 57P05) are enforced.
  • More than 1600 columns in CREATE TABLE, ADD COLUMN, inheritance, CREATE TABLE AS, CREATE VIEW or a materialized view is 54011 tables can have at most 1600 columns, dropped columns counting as Postgres counts them.
  • A backup removes the .NAME.partial folders interrupted backups left beside it (never one a running backup holds), and a backup into a name another is using is refused.

READ COMMITTED, as Postgres's default (migration trials)

  • Transactions were snapshot isolation whatever level was asked for: a statement never saw a commit made after BEGIN, SELECT ... FOR UPDATE SKIP LOCKED workers claimed rows twice and failed at COMMIT (25 duplicate claims in 200 jobs, 5 crashed workers), an UPDATE of a row another transaction had changed failed, and pgbench's TPC-B at 8 clients lost 19% of its transactions to 40001. READ COMMITTED is the default now, and REPEATABLE READ (BEGIN ISOLATION LEVEL ..., SET TRANSACTION ISOLATION LEVEL ..., default_transaction_isolation) keeps one snapshot: SHOW transaction_isolation says which.
  • Each statement of a READ COMMITTED transaction reads what was committed before it began. An UPDATE, DELETE, MERGE, INSERT ... ON CONFLICT DO UPDATE or SELECT ... FOR UPDATE takes its rows, and one that meets a row another transaction is changing waits for it (lock_timeout: 55P03; a deadlock: one 40P01), then reads the row as that transaction left it and re-checks the WHERE; at REPEATABLE READ such a write is 40001 at the statement, as Postgres's. Row locks of FOR UPDATE, FOR SHARE and the others wait instead of failing the later COMMIT; SKIP LOCKED and NOWAIT see what other sessions hold; a ROLLBACK TO lets go of what was taken since its savepoint. The write-ahead log gains one record kind (Rebase): a database that wrote one can't be opened by an earlier version.

Writes made by functions are kept

  • A function that writes a temporary table (from a SELECT, from DML, in a transaction that commits or rolls back) keeps its rows, as Postgres does: the writes were lost before. A function that is not VOLATILE may not change anything (0A000 INSERT is not allowed in a non-volatile function). A volatile function in a correlated subquery, as an index key (pk = f(2)), under OFFSET, or in an IN list now runs as often as Postgres runs it, so its writes are no longer skipped. CREATE TRIGGER on a temporary table is refused (0A000).

Batch 11 of the regression suite's differences (83.61% of expected lines)

  • Constraint triggers and foreign keys of partitioned tables (partition clones, referential-action triggers); domain constraints everywhere (NOT VALID, VALIDATE); object addresses; PL/pgSQL leftovers (labelled blocks, context lines); numbers of any size (numeric has no limit but Postgres's, NaN and Infinity order in indexes, so range scans see them; REINDEX an index built before to include such rows); ALTER TABLE column storage, statistics and compression options with dropped columns' attnums; statistics views and functions (pg_stat_* count scans, tuples and function calls).

char(n), real and sum(bigint) as Postgres has them (migration trials)

  • char(n) values compare without their trailing blanks (c = 'ab', IN, ordering, keys, unique constraints, GROUP BY, joins) and length(), ::text, || and string_agg drop them, as Postgres's bpchar does; the padded form is written where Postgres writes it (query output, rows, arrays, JSON, XML mappings). Rows a database already holds padded keep the padding until they are rewritten (update t set c = c); new inserts, COPY and imports store the stripped form.
  • real is written with its float4 digits in rows, arrays and JSON (9.8, not 9.800000190734863); real beside an integer, numeric or double precision is double precision; sum(real) is real. sum(bigint) is numeric and no longer overflows.
  • A long chain of || is planned in linear time (28 terms took 47 s). to_tsvector and ts_parse cut tokens as Postgres's default parser does (a port of its state machine).

Moving a Postgres database in: types, partitions, identity, programs and comments

  • chronos import carries domains (default, NOT NULL, CHECKs), composite, enum and range types in any schema (in the order they depend on one another, so a table with a domain or composite column is no longer dropped), partitioned tables with their partitions, inheritance, GENERATED ALWAYS|BY DEFAULT AS IDENTITY with its options, exclusion constraints, sequences' CACHE, SQL and PL/pgSQL functions and procedures, plain aggregates, triggers, rules and comments. What it brings over in part (an index's hash method, DESC, INCLUDE, operator classes) or not at all (functions in other languages, operators, event triggers, publications, foreign tables) is listed in the report.
  • A composite type of a schema other than public was read as an enum: its values failed (invalid input value for enum s.c), so a pg_dump script lost the table's rows. It is a composite type there as in public, as a column, an array's element, a function's argument or through the search_path.
  • COMMENT ON an enum type or a function is kept (pg_description, obj_description). A sequence keeps its CACHE (pg_sequences.cache_size). ntile gives an integer. A CHECK on a function call is written back as CHECK ((char_length(title) >= 1)), not with spaces between tokens. server_version says 16.15.

Queries applications run that were refused (migration trials)

  • public.uuid_generate_v4() (what pg_dump writes as a default) and the other functions of extensions built in here are called by the schema they were installed in; public.f(x) calls a user aggregate made by CREATE AGGREGATE (kept by its name); OPERATOR(schema.*), /, %, ^ and a user's own operator are read with their schema as + and = were.
  • A trigger function may return coalesce(new, old) (or a CASE of them): any expression that is a row of the triggering table's type, or NULL, as Postgres accepts; anything else is 42804 cannot return non-composite value from function returning composite type.
  • SELECT ... FOR UPDATE [NOWAIT | SKIP LOCKED]: SKIP LOCKED leaves out rows another open transaction holds locked or has written, before LIMIT counts, and NOWAIT is 55P03 on one, so the FOR UPDATE SKIP LOCKED LIMIT 1 job queue works, in a subquery or a WITH query too. A table without a primary key can be locked (tests/row_locks.rs).
  • tsvector_update_trigger and tsvector_update_trigger_column are built in, and CREATE INDEX ... USING gist on a tsvector or tsquery column is accepted (tests/pgdiff.rs).
  • GROUP BY of a composite's field ((v).a, or 1 for it), and of a set-returning function in the select list (unnest(arr) ... GROUP BY 1).
  • A citext column cast to text has the default collation, and a citext value meeting text of another collation takes that collation, so concat_ws(',', a::text, b::text) of a citext and a varchar COLLATE "C" column works.

READ ONLY transactions

  • BEGIN READ ONLY, START TRANSACTION READ ONLY, SET TRANSACTION READ ONLY, SET transaction_read_only = on, and for the transactions to come SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY and default_transaction_read_only = on, were accepted and ignored: writes went through. They are enforced now, as Postgres enforces them: INSERT, UPDATE, DELETE and MERGE of tables that aren't temporary, COPY FROM, every CREATE, ALTER and DROP (CREATE TEMP TABLE too), TRUNCATE, GRANT, REVOKE, COMMENT, SELECT ... FOR UPDATE/SHARE, nextval() and setval(), and what a function or trigger called in one writes, are 25006 cannot execute INSERT in a read-only transaction. Rows of temporary tables may change. SET TRANSACTION READ WRITE after a query is 25001, as inside a savepoint of a READ ONLY transaction; SHOW transaction_read_only says which a transaction is.

Batches 9 and 10 of the regression suite's differences (83.04% of expected lines, from 81.40%; 46 of 219 files exact, from 35)

  • Privileges: failing rows filtered to the columns the role may see, large object ACLs, aggregates dropped or altered only by their owners, EXECUTE checks, role grant chains.
  • Subqueries, set operations and joins read as Postgres reads them: alias-column joins, USING joins referenced from outer queries, aggregates of outer-only columns refused where Postgres refuses them.
  • Aggregates and grouping sets with Postgres's names, errors, order and float/numeric results; psql's describe queries and Postgres's own catalog rows.
  • Foreign keys, domains, row types; RELEASE SAVEPOINT keeps deferred foreign key checks; rules run with their statement as one write.
  • JSON, jsonpath and SQL/JSON details; XML as libxml writes it; erf/erfc and float4 output; dates and times (typmod casts, date_trunc of dates, zone abbreviations, to_timestamp separators).
  • DML and sessions: degenerate HAVING, EXECUTE argument errors, COPY HEADER MATCH and DEFAULT, COMMIT AND CHAIN, DateStyle, cached result types; TRUNCATE, sequences, partition_info, partitioned indexes (ATTACH PARTITION of an index whose definition differs is 42P17, as in Postgres), views (updatability, defaults, leaky-view quals), names, "char", VARIADIC arrays, parse_ident, width_bucket over arrays, range functions.

Batch 8 of the regression suite's differences (81.40% of expected lines, from 80.15%)

  • WITH RECURSIVE's SEARCH DEPTH/BREADTH FIRST and CYCLE clauses, rewritten as Postgres rewrites them; recursive queries of the wrong form refused with Postgres's errors (42P19); a reference inside a subquery reruns each round; data-modifying WITH on temporary tables, and rules on its targets as Postgres takes them.
  • EXPLAIN (COSTS OFF) of one-table plans as Postgres's planner writes them: Append over the partitions pruning leaves (list, range and hash, defaults, NULLs, several-column keys, sub-partitions), Seq Scan with its Filter written and ordered as Postgres does, Sort, Aggregate, Limit, Result, UPDATE and DELETE of trees. Joins, GROUP BY, indexes and the rest keep the old output (they turn on costs).
  • Inheritance and ALTER TABLE: NOT VALID CHECK and FOREIGN KEY constraints and VALIDATE CONSTRAINT; inherited CHECKs merge as Postgres's; DROP COLUMN, ONLY, RENAME and ALTER TYPE through trees by Postgres's rules; a user's comparison operator falls back to the built-in one for types it doesn't take.
  • Triggers: on views (INSTEAD OF row, BEFORE/AFTER statement), information_schema.triggers, pg_trigger_depth(), ALTER TRIGGER RENAME, ENABLE ALWAYS/REPLICA through partitions, INSERT ... ON CONFLICT DO UPDATE firing UPDATE triggers, transition tables of a partitioned parent. Rules: fired in name order (an INSERT's after it), ALTER RULE RENAME, an ON SELECT rule no longer makes a table a view.
  • Polymorphic functions resolve their types as Postgres does (anycompatible's common type, ranges, multiranges) with its errors; VARIADIC in expanded form only; parameter modes and defaults checked; composite results expanded in FROM; a user set-returning function in the select list; a SQL function's array-state step appends.
  • numeric(p, s) with a negative scale or scale past precision; numeric input with underscores and 0x/0o/0b; ln, log, exp and fractional power in numeric; to_char of numbers ported from formatting.c; tsquery operators and @@ as Postgres's TS_execute; Unicode escape errors.
  • CLUSTER marks its index and rewrites a table without a primary key in the index's order; publications (CREATE/ALTER/DROP PUBLICATION, the pg_publication catalogs, \dRp+); extended statistics objects (CREATE/ALTER/DROP STATISTICS, pg_statistic_ext).
  • Known, left for later: rule commands calling nextval can run twice when the statement is retried (rules commit command by command); an unbounded varchar compared with text isn't written (b)::text in EXPLAIN.

Batch 7 of the regression suite's differences (80.15% of expected lines, from 79.41%; 35 files exact)

  • View definitions as Postgres writes them back: pg_get_viewdef (plain, pretty, a wrap column), pg_views and pg_matviews, \d+ of a view and pg_get_triggerdef's WHEN come from the parsed query, as ruleutils.c writes it (columns qualified where more than one relation is read, * as its columns, Postgres's casts, line breaks and parentheses); a form not yet written so falls back to the text as written. pg_views lists Postgres's own system views too.
  • Window functions: rows that tie on a window's keys come out in Postgres's order (its quicksort and incremental sort, its order of windows); RANGE offsets of every type Postgres takes, with its errors; the window-misuse errors in Postgres's words; GROUPING() in a subquery of a grouped query; GROUP BY DISTINCT and SELECT DISTINCT with grouping sets; a ROLLUP in GroupAggregate's order.
  • Foreign data wrappers, servers, user mappings and foreign tables as catalog objects: CREATE/ALTER/DROP with OPTIONS, owners and grants, postgresql_fdw_validator's checks, DROP ... CASCADE, IMPORT FOREIGN SCHEMA's errors, pg_foreign_* and information_schema's foreign_* views, psql's \dew \des \deu \det. Reading or writing a foreign table is Postgres's "foreign-data wrapper has no handler" (55000).
  • PL/pgSQL: a whole record as text is a row, (3,30), as Postgres writes it (not JSON); NEW.* and r.* in statements; cursors with arguments, SCROLL, refcursors returned and fetched in SQL, FOR over a bound cursor, MOVE/FETCH by count; errors and notices carry Postgres's CONTEXT (PL/pgSQL function f(integer) line 3 at RAISE) and RAISE's DETAIL and HINT; ALIAS FOR; IF count(*) = 0 FROM t ... THEN.
  • json is its own type (oid 114): it keeps its text as written (spacing, key order, duplicate keys), with Postgres's layouts for json_build_object, json_agg, row_to_json and the rest; ->, ->>, json_each and json_array_elements read the text. jsonb is as before.
  • Arrays of more than one dimension and lower bounds other than 1: a[1][2], slices of several dimensions, '[0:2]={1,2,3}', array_dims, array_ndims, array_lower/upper/length by dimension, array_cat and || by Postgres's rules, array_agg of arrays, array_fill with bounds, assignment a[2][1] = x, and Postgres's "malformed array literal" messages. The binary wire format of a multi-dimensional array is still one-dimensional.

What stopped the Postgres regression suite most (batch 5)

  • libc collation names (en_US, de_DE.UTF-8, sv_SE.utf8), in COLLATE and in CREATE COLLATION (provider = libc, ...) or (lc_collate = ..., lc_ctype = ...), are the ICU collation of that locale (they were refused, 0A000). Chronos has no libc locales; glibc and ICU order a few strings differently, punctuation and spaces among them. A locale ICU4X has no rules for is still refused.
  • MERGE into a table without a primary key: its rows are matched by all their columns.
  • MERGE follows Postgres 16's merge.sql: ONLY, a join as USING, MERGE into a temporary table, its analysis errors in Postgres's words (an unreachable WHEN, a reference to the target where it can't be read, an ambiguous column, a system column, a WITH query or COPY that is a MERGE), statement triggers for each action it has and row triggers one row at a time, as Postgres fires them, and transition tables per action.
  • MERGE in a PL/pgSQL function or procedure, its variables and NEW.f fields in the ON, WHEN and actions.
  • WITH ... VALUES (...), WITH ... TABLE t and WITH ... (query) (WITH went with SELECT, INSERT, UPDATE, DELETE and MERGE only).
  • CREATE [OR REPLACE] RECURSIVE VIEW v (cols) AS ..., which is CREATE VIEW v AS WITH RECURSIVE v (cols) AS (...) SELECT cols FROM v, as in Postgres.
  • A temporary table may inherit from, or be a partition of, another temporary table. A temporary table under a permanent one is still refused (0A000).
  • CREATE TYPE name (a shell type), and the CREATE TYPE name AS (...) that fills it in. A base type (CREATE TYPE name (INPUT = ..., OUTPUT = ...)) is refused: its functions would be in C.
  • pg_tablespace_location(oid) with an oid (it was refused as a function of no such arguments).
  • Assignment through fields and subscripts: INSERT INTO t (f3.if1, f2[1]) ..., UPDATE ... SET r.f = ..., a[2:3] = ..., jb['a'][0] = ... (jsonb's subscript assignment, with Postgres's 22023 and 22004). $1.f, (p).f.g and a[1].f read fields. A subscript of a type that has none ((now())[1]) is 42804 as in Postgres; point, line, lseg and box take subscripts from 0.
  • The geometric types follow Postgres's geo_ops.c: its EPSILON comparisons, input parsers, every operator in pg_operator, the prefix operators (@@, @-@, #, ?-, ?|), casts between the types, and overflow and division-by-zero errors.
  • ctid: a query may read it from a plain table (select ctid, * from t, where ctid = '(0,1)', comparisons, ORDER BY). It is where the row would be were the table written afresh in its order, on Postgres's 8 kB pages, so it changes after a DELETE or UPDATE, and UPDATE or DELETE ... WHERE ctid and WHERE CURRENT OF don't read it yet.
  • Views that can't be written through say why, in Postgres's words (DETAIL and HINT); information_schema's tables, views and columns say which views and columns take writes. A table's name is its row type in a cast; int2vector, oidvector, the pseudo-types and more catalog types are known by name.
  • Row-level security as Postgres's rowsecurity.sql has it: a policy loop is 42P17 and row_security = off where a policy applies is 42501 before any permission denial (Postgres's rewriter runs first); CREATE VIEW ... WITH (security_invoker) and ALTER VIEW ... SET/RESET (security_invoker) read and write as the caller; row_security_active(table); COPY FROM with row_security off is 42501; a unique or foreign key error hides the key from a role that can't see the whole table; pg_table_is_visible is false for a table the search_path doesn't reach; policies list in the order they were made.
  • json and jsonb as Postgres's json tests have them: JSON input errors in Postgres's words, with its DETAIL and CONTEXT (JSON data, line 1: ...), \u0000 refused (22P05), nesting past 1000 levels 54001; columns named by the function called (jsonb_set, to_jsonb) and ?column? for @? and #-; named arguments for jsonb_path_*, jsonb_set, jsonb_set_lax and jsonb_insert; jsonb_contains, jsonb_exists, jsonb_concat, jsonb_delete and the rest of the operators' functions, jsonb_delete_path, pg_column_size; path errors with their positions; jsonb cast to boolean or a number refuses another kind; json[b]_populate_record(set) and json[b]_to_record(set) fill arrays and composites; json_object(text[]) of two dimensions. json still keeps no text of its own: it is jsonb, normalised.
  • CREATE AGGREGATE in every form Postgres's tests write: the old (BASETYPE = t, SFUNC = f, STYPE = t) and sfunc1/stype1/initcond1, (*), VARIADIC, named arguments, ORDER BY and HYPOTHETICAL signatures, the options that only tune (read and not used), and Postgres's checks and messages at definition (support functions found by their argument types, among the built-ins too; strictness; polymorphic types). A strict state function skips nulls and starts from the first value, as in Postgres; a non-strict one now sees every row, nulls too. ALTER AGGREGATE ... RENAME, COMMENT ON AGGREGATE, DROP FUNCTION of an aggregate's function (2BP01, or CASCADE).
  • LANGUAGE internal AS 'int4pl' and the like: a function by one of Postgres's C symbols is the built-in it names (an operator, textcat, a comparison, a function); a symbol Postgres hasn't is 42883. Ones that take cstring or internal (type I/O) aren't, nor WINDOW.
  • Date and time input is Postgres's datetime.c, ported: Julian days (J2452271.5), run-together ISO forms (20011227T040506-08), allballs, zone abbreviations with DST (MET DST), POSIX zones, today 10:30, tomorrow EST, two-digit years by DateStyle, interval typmod fields and the SQL-standard sign rule, and Postgres's errors (field out of range with the datestyle hint, 22009, 22023 for an unknown zone). to_char follows formatting.c in full (FM, TM, TH, RM, IYYY, FF1 to FF6, OF, TZH, TZM, ...), and takes an interval. RESET TIME ZONE returns to the session's starting zone, not UTC. Also: date - time, date + timetz, date_add, date_subtract, date_bin with Postgres's checks, make_timestamptz's zone, to_timestamp(±Infinity, NaN), SET TIME ZONE '-1.5' and POSIX rules, interval output signs as Postgres's. A two-digit year written first ('44-03-15') under MDY is 22008, as in Postgres.
  • After review of #122: WITH a AS (...), b AS (SELECT ... FROM a) MERGE ... USING b puts a in b (it was left unnamed); UPDATE pg_class AS c SET reltuples = ... WHERE c.relname = ... keeps its alias; a nested ARRAY whose parts differ in shape is 2202E even when they hold as many elements; pg_stat_statements records each statement of a several-statement query by its own text.
  • A query of temporary tables only, where one has children (INHERITS or partitions), runs among them, so the parent's rows include its children's; one that also reads a table that isn't temporary is refused (0A000) rather than missing them. A shell type is filled in by the CREATE TYPE that defines it.
  • What made later regression statements miss their tables: CREATE TABLE ... WITHOUT OIDS, CREATE TABLE AS with USING heap or WITH (...) before AS, PREPARE TRANSACTION (refused as Postgres refuses it with max_prepared_transactions = 0), ON DELETE SET NULL (cols) and SET DEFAULT (cols), CREATE SCHEMA's elements in Postgres's order, DROP COLUMN of a column a trigger names (2BP01, or its triggers dropped with CASCADE), COMMENT ON CONSTRAINT ... ON DOMAIN, name as a key type. create and grant are reserved words, as in Postgres.

What stopped the Postgres regression suite most

  • UPDATE pg_class SET reltuples / relpages / relallvisible ..., which tests and tuning scripts run to steer Postgres's planner, failed, and in a transaction every statement after it did (join_hash lost 225 lines to one). There is no such input to plans here: the UPDATE changes nothing and says how many relations it names.
  • ARRAY[[1, 2], [3, 4]] and ARRAY[ARRAY[1, 2], ARRAY[3, 4]] make an array of two dimensions (they were a syntax error). Reading one by two subscripts, and array_dims of one, aren't there yet: arrays are of one dimension inside.
  • WITH v AS (...) MERGE INTO t USING v ...: the USING names a query of the WITH.
  • CREATE TABLE t OF type: a typed table, its columns the composite type's fields, with table constraints after them (a column's WITH OPTIONS isn't taken yet).
  • pg_tablespace_location(oid) (what psql's \db calls): '', as for the default tablespaces.

The last three extensions the migration probe refused

  • pg_stat_statements: CREATE EXTENSION succeeds, and the view has each statement by its text with constants taken out (select * from t where id = $1), how often it ran, its time in all, at least, at most, on average and its deviation, and its rows. Kept in memory by the process, up to 5,000 statements; pg_stat_statements_reset() empties it. Buffers, WAL, planning and JIT columns are 0.
  • intarray: icount, sort (asc/desc), sort_asc, sort_desc, uniq, idx, subarray, intset, and on integer arrays + and - of an element or an array, | and &.
  • tablefunc: crosstab(source) and crosstab(source, categories) with a column definition list, and normal_rand. (connectby isn't.)

Set operations, count(DISTINCT) and PARTITION BY in a nondeterministic collation, and of citext

  • UNION, INTERSECT and EXCEPT, count(DISTINCT x) and a window's PARTITION BY go by the key the strings are equal by. Still refused: another aggregate's DISTINCT (string_agg(DISTINCT x, ',')), IN of a subquery, and keys.

Read sets across restarts, a protected main, a five-minute trial

  • What a world that checks its reads read is kept across a clean restart (in the folder's readsets, sealed if the folder is), so its merge is checked as before instead of held. After a crash it's unknown, and the merge is held as before; a file left from an earlier clean close is never used after a crash.
  • ALTER SYSTEM SET protect_main = on: main's rows change only by merging a world, for everyone, the database's own user and admins included. An INSERT, UPDATE, DELETE, COPY, TRUNCATE, HTTP put or a transaction's COMMIT that changes rows of main is refused (42501). Definitions, the database's own rows (agents, policies, the outbox) and the ways back (RESTORE, UNDO) still change it directly. SHOW protect_main.
  • examples/trial: a five-minute walkthrough on a small CRM. An agent's stale read, a merge check, a merge policy with a queued email, and an approval bound to the version reviewed, each caught before it reaches main. trial.py checks its own outcomes and runs in CI.

Reviews, effects and long-running worlds (from outside review of the agent-safety design)

  • An approval names the version it approves: MERGE WORLD w AT VERSION n merges only if the world is still as the person read it, so an agent that keeps writing while it waits doesn't get its later writes merged with the approval (55000, nothing merged). Studio merges at the version it showed, HTTP diff and merge dry runs return version, MCP merge and the CLI's merge w at n take it, and a dry run's NOTICE says it. A merge that settles rows (USING, BY COLUMNS, RESOLVE) can also pin the world it merges into with TARGET n from the dry run, so the settling can't overwrite a change made there since. A partial merge moves the world's version now, so an approval of the whole world is refused after part of it was merged; HTTP merge returns the version of a branch that stays open, and the Python and TypeScript clients take it.
  • Merge policies count queued effects (NOTIFY ON MERGE): max_effects, effects_per_hour and review_channels. A merge that changed one row and queued a thousand emails went through under max_rows = 1. A merge that would take back or change an effect its parent already queued always waits for a person.
  • ACK OUTBOX needed only write_main, so an agent allowed to merge into main could delete the effects its merges queued before any worker did them. Working the outbox takes the new outbox right (or admin).
  • The outbox has delivery states: CLAIM OUTBOX [FOR channel, ...] [LIMIT n] [LEASE 'interval'] [IDEMPOTENT] gives a worker effects for a lease, never the same one to two workers. RETRY OUTBOX 'id' ATTEMPT n queues one again, only if that's still its current attempt (a late or repeated retry never takes a newer claim). A lease that runs out on a non-idempotent claim leaves the effect ambiguous (the worker may have called the receiver): it stays in SHOW OUTBOX and no claim returns it until someone checks and acknowledges or retries it. SHOW OUTBOX shows state, attempts, worker and lease_until.
  • The stale-read check (check_reads) recorded a scan of a whole table with a WHERE as reading the whole table, so on a busy table nearly every merge was held. Such a scan now counts as reading the rows its column op constant conditions can match: other rows may change.
  • REBASE WORLD w [USING OURS|THEIRS] [BY COLUMNS] [RESOLVE (...)] [AT VERSION n] (and MCP rebase): a long-running world takes in what changed in its parent since its fork, its own changes on top, and settles conflicts as they come. Refused, with nothing changed, on unsettled conflicts or reads that went stale. RESTORE WORLD to before a rebase is refused.
  • After a restart, replaying the log refused a merge of a world that checks its reads (its earlier reads aren't known then), so the merge was lost from the replayed state. Replay doesn't check reads now.

What the migration probe still refused

  • EXCLUDE constraints: exclude using gist (room with =, during with &&), with WHERE, in CREATE TABLE and ALTER TABLE ... ADD. A conflicting row is 23P01 naming the two keys, among one statement's rows too, when a merge brings two worlds' rows together, and for the rows already there when one is added. No index answers &&: a statement that writes such a table reads it whole.
  • An enum as a primary key, alone or among several columns. (A label of an enum some table is keyed by can't be renamed.)
  • sum, min and max of money. count(t.*) counts the rows where t has one, as after a LEFT JOIN.
  • UNIQUE (...) DEFERRABLE [INITIALLY IMMEDIATE] is taken: a unique constraint here is checked once a statement's rows are written, which is what that asks for. INITIALLY DEFERRED is refused.
  • COMMIT in a PL/pgSQL procedure. A CALL is one statement here and stores its work together when it ends, so a procedure that fails after its COMMIT keeps nothing, where Postgres keeps what it committed. In a transaction block it is 2D000, as in Postgres. ROLLBACK in one is still refused.
  • (f()).field of a function with several OUT parameters; textcat, by its name, as a user aggregate's step.
  • schema.table.column, as Prisma writes every column it names (it was a syntax error, so Prisma's first query failed).
  • The migration probe: 3 of 181 statements refused, from 15, all three an extension (tablefunc, intarray, pg_stat_statements). The Postgres regression suite: 77.37% of expected lines, from 77.20%, ten files better and none worse (tests/regress_floor.tsv raised).

Lint

  • cargo fmt over the whole tree, and clippy clean (with and without s3) with warnings as errors, which ci's lint job asks for and main didn't pass. Seven always-false conditions left in place were taken out (&& false in geom.rs, money.rs, rules.rs, userdefs.rs; a self-assignment in tsearch.rs): what they did is unchanged.

GROUP BY and DISTINCT in a nondeterministic collation, and of citext

  • GROUP BY, DISTINCT and DISTINCT ON of such text were refused. They go by the key the strings are equal by: 'Ada' and 'ADA' are one group, which shows one of them (either, as in Postgres). UNION, INTERSECT, EXCEPT, count(DISTINCT) and PARTITION BY are still refused.

Drivers and ORMs

  • tests/drivers and the drivers workflow: psycopg 3, SQLAlchemy 2, Django, node-postgres, pgx, JDBC and Prisma each make a table, write and read typed values, use a transaction and read a constraint error's code against chronos serve. Nightly, on request, and on pull requests that change them. All seven pass.

After a review of the changes below

  • COPY ... FROM ... WHERE counted a row as left out when its WHERE failed (WHERE 10 / v > 0 with a zero): the COPY fails now, and stores nothing.
  • COPY (INSERT ... RETURNING ...) TO with FORCE_QUOTE of a column it doesn't return ran the INSERT, then failed, and the rows stayed. The columns are checked before the statement runs.
  • A COPY whose rows first take a sequence's value past its first 16,384 rows (a trigger that calls nextval for some rows) was refused (54000). Its parts are kept from the start, up to CHRONOS_WORK_MEM, to be stored again.
  • A key-value put to a table whose primary key is a date, a time, a timestamp, a number or a boolean wrote a second row with the same key, and get and delete didn't find the row SQL stored: the key is taken as people read it (days/2026-01-01) and put in its stored form.
  • NaN, Infinity and -Infinity are numeric primary keys, in numeric's order.
  • A default worked out for each row is typed when it is made: i int DEFAULT now() is refused (42804) by CREATE TABLE, where every INSERT failed. ADD GENERATED ... AS IDENTITY is refused on a column that has one, and two parents' different ones are a conflict for a table that inherits both.
  • A row written as JSON without a column whose default only SQL can work out (it takes a sequence's value, or calls a function made here) says so.
  • CASE x WHEN ... and NULLIF(x, y) in a nondeterministic collation worked x out once for each comparison: nextval in it advanced more than once. Once now.
  • In PL/pgSQL, NEW IS NULL is true when every field is null and NEW IS NOT NULL when none is, as a row is in Postgres (they went by whether the record held a row).
  • hstore, ltree and lquery parameters and results in binary, as a driver with an adapter for them sends and asks for (they were read as text). An array of them keeps its lower bound and its dimensions. TG_RELID is an oid.
  • Not changed: a default's (like a check's and a generated column's) functions are found by the search_path of whoever writes the row; citext as a function's parameter or result type is text.

Release gates, fuzzing, and a floor under the regression suite

  • tests/fuzz.rs: statements nobody would write (tokens thrown together, and statements that run with tokens dropped, doubled, swapped or replaced) and wire frames no driver would send (wrong lengths, unknown types, messages cut short). Nothing may panic, and the server must answer the next real client. Seeded: CHRONOS_FUZZ_SEED, CHRONOS_FUZZ_ROUNDS.
  • A release is built only after a gate job passes on the tagged commit: the crash suite, kill -9 under concurrent merges, the simulated interleavings, and the fuzz tests at 100,000 rounds.
  • tests/regress_ratchet.sh and tests/regress_floor.tsv: no Postgres regression file may match fewer of its expected lines than it has reached. The regress workflow runs it every night.
  • operations.md says what Chronos has for availability (one writer, a restart, a takeover from cloud storage) and what it doesn't (replicas, failover).

hstore and ltree

  • hstore and ltree were types that don't exist, and CREATE EXTENSION hstore and ltree errors, so a table with an attributes or a tree-path column wasn't made, nor anything after it that reads the table. Both are built in now, checked against the extensions in Postgres 16 (tests/pgdiff.rs, hstore_type and ltree_type).
  • hstore: its text read and written as Postgres does; ->, ||, ?, ?|, ?&, @>, <@, -, h['key']; hstore(...), akeys, avals, skeys, svals, each, exist, defined, delete, slice, hstore_to_json and its kin, and a cast to jsonb. A gin or gist index on one is made and holds no entries, as on a jsonb.
  • ltree and lquery: @>, <@, ~, ?, ||, the comparisons, and nlevel, subpath, subltree, index, lca, text2ltree, ltree2text. Paths order label by label, as Postgres orders them. A gist index on one is a btree here.
  • They work where an expression runs: a default, a check, a generated column, an index's expression, RETURNING, a trigger's WHEN, a policy, SQL and PL/pgSQL functions, and prepared statements with hstore or ltree parameters.
  • What pg_dump writes for an extension's objects is read: the type and the functions with their schema (public.hstore, public.nlevel(...)), OPERATOR(public.->) for every operator (it was read for the comparisons only), and an operator class with its schema and its parameters, public.gist_ltree_ops (siglen='8').
  • ORDER BY column LIMIT n read an index in its order for a money, a time with time zone or a range column, whose index is in their text's order: the first rows could be the wrong ones. Those sort now.
  • GROUP BY an operator's result on a money, bit, time with time zone or range value (GROUP BY m * 2) said the column must appear in the GROUP BY clause. It groups.
  • PL/pgSQL trigger functions have TG_RELID.
  • Not yet: ltxtquery, hstore(record) and populate_record, (each(h)).key in a select list.
  • The migration probe: 15 of 181 statements refused, from 77. What the rest wait for: EXCLUDE constraints, an enum primary key, COMMIT in a procedure, a deferrable UNIQUE, sum(money), count(t.*), a field of a function's composite result, and the tablefunc, intarray and pg_stat_statements extensions.

citext

  • citext was a type that doesn't exist, and CREATE EXTENSION citext an error, so every schema with a case-insensitive email or username column stopped there. It is built in now: text in the collation citext, equal and sorted by its lower case. A citext column, cast and domain; =, <>, <, IN, CASE, joins, ORDER BY, min and max; unique constraints and ON CONFLICT; and LIKE, which doesn't look at case, as the extension's doesn't. Each matches Postgres with the extension (tests/pgdiff.rs, citext_compares_by_lower_case).
  • A citext compared with text of a type is compared as text, as Postgres does. format_type and information_schema.columns say citext.
  • It is a nondeterministic collation's text, so what those refuse it refuses: GROUP BY, DISTINCT, set operations, and a primary or a foreign key on a citext column. Not yet as the extension has them: the regular-expression operators and replace, strpos, split_part and translate without regard to case, and pg_typeof (citext).
  • A column made from a domain takes the domain's collation.
  • The migration probe (tests/fixtures/pg-gap-probe-migrate.sql): 77 of 181 statements refused, from 86. hstore and ltree are the types the rest wait for.

Nondeterministic collations

  • CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false) was refused: every collation here was deterministic, text equal only when its bytes are. It is taken now, and the strings ICU ties are equal in it: =, <>, < and the other comparisons, BETWEEN, IN (a, b), CASE x WHEN, NULLIF, IS DISTINCT FROM, a join's ON, ORDER BY, min and max, and unique constraints, unique indexes and ON CONFLICT on a column of one. Each matches Postgres 16 (tests/pgdiff.rs, nondeterministic_collations).
  • What would still tell such strings apart by their bytes is refused (0A000) rather than done wrong: GROUP BY, DISTINCT, UNION, INTERSECT, EXCEPT, count(DISTINCT), PARTITION BY, a subquery's rows (IN (SELECT ...)), arrays and rows of such text, and a primary, foreign or partition key of one. LIKE is refused as Postgres 16 refuses it. See Nondeterministic collations.
  • pg_collation.collisdeterministic says which a collation is, so a dump keeps it.

Audit triggers, and the indexes a dump has

  • A record used whole in PL/pgSQL was refused when the function was made: to_jsonb(NEW), row_to_json(OLD), OLD IS NULL, NEW IS DISTINCT FROM OLD, a loop's record. It reads as the record's fields in a JSON object now (null while it holds no row), so the usual audit trigger, which keeps to_jsonb(OLD) and to_jsonb(NEW), works and keeps what Postgres keeps.
  • CREATE INDEX ... USING gin on a jsonb or an array column was refused, and with it every dump that has one. It is made, listed (pg_indexes, \d) and dumped; it holds no entries, so @>, ? and && read the rows as they did. jsonb_ops, jsonb_path_ops and array_ops are accepted.
  • CREATE EXTENSION btree_gin and btree_gist succeed: their operator classes are for indexes that are btrees here whatever their method.

Primary keys of dates, times and numbers

  • A one-column primary key had to be an integer, text, bytea, uuid or an address: day date PRIMARY KEY, at timestamptz PRIMARY KEY, id numeric(20,0) PRIMARY KEY and a boolean, time, timestamp or double precision key were refused (0A000), and the table with them. They work now, and numeric, double precision and time can be columns of a key of several. Rows are stored in the key's order, found by it, upserted with ON CONFLICT and referenced by foreign keys as in Postgres (tests/pgdiff.rs, primary_keys_of_dates_times_and_numbers). A numeric key of NaN or an infinity is 22003. Still refused: an enum, interval, array or JSON key.
  • tests/fixtures/pg-gap-probe-migrate.sql: 181 statements a dump or a migration of an ordinary application sends (extensions, types, tables, indexes, functions, triggers, views, row security, grants, queries), for tests/pg_gap_probe.sh. Postgres 16 takes them all; what Chronos refuses is the list to work down.

A COPY that took sequence values stored nothing

  • COPY ... FROM into a table whose rows take a sequence's value as they are stored (a trigger calling nextval, a function it calls) said COPY n and stored no row. A statement whose reserved sequence values run out is run again from its start, and the COPY's rows, which the client sends once, were gone by then. They are kept now and handed out again: the first part always, and every part once the COPY has been run again, up to CHRONOS_WORK_MEM; a COPY past that which runs out again fails (54000) with nothing stored, rather than storing some of its rows. Upgrade if you COPY into tables with such triggers.
  • A statement run again for more sequence values gives back the ones its first run took, so a sequence skips none for it: three rows whose default is nextval('s') * 10 get 10, 20 and 30, as in Postgres, where they got 30, 40 and 50.

Column defaults worked out for each row

  • A DEFAULT that Postgres works out afresh for each row was refused (0A000): now() + interval '7 days', timezone('utc', now()), random(), nextval('s') + 1, gen_random_bytes(16), a function of your own. It is kept as its SQL now and run for each row that takes it: in CREATE TABLE, ALTER COLUMN ... SET DEFAULT, LIKE ... INCLUDING DEFAULTS, inherited columns, INSERT, COPY, and UPDATE ... SET col = DEFAULT. So a dump with such defaults restores, and chronos import postgres:// brings them over instead of leaving them out.
  • DEFAULT upper(current_user) and other defaults that name the role were worked out once, as the role making the table; they are the role writing the row now.
  • A column a default names is cannot use column reference in DEFAULT expression, as in Postgres.
  • Still different: ADD COLUMN with a default that gives each row its own value (random(), a sequence, a function) is refused on a table with rows, and the catalog shows such a default as written (Known gaps).

COPY of a statement that changes rows

  • COPY (INSERT | UPDATE | DELETE ... RETURNING ...) TO STDOUT sends the rows the statement returns, in any of COPY's formats. It was refused ("COPY (query) TO takes a SELECT").
  • Without a RETURNING it is 0A000 (COPY query must have a RETURNING clause), and what the table's rules would make of it is refused in Postgres's words: DO INSTEAD NOTHING, DO ALSO, conditional and multi-statement DO INSTEAD rules, a rule that runs a utility command, and a RETURNING under an INSTEAD rule that has none.
  • What a COPY's statement raised was dropped: its triggers' RAISE NOTICEs now reach the client, the BEFORE triggers' before the rows and the AFTER triggers' after them.
  • tests/regress_triage.py ranks what breaks a run of Postgres's regression files by statements lost, charging the ones that fail after a refused CREATE (or in a transaction it aborted) to that statement.

ALTER DEFAULT PRIVILEGES, type privileges, and the predefined roles' privileges

  • ALTER DEFAULT PRIVILEGES [FOR ROLE | USER r, ...] [IN SCHEMA s, ...] GRANT | REVOKE [GRANT OPTION FOR] ... ON TABLES | SEQUENCES | FUNCTIONS | ROUTINES | TYPES | SCHEMAS TO | FROM ... [WITH GRANT OPTION] [CASCADE | RESTRICT] was a syntax error (42601); it works as in Postgres 16, for the roles whose privileges the running role has (42501 permission denied to change default privileges otherwise), with Postgres's refusals (IN SCHEMA with ON SCHEMAS, a privilege the kind hasn't, columns, a grant option for PUBLIC: 0LP01). A role's own entry replaces the built-in defaults and a schema's adds to it; one back at its start is gone. pg_default_acl shows them, psql's \ddp lists them, and pg_dump writes them, which restore.
  • What a role makes starts with them: tables, views, materialized views, CREATE TABLE AS and SELECT INTO, partitions, sequences, serial and identity columns' sequences (one ALTER TABLE adds: its table owner's), functions, procedures, types, domains and schemas (CREATE SCHEMA ... AUTHORIZATION's owner's), so relacl, proacl, typacl, nspacl and the has_*_privilege functions show them. They are rows of the world: they fork, merge and come back with RESTORE. DROP ROLE of a role that has them or is named in them is 2BP01 with Postgres's detail, DROP OWNED drops a role's and takes it out of others', REASSIGN OWNED leaves them and DROP SCHEMA drops the schema's.
  • GRANT and REVOKE ... ON TYPE | DOMAIN were refused (0A000); they work now, and USAGE on a type made with CREATE TYPE or CREATE DOMAIN is checked where Postgres checks it: a column's type in CREATE TABLE (with those LIKE, PARTITION OF and INHERITS copy) and ALTER TABLE ... ADD COLUMN | ALTER COLUMN TYPE, the columns of views, materialized views and CREATE TABLE AS, a domain's base type, a function's argument and result types (42501 permission denied for type s.mood). pg_type.typowner was always the bootstrap superuser for enums and domains: it is their owner now, and typacl their ACL; has_type_privilege answers from them (it said true).
  • pg_read_all_data and pg_write_all_data gave nothing; they give what Postgres 16's do: SELECT (the first), and INSERT, UPDATE and DELETE (the second), on every table, view and sequence, and USAGE on every schema.
  • aclexplode(acl) and makeaclitem(grantee, grantor, privileges, grantable) work, and a ::regrole of a name that needs double quotes is written with them, as in Postgres.
  • A table's new owner (ALTER TABLE ... OWNER TO, REASSIGN OWNED) is now its serial columns' sequences' grantor in their grants too, and a serial column's sequence keeps its grants when the table or the column is renamed or the table moves schema (they were dropped).
  • ALTER TYPE ... OWNER TO was a notice that changed nothing and ALTER DOMAIN ... OWNER TO a syntax error (42601): both hand the type over with its grants now, so a dump's type owners restore.
  • A GRANT or REVOKE that gives or takes nothing (its warning) writes the object's ACL out, as Postgres does; 's._mood'::regtype and has_type_privilege take an array type by its _name.

Row-level security: policies parsed once, each statement's rewrite kept, what a policy reads bound by identity

  • A policy's condition was parsed, and a statement reading its table rewritten and planned, on every run: a point read of a table with a tenant policy cost about 1.5 times a plain one (server CPU, release build). A policy is parsed once now, 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 (role and memberships, row_security, search_path) and everything it read (policies, row security, owners, views, roles) are the same: a prepared statement plans once and sees the next CREATE/ALTER/DROP POLICY, ENABLE/DISABLE/FORCE or role change at its next run, as Postgres plans again (row_security_prepared_statements_see_policy_changes, tests/rls.rs a_prepared_statement_is_rewritten_once). Release build, point reads by primary key with pgbench -M prepared, the server's CPU time per read: a plain table 32 µs, one with using (tenant = current_user) 48 µs before (1.5 times), 33 µs now (1.06 times); in-process (Session::execute, best of 25 rounds of 2,000), 12.7 against 25.7 µs before and 12.4 against 13.6 µs now.
  • A temporary table named like a table a policy reads made such reads fail (0A000); the policy reads its own table now, as Postgres does. Dropping a table or view another table's policy reads is 2BP01 with Postgres's detail (policy p on table t depends on table x), and CASCADE drops the policy, where it was dropped and the policy failed (42P01). Renaming a column a policy reads in a subquery, or that another table's policy reads, renames it in the policy, where it was refused (0A000); renaming a table or view a policy reads (or the policy's own table, where its condition names it) renames it in the condition (row_security_reads_the_tables_it_bound, tests/rls.rs). A policy reading a view could not be made (42P01). DROP SCHEMA lists what depends on it as Postgres does: each object in it followed by the policies of other tables that read it, in the 2BP01's detail and, with CASCADE, in one notice (drop cascades to 2 other objects), where it named one object in its message and no detail (dropping_a_schema_a_policy_reads_lists_what_depends_on_it).
  • A table with policies joined to others was read whole as a subquery of the rows it may see; it stays a table read through its indexes now where its policies can go first in the WHERE (or its LEFT JOIN's ON) and the query's conditions on it can't show its values (comparisons of columns, parameters and constants that cast none of its values), keeping FOR UPDATE OF, tableoid and the policies' place before conditions that could (row_security_joins_keep_their_policies_first, tests/rls.rs joined_tables_with_policies_keep_their_index_reads).

regclass, regtype and the other reg types are oids of their own

  • regclass was text (its relation's name) and regtype, regnamespace, regrole, regoper, regoperator, regconfig, regdictionary and regcollation bigints: each is a type of its own now, as regproc is (2205, 2206, 4089, 4096, 2203, 2204, 3734, 3769, 4191; in binary its object's oid). A value is the oid and knows its type, so it compares, orders and joins by oid with oids, integers and its own type (c.oid = 't'::regclass, order by c.oid::regclass, min/max give the oid), and is shown as its object's name, schema-qualified and quoted as Postgres shows it along the search_path, wherever a result leaves the statement or it is made text. Names are read as Postgres's input functions read them (quoted, schema-qualified, along the search_path, int/_int4/integer[] for a type), or an oid in digits; a name that names nothing is Postgres's error for its kind (42P01, 42704, 3F000, 42883), and to_regclass, to_regtype, to_regnamespace, to_regrole, to_regcollation, to_regoper, to_regoperator, to_regproc and to_regprocedure give NULL there. A reg value casts to and from oid, int4, int8 and text and to no other reg type but its pair (42846), and has no arithmetic or function of text (42883).
  • regclass[], regtype[] and the other reg arrays are arrays of oids, as regproc[] is, and a column can be of any reg type or array of one, keeping the oid. The HTTP and MCP row API (and the CLI's get, put, find) write a reg column by name or oid and read it as its name, as SQL shows it. regoper and regoperator know Postgres 16's built-in operators (src/sql/builtin_opers.tsv), regconfig and regdictionary its built-in text search configurations and dictionaries.
  • pg_typeof gives a regtype (pg_typeof(x) = 'int4'::regtype and format_type(pg_typeof(x), null) work), pg_sequences.data_type is a regtype, pg_prepared_statements.parameter_types and result_types are regtype[] of the types' oids (where they held names, so 'integer'::regtype = any (parameter_types) and parameter_types::oid[] were wrong), pg_partition_tree, pg_partition_ancestors and pg_partition_root give regclasses, and pg_type has rows for every reg type. nextval('s'::regclass), pg_relation_size('t'::regclass), to_tsvector('english'::regconfig, ...) and the other functions of a regclass or regconfig take one. An oid beside text in UNION or VALUES is 42804, as in Postgres, where it made text.
  • Breaking: a typed driver gets a regclass as type 2205 (four bytes in binary), not text, and the other reg types as their own types, not bigints (int8 getters fail on them: read them as an oid, or cast to ::text or ::oid); pg_typeof(...) and pg_sequences.data_type are regtypes (2206), and pg_partition_tree's and pg_partition_ancestors' relid regclasses, where they were text. A regclass column of a database made before this stays the text column it was kept as; a regtype[] kept as type names reads them as regtype does. The oids of relations, indexes, constraints, schemas, enum types and domains, collations and row types made here are worked out from their kind and name (FNV-1a, in a range per kind below 2^30), where they were 16384 plus a place in name order that moved whenever one was made or dropped: pg_class.oid, tableoid and the rest have new values, the same in every world, so a kept reg value names its object across other objects' DDL, forks, merges and a dump's restore. A rename, or a drop and a make again under the name, is as if the name were kept (Postgres's oid stays with its object).

Agents log in with SCRAM, and logins don't tell which names exist

  • An agent proves with SCRAM-SHA-256 that it knows its token (SCRAM-SHA-256-PLUS over TLS, so channel_binding=require works), where it sent the token in the clear: CREATE AGENT (and HTTP create_agent) keeps a SCRAM verifier of the token beside its hash. An agent made before this logs in once with its token in the clear, which keeps a verifier for it; from then on it uses SCRAM (tests/scram.rs agents_log_in_with_scram, agents_bind_to_the_certificate, agents_from_before_verifiers_get_one_at_their_first_login). Old databases open as they were: an agent's row without a verifier reads as one made before.
  • Breaking: the server's or admin token no longer logs in under an agent's name (it did in the clear), whether the agent logs in with SCRAM or, made before this, in the clear; an agent's name takes only its token, as a role's takes only its password. A disabled agent gets the same SCRAM exchange as a wrong token, then 28P01.
  • With --token, every name gets SCRAM with Postgres's iteration count and a salt of its own, the same each time: a role's or agent's verifier's, or one made from the name and a server secret as Postgres's scram_mock_salt makes it, for the database's own user, names nobody has, and roles without a password or past VALID UNTIL (a disabled agent keeps its verifier's). A name's token verifiers are made only once its client has sent its proof, a few at a time, and every SCRAM login waits at that step as long as making them takes, so the time a login takes doesn't tell which names exist or logged in lately either. Before, every name but a role with a password shared one salt and agents were asked for a cleartext password, so a client could tell which names were roles and agents (names_are_not_told_apart).
  • scram_iterations sets the iteration count of the verifiers new passwords and new agents' tokens get, where it was always 4096 (tests/pgdiff.rs scram_iterations_counts_a_new_verifiers_iterations). Hashing stops for statement_timeout, a cancel request and pg_terminate_backend, as Postgres's does, so a count of two billion can't hold a connection for minutes (hashing_with_many_iterations_stops_when_asked).
  • Passwords outside ASCII are SASLprep'd (RFC 4013) before hashing, as Postgres does it (NFKC, and a password SASLprep prohibits hashed as typed), so a role given one logs in with libpq and tokio-postgres, which prepare it the same way; the verifiers match those Postgres makes (passwords_outside_ascii_are_prepared_as_postgres_prepares_them).
  • chronos import binds its SCRAM login to the server's certificate over TLS (SCRAM-SHA-256-PLUS), as libpq's channel_binding=prefer does, and SASLprep's the password. A server that offers binding with a certificate whose hash can't be made (EdDSA, or a server in the middle) is refused, as libpq refuses it; channel_binding=require and disable in the URL work as in libpq.

oid and smallint are types of their own

  • oid was a bigint: it is an unsigned 32-bit integer now (type 26, four bytes in binary), as in Postgres. It reads text as oidin does ('-1' is 4294967295, 0x10 hex), wraps an int4 and refuses a negative int8 or anything past 32 bits (22003), compares unsigned, has no arithmetic, sum or avg (42883), casts to int4 by its bits and to int8, text and the reg types only (42846), and is a string in json. Columns of type oid, min/max, tableoid, regclass::oid, the catalog's oid columns and functions' oid parameters (which take a regproc, as Postgres converts one) are oids.
  • smallint reached clients as int4: it is int2 now (type 21, two bytes in binary, parameters too). Arithmetic of two smallints is a smallint checked to 16 bits (22003), << wraps around to 16 bits, of a smallint and an integer an integer (mod too); sum is a bigint, avg a numeric, min/max/abs/~ smallints; CASE, COALESCE, GREATEST, LEAST, ARRAY[...], UNION and lag's default widen as Postgres does (ARRAY[1::int2, 70000] is an integer[]); CREATE TABLE AS and views keep smallint columns; and a function of a smallint parameter is the exact match among overloads (f(1::smallint) between f(int) and f(bigint) is ambiguous, 42725, as in Postgres). An index on a smallint expression past 16 bits refuses the row with 22003, where its entry was left out.
  • An integer beside an oid is one wherever two values meet (comparisons, IN and ANY of lists, arrays and subqueries, EXISTS, joins, IS DISTINCT FROM, NULLIF, CASE, COALESCE, GREATEST, LEAST, ARRAY[...], UNION, VALUES), and a negative bigint is 22003 OID out of range there and when written to an oid column. oid[] and int2[] read their elements as their type ('{-1,0x10}'::oid[] is {4294967295,16}). oid is the preferred type of its category among overloads, and functions of text or other numbers take none (42883).
  • Breaking: a typed driver reads oid columns as u32 and smallint ones as i16 (int4/int8 getters fail on them). An oid column of a database made before this stays the bigint it was made as, and a smallserial column or a CREATE TABLE AS column of a smallint expression made before stays an integer. A call f(1) of a function whose parameter is a smallint is 42883 now, as in Postgres (cast the argument: f(1::smallint)), in views and function bodies made before too.

Logging in with SCRAM-SHA-256 and MD5

  • A role made with CREATE ROLE ... PASSWORD logs in with its password, which nothing checked before: SCRAM-SHA-256 against its verifier (AuthenticationSASL, as Postgres 16 runs the exchange: its nonces, salt and iteration count, its errors, 28P01 for a wrong password and 08P01 for a malformed message), or MD5 when it is kept as an md5 hash (SET password_encryption = 'md5'). That holds on loopback without a token too; a role without a password where the server has a token is refused with 28P01, as Postgres refuses it. A verifier made by the client (psql's \password, or one copied from Postgres's pg_authid) logs in as well (tests/scram.rs).
  • Over TLS, SCRAM-SHA-256-PLUS is offered too, bound to the server's certificate (tls-server-end-point), so libpq's and tokio-postgres's channel_binding=require work; a client that saw another certificate is refused (28000).
  • With --token, a name that is no role proves with SCRAM that it knows the server's or the admin token instead of sending it in the clear. Agents still send their token in the clear (it is kept only hashed), and a server with only --admin-token still takes any cleartext password. Login refusals are FATAL, as in Postgres.
  • chronos import logs in to a server that asks for MD5.
  • Agents' and the guest's sessions can't read pg_authid.rolpassword or pg_shadow.passwd (42501), which let an agent answer a superuser role's MD5 challenge with its hash. A role past its VALID UNTIL goes through a doomed SCRAM exchange, so the server never confirms its password. Without --token, loopback stays trust for every name but that of a role with a password, and such a login is a superuser that can become any role: role passwords protect something only with --token.

The catalog: built-in functions in pg_proc, regproc[] and regprocedure[], row types and system columns

  • pg_proc lists Postgres 16's 3,286 built-in functions (pronamespace 11) with Postgres's oids, kinds, argument and result types, flags, prosrc and proacl, where it listed only the functions made here: a catalog's regproc column (typinput, amhandler) joins to pg_proc.oid, psql's \df pg_catalog.*, \df+ and \sf now 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_init_privs has the privileges initdb gives the few that aren't everyone's, so pg_dump leaves those alone. The rows are made only when a query reads pg_proc (src/sql/builtin_procs.tsv, extended from a real Postgres 16).
  • regproc[] and regprocedure[] were refused as types (0A000), and ARRAY[...] and array_agg of regprocs were text[] of their names: they are array types now (1008 and 2207), as in Postgres. A column can be one; it reads names and oids (a literal beside one, ps = '{now,int4in}', too), shows names wherever it is made text, casts to and from oid[] and text[], gives regprocs back (a[1], unnest, = ANY), and a driver gets an array of oids in binary.
  • A lookup of a built-in function in pg_proc by its oid or name (where oid = 1299, a correlated p.oid = t.f, pg_dump's per-function queries) reads that row alone, and the built-in rows are made once; pg_function_is_visible follows the search_path (it was true for every function); a table and an enum or domain of one name are refused (42710), as in Postgres.
  • pg_type has each table's, view's and materialized view's row type and its array type, which pg_class.reltype named though no row had it; pg_attribute lists the system columns, a sequence's columns and an index's (expressions as expr, expr1, ...); a sequence's relpages/reltuples are 1, its and a view's relreplident is n, and a view's and a partitioned table's relfilenode is 0, as in Postgres. The catalog's array columns (reloptions, proargnames, proallargtypes, proargmodes, ...) are arrays, where they were text.

Row-level security

  • A table's policies can't be another's: the rows that keep them escape / in the table's name, and ALTER TABLE ... SET SCHEMA into a schema whose name has / is refused (42601), as CREATE TABLE refuses such a name. Before, a table moved into schema "a/b" had its policies read as table a's (a permissive one let a's rows through) and its rows read as a's (tests/rls.rs policies_stay_with_their_table_whatever_the_names). A policy without USING (or WITH CHECK, for INSERT) lets nothing through, as in Postgres (row_security_clauseless_policies_let_nothing_through).
  • ALTER TABLE ... ENABLE | DISABLE | FORCE | NO FORCE ROW LEVEL SECURITY and CREATE, ALTER and DROP POLICY were refused (0A000, 42601); they work now as in Postgres 16, with AS PERMISSIVE | RESTRICTIVE, FOR ALL | SELECT | INSERT | UPDATE | DELETE, TO roles, PUBLIC, CURRENT_USER and SESSION_USER, USING and WITH CHECK, RENAME TO, the table's owner alone making and changing them (42501), and Postgres's errors (42710, 42704, 42601, 42809, 42804). A role a policy names can't be dropped (2BP01) and DROP OWNED takes it out of the policies; a renamed table keeps its policies and a dropped one drops them (tests/pgdiff.rs row_security_policies_are_created_altered_and_dropped, tests/rls.rs).
  • Policies apply to every role but superusers, BYPASSRLS roles and the table's owner (unless FORCE), wherever rows are read or written: SELECT through joins, subqueries, WITH, views (as their owner) and functions, UPDATE and DELETE (rows the policies hide are passed by), INSERT and UPDATE (a new row they refuse is 42501 new row violates row-level security policy for table "t", a restrictive policy's with its name), RETURNING, INSERT ... ON CONFLICT and MERGE with Postgres 16's checks and messages, SELECT ... FOR UPDATE, COPY ... TO; COPY ... FROM into such a table is 0A000. Permissive policies are ORed and restrictive ones ANDed, with none at all seeing nothing. Policies reading other tables read them with the reading role's privileges and policies (a loop is 42P17), current_setting('app.tenant_id') and current_user give the session's, and SET row_security = off turns a statement they would limit into 42501, as pg_dump wants (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; two LOGIN tenants over real connections in tests/rls.rs).
  • Policies and each table's row security are rows of its world: they fork, merge (two worlds' new policies on one table merge without a conflict), show in DIFF and DIFF ... AS SQL, and RESTORE puts them back. pg_class.relrowsecurity and relforcerowsecurity, pg_tables.rowsecurity, pg_policy and pg_policies show them, psql's \d and \dp list them, and pg_dump writes them, which restore. A database that never enables row security reads and writes as before, with no extra work per row or statement.
  • PL/pgSQL's EXECUTE runs MERGE too.
  • Row-level security reaches every read: WITH RECURSIVE, a FROM function's arguments, the left side of IN, LIMIT/OFFSET/DISTINCT ON, PL/pgSQL's expressions, UPDATE ... FROM and DELETE ... USING (which took the SELECT policies always and the command's never), statements run again in one statement by another role's function, and reads AS OF a table renamed or dropped since; CALL's arguments can't have subqueries (0A000), as in Postgres. A policy's names are bound when it is made (not the reader's search_path or temporary tables); renaming a column renames it in policies, and dropping one a policy reads is 2BP01 (CASCADE drops the policy); hundreds of policies on a table no longer overflow the stack; a policy reading its own table is no false 42P17; FOR UPDATE OF and tableoid work through a joined table's policies; REFRESH MATERIALIZED VIEW reads as the owner; ADD COLUMN ... DEFAULT current_user works on a table with rows. The HTTP and MCP row API refuses an agent the rows of a table whose policies limit its role (403): it uses SQL (row_security_reaches_every_read, row_security_policies_bind_their_names_when_made, row_security_follows_columns_and_defaults, tests/rls.rs).
  • A column's DEFAULT current_user (or current_role, session_user) is the role writing each row, as in Postgres, where it was the name of whoever made the table, kept as a constant: the owner column multi-tenant policies compare with current_user (row_security_views_subqueries_and_settings).

Roles, membership, GRANT/REVOKE and privilege checks

  • CREATE ROLE | USER | GROUP, ALTER ROLE (attributes, RENAME TO, SET/RESET) and DROP ROLE were 42601 (and SET ROLE and SET SESSION AUTHORIZATION did nothing): they work now as in Postgres 16, with its attributes (SUPERUSER, LOGIN, INHERIT, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT, VALID UNTIL), CREATEROLE's rules, reserved names (42939), and DROP ROLE refused with 2BP01 while the role owns or holds privileges on anything. Passwords are kept as SCRAM-SHA-256 verifiers in Postgres's format (or as given when already hashed), never in the clear. Roles are the database's, like agents: every world sees them (tests/pgdiff.rs roles_are_created_altered_and_dropped, createrole_needs_admin_option_and_attributes_it_has).
  • GRANT role TO role and REVOKE with Postgres 16's ADMIN, INHERIT and SET options and grantors, circular grants refused (0LP01); SET [LOCAL] ROLE and SET SESSION AUTHORIZATION change current_user and session_user, are undone by ROLLBACK and refused (42501) for a role the session user may not set; pg_has_role answers from them (role_membership_and_set_role).
  • GRANT/REVOKE of privileges on tables, columns, views, sequences, schemas, functions, procedures and the database (and ALL ... IN SCHEMA), with grant options, GRANTED BY and CASCADE; owners (ALTER ... OWNER TO, REASSIGN OWNED, DROP OWNED); relacl, attacl, nspacl, proacl and datacl written as Postgres writes them, acldefault, pg_auth_members, and has_*_privilege answering from them where every answer was true. Owners and grants are rows of each world, so they fork, merge and travel in time with their objects; pg_dump writes them and they restore.
  • The checks: SELECT (by column, through subqueries and joins), INSERT, UPDATE, DELETE (and SELECT on what their WHERE and RETURNING read), TRUNCATE, COPY, nextval/currval/setval (and a serial column's default), EXECUTE, USAGE and CREATE on schemas, ownership for DROP and ALTER, CONNECT and CONNECTION LIMIT at login, each 42501 with Postgres's message. A view reads its tables with its owner's privileges, a SECURITY DEFINER function runs as its owner (ALTER FUNCTION ... SECURITY DEFINER too) (table_privileges_are_granted_checked_and_revoked, column_and_view_privileges, sequence_privileges, function_and_schema_privileges, owners_drop_owned_and_drop_role_dependencies, tests/roles.rs).
  • Nothing changes for a database that makes no role: a login under a name that is no role made with CREATE ROLE is a superuser, as every login was, and what it makes is owned by chronos. A role logging in gets its own privileges (NOLOGIN is 28000, an expired VALID UNTIL 28P01). Agents keep their capabilities, and a role of an agent's name adds its privileges on top. ALTER ... OWNER TO names a role now (postgres and the login's own name mean chronos), where any name was accepted with a notice. CREATE ROLE and RENAME TO refuse (42710) the bootstrap superuser's names (chronos, postgres) and the login name of the session or of any open connection that logged in as no role, which a role of that name would take over.
  • pg_cancel_backend and pg_terminate_backend follow Postgres 16's rules for a role that isn't a superuser (a superuser's connection is 42501; another role's needs its privileges or pg_signal_backend's). MERGE checks SELECT on the target's columns its ON, WHEN conditions and SET read; CREATE TABLE AS checks its query before the schema; has_*_privilege gives null for an oid that names no object, and true for the catalog's tables (but pg_authid and pg_shadow); psql's \dn+ shows schemas' owners, privileges and comments, and \dt each table's owner. A column's grant option is given on as Postgres gives it (the grantor chosen from the column's and the table's grants), DROP OWNED takes what the role granted on from its grant options, and ALTER ROLE ... SET settings apply when the role logs in. CREATE MATERIALIZED VIEW checks its query (and REFRESH reads it as the view's owner), CREATE TRIGGER needs EXECUTE on its function, and USAGE revoked on public is enforced.
  • Follow-up: CREATE EXTENSION pgcrypto needs CREATE on the database and its maker owns it (DROP and ALTER EXTENSION need that owner, with Postgres's messages, where they needed the database's owner); DROP ROLE lists, and refuses over, grants on the database (it dropped the role, leaving the grant); type messages name the type's schema; ALTER FUNCTION ... SET SCHEMA, which did nothing with a notice, is 0A000; CONNECTION LIMIT counts a role's connections from before a rename; REASSIGN OWNED gives a table's column grants to the new owner; and revoking one of a member's two ADMIN OPTION grants keeps the grants it made (tests/roles.rs types_extensions_and_set_schema_say_what_postgres_says, connection_limit_counts_a_renamed_roles_sessions; tests/pgdiff.rs reassign_owned_moves_column_grants, revoking_one_of_two_admin_grants_keeps_what_they_granted).

ICU collations: inherited columns, stored COLLATE names, LIKE (follow-up)

  • A child table's column took its parent's collation whatever it was written with: CREATE TABLE child (s text COLLATE "sv-x-icu") INHERITS (parent_de) kept German, and two parents with different ones merged. Each is 42P21 now, as in Postgres (column "s" has a collation conflict, inherited column ..., with its "de-x-icu" versus "sv-x-icu" detail; a column written without COLLATE has the default), and so are ALTER TABLE ... INHERIT, ATTACH PARTITION and a parent's ADD COLUMN (child table "c" has different collation for column "s"). A COLLATE in a PARTITION OF column list, a syntax error before, is taken and the parent's collation kept, as Postgres does (inherited_columns_collation_conflicts in tests/pgdiff.rs).
  • A CHECK, generated column, index expression or WHERE, view or materialized view naming a collation without its schema read that name along the search_path of whoever used it: under another search_path it compared in another schema's collation of that name, and DROP COLLATION missed it (or found it where it didn't depend). The name is kept with the schema the search_path found it in when the object was made (collate a.c), as Postgres keeps the collation's oid (stored_collation_names_keep_the_schema_they_were_made_with).
  • x LIKE y with two different explicit collations ran; it is 42P21, as ILIKE was. a LIKE b where the pattern is a column (any text, collated or not) panicked when the table was read for a range; it is compared row by row now (like_collation_conflicts).
  • DROP COLLATION ... CASCADE that reaches a primary key's column was 2BP01 saying the column can't be dropped; it is 0A000 saying why (Chronos can't drop a primary key column), and drops nothing.

ICU collations: CREATE COLLATION, COLLATE, and text in a language's order

  • COLLATE other than C, POSIX or default was 0A000, and CREATE, ALTER and DROP COLLATION were syntax errors: text could only sort by bytes. Postgres 16's ICU collations are there now (unicode, und-x-icu, de-x-icu, sv-x-icu, ... 869 of the 908 a Postgres 16 with ICU4C 78 has), CREATE COLLATION name (provider = icu, locale = 'de-u-co-phonebk') makes more (and FROM other, IF NOT EXISTS; libc's C and POSIX), and ALTER COLLATION ... RENAME TO, SET SCHEMA, OWNER TO and REFRESH VERSION and DROP COLLATION [IF EXISTS] ... [CASCADE] (2BP01 while a column, index or view uses one) work as in Postgres. Sorting is ICU4X's (icu_collator and the ICU crates under it, pure Rust, pinned to exact versions), which orders every collation kept as ICU4C does on the strings compared (icu_collations_order_text in tests/pgdiff.rs), but for ideographs outside the Basic Multilingual Plane.
  • A column takes COLLATE in CREATE TABLE, ADD COLUMN and ALTER COLUMN ... TYPE ... COLLATE (a new type without one takes the default), and comparisons, ORDER BY (a query's, a set operation's, a window's, an aggregate's), min, max, greatest, least, x < ANY (a, b), upper and lower (ICU's case mapping: ß is SS, Turkish's dotted i) follow it. Collations meet as Postgres derives them: an explicit COLLATE beats a column's, a column's beats the default, two explicit ones are 42P21, and two columns' are 42P21 where an order is needed and 42P22 where a comparison runs. Subqueries (an outer column read in one too, and LATERAL), views, materialized views, CTEs and CREATE TABLE AS keep their columns' collations. Equality, GROUP BY and unique keys stay bytes, as every collation is deterministic, and ICU's ties are broken by bytes, as Postgres breaks them.
  • An index keeps its column's collation or the one CREATE INDEX ... (col COLLATE c) names, in that order: ORDER BY ... LIMIT and ranges read it only in that collation, and an index of another isn't used for them; equality reads any.
  • A collation is a row of its world (_sonos_collations/<name>): it forks and merges with the tables that use it, DIFF shows it (create collation, and AS SQL writes it and columns' collate), and a column keeps a copy of its collation's name and locale. The catalog has it: pg_collation (every Postgres 16 column; collversion is icu4x 2.3.1), pg_attribute.attcollation, pg_index.indcollation, information_schema.columns.collation_name, regcollation, pg_collation_actual_version(), psql's \d Collation column, pg_get_indexdef, and pg_dump, whose script restores into Postgres and into Chronos.
  • Refused (0A000) rather than ordered differently: nondeterministic collations, ICU rules, libc locales other than C, POSIX and C.UTF-8 ("en_US"), the 39 ICU collations whose languages ICU4X has no rules for or other ones (se-x-icu, mr-x-icu, ...), locales and collation types not checked to order as ICU4C does (sr-ME, co-search), the locale keywords kr and kb, initcap(), text in an ICU collation ordered against a subquery's rows or ANY (array), arrays and rows of it ordered, range partitioning in one, and an ICU collation on a PL/pgSQL variable (Known gaps). A keyword's value ICU doesn't know is XX000, as in Postgres.
  • DROP COLLATION finds a CHECK, a generated column, an index's expression or WHERE, a view and a materialized view naming the collation by the SQL's tokens (2BP01; CASCADE drops them), and a merge that would drop one a table uses is refused. Names earlier versions read as text by bytes (collate c, posix, "C.utf8") still read.

pgcrypto

  • pgcrypto's functions are here, each giving what Postgres 16's pgcrypto 1.3 gives: digest and hmac (md5, sha1, sha224, sha256, sha384, sha512, ripemd160), crypt and gen_salt (bcrypt, MD5, extended and traditional DES, the hash Postgres computes for the same salt), gen_random_bytes, encrypt, decrypt, encrypt_iv and decrypt_iv (AES of 128, 192 or 256 bits by the key's length, Blowfish, CAST5, DES and 3DES; CBC, ECB, and Blowfish's CFB; PKCS padding or none), OpenPGP's pgp_sym_encrypt and pgp_sym_decrypt (and their _bytea forms) with every option (ciphers, zip and zlib compression, MDC or none, a separate session key, S2K modes, counts and digests, convert-crlf, unicode-mode), armor, dearmor and pgp_key_id. A password hash or OpenPGP message made by either Chronos or Postgres is read by the other (tests/pgcrypto.rs); the deterministic ones and the errors are diffed against Postgres (tests/pgdiff.rs); messages gpg makes are read too. Before, CREATE EXTENSION pgcrypto gave only gen_random_uuid(), with a notice.
  • The functions are built in, in every database, as pg_trgm's are. CREATE EXTENSION pgcrypto records the extension in the world, so pg_extension lists it (version 1.3, in the schema it was made in), its functions are found by that schema's name (public.crypt(...), as pg_dump writes them), a second CREATE EXTENSION is 42710 and a DROP EXTENSION of one not made 42704. Being a row of the world, CREATE and DROP EXTENSION pgcrypto count as writes in the MCP server's safe mode.
  • Differences are in docs/postgres-compatibility.md: among them, bf, cast5 and des work here as with OpenSSL's legacy provider, where Postgres on OpenSSL 3 without it refuses them in encrypt and writes pgp_sym_encrypt's data with them unencrypted.
  • crypt (each MD5 and bcrypt round), the S2K and inflating a compressed message stop at statement_timeout and cancel (57014); a compressed message is inflated a piece at a time into the result, so it takes about its own size in memory. pgcrypto's functions are found by any schema's name, so a view or function calling public.digest(...) keeps working after DROP EXTENSION or DROP SCHEMA ... CASCADE, and a query string may make the extension and call one. ALTER EXTENSION pgcrypto UPDATE and SET SCHEMA work; DIFF AS SQL makes a schema before the extension in it; compress-level=0 means no compression and compress-algo=3 is refused only when there is something to compress, as in pgcrypto; domains of text, bytea and integer are taken as their base types; and the error messages of an unknown S2K digest, encrypt_iv and decrypt_iv, text that isn't UTF-8 and DROP SCHEMA are Postgres's.

Values reserved ahead go stale when another session restarts the sequence

  • A writing statement's nextval (insert into t select nextval('s') from generate_series(1, 7)) reserves values for its session ahead, and they outlived another session's restart of the counter: after ALTER SEQUENCE s RESTART, setval, TRUNCATE ... RESTART IDENTITY or a serial table dropped and made again committed elsewhere, the session's next nextval went on with the old count (23 where Postgres gives 1). Each counter's restarts are counted now, and reserved values from before the last one are dropped, so the session continues from the restart; the check is one atomic read while no counter has restarted since the values were reserved (tests/seq_pool_restart.rs).

nextval in a transaction counts by the sequence's committed definition

  • A transaction's nextval (and a column default's) counted by the sequence's definition as its snapshot saw it, so an ALTER SEQUENCE s INCREMENT BY 10 or MAXVALUE another session committed after the transaction began was ignored: it went on 1, 2 where Postgres gives 1, 11, and past the new maximum. Sequences aren't transactional in Postgres; it counts by the definition last committed now, at any isolation level, and by its own ALTER SEQUENCE when it made one. That ALTER SEQUENCE changes the definition as last committed too, as Postgres's reads its catalog anew, and its COMMIT keeps the result (also after a restart replays the log): with an INCREMENT BY 10 committed meanwhile, the transaction's MAXVALUE 1000 gives 1, 11, 21 and commits both, where it built on the snapshot's definition and its COMMIT failed with 40001, and one setting the definition back to what the transaction began with was lost at COMMIT. An ALTER SEQUENCE rolled back to a savepoint is gone: the transaction counts by the committed definition again. SELECT last_value FROM s and pg_sequences.last_value in the transaction count by the same definition, so they agree with the values its nextval handed out (tests/seq_committed.rs).
  • Once a transaction has taken, set or altered a sequence's values, another session's ALTER SEQUENCE of it waits for the transaction to end, as Postgres's waits for its lock; it didn't, and the transaction counted the rest of its values by the definition it began with while other sessions counted by the new one, so the two could hand out the same value. Two transactions waiting for each other this way fail one with 40P01, and so does a cycle through an advisory lock (an ALTER SEQUENCE waiting for a transaction that waits for pg_advisory_xact_lock the altering one holds) instead of both waiting forever. As in Postgres, the sequence stays the transaction's after a ROLLBACK TO SAVEPOINT of its ALTER SEQUENCE.
  • Another session's nextval, setval and currval wait for a transaction's uncommitted ALTER SEQUENCE of the sequence, as Postgres's wait for its lock, and then count by what it committed: they counted by the definition before it, and a transaction kept that definition after the ALTER committed, so the two could hand out the same value (tests/seq_committed.rs).
  • After the transaction's own TRUNCATE ... RESTART IDENTITY, its nextval, column defaults and currval still counted by the snapshot's definition, and the restart started at the snapshot's START: with an ALTER SEQUENCE s INCREMENT BY 10 MAXVALUE 15 committed meanwhile, they gave 1, 2 where Postgres gives 1, 11. They count by the definition last committed now, and the restart starts at the START last committed. SELECT last_value FROM s (and pg_sequences.last_value, also of a serial column's sequence) in such a transaction said where the old count was, 3, true after three rows where Postgres says 1, false; it says where the restart put the sequence and the values the transaction took since (tests/seq_committed.rs, tests/pgdiff.rs serial_counters_restarted_in_a_transaction).

ALTER TABLE ... RENAME CONSTRAINT for primary key, unique and foreign key constraints

  • ALTER TABLE t RENAME CONSTRAINT a TO b of a primary key, unique or foreign key constraint was 0A000 (only CHECK constraints could be renamed). It renames them now, as Postgres does: a key's or unique constraint's index takes the new name with it (pg_indexes, pg_class, a COMMENT ON INDEX), and pg_constraint, information_schema.table_constraints and referential_constraints, the duplicate-key and foreign key violations, ON CONFLICT ON CONSTRAINT and DROP CONSTRAINT use the new name. A taken name is 42P07 when an index or other relation of the schema has it (a key or unique constraint's index is renamed first) and 42710 when another constraint of the table does; a missing one, or a unique index CREATE UNIQUE INDEX made, is 42704. A partitioned table's key, unique or foreign key is renamed on it alone and a partition may rename its own, as in Postgres. The names are kept in the table's schema, so a restart that replays the log keeps them (tests/pgdiff.rs rename_key_constraints, tests/rename_constraints.rs).
  • A table renamed with ALTER TABLE ... RENAME TO kept calling its primary key <new name>_pkey; it keeps the key's name, as Postgres does.
  • A new primary key (CREATE TABLE, a partition's, ADD PRIMARY KEY) is <table>_pkey1, <table>_pkey2, ... when a relation, index or constraint of the schema already has <table>_pkey, as Postgres names it. Renaming a table and making a new one under its old name (alter table a rename to b; create table a (id int primary key)) gave both keys the name a_pkey, so a COMMENT ON INDEX, 'a_pkey'::regclass and RENAME CONSTRAINT a_pkey could reach the wrong one; the new one is a_pkey1 (tests/pgdiff.rs new_key_names_avoid_taken_ones).
  • A partition's copy of its partitioned table's foreign key is linked to it explicitly, so a rename of either keeps the two together: with two foreign keys on the same columns, DROP CONSTRAINT on the parent dropped the partition's copy of the other one (leaving an ON DELETE CASCADE the parent no longer had), and pg_constraint.conparentid paired them wrongly. Attaching a table whose foreign keys are on the same columns pairs each of the parent's with the one that acts as it does (tests/pgdiff.rs partition_foreign_keys_on_same_columns, tests/rename_constraints.rs).
  • A foreign key a partitioned table is given, or has when a table is attached, now gets a name of its own in a partition that already has a constraint of that name: <partition>_<columns>_fkey (1, 2, ... after it when the schema has that), as Postgres names it. The partition had two constraints of the one name, which is what renaming made easy: after alter table p rename constraint fa to fz; alter table p add constraint fa foreign key ..., the partition's copy of fz and its new copy of fa were both fa, so alter table p drop constraint fa dropped both and the partition stopped enforcing fz. A foreign key added without a name gives its partitions the parent's name for it (p_s_fkey, not p1_s_fkey), so DROP CONSTRAINT on the parent reaches them; CREATE TABLE ... PARTITION OF with a constraint of a name the parent's foreign key has is 42710, as in Postgres (tests/pgdiff.rs partition_foreign_key_copy_names, tests/rename_constraints.rs).
  • ADD CONSTRAINT (and a column's or CREATE TABLE's constraint) with a name another constraint of the table has was accepted for a foreign key, a unique constraint, or a check named as a foreign key, unique constraint or the primary key, leaving two constraints of one name; it is 42710 now, and 42P07 for a unique constraint whose name is an index's of the table, as in Postgres.
  • An unnamed unique constraint or foreign key of CREATE TABLE (and of ADD CONSTRAINT) is named after the names the table's constraints have already taken: <table>_<columns>_key1, _fkey1, ... As Postgres makes a new table's checks first, then its unique constraints, then its foreign keys, create table q (a int unique, constraint q_a_key check (a > 0)) makes the check q_a_key and the unique constraint q_a_key1, and a foreign key's name skips the names of its table's checks and unique constraints; the two statements were 42710 (tests/pgdiff.rs create_table_constraint_names_skip_named_ones).
  • A foreign key given to a partitioned table takes a foreign key its partition already has that is the same (columns, referenced table and columns, actions, deferrability) as the partition's copy, whatever its name, as Postgres does, instead of adding a second one: alter table pt drop constraint g left the partition's own g enforcing. A partitioned table attached whose partitions have copies of its foreign keys no longer gives them a second copy, which a drop of the parent's left behind (tests/pgdiff.rs partition_takes_its_same_foreign_key_as_copy, attach_partitioned_table_keeps_foreign_key_copies, tests/rename_constraints.rs).

Overloads across the search_path: nested calls, FROM, untyped parameters, ties with built-ins, the default search_path and DROP by kind

  • A call of a built-in's name nested in another where no query binds them took the built-in: with lower(int) and abs(text) made here, insert ... values (lower(abs('x'))), a DO block's and PL/pgSQL's lower(abs('q')) gave 7 where a SELECT gave lower(int)'s result. The inner call's type, of the function its arguments choose, chooses the outer one now (tests/pgdiff.rs nested_built_in_names_where_no_query_binds_the_call).
  • A set-returning function in FROM was chosen by its arguments' values, so select * from w('5') and w(null) between w(int) and w(float8) were 42883 (w(text)); a quoted literal and NULL are of unknown type there, as in Postgres, so they take w(float8), and u('2020-01-01') between u(date) and u(int) is 42725 (set_returning_overloads_in_from_by_written_arguments).
  • A prepared statement's or a driver's parameter declared no type chose an overload by the type worked out for it (text, by default), so prepare q as select m3(1, $1) between m3(int, int) and m3(text, int), and the same over the extended protocol, was 42883; it is of unknown type there, as in Postgres, and takes m3(int, int) (function_overloads_for_parameters_declared_no_type, tests/pg.rs untyped_parameters_choose_overloads_over_the_wire).
  • A subquery given to an overloaded function in INSERT's VALUES or PL/pgSQL was taken for text: g((select id from tt limit 1)) took g(text) and g(1 + (select 1)) was 42883 (g(bigint)). Its value's type chooses now (function_overloads_for_subquery_arguments_where_no_query_binds_the_call).
  • A function made here that ties with a built-in of its name was passed over for the built-in: format('%s', 1) with a format(text, bigint) gave 1, and concat(1, 2) with a concat(bigint, bigint) called it, a VARIADIC built-in not counting. Both are 42725, as in Postgres (functions_made_here_tying_with_built_ins).
  • With the default search_path (or set search_path = public), a function made with a built-in's name (md5(int), lower(int)) was never called; it is where its arguments choose it (built_in_names_with_the_default_search_path). So is one ON CONFLICT DO UPDATE's SET and WHERE call, which took the built-in (built_in_names_in_on_conflict_do_update); a CHECK, generated column or index expression where one wins is refused, as any function made here is there, where it silently took the built-in.
  • DROP PROCEDURE p and DROP FUNCTION f without argument types counted routines of both kinds, so a procedure in one schema and a function of that name in another were 42725; each counts only its own kind, and with argument types a routine of the other kind is 42809, as in Postgres, where Chronos dropped it (drop_function_or_procedure_by_kind_across_the_search_path).

Overloads across the search_path chosen by declared types, and an earlier schema winning when defaults make two the same

  • With overloads in several schemas on the search_path, a parameter, a window function's result, a named argument and a call no query binds were chosen among wrongly: f(i) in a SQL function and prepare p(int) as select f($1) took a later schema's f(text) over f(int) (a parameter counted as an unknown literal), abs($1) and abs(sum(k) over ()) took an abs(text) made here over the built-in, f(x => 1) between f(x int) and f(x text) in two schemas was 42725, and insert ... values (g(5::bigint)) or PL/pgSQL's g(b) for a bigint b took g(int) by the value over g(bigint). Each argument now has its declared type there, as in Postgres (tests/pgdiff.rs function_overloads_across_the_search_path_by_declared_types).
  • f(1) with f(x int) in one schema and f(x int, y int default 0) in a later one (either way round), and CALL of such procedures, was 42725; the earlier schema's is called, as Postgres hides a later function whose parameters taking the arguments have the same types, defaults not counting (function_overloads_across_the_search_path_with_defaults).

A built-in's name that a function made here shares means the built-in outside queries too, unknown arguments tie as in Postgres, and a function's body finds overloads made after it

  • With a function made here of a built-in's name on the search_path (lower(int), now(int)), a call of the built-in that no query binds took the function made here: insert ... values (lower('ABC')), limit length(lower('ab')) and PL/pgSQL's y := lower('QQ') and return lower(x) were 22P02, values (now()) 42883, default now() 0A000 and a generated column lower(x) 42P17. The arguments' types choose there as in a query, so they give the built-in, and lower(5) or abs('y') the function made here (tests/pgdiff.rs built_in_names_where_no_query_binds_the_call).
  • COALESCE, NULLIF, GREATEST and LEAST are syntax in Postgres, always the built-in; a function made here of one of their names was called instead (coalesce_nullif_greatest_least_are_syntax).
  • Unknown arguments chose their places one at a time, so with m(int, text) and m(text, int), m('1', '1') and m(null, null) took m(text, int). Each unknown place takes its category against all the candidates at once, as Postgres does, so those are 42725; and a function's body, parsed once, found only the schemas that had a function of a name it calls when it was parsed, so a g(text) made later in another schema was not called from it (unknown_arguments_and_overloads_made_later).

A function's plain name finds its overloads in every schema on the search_path, and a function made here with a built-in's name is called where it fits better

  • A call's plain name looked only in the first schema on the search_path that had any function of that name, so with f(int) in the first schema and f(text) in the second, f('x') was 22P02 and f(1::bigint) took f(int), and a tie Postgres refuses (42725) took the first schema's. Every function of that name on the path is a candidate now, chosen among by argument types as Postgres chooses (exact matches, then conversions to the preferred type, so k(1) between k(numeric) and k(float8) takes k(float8), then text for an unknown literal), an earlier schema's hiding only a later one of the same argument types; CALL and DROP FUNCTION f(text) find them the same way (tests/pgdiff.rs function_overloads_across_the_search_path).
  • With the search_path set, a function made here with a built-in's name was never called: abs('x'::text) with an abs(text) on the path was 22P02, upper('a', 'b') with an upper(text, text) was 42883, and length('abc') with a length(text) in a schema named before pg_catalog gave 3. The built-in is called only where it fits the arguments at least as well, and a function of the built-in's own argument types in a schema named before pg_catalog is called over it (built_ins_and_functions_made_here_of_their_names).
  • An unaliased call of a function found in another schema was named with its schema (s.f); it is named f, as in Postgres (tests/column_names.rs a_function_in_a_schema_takes_its_plain_name).

A function returning void gives the void value, not NULL

  • select pg_sleep(0), pg_notify(...), pg_advisory_lock(...), pg_advisory_xact_lock(...), pg_advisory_unlock_all(), setseed(...) and a PL/pgSQL function RETURNS void gave NULL (pg_sleep empty text), typed text: IS NULL was true for some, pg_typeof said text and a driver got type 25. They give the void value, as Postgres does: empty, not NULL, pg_typeof void, type 2278 on the wire; a SQL-language function returning void gives NULL as Postgres's does when Postgres runs it (a body such as select 1, several statements, FROM or WHERE), and the void value of the function it calls where Postgres inlines it (a body of just select pg_notify('c', m) or select pg_sleep(0), within the function's volatility and strictness) (sql_void_functions_inlined). void has no =, < and the like (42883), DISTINCT, DISTINCT ON, GROUP BY, ORDER BY and UNION (but UNION ALL) of it are 42883 (a void column shown beside DISTINCT ON's expressions is fine), and CREATE TABLE AS or a view with a void column is 42P16 where Chronos wrote a table it couldn't read back (tests/pgdiff.rs void_functions_return_void, builtin_void_functions, distinct_on_beside_void, tests/pg.rs void_over_the_wire).
  • CREATE TABLE ... IF NOT EXISTS ... AS and CREATE MATERIALIZED VIEW IF NOT EXISTS over a relation already there checked the new columns first, so a void, repeated or surplus column was an error (42P16, 42701, 42601) and a materialized view's query was run (select 1 / 0 was 22012), where Postgres finds the relation first and skips with a NOTICE. They skip first now, and without IF NOT EXISTS a void column over a relation already there is 42P07, not 42P16 (if_not_exists_skips_column_checks, tests/sql.rs relation_there_before_void_column).

PL/pgSQL's $1 is the first parameter whatever hides its name; a regproc reads an oid only from digits and only of 32 bits; a foreign key on a regproc column holds across search_paths

  • A PL/pgSQL $n was read as a reference to its parameter's name, so an inner DECLARE or FOR loop variable of that name took it over (declare a int := 5; ... return $1 gave 5, for i in 1..2 loop s := s || $1 gave 1,2,), and a table column of that name made embedded SQL fail with 42702 (where t.id = $1 in a function with a parameter id). $n is the parameter again, with its declared type (tests/pgdiff.rs plpgsql_numbered_parameter_is_the_parameter).
  • A string read as a regproc was an oid whenever it parsed as a number: '-1'::regproc was -1, ' 42' and '+42' were int4in, '00042' was int4in where Postgres reads octal (namein), and '4294967296' was kept. Only all digits are an oid now, by oidin's rules (22P02 for '08', 22003 past 32 bits), and an integer cast to a regproc wraps a negative int4 around ((-1)::regproc::oid is 4294967295) and refuses more than 32 bits (22003), as Postgres does; written to a regproc column and given as a parameter too (regproc_read_from_digits).
  • A foreign key on a regproc column keyed its references by the function's name as the search_path showed it, so a reference written under one search_path wasn't found under another and deleting its parent succeeded, leaving an orphan. It keys them by oid, as unique keys are (regproc_foreign_key_across_search_paths).

A regproc default, partition bound or unique column keeps its oid; EXECUTE's regproc arguments, comparisons with text, mixed sorting and pg_typeof of parameters match Postgres

  • A table's schema kept a regproc column's default, ADD COLUMN's value for the rows already there and a partition bound as the function's name, read back through the catalog while the catalog itself was being read: after create table t (p regproc default 'myf') every later statement, even select count(*) from pg_class and DROP, failed with 42883 (also after a restart), and a default or bound of an overloaded built-in (default 1397, abs(integer)) made them fail with 42725. A unique constraint on a regproc column keyed the name too, so two overloads (1397, 1396) clashed. They keep the oid, as Postgres does (tests/pgdiff.rs regproc_kept_in_a_table_s_schema).
  • EXECUTE's arguments were read outside the statement's world: a regproc parameter found only built-in functions (execute q('myf') was 42883) and a regproc argument (execute q('now'::regproc)) failed with 0A000. They are read as the statement is (regproc_given_to_execute).
  • A regproc compared with text ('now'::regproc = '1299'::text, in (...), <, a text column, a PL/pgSQL text variable) read the text as an oid; Postgres has no such operator, and it is 42883 now. A string literal is still read as an oid (regproc_compared_with_text).
  • CASE or COALESCE of a regproc and an integer sorted every integer before every regproc (order by coalesce(p, 5000)); they sort by oid together (regproc_mixed_with_integers_sorts_by_oid).
  • pg_typeof of a parameter said unknown for a NULL: a prepared statement's $1 declared text, a PL/pgSQL argument or variable (pg_typeof(a) with a NULL) and a value EXECUTE ... USING passes from a variable. It is the declared type, as in Postgres (pg_typeof_of_a_null_parameter).

A regproc keeps its type in every value: names in VALUES, defaults, RETURNING, $1 and EXECUTE ... USING; no arithmetic or casts Postgres lacks; ALTER COLUMN ... TYPE keeps its index

  • A regproc was a plain number once it left a place that knew its type, so it was written as its oid where Postgres writes its name: a PL/pgSQL $1 ($1 || '!' gave 1299!, pg_typeof($1) said text), a value EXECUTE ... USING passed (execute 'select $1::text' using p gave 1299), INSERT's VALUES and a column's default (concat('now'::regproc, '!'), to_jsonb(...), array[...], quote_literal(...) stored 1299!, 1299, {1299}, '1299'), RETURNING, a LATERAL subquery and a window function's result (concat(lag(p) over (...), '')). Each value now carries its type, so all of these give the function's name, and ::varchar(2) of one cuts it to no as it would text (tests/pgdiff.rs regproc_values_keep_their_type, regproc_as_text_in_values_returning_and_windows).
  • A regproc still worked as a number where Postgres has nothing for it: 'now'::regproc + 1 gave 1300, || 1 gave now1, sum, avg, string_agg and hashtext gave answers, casts to bool, numeric, float8, int2, jsonb, regclass and regtype gave 1299, and a function made here with a text or bigint parameter took it. Each is 42883 or 42846 now, as in Postgres (regproc_is_not_a_number, regproc_given_to_functions_made_here).
  • ALTER COLUMN ... TYPE text of a regproc column with an index or unique constraint left the index holding oids while the rows held names: where p = 'now' found nothing and a duplicate name went in. The rows are converted first and the index rebuilt from them; USING p::oid::text writes the oids' digits, as Postgres does, where Chronos wrote names (regproc_column_made_text).
  • PL/pgSQL's RETURN QUERY of an element of a regproc array (unnest(array['now'::regproc]), (array_agg(p))[1]) for a setof regproc function was 42804, since Chronos keeps such arrays as text[]; the text is read as a regproc again (regproc_returned_from_arrays).

A regproc is its function's name wherever Postgres writes it as text: PL/pgSQL expressions, set-returning functions, UPDATE ... FROM, MERGE, ALTER COLUMN ... TYPE text, big tables

  • In PL/pgSQL a regproc variable, a record's regproc field (a trigger's NEW.p), a subquery giving a regproc or a call of a function returning one was its oid inside an expression: concat(r, 'x'), format('%s', r), json_build_object('a', r), array[r]::text, quote_literal(r), row(r)::text, t := coalesce(r, ...), t := (select p from ...), RAISE ... USING message = r gave 1299x, {1299} or 42, and pg_typeof(r) said bigint. Expressions that name a regproc now run bound, as SQL does, and give the function's name and regproc (tests/pgdiff.rs regproc_in_plpgsql_expressions).
  • A set-returning function's regprocs for a column of text were oids: PL/pgSQL's RETURN NEXT r in a setof text function and a SQL function's rows gave 42. They are names; PL/pgSQL's RETURN QUERY of a regproc for a column of another type (or another type for a regproc column) is 42804, as in Postgres (regproc_rows_returned_as_text).
  • UPDATE ... FROM, MERGE (both branches), INSERT ... SELECT into a temporary table and ALTER COLUMN ... TYPE text wrote a regproc's oid into a text column; they write its function's name (regproc_written_as_text_by_every_statement).
  • A table big enough to be scanned on several threads failed with 0A000 (the catalog's functions need a database) as soon as a regproc of it was named, compared to a cast or written as text: the helper threads now read the statement's world and search_path (regproc_in_a_big_table).
  • A function of text or numbers took a regproc as its name or oid (lower('now'::regproc) was now, abs(...) and md5(...) gave answers), and LIKE, ILIKE, SIMILAR TO and ~ matched its oid's digits (so were quietly false). Each is 42883 now, as in Postgres, which has no such function or operator; functions of any type (concat, concat_ws's and format's values, quote_literal, to_jsonb) still take it as its name (regproc_given_to_functions, regproc_has_no_pattern_operators).

A regproc of a function made here outlives DDL; a regproc written as text is its name; notices before an error reach extended-protocol clients

  • A function made here had an oid counted from the tables, indexes, constraints and schemas before it, so any later DDL moved it: a regproc kept in a table (create table kept as select 'myf'::regproc f, then create table other (x int)) read back as a bare number or another function, f = 'myf'::regproc was false, and a merge or DIFF ... AS SQL between worlds carried an oid the other world gave to something else. A function's oid is now worked out from its name and argument types, the same after any DDL and in every world (tests/pgdiff.rs regproc_of_a_function_made_here_outlives_ddl, tests/catalog_introspection.rs a_regproc_of_a_function_made_here_is_the_same_in_every_world).
  • A regproc written where text goes kept its oid: insert into t (s) values ('now'::regproc), update t set s = r, ON CONFLICT DO UPDATE, a SQL function returning text, PL/pgSQL's SELECT INTO, :=, a declaration's default, RETURN, a record's field, ::text, || and RAISE's %, and $1::text of a prepared statement's regproc parameter gave 1299 or 42. They give the function's name, as Postgres does. CASE, COALESCE, GREATEST and LEAST of a regproc and a string literal or integer are a regproc (case when true then typinput else 'now' end was 42, greatest('int4in'::regproc, 'boolin') was 22P02), as are UNION and VALUES of a regproc and an integer; beside text each is 42804 (UNION types regproc and text cannot be matched) where Chronos gave the oid (tests/pgdiff.rs regproc_written_as_text, regproc_beside_other_types).
  • Over the extended protocol, a statement that raised notices and then failed sent only its error: the notices were dropped. They go before the error again, as Postgres sends them (tests/pg.rs notices_before_an_error_reach_an_extended_query).

A regproc is its function's oid: overloads, every comparison and ordering, regproc columns

  • A regproc was kept as the name it showed, so an overload lost which one it was ('abs(int)'::regprocedure::regproc::oid was 1394, another abs, and 2108::regproc kept in a table read back as 2107), comparisons other than =, < and > compared names or failed with 22P02 (typinput in (42, 1242), = any (array[...]), between, is distinct from, in (select ...), greatest), and every ordering but a plain ORDER BY was by name (window order by, an aggregate's order by, a UNION's ORDER BY; max(typinput) gave a name where Postgres gives the oid). A regproc is now its function's oid while a statement runs and is shown as its name as the result leaves, so all of these go by oid as in Postgres. A function made here with a built-in function's name and argument types is hidden behind pg_catalog unless the search_path names pg_catalog after its schema ('now'::regproc was 42725), and a catalog's regproc is shown with its schema when a function made here shares its name (pg_catalog.array_in). A column can be declared regproc or regprocedure (42704 before), so DIFF ... AS SQL of a table made from a catalog's regproc column replays. Read in binary, a regproc is sent as its oid without reading the catalog again for each value (a result of many regprocs took seconds) (tests/pgdiff.rs regproc_keeps_its_overload, regproc_compares_as_an_oid, regproc_orders_by_oid, regproc_columns, regproc_behind_pg_catalog, tests/pg.rs many_regprocs_over_the_wire, tests/catalog_introspection.rs a_kept_regproc_column_replays_from_diff_as_sql).

regproc and regprocedure are types; every built-in function by name or oid

  • A regproc was text: pg_typeof('now'::regproc) and pg_typeof(typinput) said text, typinput::oid and typinput = 42 failed with 22P02, 42::regproc was 42, and only the few functions a catalog row names could be read ('now'::regproc was 42883). regproc and regprocedure are now their own types, as in Postgres: every one of Postgres 16's built-in functions reads by name or oid and back ('now'::regproc::oid is 1299, 42::regproc is int4in, 'int4in'::regproc::regprocedure is int4in(cstring)), a name several have is 42725 ('sum'::regproc) and shown with its schema, functions made here read the same way, and the catalog's regproc columns compare with numbers and each other, and order, by oid. A driver gets them as type 24 and 2202, in binary as oids. pg_am.amhandler names Postgres's handlers (bthandler, not btreehandler) (tests/pgdiff.rs regproc_values, tests/pg.rs regproc_over_the_wire).

regtype[] columns read their types; public enums off the search_path; regtype and _regtype in pg_type

  • A regtype[] column (a table made from pg_prepared_statements' type lists) kept whatever text was written to it: '{int4,timestamptz}' stayed so, and '{nosuch}' was taken. Each name is now read as regtype reads it and kept as regtype shows it ({integer,"timestamp with time zone"}), by INSERT, UPDATE and COPY, and a name of no type fails with 42704, as in Postgres; vector reads as Chronos's own type, so 'vector'::regtype works too (tests/pgdiff.rs a_kept_regtype_array_column_reads_its_types, tests/catalog_introspection.rs a_kept_type_list_naming_a_vector).
  • pg_type had no rows for regtype (2206) and _regtype (2211), the type the lists go out as: reflection joining pg_attribute to pg_type lost such a column, information_schema.columns.udt_name was _text, 2211::regtype was 2211, and asyncpg, which looks the type up by id, couldn't read pg_prepared_statements at all ("no decoder for OID 2211"). They are there now, and 2211::regtype is regtype[] (tests/cursors.rs asyncpg_finds_the_regtype_array_type).
  • An enum in public was named bare by the type lists, format_type and regtype even with public off the search_path (pg_dump's empty one, or pg_catalog); it is public.mood there, as in Postgres, and 'public.mood'::regtype, which failed with 42704, reads it (tests/catalog_introspection.rs a_public_enum_off_the_search_path_is_named_with_its_schema).

regtype[] in binary for every type; tsvector and tsquery by name

  • A regtype[] read in binary (tokio-postgres, pgx, Npgsql) whose names weren't all known as oids went out as its names' text under the binary format, which drivers can't decode: pg_prepared_statements' type lists of a statement taking or returning tsvector or tsquery, a list with a null element, or one kept in a table (create table t as select parameter_types ...) read after the search_path no longer finds its enum by the name stored. Each is now a binary array of oids, as Postgres sends; an element naming a type dropped since is 0 (tests/cursors.rs every_regtype_array_goes_out_in_binary_as_oids).
  • format_type(3614, null), 3614::regtype::text and a tsvector or tsquery column's format_type in pg_attribute were ??? or the number, and 'tsvector'::regtype failed with 42704; they are tsvector and tsquery, as in Postgres (tests/pgdiff.rs text_search_types_by_name).

Tables made from pg_prepared_statements' type lists; enum names by the search_path; regtype[] in binary

  • A table, temporary table or materialized view made from pg_prepared_statements' parameter_types or result_types (create table t as select parameter_types from pg_prepared_statements, select ... into) failed with XX001, and a materialized view was made and then broke every later read of the catalog, \d and its own DROP, past a restart. The regtype[] column is now kept and read back; so is an int2vector or oidvector one (create table t as select indkey from pg_index), which failed the same way (tests/pgdiff.rs tables_made_from_the_prepared_statements_type_lists).
  • format_type(22, null) and format_type(30, null) overflowed the stack and closed the connection; they are int2vector and oidvector. format_type(2211, null) is regtype[].
  • An enum type in a schema on the search_path was named with its schema by format_type, regtype ('plain'::regtype::text) and the type lists; it is now bare where the path finds it, and a name that needs quotes has them ("Mood"), as in Postgres (prepared_statement_types_by_the_search_path).
  • A driver reading the type lists in binary (tokio-postgres, Npgsql) got their text under the regtype[] type id; it now gets a binary array of type oids, as Postgres sends (tests/cursors.rs a_drivers_binary_read_of_the_type_lists_is_an_array_of_oids). As JSON (to_jsonb, jsonb_build_object) they are arrays of names, not one string.

pg_prepared_statements: parameter and result types as regtype[]

  • pg_prepared_statements.parameter_types was text holding Chronos's own type names ({timestamptz}), and result_types was always null. Both are now regtype[], with the names regtype shows ({integer,text[],"timestamp with time zone"}, an enum's own name), so parameter_types::text[], array_length, unnest and subscripts give what Postgres gives; result_types is null for a statement that returns no rows. A declared parameter the statement doesn't use (prepare p (int, text) as select $1) was dropped, so EXECUTE p(1, 'x') failed; it now counts, for SQL PREPARE and a driver's Parse alike (tests/pgdiff.rs prepared_statement_parameter_and_result_types, tests/cursors.rs a_drivers_statement_lists_its_parameter_and_result_types).

pg_prepared_statements and pg_cursors through a view or a function; views and the catalog in function bodies

  • pg_prepared_statements and pg_cursors read through a view (create view v as select * from pg_prepared_statements) were empty: the session's statements and cursors were handed only to a statement naming either view itself. Every statement now reaches them however it reads them, at no cost to one that doesn't. A cursor's own query now sees it in pg_cursors, as in Postgres.
  • A LANGUAGE sql function's statements, and a PL/pgSQL function's (RETURN QUERY, PERFORM, SELECT ... INTO, INSERT, ...), failed with 42P01 on a view, a sequence read as a table, or a catalog table (pg_class, information_schema.tables, pg_prepared_statements). They now read them as a statement of its own does, as they are each time they run (tests/pgdiff.rs prepared_statements_and_cursors_through_views_and_functions, function_bodies_read_views_sequences_and_the_catalog). An expression's subquery in PL/pgSQL (RETURN (SELECT ... FROM a_view)) still fails on them.
  • In such a body, a column of a view, a sequence or a catalog table the statement reads was taken for an argument or a PL/pgSQL variable of the same name: update a_view set v = v + 1 in a function with an argument v wrote the argument's value plus one, and where name = 'p' over pg_prepared_statements compared the argument. The column now wins over a SQL function's argument, and is ambiguous with a PL/pgSQL variable (42702), as for a table's (tests/pgdiff.rs function_arguments_named_like_columns_of_views_and_the_catalog).

pg_cursors and pg_prepared_statements: the text a driver sent

  • A cursor DECLAREd (or a statement PREPAREd) through the extended protocol, as psycopg and other drivers do, showed in pg_cursors.statement (pg_prepared_statements.statement) the last simple query the connection ran, such as START TRANSACTION, instead of its own text. They now show the text of the protocol's Parse, as Postgres does (tests/cursors.rs the_views_show_the_text_a_driver_sent_through_the_extended_protocol).

pg_locks: who holds each advisory lock

  • pg_locks showed a pid only for the querying session's own locks, so monitoring couldn't join it to pg_stat_activity to see who blocks whom. Every lock held or waited for by a connection now has that connection's pid, as in Postgres. A program that opens several databases in one process also saw the other databases' advisory locks in each one's pg_locks; it now sees only its own (tests/sysinfo.rs pg_locks_shows_every_holders_and_waiters_pid, tests/advisory.rs pg_locks_shows_only_its_own_databases_locks).

serial in a transaction that drops and recreates its table (SQLAlchemy and Django test setups)

  • A table with a serial column dropped and made again inside one transaction kept counting from the old counter (ids 3, 4 where Postgres gives 1, 2), and so did TRUNCATE ... RESTART IDENTITY in a transaction: the restart waited for COMMIT and nextval didn't see it. Such a transaction now numbers its rows from the start, the counter moves past them at COMMIT, ROLLBACK leaves the old counter, and ROLLBACK TO a savepoint takes back a restart made after it, as in Postgres (tests/pgdiff.rs serial_counters_restarted_in_a_transaction).

Parallel pg_dump (pg_dump -Fd -j N): pg_export_snapshot() and SET TRANSACTION SNAPSHOT

  • pg_dump -j failed: its workers read the leader's snapshot. pg_export_snapshot() gives an id for what the calling transaction reads, and SET TRANSACTION SNAPSHOT 'id' as the first statement of another session's transaction makes it read exactly that (rows committed before the exporter began, not the exporter's own writes) until the exporting transaction ends. Postgres's errors: outside a transaction 25P01, after a query 25001, an unknown or ended snapshot 22023. Such a transaction only reads in Chronos (a write is 25006) and its COMMIT changes nothing; it is brought to the snapshot by logged writes to its own fork, so a restart replays to the same database. A real pg_dump -Fd -j 3 of Chronos restores into Postgres 16 complete (tests/snapshot.rs).

Monitoring views (what postgres_exporter, pgAdmin and Datadog read)

  • The 35 statistics views of Postgres 16 that were missing (pg_stat_wal, pg_stat_archiver, pg_stat_database_conflicts, pg_stat_io, pg_stat_ssl, the pg_stat_progress_*, pg_statio_*_indexes, pg_stat_xact_* and the rest) exist with their columns. pg_stat_bgwriter, pg_stat_wal, pg_stat_archiver and pg_stat_recovery_prefetch have the one row Postgres always has (counters 0), and the index, per-transaction and conflict views have a row per index, table and world.
  • pg_prepared_statements, pg_cursors and pg_locks were always empty: they list the session's prepared statements (SQL PREPARE's and a driver's named ones, with the query string they came in), its open cursors, and the advisory locks held and waited for. Compared with Postgres 16 in tests/pgdiff.rs monitoring_views.

MCP instructions tell a session that may merge to merge

  • The instructions the MCP server sends said "you can't merge: a person reviews your diff and merges it from a terminal" to every --agent session, even one with merge_own or merge, so some agents stopped at "run chronos merge <branch>" with the merge tool in their list. They now follow the session's rights: with --allow-merge, or an agent that may merge, they tell it to call merge when its diff is right, and that a merge policy may hold the merge for a person (then it stops and reports the branch and the reasons). Safe mode and an agent that may not merge get the same text as before.

JDBC tools read a Chronos schema (found by running the real pgjdbc 42.7.4: Hibernate, Flyway, Liquibase, DBeaver and Metabase read it this way)

  • DatabaseMetaData.getPrimaryKeys, getIndexInfo and getBestRowIdentifier failed with a syntax error: pgjdbc reads a key's columns with (information_schema._pg_expandarray(i.indkey)).n and a record column read back as (result.KEYS).x. Chronos now has records: (expr).field reads a field of a record value, the function _pg_expandarray(array) gives each element and its place (as a record in a select list, as columns in FROM), and a record prints as Postgres writes it, (5,1). (1).x is 42809 and an unknown field 42703, as in Postgres. The old reading of (table).column is unchanged; a record column of that name is read first when no table has it.
  • getColumns on any table with an array column, and getTypeInfo, failed with function "pg_catalog.array_in" does not exist: pgjdbc asks typinput = 'pg_catalog.array_in'::regproc. A regproc cast of a built-in function a catalog row names (array_in, int4out, ...) now gives its name, which is what those rows hold, and a regproc is shown by its name. (Known difference: that regproc is the name as text, so pg_typeof says text and ::oid of a built-in's name is not supported; catalog rows hold these names.)
  • Found by Codex on the PR: _pg_expandarray's x is the array's element type, as in Postgres (it was always an integer, so (..).x of a text array failed with 22P02), and a record fetched in binary format is sent as PostgreSQL's binary composite (field count, then each field's type id, length and bytes), not as text under the record's type id. A real asyncpg, which decodes records in binary, reads them the same as from Postgres. _pg_expandarray next to other set-returning functions in one select list is refused (0A000) rather than reported as an unknown function.
  • A function an application defines as public._pg_expandarray(...) was read as the built-in. Only information_schema._pg_expandarray is the built-in now, as in Postgres: the bare name is an error on the default search path, and the built-in with information_schema on it (SET search_path = information_schema, public).
  • pg_get_keywords() (getSQLKeywords): Postgres 16's 471 keywords, taken from a real Postgres 16.
  • The statements are in tests/pgdiff.rs as pgjdbc writes them, compared with Postgres 16 (pgjdbc_metadata_queries, record_fields); a full pgjdbc run of 33 metadata and data calls now passes, with the same answers as Postgres for everything but getTypeInfo (52 types, Postgres has 410), getUDTs, the grantee name and the default isolation level.

ALTER TABLE ... RENAME CONSTRAINT (CHECK)

  • ALTER TABLE t RENAME CONSTRAINT a TO b was a syntax error. A CHECK constraint is renamed, with Postgres's behaviour: the new name in the violation error and pg_constraint, its COMMENT ON CONSTRAINT, a column rename after it, an inheriting child's copy (renaming on the child alone is 42P16), 42704 for a missing name and 42710 for a taken one (including its own). A unique, primary key or foreign key constraint is 0A000: its name is part of the rows that keep it.

Rails and Django connect and migrate (found by running the real ActiveRecord 8.1 and SQLAlchemy 2.1)

  • SET IntervalStyle takes postgres, postgres_verbose, sql_standard and iso_8601 and writes intervals in each (P1Y2M3DT4H5M6.5S, +1-2 +3 +4:05:06.5, @ 1 day 2 hours ago), per session, in text and JSON; Rails sends iso_8601 on every connection, and was refused (0A000). Compared with Postgres 16 on 14 intervals in all four styles.
  • A derived table inside a subquery that reads the outer query's columns ((select array_agg(x) from (select t.a[i] x from generate_subscripts(t.a, 1) i) q)) was "missing FROM-clause entry for table"; Rails' foreign-key reflection is written that way, so foreign_keys(table) failed.
  • ALTER TABLE ... DROP COLUMN x CASCADE and DROP CONSTRAINT x CASCADE (Django writes both) drop the foreign keys, on any table, that reference what goes, with Postgres's NOTICE; without CASCADE they stay refused (2BP01).
  • Two fixes to those, from review: DROP ... CASCADE on something that several foreign keys of one table reference dropped only the last of them (the others came back, and rejected rows Postgres accepts); and a table alias hides the table's own name, so an outer t read inside from t u binds to the outer one.
  • pgdiff fails instead of skipping when CHRONOS_REQUIRE_ORACLE is set and there is no Postgres, so a dead oracle can't read as a pass.
  • Gap probe against Postgres 16.13: 12 gaps on the first fixture (was 101), 1 on the ORM fixture (was 34). The "known gaps" lists in the docs no longer name features that work (domains, MATCH FULL, ON COMMIT DROP, WITH CHECK OPTION, WITH TIES, regex lookaround, binary COPY FROM).

Table inheritance and declarative partitioning

  • CREATE TABLE child (...) INHERITS (a, b) with Postgres's column merging (same name and type merge, a clash of types is 42804, a clash of defaults 42622), inherited and local checks and NOT NULL, ALTER TABLE ... INHERIT / NO INHERIT, ADD/DROP COLUMN carried down (and refused on an inherited column, 42P16), SELECT/UPDATE/DELETE that reach the children and ONLY that does not, tableoid, and pg_inherits.
  • PARTITION BY RANGE | LIST | HASH on columns or expressions, PARTITION OF ... FOR VALUES FROM .. TO | IN | WITH (MODULUS, REMAINDER) | DEFAULT (MINVALUE/MAXVALUE), and sub-partitioning. Rows route to their partition on INSERT, INSERT ... SELECT, COPY and ON CONFLICT; a row with no partition is 23514 "no partition of relation ... found for row". Hash partitions place rows exactly where Postgres does. An UPDATE that changes the key moves the row. Overlapping bounds are 42P17.
  • ATTACH PARTITION (checks the rows against the bound, adopts matching columns, checks and indexes), DETACH PARTITION [CONCURRENTLY | FINALIZE], DROP of a partition or the parent (2BP01 without CASCADE), TRUNCATE of the parent, and indexes, primary keys and unique constraints on the parent (they must hold the key; each partition gets its own), foreign keys from and to partitioned tables, and triggers that follow the partitions.
  • Catalog: \d and \d+ of a parent and of a partition, pg_partitioned_table, pg_class.relkind = 'p', relispartition, relpartbound, pg_get_partkeydef, pg_get_expr(relpartbound, oid), pg_get_partition_constraintdef, pg_partition_tree, pg_partition_ancestors, pg_partition_root. A real pg_dump of a partitioned database restores into Postgres.
  • Compared with Postgres 16 on seven scripts of about 350 statements (rows and SQLSTATEs), no differences. Other errors and notices were reworded to Postgres's ("of relation", DETAIL lines on not-null and check failures).

SQL/JSON path

  • jsonb_path_exists, jsonb_path_match, jsonb_path_query_array, jsonb_path_query_first, the set-returning jsonb_path_query, their _tz forms, and the operators @? and @@ on jsonb, with the path language of Postgres 16: lax and strict modes, $var, filters ? (...), like_regex, starts with, arithmetic, .type(), .size(), .double(), .ceiling(), .floor(), .abs(), .keyvalue(), .datetime(). Errors carry Postgres's SQLSTATEs and texts; silent suppresses what it does there. Compared with Postgres 16 on a 55-statement script, no differences. The engine behind them (src/sql/jsonpath) was checked on a much larger generated set.
  • jsonpath is accepted as a type name (held as text; a bad path is 42601 when it is used).

Network address types

  • inet, cidr, macaddr and macaddr8 are types: columns, literals ('192.168.1.5/24'::inet), casts to and from text, ordering, indexes, min/max, JSON output, and pg_typeof. Invalid input is 22P02, as in Postgres.
  • Operators <<, <<=, >>, >>=, &&, +, -, &, |, ~ and the comparisons; functions host, text, family, masklen, netmask, hostmask, broadcast, network, abbrev, set_masklen, inet_same_family, inet_merge, trunc, macaddr8_set7bit. Compared with Postgres 16 on a 37-statement script, no differences.

Catalog queries tools send by table name

  • select ... from pg_catalog.pg_attribute a where a.attrelid = 'customers'::regclass ... (and the same for pg_index, pg_constraint, pg_trigger, pg_depend) returned no rows: Chronos answered psql's \d queries by matching their text, and took any query of that shape for one. psql names the table by number ('16385'), so only a query that does is answered that way now, and one that names it by ::regclass is answered from the catalog tables.
  • unnest(outer.array_col) inside a correlated subquery gave text elements, so ca.attnum = cols.colid never matched: the array(select attname from unnest(c.conkey) with ordinality ... join pg_attribute ...) that Django and SQLAlchemy ask for read {}. An array from the outer row keeps its type in the subquery.
  • pg_roles, pg_authid and pg_user list the name the session logged in as (a superuser, as every login is), so where rolname = current_user finds a row.
  • pg_stat_user_tables, pg_stat_all_tables, pg_statio_user_tables and pg_statio_all_tables list the tables, with their counters at 0 (Chronos keeps none).
  • pg_get_indexdef(oid, 0, true) leaves the schema off a table the search_path shows, as Postgres's pretty form does; information_schema.check_constraints.check_clause reads ((total >= (0)::numeric)).

|| by type, and a named jsonb_array_elements column

  • payload || '{"verified":true}' on a jsonb column (and '[3]' appended to a jsonb array) was text concatenation; an unknown literal beside a jsonb now reads as jsonb, so it merges or appends.
  • select 1 || 2, 1.5 || 2.5 and true || false (two typed numbers or booleans) are 42883 "operator does not exist", as in Postgres, instead of the text 12; 1 || '2' and 'a' || 1 still concatenate, as do arrays.
  • select i->>'sku' from jsonb_array_elements(x) as i was a whole-row composite under the alias; the single column of jsonb_array_elements, json_array_elements and their _text forms is named value with no alias and by the alias with one (as i, as t(v)).

Row values against subqueries, SET (a, b) = ..., and a row as a value

  • where (a, b) in (select x, y from t), (a, b) = any (...), (a, b) <> all (...) and (a, b) = (select x, y ...) (and <, <=, >, >=) were 0A000 or 42883; they now compare row by row with SQL's null rules (a true decides ANY, a false ALL, otherwise unknown; 21000 for a scalar subquery that returns two rows; 42601 when the subquery's column count differs).
  • update t set (a, b) = (1, 'x') and update t set (a, b) = (select x, y from u where u.id = t.id) were syntax errors.
  • A row is a value: select t from t gives (7,q,1) as Postgres writes it, row(1, 'a b', null) gives (1,"a b",), and count(t), t is null, t = t, (t).col, (t).* and row(t.*) work. It is held as that text (pg_typeof says text, not record, and a column of composite type still isn't there).

WITH CHECK OPTION on views

  • create view v as select * from t where a < 100 with local check option (and cascaded, or a bare with check option, which is cascaded) was 0A000. Now an INSERT or UPDATE through the view that would write a row the view doesn't show is 44000 ("new row violates check option for view", with the failing row), LOCAL checking the view's own condition and the ones of views below it that have an option, CASCADED all of them; a view that can't be written through refuses the option with 0A000. information_schema.views.check_option reports it. 38 statements diffed against Postgres 16.

GROUP BY a primary key, and xmax on an upsert

  • select id, name, count(*) from users group by id (every ORM writes it: the other columns follow from the key) was 42803 "must appear in the GROUP BY clause"; a table's columns are now allowed when its whole primary key is grouped, as in Postgres (a derived table or a view has no key, so still 42803).
  • insert ... on conflict (sku) do update set ... returning *, (xmax = 0) as inserted, the idiom for telling the rows an upsert made from the ones it changed, was 42703. xmax is 0 for a row inserted and 1 for one the conflict updated; xmin, xmax, cmin and cmax read 1, 0, 0, 0 anywhere else (Chronos keeps no per-row transaction ids). ctid and tableoid are not there.

Time zones with daylight saving time

  • SET TIME ZONE 'America/New_York' (and what JDBC sends at connect, TimeZone=Europe/Berlin in the options, ALTER ... SET timezone) was 22023: only zones with one offset all year worked. Named zones are now read from the system's tz database (no new dependency; tzdata must be installed, as for Postgres built with system tzdata), with their daylight saving time and history. A timestamptz shows the offset the zone had then, a wall clock a change skips or repeats is read as Postgres does, timestamptz + interval '1 day' counts days on the zone's calendar, and AT TIME ZONE, date_trunc(unit, ts, zone), generate_series(from, to, step, zone), make_timestamptz(.., zone), to_timestamp(text, fmt), casts between timestamp, date and timestamptz, extract, abbreviations (AT TIME ZONE 'PST', '2024-03-15 12:00 EST', '... America/Chicago') and to_char's TZ, tz, TZH, TZM and OF follow it. pg_timezone_names and pg_timezone_abbrevs are there. The rules were compared with Postgres 16 on 912 statements (every zone of the database, at its changes and far past its last one) and 100 more. to_char's FM now applies to the pattern right after it only, as in Postgres.

pg_trgm, fuzzystrmatch and unaccent

  • The three contrib extensions apps install most for search ("did you mean", fuzzy names, accent-insensitive matching) were 0A000 at CREATE EXTENSION and 42883 at every function. similarity, word_similarity, strict_word_similarity, show_trgm, the operators %, <%, %>, <<%, %>>, <->, <<->, <->>, <<<-> and <->>> on text, levenshtein (and _less_equal, with costs), soundex, difference, metaphone, dmetaphone(_alt), daitch_mokotoff and unaccent work, each the extension's own algorithm (Postgres 16 and Chronos agree on 138,000 generated cases and on the statements in tests/pgdiff.rs). CREATE INDEX ... USING gin (col gin_trgm_ops) (and gist_trgm_ops) is accepted and kept as a btree, so % and ILIKE scan. Not done: the pg_trgm.*_threshold settings (the defaults are used), a dependency error for DROP EXTENSION with an index on its operator class, and real as a type of its own (the results print as a float4 does).

jsonb_to_record and jsonb_to_recordset

  • Reading JSON as rows with a column definition list (select * from jsonb_to_recordset('[{"a":1,"b":"x"}]') as t(a int, b text), the way ORMs and ETL queries unpack a JSON column) was 42883. It works, with the json_ names too, for one object or an array of objects, from a value or from the row before it (from orders o, jsonb_to_recordset(o.items) as i(sku text, qty int)). Keys the list doesn't name are ignored, missing ones are NULL, values are cast as the named type (22P02), numeric(p,s) and varchar(n) limits are applied (22003, 22001), a name given twice is 42701, and a non-object is 22023. jsonb_populate_record needs composite types and is still refused.

to_number(text, template)

  • Reading a number out of formatted text (to_number('12,345.67', '99G999D99'), '$1,234.50' with L99G999D99, '<12>' with PR, Roman numerals with RN) was 42883, the last step of most CSV and ETL imports. src/sql/tonumber.rs is a port of Postgres's NUM_processor (its quirks included: every pattern takes one character of the text whether it fits or not, text left over is ignored), checked against Postgres 16 on 14,000 random and hand-picked template/text pairs with no difference. Numbers past numeric's 38 digits are not held.

CREATE DOMAIN

  • create domain email as text check (value ~ '@'), the usual way a schema keeps a rule in one place (and what pg_dump writes for one), was a syntax error, and a table using one could not be restored. A column of a domain is now its base type with the domain's NOT NULL, DEFAULT and CHECKs (a violation is 23514, the column's own DEFAULT wins), DROP DOMAIN is refused while a column uses it (2BP01) and CASCADE drops those columns. Checked against Postgres 16 (46 statements). Not done: checks on a cast to a domain, pg_typeof naming the domain, ALTER DOMAIN.

ORDER BY x USING >, and unnest of several arrays

  • order by name using > (ORMs that sort by operator) was a syntax error, and select * from unnest(array[1,2], array['a','b','c']) as t(x, y) was 42883; both work as in Postgres (the shorter array padded with nulls, WITH ORDINALITY too). Checked against Postgres 16 (22 statements).

ALTER options that only tune Postgres are accepted

  • ALTER TABLE t REPLICA IDENTITY FULL, CLUSTER ON idx, SET WITHOUT CLUSTER, ALTER COLUMN c SET STATISTICS 100 | SET STORAGE plain | SET (n_distinct = 10) and ALTER FUNCTION f(int) IMMUTABLE | SECURITY DEFINER | COST 10 | SET search_path ... were 42601 or 0A000, so a pg_dump script or a migration that contains them stopped half way. They are accepted with a notice (there is nothing here for them to tune). Checked against Postgres 16 (28 statements).

UNIQUE NULLS NOT DISTINCT, MATCH FULL, ON COMMIT DELETE ROWS | DROP, and TRUNCATE of a temporary table

  • Postgres 15's unique nulls not distinct (a) (constraint, column constraint or CREATE UNIQUE INDEX ... NULLS NOT DISTINCT: two nulls clash), FOREIGN KEY ... MATCH FULL (mixing null and non-null columns is 23503) and temporary tables ON COMMIT DELETE ROWS / DROP were 42601 or 0A000. They work as in Postgres, and show in pg_get_constraintdef, pg_get_indexdef and pg_index.indnullsnotdistinct.
  • TRUNCATE of a temporary table did nothing (it was looked for in the world, where it is not): it empties the table. Checked against Postgres 16 (110 statements). Not done: DEFERRABLE unique constraints.

TABLESAMPLE, and generate_series with a numeric step

  • select * from events tablesample bernoulli (1) (sampling a big table) was a syntax error; BERNOULLI and SYSTEM with REPEATABLE (seed) work, with Postgres's errors for a percentage outside 0..100, a null, or an unknown method. SYSTEM samples a row at a time here (Postgres takes whole pages), so the two can differ on small tables. generate_series(0, 1, 0.25) and generate_series(1, 2, 0.5) were 42804. Checked against Postgres 16 (26 statements, row counts compared by range since the draws are random).

UPDATE t SET a[i] = v and SET a[lo:hi] = v

  • Changing one element or a slice of an array column was a syntax error, so every such update had to read the array, edit it in the application and write it back. SET a[2] = 20, SET a[1] = 1, a[2] = 2, SET a[1:2] = '{7,8}' work as in Postgres: a place past the end grows the array with nulls, one before the start moves its lower bound ([-2:7]={...}), a null array is started, and a null subscript, a backwards slice or a too-small source are 22004 and 2202E. Checked against Postgres 16 (35 statements). Not done: more than one subscript (a[1][2]).

Degree functions, asinh/acosh/atanh, scale/min_scale/trim_scale, exact numeric to double

  • sind(30), cosd, tand, cotd, asind, acosd, atand, atan2d, asinh, acosh, atanh, scale, min_scale and trim_scale were 42883. The degree functions are Postgres's (first-quadrant reduction and exact constants, so sind(30) is 0.5 and tand(45) 1); checked on 14,000 angles and 1,000 sines against a real Postgres 16: no difference in any digit.
  • A numeric converted to a double was rounded twice (numeric 0.982 to float8 could differ in the last digit); it is rounded once now, as Postgres does. A literal too long for numeric's 38 digits (1e308) is a double instead of "bad number".

jsonb numbers keep the form they were written in

  • '7.50'::jsonb, to_jsonb(7.50::numeric) printed 7.5, '1e3'::jsonb 1e+3 and '100000000000000000000'::jsonb 1e+20, because numbers went through a double. serde_json now keeps the digits (arbitrary_precision) and they are printed as Postgres's numeric does (no exponent, the scale written, no -0); equality, ordering and containment still compare by value. Checked against Postgres 16 (8 statements). Left: '1.50'::jsonb::numeric is 1.5 (the cast goes through a double), and row_to_json prints jsonb's spacing.

jsonb editing: -, #-, jsonb_insert, jsonb_pretty, jsonb_set_lax

  • doc - 'key', doc - 2, doc - array['a','b'], doc #- '{a,b,0}', jsonb_insert(doc, '{a,1}', '9', true), jsonb_pretty(doc) and jsonb_set_lax(..., 'delete_key') were 42883 or syntax errors; a patch-style update of a document column had to be done in the application. They give Postgres's results and errors (22023 "cannot delete from scalar", "cannot replace existing key", ...). Checked against Postgres 16 (37 statements).

regexp_instr and the start/N/flags forms of regexp_*, string_to_table, SRFs in the select list

  • regexp_instr, regexp_count(s, p, start, flags), regexp_substr(s, p, start, N, flags, subexpr) and regexp_replace(s, p, r, start, N, flags) were 42883 or ignored their extra arguments; string_to_table did not exist. regexp_matches and string_to_table also work in the select list. Checked against Postgres 16 (22 statements).

LIKE ANY/ALL, the ~~ operators, ^@, and substring with a pattern

  • name LIKE ANY (ARRAY['a%', 'b%']), name NOT ILIKE ALL (...), name ~~ 'a%', !~~, ~~*, !~~* (what ORMs and pg_dump-style tools write for LIKE) and s ^@ 'prefix' were 42883 or 42601; they work, over an array literal, ARRAY[...], a column, or a subquery, with Postgres's rules for nulls and for an empty array. 'abc' ~ ANY (ARRAY['x','b']) answered false (the regular-expression operators were compared as if they were >=); ~, !~, ~* and !~* with ANY/ALL now match.
  • substring('Thomas' from '...$'), substring(s, 'o(.)b'), substring(s from '%#"o_b#"%' for '#') and substring(s similar '%#"cd#"%' escape '#') were 22P02 or syntax errors; they give the first parenthesized part of the match (or all of it), or the part between the escape-double-quotes. More than two #" separators is 2200C, as in Postgres (it was 2201B).
  • The lexer read four-character operators as three, so !~~* would not have parsed.

Text functions: translate, unistr, U&'...', sha224, character_length, letter case, chr

  • translate('12345', '143', 'ax'), unistr('d\0061t\+000061'), the literal U&'d\0061t' UESCAPE '!', sha224(bytea), character_length and the bpchar type name were 42883 or 42601, which broke exports and migrations that use them. They are read as Postgres reads them, errors and SQLSTATEs included (surrogate pairs, \+110000, a bad UESCAPE character). sha224 is written out in src/sql/bytes.rs (ring has no SHA-224).
  • upper, lower and initcap used Unicode's full case mapping (upper('straße') was STRASSE, lower('İ') two characters, a final capital sigma ς); Postgres maps one character to one, so they do now. Checked on every code point to U+2FFFF against a real Postgres 16: 110 of 194,559 differ, all letters newer than that server's C library knows (the two disagree on the Unicode version, not on the rules).
  • chr(0) is 54000 "null character not permitted" (it was 22003), chr(-1) 22023, and chr(n) for an n past integer 42883, as Postgres has them.

Regular expressions: Postgres's advanced syntax

  • Patterns from real codebases failed on syntax Postgres accepts: (?i) at the start, \y and \m word edges, (?<=...) and (?!...) lookaround, \1 back references to earlier groups, [[:<:]], \x41 and \u0041, and a \D in a newline-sensitive pattern. src/sql/regex.rs is rewritten to read a pattern as Postgres does (embedded options (?bcegimnpqstwx) and ***=/***: prefixes, the same flag letters in regexp_* calls, the greediness of the first quantified atom deciding whether the longest or the shortest match wins at the leftmost place, groups inside lookaround capturing nothing) and to refuse what Postgres refuses: a quantifier after an anchor or lookaround, \2 with one group, a back reference inside lookaround or to a group not yet closed (all 2201B).
  • Checked against Postgres 16 with 1,500 random patterns (100% of the 812 it accepts give the same rows, the 688 it refuses are refused) and 340 hand-picked ones (tests/pgdiff.rs, and the same answers without a server in tests/regex_syntax.rs). Left as they are: a lazy quantifier inside an alternation under a counted repeat ((a??|ab){2}), (x)+ where x can match nothing, and regexp_matches beside another set-returning function in the select list.

Set-returning functions inside select-list expressions

  • select generate_series(1, 3) * 2, select unnest(tags)::text, select jsonb_array_elements(doc -> 'items') ->> 'sku' and select upper(regexp_split_to_table(s, ',')) were 42883 (only a bare unnest or generate_series worked in the select list), and a script that expands a JSON array or a delimited string into rows had to be rewritten with a LATERAL join. Any of generate_series, unnest, jsonb_array_elements, jsonb_array_elements_text, jsonb_object_keys, regexp_split_to_table and generate_subscripts works inside an expression now, several side by side as Postgres runs them (the shorter padded with nulls); in WHERE, GROUP BY, HAVING or ON it is 0A000. Not done: in a grouped query (Postgres runs them after the grouping).
  • A lone jsonb value is an array of one, as in Postgres: '1'::jsonb -> 0 is 1, not null.

to_date and to_timestamp with a template, and make_time, make_timestamp, make_timestamptz

  • to_date('15/03/2024', 'DD/MM/YYYY') and to_timestamp('2024-03-15 10:30 PM', 'YYYY-MM-DD HH:MI AM'), the usual way to read dates that arrive as text (CSV imports, ETL, legacy formats), were 42883. They read a template as Postgres does (src/sql/datefmt.rs, ported from what DCH_from_char does, checked against 2,500 random templates and 1,500 round trips of Postgres's own to_char): every date, time, name, 12-hour clock, day-of-year, ISO-week and zone-offset pattern, how separators and spaces and digits are taken, the year adjustments (YY 24 is 2024, 70 is 1970), and each error with its SQLSTATE.
  • make_time, make_timestamp and make_timestamptz (with an optional zone) did not exist, and make_date(-44, 3, 15) was not 44 BC.

infinity, dates before the common era, and the special date words

  • 'infinity'::date and '-infinity'::timestamp were 22007, so the usual open-ended period (valid_to date default 'infinity', where now() < valid_to) could not be written; they are now the largest and smallest date, timestamp and timestamptz (on the binary wire too), sorting, comparing, indexing, adding intervals and days as Postgres has them, with isfinite(); and epoch, now, today, tomorrow and yesterday read as the moments they name.
  • '0044-03-15 BC' was a syntax error and year 0 an accepted date; BC dates parse and print as Postgres does (to_char with BC/AD too), year 0 is 22008, and a year of one or two digits is 20xx or 19xx ('44-03-15' is 2044-03-15, as Postgres reads it, not year 44).
  • generate_series(date, date, interval) gave timestamps; it gives timestamptz as Postgres does, and an infinite bound is 22023 (Postgres 16 loops forever).

Intervals: * and / by numbers, sum and avg, justify_*, OVERLAPS

  • interval '1 hour' * 2.5 and interval / 2 were 42883, sum(interval_column) failed, justify_hours, justify_days and justify_interval did not exist, (a, b) OVERLAPS (c, d) was a syntax error, and interval '0' was 22P02. They work as Postgres does: the products keep whole months and days and cascade fractions down (its interval_mul and interval_div, ported), sum/avg/min/max of intervals, the justify functions, and OVERLAPS over dates, timestamps and times (an end may be an interval; periods put in order; Postgres's rules for nulls), with a bare number in an interval read as seconds.

Math and number functions, hex literals, integer/boolean casts, bit aggregates

  • select 0x1F returned 0 (the lexer read 0 and an alias x1F); hex, octal and binary literals (0x1F, 0o17, 0b101) are the whole numbers they say, and trailing junk after a number (1_0x, 0x, 0b2) is a syntax error, as Postgres 16 has it.
  • 1::boolean and true::int were errors (42804); they cast as Postgres does. bit_and, bit_or, bit_xor and any_value did not exist. New functions: sin, cos, tan, cot, asin, acos, atan, atan2, sinh, cosh, tanh, degrees, radians, div, gcd, lcm, factorial, width_bucket, to_hex.
  • Fixed: round(2.5::float8) gave 3 (Postgres rounds halves to even: 2), and ceil, floor, trunc and sign of an integer were integers where Postgres gives double precision.

WITH ORDINALITY and FETCH FIRST ... WITH TIES

  • select * from unnest(tags) with ordinality as t(tag, n) was a syntax error, so the usual way to number what an array or a JSON array holds (or to pair two arrays by position) failed. It works on every set-returning function in FROM, alone or after a table. FETCH FIRST n ROWS WITH TIES, which was refused (0A000), now gives the first n rows and the rows after them that tie with the last on the ORDER BY keys, with OFFSET, GROUP BY, DISTINCT and UNION as Postgres has them; without an ORDER BY it is 42601, and a null or negative count is 2201W. It reads the whole ordered result rather than keeping the best n.
  • Fixed on the way: SELECT * FROM LATERAL (SELECT 1) t gave no rows; and a subquery in the FROM of a correlated subquery could not read the outer row ((select count(*) from (select * from unnest(t.tags) u) z) was 42703).

BETWEEN SYMMETRIC, TABLE t, MATERIALIZED hints and IS NULL of a row

  • Four spellings of the SQL standard that Postgres reads were syntax errors here: x BETWEEN SYMMETRIC a AND b (the bounds in either order), TABLE t (SELECT * FROM t, in a statement, a subquery, a UNION and CREATE TABLE AS), WITH c AS [NOT] MATERIALIZED (...) (a hint that ORMs and query builders emit; ignored, since the rows are the same), and (a, b) IS [NOT] NULL (all fields null, and all fields not null, so a row can be neither), which failed with "composite values aren't supported".

A table's row as JSON: to_jsonb(t), row_to_json(t), json_agg(t), and arrays as JSON arrays

  • select to_jsonb(t) from t and select json_agg(t) from t (the shape an API layer reads a whole table as) failed with "column t does not exist", and row_to_json was not a function. A table's name, its alias or t.* now stands for its whole row where a value is wanted by to_json(b), row_to_json, json(b)_agg, jsonb_object_agg and json(b)_build_object / _array: an object of its columns, as Postgres builds it (a column with the table's name still wins, as does the single column of a function's alias). A bare select t from t is still an error: there is no composite type to return.
  • Arrays inside JSON were strings (to_jsonb('{x,y}'::text[]) gave "{x,y}"); they are JSON arrays now, typed by their element type ([1, 2], ["x", "y"], [true, false]), and array_to_json exists. Still open: a numeric in JSON is a double, so 7.50 reads back 7.5.

AT TIME ZONE and timezone(zone, value)

  • ts AT TIME ZONE 'Asia/Tokyo' was a syntax error, so any query that shows a timestamptz as a wall clock in a named zone failed. It works now: a timestamp read in a zone is a timestamptz, a timestamptz shown in one is a timestamp, a date is its midnight, and timezone(zone, value) is the function form. The zone is a name of a zone with one offset all year, a POSIX offset string, or an interval, and the operator has Postgres's precedence (a + b AT TIME ZONE 'UTC' is a + (b AT TIME ZONE 'UTC')). Zones with daylight saving time (America/New_York, Europe/London) are still 22023, as SET TIME ZONE says: Chronos carries no time zone database, and adding one is a decision for the project. extract and AT TIME ZONE columns are named extract and timezone, as Postgres 14 names them (at_time_zone in tests/pgdiff.rs, function_columns_are_named_as_in_postgres in tests/sql.rs).

EXTRACT is a numeric

  • extract(year from d) gave 2024.0, a double precision, where Postgres 14 and later gives the numeric 2024, so an ORM read a float where it expects an integer or a decimal, and extract(second ...) and extract(epoch ...) lost the six decimals Postgres writes (45.678901, 1710513045.678901). It is a numeric now at Postgres's scale for every field (second and epoch six decimals, milliseconds three, julian twenty for a timestamp), date_part is the same value as a double precision, and the fields Chronos did not have are there: century, decade, millennium, week, isoyear, julian, timezone, timezone_hour, timezone_minute, on date, timestamp, timestamptz, time and interval as each has them (hour of a date is 0A000, an unknown unit 22023, as in Postgres). An interval's epoch counted a year as 12 months of 30 days; it is 365.25 days, as Postgres has it, and interval '100:00:00' (more than 24 hours) now reads (extract_is_numeric in tests/pgdiff.rs, which compares every field on every type against a real Postgres 16). EXTRACT(field FROM 'text') is still read as a timestamp, which Postgres refuses.

Statistical aggregates, and list aggregates as window functions

  • stddev, variance, corr, covar_pop, regr_slope and their families did not exist (42883), so any report that measures spread or correlation failed on everyday SQL; and string_agg(...) OVER (...), array_agg(...) OVER (...), jsonb_agg(...) OVER (...) were 0A000. All of stddev, stddev_pop, stddev_samp, variance, var_pop, var_samp, corr, covar_pop, covar_samp and regr_count, regr_avgx, regr_avgy, regr_sxx, regr_syy, regr_sxy, regr_slope, regr_intercept, regr_r2 exist now, with Postgres's own arithmetic so the results agree digit for digit with a real Postgres 16: floats accumulate as its float8_accum and float8_regr_accum (Youngs-Cramer) do, integers and numerics through its numeric arithmetic (a numeric result, at the scale it picks for a quotient), with FILTER, DISTINCT and an inner ORDER BY, in GROUP BY queries and as window functions over any frame (statistical_aggregates and list_and_statistical_aggregates_as_windows in tests/pgdiff.rs). A real column is held as a double here, so stddev over one can differ in the last digits. Dec gained a rounded square root (sqrt_at) for the numeric standard deviation.

CREATE TABLE AS and SELECT INTO

  • create table t as select ... and select ... into t from ... were syntax errors, so every script, migration and report that copies a query's result into a table failed at the first one (found by running a set of everyday statements against Postgres and Chronos side by side). Both work now, with TEMP, IF NOT EXISTS, a column-name list (create table t (a, b) as ...), WITH [NO] DATA, and SELECT, WITH and VALUES queries: the table has the query's columns and rows and is a plain table after that, as Postgres's is, and it is all or nothing (a query that fails leaves no table; inside a transaction it rolls back with it) (create_table_as in tests/pgdiff.rs). Column types are the query's as Chronos types them: varchar(n) becomes text, smallint becomes integer.

All of Postgres's settings: SHOW, SET, RESET, set_config and pg_settings

  • Chronos knew 26 of Postgres 16's 363 settings. SET foo_bar = 1 succeeded (Postgres: 42704), SET work_mem = '64MB' succeeded and SHOW work_mem then said the setting did not exist, pg_settings had 26 rows, and SQLAlchemy's get_table_options() and so Alembic's autogenerate died on current_setting('default_table_access_method'). The 363 settings now exist (src/sql/settings.tsv, made from a real Postgres 16, with each one's type, unit, range, allowed values, context and description). SET checks a value as Postgres does (booleans in every spelling, '64MB'/'1.5s'/'2min' units and rounding, ranges, enum values, 22023 for a bad one, 55P02 for a setting a session may not change, 42704 for an unknown name), SHOW, current_setting() and pg_settings read it back in Postgres's units (64MB, 1500ms, on), and RESET, SET ... TO DEFAULT and set_config() work on all of them. A SET is now undone by ROLLBACK, ROLLBACK TO SAVEPOINT and a failed transaction (it stuck before), and a custom setting once set stays, empty, as in Postgres. A setting that would change an answer if Chronos honored it as Postgres does (DateStyle, IntervalStyle, standard_conforming_strings, bytea_output, extra_float_digits below 1, client_encoding, session_replication_role, default_transaction_read_only, lc_numeric ...) takes only the value Chronos implements and refuses another with 0A000, where it was accepted and ignored. Not done: set_config('search_path' | 'statement_timeout' | 'TimeZone', ...) still leaves the session's own settings as they were, and SHOW synchronous_commit is the database's mode (pg_settings says what a session set) (settings and settings_follow_transactions in tests/pgdiff.rs, settings_that_change_answers_take_only_what_chronos_does in tests/pg.rs).

What SQLAlchemy and psycopg needed: LIKE ... ESCAPE, int2vector, generate_subscripts

  • Running real drivers (psycopg 3, SQLAlchemy 2.1) against Chronos, which the probes had not, found the reflection calls broken: Inspector.get_schema_names() writes NOT LIKE 'pg/_%' ESCAPE '/', which was a syntax error (LIKE, NOT LIKE and ILIKE now take ESCAPE 'c': the escape character makes the next one literal, a backslash is an ordinary character once another escape is named, ESCAPE '' turns escaping off, a longer one is 22025); get_columns() calls pg_collation_is_visible (and its siblings pg_function_is_visible, pg_operator_is_visible, pg_opclass_is_visible, pg_opfamily_is_visible, pg_conversion_is_visible now exist); and get_indexes() failed in psycopg with malformed array because pg_index.indkey, indclass, indoption and indcollation reached it as int2[] (type id 1005) holding text like 1 2, which is Postgres's int2vector and oidvector (type ids 22 and 30; in binary an array that counts from 0). They are those types now, indkey::int2[] is [0:1]={1,2} as in Postgres, pg_typeof(conkey) says smallint[], and int2[] and oid-element arrays go out in binary with two- and four-byte elements as Postgres sends them, where they went as four and eight. generate_subscripts(array, 1[, reverse]) works in FROM (SQLAlchemy reads index columns with it). A cast to int2[]/smallint[] carried the new smallint range check into the array (it was refused as an integer): fixed (like_with_an_escape_character, index_vectors in tests/pgdiff.rs, int2vector_and_oidvector_over_the_wire in tests/pg.rs).

smallint holds 16 bits

  • A smallint column was an integer underneath: insert ... values (40000) succeeded, and pg_dump, information_schema and psql's \d said integer, so a dump restored into Postgres changed the column's type. The column now refuses what doesn't fit (smallint out of range, 22003, on insert, update and ALTER COLUMN ... TYPE smallint over rows that don't fit), x::smallint and '40000'::smallint check the range, the width survives LIKE and ADD COLUMN, and the catalogs (atttypid 21, attlen 2, format_type, information_schema.columns.data_type) say smallint. psql's \d also shows character varying(5), character(3) and numeric(10,2) as they were made, where it said text and numeric. Not done: a smallint still reaches a driver as int4 in result columns and parameters, arithmetic on smallints is not range-checked until it is written, and CREATE TABLE ... AS makes an integer (smallints in tests/pgdiff.rs, psql in tests/pg.rs).

Error position and hint, and Postgres's syntax error wording

  • A syntax error carried only a message, so psql could not draw its LINE 1: ... ^ caret and drivers had no position. It now says syntax error at or near "x" (or at end of input), as Postgres does, sends the position of the token (characters from 1, across lines and non-ASCII text) and, as its HINT, what was expected there (Expected a value). Dropping a table other objects depend on says cannot drop table t because other objects depend on it with the DETAIL (constraint fk on table c depends on table t, view v depends on table t) and the HINT (Use DROP ... CASCADE to drop the dependent objects too.) as Postgres does, where it used to put it all in one message; a backward FETCH on a NO SCROLL cursor carries Postgres's hint (syntax_errors_carry_their_position, errors_that_say_what_to_do_carry_a_hint in tests/pg.rs). The error's message text changed for these, its code did not.

pg_stat_activity, pg_stat_database and pg_terminate_backend

  • pg_stat_activity had the columns and no rows, so every monitoring tool (pgAdmin's dashboard, postgres_exporter, Datadog, pg_top, PgHero) saw a server nobody was connected to; it lists the open connections: pid, usename, datname (the world), application_name, client_addr and port, backend_start, xact_start, query_start, state_change, state (active, idle, idle in transaction, idle in transaction (aborted)) and the query it is running or ran last (its first 1024 bytes, as Postgres keeps). pg_stat_database has a row for each world with its numbackends; its other counters are null (Chronos keeps none). pg_terminate_backend(pid) did not exist: it closes the connection (pg_cancel_backend only stops its statement), and both say PID n is not a PostgreSQL backend process for a pid nobody has; an agent can do neither (42501) (pg_stat_activity_shows_connections_and_pg_terminate_backend_ends_one in tests/sysinfo.rs).

information_schema.routines

  • select * from information_schema.routines was 42P01, so tools that list a database's functions and procedures through the standard catalog (DBeaver, dbt, schemacrawler, Metabase's sync) found none. It lists the world's functions and procedures as Postgres's view does: specific_name (name and oid), routine_type, data_type (record for a table function, trigger, void, none for a procedure), routine_body (SQL for LANGUAGE sql, else EXTERNAL), routine_definition, external_language, is_deterministic (immutable only) (information_schema_routines in tests/pgdiff.rs). Fewer columns than Postgres's 80-odd: the ones tools read.

uuid is a type

  • A uuid column was text underneath, which was wrong twice over. Answers: 'A0EEBC99-...' and 'a0eebc99-...' were two different values (so where id = 'A0EE...' found nothing and both could be inserted as one key), 'not-a-uuid' was accepted, and pg_typeof, information_schema, pg_dump and psql's \d said text. Wire: drivers got text where they expect a UUID (sqlx's Uuid, pgx's pgtype.UUID, npgsql's Guid, JDBC's getObject(UUID)), and a typed uuid parameter (type id 2950) was unknown. It is now its own type: input in Postgres's spellings (either case, no hyphens, braces, hyphens after any group of four digits, anything else 22P02), stored and printed as lowercase 8-4-4-4-12, literals compared with a uuid read as one, 16 bytes in binary both ways, gen_random_uuid() a uuid, and usable as a primary key, unique or indexed column and foreign key target (uuids in tests/pgdiff.rs, uuid_over_the_wire in tests/pg.rs, tests/pg_dump.rs). chronos import makes uuid columns uuid. Tables made before keep text columns; ALTER TABLE t ALTER COLUMN id TYPE uuid converts them (and rewrites a key's rows to the canonical text). The Mem0-style test in tests/sql.rs used 'a', 'b' as uuids, which Postgres refuses: it uses real ones now.

ALTER VIEW ... RENAME TO, ALTER MATERIALIZED VIEW ... RENAME TO, pg_has_role

  • Renaming a view was refused (ALTER VIEW does OWNER TO only here), and ALTER TABLE v RENAME TO on a view said the view wasn't there; both now rename it (the comment goes with it), as does ALTER MATERIALIZED VIEW. A view another view reads still can't be renamed here (0A000: views name what they read), and RENAME COLUMN on a view is 0A000 (renaming_views in tests/pgdiff.rs).
  • pg_has_role([user,] role, privilege) did not exist, so psql 17's \l+ failed; it answers as the other has_*_privilege functions do (the database's users have every role's privileges).

Notices and warnings where a statement does less than asked, as Postgres words them

  • ALTER TABLE IF EXISTS nosuch ... was 42P01: it is a notice (relation "nosuch" does not exist, skipping) and the statement does nothing, so migration scripts that guard with IF EXISTS run through. ALTER SEQUENCE IF EXISTS says so too.
  • CREATE TABLE | INDEX | SEQUENCE | MATERIALIZED VIEW IF NOT EXISTS on something already there, DROP INDEX | EXTENSION IF EXISTS, ALTER TABLE ... DROP COLUMN | CONSTRAINT IF EXISTS and DROP TRIGGER IF EXISTS ... ON a missing table did nothing without saying so (or worded the notice differently); they now send Postgres's notice.
  • COMMIT and ROLLBACK outside a transaction, and BEGIN inside one, send Postgres's warnings (there is no transaction in progress, there is already a transaction in progress); they were silent. (tests/notices.rs; a function's signature in DROP FUNCTION IF EXISTS f(int) is still worded f(integer), where Postgres echoes the type as written: f(pg_catalog.int4).)

psql's \d+, \dt+ name and OPERATOR(pg_catalog.~)

  • \d+ table crashed psql 16 ("column number 9 is out of range 0..8": it asks for the compression method and statistics target of each column too, and the answer had two columns fewer); it now lists them, with each column's comment.
  • a OPERATOR(pg_catalog.~) b (and =, <>, <, <=, >, >=, !~, ~*, !~*) was a syntax error, which is how psql writes every name pattern: \dt+ orders, \di+ idx, \dv+ v failed. The operator, named with or without its schema, is that operator (schema_qualified_operators in tests/pgdiff.rs).

COMMENT ON is kept

  • COMMENT ON TABLE | VIEW | MATERIALIZED VIEW | SEQUENCE | INDEX | COLUMN | CONSTRAINT | SCHEMA checked its object and threw the text away ("the comment isn't kept"), so obj_description, col_description, pg_description, psql's \d+ and \dt+, PostgREST's and Hasura's descriptions, and the COMMENT ON lines of pg_dump all came out empty. The comments are rows of the world: they fork, diff (DIFF ... AS SQL writes COMMENT ON after what it is on) and merge with it; they follow a renamed table, column or index and go with a dropped object (a table made again has none); NULL or '' takes one away. Comments on functions, types, triggers, extensions and databases are still not kept, and say so (comments in tests/pgdiff.rs, tests/comments.rs, tests/pg_dump.rs).

Cursors, and Execute's row limit

  • DECLARE name [SCROLL | NO SCROLL] CURSOR [WITH HOLD] FOR query, FETCH / MOVE (NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE, counts, ALL, FORWARD, BACKWARD) and CLOSE did not exist (42601), so psycopg's named server-side cursors, psql's \set FETCH_COUNT and every "stream a big table" recipe failed. A cursor keeps the rows its query gave when it was declared (as Postgres's snapshot does), ends with its transaction unless it is WITH HOLD, and follows Postgres's positions, tags and errors (34000, 42P03, 25P01, 55000); ~100 statements agree with Postgres 16 (cursors in tests/pgdiff.rs, tests/cursors.rs). Unlike Postgres, a cursor without NO SCROLL scans backward whatever it reads, and BINARY cursors and WHERE CURRENT OF are refused.
  • The protocol's Execute row limit was ignored (every row came back at once, never PortalSuspended), so JDBC's setFetchSize, postgres's query_portal and every driver that pages through a portal got everything. Execute now sends that many rows and PortalSuspended, and the next Execute goes on; a portal that has run to its end is 55000 if run again, as in Postgres, instead of running the statement again.

MERGE INTO, and UPDATE ... FROM / DELETE ... USING a view

  • SQL's MERGE INTO t USING source ON ... WHEN [NOT] MATCHED [AND ...] THEN UPDATE | DELETE | INSERT | DO NOTHING was a syntax error (MERGE meant only Chronos's MERGE BRANCH), so the upsert-by-source statement ORMs, ETL tools (dbt's merge strategy, Airbyte, Fivetran) and hand-written migrations use failed. It follows Postgres 16: several clauses each with an AND condition, a table, view or subquery as the source, parameters through a driver or PREPARE, the tag MERGE n, and 21000 when a target row would change twice; all clauses are one write. The target needs a primary key here (0A000 without), and a temporary table or view as the target, EXPLAIN MERGE and Postgres 17's NOT MATCHED BY SOURCE / RETURNING are refused (merge_into in tests/pgdiff.rs, tests/merge_into.rs).
  • UPDATE t SET ... FROM some_view and DELETE FROM t USING some_view answered table "some_view" does not exist (the views a FROM reads were expanded for a SELECT, not for these); they read the view (update_and_delete_read_views in tests/pgdiff.rs).

Data-changing WITH queries

  • WITH x AS (INSERT ... RETURNING ...) SELECT ... was a syntax error (WITH goes with SELECT), so PostgREST's and Hasura's mutations, and every WITH moved AS (DELETE ... RETURNING *) INSERT INTO b SELECT * FROM moved, failed. INSERT, UPDATE and DELETE (with RETURNING) work in a WITH before a SELECT, INSERT, UPDATE or DELETE: each runs once, in order, and the rest reads what they returned; the whole statement is atomic; a driver's parameters and the describe of the result work. A statement that reads a table an earlier one of the WITH changes is 0A000 (Postgres shows it the old rows; here it would see the new). Checked against Postgres (writable_ctes in tests/pgdiff.rs) and for the refusals, atomicity, agents and the wire in tests/writable_ctes.rs.

ALTER COLUMN ... TYPE varchar(n) keeps the new length

  • ALTER TABLE t ALTER COLUMN name TYPE varchar(100) (what Django's AlterField writes when a CharField's max_length changes, and Rails's and Alembic's change_column) changed the type but kept the old limit: the column stayed character varying(50) in the catalog and a 60-character value was refused with 22001. The length of varchar(n) / char(n), the precision and scale of numeric(p, s) and the dimensions of vector(n) now change with the type, and existing values must fit (22001, 22003, 22000, as Postgres). The 22001 message reads character varying(4), as Postgres's does, not character varying (4). Checked against Postgres (alter_type_keeps_the_new_length_and_precision in tests/pgdiff.rs).

Fixed: a deadlock between forking a world and the first SQL check

  • fork (which holds a world's rows while it takes the map of worlds to add one) and the first look for SQL tables and views (probe_catalog, run by the first write after opening, which held that map while it read every world's rows) took the two in opposite orders: with a write waiting on the same world's rows, all of them waited on one another forever. concurrent_history_replays_exactly hung one run in three on a busy machine; it now passes 25 runs in 25.

bytea

  • The binary string type did not exist (type "bytea" does not exist), so Django's BinaryField, SQLAlchemy's LargeBinary, Prisma's Bytes, Rails's binary, and every hash, key and file column failed to create. bytea is a value type of its own (chronos::sql::Value::Bytea): read as \x hex or the escape form, printed as hex, sent in binary as the bytes, ordered bytewise (so ORDER BY, GROUP BY, DISTINCT, indexes, UNIQUE and primary keys of one or several columns work), with length, octet_length, bit_length, substring, position, overlay, ||, string_agg, get_byte, set_byte, encode / decode (hex, base64, escape), md5, sha256, sha384, sha512, convert_to and convert_from, and Postgres's errors and SQLSTATEs. overlay(s PLACING r FROM n FOR m) also works on text (it was a syntax error). Checked against Postgres (bytea in tests/pgdiff.rs), through a driver's binary parameters and results and COPY (bytea_over_the_wire in tests/pg.rs), and by pg_dump (tests/pg_dump.rs). Stored as the bytes' hex text, so twice their size.

Size functions

  • pg_size_pretty, pg_size_bytes, pg_relation_size, pg_table_size, pg_indexes_size, pg_total_relation_size and pg_database_size did not exist (function pg_size_pretty(bigint) does not exist), so every admin tool's table list (pgAdmin, DBeaver, DataGrip, Metabase's sync) and psql's \dt+, \di+ and \l+ failed or showed no sizes. pg_size_pretty and pg_size_bytes match Postgres exactly; the relation sizes are estimates in Postgres's 8 kB pages from the rows and index entries stored. Checked against Postgres (size_functions in tests/pgdiff.rs, and tests/pg.rs).

Foreign keys to a UNIQUE column

  • REFERENCES t (email) where email is UNIQUE (Django's ForeignKey(to_field=...), Prisma's references: [email], Rails's primary_key:) was 42830 ("a foreign key must reference the primary key"). It takes a UNIQUE constraint's or a plain unique index's columns, several of them too and in any order, and ON DELETE / ON UPDATE act on a change of the referenced value (not only of a primary key). The catalog says what it references (pg_constraint.confkey, conindid, information_schema.referential_constraints.unique_constraint_name, pg_get_constraintdef, pg_dump), and diff as sql writes the columns. What it needs is kept: the unique constraint, index or column can't be dropped while a foreign key uses it (2BP01), nor its type changed except integer <-> bigint. Checked against Postgres (foreign_key_to_a_unique_column in tests/pgdiff.rs) and across worlds, merges and reopening (tests/fk_unique.rs).

Foreign key ON UPDATE actions, and ON DELETE SET DEFAULT

  • Changing the primary key of a row other rows reference deleted the referencing rows when the foreign key said ON DELETE CASCADE (and set them null under ON DELETE SET NULL): the update was read as a delete of the old key. Postgres refuses it (23503, ON UPDATE NO ACTION); so does Chronos now. ON UPDATE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION and ON DELETE SET DEFAULT (which were 0A000) work: a cascaded key that is part of the child's own key moves the child (and its children) too. pg_constraint.confupdtype, information_schema.referential_constraints.update_rule and pg_get_constraintdef show them. Checked against Postgres (foreign_key_actions in tests/pgdiff.rs).
  • oid_column IN ('t'::regclass) was 22P02 (invalid input syntax for type bigint: "t"); a regclass in an IN list compares as its oid, as it does after = (regclass_in_a_list in tests/pgdiff.rs).

Generated columns

  • col type GENERATED ALWAYS AS (expr) STORED was a syntax error (Django 5's GeneratedField, Rails 7's virtual and stored columns, Prisma's and Drizzle's generatedAlwaysAs, hand-written migrations, and what pg_dump writes for one). The expression is worked out from the row's other columns on every insert and update (ON CONFLICT DO UPDATE, UPDATE, COPY), and ALTER TABLE ... ADD COLUMN ... GENERATED fills the rows already there. A value written to the column is 428C9 (DEFAULT is fine); an expression reading another generated column, a subquery or a non-immutable function is refused at definition (42P17, 0A000), as are dropping or retyping a column it reads (2BP01, 0A000); renaming one rewrites the expression. ALTER COLUMN ... DROP EXPRESSION [IF EXISTS] makes it a plain column. COPY leaves generated columns out unless told to (naming one is 42P10). attgenerated, information_schema.columns.generation_expression, pg_attrdef and pg_dump (GENERATED ALWAYS AS (...) STORED) show them; LIKE ... INCLUDING GENERATED keeps them. Checked against Postgres (generated_columns in tests/pgdiff.rs, generated_columns_stay_out_of_copy in tests/copy.rs, and tests/pg_dump.rs).

CREATE TABLE ... (LIKE other ...)

  • It was a syntax error at like (Django's CREATE TABLE ... LIKE for test copies, Rails and Alembic scripts, hand-written partition-style migrations). Copies columns (of a table or a view), types, NOT NULL, and with INCLUDING DEFAULTS | CONSTRAINTS | INDEXES | IDENTITY | ALL more, wherever it stands among the other columns; several LIKEs work. Checked against Postgres (create_table_like in tests/pgdiff.rs). Not for temporary tables (0A000).
  • information_schema.columns gives character varying with character_maximum_length, character and numeric_precision / numeric_scale for varchar(n), char(n) and numeric(p, s) columns (they were text with nulls).

Function parameter defaults

  • CREATE FUNCTION f(a int, b text DEFAULT 'x', c int = 3) (and procedures) was 0A000. A call may leave out the last parameters or name the ones it gives (f(1, c => 7)); a call to a set-returning function in FROM too. Errors as Postgres's: 42P13 for a non-default parameter after a default one, 42804 for a default of the wrong type, 42725 when two functions fit a call equally. pg_get_function_arguments, pg_get_functiondef and pg_proc.pronargdefaults show them. Checked against Postgres (function_parameter_defaults in tests/pgdiff.rs).

Identity columns with options

  • GENERATED ... AS IDENTITY (START WITH 10 INCREMENT BY 5 MAXVALUE 1000 CYCLE ...) was 0A000, and it is what pg_dump writes for every identity column (with the defaults spelled out). It makes the sequence t_c_seq, owned by the column, with pg_sequence, information_schema.columns.identity_start / identity_increment / identity_maximum / identity_minimum / identity_cycle and pg_depend (i) showing it; the defaults keep using the column's own counter. Also in ALTER TABLE ... ADD COLUMN and ALTER COLUMN ... ADD GENERATED; DROP IDENTITY and dropping the column drop the sequence, as does dropping a column a sequence is OWNED BY. Checked against Postgres (identity_options in tests/pgdiff.rs).

UPDATE ... SET col = DEFAULT

  • It was a syntax error (42601: "DEFAULT is only allowed in an INSERT's VALUES"). It sets the column's default for each row: a serial or identity column takes its counter's next number, gen_random_uuid() and the clock a new value each, a plain default its value, and a column with none null (so a primary key fails with 23502). Also in ON CONFLICT DO UPDATE SET. Checked against Postgres (update_set_default in tests/pgdiff.rs).

pg_dump works

  • Postgres's own pg_dump (16) dumps a Chronos database: schemas, enum types, functions and procedures, tables (with character varying(n), numeric(p,s), defaults, checks), sequences owned by their columns, identity columns, foreign keys, indexes, views, materialized views, triggers, every row, and each sequence's setval. The script restores into Postgres with no error, and into Chronos, where a second dump of the result is the first (tests/pg_dump.rs). It read the catalog and found it empty of everything but tables, so it needed real rows for what the world holds: pg_proc, pg_type and pg_enum (enum types and their arrays), pg_trigger, pg_rewrite, pg_depend (what a sequence, a default, a view and a trigger depend on, so the script comes out in an order that restores), pg_attrdef, and pg_class and pg_attribute with their real relhastriggers, relnatts, attlen, attstorage, atttypmod, attidentity. format_type(oid, typmod), pg_get_functiondef, pg_get_function_arguments, pg_get_function_identity_arguments, pg_get_function_result, pg_get_triggerdef, pg_get_viewdef and pg_options_to_table exist.
  • A serial or identity column's counter is a sequence relation, t_id_seq: it is in pg_class, pg_sequences and information_schema.sequences, select last_value, is_called from t_id_seq reads it (as it does any sequence), and the column's default is nextval('t_id_seq'::regclass). OWNED BY is kept, and dropping a table drops the sequences it owns.
  • GENERATED ALWAYS AS IDENTITY was the same as BY DEFAULT: an explicit value went in. It is refused now (428C9, with OVERRIDING SYSTEM VALUE to allow it, OVERRIDING USER VALUE to ignore it), on INSERT and UPDATE; COPY fills it. ALTER COLUMN ... ADD GENERATED, SET GENERATED and DROP IDENTITY work, attidentity and information_schema.columns.identity_* say which. Identity options other than the defaults are 0A000.
  • ALTER SCHEMA | TYPE | FUNCTION | PROCEDURE | SEQUENCE | VIEW | MATERIALIZED VIEW | DATABASE ... OWNER TO (what pg_dump writes after every object) is accepted with a notice, and SET client_min_messages is kept and honored, so the notice isn't sent to a script that set warning.
  • Bugs it found: a column default nextval('app.seq') on a sequence in a schema counted from 1 by 1 (it took the sequence for the column's own counter, so START and INCREMENT were ignored and setval moved the wrong one); unnest(array) AS s(x) joined to a subquery returned the rows of both sides unmatched, and its column was typed text; a catalog table read inside WITH RECURSIVE was "does not exist"; a table CHECK on one column was named t_check, not t_col_check; a DEFAULT on a jsonb, array or enum column was written without its cast ({}, 'ok'::text), which Postgres refuses; a view's query was stored with a space around every token (t . id), and is now stored as written (t.id); pg_index.indkey was written [0:0]={2}, which no driver reads as an int2vector; the psql \d shim answered pg_dump's sequence, view definition and inheritance queries with empty results.

now() is fixed for the transaction

  • now(), current_timestamp, transaction_timestamp(), localtimestamp, current_time, current_date and a column's DEFAULT now() were read from the clock at every call, so select now() = now() was false, age(now(), now()) was -1 µs, and created_at and updated_at set to now() in one transaction differed. They now give the moment the transaction began (the statement's, outside BEGIN), as Postgres does. statement_timestamp() is the statement's, and clock_timestamp() still reads the clock at each call (a DEFAULT clock_timestamp() too). All four names were one function before.

What Django, Rails and Alembic emit that failed

  • INSERT INTO t DEFAULT VALUES was a syntax error at default (a model created with no fields; Django's Model.objects.create()).
  • pg_get_serial_sequence('t', 'id') returned NULL, so setval(pg_get_serial_sequence(...), max(id)), which Django's sqlsequencereset and loaddata and Rails's fixtures and reset_pk_sequence! run after loading rows with their own ids, did nothing and the next insert collided (23505). It now returns Postgres's name (public.t_id_seq); nextval, currval, setval and lastval take that name, and setval moves a serial column's counter, which it did not before: a serial column ignored the origin setval and RESTART set. lastval() also works after an insert into a serial table.
  • CREATE INDEX ... (col varchar_pattern_ops) (Django adds one to every indexed CharField), text_pattern_ops and the default operator classes, COLLATE "C", INCLUDE (cols), WITH (fillfactor = ...), TABLESPACE, and USING hash / brin (built as btrees) were syntax errors or 0A000. Another operator class or collation is still 0A000.

ALTER TABLE and ALTER INDEX as migration tools write them

  • ALTER TABLE ONLY t ... (pg_dump and Rails's structure.sql write it for every constraint), ADD CONSTRAINT ... NOT VALID (it checks the rows there are, so a table that breaks the rule fails where Postgres adds the constraint unchecked), VALIDATE CONSTRAINT, ALTER COLUMN c TYPE t USING c::t (Django's AlterField and Alembic write USING; an expression that computes something else is still 0A000), SET SCHEMA, and ALTER INDEX a RENAME TO b were syntax errors or 0A000.
  • OWNER TO, storage parameters (SET (fillfactor = 70, autovacuum_enabled = false)), SET TABLESPACE and SET LOGGED succeed and change nothing, with a notice; a restore of a pg_dump no longer stops at the first ALTER TABLE ... OWNER TO.

Catalog: every column Postgres has, pg_roles, pg_settings, the reg types, and E'...'

  • Every pg_catalog table and view Postgres 16 has now exists with all of its columns in Postgres's order (src/sql/catalog_columns.txt, made from a real Postgres 16). Chronos fills the columns it works out; the others take a value a plain object has (relpersistence p, a table's relreplident d), and a table it has nothing for (pg_operator, pg_ts_*, pg_foreign_*, pg_event_trigger, pg_publication, ...) is empty. pg_type has Postgres's own rows for the types Chronos holds (with typcategory, typalign, typstorage), pg_language (internal, c, sql, plpgsql), pg_tablespace and pg_extension (plpgsql). Each has a trailing tableoid, which pg_dump reads. Only information_schema tables keep their own shape.
  • pg_roles, pg_user and pg_authid list the one role, chronos, that owns everything; pg_settings and SHOW ALL list the settings a session can read, its own SETs included.
  • 'pg_catalog'::regnamespace, regrole, regtype, regproc, regprocedure, regoper, regoperator, regconfig, regdictionary, regcollation casts, with or without pg_catalog. (a string is read as its oid; an oid shows as its name: 23::regtype is integer), 'pg_class'::regclass and the other catalog tables' oids, and acldefault().
  • E'...' escape strings (\n, \t, \\, \', \xHH, \uXXXX, octal) were a syntax error at the string.
  • A function in FROM inside a subquery can read the outer query's columns: exists (select 1 from unnest(t.tags) x where x = 'a'), array(select ... from jsonb_array_elements(t.doc) e) failed with column "tags" does not exist.

pgbench runs

  • Postgres's own benchmark could not even make its tables: CREATE TABLE ... WITH (fillfactor = 100) was a syntax error. USING heap, WITH (storage parameters) (a notice says none is used), TABLESPACE pg_default and ON COMMIT PRESERVE ROWS are accepted after the column list in any order; INHERITS and PARTITION BY are 0A000; an unknown parameter is 22023, another tablespace or access method 42704. pg_inherits and pg_partitioned_table are catalog tables (empty: nothing inherits or is partitioned), which pgbench reads.
  • The array functions array_append, array_prepend, array_cat, array_remove, array_replace, array_position, array_positions, array_dims, array_ndims, array_fill (one dimension) and trim_array, and || on arrays (arr || arr, arr || elem, elem || arr, which joined arrays as text). pgbench's catalog check uses array_position(current_schemas(true), ...).
  • psql's \d shim answered any query that mentioned pg_catalog.pg_inherits, so a join with the table returned nothing; it now answers only psql's own queries.
  • pgbench -i, -S, and the TPC-B run in simple, extended and prepared mode work (tests/pgbench.rs, skipped where pgbench isn't installed). With several clients on the same rows Postgres waits for a row lock where Chronos, which commits by merging snapshots, fails the second commit with 40001: pgbench --max-tries=N retries it (4 clients on one branch row: 73% of transactions fail with no retries, 0.5% with 50). See Transactions.

DO, CALL and CREATE PROCEDURE

  • DO $$ ... $$ (the anonymous PL/pgSQL block hand-written migrations, Flyway scripts and IF NOT EXISTS-style guards use), CREATE [OR REPLACE] PROCEDURE, CALL name(args) (with named arguments) and DROP PROCEDURE were syntax errors. A block or procedure writes with the statement, in the session's transaction, and an error undoes it. A procedure is stored as a function returning void, so select proc() works where Postgres says 42809; INOUT parameters and transaction control inside are not supported. Diffed against Postgres in do_blocks_and_procedures (tests/pgdiff.rs).

Analytics: ROLLUP, CUBE, GROUPING SETS, ordered-set aggregates, GROUPS frames and EXCLUDE

  • GROUP BY ROLLUP (...), CUBE (...), GROUPING SETS (...) (nested, and multiplied by the other GROUP BY items), GROUP BY DISTINCT, and GROUPING(...) were syntax errors or function rollup(text) does not exist. A query with sets runs once per set, joined with UNION ALL, a column outside a set as NULL; ORDER BY after it can name a column, a position, or an expression from the select list (order by sum(qty)). Row order within ties and the GROUPING bits match Postgres; the empty set gives its row even over no rows, as in Postgres.
  • percentile_cont(f) WITHIN GROUP (ORDER BY x), percentile_disc(f) (also with an array of fractions) and mode() WITHIN GROUP (...), with FILTER.
  • Window frames GROUPS BETWEEN ... and EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS (aggregates, first_value, last_value, nth_value).
  • Each is checked against Postgres statement by statement (grouping_sets_rollup_and_cube, ordered_set_aggregates and window_frames_by_groups_and_with_exclusions in tests/pgdiff.rs).

Array slices, named arguments and other small syntax

  • a[2:3], a[:2], a[2:] and a[:] on arrays (a syntax error at :), clamped as Postgres does; a subscript after a slice is 0A000, since Postgres would read it as a second dimension. @ x, |/ x and ||/ x (absolute value, square root, cube root; also cbrt()), int4('1'), int2, int8, float4, float8, text(5), bool('t') and date('...') written as functions, make_interval(...), and x COLLATE "C" (and "default", "POSIX", "ucs_basic": text compares by bytes, so they change nothing; another collation is 0A000 rather than an order that differs from Postgres's).
  • Named arguments: make_interval(days => 1, hours => 2) and f(b => 2, a => 1) for a function made with CREATE FUNCTION, with => or := (was a syntax error at >); by position first, each parameter once, as in Postgres (42601 for a positional argument after a named one or a name used twice, 42883 for a name the function doesn't have).
  • Checked against Postgres statement by statement (array_slices_and_small_operators in tests/pgdiff.rs).

Deferrable foreign keys

  • REFERENCES t(id) DEFERRABLE INITIALLY DEFERRED, which Django writes on every foreign key, was refused with 0A000, so no Django migration that made a foreign key could run. DEFERRABLE, NOT DEFERRABLE, INITIALLY DEFERRED and INITIALLY IMMEDIATE (also on FOREIGN KEY (...) and ADD CONSTRAINT) are accepted, read as Postgres reads them (INITIALLY DEFERRED alone implies DEFERRABLE; NOT DEFERRABLE INITIALLY DEFERRED is 42601). A deferred key is checked when the transaction ends: children can come before their parents, and a row still without a parent fails the COMMIT with 23503 and the transaction is rolled back. Deleting a parent that rows still reference is put off the same way (ON DELETE RESTRICT isn't).
  • SET CONSTRAINTS ALL | name, ... DEFERRED | IMMEDIATE (IMMEDIATE checks what was put off at once; outside a transaction it warns, as Postgres does, after checking the names: 42704, and 42809 for a constraint that isn't deferrable), and ALTER TABLE ... ALTER CONSTRAINT name [NOT] DEFERRABLE [INITIALLY ...]. pg_constraint.condeferrable and condeferred, pg_get_constraintdef and information_schema.table_constraints say which. MATCH SIMPLE is accepted; MATCH FULL is 0A000.
  • Answers checked against Postgres statement by statement, savepoints and cascades included (deferrable_foreign_keys in tests/pgdiff.rs).

CREATE DATABASE and DROP DATABASE

  • Both were syntax errors, which stopped Rails's db:create, Django's test runner, Prisma's shadow database and docker-entrypoint-initdb.d scripts at their first statement. A database is a world: CREATE DATABASE app makes an empty world with no parent (nothing of main's, as Postgres copies an empty template1), and a client connects to it by name (dbname=app); TEMPLATE some_world forks that world, instantly at any size; DROP DATABASE [IF EXISTS] app [(FORCE)] drops it. Errors follow Postgres's (42P04 for a name in use, 3D000 for a missing database or template, 55006 for the open one, 25001 inside a transaction block, 42601, 22023, 42704 for bad options); options that only describe Postgres's storage are accepted and options that would change results (a locale other than C, a connection limit) are accepted with a warning that they aren't applied.
  • Tools connect to postgres or template1 to make the others: both are main unless a world is named so. pg_catalog.pg_database lists the worlds, and postgres, template0 and template1, with the columns tools read (it was answered only for psql's \l).
  • A world with no parent is new to the core, and a checkpoint left it out, so a database made before a clean shutdown was gone after it; the checkpoint now writes every tree of the world forest.

Arrays come back in binary to drivers that ask for it

  • A driver that requests binary results (the Rust postgres crate, pgx, asyncpg, JDBC with binary transfer) couldn't read any array: select array_agg(id) failed on the client with "failed to fill whole buffer" (or a driver-specific decode error), because the server sent the array's text under a binary format code. One-dimension arrays of integers, floats, booleans, text, numeric, dates and times, with nulls and empty arrays, now come back in Postgres's binary layout. Arrays were already accepted as binary parameters. (An array of arrays is still sent as text.)

The SQL every ORM emits: DISTINCT ON, keyset pagination, ARRAY(subquery), NATURAL JOIN and more

  • SELECT DISTINCT ON (a) ... ORDER BY a, b (a syntax error at (, from Django's distinct(*fields), Rails and hand-written "latest row per group" queries): the first row of each set, checked as Postgres checks it (42P10 when ORDER BY doesn't lead with the ON expressions).
  • Row comparisons (a, b) < (c, d), ROW(...) and (a, b) IN ((1, 2), (3, 4)), the keyset pagination ORMs write (where (created_at, id) < ($1, $2)), were a syntax error at ,. Nulls behave as in Postgres.
  • FETCH FIRST | NEXT n ROWS ONLY (JDBC-style and standard SQL paging; WITH TIES is 0A000); LIMIT twice, or LIMIT with FETCH, is now a syntax error as in Postgres, where the last one used to win quietly.
  • ARRAY(SELECT ...), including one that reads the query around it (select id, array(select tag from tags where post_id = posts.id)); a @> b, a <@ b and a && b on arrays, which read an array as jsonb (an error, or a wrong answer for a text array that parses as JSON); NATURAL JOIN; x IS [NOT] TRUE | FALSE | UNKNOWN.
  • ^, #, ~, << and >> (with Postgres's precedence: ~5 + 1 is ~(5 + 1)), and generate_series(...) in the select list, ::date on it included.
  • natural and fetch are reserved words now, as in Postgres: a bare column or alias of either name needs quotes. << and >> are their own symbols, so PL/pgSQL labels (<<outer>>, still refused) read as before.

VACUUM, ANALYZE, DISCARD, LOCK TABLE and the rest of what tools send

  • VACUUM, ANALYZE, REINDEX and CLUSTER were syntax errors, so Rails, Django, Alembic and Flyway migrations that end with one failed. They now succeed and do nothing (there are no dead rows to reclaim, indexes are always current, no statistics are kept), after checking their names and where they may run as Postgres does (VACUUM and REINDEX CONCURRENTLY are 25001 in a transaction block; a missing table is 42P01; a view is skipped with a warning).
  • DISCARD ALL (what pgbouncer sends between clients) puts a session back as it connected: settings, prepared statements, LISTENs, temporary tables, advisory locks, currval, and its world. Without it a pooled connection kept the last client's SET app.org_id. DISCARD PLANS, SEQUENCES and TEMP too.
  • COMMENT ON checks its object and succeeds, with a notice that the comment isn't kept.
  • LOCK TABLE in a transaction: the modes that would keep writers out make other LOCK TABLE statements wait (an advisory lock on the table's name until the transaction ends; NOWAIT, statement_timeout and deadlock detection work), and a warning says plain writers aren't held off, since no writer takes table locks here. The weaker modes only check the table.
  • CREATE EXTENSION and DROP EXTENSION take plpgsql, uuid-ossp and pgcrypto (which give what's built in: uuid_generate_v4() and gen_random_uuid(), with a notice); pg_trgm, citext and the rest are still 0A000. An agent can run CREATE EXTENSION IF NOT EXISTS vector, as pgvector clients send on connect: it needed the database's own user, though it does nothing.

What drivers, pools and admin tools ask on connect

  • version() (PostgreSQL 16.0 (Chronos ...): SQLAlchemy, Prisma and Flyway parse the leading version), current_user, session_user, current_role (the name the client logged in with, or an agent's name), current_catalog and current_schema without parentheses, pg_backend_pid(), pg_is_in_recovery(), pg_postmaster_start_time(), txid_current(), inet_server_addr(), inet_client_addr() and their ports (null), pg_sleep(seconds), pg_encoding_to_char(n), num_nulls(...), num_nonnulls(...) and array_to_string(a, sep[, null_string]) were errors (42883, or 42703 for the keywords).
  • pg_cancel_backend(pid) stops the statement another connection is running, as a cancel request does; false (with a warning) for a pid that isn't open.
  • has_table_privilege, has_column_privilege, has_schema_privilege, has_database_privilege, has_sequence_privilege, has_function_privilege, has_type_privilege and has_any_column_privilege check the object and privilege names as Postgres does (42P01, 3F000, 3D000, 22023, 42704) and answer true for a database user. An agent's session gets 0A000: its rights depend on the world, and a plain yes would be wrong.
  • A driver that sends $1 for pg_cancel_backend, pg_sleep, pg_advisory_lock and setval's value now gets the type Postgres gives it (integer, double precision, bigint), so strictly typed drivers can send an integer.
  • tests/pg_gap_probe.sh measures what's left: it runs 178 statements that drivers, ORMs and migration tools send on a real Postgres and on Chronos and counts what only Postgres accepts. The plan is in docs/designs/postgres-parity.md.

Correlated EXISTS, NOT EXISTS and IN, joins on an expression, and long IN lists are fast

  • exists (select 1 from leads l2 where l2.company = l1.company and l2.created_at > l1.created_at) read the whole inner table again for every outer row: over a 150,600-row table with 15,000 outer rows it took 120 s (the NOT EXISTS form 122 s, the same predicate in a DELETE as long). The subquery now runs once, without the conditions that read the outer row, and each outer row looks its candidates up by the = (here l2.company = l1.company) and checks the rest on them: 0.13 s. The same goes for a correlated IN. Nulls, NOT EXISTS and NOT IN answer as before. A subquery with LIMIT, grouping, an aggregate, a nested subquery or a volatile function, or too big for CHRONOS_WORK_MEM, still runs per outer row; so does one comparing an indexed column or the primary key, where each lookup is cheap, until those lookups have taken half a second in all, when it is hashed after all.
  • a join b on lower(a.email) = lower(b.email) compared every pair of rows (15,000 x 150,600: over 20 minutes); it now hashes the expression like a join on a column does (0.14 s in a subquery of IN).
  • x IN (select ...) and NOT IN with 16 rows or more in the list look each value up in a hash set instead of comparing it with every row of the list (a 15,000-row list over 150,600 rows: 14 s, now 0.2 s to 0.4 s). Only when the list's values are all integers and numeric, all floats, all text, all dates or all booleans.

ANY(ARRAY[...]::type[])

  • x = ANY(ARRAY[$1, $2]::text[]) (how most ORMs bind an IN list), and other ARRAY[...] with something after the ] (::type[], a subscript), inside ANY/SOME/ALL were a syntax error at ::. They now work.

Parameters inside WITH queries are counted

  • with a as (select $1::int as x) select x from a failed over the Postgres protocol with 08P01 "1 parameters given, but the statement takes 0": a $n only in a WITH query, a derived table (FROM (select ...)), a WITH RECURSIVE part or a function's arguments in FROM (generate_series(1, $1)) was not counted, and its type was not guessed from use. Both now cover the whole statement.

Quoted function names, and a lost unique index fails the import

  • "right"('abc', 2) and create unique index u on t (org_id, "right"(x, 10)) were a syntax error at (. A quoted, already lowercase name before ( is the function of that name, as pg_dump and pg_get_indexdef write it. Other quoted names stay columns.
  • chronos import postgres://... listed a unique index it could not create under "not imported" and still exited 0, leaving a table that accepts rows Postgres refused. It now ends with an error naming those indexes (exit 1), after the rest of the import is done and reported.

avg of integers is numeric, and pg_typeof

  • avg(int) and avg(bigint) now return numeric (exact, with Postgres's scale: 6.2297297297297297, 10.0000000000000000), as Postgres does, where they returned double precision. Drivers such as node-postgres get a string, not a rounded JS number. avg of floats is still double precision; avg of numeric was already numeric. Applies to grouped and window averages.
  • pg_typeof(x) returns the type's name (integer, numeric, double precision, timestamp with time zone, ...).

array_agg is an array, and output columns have Postgres's names

  • array_agg(x) reports an array of x's type (text[], integer[], ...) instead of text, so node-postgres and other drivers return a real array, not the string {a,b}.
  • An unnamed array_agg, string_agg, json_agg, bool_and or bool_or column is named array_agg, string_agg, jsonb_agg, bool_and, bool_or, where it was named with internal plan text.
  • select id::text from t names its column id (was ?column?), so a driver that maps rows by name finds it. A cast takes its operand's name (u.code::text is code, now()::date is now), a cast of a constant its type's (1::int is int4), and CASE, EXISTS(...) and ARRAY[...] are case, exists and array, as in Postgres. A query that sorts by such a name (select j::text ... order by j) now sorts by that output column, as Postgres does.

Constraint violations name what they broke

  • The ErrorResponse of a unique (23505), NOT NULL (23502), CHECK (23514) or foreign-key (23503) violation now carries constraint, table, schema and, for NOT NULL, column, and detail where Postgres words one (Key (k)=(a) already exists., Key (q)=(99) is not present in table "q"., Key (id)=(1) is still referenced from table "kid".). Apps that branch on err.constraint now match. The message text is unchanged for now.

A foreign key keeps ON DELETE RESTRICT

  • information_schema.referential_constraints.delete_rule, pg_constraint.confdeltype and pg_get_constraintdef reported ON DELETE RESTRICT as NO ACTION, so schema diff tools saw a change on every run after chronos import. RESTRICT is now kept apart from NO ACTION, the default. Both refuse the delete, as before. Foreign keys made before this release keep reading as NO ACTION. ON UPDATE actions still all read as NO ACTION.

varchar(n) and char(n) limits are enforced

  • A value longer than n characters written to a varchar(n), char(n) or character varying(n) column fails with 22001 (value too long for type character varying (4)), on INSERT and UPDATE; blanks past the limit are cut, as in Postgres. Before, the limit was accepted and ignored, so a database moved with chronos import lost every length rule its schema had. A bare char is char(1).
  • 'abcdefgh'::varchar(4) and CAST(... AS character varying(4)) cut to abcd.
  • Not yet: char(n) values are not blank-padded, information_schema.columns still reports these columns as text without character_maximum_length, and tables made before this release have no limit until the column is declared again.

Underscores in numbers

  • 1_000, 1_000.000_5 and 1e1_0 read as one number, as in Postgres 16+. 1_000 was read as 1 AS _000: the query succeeded with the wrong value. A trailing or doubled underscore (1_, 1__0) is now a syntax error, not an alias.

SELECT ... FOR UPDATE in transactions, and SERIALIZABLE refused instead of ignored

  • SELECT ... FOR UPDATE / NO KEY UPDATE / SHARE / KEY SHARE, with OF and NOWAIT, lock the rows they read until the transaction commits. The commit fails with 40001 if another commit changed one of those rows, or locked one FOR UPDATE, since the transaction began. Of two transactions that each lock a unit, find it free and hold it, one commits and the other fails and retries, where before both holds committed. Locks work in PL/pgSQL and SQL functions too. They were a syntax error before. SKIP LOCKED is 0A000.
  • BEGIN / START TRANSACTION / SET TRANSACTION / SET SESSION CHARACTERISTICS with ISOLATION LEVEL SERIALIZABLE, and SET default_transaction_isolation = 'serializable', fail with 0A000. They were accepted and silently gave snapshot isolation, which lets write skew through.

SET TIME ZONE takes effect

  • SET TIME ZONE 'Asia/Kolkata' (and SET timezone, INTERVAL '+05:30', numbers, LOCAL/DEFAULT, RESET, SHOW timezone, and the TimeZone connection parameter) used to return SET and change nothing, so now() printed in UTC and current_date was UTC's date: 5.5 hours off in India with no error. Now timestamptz prints and reads in the session's zone, current_date is its date, and casts to date/timestamp/time, date comparisons, date_trunc, extract and to_char work in its clock. Postgres clients get a TimeZone ParameterStatus when it changes.
  • Only zones with one offset all year (UTC, fixed offsets, Etc/GMT±n, and zones such as Asia/Kolkata, Asia/Dubai, Asia/Singapore); a zone with daylight saving time fails with 22023 rather than getting the wrong time for half the year.

IS DISTINCT FROM, advisory locks and hashtext

  • x IS [NOT] DISTINCT FROM y: <> and = where null is a value like any other, in queries, parameters and PL/pgSQL (a ledger trigger's NEW.amount IS DISTINCT FROM OLD.amount). It was a syntax error.
  • pg_advisory_lock, pg_advisory_xact_lock, their try kinds, pg_advisory_unlock[_all], and hashtext (Postgres's own hash). A lock another session holds waits, within the statement timeout, and a cycle of waits fails with 40P01. A transaction that waited for a lock released by another's commit reads the database anew from its next statement, so it sees what that one committed; one that already wrote fails with 40001. Shared advisory locks are 0A000.

VALUES lists as queries, SHOW and current_setting() of Postgres's settings, custom settings

  • (VALUES (1, 'a'), (2, 'b')) AS t(id, name) in FROM, in IN (...), in WITH, and VALUES ... on its own (with ORDER BY and LIMIT); columns column1, column2, ... unless named. They were syntax errors.
  • SHOW server_version, SHOW transaction isolation level, current_setting('TimeZone') and the other common Postgres settings answer with what Chronos does (repeatable read, lc_collate C, ...); they were syntax errors. Unknown names are 42704.
  • Custom settings, as row-level filters use them: SET app.org_id = '42', SET LOCAL, set_config(name, value, is_local), RESET, RESET ALL, read with current_setting(); and application_name, from SET or the connection.

Vector columns of 1M rows in 60% less memory, with the same answers

  • A vector column keeps its numbers as floats while it holds at most 64M of them (256 MB), and at 8 bits a number above that, each vector with its own scale: at 1M real embeddings (1,536 dimensions) the server holds 3.1 GB after the run, from 7.7 GB, and peaks at 7.7 GB building the index, from 9.1 GB. The index builds 2.3 times faster (58.7 s, from 133.5 s).
  • 8-bit numbers alone can't tell near-duplicates apart (94% of the true top 10 at 1M), so such an index proposes twice the rows asked for and ranks them from the rows' own vectors, read on the free cores: 1.34 ms at 98.6% recall@10, against 1.32 ms at 98.7% with floats; one user 1.19 ms at 100%. SQL and find (HTTP, MCP) both rank that way, and their scores stay exact.
  • CHRONOS_VECTOR_FLOATS sets the limit (0: every column 8-bit), CHRONOS_RERANK the rows proposed per row asked for.
Chronos DB 0.2.0 · Chronos