CockroachDB adds enforced read-only transaction mode

A session intended for reporting can accidentally issue a write unless the database enforces its read-only intent. CockroachDB's December 18 alpha adds that enforcement with PostgreSQL-compatible transaction syntax. The feature includes a mode for the current transaction and a session default for transactions started afterward, giving callers a way to express the intended behavior at either scope.

The contemporary release notes identify this as v2.0-alpha.20171218. It is a development release, not a statement that the final 2.0 database is available. The notes list SET TRANSACTION, session characteristics, and the corresponding read-only variables as supported interfaces.

I value explicit transaction intent because it makes an application's allowed work easier to inspect. CockroachDB's strong isolation model answers questions about concurrent operations, while read-only mode answers a different question, whether this transaction may modify data at all. The two properties should be described separately.

Transaction state and session defaults have different scopes

The implementation change, merged December 8, adds both session-level default state and current-transaction state. Its tests verify that the default begins off, can be enabled, and then supplies the initial read-only status for a new transaction.

For a caller that wants to mark one transaction explicitly, the supported statement can appear near its beginning. The following sequence inspects the selected mode before completing the transaction:

BEGIN;
SET TRANSACTION READ ONLY;
SHOW transaction_read_only;
COMMIT;

The implementation tests expect SHOW transaction_read_only to report on after the mode is set. They also test BEGIN READ ONLY, so the intent can be declared when opening the transaction rather than through a separate setting statement.

The session-level form changes the default for new transactions. Its companion variable reports the default, giving the caller a separate inspection interface from the current transaction's mode:

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
SHOW default_transaction_read_only;

The default and the current transaction are separate pieces of state. That distinction is important in a long-lived connection, where one session can execute many transactions. An application can establish a default once while still needing to know the mode of a particular transaction.

Read-only mode rejects operations with write effects

The added logic tests reject CREATE TABLE, INSERT, UPDATE, UPSERT, and DELETE in read-only transactions. This is behavior enforced by the database, not simply an accepted setting that tools can display. A write statement cannot proceed normally under that mode.

The tests also reject nextval() and setval(). These operations illustrate why identifying read-only work by the outer statement name is insufficient. A SELECT can invoke a function that changes sequence state. The relevant property is the operation's effect, not just whether the SQL begins with a familiar read keyword.

Rejecting sequence changes gives the mode a concrete meaning for callers using generated identifiers. An expression that allocates another sequence value is not a harmless read merely because the caller retrieves its result. The implementation tests cover that distinction directly.

This remains a transaction-mode feature. It does not identify a separate read-only replica or change the topology of the cluster. The documented statements configure execution intent on a session or transaction, so they should not be expanded into claims about routing reads to a particular replica.

A session default is not a permanent authorization boundary

The tests explicitly show that SET TRANSACTION READ WRITE overrides a read-only default. They also verify that BEGIN READ WRITE can do so. The default is therefore useful for establishing expected behavior, but it is not an immutable restriction on what the connected identity can ever do.

The release also supports setting transaction_read_only and default_transaction_read_only directly. These variable interfaces represent the same two scopes described by the transaction and session-characteristics forms.

That distinction separates accident prevention from privileges. A caller that legitimately changes the transaction mode can request write execution again. A session default alone should not be described as equivalent to removing write privileges from the database user.

I find the override behavior reasonable when stated plainly. A connection can default to read-only work while marking a deliberate write transaction explicitly. The application still needs a privilege policy appropriate to the identity using that connection, independent of its chosen default.

Compatibility also changes function result types

The same alpha changes date_trunc result types for PostgreSQL compatibility. With a TIME argument it now returns INTERVAL, and with a DATE argument it returns TIMESTAMPTZ. The notes also fix an incorrect timezone-bearing result for the TIMESTAMP form and a panic in distributed execution of some variants.

Those are application-visible type changes, separate from accepting transaction syntax. A client can depend on the type of a result as well as its printed value. The release also corrects aggregation memory accounting and a cancellation race that could leave queries hanging.

For read-only transactions, the defining behavior is now explicit in the SQL interface and its implementation tests. Session defaults supply the initial intent, transaction statements can override it, and operations with write effects encounter errors while the transaction remains read-only.