Chronos/DBWaitlist

Changelog / Chronos DB

Chronos DB 0.4.3

Postgres compatibility from running real apps (PostgREST and Supabase's setup, psycopg's server-side cursors, Django's `explain()`, deferred UNIQUE constraints, a third batch of PostgreSQL 16's regression suite), and agents on production data held tighter: masked columns that triggers can't leak, tenants that must name a real column, low-priority scans, and an attached Postgres's write-back you can rehearse and undo

Beta38 changesDownloads

Postgres compatibility from running real apps (PostgREST and Supabase's setup, psycopg's server-side cursors, Django's explain(), deferred UNIQUE constraints, a third batch of PostgreSQL 16's regression suite), and agents on production data held tighter: masked columns that triggers can't leak, tenants that must name a real column, low-priority scans, and an attached Postgres's write-back you can rehearse and undo.

Changed from 0.4.2:

  • A bare VACUUM or ANALYZE reads every table to record the planner's statistics (it did nothing before), and an agent that may only read can't run them any more.
  • An agent's tenant must name a column some table has: create_agent and ALTER AGENT ... SET (tenant = ...) refuse one that no table on main has, where a typo made an agent that saw every tenant's rows.
  • An agent without unmask can't UPDATE or DELETE rows whose trigger reads a masked column: the statement is refused (42501), where the trigger used to copy the value somewhere the agent could read.

