sql implemented read profiles/v1alpha1

Reads and catalogue reads implemented on PostgreSQL and MySQL

Rendered from adapters/sql/contracts/reads/v1alpha1/semantics.md by connectors-docs; the source owns every rule on this page.

This adapter owns the native behavior of its existing local implementation. Shared wire envelopes and admission remain in service v1alpha1. This relocation changes no runtime or selected codec.

SQL: PostgreSQL (and, below, MySQL) schema discovery and parameterized, bounded single-statement reads through a configured role/database. Database-enforced read-only transactions and an adapter-owned execution deadline are mandatory. Each transaction installs statement/lock timeouts as additional server-side guards. Caller-accessible functions can change those session settings, including between portal executions; they cannot extend the adapter's deadline. Schema scope is enforced by database grants, not a string-prefix SQL filter. Preserve column type metadata, NULL, arrays/JSON and numeric values without silently converting unsupported values into strings. The first postgresql-native-text profile encodes non-NULL cells as PostgreSQL's lossless text representation alongside each column's native type name; NULL is JSON null. Numeric precision, arrays, timestamps and JSON are preserved as native text rather than coerced to JavaScript numbers. Parameters are text or null with server-side type resolution. This representation is explicit in the descriptor. Report truncation explicitly; no invented resumable cursor for a fresh SQL query (a read profile's keyset cursor follows a column the operator declared, and is a new read after a value, not a resumed one). Each request uses a fresh connection and read-only transaction, initialized with search_path = public, pg_catalog; qualify names in other schemas. Session settings are not a substitute for the configured role's schema and function grants. No connection pool is claimed in this local profile. A configured CA bundle replaces public trust roots for SQL; empty bundles are refused. Do not advertise writes, transactions across requests, or dialect portability. PostgreSQL SQLSTATE 0A000 (feature not supported) returns unsupported, not unavailable. The statement runs as a derived table that closes on its own line; a syntax error (42601) answered to that wrapper, after the statement itself prepared, means the statement is not a query (DELETE … RETURNING, EXPLAIN, SHOW) and returns unsupported (see how a statement that is not a read is refused). Source facts: https://www.postgresql.org/docs/current/sql-set-transaction.html and https://www.postgresql.org/docs/current/runtime-config-client.html. Source access: 2026-09-08.

Native deadlines and bounds

Within the shared 15 s provider budget (connect <=5 s), execution is at most 10 s after connection, with up to 2 s reserved for cancellation/cleanup. Slow connection reduces remaining execution time. Initial statement and lock timeouts are 10 s and 2 s. Native limits are 1,000 rows and 8 KiB query text; callers may request smaller row limits, but configuration exposes no ceiling overrides.

An independent execution deadline triggers backend-keyed cancellation on failure, deadline or dropped invocation, using the established endpoint and captured TLS trust. The supervisor drives cleanup for at most 2 s if the caller disappears, then closes the connection. Cancellation has no acknowledgement; an unreachable database, process crash or shutdown may prevent backend termination. Finite local wait does not guarantee zero query cost or remote termination.

MySQL engine

A connection whose configuration names "engine": "mysql" serves the same operations, including the catalogue reads below, with the same input bounds, deadlines and output shape over the MySQL wire protocol; a configuration without engine is PostgreSQL, unchanged, and its effective configuration (and so its revision) carries no engine member. The instance descriptor names the profile mysql-native-text for every operation.

Each request uses a fresh session made READ ONLY (SET SESSION TRANSACTION READ ONLY) and bounded (max_execution_time 10 s, lock_wait_timeout and innodb_lock_wait_timeout 2 s), with its time_zone set to +00:00, before the caller's statement is prepared. A statement must return between one and 256 columns, so a statement that returns no rows is refused as unsupported before execution; it then runs as a derived table with positional column aliases and LIMIT <limit+1>, so only one result-returning statement runs and truncation is reported without draining the result. A statement that prepares with columns but cannot be a derived table (SHOW, EXPLAIN), which the server refuses with error 1064 on the wrapper, is unsupported, as on PostgreSQL. A table write the read-only session refuses (error 1792), including one inside a stored function, is forbidden. The guarantee is that read-only session plus one result-returning statement; it does not prevent the side effects of a routine the statement calls. A function the configured user may execute runs with its own rights: on a live MySQL 8.0.46 a definer-rights function called through query.read ran SET PERSIST and SET GLOBAL, and the change persisted. The configured user's grants are therefore the boundary for routines: grant SELECT only, and no EXECUTE on a routine with side effects. Parameters are text or null, bound positionally.

