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
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
VACUUMorANALYZEreads 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_agentandALTER 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
unmaskcan'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,xa 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. VACUUMandANALYZErecord the planner's statistics, and joins can follow PostgreSQL's order.pg_class.relpages,reltuplesandrelallvisiblewere always 0, -1 and 0; nowVACUUMandANALYZErecord 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 doCREATE INDEXandREINDEX, as Postgres's index build does.reltuplesis afloat4, andUPDATE pg_class SET reltuples = ...is kept too.TRUNCATE, and anALTER TABLEPostgres rewrites the table for, reset them;ANALYZEof a partitioned table records its partitions' rows;VACUUM (ONLY_DATABASE_STATS)records none. A bareVACUUMorANALYZE, 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 asa.x < b.yor 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: seeNOTICE), and anything it doesn't model exactly keeps Chronos's own order; a table whose rows were deleted, updated or loaded byCOPYtakes 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.
EXPLAINtakesFORMAT JSON,XMLandYAML,VERBOSE,SETTINGS,SUMMARYandGENERIC_PLAN(BUFFERSandWALshow zeros), and fails on the data errors Postgres's planner meets (1/0in a constant part); catalog tables have actid; indexes keep their access method,DESC/NULLS FIRST,INCLUDEcolumns and operator classes, and the index property functions (pg_index_column_has_propertyand its kin) answer from them;CREATE ACCESS METHOD,DROP ACCESS METHODand table access methods (USING,default_table_access_method,ALTER TABLE ... SET ACCESS METHOD). - Every encoding Postgres has converts, as Postgres converts it.
convert_toandconvert_fromknew UTF8, LATIN1 and SQL_ASCII; they and the newconvert(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.cand the 84 functions ofconversion_procs, with their generated maps converted bytools/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_encodingtakes every alias (unicode,win,mskanji...).CREATE [DEFAULT] CONVERSIONchecks 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: itstest_enc_setupandtest_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, andSELECT (SELECT count(*) FILTER (WHERE outer_c <> 0) FROM ...) FROM t1makes the outer query aggregate (one row); these failed withan 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, wherephas no columnhobbiesbut a functionhobbiestakes its row, callshobbies(p);(h.equipment).namelikewise;name(x)isx.namewhere no functionnametakes 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,hashoidvectorandhash_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_objectandpg_identify_object_as_addressfound no oid for an aggregate made withCREATE AGGREGATE, a text search parser, dictionary, template or configuration made withCREATE 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 TRANSFORMandDROP TRANSFORMare taken (kept as the type and language: no language here runs transforms).CREATE TEXT SEARCH PARSERtakes itsendfunction, and a text search object is in the schema it was made in.DROP SCHEMA ... CASCADEdrops 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)gaveCONTEXT: 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 withrelation "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 ofpg_classat the oids Postgres gives them, so whatpg_description,pg_dependandpg_init_privsname byclassoidis there; a table'sreltoastrelidnames its TOAST table, a row ofpg_classin thepg_toastschema (now inpg_namespace); and the input and output functions of hstore's and ltree's types are rows ofpg_proc. The regression suite's oidjoins, which checks every foreign keypg_get_catalog_foreign_keyslists, finds no violations. GRANT ... ON DATABASE postgres(andtemplate1) 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 bloomis accepted, andCREATE INDEX ... USING bloom (...) WITH (length = ..., col1 = ...)(Django'sBloomIndex) makes an index kept as a btree on its columns. Its parameters are checked as bloom checks them, its columns need bloom'sint4ortextoperator classes, and pg_am listsbloomonce an index uses it. - psycopg's server-side cursors work. A cursor made with SQL
DECLAREis 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 withportal "..." does not exist. - Deferred
UNIQUEconstraints.UNIQUE ... DEFERRABLE INITIALLY DEFERRED, andDEFERRABLEwithSET CONSTRAINTS ... DEFERRED, let a transaction hold a key on two rows until it commits (swapping two rows' positions), as in Postgres; theCOMMITfails with 23505 if two rows still hold it, andSET CONSTRAINTS ... IMMEDIATEchecks what was put off at once.INITIALLY DEFERREDwas refused (0A000), which stopped Django's and Rails' test schemas from loading.pg_constraintandpg_get_constraintdefshow 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 werefunction ... does not exist. Every role still runs a built-in function, soREVOKEon one is refused (0A000) rather than recorded to no effect. ALTER TABLE ... DISABLE TRIGGER ALLturns off foreign key checks: as Postgres's RI_ConstraintTrigger triggers are, untilENABLE 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
oidvectorandint2vector:ARRAY[...]::oidvector(aregtype[]too) is a vector written as one,25 1043, so it compares withpg_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)inauth.users, as Supabase Auth's migrations write it); it wasmissing FROM-clause entry for table "users". And a generated column or a CHECK may call a SQL function made here whose body is oneSELECT expression(Supabase Storage'sstorage.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 asnot immutable, and a CHECK of one failed withfunctions 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 idtakes the primary key with it, as in Postgres (it was refused). Foreign keys that reference the key keep it (2BP01) unlessCASCADEdrops them too. Rails' legacy migrations removeidcolumns this way. NOTIFYin a PL/pgSQL function ispg_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 writeschema_migrationsversion''. - 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'extensionsand schema dumps and Django'sCreateExtensionsee it. As in Postgres, making one again is 42710 (IF NOT EXISTSskips it), another version is 22023, andDROP EXTENSIONof one never made is 42704. Their types and functions are built in whether or not they're made. date_truncof 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 ... ASa serial column's own type: a serial column's sequence takesASthe type its column has (it was refused as anything but RESTART). Django changes an AutoField to a BigAutoField or SmallAutoField by altering the column, thenALTER SEQUENCE IF EXISTS t_id_seq AS bigint.- UNIQUE on
realanddouble precisioncolumns. A UNIQUE constraint or unique index naming a float column was refused (UNIQUE on double precision columns isn't supported); Django'sunique_togetherof aFloatFieldmakes one. It is now taken, with Postgres's equality:-0and0are one key, everyNaNis one key, and NULLs are never the same. EXPLAIN (SELECT ...) UNION (SELECT ...), as Django writes a union'sQuerySet.explain(), was read as an option list and refused withunrecognized EXPLAIN option "select"; a(followed bySELECT,VALUES,WITH,TABLEor 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()ofTimeZone,search_pathandstatement_timeoutsets them, asSETdoes, once the statement is done; it returned the new value but left the session's setting as it was. Django connects withSELECT set_config('TimeZone', %s, false), so its sessions stayed in UTC: atimestampwritten from an aware datetime was off by the zone's offset, and the server never sent theTimeZoneParameterStatus the driver checks.set_config('TimeZone', ..., true)lasts until the transaction ends;set_config(..., true)ofsearch_pathorstatement_timeoutstill 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_fkeymigration), 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, isSET LOCAL ROLE r(and withfalse,SET ROLE) once its statement is done; it wasunrecognized configuration parameter "role". A role there isn't is 22023.ALTER DATABASE d SET x = v,RESET xandRESET ALL: every session connecting to the database takes the setting, under its role's; it was 0A000.ALTER ROLE chronos SET(orpostgres) gives a setting to every login that is no role; it was refused for the bootstrap superuser.pg_db_role_settinglists 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_scaleand_pg_datetime_precision, which PostgREST reads columns' types with, andpg_is_other_temp_schema(oid), which its schema discovery asks of every schema.pg_tables.tableowner,pg_views.viewowner,pg_matviews.matviewownerandpg_sequences.sequenceownername the relation's owner afterALTER ... OWNER TO; they always saidchronos, whilepg_class.relownerhad 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 withoutunmask: 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 PRIVILEGESnames the triggers on each masked column. Atsvector_update_triggercolumn 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(andCREATE 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 withRETURNING, triggers, constraints,SET CONSTRAINTS ALL IMMEDIATE, the merge checks), thenROLLBACK. 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 nstops after the nth statement to show the rollback. Refused in a folder that isn't attached.UNDO AGENT ... SINCEandUNDO DIFFon main of an attached folder go back through Postgres asUNDO MERGEdoes; they were refused. WithSKIP 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 aWARNINGand in HTTPundo_merge'seffects_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 inCREATE/ALTER AGENT ... WITH (priority = 'low')or over HTTP;SHOW AGENTSshows it, andagent_yield_msinSHOW METRICScounts 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 hasrerun_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 readselect sum(amount) from orders where status = 'open', which gave 30 then and gives 31 now), in the error,SHOW REVIEWSandCHECK 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 arerun_readscolumn.