The macOS builds need macOS 13 or later, as for 0.2.0.

  • A window frame's ROWS or GROUPS offset may read parameters. ROWS BETWEEN x PRECEDING AND x FOLLOWING, x a SQL function's argument (or $1), is worked out when the window runs, as Postgres allows; a column there is still refused (42P10). window.sql's unbounded_syntax tests pass.
  • VACUUM and ANALYZE record the planner's statistics, and joins can follow PostgreSQL's order. pg_class.relpages, reltuples and relallvisible were always 0, -1 and 0; now VACUUM and ANALYZE record them as Postgres would have them, the pages from a model of Postgres's heap (8 kB pages, each row laid out as Postgres lays it out), and so do CREATE INDEX and REINDEX, as Postgres's index build does. reltuples is a float4, and UPDATE pg_class SET reltuples = ... is kept too. TRUNCATE, and an ALTER TABLE Postgres rewrites the table for, reset them; ANALYZE of a partitioned table records its partitions' rows; VACUUM (ONLY_DATABASE_STATS) records none. A bare VACUUM or ANALYZE, which did nothing, now reads every table to count its rows (writers go on meanwhile). They write the world, so an agent that may only read can't run them any more. A new setting, chronos.follow_pg_plan, joins the tables of a query PostgreSQL 16 can only run as nested loops (cross joins, and joins on conditions such as a.x < b.y or geometric operators) in the order Postgres's planner would pick from those statistics, so their rows come out in Postgres's order. The same rows are read in any order, but with three tables or more the work done can differ. It is computed by a port of Postgres's cost model and join search (src/sql/opt, under the PostgreSQL License: see NOTICE), and anything it doesn't model exactly keeps Chronos's own order; a table whose rows were deleted, updated or loaded by COPY takes pages in Postgres that Chronos doesn't count, and may be ordered differently. On in exact mode (CHRONOS_PG_EXACT=1), off otherwise. See join order.
  • Postgres compatibility: a third batch of the PostgreSQL 16 regression suite. EXPLAIN takes FORMAT JSON, XML and YAML, VERBOSE, SETTINGS, SUMMARY and GENERIC_PLAN (BUFFERS and WAL show zeros), and fails on the data errors Postgres's planner meets (1/0 in a constant part); catalog tables have a ctid; indexes keep their access method, DESC/NULLS FIRST, INCLUDE columns and operator classes, and the index property functions (pg_index_column_has_property and its kin) answer from them; CREATE ACCESS METHOD, DROP ACCESS METHOD and table access methods (USING, default_table_access_method, ALTER TABLE ... SET ACCESS METHOD).
  • Every encoding Postgres has converts, as Postgres converts it. convert_to and convert_from knew UTF8, LATIN1 and SQL_ASCII; they and the new convert(bytes, from, to) now take all 42 of Postgres's encodings (EUC_JP, SJIS, GB18030, Big5, KOI8R, the WIN and ISO 8859 sets...), by a port of Postgres 16's own conversion code (src/common/wchar.c, conv.c and the 84 functions of conversion_procs, with their generated maps converted by tools/pg_unicode_maps.py), with its messages and SQLSTATEs (invalid byte sequence for encoding "UTF8": 0xe8 0xb1 0xff, 22021; character with byte sequence ... has no equivalent in encoding ..., 22P05). pg_char_to_encoding takes every alias (unicode, win, mskanji...). CREATE [DEFAULT] CONVERSION checks its encodings and function as Postgres does, and refuses a second default for a pair in a schema. The regression suite's conversion.sql passes: its test_enc_setup and test_enc_conversion (regress.c) run as their C does.
  • An aggregate in a subquery of only the outer query's columns is the outer query's, as in Postgres. HAVING EXISTS (SELECT 1 FROM b WHERE sum(DISTINCT a.four) = b.four) sums the outer group, and SELECT (SELECT count(*) FILTER (WHERE outer_c <> 0) FROM ...) FROM t1 makes the outer query aggregate (one row); these failed with an aggregate of only the outer query's columns in a subquery is not supported. In a WHERE's subquery or a subquery in FROM they are still refused.
  • Attribute notation, functional notation and nested set-returning calls, as Postgres reads them. p.hobbies, where p has no column hobbies but a function hobbies takes its row, calls hobbies(p); (h.equipment).name likewise; name(x) is x.name where no function name takes x; and a set-returning function in a select list nested in another's arguments (name(equipment(p.hobbies))) runs first, a level each, as Postgres's ProjectSet runs them. SELECT * beside such a call is the FROM items' columns only. The regression suite's misc.sql goes from 58% to 98%.
  • pg_get_catalog_foreign_keys() and ten more hash functions, ported from Postgres 16. pg_get_catalog_foreign_keys() lists the 219 foreign keys between the system catalogs, generated by Postgres's own genbki.pl from the catalog headers (tools/pg_catalog_fks.py), so the regression suite's oidjoins.sql checks every one. time_hash, timetz_hash, timestamp_hash, uuid_hash, pg_lsn_hash, hashinet, hashmacaddr, hashmacaddr8, hashoidvector and hash_numeric, and their extended forms (with a seed), give a real server's numbers.
  • Object addresses for aggregates, text search objects, transforms, subscriptions and Postgres's own casts. pg_get_object_address, pg_identify_object and pg_identify_object_as_address found no oid for an aggregate made with CREATE AGGREGATE, a text search parser, dictionary, template or configuration made with CREATE TEXT SEARCH, a subscription, a domain's constraint, a built-in cast or a member of a built-in operator family (operator 1 (integer, integer) of pg_catalog.integer_ops USING btree); each now has one, and names it as Postgres does. CREATE TRANSFORM and DROP TRANSFORM are taken (kept as the type and language: no language here runs transforms). CREATE TEXT SEARCH PARSER takes its end function, and a text search object is in the schema it was made in. DROP SCHEMA ... CASCADE drops the schema's aggregates and text search objects too, listed in the order they were made. CREATE SUBSCRIPTION ... WITH (connect = false) gives Postgres's warning that the subscription isn't connected.
  • PL/pgSQL: a polymorphic function's CONTEXT names it as declared, and an expression's subquery reads the catalog. An error in a call of foreach_test(anyarray) gave CONTEXT: PL/pgSQL function foreach_test(integer[]) line 4 ..., the types of the call; it now names the function as Postgres does, foreach_test(anyarray). RETURN (SELECT ... FROM pg_class) and other expressions whose subqueries read a catalog table failed with relation "pg_class" does not exist; they now read it.
  • The catalog's rows name each other as Postgres's do. Postgres's own catalog tables and system views (pg_proc, pg_type, pg_roles, ...) are rows of pg_class at the oids Postgres gives them, so what pg_description, pg_depend and pg_init_privs name by classoid is there; a table's reltoastrelid names its TOAST table, a row of pg_class in the pg_toast schema (now in pg_namespace); and the input and output functions of hstore's and ltree's types are rows of pg_proc. The regression suite's oidjoins, which checks every foreign key pg_get_catalog_foreign_keys lists, finds no violations.
  • GRANT ... ON DATABASE postgres (and template1) grants on main, the world a connection to those maintenance databases is in, as Supabase's init scripts expect; it was refused as naming another world.
  • contrib/bloom: CREATE EXTENSION bloom is accepted, and CREATE INDEX ... USING bloom (...) WITH (length = ..., col1 = ...) (Django's BloomIndex) makes an index kept as a btree on its columns. Its parameters are checked as bloom checks them, its columns need bloom's int4 or text operator classes, and pg_am lists bloom once an index uses it.
  • psycopg's server-side cursors work. A cursor made with SQL DECLARE is a portal of its name, as in Postgres, so the extended protocol's Describe of it gives its columns; psycopg 3's named cursors (Django's .iterator()) describe theirs so, and failed with portal "..." does not exist.
  • Deferred UNIQUE constraints. UNIQUE ... DEFERRABLE INITIALLY DEFERRED, and DEFERRABLE with SET CONSTRAINTS ... DEFERRED, let a transaction hold a key on two rows until it commits (swapping two rows' positions), as in Postgres; the COMMIT fails with 23505 if two rows still hold it, and SET CONSTRAINTS ... IMMEDIATE checks what was put off at once. INITIALLY DEFERRED was refused (0A000), which stopped Django's and Rails' test schemas from loading. pg_constraint and pg_get_constraintdef show the deferral.
  • Built-in functions take GRANT and ALTER FUNCTION ... OWNER TO (GRANT EXECUTE ON FUNCTION pg_catalog.pg_reload_conf() TO postgres, ALTER FUNCTION pg_catalog.lo_export OWNER TO supabase_admin, as Supabase's migrations run them); they were function ... does not exist. Every role still runs a built-in function, so REVOKE on one is refused (0A000) rather than recorded to no effect.
  • ALTER TABLE ... DISABLE TRIGGER ALL turns off foreign key checks: as Postgres's RI_ConstraintTrigger triggers are, until ENABLE TRIGGER ALL (a superuser's to do, 42501 otherwise). A row the table writes isn't checked against what its foreign keys reference, and a row of it deleted or changed doesn't touch rows that reference it. Rails loads its test fixtures this way; they were refused with 23503.
  • Arrays cast to oidvector and int2vector: ARRAY[...]::oidvector (a regtype[] too) is a vector written as one, 25 1043, so it compares with pg_proc.proargtypes. Rails' case-insensitive uniqueness validation asks so (it was refused, or never equal).
  • Generated columns and CHECKs: table-qualified columns and SQL functions. A generated column may name its table's columns with the table's name (LEAST(users.email_confirmed_at, users.phone_confirmed_at) in auth.users, as Supabase Auth's migrations write it); it was missing FROM-clause entry for table "users". And a generated column or a CHECK may call a SQL function made here whose body is one SELECT expression (Supabase Storage's storage.get_level(name)): its body is put in its place, as Postgres inlines such functions (a STRICT one's is null when an argument is). Such a generated column was refused as not immutable, and a CHECK of one failed with functions made with CREATE FUNCTION run in queries. A PL/pgSQL function there still is refused.
  • Dropping a primary key column drops the key: ALTER TABLE ... DROP COLUMN id takes the primary key with it, as in Postgres (it was refused). Foreign keys that reference the key keep it (2BP01) unless CASCADE drops them too. Rails' legacy migrations remove id columns this way.
  • NOTIFY in a PL/pgSQL function is pg_notify's call, delivered as the transaction commits; it was refused (PostgREST's schema-reload event triggers, NOTIFY pgrst, 'reload schema', need it).
  • The empty text is a primary key: a row whose one-column text key is '' is kept, found, referenced and changed as any other (it was refused as "a key can't be empty"). Rails' migration tests write schema_migrations version ''.
  • pg_extension lists the built-in extensions once they're made: CREATE EXTENSION hstore (and citext, ltree, pg_trgm, fuzzystrmatch, unaccent, btree_gin, btree_gist, uuid-ossp, intarray, tablefunc, pg_stat_statements, vector, postgis) is recorded as pgcrypto's is, with its schema and its Postgres 16 version, so Rails' extensions and schema dumps and Django's CreateExtension see it. As in Postgres, making one again is 42710 (IF NOT EXISTS skips it), another version is 22023, and DROP EXTENSION of one never made is 42704. Their types and functions are built in whether or not they're made.
  • date_trunc of an interval, and of a time: date_trunc('hour', interval) cuts it as Postgres's interval_trunc does (a week is 0A000: months have fractional weeks), and a time of day is cut as the interval it casts to, as Postgres finds it. Django truncates TimeFields and DurationFields this way; both were refused.
  • ALTER SEQUENCE ... AS a serial column's own type: a serial column's sequence takes AS the type its column has (it was refused as anything but RESTART). Django changes an AutoField to a BigAutoField or SmallAutoField by altering the column, then ALTER SEQUENCE IF EXISTS t_id_seq AS bigint.
  • UNIQUE on real and double precision columns. A UNIQUE constraint or unique index naming a float column was refused (UNIQUE on double precision columns isn't supported); Django's unique_together of a FloatField makes one. It is now taken, with Postgres's equality: -0 and 0 are one key, every NaN is one key, and NULLs are never the same.
  • EXPLAIN (SELECT ...) UNION (SELECT ...), as Django writes a union's QuerySet.explain(), was read as an option list and refused with unrecognized EXPLAIN option "select"; a ( followed by SELECT, VALUES, WITH, TABLE or another ( now opens the query, as in Postgres, where no option has a reserved word's name. Each part of a UNION, INTERSECT or EXCEPT shows its own scan in the plan, where reads of one table in several parts were shown as one.
  • set_config() of TimeZone, search_path and statement_timeout sets them, as SET does, once the statement is done; it returned the new value but left the session's setting as it was. Django connects with SELECT set_config('TimeZone', %s, false), so its sessions stayed in UTC: a timestamp written from an aware datetime was off by the zone's offset, and the server never sent the TimeZone ParameterStatus the driver checks. set_config('TimeZone', ..., true) lasts until the transaction ends; set_config(..., true) of search_path or statement_timeout still sets nothing.
  • An index or UNIQUE constraint a DO block or function makes is there for what it does next: a foreign key to a UNIQUE constraint added just before in the same block (GoTrue's refresh_tokens_parent_fkey migration), or a query of pg_constraint or pg_class, found it missing until the statement ended.
  • PL/pgSQL runs a WITH whose queries write: WITH ins AS (INSERT ...) SELECT ... INTO ... in a DO block or function was refused ("this statement isn't supported in PL/pgSQL functions"). Supabase Storage's prefix backfill migrations use one.
  • What PostgREST and Supabase's setup ask of the catalog and the session, found by running the open issues of Doltgres, another Postgres-compatible database, against this one:
    • set_config('role', r, true), which PostgREST runs at the start of every request, is SET LOCAL ROLE r (and with false, SET ROLE) once its statement is done; it was unrecognized configuration parameter "role". A role there isn't is 22023.
    • ALTER DATABASE d SET x = v, RESET x and RESET ALL: every session connecting to the database takes the setting, under its role's; it was 0A000. ALTER ROLE chronos SET (or postgres) gives a setting to every login that is no role; it was refused for the bootstrap superuser. pg_db_role_setting lists both kinds.
    • information_schema._pg_truetypid, _pg_truetypmod, _pg_char_max_length, _pg_char_octet_length, _pg_numeric_precision, _pg_numeric_precision_radix, _pg_numeric_scale and _pg_datetime_precision, which PostgREST reads columns' types with, and pg_is_other_temp_schema(oid), which its schema discovery asks of every schema.
    • pg_tables.tableowner, pg_views.viewowner, pg_matviews.matviewowner and pg_sequences.sequenceowner name the relation's owner after ALTER ... OWNER TO; they always said chronos, while pg_class.relowner had the new owner.
  • Masked columns: a person's trigger no longer hands an agent the values. A trigger that reads a masked column of the rows it fires for (NEW.email, OLD.*, row_to_json(OLD), a WHEN that tests one, or its transition tables) doesn't fire for the UPDATE or DELETE of an agent without unmask: the statement is refused (42501), where before the trigger copied the value as it is to a table the agent could read. An agent's INSERT fires it as ever (its NEW is the agent's own row), as does any trigger that reads only columns that aren't masked; SHOW AGENT name PRIVILEGES names the triggers on each masked column. A tsvector_update_trigger column made from a masked column is masked too. Closes the last gap listed in Abhishekxdg/Chronos-DB#45.
  • An agent's tenant must name a column some table has. create_agent (and CREATE AGENT ... WITH (tenant = ...), ALTER AGENT ... SET (tenant = ...)) refuses a tenant whose column no table on main has, naming the column and each table with its columns: an agent is held to its tenant only in tables with that column, so a typo such as {"nosuch": 1} made an agent that saw every tenant's rows. Tables without the column are still shared lookup tables, read whole, as long as one table has it; a database with no tables yet takes any tenant.
  • Attach: a restore you can rehearse, and undo that goes back through Postgres (Abhishekxdg/Chronos-DB#48):
    • MERGE WORLD w REHEARSE [FAIL AT n] runs the merge's whole write-back in the attached Postgres (rows locked and compared, each statement with RETURNING, triggers, constraints, SET CONSTRAINTS ALL IMMEDIATE, the merge checks), then ROLLBACK. The plan lists each statement run, the values triggers and defaults set, tables written besides, the statement that failed and Postgres's error, a check that Postgres holds every row as before, and the effects the merge would send (none are sent). FAIL AT n stops after the nth statement to show the rollback. Refused in a folder that isn't attached.
    • UNDO AGENT ... SINCE and UNDO DIFF on main of an attached folder go back through Postgres as UNDO MERGE does; they were refused. With SKIP CHANGED, a row changed straight in Postgres since (not yet in main) is left and the rest goes back; it was a conflict that undid nothing.
    • Undos leave effects (NOTIFY ON MERGE) alone, and list those the undone change sent, with where each is (sent, queued, claimed, ambiguous), as a WARNING and in HTTP undo_merge's effects_not_undone. Undoing a merge whose effect a worker had acknowledged was refused as a row changed since, and an effect still queued was deleted without a trace.
    • The attach guide has a restore drill: rehearse, fail halfway, merge, undo, with what each leaves in Postgres and the outbox.
  • An agent's priority = 'low' keeps its heavy scans from slowing the database's own users (Abhishekxdg/Chronos-DB#43). While a statement of a session no agent runs is running, a low-priority agent's statements yield: each of their threads sleeps three times as long as it ran at the checks the statement timeout uses (up to 50 ms at a time), and their parallel scans borrow no helper threads. With nobody else's statement running they run at full speed. Set it in CREATE/ALTER AGENT ... WITH (priority = 'low') or over HTTP; SHOW AGENTS shows it, and agent_yield_ms in SHOW METRICS counts the time given way. max_threads (0.4.0) still caps the cores one statement takes.
  • Stale reads: a hold is lifted when the read gives the same answer. A world forked with (rerun_reads = true), or by an agent whose merge policy has rerun_reads, keeps the plain SELECTs it runs (with their parameters and a hash of what they gave). A merge its reads would hold runs them again, as its session ran them, on its fork point and on main as it is (in a folder attached to Postgres, on Postgres's rows too): the same answer (a sum that came out the same, changes that cancel out, rows a filter found found again) and it isn't held; another answer and it's held, the reason saying what it gave then and gives now (it read select sum(amount) from orders where status = 'open', which gave 30 then and gives 31 now), in the error, SHOW REVIEWS and CHECK READS. Rows read any other way, a SELECT that can't be run again as it ran (a function of the session, row security, more than 10,000 rows, a read of the world's own writes or of the clock), one that takes more than 1 s again, and a restart since: held as found. Issue Abhishekxdg/Chronos-DB#38. SHOW MERGE POLICIES has a rerun_reads column.
Chronos DB 0.4.3 · Chronos