rails_query runs only on a database family it can guard
Status: accepted
rails_query promises two guards the database itself enforces: a read-only transaction and a statement timeout. Each database server spells both differently, and some cannot give one at all, so the tool runs a statement only where it knows the spelling. A database family is that knowledge: one per kind of database, told by the adapter's name, and the only place a database's read-only and timeout rules live. rails_query reads the name from the database's configuration, before any connection opens.
Decision
- Three families: PostgreSQL (PostGIS too), MySQL (Trilogy and MariaDB too) and SQLite. Any other adapter gets a refusal that names it before anything reaches the database. Statistics answers (table sizes, row estimates, index usage) say "not available" for it instead.
- A family is claimed by the adapter's name alone, as
adapter_nameand database.yml spell it. CockroachDB's adapter subclasses PostgreSQL's, but it has noEXPLAIN (FORMAT JSON)and its own read-only rules, so it is refused until someone verifies them. - The timeout per family:
- PostgreSQL:
SET LOCAL statement_timeoutinside the read-only transaction, at one millisecond or more, since zero turns the timeout off. - MySQL: the session's
max_execution_time, read first, set for the statement and restored after. TheMAX_EXECUTION_TIMEhint binds only the outermost SELECT and leaves a statement that starts with WITH or EXPLAIN with no bound, so it is not used. - MariaDB: the session's
max_statement_time, read first, set for the statement and restored after. MariaDB ignores the MySQL hint and has nomax_execution_time. - SQLite: a forked child with a deadline, since sqlite3-ruby holds the interpreter lock while a statement steps and nothing in the process can end it. A statement runs on the app's own connection in five cases: an in-memory database, a platform without
fork, a statement the child cannot prepare, a transaction open on that connection, and, while that connection holds a temp object or an attachment, a statement that names one of them, the temp catalog or schema, or calls a pragma function ordbstat. There a watchdog's interrupt ends a statement that returns rows slowly.
- PostgreSQL:
- What bounds each kind of statement a call sends:
- The query and
explain: truehavequery_timeoutto themselves:statement_timeouton PostgreSQL, the session's statement limit withlock_wait_timeouton MySQL and MariaDB, the child's deadline on SQLite. - The reads beside the query (the schema, the PostgreSQL plan with its
pg_proclookup, the column trace) run in a block the family bounds the same way. They share a secondquery_timeout, and each is charged the time it ran. On SQLite they run on the app's own connection with no statement limit, and a wait on another writer ends at its busy timeout. rails_runtime_info's table sizes, index usage and pending migrations each run in such a block withquery_timeoutof their own.
- The query and
- Stated limits of the bound:
- SQLite cannot end one long step on the app's own connection: an interrupt lands only between two rows. That run is reported as one without a time limit.
- PostgreSQL has no client read timeout.
statement_timeoutis the server's to keep, so a server that goes silent after it connects is not ended by the client. A leased MySQL or MariaDB connection gets aread_timeoutof twicequery_timeoutplus 5 seconds unless its entry sets one. - MySQL and MariaDB take
lock_wait_timeoutin whole seconds. It is set because their statement limit does not coverEXPLAINorSHOW, and a wait on a lock ends atquery_timeoutrounded up to the next second. - MySQL's statement limit holds
SELECTonly, so the gate refusesSHOWwith aWHEREclause on every family.
- A timeout is recognised by
ActiveRecord::QueryAborted(QueryCanceled, StatementTimeout) or by the database's native code. MariaDB's 1969 has no Rails class in any version. Message text is never read for a timeout, so a missing column named "timeout" is reported as an SQL error. PostgreSQL gives its timeout and a cancel from another session one SQLSTATE, so there the server's message is read to tell a cancel ("due to user request") from the timeout; a server that writes its messages in another language answers a cancel as the timeout. - The statement checks before execution stay on every family. MySQL's read-only transaction lets
FOR SHARE,LOCK IN SHARE MODE,INTO @varandINTO OUTFILEthrough, so it cannot be the only guard. - Only PostgreSQL traces a result column to its table column, through the statement's plan, so there a statement runs only with a plan. MySQL, MariaDB and SQLite have no such trace. There a view is checked by the definition the database stores and refused when it names a secret column, and a
*inside a subquery is refused when the statement names a table that holds one. A PostgreSQL materialized view stores its rows, so the plan traces none of its columns either, and it is held to the same view rule. - A guarded read leaves an open transaction as it found it, one the app opened with a raw
BEGINtoo: a savepoint on PostgreSQL, chosen by the server's own transaction status since Active Record does not count a rawBEGIN, and a refusal on MySQL and MariaDB, which cannot make an open transaction read-only and would commit it on aBEGIN. The server's refusal ofSET TRANSACTIONthere (1568) is read as that refusal, so noBEGINis sent. SQLite reads such a transaction on the app's own connection. - A statement that reads a setting the guard sets for it (PostgreSQL's
statement_timeoutandtransaction_read_only, the MySQL and MariaDB statement limit andlock_wait_timeout) answers the guard's value. The answer says so and gives the session's own value, read after the statement with no bound, since reading a setting waits on no lock.
Consequences
Apps on SQL Server, Oracle or CockroachDB have no rails_query. On SQL Server and Oracle the LIMIT its row cap appends is a syntax error, so a query without its own row limit fails there. Supporting another database means adding a family with its own read-only and timeout spelling, verified against a real server.
Alternatives not taken
- Running other adapters on the statement checks alone: the tool's description promises database-level guards, and a check on text is not one.
- Classifying by class ancestry: it would admit a database whose guards nobody has run.
- Injecting the MySQL hint after a WITH list: it needs a parse of the statement to find the outermost SELECT. The session variable needs none, and it bounds EXPLAIN ANALYZE too.