mysql-native-text writes every non-NULL cell as a JSON string by its column's family, never coercing a database value to a JSON number: integers (signed, unsigned, YEAR) as decimal text; DECIMAL as the server's exact text; floating values as the shortest text that reads back as the same value (positional on a tie, otherwise the exponent form 1e300, 1e-4; PostgreSQL writes 1e+300); DATE as YYYY-MM-DD; DATETIME, which records no time zone, as the naive local date-time YYYY-MM-DDTHH:MM:SS with as many fractional digits as the column declares and no offset; TIMESTAMP, an instant, as that instant in UTC in the same form followed by Z (the session time zone is UTC, so the server converts it before sending it, whatever its own time_zone; two distinct instants are never written alike across a daylight-saving change); TIME as [-]HH:MM:SS[.ffffff] with unbounded hours (an elapsed time, not a time of day); binary-charset strings, BIT and geometry as padded standard base64; other strings, ENUM, SET and JSON as their text. A text cell that is not valid UTF-8 is refused as unsupported, not replaced. NULL is JSON null. Column metadata carries MySQL's lower-case type name, with unsigned for unsigned integers. The adapter's ESS model (adapters/sql/spec/ess, connectors_sql.reads) fixes these rules.

A connection is bound to one database on both engines, and on MySQL a schema is a database, so schema.list on MySQL reads only the connected database: schema may be omitted, which reads it, or name it exactly; any other value is refused as invalid_input before a session is opened or any query sent. It reads information_schema.COLUMNS for that database, returning the PostgreSQL path's seven columns, and the provenance resource names it; udt_name carries MySQL's COLUMN_TYPE. On PostgreSQL schema remains required (an input without it is invalid_input) and names a schema inside the connected database.

The connection uses only its configured endpoint (never a server-named Unix socket), installs no local-file handler, and without allow_plaintext requires TLS before any credential is sent; a configured CA bundle replaces the public roots and is refused when empty. The adapter's own deadline or a dropped invocation kills the statement with KILL QUERY <connection id> from a second session within the 2 s cleanup budget, then closes the session; as on PostgreSQL this does not guarantee remote termination. MySQL errors are classified by number where the SQLSTATE class is too coarse (access refusals 1044, 1142, 1143, 1227 and 1370 are forbidden, 1045 unauthorized, 1235 unsupported, 3024 timeout and 1040/1203 capacity, the last two as the server's answer), and otherwise by SQLSTATE as above. MySQL 8.0 or later is assumed; MariaDB is not claimed. Source facts: https://dev.mysql.com/doc/refman/8.4/en/set-transaction.html, https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html, https://dev.mysql.com/doc/refman/8.4/en/derived-tables.html, https://dev.mysql.com/doc/refman/8.4/en/information-schema-columns-table.html, https://dev.mysql.com/doc/refman/8.4/en/kill.html and https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_binary_resultset.html. These MySQL pages were cited, not fetched, when this section was written (2026-10-09); the wire behavior is checked against a scripted fixture. The ignored live cases in adapters/sql/tests/ ran against disposable MySQL 8.0.46 and PostgreSQL 17.11 servers on 2026-10-09; they are not part of the default suite.

Catalogue reads

Four further reads describe the catalogue on both engines: database.list, table.list, table.describe and index.list. Each is a declared read (effect: read in the local bootstrap) and runs one fixed statement per engine through the query.read path: the same fresh read-only transaction (PostgreSQL) or READ ONLY session (MySQL), deadlines, limit of 1 to 1,000 rows, byte limit and explicit truncated flag. The caller's schema and table are bound as text parameters and never written into the statement. Each answer has the relational result shape of query.read under the engine's profile, so every cell is native text or JSON null; integers are decimal text, and flags are YES or NO on both engines. The columns are fixed, in this order:

operation	input	columns
database.list	limit	database_name
table.list	schema, limit	table_name, table_kind (table or view), row_estimate
table.describe	schema, table, limit	column_name, native_type, is_nullable, column_default, ordinal_position, primary_key_position, foreign_key, referenced_schema, referenced_table, referenced_column
index.list	schema, optional table, limit	index_name, table_name, column_position, column_name, is_unique, is_primary

