CockroachDB 23.2 brings migration compatibility into transaction behavior

A PostgreSQL-compatible connection does not establish that a database will execute the same procedural code or make the same transaction-isolation choices. CockroachDB 23.2, released on February 5, addresses both issues. It adds stored procedures and PL/pgSQL capabilities, while introducing Read Committed isolation in public preview. These changes reduce distinct migration obstacles, with different conditions for using each.

Stored procedures bring database-side control flow closer to PostgreSQL's model. Read Committed addresses applications whose concurrency behavior was designed around statement-level snapshots and fewer serialization retries. Accepting the syntax of either feature is only the beginning. A migration also needs to preserve the procedural operations and consistency assumptions that the application actually depends on.

I value CockroachDB's default serializable isolation because it makes stronger consistency the ordinary starting point. PostgreSQL wire compatibility also makes adoption cheaper. The new release extends compatibility into areas where an application can otherwise discover substantial differences after it has already connected successfully.

Procedures execute a body through a single call

The 23.2 procedure documentation describes a named database object containing SQL or PL/pgSQL statements, invoked using CALL. The release highlights conditional logic, loops and exception handling. It also states that stored procedures and PL/pgSQL require an Enterprise license. Availability is therefore an explicit part of the feature description.

A procedure can collect several operations behind one named call. That changes where control flow is written and executed. An application no longer needs to represent every branch as a separate client-side exchange merely because the database lacks a procedural body. It does not follow that all PostgreSQL procedures can be transferred unchanged, since the supported body has documented restrictions.

The CREATE PROCEDURE reference supplies examples of conditional logic and composite variables. A small conditional procedure illustrates the supported shape without making claims about transaction control inside the body.

CREATE PROCEDURE compare_values(a INT, b INT)
LANGUAGE PLpgSQL AS $$
BEGIN
  IF a > b THEN
    RAISE NOTICE 'a is greater than b';
  ELSE
    RAISE NOTICE 'a is not greater than b';
  END IF;
END;
$$;
CALL compare_values(1, 2);

The body uses PL/pgSQL block structure, and the call supplies typed positional arguments. Dollar quoting avoids the additional escaping that ordinary single-quoted bodies would require for the embedded message strings. The example demonstrates control flow inside the procedure. It does not depend on nested routine calls or an internal commit.

Procedure compatibility has explicit boundaries

Version 23.2's documentation says a stored procedure cannot call another stored procedure or a user-defined function. It also does not support OUT and INOUT argument modes. These restrictions are relevant to porting a library of routines, because a simple standalone procedure can work while a composition of otherwise similar routines remains unsupported.

DDL statements such as CREATE TABLE and CREATE INDEX cannot appear in a stored procedure body in this version. Transactions cannot be run within stored procedures either. A procedural migration that includes schema creation or transaction control therefore needs more analysis than one that only uses supported SQL statements and local conditional logic.

Those limitations describe different parts of the procedural contract. Output argument modes affect how values return through the call interface. Nested calls affect composition. DDL and transaction restrictions affect what operations can occur inside the body. Grouping all four under a vague statement of partial compatibility would make it harder to identify which routine requires changes.

Privileges are explicit too. Defining a procedure requires CREATE on its schema, while resolving it requires schema USAGE and calling it requires EXECUTE. The reference also requires privileges on referenced objects at definition and execution time. Revoking such privileges can make a later call fail. Merely being able to invoke the routine is not the whole authorization model described here.

I find those limits more useful than a broad claim that procedural SQL now works. They let a migration distinguish a supported conditional routine from one whose structure depends on nested calls or internal transaction control. That is an architectural distinction within an application, not just a parser-compatibility question.

Read Committed changes which snapshot a statement sees

The 23.2 Read Committed documentation describes a weaker isolation level than the serializable default. Its intended uses include applications that need high concurrency with fewer retries and migrations where changing the application to handle serializable isolation is impractical. The tradeoff is that some concurrency anomalies become permitted.

Under Read Committed, a statement reads data committed before that statement began. A later statement in the same transaction can therefore observe a concurrent transaction's newly committed work. Repeating a read need not return the same values. A query that selects a set of rows can also return a different set after another transaction changes which rows satisfy the condition.

This is a behavioral choice rather than an optimization hidden beneath the same guarantees. Non-repeatable reads and phantom reads are documented consequences. A transaction can remain atomic while its successive statements observe different committed states. Moving to the weaker level changes what the application can infer from an earlier read within that transaction.

The documentation also describes write skew. Two transactions can each read a condition that appears to permit a change, then modify different rows in ways that jointly violate the intended condition. Neither transaction needs to overwrite the other's row. That is why evaluating only direct write conflicts is insufficient when judging whether an application's invariant remains safe.

Enabling the preview and requesting it are separate actions

Version 23.2 gates the feature with sql.txn.read_committed_isolation.enabled, which is false by default. Without enabling it, transactions requesting Read Committed are upgraded to Serializable. The requested SQL isolation name therefore does not by itself establish the effective behavior on this release.

The documented cluster setting and transaction syntax make the two steps visible. Enabling the setting makes the isolation level available, after which a transaction can request it explicitly.

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT 1;
COMMIT;

The example's constant query illustrates selection of the mode, not its concurrency effects. Demonstrating an anomaly requires interacting transactions and reads of shared data. The preview's documentation supplies such examples, including repeated reads and decisions based on multiple rows. A successful isolated query cannot establish that an application tolerates those interleavings.

The release notes also contain the earlier name sql.txn.read_committed_syntax.enabled and record its rename to the isolation setting. For the released configuration, the versioned documentation uses the latter name. This is a useful reason to read the final setting definition instead of extracting one intermediate entry from the development history.

Fewer serialization failures do not eliminate transaction failure

Read Committed aims to reduce retries caused by contention, but the documentation retains abort cases. Deadlocks and constraint violations can still abort transactions. A statement can also encounter a case where it cannot retry transparently after it has started sending a partial result to the client. Choosing the weaker isolation level does not remove every reason to handle a failed transaction.

Locking reads offer another way to coordinate access to rows. The documented FOR UPDATE pattern reserves rows while the current transaction makes its decision. That can prevent a competing transaction from changing them during the protected operation, while introducing waiting and potential deadlock concerns. It is an explicit coordination decision within the weaker isolation model.

The preview also excludes schema changes within explicit Read Committed transactions. The documentation identifies commands such as CREATE TABLE and CREATE INDEX as causing such transactions to abort, with Serializable as the supported alternative for that work. This restriction belongs to transaction execution even when a migration tool uses no stored procedures. A tool that sets one isolation level for every operation can therefore encounter a different limitation from an application that uses the preview only for ordinary data access.

Some locking operations also have a distributed execution cost. The versioned documentation says locks taken by foreign-key checks and locking reads are fully replicated under Read Committed, and some queries incur a Raft replication delay. Accepting weaker isolation does not mean that every coordination operation becomes local or free. The workload can trade fewer serialization retries for a different combination of waiting and replicated locking, which is consistent with retaining explicit consistency choices in a distributed database.

The two additions in 23.2 consequently solve different compatibility problems. Procedures provide supported database-side control flow, with specific composition and transaction limits. Read Committed provides a preview of different concurrency semantics, gated by cluster configuration. A migration can need either capability independently, and each changes an assumption that the PostgreSQL wire protocol alone cannot preserve.