Chronos DB 0.2.0
The first public beta: agents' changes to your Postgres data, reviewed and checked before they land
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 opmerge_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, andrules_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 serveanswers MCP atPOST /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 importor apg_dumpreplay, 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 COMMITTEDis the default andREPEATABLE READis there, row locks wait (FOR UPDATE,SKIP LOCKED,NOWAIT),READ ONLYis enforced, andSERIALIZABLEis 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,
jsonas its own type. - Reliability fixes from the trials: sequences never repeat a value after a crash,
max_connectionsis 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 serveon an address other machines can reach, without--safeand withprotect_mainoff, warns that its token changesmaindirectly, with no world and no review; the hardened deployment checklist now says to keep server and admin tokens out of agents' environments and to turnprotect_mainon.
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 (
RebaseTxfor READ COMMITTED statements,Rebasefor 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 TOandSET SCHEMArewrite every row (about 6.5 s per million rows); C-language functions and extensions, custom access methods andEXPLAIN's plan shapes are not there; function signatures withvarcharshowtext. 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, andpg_stat_activity'swait_event_type/wait_eventsayLock/transactionid(a row) orLock/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
agentsop'sneeds_http_login).
Studio and the HTTP API (a click-through of Studio)
- API change:
numericvalues insqlresults 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 hastypes, its columns' Postgres type names. See values in sql results. real(float4) values come with float4's shortest digits (0.1) insqlresults and in the rows ofget,findanddiff; they had a double's (0.10000000149011612). What is stored is unchanged.- History and the audit trail count a write's table rows: an
UPDATEof 6,666 rows said 19,998, counting the entries behind its table's indexes and constraints. Events already in the log from big statements (pastCHRONOS_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).fin a LATERAL or other subquery, for an outer row whose composite column is NULL, is null as in Postgres; it failed with 42809column notation .f applied to type text.chronos importcreates a materialized view Postgres holds unpopulated (WITH NO DATA, such as Pagila'srental_by_category) unpopulated: reading it is 55000 untilREFRESH MATERIALIZED VIEW, as in Postgres. It was filled.- Docs: what the search index behind
column = valueon 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 serverefuses to start with a server or admin token shorter than 24 characters, or withchange-me(the placeholder the examples used): make one withopenssl rand -hex 24and pass it asCHRONOS_TOKEN, not--token, which other users see inps. 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-certand--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 on127.0.0.1only. - 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, HTTP429).CHRONOS_LOGIN_FREE_TRIESchanges 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 forpasswordormd5is refused unless the connection is TLS (the defaultsslmode=prefertakes TLS where offered) or--allow-plaintext-passwordis given; SCRAM-SHA-256 is unaffected. It now gives up connecting afterconnect_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 ofpg_read_server_filesandCOPY ... TO '/path'those ofpg_write_server_files(42501 permission denied to COPY from a file, as Postgres says it);lo_import/lo_exporta superuser; a file_fdw table'sfilenamepg_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,simulateandreplayHTTP ops, a session opened on a_tx_…world by name (orname@when), andUSE WORLDof 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-storeand, 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,UPDATEorDELETEof millions of rows insideBEGIN ... COMMIT(pg_restore -1,psql --single-transaction,pgbench -i, Rails and Django migrations), andCREATE TABLE AS, spilled as it does outside a transaction, but itsCOMMITthen 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 byCOPY.COMMITnow 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 aREAD COMMITTEDstatement 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 itsCOMMIT, 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_connectionsgets 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 COLUMN16 s,DROP COLUMN24 s). Now only what the change touches is written:RENAME COLUMN,ADD COLUMN(a constant,now(),current_dateorcurrent_userdefault 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, andALTER COLUMN TYPEto a type that holds what the column does (varchar(n)totextor a longervarchar,numeric(p,s)to a wider one,integertobigintwith nothing indexed on it) write the table's schema and nothing else.ADD FOREIGN KEYandCREATE INDEXread 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()(orcurrent_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 (
nextval80129 after a restart, with 80147 already handed out). Gaps after a crash remain, as in Postgres; repeats do not. max_connectionsis 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 get53300 sorry, too many clients alreadyinstead of being dropped, andSHOW max_connectionssays 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 FATAL08P01 invalid message length. idle_in_transaction_session_timeout(FATAL25P03) andidle_session_timeout(FATAL57P05) 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.partialfolders 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 LOCKEDworkers claimed rows twice and failed atCOMMIT(25 duplicate claims in 200 jobs, 5 crashed workers), anUPDATEof a row another transaction had changed failed, and pgbench's TPC-B at 8 clients lost 19% of its transactions to 40001.READ COMMITTEDis the default now, andREPEATABLE READ(BEGIN ISOLATION LEVEL ...,SET TRANSACTION ISOLATION LEVEL ...,default_transaction_isolation) keeps one snapshot:SHOW transaction_isolationsays which. - Each statement of a
READ COMMITTEDtransaction reads what was committed before it began. AnUPDATE,DELETE,MERGE,INSERT ... ON CONFLICT DO UPDATEorSELECT ... FOR UPDATEtakes 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 theWHERE; atREPEATABLE READsuch a write is 40001 at the statement, as Postgres's. Row locks ofFOR UPDATE,FOR SHAREand the others wait instead of failing the laterCOMMIT;SKIP LOCKEDandNOWAITsee what other sessions hold; aROLLBACK TOlets 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 (
numerichas 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) andlength(),::text,||andstring_aggdrop 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.realis written with its float4 digits in rows, arrays and JSON (9.8, not9.800000190734863);realbeside 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_tsvectorandts_parsecut 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 importcarries 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 IDENTITYwith 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 ONan enum type or a function is kept (pg_description, obj_description). A sequence keeps itsCACHE(pg_sequences.cache_size).ntilegives an integer. A CHECK on a function call is written back asCHECK ((char_length(title) >= 1)), not with spaces between tokens.server_versionsays 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 byCREATE 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 42804cannot return non-composite value from function returning composite type. SELECT ... FOR UPDATE [NOWAIT | SKIP LOCKED]:SKIP LOCKEDleaves out rows another open transaction holds locked or has written, beforeLIMITcounts, andNOWAITis 55P03 on one, so theFOR UPDATE SKIP LOCKED LIMIT 1job queue works, in a subquery or aWITHquery too. A table without a primary key can be locked (tests/row_locks.rs).tsvector_update_triggerandtsvector_update_trigger_columnare built in, andCREATE INDEX ... USING giston a tsvector or tsquery column is accepted (tests/pgdiff.rs).GROUP BYof a composite's field ((v).a, or1for 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 avarchar 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 comeSET SESSION CHARACTERISTICS AS TRANSACTION READ ONLYanddefault_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 25006cannot execute INSERT in a read-only transaction. Rows of temporary tables may change.SET TRANSACTION READ WRITEafter 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)::textin 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.*andr.*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. jsonis 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, assignmenta[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), inCOLLATEand inCREATE 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. MERGEinto a table without a primary key: its rows are matched by all their columns.MERGEfollows 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.MERGEin a PL/pgSQL function or procedure, its variables andNEW.ffields in the ON, WHEN and actions.WITH ... VALUES (...),WITH ... TABLE tandWITH ... (query)(WITH went with SELECT, INSERT, UPDATE, DELETE and MERGE only).CREATE [OR REPLACE] RECURSIVE VIEW v (cols) AS ..., which isCREATE 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 theCREATE 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.ganda[1].fread 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,viewsandcolumnssay 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 = offwhere a policy applies is 42501 before any permission denial (Postgres's rewriter runs first);CREATE VIEW ... WITH (security_invoker)andALTER 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_visibleis 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: ...),\u0000refused (22P05), nesting past 1000 levels 54001; columns named by the function called (jsonb_set,to_jsonb) and?column?for@?and#-; named arguments forjsonb_path_*,jsonb_set,jsonb_set_laxandjsonb_insert;jsonb_contains,jsonb_exists,jsonb_concat,jsonb_deleteand 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)andjson[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 AGGREGATEin every form Postgres's tests write: the old(BASETYPE = t, SFUNC = f, STYPE = t)andsfunc1/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 takecstringorinternal(type I/O) aren't, norWINDOW.- 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_charfollows 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_binwith 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 bputsainb(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 ASwithUSING heaporWITH (...)before AS,PREPARE TRANSACTION(refused as Postgres refuses it with max_prepared_transactions = 0),ON DELETE SET NULL (cols)andSET 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,nameas a key type.createandgrantare 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_hashlost 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]]andARRAY[ARRAY[1, 2], ARRAY[3, 4]]make an array of two dimensions (they were a syntax error). Reading one by two subscripts, andarray_dimsof 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'sWITH OPTIONSisn't taken yet).pg_tablespace_location(oid)(what psql's\dbcalls):'', as for the default tablespaces.
The last three extensions the migration probe refused
pg_stat_statements:CREATE EXTENSIONsucceeds, 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)andcrosstab(source, categories)with a column definition list, andnormal_rand. (connectbyisn't.)
Set operations, count(DISTINCT) and PARTITION BY in a nondeterministic collation, and of citext
UNION,INTERSECTandEXCEPT,count(DISTINCT x)and a window'sPARTITION BYgo by the key the strings are equal by. Still refused: another aggregate'sDISTINCT(string_agg(DISTINCT x, ',')),INof 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. AnINSERT,UPDATE,DELETE,COPY,TRUNCATE, HTTPputor a transaction'sCOMMITthat changes rows ofmainis 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 reachesmain.trial.pychecks 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 nmerges 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, HTTPdiffandmergedry runs returnversion, MCPmergeand the CLI'smerge w at ntake 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 withTARGET nfrom 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; HTTPmergereturns 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_hourandreview_channels. A merge that changed one row and queued a thousand emails went through undermax_rows = 1. A merge that would take back or change an effect its parent already queued always waits for a person. ACK OUTBOXneeded onlywrite_main, so an agent allowed to merge intomaincould delete the effects its merges queued before any worker did them. Working the outbox takes the newoutboxright (oradmin).- 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 nqueues 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 effectambiguous(the worker may have called the receiver): it stays inSHOW OUTBOXand no claim returns it until someone checks and acknowledges or retries it.SHOW OUTBOXshowsstate,attempts,workerandlease_until. - The stale-read check (
check_reads) recorded a scan of a whole table with aWHEREas reading the whole table, so on a busy table nearly every merge was held. Such a scan now counts as reading the rows itscolumn op constantconditions can match: other rows may change. REBASE WORLD w [USING OURS|THEIRS] [BY COLUMNS] [RESOLVE (...)] [AT VERSION n](and MCPrebase): 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 WORLDto 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
EXCLUDEconstraints:exclude using gist (room with =, during with &&), withWHERE, inCREATE TABLEandALTER 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,minandmaxofmoney.count(t.*)counts the rows wherethas one, as after aLEFT 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 DEFERREDis refused.COMMITin a PL/pgSQL procedure. ACALLis one statement here and stores its work together when it ends, so a procedure that fails after itsCOMMITkeeps nothing, where Postgres keeps what it committed. In a transaction block it is 2D000, as in Postgres.ROLLBACKin one is still refused.(f()).fieldof a function with severalOUTparameters;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.tsvraised).
Lint
cargo fmtover the whole tree, and clippy clean (with and withouts3) with warnings as errors, which ci'slintjob asks for andmaindidn't pass. Seven always-false conditions left in place were taken out (&& falseingeom.rs,money.rs,rules.rs,userdefs.rs; a self-assignment intsearch.rs): what they did is unchanged.
GROUP BY and DISTINCT in a nondeterministic collation, and of citext
GROUP BY,DISTINCTandDISTINCT ONof 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)andPARTITION BYare still refused.
Drivers and ORMs
tests/driversand thedriversworkflow: 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 againstchronos serve. Nightly, on request, and on pull requests that change them. All seven pass.
After a review of the changes below
COPY ... FROM ... WHEREcounted a row as left out when its WHERE failed (WHERE 10 / v > 0with a zero): the COPY fails now, and stores nothing.COPY (INSERT ... RETURNING ...) TOwithFORCE_QUOTEof 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
nextvalfor some rows) was refused (54000). Its parts are kept from the start, up toCHRONOS_WORK_MEM, to be stored again. - A key-value
putto 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, andgetanddeletedidn'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,Infinityand-Infinityare 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) byCREATE TABLE, where every INSERT failed.ADD GENERATED ... AS IDENTITYis 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 ...andNULLIF(x, y)in a nondeterministic collation workedxout once for each comparison:nextvalin it advanced more than once. Once now.- In PL/pgSQL,
NEW IS NULLis true when every field is null andNEW IS NOT NULLwhen none is, as a row is in Postgres (they went by whether the record held a row). hstore,ltreeandlqueryparameters 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_RELIDis anoid.- 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;
citextas 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
gatejob passes on the tagged commit: the crash suite,kill -9under concurrent merges, the simulated interleavings, and the fuzz tests at 100,000 rounds. tests/regress_ratchet.shandtests/regress_floor.tsv: no Postgres regression file may match fewer of its expected lines than it has reached. Theregressworkflow 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
hstoreandltreewere types that don't exist, andCREATE EXTENSION hstoreandltreeerrors, 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_typeandltree_type).- hstore: its text read and written as Postgres does;
->,||,?,?|,?&,@>,<@,-,h['key'];hstore(...),akeys,avals,skeys,svals,each,exist,defined,delete,slice,hstore_to_jsonand its kin, and a cast tojsonb. Aginorgistindex on one is made and holds no entries, as on ajsonb. - ltree and lquery:
@>,<@,~,?,||, the comparisons, andnlevel,subpath,subltree,index,lca,text2ltree,ltree2text. Paths order label by label, as Postgres orders them. Agistindex 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'sWHEN, a policy, SQL and PL/pgSQL functions, and prepared statements withhstoreorltreeparameters. - 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 nread an index in its order for amoney, atime with time zoneor a range column, whose index is in their text's order: the first rows could be the wrong ones. Those sort now.GROUP BYan operator's result on amoney,bit,time with time zoneor 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)andpopulate_record,(each(h)).keyin a select list. - The migration probe: 15 of 181 statements refused, from 77. What the rest wait for:
EXCLUDEconstraints, an enum primary key,COMMITin a procedure, a deferrableUNIQUE,sum(money),count(t.*), a field of a function's composite result, and thetablefunc,intarrayandpg_stat_statementsextensions.
citext
citextwas a type that doesn't exist, andCREATE EXTENSION citextan error, so every schema with a case-insensitive email or username column stopped there. It is built in now: text in the collationcitext, equal and sorted by its lower case. Acitextcolumn, cast and domain;=,<>,<,IN,CASE, joins,ORDER BY,minandmax; unique constraints andON CONFLICT; andLIKE, 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_typeandinformation_schema.columnssaycitext. - 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 andreplace,strpos,split_partandtranslatewithout regard to case, andpg_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.hstoreandltreeare 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'sON,ORDER BY,minandmax, and unique constraints, unique indexes andON CONFLICTon 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.LIKEis refused as Postgres 16 refuses it. See Nondeterministic collations. pg_collation.collisdeterministicsays 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 keepsto_jsonb(OLD)andto_jsonb(NEW), works and keeps what Postgres keeps. CREATE INDEX ... USING ginon ajsonbor 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_opsandarray_opsare accepted.CREATE EXTENSION btree_ginandbtree_gistsucceed: 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 KEYand 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 withON CONFLICTand 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), fortests/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 ... FROMinto a table whose rows take a sequence's value as they are stored (a trigger callingnextval, a function it calls) saidCOPY nand 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 toCHRONOS_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') * 10get 10, 20 and 30, as in Postgres, where they got 30, 40 and 50.
Column defaults worked out for each row
- A
DEFAULTthat 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: inCREATE TABLE,ALTER COLUMN ... SET DEFAULT,LIKE ... INCLUDING DEFAULTS, inherited columns,INSERT,COPY, andUPDATE ... SET col = DEFAULT. So a dump with such defaults restores, andchronos 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 COLUMNwith 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 STDOUTsends the rows the statement returns, in any of COPY's formats. It was refused ("COPY (query) TO takes a SELECT").- Without a
RETURNINGit 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-statementDO INSTEADrules, a rule that runs a utility command, and aRETURNINGunder anINSTEADrule 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.pyranks what breaks a run of Postgres's regression files by statements lost, charging the ones that fail after a refusedCREATE(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 (42501permission denied to change default privilegesotherwise), with Postgres's refusals (IN SCHEMAwithON 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_aclshows them, psql's\ddplists them, andpg_dumpwrites them, which restore.- What a role makes starts with them: tables, views, materialized views,
CREATE TABLE ASandSELECT INTO, partitions, sequences, serial and identity columns' sequences (oneALTER TABLEadds: its table owner's), functions, procedures, types, domains and schemas (CREATE SCHEMA ... AUTHORIZATION's owner's), sorelacl,proacl,typacl,nspacland thehas_*_privilegefunctions show them. They are rows of the world: they fork, merge and come back withRESTORE.DROP ROLEof a role that has them or is named in them is 2BP01 with Postgres's detail,DROP OWNEDdrops a role's and takes it out of others',REASSIGN OWNEDleaves them andDROP SCHEMAdrops the schema's. GRANTandREVOKE ... ON TYPE | DOMAINwere refused (0A000); they work now, andUSAGEon a type made withCREATE TYPEorCREATE DOMAINis checked where Postgres checks it: a column's type inCREATE TABLE(with thoseLIKE,PARTITION OFandINHERITScopy) andALTER TABLE ... ADD COLUMN | ALTER COLUMN TYPE, the columns of views, materialized views andCREATE TABLE AS, a domain's base type, a function's argument and result types (42501permission denied for type s.mood).pg_type.typownerwas always the bootstrap superuser for enums and domains: it is their owner now, andtypacltheir ACL;has_type_privilegeanswers from them (it said true).pg_read_all_dataandpg_write_all_datagave 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)andmakeaclitem(grantee, grantor, privileges, grantable)work, and a::regroleof 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 TOwas a notice that changed nothing andALTER DOMAIN ... OWNER TOa syntax error (42601): both hand the type over with its grants now, so a dump's type owners restore.- A
GRANTorREVOKEthat gives or takes nothing (its warning) writes the object's ACL out, as Postgres does;'s._mood'::regtypeandhas_type_privilegetake 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 nextCREATE/ALTER/DROP POLICY,ENABLE/DISABLE/FORCEor role change at its next run, as Postgres plans again (row_security_prepared_statements_see_policy_changes, tests/rls.rsa_prepared_statement_is_rewritten_once). Release build, point reads by primary key withpgbench -M prepared, the server's CPU time per read: a plain table 32 µs, one withusing (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), andCASCADEdrops 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 SCHEMAlists 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, withCASCADE, 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 itsLEFT JOIN'sON) and the query's conditions on it can't show its values (comparisons of columns, parameters and constants that cast none of its values), keepingFOR UPDATE OF,tableoidand the policies' place before conditions that could (row_security_joins_keep_their_policies_first, tests/rls.rsjoined_tables_with_policies_keep_their_index_reads).
regclass, regtype and the other reg types are oids of their own
regclasswas text (its relation's name) andregtype,regnamespace,regrole,regoper,regoperator,regconfig,regdictionaryandregcollationbigints: 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/maxgive 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), andto_regclass,to_regtype,to_regnamespace,to_regrole,to_regcollation,to_regoper,to_regoperator,to_regprocandto_regproceduregive 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, asregproc[]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'sget,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_typeofgives a regtype (pg_typeof(x) = 'int4'::regtypeandformat_type(pg_typeof(x), null)work),pg_sequences.data_typeis a regtype,pg_prepared_statements.parameter_typesandresult_typesareregtype[]of the types' oids (where they held names, so'integer'::regtype = any (parameter_types)andparameter_types::oid[]were wrong),pg_partition_tree,pg_partition_ancestorsandpg_partition_rootgive regclasses, andpg_typehas 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 (
int8getters fail on them: read them as an oid, or cast to::textor::oid);pg_typeof(...)andpg_sequences.data_typeare regtypes (2206), andpg_partition_tree's andpg_partition_ancestors'relidregclasses, 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,tableoidand 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=requireworks), where it sent the token in the clear:CREATE AGENT(and HTTPcreate_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.rsagents_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'sscram_mock_saltmakes it, for the database's own user, names nobody has, and roles without a password or pastVALID 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_iterationssets the iteration count of the verifiers new passwords and new agents' tokens get, where it was always 4096 (tests/pgdiff.rsscram_iterations_counts_a_new_verifiers_iterations). Hashing stops for statement_timeout, a cancel request andpg_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 importbinds its SCRAM login to the server's certificate over TLS (SCRAM-SHA-256-PLUS), as libpq'schannel_binding=preferdoes, 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=requireanddisablein the URL work as in libpq.
oid and smallint are types of their own
oidwas 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,0x10hex), wraps an int4 and refuses a negative int8 or anything past 32 bits (22003), compares unsigned, has no arithmetic,sumoravg(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 typeoid,min/max,tableoid,regclass::oid, the catalog's oid columns and functions' oid parameters (which take a regproc, as Postgres converts one) are oids.smallintreached clients asint4: 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 (modtoo);sumis a bigint,avga 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 ASand views keep smallint columns; and a function of a smallint parameter is the exact match among overloads (f(1::smallint)betweenf(int)andf(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 rangethere and when written to an oid column.oid[]andint2[]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
oidcolumns asu32andsmallintones asi16(int4/int8getters fail on them). Anoidcolumn of a database made before this stays the bigint it was made as, and asmallserialcolumn or aCREATE TABLE AScolumn of a smallint expression made before stays an integer. A callf(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 ... PASSWORDlogs 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'spg_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'schannel_binding=requirework; 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-tokenstill takes any cleartext password. Login refusals are FATAL, as in Postgres. chronos importlogs in to a server that asks for MD5.- Agents' and the guest's sessions can't read
pg_authid.rolpasswordorpg_shadow.passwd(42501), which let an agent answer a superuser role's MD5 challenge with its hash. A role past itsVALID UNTILgoes 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_proclists Postgres 16's 3,286 built-in functions (pronamespace11) with Postgres's oids, kinds, argument and result types, flags,prosrcandproacl, where it listed only the functions made here: a catalog's regproc column (typinput,amhandler) joins topg_proc.oid, psql's\df pg_catalog.*,\df+and\sf nowlist and show them, andpg_get_function_arguments,pg_get_function_identity_arguments,pg_get_function_resultandpg_get_functiondefgive Postgres's text for them.pg_init_privshas 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 readspg_proc(src/sql/builtin_procs.tsv, extended from a real Postgres 16).regproc[]andregprocedure[]were refused as types (0A000), andARRAY[...]andarray_aggof regprocs weretext[]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 fromoid[]andtext[], 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_procby its oid or name (where oid = 1299, a correlatedp.oid = t.f, pg_dump's per-function queries) reads that row alone, and the built-in rows are made once;pg_function_is_visiblefollows 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_typehas each table's, view's and materialized view's row type and its array type, whichpg_class.reltypenamed though no row had it;pg_attributelists the system columns, a sequence's columns and an index's (expressions asexpr,expr1, ...); a sequence'srelpages/reltuplesare 1, its and a view'srelreplidentisn, and a view's and a partitioned table'srelfilenodeis 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, andALTER TABLE ... SET SCHEMAinto a schema whose name has/is refused (42601), asCREATE TABLErefuses such a name. Before, a table moved into schema"a/b"had its policies read as tablea's (a permissive one leta's rows through) and its rows read asa's (tests/rls.rspolicies_stay_with_their_table_whatever_the_names). A policy withoutUSING(orWITH CHECK, forINSERT) lets nothing through, as in Postgres (row_security_clauseless_policies_let_nothing_through). ALTER TABLE ... ENABLE | DISABLE | FORCE | NO FORCE ROW LEVEL SECURITYandCREATE,ALTERandDROP POLICYwere refused (0A000, 42601); they work now as in Postgres 16, withAS PERMISSIVE | RESTRICTIVE,FOR ALL | SELECT | INSERT | UPDATE | DELETE,TOroles,PUBLIC,CURRENT_USERandSESSION_USER,USINGandWITH 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) andDROP OWNEDtakes it out of the policies; a renamed table keeps its policies and a dropped one drops them (tests/pgdiff.rsrow_security_policies_are_created_altered_and_dropped,tests/rls.rs).- Policies apply to every role but superusers,
BYPASSRLSroles and the table's owner (unlessFORCE), wherever rows are read or written:SELECTthrough joins, subqueries,WITH, views (as their owner) and functions,UPDATEandDELETE(rows the policies hide are passed by),INSERTandUPDATE(a new row they refuse is 42501new row violates row-level security policy for table "t", a restrictive policy's with its name),RETURNING,INSERT ... ON CONFLICTandMERGEwith Postgres 16's checks and messages,SELECT ... FOR UPDATE,COPY ... TO;COPY ... FROMinto 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')andcurrent_usergive the session's, andSET row_security = offturns 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; twoLOGINtenants over real connections intests/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
DIFFandDIFF ... AS SQL, andRESTOREputs them back.pg_class.relrowsecurityandrelforcerowsecurity,pg_tables.rowsecurity,pg_policyandpg_policiesshow them, psql's\dand\dplist them, andpg_dumpwrites 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
EXECUTErunsMERGEtoo. - Row-level security reaches every read:
WITH RECURSIVE, a FROM function's arguments, the left side ofIN,LIMIT/OFFSET/DISTINCT ON, PL/pgSQL's expressions,UPDATE ... FROMandDELETE ... USING(which took theSELECTpolicies always and the command's never), statements run again in one statement by another role's function, and readsAS OFa 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'ssearch_pathor temporary tables); renaming a column renames it in policies, and dropping one a policy reads is 2BP01 (CASCADEdrops 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 OFandtableoidwork through a joined table's policies;REFRESH MATERIALIZED VIEWreads as the owner;ADD COLUMN ... DEFAULT current_userworks 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(orcurrent_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 withcurrent_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) andDROP ROLEwere 42601 (andSET ROLEandSET SESSION AUTHORIZATIONdid 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), andDROP ROLErefused 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.rsroles_are_created_altered_and_dropped,createrole_needs_admin_option_and_attributes_it_has).GRANT role TO roleandREVOKEwith Postgres 16'sADMIN,INHERITandSEToptions and grantors, circular grants refused (0LP01);SET [LOCAL] ROLEandSET SESSION AUTHORIZATIONchangecurrent_userandsession_user, are undone byROLLBACKand refused (42501) for a role the session user may not set;pg_has_roleanswers from them (role_membership_and_set_role).GRANT/REVOKEof privileges on tables, columns, views, sequences, schemas, functions, procedures and the database (andALL ... IN SCHEMA), with grant options,GRANTED BYandCASCADE; owners (ALTER ... OWNER TO,REASSIGN OWNED,DROP OWNED);relacl,attacl,nspacl,proaclanddataclwritten as Postgres writes them,acldefault,pg_auth_members, andhas_*_privilegeanswering 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_dumpwrites them and they restore.- The checks:
SELECT(by column, through subqueries and joins),INSERT,UPDATE,DELETE(andSELECTon what theirWHEREandRETURNINGread),TRUNCATE,COPY,nextval/currval/setval(and a serial column's default),EXECUTE,USAGEandCREATEon schemas, ownership forDROPandALTER,CONNECTandCONNECTION LIMITat login, each 42501 with Postgres's message. A view reads its tables with its owner's privileges, aSECURITY DEFINERfunction runs as its owner (ALTER FUNCTION ... SECURITY DEFINERtoo) (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 ROLEis a superuser, as every login was, and what it makes is owned bychronos. A role logging in gets its own privileges (NOLOGINis 28000, an expiredVALID UNTIL28P01). Agents keep their capabilities, and a role of an agent's name adds its privileges on top.ALTER ... OWNER TOnames a role now (postgresand the login's own name meanchronos), where any name was accepted with a notice.CREATE ROLEandRENAME TOrefuse (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_backendandpg_terminate_backendfollow Postgres 16's rules for a role that isn't a superuser (a superuser's connection is 42501; another role's needs its privileges orpg_signal_backend's).MERGEchecksSELECTon the target's columns itsON,WHENconditions andSETread;CREATE TABLE ASchecks its query before the schema;has_*_privilegegives null for an oid that names no object, and true for the catalog's tables (butpg_authidandpg_shadow); psql's\dn+shows schemas' owners, privileges and comments, and\dteach 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 OWNEDtakes what the role granted on from its grant options, andALTER ROLE ... SETsettings apply when the role logs in.CREATE MATERIALIZED VIEWchecks its query (andREFRESHreads it as the view's owner),CREATE TRIGGERneedsEXECUTEon its function, andUSAGErevoked onpublicis enforced.- Follow-up:
CREATE EXTENSION pgcryptoneedsCREATEon the database and its maker owns it (DROPandALTER EXTENSIONneed that owner, with Postgres's messages, where they needed the database's owner);DROP ROLElists, 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 LIMITcounts a role's connections from before a rename;REASSIGN OWNEDgives a table's column grants to the new owner; and revoking one of a member's twoADMIN OPTIONgrants keeps the grants it made (tests/roles.rstypes_extensions_and_set_schema_say_what_postgres_says,connection_limit_counts_a_renamed_roles_sessions;tests/pgdiff.rsreassign_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 withoutCOLLATEhas the default), and so areALTER TABLE ... INHERIT,ATTACH PARTITIONand a parent'sADD COLUMN(child table "c" has different collation for column "s"). ACOLLATEin aPARTITION OFcolumn list, a syntax error before, is taken and the parent's collation kept, as Postgres does (inherited_columns_collation_conflictsin 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 ywith two different explicit collations ran; it is 42P21, as ILIKE was.a LIKE bwhere 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 ... CASCADEthat 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
COLLATEother thanC,POSIXordefaultwas 0A000, andCREATE,ALTERandDROP COLLATIONwere 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 (andFROM other,IF NOT EXISTS; libc'sCandPOSIX), andALTER COLLATION ... RENAME TO,SET SCHEMA,OWNER TOandREFRESH VERSIONandDROP COLLATION [IF EXISTS] ... [CASCADE](2BP01 while a column, index or view uses one) work as in Postgres. Sorting is ICU4X's (icu_collatorand 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_textin tests/pgdiff.rs), but for ideographs outside the Basic Multilingual Plane.- A column takes
COLLATEinCREATE TABLE,ADD COLUMNandALTER 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),upperandlower(ICU's case mapping:ßisSS, Turkish's dotted i) follow it. Collations meet as Postgres derives them: an explicitCOLLATEbeats 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 andCREATE TABLE ASkeep their columns' collations. Equality,GROUP BYand 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 ... LIMITand 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,DIFFshows it (create collation, andAS SQLwrites 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;collversionisicu4x 2.3.1),pg_attribute.attcollation,pg_index.indcollation,information_schema.columns.collation_name,regcollation,pg_collation_actual_version(), psql's\dCollation column,pg_get_indexdef, andpg_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 keywordskrandkb,initcap(), text in an ICU collation ordered against a subquery's rows orANY (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:
digestandhmac(md5, sha1, sha224, sha256, sha384, sha512, ripemd160),cryptandgen_salt(bcrypt, MD5, extended and traditional DES, the hash Postgres computes for the same salt),gen_random_bytes,encrypt,decrypt,encrypt_ivanddecrypt_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'spgp_sym_encryptandpgp_sym_decrypt(and their_byteaforms) 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,dearmorandpgp_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 pgcryptogave onlygen_random_uuid(), with a notice. - The functions are built in, in every database, as pg_trgm's are.
CREATE EXTENSION pgcryptorecords the extension in the world, sopg_extensionlists 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 secondCREATE EXTENSIONis 42710 and aDROP EXTENSIONof one not made 42704. Being a row of the world,CREATEandDROP EXTENSION pgcryptocount as writes in the MCP server's safe mode. - Differences are in docs/postgres-compatibility.md: among them,
bf,cast5anddeswork here as with OpenSSL's legacy provider, where Postgres on OpenSSL 3 without it refuses them inencryptand writespgp_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 callingpublic.digest(...)keeps working afterDROP EXTENSIONorDROP SCHEMA ... CASCADE, and a query string may make the extension and call one.ALTER EXTENSION pgcrypto UPDATEandSET SCHEMAwork;DIFF AS SQLmakes a schema before the extension in it;compress-level=0means no compression andcompress-algo=3is 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_ivanddecrypt_iv, text that isn't UTF-8 andDROP SCHEMAare 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: afterALTER SEQUENCE s RESTART,setval,TRUNCATE ... RESTART IDENTITYor a serial table dropped and made again committed elsewhere, the session's nextnextvalwent on with the old count (23where Postgres gives1). 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 anALTER SEQUENCE s INCREMENT BY 10orMAXVALUEanother session committed after the transaction began was ignored: it went on1, 2where Postgres gives1, 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 ownALTER SEQUENCEwhen it made one. ThatALTER SEQUENCEchanges 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 anINCREMENT BY 10committed meanwhile, the transaction'sMAXVALUE 1000gives1, 11, 21and 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. AnALTER SEQUENCErolled back to a savepoint is gone: the transaction counts by the committed definition again.SELECT last_value FROM sandpg_sequences.last_valuein the transaction count by the same definition, so they agree with the values itsnextvalhanded out (tests/seq_committed.rs). - Once a transaction has taken, set or altered a sequence's values, another session's
ALTER SEQUENCEof 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 (anALTER SEQUENCEwaiting for a transaction that waits forpg_advisory_xact_lockthe altering one holds) instead of both waiting forever. As in Postgres, the sequence stays the transaction's after aROLLBACK TO SAVEPOINTof itsALTER SEQUENCE. - Another session's
nextval,setvalandcurrvalwait for a transaction's uncommittedALTER SEQUENCEof 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, itsnextval, column defaults andcurrvalstill counted by the snapshot's definition, and the restart started at the snapshot'sSTART: with anALTER SEQUENCE s INCREMENT BY 10 MAXVALUE 15committed meanwhile, they gave1, 2where Postgres gives1, 11. They count by the definition last committed now, and the restart starts at theSTARTlast committed.SELECT last_value FROM s(andpg_sequences.last_value, also of a serial column's sequence) in such a transaction said where the old count was,3, trueafter three rows where Postgres says1, false; it says where the restart put the sequence and the values the transaction took since (tests/seq_committed.rs,tests/pgdiff.rsserial_counters_restarted_in_a_transaction).
ALTER TABLE ... RENAME CONSTRAINT for primary key, unique and foreign key constraints
ALTER TABLE t RENAME CONSTRAINT a TO bof 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, aCOMMENT ON INDEX), andpg_constraint,information_schema.table_constraintsandreferential_constraints, the duplicate-key and foreign key violations,ON CONFLICT ON CONSTRAINTandDROP CONSTRAINTuse 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.rsrename_key_constraints,tests/rename_constraints.rs).- A table renamed with
ALTER TABLE ... RENAME TOkept 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 namea_pkey, so aCOMMENT ON INDEX,'a_pkey'::regclassandRENAME CONSTRAINT a_pkeycould reach the wrong one; the new one isa_pkey1(tests/pgdiff.rsnew_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 CONSTRAINTon the parent dropped the partition's copy of the other one (leaving anON DELETE CASCADEthe parent no longer had), andpg_constraint.conparentidpaired 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.rspartition_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: afteralter table p rename constraint fa to fz; alter table p add constraint fa foreign key ..., the partition's copy offzand its new copy offawere bothfa, soalter table p drop constraint fadropped both and the partition stopped enforcingfz. A foreign key added without a name gives its partitions the parent's name for it (p_s_fkey, notp1_s_fkey), soDROP CONSTRAINTon the parent reaches them;CREATE TABLE ... PARTITION OFwith a constraint of a name the parent's foreign key has is 42710, as in Postgres (tests/pgdiff.rspartition_foreign_key_copy_names,tests/rename_constraints.rs). ADD CONSTRAINT(and a column's orCREATE 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 ofADD 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 checkq_a_keyand the unique constraintq_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.rscreate_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 gleft the partition's owngenforcing. 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.rspartition_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)andabs(text)made here,insert ... values (lower(abs('x'))), a DO block's and PL/pgSQL'slower(abs('q'))gave7where a SELECT gavelower(int)'s result. The inner call's type, of the function its arguments choose, chooses the outer one now (tests/pgdiff.rsnested_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')andw(null)betweenw(int)andw(float8)were 42883 (w(text)); a quoted literal and NULL are of unknown type there, as in Postgres, so they takew(float8), andu('2020-01-01')betweenu(date)andu(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)betweenm3(int, int)andm3(text, int), and the same over the extended protocol, was 42883; it is of unknown type there, as in Postgres, and takesm3(int, int)(function_overloads_for_parameters_declared_no_type,tests/pg.rsuntyped_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))tookg(text)andg(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 aformat(text, bigint)gave1, andconcat(1, 2)with aconcat(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(orset 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 pandDROP FUNCTION fwithout 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 andprepare p(int) as select f($1)took a later schema'sf(text)overf(int)(a parameter counted as an unknown literal),abs($1)andabs(sum(k) over ())took anabs(text)made here over the built-in,f(x => 1)betweenf(x int)andf(x text)in two schemas was 42725, andinsert ... values (g(5::bigint))or PL/pgSQL'sg(b)for a bigintbtookg(int)by the value overg(bigint). Each argument now has its declared type there, as in Postgres (tests/pgdiff.rsfunction_overloads_across_the_search_path_by_declared_types). f(1)withf(x int)in one schema andf(x int, y int default 0)in a later one (either way round), andCALLof 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'sy := lower('QQ')andreturn lower(x)were 22P02,values (now())42883,default now()0A000 and a generated columnlower(x)42P17. The arguments' types choose there as in a query, so they give the built-in, andlower(5)orabs('y')the function made here (tests/pgdiff.rsbuilt_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)andm(text, int),m('1', '1')andm(null, null)tookm(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 ag(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_paththat had any function of that name, so withf(int)in the first schema andf(text)in the second,f('x')was 22P02 andf(1::bigint)tookf(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, sok(1)betweenk(numeric)andk(float8)takesk(float8), then text for an unknown literal), an earlier schema's hiding only a later one of the same argument types;CALLandDROP FUNCTION f(text)find them the same way (tests/pgdiff.rsfunction_overloads_across_the_search_path). - With the
search_pathset, a function made here with a built-in's name was never called:abs('x'::text)with anabs(text)on the path was 22P02,upper('a', 'b')with anupper(text, text)was 42883, andlength('abc')with alength(text)in a schema named beforepg_cataloggave 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 beforepg_catalogis 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 namedf, as in Postgres (tests/column_names.rsa_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 functionRETURNS voidgave NULL (pg_sleepempty text), typed text:IS NULLwas true for some,pg_typeofsaidtextand a driver got type 25. They give the void value, as Postgres does: empty, not NULL,pg_typeofvoid, type 2278 on the wire; a SQL-language function returning void gives NULL as Postgres's does when Postgres runs it (a body such asselect 1, several statements, FROM or WHERE), and the void value of the function it calls where Postgres inlines it (a body of justselect pg_notify('c', m)orselect 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.rsvoid_functions_return_void,builtin_void_functions,distinct_on_beside_void,tests/pg.rsvoid_over_the_wire).CREATE TABLE ... IF NOT EXISTS ... ASandCREATE MATERIALIZED VIEW IF NOT EXISTSover 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 / 0was 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.rsrelation_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
$nwas 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 $1gave 5,for i in 1..2 loop s := s || $1gave1,2,), and a table column of that name made embedded SQL fail with 42702 (where t.id = $1in a function with a parameterid).$nis the parameter again, with its declared type (tests/pgdiff.rsplpgsql_numbered_parameter_is_the_parameter). - A string read as a regproc was an oid whenever it parsed as a number:
'-1'::regprocwas -1,' 42'and'+42'wereint4in,'00042'wasint4inwhere 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::oidis 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, evenselect count(*) from pg_classand 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.rsregproc_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_typeofof a parameter saidunknownfor a NULL: a prepared statement's$1declared text, a PL/pgSQL argument or variable (pg_typeof(a)withaNULL) 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 || '!'gave1299!,pg_typeof($1)saidtext), a value EXECUTE ... USING passed (execute 'select $1::text' using pgave1299), INSERT's VALUES and a column's default (concat('now'::regproc, '!'),to_jsonb(...),array[...],quote_literal(...)stored1299!,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 tonoas it would text (tests/pgdiff.rsregproc_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 + 1gave 1300,|| 1gavenow1,sum,avg,string_aggandhashtextgave 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 textof 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::textwrites 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 asetof regprocfunction was 42804, since Chronos keeps such arrays astext[]; 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 = rgave1299x,{1299}or42, andpg_typeof(r)saidbigint. Expressions that name a regproc now run bound, as SQL does, and give the function's name andregproc(tests/pgdiff.rsregproc_in_plpgsql_expressions). - A set-returning function's regprocs for a column of text were oids: PL/pgSQL's
RETURN NEXT rin asetof textfunction and a SQL function's rows gave42. 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 textwrote 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)wasnow,abs(...)andmd5(...)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 andformat'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, thencreate table other (x int)) read back as a bare number or another function,f = 'myf'::regprocwas false, and a merge orDIFF ... AS SQLbetween 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.rsregproc_of_a_function_made_here_outlives_ddl,tests/catalog_introspection.rsa_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::textof a prepared statement's regproc parameter gave1299or42. 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' endwas42,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.rsregproc_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.rsnotices_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::oidwas 1394, another abs, and2108::regprockept 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 (windoworder by, an aggregate'sorder 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'::regprocwas 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 declaredregprocorregprocedure(42704 before), soDIFF ... AS SQLof 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.rsregproc_keeps_its_overload,regproc_compares_as_an_oid,regproc_orders_by_oid,regproc_columns,regproc_behind_pg_catalog,tests/pg.rsmany_regprocs_over_the_wire,tests/catalog_introspection.rsa_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)andpg_typeof(typinput)saidtext,typinput::oidandtypinput = 42failed with 22P02,42::regprocwas42, and only the few functions a catalog row names could be read ('now'::regprocwas 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::oidis 1299,42::regprocisint4in,'int4in'::regproc::regprocedureisint4in(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.amhandlernames Postgres's handlers (bthandler, notbtreehandler) (tests/pgdiff.rsregproc_values,tests/pg.rsregproc_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 frompg_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"}), byINSERT,UPDATEandCOPY, and a name of no type fails with 42704, as in Postgres;vectorreads as Chronos's own type, so'vector'::regtypeworks too (tests/pgdiff.rsa_kept_regtype_array_column_reads_its_types,tests/catalog_introspection.rsa_kept_type_list_naming_a_vector). pg_typehad no rows forregtype(2206) and_regtype(2211), the type the lists go out as: reflection joiningpg_attributetopg_typelost such a column,information_schema.columns.udt_namewas_text,2211::regtypewas2211, and asyncpg, which looks the type up by id, couldn't readpg_prepared_statementsat all ("no decoder for OID 2211"). They are there now, and2211::regtypeisregtype[](tests/cursors.rsasyncpg_finds_the_regtype_array_type).- An enum in
publicwas named bare by the type lists,format_typeand regtype even withpublicoff the search_path (pg_dump's empty one, orpg_catalog); it ispublic.moodthere, as in Postgres, and'public.mood'::regtype, which failed with 42704, reads it (tests/catalog_introspection.rsa_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 returningtsvectorortsquery, 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.rsevery_regtype_array_goes_out_in_binary_as_oids). format_type(3614, null),3614::regtype::textand atsvectorortsquerycolumn'sformat_typeinpg_attributewere???or the number, and'tsvector'::regtypefailed with 42704; they aretsvectorandtsquery, as in Postgres (tests/pgdiff.rstext_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_typesorresult_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,\dand its ownDROP, past a restart. Theregtype[]column is now kept and read back; so is anint2vectororoidvectorone (create table t as select indkey from pg_index), which failed the same way (tests/pgdiff.rstables_made_from_the_prepared_statements_type_lists). format_type(22, null)andformat_type(30, null)overflowed the stack and closed the connection; they areint2vectorandoidvector.format_type(2211, null)isregtype[].- 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.rsa_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_typeswas text holding Chronos's own type names ({timestamptz}), andresult_typeswas always null. Both are nowregtype[], with the names regtype shows ({integer,text[],"timestamp with time zone"}, an enum's own name), soparameter_types::text[],array_length,unnestand subscripts give what Postgres gives;result_typesis 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, soEXECUTE p(1, 'x')failed; it now counts, for SQLPREPAREand a driver's Parse alike (tests/pgdiff.rsprepared_statement_parameter_and_result_types,tests/cursors.rsa_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_statementsandpg_cursorsread 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 inpg_cursors, as in Postgres.- A
LANGUAGE sqlfunction'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.rsprepared_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 + 1in a function with an argumentvwrote the argument's value plus one, andwhere name = 'p'overpg_prepared_statementscompared 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.rsfunction_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 statementPREPAREd) through the extended protocol, as psycopg and other drivers do, showed inpg_cursors.statement(pg_prepared_statements.statement) the last simple query the connection ran, such asSTART TRANSACTION, instead of its own text. They now show the text of the protocol's Parse, as Postgres does (tests/cursors.rsthe_views_show_the_text_a_driver_sent_through_the_extended_protocol).
pg_locks: who holds each advisory lock
pg_locksshowed apidonly for the querying session's own locks, so monitoring couldn't join it topg_stat_activityto see who blocks whom. Every lock held or waited for by a connection now has that connection'spid, as in Postgres. A program that opens several databases in one process also saw the other databases' advisory locks in each one'spg_locks; it now sees only its own (tests/sysinfo.rspg_locks_shows_every_holders_and_waiters_pid,tests/advisory.rspg_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
serialcolumn dropped and made again inside one transaction kept counting from the old counter (ids 3, 4 where Postgres gives 1, 2), and so didTRUNCATE ... RESTART IDENTITYin a transaction: the restart waited for COMMIT andnextvaldidn'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.rsserial_counters_restarted_in_a_transaction).
Parallel pg_dump (pg_dump -Fd -j N): pg_export_snapshot() and SET TRANSACTION SNAPSHOT
pg_dump -jfailed: its workers read the leader's snapshot.pg_export_snapshot()gives an id for what the calling transaction reads, andSET 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 realpg_dump -Fd -j 3of 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, thepg_stat_progress_*,pg_statio_*_indexes,pg_stat_xact_*and the rest) exist with their columns.pg_stat_bgwriter,pg_stat_wal,pg_stat_archiverandpg_stat_recovery_prefetchhave 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_cursorsandpg_lockswere always empty: they list the session's prepared statements (SQLPREPARE'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 intests/pgdiff.rsmonitoring_views.
MCP instructions tell a session that may merge to merge
- The
instructionsthe MCP server sends said "you can't merge: a person reviews your diff and merges it from a terminal" to every--agentsession, even one withmerge_ownormerge, so some agents stopped at "runchronos 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 callmergewhen 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,getIndexInfoandgetBestRowIdentifierfailed with a syntax error: pgjdbc reads a key's columns with(information_schema._pg_expandarray(i.indkey)).nand a record column read back as(result.KEYS).x. Chronos now has records:(expr).fieldreads 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).xis 42809 and an unknown field 42703, as in Postgres. The old reading of(table).columnis unchanged; a record column of that name is read first when no table has it.getColumnson any table with an array column, andgetTypeInfo, failed withfunction "pg_catalog.array_in" does not exist: pgjdbc askstypinput = 'pg_catalog.array_in'::regproc. Aregproccast 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, sopg_typeofsaystextand::oidof a built-in's name is not supported; catalog rows hold these names.)- Found by Codex on the PR:
_pg_expandarray'sxis the array's element type, as in Postgres (it was always an integer, so(..).xof 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_expandarraynext 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. Onlyinformation_schema._pg_expandarrayis the built-in now, as in Postgres: the bare name is an error on the default search path, and the built-in withinformation_schemaon 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.rsas 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 butgetTypeInfo(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 bwas a syntax error. A CHECK constraint is renamed, with Postgres's behaviour: the new name in the violation error andpg_constraint, itsCOMMENT 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 IntervalStyletakespostgres,postgres_verbose,sql_standardandiso_8601and 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 sendsiso_8601on 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, soforeign_keys(table)failed. ALTER TABLE ... DROP COLUMN x CASCADEandDROP CONSTRAINT x CASCADE(Django writes both) drop the foreign keys, on any table, that reference what goes, with Postgres's NOTICE; withoutCASCADEthey stay refused (2BP01).- Two fixes to those, from review:
DROP ... CASCADEon 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 outertread insidefrom t ubinds to the outer one. pgdifffails instead of skipping whenCHRONOS_REQUIRE_ORACLEis 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, binaryCOPY 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 andNOT NULL,ALTER TABLE ... INHERIT/NO INHERIT,ADD/DROP COLUMNcarried down (and refused on an inherited column, 42P16),SELECT/UPDATE/DELETEthat reach the children andONLYthat does not,tableoid, andpg_inherits.PARTITION BY RANGE | LIST | HASHon columns or expressions,PARTITION OF ... FOR VALUES FROM .. TO | IN | WITH (MODULUS, REMAINDER) | DEFAULT(MINVALUE/MAXVALUE), and sub-partitioning. Rows route to their partition onINSERT,INSERT ... SELECT,COPYandON CONFLICT; a row with no partition is 23514 "no partition of relation ... found for row". Hash partitions place rows exactly where Postgres does. AnUPDATEthat 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],DROPof a partition or the parent (2BP01 withoutCASCADE),TRUNCATEof 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:
\dand\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 realpg_dumpof 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-returningjsonb_path_query, their_tzforms, 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;silentsuppresses 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.jsonpathis accepted as a type name (held as text; a bad path is 42601 when it is used).
Network address types
inet,cidr,macaddrandmacaddr8are types: columns, literals ('192.168.1.5/24'::inet), casts to and from text, ordering, indexes,min/max, JSON output, andpg_typeof. Invalid input is 22P02, as in Postgres.- Operators
<<,<<=,>>,>>=,&&,+,-,&,|,~and the comparisons; functionshost,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 forpg_index,pg_constraint,pg_trigger,pg_depend) returned no rows: Chronos answered psql's\dqueries 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::regclassis answered from the catalog tables.unnest(outer.array_col)inside a correlated subquery gave text elements, soca.attnum = cols.colidnever matched: thearray(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_authidandpg_userlist the name the session logged in as (a superuser, as every login is), sowhere rolname = current_userfinds a row.pg_stat_user_tables,pg_stat_all_tables,pg_statio_user_tablesandpg_statio_all_tableslist 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_clausereads((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.5andtrue || false(two typed numbers or booleans) are 42883 "operator does not exist", as in Postgres, instead of the text12;1 || '2'and'a' || 1still concatenate, as do arrays.select i->>'sku' from jsonb_array_elements(x) as iwas a whole-row composite under the alias; the single column ofjsonb_array_elements,json_array_elementsand their_textforms is namedvaluewith 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')andupdate 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 tgives(7,q,1)as Postgres writes it,row(1, 'a b', null)gives(1,"a b",), andcount(t),t is null,t = t,(t).col,(t).*androw(t.*)work. It is held as that text (pg_typeofsays 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(andcascaded, or a barewith check option, which is cascaded) was 0A000. Now anINSERTorUPDATEthrough 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_optionreports 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.xmaxis 0 for a row inserted and 1 for one the conflict updated;xmin,xmax,cminandcmaxread 1, 0, 0, 0 anywhere else (Chronos keeps no per-row transaction ids).ctidandtableoidare not there.
Time zones with daylight saving time
SET TIME ZONE 'America/New_York'(and what JDBC sends at connect,TimeZone=Europe/Berlinin 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;tzdatamust 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, andAT 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') andto_char'sTZ,tz,TZH,TZMandOFfollow it.pg_timezone_namesandpg_timezone_abbrevsare 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'sFMnow 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 EXTENSIONand 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_mokotoffandunaccentwork, 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)(andgist_trgm_ops) is accepted and kept as a btree, so%andILIKEscan. Not done: thepg_trgm.*_thresholdsettings (the defaults are used), a dependency error forDROP EXTENSIONwith an index on its operator class, andrealas 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 thejson_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)andvarchar(n)limits are applied (22003, 22001), a name given twice is 42701, and a non-object is 22023.jsonb_populate_recordneeds 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'withL99G999D99,'<12>'withPR, Roman numerals withRN) was 42883, the last step of most CSV and ETL imports.src/sql/tonumber.rsis a port of Postgres'sNUM_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 whatpg_dumpwrites 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 DOMAINis refused while a column uses it (2BP01) andCASCADEdrops those columns. Checked against Postgres 16 (46 statements). Not done: checks on a cast to a domain,pg_typeofnaming 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, andselect * 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 ORDINALITYtoo). 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)andALTER FUNCTION f(int) IMMUTABLE | SECURITY DEFINER | COST 10 | SET search_path ...were 42601 or 0A000, so apg_dumpscript 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 orCREATE UNIQUE INDEX ... NULLS NOT DISTINCT: two nulls clash),FOREIGN KEY ... MATCH FULL(mixing null and non-null columns is 23503) and temporary tablesON COMMIT DELETE ROWS/DROPwere 42601 or 0A000. They work as in Postgres, and show inpg_get_constraintdef,pg_get_indexdefandpg_index.indnullsnotdistinct. TRUNCATEof 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:DEFERRABLEunique constraints.
TABLESAMPLE, and generate_series with a numeric step
select * from events tablesample bernoulli (1)(sampling a big table) was a syntax error;BERNOULLIandSYSTEMwithREPEATABLE (seed)work, with Postgres's errors for a percentage outside 0..100, a null, or an unknown method.SYSTEMsamples a row at a time here (Postgres takes whole pages), so the two can differ on small tables.generate_series(0, 1, 0.25)andgenerate_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_scaleandtrim_scalewere 42883. The degree functions are Postgres's (first-quadrant reduction and exact constants, sosind(30)is 0.5 andtand(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.982tofloat8could 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)printed7.5,'1e3'::jsonb1e+3and'100000000000000000000'::jsonb1e+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::numericis 1.5 (the cast goes through a double), androw_to_jsonprints 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)andjsonb_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)andregexp_replace(s, p, r, start, N, flags)were 42883 or ignored their extra arguments;string_to_tabledid not exist.regexp_matchesandstring_to_tablealso 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 andpg_dump-style tools write for LIKE) ands ^@ '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!~*withANY/ALLnow match.substring('Thomas' from '...$'),substring(s, 'o(.)b'),substring(s from '%#"o_b#"%' for '#')andsubstring(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 literalU&'d\0061t' UESCAPE '!',sha224(bytea),character_lengthand thebpchartype 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 badUESCAPEcharacter).sha224is written out insrc/sql/bytes.rs(ringhas no SHA-224).upper,lowerandinitcapused Unicode's full case mapping (upper('straße')wasSTRASSE,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, andchr(n)for annpastinteger42883, as Postgres has them.
Regular expressions: Postgres's advanced syntax
- Patterns from real codebases failed on syntax Postgres accepts:
(?i)at the start,\yand\mword edges,(?<=...)and(?!...)lookaround,\1back references to earlier groups,[[:<:]],\x41and\u0041, and a\Din a newline-sensitive pattern.src/sql/regex.rsis rewritten to read a pattern as Postgres does (embedded options(?bcegimnpqstwx)and***=/***:prefixes, the same flag letters inregexp_*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,\2with 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 intests/regex_syntax.rs). Left as they are: a lazy quantifier inside an alternation under a counted repeat ((a??|ab){2}),(x)+wherexcan match nothing, andregexp_matchesbeside 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'andselect upper(regexp_split_to_table(s, ','))were 42883 (only a bareunnestorgenerate_seriesworked in the select list), and a script that expands a JSON array or a delimited string into rows had to be rewritten with aLATERALjoin. Any ofgenerate_series,unnest,jsonb_array_elements,jsonb_array_elements_text,jsonb_object_keys,regexp_split_to_tableandgenerate_subscriptsworks inside an expression now, several side by side as Postgres runs them (the shorter padded with nulls); inWHERE,GROUP BY,HAVINGorONit 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 -> 0is1, 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')andto_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 whatDCH_from_chardoes, checked against 2,500 random templates and 1,500 round trips of Postgres's ownto_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 (YY24 is 2024, 70 is 1970), and each error with its SQLSTATE.make_time,make_timestampandmake_timestamptz(with an optional zone) did not exist, andmake_date(-44, 3, 15)was not 44 BC.
infinity, dates before the common era, and the special date words
'infinity'::dateand'-infinity'::timestampwere 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, withisfinite(); andepoch,now,today,tomorrowandyesterdayread 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_charwithBC/ADtoo), 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.5andinterval / 2were 42883,sum(interval_column)failed,justify_hours,justify_daysandjustify_intervaldid not exist,(a, b) OVERLAPS (c, d)was a syntax error, andinterval '0'was 22P02. They work as Postgres does: the products keep whole months and days and cascade fractions down (itsinterval_mulandinterval_div, ported),sum/avg/min/maxof intervals, the justify functions, andOVERLAPSover 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 0x1Freturned0(the lexer read0and an aliasx1F); 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::booleanandtrue::intwere errors (42804); they cast as Postgres does.bit_and,bit_or,bit_xorandany_valuedid 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)gave3(Postgres rounds halves to even:2), andceil,floor,truncandsignof 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 inFROM, 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 theORDER BYkeys, withOFFSET, GROUP BY, DISTINCT and UNION as Postgres has them; without anORDER BYit 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) tgave 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 andCREATE 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 tandselect json_agg(t) from t(the shape an API layer reads a whole table as) failed with "column t does not exist", androw_to_jsonwas not a function. A table's name, its alias ort.*now stands for its whole row where a value is wanted byto_json(b),row_to_json,json(b)_agg,jsonb_object_aggandjson(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 bareselect t from tis 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]), andarray_to_jsonexists. Still open: anumericin JSON is a double, so7.50reads back7.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, andtimezone(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'isa + (b AT TIME ZONE 'UTC')). Zones with daylight saving time (America/New_York,Europe/London) are still 22023, asSET TIME ZONEsays: Chronos carries no time zone database, and adding one is a decision for the project.extractandAT TIME ZONEcolumns are namedextractandtimezone, as Postgres 14 names them (at_time_zoneintests/pgdiff.rs,function_columns_are_named_as_in_postgresintests/sql.rs).
EXTRACT is a numeric
extract(year from d)gave2024.0, a double precision, where Postgres 14 and later gives the numeric2024, so an ORM read a float where it expects an integer or a decimal, andextract(second ...)andextract(epoch ...)lost the six decimals Postgres writes (45.678901,1710513045.678901). It is a numeric now at Postgres's scale for every field (secondandepochsix decimals,millisecondsthree,juliantwenty for a timestamp),date_partis 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, ondate,timestamp,timestamptz,timeandintervalas each has them (hourof a date is 0A000, an unknown unit 22023, as in Postgres). An interval'sepochcounted a year as 12 months of 30 days; it is 365.25 days, as Postgres has it, andinterval '100:00:00'(more than 24 hours) now reads (extract_is_numericintests/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_slopeand their families did not exist (42883), so any report that measures spread or correlation failed on everyday SQL; andstring_agg(...) OVER (...),array_agg(...) OVER (...),jsonb_agg(...) OVER (...)were 0A000. All ofstddev,stddev_pop,stddev_samp,variance,var_pop,var_samp,corr,covar_pop,covar_sampandregr_count,regr_avgx,regr_avgy,regr_sxx,regr_syy,regr_sxy,regr_slope,regr_intercept,regr_r2exist now, with Postgres's own arithmetic so the results agree digit for digit with a real Postgres 16: floats accumulate as itsfloat8_accumandfloat8_regr_accum(Youngs-Cramer) do, integers and numerics through its numeric arithmetic (a numeric result, at the scale it picks for a quotient), withFILTER,DISTINCTand an innerORDER BY, inGROUP BYqueries and as window functions over any frame (statistical_aggregatesandlist_and_statistical_aggregates_as_windowsintests/pgdiff.rs). Arealcolumn is held as a double here, sostddevover one can differ in the last digits.Decgained a rounded square root (sqrt_at) for the numeric standard deviation.
CREATE TABLE AS and SELECT INTO
create table t as select ...andselect ... 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, withTEMP,IF NOT EXISTS, a column-name list (create table t (a, b) as ...),WITH [NO] DATA, andSELECT,WITHandVALUESqueries: 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_asintests/pgdiff.rs). Column types are the query's as Chronos types them:varchar(n)becomestext,smallintbecomesinteger.
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 = 1succeeded (Postgres: 42704),SET work_mem = '64MB'succeeded andSHOW work_memthen said the setting did not exist,pg_settingshad 26 rows, and SQLAlchemy'sget_table_options()and so Alembic's autogenerate died oncurrent_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).SETchecks 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()andpg_settingsread it back in Postgres's units (64MB,1500ms,on), andRESET,SET ... TO DEFAULTandset_config()work on all of them. ASETis now undone byROLLBACK,ROLLBACK TO SAVEPOINTand 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_digitsbelow 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, andSHOW synchronous_commitis the database's mode (pg_settingssays what a session set) (settingsandsettings_follow_transactionsintests/pgdiff.rs,settings_that_change_answers_take_only_what_chronos_doesintests/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()writesNOT LIKE 'pg/_%' ESCAPE '/', which was a syntax error (LIKE,NOT LIKEandILIKEnow takeESCAPE '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()callspg_collation_is_visible(and its siblingspg_function_is_visible,pg_operator_is_visible,pg_opclass_is_visible,pg_opfamily_is_visible,pg_conversion_is_visiblenow exist); andget_indexes()failed in psycopg withmalformed arraybecausepg_index.indkey,indclass,indoptionandindcollationreached it asint2[](type id 1005) holding text like1 2, which is Postgres'sint2vectorandoidvector(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)sayssmallint[], andint2[]andoid-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 toint2[]/smallint[]carried the new smallint range check into the array (it was refused as an integer): fixed (like_with_an_escape_character,index_vectorsintests/pgdiff.rs,int2vector_and_oidvector_over_the_wireintests/pg.rs).
smallint holds 16 bits
- A
smallintcolumn was anintegerunderneath:insert ... values (40000)succeeded, andpg_dump,information_schemaand psql's\dsaidinteger, 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 andALTER COLUMN ... TYPE smallintover rows that don't fit),x::smallintand'40000'::smallintcheck the range, the width survivesLIKEandADD COLUMN, and the catalogs (atttypid21,attlen2,format_type,information_schema.columns.data_type) saysmallint. psql's\dalso showscharacter varying(5),character(3)andnumeric(10,2)as they were made, where it saidtextandnumeric. Not done: a smallint still reaches a driver asint4in result columns and parameters, arithmetic on smallints is not range-checked until it is written, andCREATE TABLE ... ASmakes aninteger(smallintsintests/pgdiff.rs,psqlintests/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 noposition. It now sayssyntax error at or near "x"(orat 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 sayscannot drop table t because other objects depend on itwith 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 backwardFETCHon aNO SCROLLcursor carries Postgres's hint (syntax_errors_carry_their_position,errors_that_say_what_to_do_carry_a_hintintests/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_activityhad 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_addrand port,backend_start,xact_start,query_start,state_change,state(active,idle,idle in transaction,idle in transaction (aborted)) and thequeryit is running or ran last (its first 1024 bytes, as Postgres keeps).pg_stat_databasehas a row for each world with itsnumbackends; its other counters are null (Chronos keeps none).pg_terminate_backend(pid)did not exist: it closes the connection (pg_cancel_backendonly stops its statement), and both sayPID n is not a PostgreSQL backend processfor a pid nobody has; an agent can do neither (42501) (pg_stat_activity_shows_connections_and_pg_terminate_backend_ends_oneintests/sysinfo.rs).
information_schema.routines
select * from information_schema.routineswas 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(recordfor a table function,trigger,void, none for a procedure),routine_body(SQLfor LANGUAGE sql, elseEXTERNAL),routine_definition,external_language,is_deterministic(immutable only) (information_schema_routinesintests/pgdiff.rs). Fewer columns than Postgres's 80-odd: the ones tools read.
uuid is a type
- A
uuidcolumn wastextunderneath, which was wrong twice over. Answers:'A0EEBC99-...'and'a0eebc99-...'were two different values (sowhere id = 'A0EE...'found nothing and both could be inserted as one key),'not-a-uuid'was accepted, andpg_typeof,information_schema,pg_dumpand psql's\dsaidtext. Wire: drivers got text where they expect a UUID (sqlx'sUuid, pgx'spgtype.UUID, npgsql'sGuid, JDBC'sgetObject(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 lowercase8-4-4-4-12, literals compared with a uuid read as one, 16 bytes in binary both ways,gen_random_uuid()auuid, and usable as a primary key, unique or indexed column and foreign key target (uuidsintests/pgdiff.rs,uuid_over_the_wireintests/pg.rs,tests/pg_dump.rs).chronos importmakesuuidcolumns uuid. Tables made before keeptextcolumns;ALTER TABLE t ALTER COLUMN id TYPE uuidconverts them (and rewrites a key's rows to the canonical text). The Mem0-style test intests/sql.rsused'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), andALTER TABLE v RENAME TOon a view said the view wasn't there; both now rename it (the comment goes with it), as doesALTER MATERIALIZED VIEW. A view another view reads still can't be renamed here (0A000: views name what they read), andRENAME COLUMNon a view is 0A000 (renaming_viewsintests/pgdiff.rs). pg_has_role([user,] role, privilege)did not exist, so psql 17's\l+failed; it answers as the otherhas_*_privilegefunctions 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 withIF EXISTSrun through.ALTER SEQUENCE IF EXISTSsays so too.CREATE TABLE | INDEX | SEQUENCE | MATERIALIZED VIEW IF NOT EXISTSon something already there,DROP INDEX | EXTENSION IF EXISTS,ALTER TABLE ... DROP COLUMN | CONSTRAINT IF EXISTSandDROP TRIGGER IF EXISTS ... ONa missing table did nothing without saying so (or worded the notice differently); they now send Postgres's notice.COMMITandROLLBACKoutside a transaction, andBEGINinside 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 inDROP FUNCTION IF EXISTS f(int)is still wordedf(integer), where Postgres echoes the type as written:f(pg_catalog.int4).)
psql's \d+, \dt+ name and OPERATOR(pg_catalog.~)
\d+ tablecrashed 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+ vfailed. The operator, named with or without its schema, is that operator (schema_qualified_operatorsintests/pgdiff.rs).
COMMENT ON is kept
COMMENT ON TABLE | VIEW | MATERIALIZED VIEW | SEQUENCE | INDEX | COLUMN | CONSTRAINT | SCHEMAchecked its object and threw the text away ("the comment isn't kept"), soobj_description,col_description,pg_description, psql's\d+and\dt+, PostgREST's and Hasura's descriptions, and theCOMMENT ONlines ofpg_dumpall came out empty. The comments are rows of the world: they fork, diff (DIFF ... AS SQLwritesCOMMENT ONafter 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);NULLor''takes one away. Comments on functions, types, triggers, extensions and databases are still not kept, and say so (commentsintests/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) andCLOSEdid not exist (42601), so psycopg's named server-side cursors, psql's\set FETCH_COUNTand 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 isWITH HOLD, and follows Postgres's positions, tags and errors (34000, 42P03, 25P01, 55000); ~100 statements agree with Postgres 16 (cursorsintests/pgdiff.rs,tests/cursors.rs). Unlike Postgres, a cursor withoutNO SCROLLscans backward whatever it reads, andBINARYcursors andWHERE CURRENT OFare refused.- The protocol's Execute row limit was ignored (every row came back at once, never
PortalSuspended), so JDBC'ssetFetchSize,postgres'squery_portaland every driver that pages through a portal got everything. Execute now sends that many rows andPortalSuspended, 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 NOTHINGwas a syntax error (MERGEmeant only Chronos'sMERGE BRANCH), so the upsert-by-source statement ORMs, ETL tools (dbt'smergestrategy, Airbyte, Fivetran) and hand-written migrations use failed. It follows Postgres 16: several clauses each with anANDcondition, a table, view or subquery as the source, parameters through a driver orPREPARE, the tagMERGE 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 MERGEand Postgres 17'sNOT MATCHED BY SOURCE/RETURNINGare refused (merge_intointests/pgdiff.rs,tests/merge_into.rs). UPDATE t SET ... FROM some_viewandDELETE FROM t USING some_viewansweredtable "some_view" does not exist(the views aFROMreads were expanded for aSELECT, not for these); they read the view (update_and_delete_read_viewsintests/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 everyWITH moved AS (DELETE ... RETURNING *) INSERT INTO b SELECT * FROM moved, failed.INSERT,UPDATEandDELETE(withRETURNING) work in aWITHbefore aSELECT,INSERT,UPDATEorDELETE: 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 theWITHchanges is 0A000 (Postgres shows it the old rows; here it would see the new). Checked against Postgres (writable_ctesintests/pgdiff.rs) and for the refusals, atomicity, agents and the wire intests/writable_ctes.rs.
ALTER COLUMN ... TYPE varchar(n) keeps the new length
ALTER TABLE t ALTER COLUMN name TYPE varchar(100)(what Django'sAlterFieldwrites when aCharField'smax_lengthchanges, and Rails's and Alembic'schange_column) changed the type but kept the old limit: the column stayedcharacter varying(50)in the catalog and a 60-character value was refused with 22001. The length ofvarchar(n)/char(n), the precision and scale ofnumeric(p, s)and the dimensions ofvector(n)now change with the type, and existing values must fit (22001, 22003, 22000, as Postgres). The 22001 message readscharacter varying(4), as Postgres's does, notcharacter varying (4). Checked against Postgres (alter_type_keeps_the_new_length_and_precisionintests/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_exactlyhung 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'sBinaryField, SQLAlchemy'sLargeBinary, Prisma'sBytes, Rails'sbinary, and every hash, key and file column failed to create.byteais a value type of its own (chronos::sql::Value::Bytea): read as\xhex or the escape form, printed as hex, sent in binary as the bytes, ordered bytewise (soORDER BY,GROUP BY,DISTINCT, indexes,UNIQUEand primary keys of one or several columns work), withlength,octet_length,bit_length,substring,position,overlay,||,string_agg,get_byte,set_byte,encode/decode(hex, base64, escape),md5,sha256,sha384,sha512,convert_toandconvert_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 (byteaintests/pgdiff.rs), through a driver's binary parameters and results and COPY (bytea_over_the_wireintests/pg.rs), and bypg_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_sizeandpg_database_sizedid 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_prettyandpg_size_bytesmatch Postgres exactly; the relation sizes are estimates in Postgres's 8 kB pages from the rows and index entries stored. Checked against Postgres (size_functionsintests/pgdiff.rs, andtests/pg.rs).
Foreign keys to a UNIQUE column
REFERENCES t (email)whereemailisUNIQUE(Django'sForeignKey(to_field=...), Prisma'sreferences: [email], Rails'sprimary_key:) was 42830 ("a foreign key must reference the primary key"). It takes aUNIQUEconstraint's or a plain unique index's columns, several of them too and in any order, andON DELETE/ON UPDATEact 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), anddiff as sqlwrites 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_columnintests/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 underON 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 ACTIONandON 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_ruleandpg_get_constraintdefshow them. Checked against Postgres (foreign_key_actionsintests/pgdiff.rs). oid_column IN ('t'::regclass)was 22P02 (invalid input syntax for type bigint: "t"); aregclassin anINlist compares as its oid, as it does after=(regclass_in_a_listintests/pgdiff.rs).
Generated columns
col type GENERATED ALWAYS AS (expr) STOREDwas a syntax error (Django 5'sGeneratedField, Rails 7's virtual and stored columns, Prisma's and Drizzle'sgeneratedAlwaysAs, hand-written migrations, and whatpg_dumpwrites for one). The expression is worked out from the row's other columns on every insert and update (ON CONFLICT DO UPDATE,UPDATE,COPY), andALTER TABLE ... ADD COLUMN ... GENERATEDfills the rows already there. A value written to the column is 428C9 (DEFAULTis 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.COPYleaves generated columns out unless told to (naming one is 42P10).attgenerated,information_schema.columns.generation_expression,pg_attrdefandpg_dump(GENERATED ALWAYS AS (...) STORED) show them;LIKE ... INCLUDING GENERATEDkeeps them. Checked against Postgres (generated_columnsintests/pgdiff.rs,generated_columns_stay_out_of_copyintests/copy.rs, andtests/pg_dump.rs).
CREATE TABLE ... (LIKE other ...)
- It was a syntax error at
like(Django'sCREATE TABLE ... LIKEfor test copies, Rails and Alembic scripts, hand-written partition-style migrations). Copies columns (of a table or a view), types,NOT NULL, and withINCLUDING DEFAULTS | CONSTRAINTS | INDEXES | IDENTITY | ALLmore, wherever it stands among the other columns; severalLIKEs work. Checked against Postgres (create_table_likeintests/pgdiff.rs). Not for temporary tables (0A000). information_schema.columnsgivescharacter varyingwithcharacter_maximum_length,characterandnumeric_precision/numeric_scaleforvarchar(n),char(n)andnumeric(p, s)columns (they weretextwith 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 inFROMtoo. 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_functiondefandpg_proc.pronargdefaultsshow them. Checked against Postgres (function_parameter_defaultsintests/pgdiff.rs).
Identity columns with options
GENERATED ... AS IDENTITY (START WITH 10 INCREMENT BY 5 MAXVALUE 1000 CYCLE ...)was 0A000, and it is whatpg_dumpwrites for every identity column (with the defaults spelled out). It makes the sequencet_c_seq, owned by the column, withpg_sequence,information_schema.columns.identity_start/identity_increment/identity_maximum/identity_minimum/identity_cycleandpg_depend(i) showing it; the defaults keep using the column's own counter. Also inALTER TABLE ... ADD COLUMNandALTER COLUMN ... ADD GENERATED;DROP IDENTITYand dropping the column drop the sequence, as does dropping a column a sequence isOWNED BY. Checked against Postgres (identity_optionsintests/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 inON CONFLICT DO UPDATE SET. Checked against Postgres (update_set_defaultintests/pgdiff.rs).
pg_dump works
- Postgres's own
pg_dump(16) dumps a Chronos database: schemas, enum types, functions and procedures, tables (withcharacter 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'ssetval. 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_typeandpg_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, andpg_classandpg_attributewith their realrelhastriggers,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_viewdefandpg_options_to_tableexist. - A serial or identity column's counter is a sequence relation,
t_id_seq: it is inpg_class,pg_sequencesandinformation_schema.sequences,select last_value, is_called from t_id_seqreads it (as it does any sequence), and the column's default isnextval('t_id_seq'::regclass).OWNED BYis kept, and dropping a table drops the sequences it owns. GENERATED ALWAYS AS IDENTITYwas the same asBY DEFAULT: an explicit value went in. It is refused now (428C9, withOVERRIDING SYSTEM VALUEto allow it,OVERRIDING USER VALUEto ignore it), onINSERTandUPDATE;COPYfills it.ALTER COLUMN ... ADD GENERATED,SET GENERATEDandDROP IDENTITYwork,attidentityandinformation_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(whatpg_dumpwrites after every object) is accepted with a notice, andSET client_min_messagesis kept and honored, so the notice isn't sent to a script that setwarning.- 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, soSTARTandINCREMENTwere ignored andsetvalmoved 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 insideWITH RECURSIVEwas "does not exist"; a tableCHECKon one column was namedt_check, nott_col_check; aDEFAULTon ajsonb, 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.indkeywas written[0:0]={2}, which no driver reads as an int2vector; the psql\dshim answeredpg_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_dateand a column'sDEFAULT now()were read from the clock at every call, soselect now() = now()was false,age(now(), now())was -1 µs, andcreated_atandupdated_atset tonow()in one transaction differed. They now give the moment the transaction began (the statement's, outsideBEGIN), as Postgres does.statement_timestamp()is the statement's, andclock_timestamp()still reads the clock at each call (aDEFAULT clock_timestamp()too). All four names were one function before.
What Django, Rails and Alembic emit that failed
INSERT INTO t DEFAULT VALUESwas a syntax error atdefault(a model created with no fields; Django'sModel.objects.create()).pg_get_serial_sequence('t', 'id')returned NULL, sosetval(pg_get_serial_sequence(...), max(id)), which Django'ssqlsequenceresetandloaddataand Rails's fixtures andreset_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,setvalandlastvaltake that name, andsetvalmoves a serial column's counter, which it did not before: a serial column ignored the originsetvalandRESTARTset.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_opsand the default operator classes,COLLATE "C",INCLUDE (cols),WITH (fillfactor = ...),TABLESPACE, andUSING 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_dumpand Rails'sstructure.sqlwrite 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'sAlterFieldand Alembic writeUSING; an expression that computes something else is still 0A000),SET SCHEMA, andALTER INDEX a RENAME TO bwere syntax errors or 0A000.OWNER TO, storage parameters (SET (fillfactor = 70, autovacuum_enabled = false)),SET TABLESPACEandSET LOGGEDsucceed and change nothing, with a notice; a restore of apg_dumpno longer stops at the firstALTER TABLE ... OWNER TO.
Catalog: every column Postgres has, pg_roles, pg_settings, the reg types, and E'...'
- Every
pg_catalogtable 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 (relpersistencep, a table'srelreplidentd), and a table it has nothing for (pg_operator,pg_ts_*,pg_foreign_*,pg_event_trigger,pg_publication, ...) is empty.pg_typehas Postgres's own rows for the types Chronos holds (withtypcategory,typalign,typstorage),pg_language(internal,c,sql,plpgsql),pg_tablespaceandpg_extension(plpgsql). Each has a trailingtableoid, which pg_dump reads. Onlyinformation_schematables keep their own shape. pg_roles,pg_userandpg_authidlist the one role,chronos, that owns everything;pg_settingsandSHOW ALLlist the settings a session can read, its ownSETs included.'pg_catalog'::regnamespace,regrole,regtype,regproc,regprocedure,regoper,regoperator,regconfig,regdictionary,regcollationcasts, with or withoutpg_catalog.(a string is read as its oid; an oid shows as its name:23::regtypeisinteger),'pg_class'::regclassand the other catalog tables' oids, andacldefault().E'...'escape strings (\n,\t,\\,\',\xHH,\uXXXX, octal) were a syntax error at the string.- A function in
FROMinside 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 withcolumn "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_defaultandON COMMIT PRESERVE ROWSare accepted after the column list in any order;INHERITSandPARTITION BYare 0A000; an unknown parameter is 22023, another tablespace or access method 42704.pg_inheritsandpg_partitioned_tableare 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) andtrim_array, and||on arrays (arr || arr,arr || elem,elem || arr, which joined arrays as text). pgbench's catalog check usesarray_position(current_schemas(true), ...). - psql's
\dshim answered any query that mentionedpg_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 insimple,extendedandpreparedmode 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=Nretries 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 andIF NOT EXISTS-style guards use),CREATE [OR REPLACE] PROCEDURE,CALL name(args)(with named arguments) andDROP PROCEDUREwere 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, soselect proc()works where Postgres says 42809;INOUTparameters and transaction control inside are not supported. Diffed against Postgres indo_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 otherGROUP BYitems),GROUP BY DISTINCT, andGROUPING(...)were syntax errors orfunction rollup(text) does not exist. A query with sets runs once per set, joined withUNION ALL, a column outside a set as NULL;ORDER BYafter it can name a column, a position, or an expression from the select list (order by sum(qty)). Row order within ties and theGROUPINGbits 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) andmode() WITHIN GROUP (...), withFILTER.- Window frames
GROUPS BETWEEN ...andEXCLUDE 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_aggregatesandwindow_frames_by_groups_and_with_exclusionsintests/pgdiff.rs).
Array slices, named arguments and other small syntax
a[2:3],a[:2],a[2:]anda[:]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,|/ xand||/ x(absolute value, square root, cube root; alsocbrt()),int4('1'),int2,int8,float4,float8,text(5),bool('t')anddate('...')written as functions,make_interval(...), andx 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)andf(b => 2, a => 1)for a function made withCREATE 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_operatorsintests/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 DEFERREDandINITIALLY IMMEDIATE(also onFOREIGN KEY (...)andADD CONSTRAINT) are accepted, read as Postgres reads them (INITIALLY DEFERREDalone impliesDEFERRABLE;NOT DEFERRABLE INITIALLY DEFERREDis 42601). A deferred key is checked when the transaction ends: children can come before their parents, and a row still without a parent fails theCOMMITwith 23503 and the transaction is rolled back. Deleting a parent that rows still reference is put off the same way (ON DELETE RESTRICTisn't).SET CONSTRAINTS ALL | name, ... DEFERRED | IMMEDIATE(IMMEDIATEchecks 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), andALTER TABLE ... ALTER CONSTRAINT name [NOT] DEFERRABLE [INITIALLY ...].pg_constraint.condeferrableandcondeferred,pg_get_constraintdefandinformation_schema.table_constraintssay which.MATCH SIMPLEis accepted;MATCH FULLis 0A000.- Answers checked against Postgres statement by statement, savepoints and cascades included (
deferrable_foreign_keysintests/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 anddocker-entrypoint-initdb.dscripts at their first statement. A database is a world:CREATE DATABASE appmakes an empty world with no parent (nothing of main's, as Postgres copies an emptytemplate1), and a client connects to it by name (dbname=app);TEMPLATE some_worldforks 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 thanC, a connection limit) are accepted with a warning that they aren't applied. - Tools connect to
postgresortemplate1to make the others: both aremainunless a world is named so.pg_catalog.pg_databaselists the worlds, andpostgres,template0andtemplate1, 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
postgrescrate, 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'sdistinct(*fields), Rails and hand-written "latest row per group" queries): the first row of each set, checked as Postgres checks it (42P10 whenORDER BYdoesn't lead with theONexpressions).- 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 TIESis 0A000);LIMITtwice, orLIMITwithFETCH, 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 <@ banda && bon 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 + 1is~(5 + 1)), andgenerate_series(...)in the select list,::dateon it included.naturalandfetchare 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,REINDEXandCLUSTERwere 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 (VACUUMandREINDEX CONCURRENTLYare 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'sSET app.org_id.DISCARD PLANS,SEQUENCESandTEMPtoo.COMMENT ONchecks its object and succeeds, with a notice that the comment isn't kept.LOCK TABLEin a transaction: the modes that would keep writers out make otherLOCK TABLEstatements wait (an advisory lock on the table's name until the transaction ends;NOWAIT,statement_timeoutand 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 EXTENSIONandDROP EXTENSIONtakeplpgsql,uuid-osspandpgcrypto(which give what's built in:uuid_generate_v4()andgen_random_uuid(), with a notice);pg_trgm,citextand the rest are still 0A000. An agent can runCREATE 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_catalogandcurrent_schemawithout 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(...)andarray_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_privilegeandhas_any_column_privilegecheck 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
$1forpg_cancel_backend,pg_sleep,pg_advisory_lockandsetval'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.shmeasures 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 indocs/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 (theNOT EXISTSform 122 s, the same predicate in aDELETEas long). The subquery now runs once, without the conditions that read the outer row, and each outer row looks its candidates up by the=(herel2.company = l1.company) and checks the rest on them: 0.13 s. The same goes for a correlatedIN. Nulls,NOT EXISTSandNOT INanswer as before. A subquery withLIMIT, grouping, an aggregate, a nested subquery or a volatile function, or too big forCHRONOS_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 ofIN).x IN (select ...)andNOT INwith 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 otherARRAY[...]with something after the](::type[], a subscript), insideANY/SOME/ALLwere a syntax error at::. They now work.
Parameters inside WITH queries are counted
with a as (select $1::int as x) select x from afailed over the Postgres protocol with 08P01 "1 parameters given, but the statement takes 0": a$nonly in a WITH query, a derived table (FROM (select ...)), aWITH RECURSIVEpart 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)andcreate 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 andpg_get_indexdefwrite 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)andavg(bigint)now returnnumeric(exact, with Postgres's scale:6.2297297297297297,10.0000000000000000), as Postgres does, where they returneddouble precision. Drivers such as node-postgres get a string, not a rounded JS number.avgof floats is stilldouble precision;avgof 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 oftext, so node-postgres and other drivers return a real array, not the string{a,b}.- An unnamed
array_agg,string_agg,json_agg,bool_andorbool_orcolumn is namedarray_agg,string_agg,jsonb_agg,bool_and,bool_or, where it was named with internal plan text. select id::text from tnames its columnid(was?column?), so a driver that maps rows by name finds it. A cast takes its operand's name (u.code::textiscode,now()::dateisnow), a cast of a constant its type's (1::intisint4), andCASE,EXISTS(...)andARRAY[...]arecase,existsandarray, 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,schemaand, for NOT NULL,column, anddetailwhere 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 onerr.constraintnow match. The message text is unchanged for now.
A foreign key keeps ON DELETE RESTRICT
information_schema.referential_constraints.delete_rule,pg_constraint.confdeltypeandpg_get_constraintdefreportedON DELETE RESTRICTasNO ACTION, so schema diff tools saw a change on every run afterchronos 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 UPDATEactions still all read as NO ACTION.
varchar(n) and char(n) limits are enforced
- A value longer than
ncharacters written to avarchar(n),char(n)orcharacter 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 withchronos importlost every length rule its schema had. A barecharischar(1). 'abcdefgh'::varchar(4)andCAST(... AS character varying(4))cut toabcd.- Not yet:
char(n)values are not blank-padded,information_schema.columnsstill reports these columns astextwithoutcharacter_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_5and1e1_0read as one number, as in Postgres 16+.1_000was read as1 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, withOFandNOWAIT, lock the rows they read until the transaction commits. The commit fails with 40001 if another commit changed one of those rows, or locked oneFOR 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 LOCKEDis 0A000.BEGIN/START TRANSACTION/SET TRANSACTION/SET SESSION CHARACTERISTICSwithISOLATION LEVEL SERIALIZABLE, andSET 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'(andSET timezone,INTERVAL '+05:30', numbers,LOCAL/DEFAULT,RESET,SHOW timezone, and theTimeZoneconnection parameter) used to returnSETand change nothing, sonow()printed in UTC andcurrent_datewas UTC's date: 5.5 hours off in India with no error. Nowtimestamptzprints and reads in the session's zone,current_dateis its date, and casts todate/timestamp/time, date comparisons,date_trunc,extractandto_charwork in its clock. Postgres clients get aTimeZoneParameterStatus when it changes.- Only zones with one offset all year (UTC, fixed offsets,
Etc/GMT±n, and zones such asAsia/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'sNEW.amount IS DISTINCT FROM OLD.amount). It was a syntax error.pg_advisory_lock,pg_advisory_xact_lock, theirtrykinds,pg_advisory_unlock[_all], andhashtext(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)inFROM, inIN (...), inWITH, andVALUES ...on its own (withORDER BYandLIMIT); columnscolumn1,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_collateC, ...); 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 withcurrent_setting(); andapplication_name, fromSETor 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_FLOATSsets the limit (0: every column 8-bit),CHRONOS_RERANKthe rows proposed per row asked for.