schema follows schema.list: on PostgreSQL it is required and names a schema of the connected database; on MySQL it may be omitted or name the connected database, and any other value is refused as invalid_input before a session is opened. database.list takes no schema: it lists names only and opens no other database. On PostgreSQL it reads pg_database, excluding templates, databases that refuse connections and those the role lacks CONNECT on; on MySQL it reads information_schema.SCHEMATA, which shows the databases the user holds a privilege on (all of them with SHOW DATABASES).

table.list reports PostgreSQL ordinary and partitioned tables as table and views and materialized views as view, and MySQL base tables and views. row_estimate is the engine's own estimate, not a count: pg_class.reltuples (null when the table was never analysed) or information_schema.TABLES.TABLE_ROWS (cached statistics, an approximation for InnoDB); it is null for every view.

table.describe answers one row per column in ordinal order. native_type is the full type (format_type on PostgreSQL, COLUMN_TYPE on MySQL), column_default the engine's own text of the default or null, and primary_key_position the column's 1-based place in the primary key or null. A generated column has no default: on PostgreSQL its generation expression (attgenerated) is not reported as column_default, as information_schema.columns does not report it, and MySQL's COLUMN_DEFAULT is null for it. A column in a foreign key carries the constraint name and the referenced schema, table and column paired with it, so a composite key appears on each of its columns; a column in several foreign keys has one row per key, ordered by constraint name. A row is one declared key: on PostgreSQL a key into a partitioned table is reported once, not once more per referenced partition (the internal clone constraints PostgreSQL keeps on the referencing table, whose parent constraint is on that same table, are left out), and a partition reports the key it inherits from its partitioned parent as its own. A table with no row is refused as not_found, whether it does not exist or the configured role or user can see none of its columns: the two are not told apart.

Foreign keys into what the caller cannot see differ by engine. On PostgreSQL a foreign key is reported only when the role holds USAGE on the referenced schema and some privilege on the referenced table (or is a member of its owner), as information_schema decides, and a column privilege on every referenced column, so no row names a column that table.describe of the referenced table hides; otherwise each column of the key is still described, without that key. On MySQL information_schema.KEY_COLUMN_USAGE decides: it lists a foreign key of a table the user can see and names its referenced schema, table and column even when the user holds no privilege on them, so table.describe can name a referenced database that database.list does not list, and a referenced column the user cannot read.

index.list answers one row per key column of each index, in index order, for one table or, without table, for every table of the schema; included (non-key) columns are not listed. An expression key part carries the engine's text of the expression in column_name. A table with no index, and a table that does not exist, are both an empty answer. On PostgreSQL an index follows column visibility: it is left out when any column it is built on is one table.describe hides (one the role holds no column privilege on and does not own through the table's owner). That covers a key column, an INCLUDE column and a column used in a key expression; a column used only in a partial index's predicate counts too, because PostgreSQL records expression and predicate columns alike (pg_depend). A whole-row reference (such as (t IS NOT NULL), in a key expression or the predicate) is built on every column of the table, so it counts as every column; PostgreSQL records no dependency for it, and the read finds it in the stored expression and predicate. PostgreSQL gives an index with no plain key column a dependency on the whole table (refobjsubid = 0), which counts as every column as well: an index on expressions alone, such as ((pub + 1)), is listed only to a role that owns the table or holds a column privilege on every column, even when the expression uses only visible columns.

MySQL cannot hold a table name that ends in a space, and its metadata collation (utf8mb3_bin) pads, so a lookup of orders would match orders. On MySQL, table.describe and index.list answer such a name not_found before any session is opened. A name the utf8mb3 metadata cannot represent (a supplementary character) makes the server answer error 3988 to the bound lookup; these reads answer it not_found, as no table can carry that name. query.read keeps the shared classification, under which 3988 (HY000) is unavailable. On PostgreSQL both kinds of name are ordinary names and are looked up as given.

