CockroachDB 23.1 makes supported SQL functions visible to the optimizer

A SQL function that looks like a small table lookup can become an expensive operation when the database invokes it separately for every input row. CockroachDB 23.1 can inline supported user-defined functions into a larger query plan. That gives the optimizer an opportunity to transform the function's relational work along with the surrounding query, instead of retaining a repeated function invocation as the execution boundary.

The May 15 release notes describe several UDF additions, including subqueries in statements, SELECT * expressions and SETOF results. They also identify inlining for supported functions and validation of function-body volatility. These changes make database-defined logic more usable, while leaving a distinction between what the language accepts and what the optimizer can inline.

I find CockroachDB's PostgreSQL protocol compatibility useful because it reduces adoption costs. SQL compatibility also depends on the expressions applications can define and how those expressions execute. The UDF work interests me because it develops that part of the programming model without requiring an application to abandon relational queries for every reusable operation.

A named function does not require opaque execution

The 23.1 UDF documentation defines functions at the database level with arguments, a return type, volatility and a language. SQL is the supported function-body language. Calls can appear in query contexts including SELECT, FROM and WHERE, so the same named operation can participate in larger relational expressions.

Inlining changes the execution representation of an eligible call. The documentation gives a function that looks up a value in another table, then compares using that function as VOLATILE and as STABLE. In the stable case, the plan incorporates a join with an equality condition. The function's table access becomes visible as relational work within the caller's plan.

That is more specific than assuming inlining merely removes a function-call instruction. For a table-accessing SQL function, it can allow the optimizer to arrange scans and joins across what had been a named function boundary. The opportunity comes from exposing the underlying expression while preserving the function's declared behavior.

The documentation includes timings for its example, but those are not a general performance ratio for applications. The structural difference is the relevant evidence. One plan invokes the function for each scanned row, while the other contains a join that performs the lookup relationally. Query shape and eligibility determine whether an application receives that transformation.

Volatility constrains transformations

CockroachDB uses volatility declarations to describe whether evaluating a function has effects or restrictions that the optimizer must respect. The UDF documentation says VOLATILE is the default and prevents pre-evaluation. It describes STABLE and IMMUTABLE functions as not mutating data. Changing that declaration is therefore a semantic commitment, rather than a performance hint with no effect on correctness.

The release notes add validation that statements in the function body satisfy the declared volatility. That connects the public declaration to the work actually defined by the function. An optimizer can rely on a property only when the implementation prevents the function body from violating it.

For inlining, the versioned documentation requires a non-volatile function containing one statement. It excludes set-returning and record-returning functions from this path and limits arguments to variable or constant expressions. These conditions explain why expanding the set of supported UDF definitions does not mean every new function form will be inlined.

A SETOF function illustrates the distinction. The release adds support for returning multiple rows, which broadens the logic that can be expressed. The inlining rules still exclude set-returning functions. An application can gain a useful new abstraction without gaining the particular optimization described for eligible scalar results.

Reusable SQL can keep its relational context

A table lookup is a useful example because its result depends on database contents. The 23.1 documentation uses STABLE for a function selecting from a table. Its demonstration does not require claiming that the result remains identical forever, which would be a stronger promise than the example needs.

The release's support for subqueries and star expressions also reduces restrictions on writing those bodies. This is language support, independent of whether a specific expression qualifies for inlining. The distinction helps explain the release as a programming-model change rather than a collection of syntax additions with an assumed universal speedup.

For diagnosis, a plan inspection can show what happened to a call. The following uses the function and table names from the documented example, assuming they have already been defined.

EXPLAIN ANALYZE
SELECT foo_s(a)
FROM a
WHERE a = 10;

The result of interest is whether the plan retains repeated function evaluation or incorporates the relational lookup. The statement executes the query while collecting execution information. It is an inspection tool for that defined example, not a standalone function definition or a promise that every similarly named function produces the same join.

My preference is for reusable SQL logic whose cost remains explainable in the caller's plan. Inlining can preserve that inspectability when its conditions hold. Where it does not apply, the application still needs to understand the cost of invoking the function across the selected rows.

Argument and result conventions remain part of that API. The documentation supports input arguments and permits referring to them by name or by ordinal expressions such as $1. It does not support default argument values. A function can return a built-in type, an enumerated type or a record form, but the optimizer eligibility rules still apply separately. These details matter for a ported SQL interface because a matching function name and parameter count do not establish support for its original defaults or return behavior.

Functions become part of more database operations

The release notes say UDFs are included in backup and restore operations. That matters because a database-level definition participates in the state needed to reconstruct an application's query environment. Recovering table data without the functions referenced by application queries would leave a different usable database, even if every row had been restored.

The notes also add declarative schema-changer support for CREATE FUNCTION and DROP FUNCTION. These operations therefore participate in the release's broader schema machinery. That is a lifecycle change surrounding UDF definitions, separate from the optimizer's choice to inline a call.

The feature highlights name UDF use in changefeed expressions and CHECK constraints, along with references from other objects. Those capabilities place reusable database logic in more contexts. Each context still needs its documented expression and volatility restrictions. The release announcement does not establish that every PostgreSQL function definition or every function-body language is accepted.

Multi-region feedback addresses a different execution cost

CockroachDB 23.1 also improves the developer experience around enforce_home_region. The release notes describe better optimizer feedback when a query lacks a home region or runs outside it. The option serves workloads that need execution within one region rather than accepting delays across regions.

This is related to UDF planning through the broader question of visible execution cost, but it solves a different problem. Inlining changes how the optimizer can represent a function's relational work. Home-region enforcement addresses the locality of the query. A plan can satisfy one requirement while still requiring attention to the other.

The release's external-connection work makes another boundary explicit. Named external connections separate resource configuration and permissions from operations such as backup, restore and changefeed creation. This is useful when SQL operations reach systems outside the database. The function additions do not erase those separate permission requirements.

Administrative visibility becomes more specific

The new VIEWJOB system privilege permits viewing all jobs without also granting job-control capabilities. The release notes contrast it with broader CONTROLJOB authority. That distinction fits the same practical requirement for inspectability that makes query plans valuable, while preserving the separation between observing an operation and changing it.

For a UDF-containing workload, several independently defined properties now matter. The database can store more function definitions and include them in recovery operations. Eligible calls can become part of a larger optimized query, while other accepted calls retain a separate execution boundary. Administrative tools can expose more context without requiring every observer to control the underlying jobs.

The significant result is a broader SQL programming model with specific optimization rules. A one-statement, non-volatile scalar function using suitable arguments can expose its table lookup to relational planning. A set-returning function gains language support through a different part of the release. Keeping those categories explicit lets an application use the new definitions without attributing the inlining behavior to every supported function.