CockroachDB 22.2 adds SQL functions and row-specific expiration

Moving a rule into the database is useful when the rule has a precise database representation. CockroachDB 22.2 adds named SQL functions and lets a table's expiration policy refer to row values. These changes reduce selected kinds of repeated application logic, but neither introduces an unrestricted procedural environment inside the database.

The December 6 release notes introduce user-defined functions and promote row-level TTL to general availability. TTL gains row-specific configuration and performance improvements. SQL functions provide a reusable expression boundary with documented restrictions on function bodies and invocation contexts.

I value CockroachDB's explicit transaction and placement model. Database-side rules appeal to me when their semantics remain equally inspectable. The useful question is which rule the database now owns, and what an application must still express separately.

SQL functions provide a named query expression

The user-defined-function documentation at the release date defines a function through its name, arguments, return type, volatility, language, and body. Version 22.2 supports SQL as the function language and permits calls in SELECT, FROM, and WHERE clauses of data-manipulation statements. For a small illustration, a function can return the sum of two integers.

CREATE FUNCTION add_values(a INT, b INT)
RETURNS INT
IMMUTABLE
LANGUAGE SQL
AS 'SELECT a + b';

SELECT add_values(3, 5);

The function returns eight for that call. Naming the operation allows a caller to invoke the same definition instead of repeating its SQL body. The arithmetic example demonstrates the syntax without implying that a two-operand addition needs a database abstraction in an ordinary application.

Arguments can be referenced by name or by ordinal, such as $1. The initial implementation supports input arguments and does not support default argument values. A migration from another database therefore needs to inspect the function signature as well as the body.

Function bodies can refer to tables, so the feature is not restricted to arithmetic over supplied values. The documentation shows aggregate queries over an application table. That gives a named database operation access to relational data, subject to the supported body syntax and privileges.

The definition can be inspected with SHOW CREATE FUNCTION. When creation does not specify a schema, the documented example uses public, and the displayed definition includes the argument modes and function properties. This makes inspection useful beyond recovering the text originally typed by its author. A caller can see the schema and properties the database actually associates with the name. The body can use a quoted single-line string or dollar quoting over multiple lines, but those choices only change how the definition is written. They do not expand the set of statements permitted inside it.

Volatility communicates a promise to the optimizer

The default function properties are VOLATILE and NOT LEAKPROOF. The documentation says volatility identifies side effects and prevents the optimizer from pre-evaluating a volatile function. A stable or immutable function does not mutate data. These annotations affect how the database can reason about an invocation.

Marking the example immutable is a claim about that expression. Its result depends on its supplied integer values. A function reading changing table contents needs a different assessment from a function performing arithmetic alone. Giving both the same annotation merely because their bodies use SELECT would omit the distinction the property expresses.

The leakproof property is narrower still. It promises that a function communicates nothing dependent on its arguments except its returned value, including through argument-dependent errors. The documentation requires an immutable function for that annotation. Reusability alone is not a reason to declare a function leakproof.

I prefer treating these properties as semantic commitments. They give the optimizer information it cannot infer safely from a convenient name. The benefit of a named function comes with the responsibility to describe its behavior accurately.

The first implementation has a defined scope

Version 22.2 permits only SELECT statements in a function body. Its published limitations exclude mutation statements, subqueries in statements, common table expressions, wildcard expressions such as SELECT *, and calls to other user-defined functions. A collection of nested database routines cannot be moved over unchanged just because CREATE FUNCTION now exists.

Invocation contexts also have limits. The release documentation excludes functions from table expressions involving columns, indexes, or constraints, and from views. It also excludes them from CDC transformations. Defining a reusable calculation does not make it available everywhere SQL can contain an expression.

Those restrictions matter to the feature's migration value. A function called by an application's top-level query can fit the supported model while a function embedded in an existing view does not. The dependency location changes feasibility even when the body is identical.

The CREATE FUNCTION documentation also defines privileges separately. Creation requires schema creation rights, resolving a function needs schema usage, and calling it requires execute permission. Referenced objects require their own privileges at definition and execution time. Revoking access to a referenced table can therefore make a later function call fail.

TTL can use an application-owned expiration value

The 22.2 TTL documentation introduces ttl_expiration_expression as the recommended way to define expiration. The expression must evaluate to TIMESTAMPTZ and can refer to columns in the same table. Each row can consequently carry its own expiration value. An illustrative table can name that value directly.

CREATE TABLE expiring_events (
  id INT PRIMARY KEY,
  expire_at TIMESTAMPTZ
) WITH (ttl_expiration_expression = 'expire_at');

This differs from the earlier fixed-interval convenience parameter. ttl_expire_after adds a hidden expiration column based on creation or update time plus an interval. A row-specific expression lets the application supply the expiration policy through its own values without treating every update as the same lifetime-reset operation.

The expression also becomes a schema dependency. A column it references cannot be dropped or have its type changed. Renaming the column updates the expression automatically. The retention rule is therefore part of the table definition, with consequences for later schema work.

That dependency is useful when different rows have different lifetimes. The database can identify eligible rows from the expression while the application records the intended expiration. The mechanism does not require pretending that every row was created or last updated at the business event time that actually determines retention.

The fixed-interval option also has a documented setup cost on existing data. Adding or changing it creates a new hidden expiration column and backfills its values using the current time plus the interval. That can affect performance on a large table. It also explains why changing a retention setting is not necessarily just changing a scheduler field. The expression-based form and the automatically managed column encode different ways of obtaining the timestamp, so inspecting the table definition identifies which mechanism the cleanup job will use.

General availability retains asynchronous deletion

TTL still selects expired rows and deletes them through scheduled background work. Passing the expiration timestamp makes a row eligible for removal. The job schedule determines when that work runs, with hourly execution as the documented default. General availability does not turn eligibility into immediate physical removal.

The launch-day limitations also say ordinary queries do not automatically exclude expired rows. That includes updates and deletes. Applications whose validity rules end at expiration still need read predicates expressing that boundary while cleanup removes the stored data afterward.

The worker retains separate selection and deletion batch sizes, with published defaults of 500 and 100 rows. A delete-rate parameter controls rows per second, and zero means no explicit configured rate limit. These settings describe how cleanup competes with other work rather than changing the expiration value itself.

Foreign-key references to or from TTL tables remain unsupported in the launch documentation. Its limitations also say TTL queries do not use indexes. Those properties continue to constrain suitable schemas and cleanup work even after the preview label has gone.

The two features express different kinds of rules

A SQL function provides named logic that a query can invoke. An expiration expression supplies a timestamp to a scheduled deletion mechanism. Their common database location does not make them interchangeable. In particular, the published restrictions on functions in table expressions mean a function's availability cannot be assumed inside every schema-level policy.

The practical gain is a more precise place for selected application decisions. A supported function centralizes a calculation or query behind a documented signature and privilege model. Row-specific TTL records when each row becomes eligible for cleanup through a typed expression. The application still owns invocation choices and immediate visibility semantics, while the database executes the supported function and scheduled deletion rules it has been given.