Visibility is the engine's: on PostgreSQL a relation or column is listed when the role owns it (or is a member of its owner) or holds a privilege on it, as information_schema decides; on MySQL information_schema shows only what the user holds a privilege on. The adapter's ESS model (connectors_sql.reads.CatalogScope and the row types DatabaseRow, TableRow, ColumnRow and IndexRow) fixes these rules. The statements are checked on scripted wire fixtures of both engines. On 2026-10-09 the generated-column, foreign-key, index-visibility and MySQL name rules were also run against disposable PostgreSQL 17 and MySQL 8.0.46 servers (adapters/sql/tests/catalogue_adversary.rs and catalogue_adversary_2.rs, ignored by default). Source facts: https://www.postgresql.org/docs/current/catalog-pg-database.html, https://www.postgresql.org/docs/current/catalog-pg-class.html, https://www.postgresql.org/docs/current/catalog-pg-attribute.html, https://www.postgresql.org/docs/current/catalog-pg-constraint.html, https://www.postgresql.org/docs/current/catalog-pg-index.html, https://dev.mysql.com/doc/refman/8.4/en/information-schema-schemata-table.html, https://dev.mysql.com/doc/refman/8.4/en/information-schema-tables-table.html, https://dev.mysql.com/doc/refman/8.4/en/information-schema-key-column-usage-table.html and https://dev.mysql.com/doc/refman/8.4/en/information-schema-statistics-table.html, cited, not fetched, on 2026-10-09.

Read profiles

A connection may declare read profiles in its owner-only native file (read_profiles, at most 32, distinct ids). A read profile is the operator's: one read statement with the engine's placeholders ($1… on PostgreSQL, ? on MySQL), the names of its parameters in placeholder order, its output columns in statement order, each with a declared type (text, integer, timestamp or phone_number) and optional masking, the column that carries the call identifier and the column the keyset cursor follows. The repository holds only this shape and its validation, which the adapter's ESS model (connectors_sql.profiles, and the profile rules of connectors_sql.connection.LocalConfiguration) fixes; the composition admits a profile exactly when the model does. Profiles are part of the binding: the effective configuration carries them, so a changed profile is a changed configuration revision, and a file without profiles (or with an empty list) keeps the revision it had before profiles existed.

profile.read serves them. It is advertised only by a connection that declares a profile, is a declared read (effect: read) and needs no mutation profile. The instance descriptor publishes each profile's exact input and output as one alternative of the operation's input and output schemas, so operations describe shows the parameters a caller may pass and the typed columns it gets back. The caller names the profile, gives a value (text or null) for each declared parameter and nothing else, and may pass a cursor and a limit of 1 to 1,000. An unknown profile, an undeclared or missing parameter, or any other member is invalid_input; a cursor the checks below do not admit is stale_cursor. Both are refused before any session is opened. Values are bound as text parameters, never written into a statement.

A cursor is not signed, so the adapter cannot tell a cursor it issued from one built to look like it. What it checks is shape, context and type, and a cursor that fails any of them is stale_cursor. Shape: the token is base64url without padding of exactly the object {"profile", "context", "after"} or {"profile", "context", "after_base64"}, in the one form the adapter issues for its value (plain when JSON writes the value without an escape, otherwise base64url of its UTF-8 bytes), with a value of at most 8,192 bytes. Context: profile names this profile, and context is the digest of the instance's revision (which covers the connection's endpoint, database and user and the full definition of every profile), the profile's definition and the parameter values, so a cursor issued before any change to the profile, on another connection or for other values is stale. Type: the ordering column's declared type admits the value, as it must admit a returned one, so an integer cursor that is not an integer never reaches a cast that would read it as 0. A cursor that passes all three but was never issued carries a value of the right type for this read: the ordering column is never masked, so it only moves where a read the caller may make anyway starts. The longest token the adapter issues (a 64-character id and an 8,192-byte value that JSON must escape throughout) is 14,795 characters, inside the 16,384 the published schemas admit; an ordering value longer than 8,192 bytes refuses the page as unsupported rather than issuing a cursor the schemas would refuse.

A page runs the statement through the query.read path: the same fresh read-only transaction (PostgreSQL) or READ ONLY session (MySQL), deadlines and byte bounds, and the same refusals of a statement that is not a read (below) before it runs. A statement that returns other than its declared number of columns is unsupported, also before it runs. The statement runs inside the derived table with positional aliases, which closes on its own line on both engines (so a statement ending in a -- comment cannot reach the aliases), ordered by one expression E of the ordering column and asking for one row past the limit; a continuation compares the cursor by the same expression: … \n) AS result(c0,…) [WHERE E(result.c<k>) > E($<n+1>)] ORDER BY E(result.c<k>) LIMIT <limit+1> on PostgreSQL and … \n) AS result (c0,…) [WHERE E(c<k>) > E(?)] ORDER BY E(c<k>) LIMIT <limit+1> on MySQL, where the cursor's value is bound after the statement's own parameters. E is chosen by the declared type and the column's native type when the statement is prepared, before any row is read (connectors_sql.profiles.KeysetOrdering):

