CockroachDB separates routine dependencies from implicit column work

A routine can prevent a column from being dropped even when its SQL never names that column. The dependency can arrive through a computed expression, a trigger or a constraint considered while the routine is created. CockroachDB 25.4.2, released on December 12, addresses several cases where those indirect references were recorded as unnecessary routine dependencies.

The release has an important qualification. Improved tracking is gated by use_improved_routine_deps_triggers_and_computed_cols, which is off by default in 25.4.2. Installing the patch does not by itself change how newly created routines record these references. The setting and the time the routine is created are part of the behavior being fixed.

I value database-enforced dependencies because stored SQL should constrain schema changes that would break it. False dependencies impose a different cost. They can force removal of a valid routine merely to change an unrelated part of a table's implementation.

A mutation contains more work than the routine explicitly requests

The implementation patch distinguishes explicit column references from expressions synthesized while building a mutation. Updating a table can require evaluating computed columns or checking constraints. That work follows the table's current schema, rather than necessarily expressing a permanent requirement in the routine's own SQL.

Previously, columns referenced by a computed-column expression could be added to the routine's dependency list. Trigger handling introduced related problems. A before trigger could change internal column identifiers and confuse the distinction between explicit and implicit insert columns. Statements inside a trigger could also contribute their dependencies transitively to the routine that caused the trigger to run.

The fix suppresses dependency tracking while building appropriate implicit expressions. Its code comment explains why this is safe for synthesized computed values. Those values are determined from the table schema. If the relevant column is removed, subsequent mutation planning no longer synthesizes that value.

That does not make the table's constraints disappear. It changes which object owns the reference. A trigger that genuinely refers to a column still has a dependency on it. The routine invoking a mutation does not need to retain an additional permanent copy of every dependency reached through that trigger.

The regression tests preserve the real dependency

One test creates a trigger that reads both x and y, then creates a procedure that inserts only into x. Dropping y initially fails because the trigger depends on it. After the trigger and its function are removed, the column can be dropped without also removing the procedure, and the procedure can still be called.

This sequence is stronger evidence than simply accepting a previously rejected DROP COLUMN. It demonstrates that the trigger's legitimate dependency continues to block the operation while the trigger exists. The improvement removes the procedure's unnecessary transitive dependency after that actual blocker has gone.

The same patch extends the correction to check constraints and partial-index predicates. The release explicitly includes checks associated with row-level security policies and hash-sharded indexes. These references can arise from machinery attached to a table even when the routine's mutation targets a different column.

The hash-sharded-index regression test makes that consequence concrete. It creates an index on col2 and a procedure that increments col1. After the procedure runs, the index can be dropped and the procedure can run again. An internal computed column associated with that index should not turn a procedure concerned with col1 into an obstacle to removing the index on col2.

A constraint example from the tests makes the boundary visible. The procedure inserts and updates a, while the table has a check on b. With improved tracking enabled before routine creation, removing the check and b does not require dropping the procedure:

SET use_improved_routine_deps_triggers_and_computed_cols = true;

CREATE TABLE routine_deps (
    a INT,
    b INT,
    CONSTRAINT positive_b CHECK (b > 0)
);

CREATE PROCEDURE change_a() LANGUAGE SQL AS $$
    INSERT INTO routine_deps (a) VALUES (1);
    UPDATE routine_deps SET a = a + 1;
$$;

ALTER TABLE routine_deps
    DROP CONSTRAINT positive_b,
    DROP COLUMN b;

CALL change_a();

The explicit constraint removal matters. This is not an example of bypassing a still-valid constraint reference. It demonstrates that the procedure should remain independent of a column used only by table-level work that has itself been removed.

Creation-time metadata determines later schema behavior

The patch describes preventing unnecessary dependencies for newly created routines. It does not describe a general repair pass rewriting dependency metadata for existing routines. Enabling the session setting should therefore not be presented as automatically cleaning every routine already present in a cluster.

This explains why creation context matters to migration tooling. A setting applied in one session does not establish how a routine created previously recorded its dependencies. The release's default-off gate must be accounted for in the session that constructs the relevant routine, rather than inferred from the server version alone.

I find the change useful because it preserves the reason for dependency enforcement while narrowing its scope. A routine's explicit references remain part of the schema contract. Expressions generated from the table's current implementation can change with that implementation, without making every incidental column a permanent requirement of the routine.