engine	declared type	native column	E(x)
PostgreSQL	text, phone_number	any	x::text COLLATE "C": the returned text, by its bytes
PostgreSQL	integer	int2, int4, int8	x
PostgreSQL	timestamp	timestamp, timestamptz	x
MySQL	text, phone_number	a text string (CHAR, VARCHAR, TEXT, ENUM, SET)	CAST(CONVERT(x USING utf8mb4) AS BINARY)
MySQL	integer	a signed or unsigned integer, YEAR	CAST(x AS DECIMAL(65,0))
MySQL	timestamp	DATETIME, TIMESTAMP	CAST(x AS DATETIME(6)), the Z of a TIMESTAMP cursor dropped (the session is UTC)

Every other pairing is refused as unsupported, naming the profile's column, before the page is read. Each admitted pairing orders exactly as the cursor compares, and two returned values are equal under E exactly when their texts are equal. That is why the rest are refused: MySQL writes a binary string, BIT and geometry as base64, whose order is not the order of their bytes; it writes floating, decimal, JSON, date and time values otherwise than its own text conversion; and an integer or timestamp declared over a text column orders as text, not as the declared type. ENUM and SET, ordered by their bytes rather than their index, are consistent. The text expressions also keep a case- or pad-insensitive collation from tying two distinct values.

A server, or a proxy in front of it, may return fewer rows than any LIMIT asks for, so the adapter never takes a short page for the end. A page that received more than limit rows returns limit of them; every page that received a row carries next_cursor, after the last row it returns; only a page that received none is complete with a null cursor. The ordering column must hold distinct values for the rows the statement returns. When a page received the row past the limit and that row shares the last returned row's ordering value, the page is refused as unsupported, naming the ordering column as not unique: continuing after that value would drop the row (connectors_sql.profiles.PageBoundary). A shared value inside a page is not a boundary and is served. On PostgreSQL a row past the limit whose cells exceed the row byte bound cannot be compared, and refuses the page as capacity, as the next page would. The adapter cannot see a row a server cap withheld: when a server returns fewer rows than asked and a withheld row shares the last returned value, the next page continues after that value and the withheld row is not returned. Uniqueness of the ordering column is therefore the operator's obligation; the check above catches it only where the adapter received the next row.

A statement that is not a read is refused before it runs, the same way on query.read and profile.read, and the classes match across the engines. A statement that returns no columns (an INSERT, UPDATE or DELETE without RETURNING, a SET, a CALL) is unsupported when it is prepared, on both engines. A statement that prepares with columns but cannot be a derived table is refused by the server's syntax check of the wrapper, which the adapter answers unsupported too: on PostgreSQL that is DELETE, UPDATE or INSERT with RETURNING, EXPLAIN and SHOW (SQLSTATE 42601 on the wrapper); on MySQL, which has no RETURNING, SHOW and EXPLAIN (error 1064 on the wrapper). A PostgreSQL data-modifying WITH is refused by the server as not at the top level (0A000), also unsupported. The caller's own statement refused at its own preparation keeps the server's class (a syntax error in it is invalid_input). A write reached at run time through a function the statement calls is refused by the read-only transaction (PostgreSQL 25006) or session (MySQL 1792), and is forbidden on both.

Each item carries the declared columns under their declared names, never the database's own. Every non-null value is a JSON string: text the engine's native text; integer decimal digits with an optional -; timestamp the engine's native text, which starts YYYY-MM-DD, T or a space, then HH:MM:SS; phone_number the number. A value its declared type does not admit refuses the page as unsupported; it is never coerced. A column is masked when the profile says so, and otherwise exactly when it is a phone_number; a masked value keeps its last four characters and writes * for each earlier one, and a value of four characters or fewer is all *. The call identifier and the ordering column are never masked, are not phone_number columns, and are never null: a null in either refuses the page as unsupported. The answer names the profile, its call identifier and ordering columns and each column's type and masking, so a workflow joins on the call identifier without a model. A page whose answer would exceed the response limit is capacity; ask for a smaller limit.

The scripted wire fixtures of both engines check these rules (adapters/sql/tests/profiles.rs, profiles_adversary.rs, protocol.rs and mysql_local.rs), against a synthetic call table (tests/mysql/calls.rs), including the SQL text of each keyset expression; tests/ess_model.rs holds the executable's admission, masking, pages, page boundaries, keyset orderings, issued cursors, cursor admission and refusals to the model. No live database has run a read profile yet.