# What's New in v22.2

CockroachDB v22.2 is a required Regular Release.

Refer to Major release types before installing or upgrading for release timing and support details.

On this page, you can read about changes and find downloads for all production and testing releases of CockroachDB v22.2

- For key feature enhancements in v22.2 and other upgrade considerations, refer to the notes for v22.2.0.

- For details about release types, naming, and licensing, refer to the Releases page.

- Be sure to also review the Release Support Policy.

- After downloading a supported CockroachDB binary, learn how to install CockroachDB or upgrade your cluster.

Get future release notes emailed to you:

Thanks!

## v22.2.19

Release Date: February 26, 2024

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.19

### Changelog

View a detailed changelog on GitHub: v22.2.18...v22.2.19

### Bug fixes

- Previously, querying the `crdb_internal.leases` table could cause a node to become unavailable due to a deadlock in the leasing subsystem. This is now fixed. #119463
- Fixed a bug where rangefeed resolved timestamps could get stuck, continually emitting the log message `pushing old intents failed: range barrier failed, range split`, typically following a range merge. This bug was introduced in v22.2.18. #119560

### Contributors

This release includes 2 merged PRs by 2 authors.

## v22.2.18

Release Date: February 8, 2024

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.18

### Changelog

View a detailed changelog on GitHub: v22.2.17...v22.2.18

### Bug fixes

- Fixed a bug that could cause `DELETE` queries sent by the row-level TTL job to use a secondary index rather than the primary index to find the rows to delete. This could lead to some `DELETE` operations taking longer than expected. This bug was present since v22.2.0. #118420
- Fixed a bug where a changefeed could omit events in rare cases, logging the error `cdc ux violation: detected timestamp ... that is less or equal to the local frontier`. This could happen in the following scenario:
  1. A rangefeed runs on a follower replica that lags significantly behind the leaseholder.
  2. A transaction commits and removes its transaction record before its intent resolution is applied on the follower.
  3. The follower's closed timestamp has advanced past the transaction commit timestamp.
  4. The rangefeed attempts to push the transaction to a new timestamp (at least 10 seconds after the transaction began).
  5. This may cause the rangefeed to prematurely emit a checkpoint before emitting writes at lower timestamps, which in turn may cause the changefeed to drop these events entirely, never emitting them. #118633

### Contributors

This release includes 7 merged PRs by 3 authors.

## v22.2.17

Release Date: November 20, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.17

### Changelog

View a detailed changelog on GitHub: v22.2.16...v22.2.17

### Bug fixes

- Fixed a bug that could prevent `RESTORE` from working if it was performed during a cluster upgrade. #112757
- Queries with the `st_union` aggregate function could produce incorrect results in some cases due to the query optimizer performing invalid optimizations. This bug has been present since the `st_union` function was introduced in v20.2.0. #112796
- Previously, CockroachDB could incorrectly evaluate lookup and index joins into tables with at least 3 column families in some cases (either a `non-nullable column with no value` internal error would occur, or the query would return incorrect results). This is now fixed. The bug was introduced in v22.2. #113109
- Fixed a rare internal error in the optimizer that could occur while enforcing orderings between SQL operators. This bug had existed since before v22.1. #113175
- Previously, a warning that points to technical advisory #99561 could incorrectly surface when dropping secondary indexes that store primary key columns. This is now fixed. #113004
- Fixed a bug that could cause internal errors or panics while attempting to forecast statistics on a numeric column. cockroachdb/cockroach#113800
- Previously, when executing queries with index / lookup joins when the ordering needs to be maintained, CockroachDB in some cases could get into a pathological behavior which would lead to increased query latency, possibly by 1 or 2 orders of magnitude. This bug was introduced in v22.2 and is now fixed. #114368

### Contributors

This release includes 20 merged PRs by 14 authors.

## v22.2.16

Release Date: November 6, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.16

### Changelog

View a detailed changelog on GitHub: v22.2.15...v22.2.16

### Bug fixes

- Fixed a rare internal error in the optimizer, which could occur while enforcing orderings between SQL operators. This error has existed since before v22.1. #113640
- Fixed a bug where CockroachDB could incorrectly evaluate lookup and index joins into tables with at least three column families. This would result in either the `non-nullable column with no value` internal error, or the query would return incorrect results. This bug was introduced in v22.2. #113694

### Contributors

This release includes 2 merged PRs by 2 authors.

## v22.2.15

Release Date: October 23, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.15

### Changelog

View a detailed changelog on GitHub: v22.2.14...v22.2.15

### General changes

- The maximum permitted value of the `COCKROACH_RPC_INITIAL_WINDOW_SIZE` environment variable has been increased to `64MB`. By increasing this environment variable, in conjunction with tuning OS-level maximum TCP window size, you can increase the throughput that Raft replication can sustain over high latency network links. #111288

### Operational changes

- Added a new metric `changefeed.lagging_ranges` that shows the number of ranges which are behind in changefeeds. This metric can be used with the `metrics_label` changefeed option. The calculation of this metric is controlled by two new cluster settings: (1) `lagging_ranges_threshold`, with a default of 3 minutes, is the amount of time a range needs to be behind to be considered lagging, and (2) `lagging_ranges_polling_interval`, with a default of 1 minute, controls how often the lagging ranges calculation is done. Note that polling adds latency to the metric being updated. For example, if a range falls behind by 3 minutes, the metric may not update until an additional minute afterwards. Also note that ranges undergoing an initial scan for longer than the threshold are considered to be lagging. Starting a changefeed with an initial scan on a large table will likely increment the metric for each range in the table. However, as ranges complete the initial scan, the number of lagging ranges will decrease. #110970
- Added a new cluster setting `server.http.base_path` that controls the redirection of the browser after successful login with OIDC SSO. It is expected that most users should not have to adjust this setting. Adjust this setting in the case where the `cockroach` process is running behind a load-balancer or proxy that serves `cockroach` under a subpath such as `https://<hostname>/crdb/`. In which case, it is necessary for the browser to redirect to `/crdb` after login instead of `/` which previously was the hardcoded default. #111463

### Command-line changes

- The `cockroach debug zip` command now has an `--include-goroutine-stacks` flag which controls whether to fetch stack traces for all goroutines running on each targeted node in `nodes/*/stacks.txt` and `nodes/*/stacks_with_labels.txt` files. Fetching stack traces for all goroutines is a "stop-the-world" operation, which can momentarily have negative impacts on SQL service latency. Set `--include-goroutine-stacks` to `false` to not fetch stack traces in order to avoid negative impacts to SQL service latency. Note that any periodic goroutine dumps previously taken on the node will still be included in `nodes/*/goroutines/*.txt.gz`, as these would have already been generated and do not require any "stop-the-world" operations. #110258
- The `cockroach debug zip` archive now includes one file per node with information about the KV ranges stored on that node, in `nodes/{node ID}/ranges.json`. Previously, there was one file per range per node, in `nodes/{node ID}/ranges/{range ID}.json`. #110050
- The `cockroach debug zip` command now accepts an `--include-range-info` flag, which determines whether range information is retrieved in `nodes/{node ID}/ranges.json` files. This flag defaults to true. #110050

### DB Console changes

- The DB Console now constructs client-side requests using relative URLs instead of absolute ones. This enables proxying of the DB Console at arbitrary subpaths. #110444
- The SQL Connection Rate metric on the SQL Dashboard is downsampled using the MAX function instead of SUM. This improves situations where zooming out the time range would cause the connection rate to increase for downsampled data. #110496

### Bug fixes

- Previously, when memory was exceeded while executing the `EXPORT INTO PARQUET` statement, this resulted in out-of-memory crashes (OOMs). Now, if memory is exceeded, the `EXPORT INTO PARQUET` statement will return an error. If you see an error related to memory, retry the `EXPORT INTO PARQUET` statement using a smaller value for the `chunk_rows` option. Cockroach Labs recommends using changefeeds to export data because they provide better performance for growing workloads. Additionally, changefeeds operate as jobs, which offer observability and job management. #110718
- Fixed a bug that prevented table statistics from loading on the Databases page Tables view. #109921
- Fixed a bug that could cause a transaction performing multiple parallel foreign key checks to return a `concurrent txn use detected` error. #109850
- Fixed a bug where dependencies on sequences from tables would be reported with the wrong value for the `classid` column in the `pg_catalog.pg_depend` table. #110211
- Fixed edge cases in decimal and float evaluation for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0` instead of `0E-2019`. #110298
- The `cockroach debug pebble` command now works correctly with encrypted stores which do not use the default `cockroach-data` path without having to also pass `--store`. #110509
- When upgrading from previous releases, it was possible that ambiguous references to sequences would fail to be resolved by ID if they were in the same schema. Additionally, the logging is improved for ambiguous cases where the reference cannot be resolved. #110721
- Fixed a bug where `RESET`(`ttl_expire_after`) could incorrectly remove `ttl_expiration_expression`. #110750
- Fixed a bug where `format_type` builtin did not honor `typemod` information for array types, leading to incorrect output. #110939
- Fixed a bug where `indoption` inside `pg_index` was not properly encoded, causing it to be unable to be decoded as `int2vector`. #111956
- Fixed a bug where the optimizer failed to honor the `statement_timeout` session setting when generating constrained index scans for queries with large `IN` lists or `= ANY` predicates on multiple index key columns, which may lead to an out-of-memory crash (OOM) on the node. #112075
- Fixed a bug that caused internal errors during query optimization in rare cases. The bug has been present since version 2.1.11, but it is more likely to occur in version 21.2.0 and later, though it is still rare. The bug only presents when a query contains `min` and `max` aggregate functions. #112299

### Performance improvements

- The impact of high concurrency blind writes to the same key on goroutine scheduling latency was reduced. #110972

### Contributors

This release includes 77 merged PRs by 39 authors.

## v22.2.14

Release Date: September 14, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image, both Intel and ARM images are generally available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.14

### Changelog

View a detailed changelog on GitHub: v22.2.13...v22.2.14

### SQL language changes

- Added a new syntax to `SHOW DEFAULT PRIVILEGES`. Executing `SHOW DEFAULT PRIVILEGES FOR GRANTEE <grantee>` shows the default privileges that a grantee received. #108283

### General changes

- CockroachDB binaries and Docker images for Linux on the ARM architecture are generally available.

### Operational changes

- Nodes are now considered suspect when rejoining a cluster and cannot accept lease transfers for one `server.time_after_store_suspect` window, which defaults to 30 seconds. #107670
- Added two new changefeed metrics: `changefeed.checkpoint_progress` is similar to `changefeed.max_behind_nanos` but supports metrics labels; `changefeed.aggregator_progress` tracks the progress of individual aggregators (the lowest timestamp for which all aggregators with the label have emitted all values they're responsible for). #109745

### Bug fixes

- Fixed a potential deadlock when running changefeeds with `end_time` option set. #108076
- Added cancel checking to index constraint initialization code to allow queries to timeout during query optimization if analyzing predicates to constrain an index starts using too many resources. Example of setting a timeout using the `statement_timeout` session setting: `SET statement_timeout='5.0s';`. #106950
- Fixed the schema changer job when `CREATE AS` sources from `SHOW CREATE FUNCTION`, for example: `CREATE TABLE t AS SELECT * FROM [SHOW CREATE FUNCTION f];`. #108167
- Previously, CockroachDB could encounter an internal error `unexpected non-zero bytes limit for txnKVStreamer` when evaluating locality-optimized lookup joins in case it had to perform the remote regions' lookup. The bug was introduced in v22.2 and is now fixed. A temporary workaround can be accomplished without upgrading by setting the `streamer_enabled` session variable: `SET streamer_enabled = false;`. #108252
- Fixed a spurious error `no data source matches prefix` that could occur during planning for a query with `DISTINCT ON` and `ORDER BY ASC NULLS LAST` or `ORDER BY DESC NULLS FIRST`. #108302
- Fixed a bug where using `IMPORT INTO` for `DELIMITED DATA` or MySQL imports would error with `column ... does not exist` if it was importing into a collated string column. #108287
- Fixed a bug in the index recommendations provided in the `EXPLAIN` output where `ALTER INDEX ... VISIBLE` index recommendations may suggest making the wrong index visible when there are multiple invisible indexes in a table. #108647
- Fixed a bug that could cause a query with `LIMIT` and `ORDER BY` to return results in the wrong order. This bug could also cause incorrect results if the `LIMIT` was nested within an outer query, for example, under another `LIMIT`. #107379
- Fixed a bug introduced in v22.1 that could cause a join to infinite-loop in rare cases when the join filter is not an equality and no columns from the left input are returned. #106874
- Fixed a bug that caused nodes to crash when attempting to `EXECUTE` a prepared statement with an argument that referenced a user-defined function (UDF). This bug was present since user-defined functions were introduced in v22.2.0. #108473
- Fixed an issue with the full scan filter on the Statements page where the filter was always evaluating to false, even if a full scan had occurred. #109284
- Fixed a bug that could cause some rows to be silently skipped during `IMPORT` when a node's import worker failed. #109662
- Fixed a bug where calls to user-defined functions (UDFs) with NULL arguments returned ambiguous results. #109193
- Fixed a very rare condition where the storage engine stops making progress, reporting `background error: pebble: flush next log number is unset` in the `cockroach-pebble.log`. #109862
- Fixed a nil pointer dereference panic during node startup caused by an incorrect initialization order. #109685
- Fixed an issue where a split can be called on an invalid key that's in the form of `someValidKey.Next()` during restore with the cluster setting `bulkio.restore.use_simple_import_spans=true`. This split key can land in the middle of a row with column families, and thus result in failing SQL queries when querying the restored table. #109941

### Contributors

This release includes 78 merged PRs by 42 authors.

## v22.2.13

Release Date: August 8, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is in Limited Access.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.13

### Changelog

View a detailed changelog on GitHub: v22.2.12...v22.2.13

### Security updates

- The full set of TLS ciphers that was present in v22.1 have been included in the existing cipher suites list, which can be enabled with the `COCKROACH_TLS_ENABLE_OLD_CIPHER_SUITES` environment variable. #105355

### SQL language changes

- Inbound foreign keys are now allowed on TTL tables. If the inbound foreign key has the `ON DELETE CASCADE` option, the TTL job will work as usual. If the inbound foreign key has the `ON DELETE RESTRICT` option, the TTL job will fail if a row references the TTL table. #105342
- The `crdb_internal.cluster_queries` and `crdb_internal.node_queries` tables will redact SQL queries if the user has the `VIEWACTIVITYREDACTED` system privilege. If the user has the `VIEWACTIVITY` system privilege, these tables will not redact SQL queries. Users can now only view the `crdb_internal.cluster_queries` and `crdb_internal.node_queries` tables if they have any of the following privileges: `admin`, `VIEWACTIVITY`, or `VIEWACTIVITYREDACTED`. #105551
- The `kv.allocator.lease_rebalance_threshold` cluster setting can now be used to control the minimum fraction away from the mean a store's lease count is before it is considered for lease transfers. The default setting is `0.05`. #106186

### Operational changes

- ARM binaries for Linux and Docker images are now in Limited Access, but are not yet qualified for production use and not eligible for support or uptime SLA commitments.

### DB Console changes

- Improved the troubleshooting experience in the DB Console so that generic error messages now include details about the actual error and other contextual detail. This will make it easier to find the root cause of an error. #106410
- Previously, there was a delay when the Now button was enabled in the time picker. This is fixed so that the Now button on the time picker is always enabled. #107836
- Updated the histogram window merge calculation to more accurately interpolate quantile values. This change will result in smoother, more accurate Metrics charts in the DB Console. #104905
- The Database pages will no longer display `undefined` regions and outdated node information. #106777

### Bug fixes

- Fixed a crash when using `DurationToNumber` with an empty duration object on SQL Activity tables. #105151
- `GRANT SYSTEM ALL ...` no longer causes the grantee to be unable to log in. This was due to a bug where `ALL` would include the `NOSQLLOGIN` system privilege. Since `NOSQLLOGIN` is the only "negative" privilege, it is now excluded from the `ALL` shorthand, and must be granted explicitly in order to restrict logins. #105081
- Fixed a source of mutex contention within the storage engine that could increase tail latencies on high-CPU, high-throughput deployments. #105375
- Fixed a bug where the Job Details page would flicker between the job details and a loading animation while a job is still executing. #106156
- Fixed a bug where the Sort dropdown on the Network Latency page would not persist the selected value because a page reload was triggered. #107295
- Previously, CockroachDB would crash when evaluating `CREATE TABLE..AS` or `CREATE MATERIALIZED VIEW..AS` statements when the `AS` clause selected data from `crdb_internal.cluster_statement_statistics` or `crdb_internal.cluster_transaction_statistics` virtual tables. This bug has been present since at least v22.1 and is now fixed. #105359
- Fixed a bug that caused `INSERT..ON CONFLICT..DO UPDATE` queries to incorrectly result in an ambiguous column error. The bug only presented if the target table had a computed column with an expression referencing a column with a `DEFAULT` value. #105024
- Previously, cross-database type references were allowed through `CREATE TABLE..AS` statements if the source table was from another database and any of its columns was of a user-defined type. This introduced a bug where the source table could be dropped and the type could not be found for the `CREATE TABLE..AS` table. This fix disallows such `CREATE TABLE..AS` statements. #105619
- Fixed a bug that could cause an upgrade from v22.1 to v22.2 to take a long time, and sometimes not finish if there were a lot of tables that were being created and dropped regularly. #99936
- Fixed a bug in the geospatial cartesian bounding box type that had incorrect behavior when comparing boxes with `NaN` values. #105829
- Fixed a bug that manifested itself in error messages containing `failed to drop all of the relevant elements` when executing DDL statements with the declarative schema changer. This error message actually meant that a concurrent schema change is ongoing. Instead, this now behaves as expected and waits for it to finish. #106300
- Fixed an issue where changefeeds emitting to a cloud storage sink with the `compression` option may experience resource leakage (memory and go routines) when encountering transient errors. #106856
- Fixed a rare changefeed issue that was triggered when the parent database or types were dropped. Instead of exiting with a descriptive error message, the changefeed would observe the opaque error `value type is not BYTES: UNKNOWN`. #107937
- Fixed a bug where the SQL activity page was not loading data during v22.1 to v22.2 ugprades. #105664
- Previously, flushing SQL stats in mixed version v22.1 and v22.2 clusters would fail due to an attempt to update the `index_recommendations` column, which is new in v22.2. This bug is now fixed. #105664
- Fixed a panic that occurred when executing `CREATE AS` statements with a `pg_catalog.pg_prepared_statements` or `pg_catalog.pg_cursors` source. For example: `CREATE TABLE t AS SELECT * FROM pg_catalog.pg_prepared_statements;` or `CREATE MATERIALIZED VIEW v AS SELECT * FROM pg_catalog.pg_prepared_statements;`. #106288
- Fixed a bug where some secondary indexes would incorrectly be treated internally as primary indexes, which could cause some schema change operations to fail. The bug could occur if `ALTER PRIMARY KEY` was used on v21.1 or earlier, and the cluster was upgraded. #106428
- CockroachDB will now return an error during user-defined function creation if an input argument has type `RECORD`. #105734
- `SHOW SCHEMAS FROM db_name` will no longer incorrectly show schemas from the current database when the current database has a schema named `db_name`. It will now look up the database name instead of using the schema look up logic. #106198
- The statement tag for `SHOW SEQUENCES` has now been corrected to `SHOW SEQUENCES` instead of `SHOW SCHEMAS`. #106796
- Fixed a rare bug in which some userfile uploads would silently upload incorrect data. #106839
- Under prolonged unavailability, such as loss of quorum, affected ranges would exhibit raft log growth that was quadratic as a function of the duration of the outage. Now this growth is approximately linear instead. #106055
- This fix blocks dropping indexes that are impacted by technical advisory 99561 if data loss could occur. #106936
- Fixed a crash that could occur if `SHOW GRANTS ON SCHEMA` referenced a non-existent database name. #107232
- Fixed a failing schema change job when `CREATE TABLE AS` or `CREATE MATERAILIZED VIEW AS` sources from a `SHOW` command, such as:
  - `CREATE TABLE t AS SELECT * FROM [SHOW CREATE TABLE tbl];`
  - `CREATE TABLE t AS SELECT * FROM [SHOW INDEXES FROM tbl];`
  - `CREATE TABLE t AS SELECT * FROM [SHOW COLUMNS FROM tbl];`
  - `CREATE TABLE t AS SELECT * FROM [SHOW CONSTRAINTS FROM tbl];`
  - `CREATE TABLE t AS SELECT * FROM [SHOW PARTITIONS FROM TABLE tbl];`
  - `CREATE TABLE t AS SELECT * FROM [SHOW PARTITIONS FROM INDEX tbl@tbl_pkey];` #107227
- The `pg_get_serial_sequence` built-in function can now handle mixed-case names correctly. #107371
- Fixed a bug in upstream `etcd-io/raft` which could result in pulling unlimited amount of logs into memory, and lead to out-of-memory errors. Now the log scan has a limited memory footprint. [#104483][#104483]
- Fixed a bug where, in rare circumstances, a replication could get stuck when proposed near lease or leadership changes, especially under overload, and the [replica circuit breakers](../v22.2 could trip. A previous attempt to fix this issue has been reverted in favor of this fix. [#106515][#106515]
- Fixed the SQL syntax for the `CREATE TABLE AS` schema change job description. #107402
- Fixed a bug that caused internal errors when using user-defined types in views that have subqueries. This bug was present when using views since v21.2. #106956
- Fixed an internal error that would occur in `UPDATE`, `UPSERT`, `INSERT`, or `DELETE` statements run concurrently with `ALTER TABLE..ADD COLUMN` of a virtual computed column on the same table. #107408
- Previously, the `ST_ClosestPoint` built-in function did not preserve the correct Spatial Reference System ID (SRID) when comparing two different points. This is now resolved. #107594
- Fixed the SQL syntax for the `CREATE MATERIALIZED VIEW AS` schema change job description. #107547
- Reduced contention on the `system.statement_statistics` table, which has caused the SQL statistics compaction job to fail. #107794
- CockroachDB would previously crash when evaluating the `st_asencodedpolyline` built-in function on `GeometryCollection` geometry type. The bug was introduced before v22.1 and is now fixed. #107902
- CockroachDB would previously return an error when using `SHOW RANGE..FOR ROW..` in `CREATE TABLE..AS` constructs. This is now fixed. #107602
- Cloud storage buckets containing backups can now be copied via AWS DataSync and other third-party services, which will insert an empty object with a trailing `/`. Previously, restore would fail with the following error message, `read LATEST path: path does not contain a completed latest backup: NoSuchKey`. #106234
- Fixed a nil pointer dereference caused by a race condition when using the `to_char` built-in function. #108078
- Since v22.2.0, using a PTP clock device (enabled by the `--clock-device` flag) would generate timestamps in the far future. It now generates the correct time. This could cause nodes to crash due to incorrect timestamps, or in the worst case irreversibly advance the cluster's HLC clock into the far future. #108120
- Previously, when planning expressions containing many sub-expressions (e.g., deeply nested `AND` / `OR` structures), CockroachDB would use memory quadratical in the number of sub-expressions. In the worst cases (thousands of sub-expressions), this could lead to OOMs. This bug has been present since at least v22.1 and has now been fixed. #107370

### Performance improvements

- If the cluster setting `sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled` is set to `false`, the optimizer can now eliminate uniqueness checks for `STRING` and `BYTES` columns when the value is set to `gen_random_uuid()` (with an implicit or explicit cast to `STRING` or `BYTES`). If users still want the checks, they can set `sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled` to `true`. The default for this setting is `false`. #105445
- Performance of the `LIKE` and `ILIKE` operators using patterns without any wildcards has been improved. #105863
- Backups no longer perform work proportional to the number of pending intents that they encounter, so they are over 100x faster when encountering long-running, bulk-writing transactions. #105527

### Contributors

This release includes 145 merged PRs by 53 authors.

## v22.2.12

Release Date: July 24, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.12

### Changelog

View a detailed changelog on GitHub: v22.2.11...v22.2.12

### SQL language changes

- Attempting to drop an index that is the only source of a column is now forbidden and results in an error. #106936

### Command-line changes

- The `debug doctor` command now allows you to verify that primary indexes store all columns. #106936

### Bug fixes

- Fixed a bug in the new declarative schema changer framework that could cause the incorrect retrieval of the unique secondary index rather than the primary index. On such a cluster, an `ALTER TABLE..ADD COLUMN` statement will result in data for the new column being added to the secondary index, which could lead to query inconsistency. For details and to determine whether a cluster is impacted, refer to Technical Advisory 99561. #106428

### Contributors

This release includes 2 merged PRs by 2 authors.

## v22.2.11

Release Date: June 27, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.11

### Changelog

View a detailed changelog on GitHub: v22.2.10...v22.2.11

### Security updates

- There is a new `server.client_cert_expiration_cache.capacity` cluster setting which, when set to a non-zero number, makes it so that the minimum time-until-expiration of the set of client certificates seen is stored (for every user). This setting can be used to ensure client cert expirations are exported as a metric (if set to zero, the metric `security.certificate.expiration.client` will have a value of zero). #104209

### SQL language changes

- Added a new session variable `allow_role_memberships_to_change_during_transaction` which can be used to make the granting and revoking of role memberships faster at the cost of some isolation claims. By default, when granting or revoking a role from another role, CockroachDB waits for all transactions that are consulting the current set of role memberships to complete. This means that by the time the transaction which performed the grant or revoke operation returns successfully, the user has a proof that no ongoing transaction is relying on the state that existed prior to the change. The downside of this waiting is that it means that `GRANT` and `REVOKE` will take longer than the longest currently executing transaction. In some cases, users do not care about whether concurrent transactions will immediately see the side-effects of the operation, and would instead prefer that the grant or revoke finish rapidly. In order to aid in those cases, the session variable `allow_role_memberships_to_change_during_transaction` has been added. Now, the grant or revoke will only need to wait for the completion of statements in sessions which do not have this option set. One can set the option as enabled by default in all sessions in order to accelerate and grant and revoke role operations. #103847
- `SHOW GRANTS` now lists not just privileges explicitly granted to each role, but also roles which inherit from those. `SHOW GRANTS ON ROLE` statements no longer require any privileges, and also list implicit grantees. #104589
- Users with the `MODIFYCLUSTERSETTING` system-level privilege will no longer be able to view non `sql.defaults.*` settings if the cluster setting `sql.full_modify_cluster_setting.enabled` is set to `false`. #104231

### Operational changes

- Added a new debug tool to allow for decrypting files in a store using encryption-at-rest. This tool is intended for use while debugging, or for providing debug artifacts to Cockroach Labs to aid with support investigations. It is intended to be run "in-situ" (i.e., on site), as it prevents having to move sensitive key material. #104091
- Added a new command that can be used by an operator to list the files present in the encryption-at-rest file registry. #104091
- Added a new metric `leases.liveness` that shows the number of liveness range leases per node (generally 1 or 0) to track the liveness range leaseholder. #104077
- The new a gauge metric `sql.conns_waiting_to_hash` counts the number of connection attempts that are being limited due to the number of concurrent password hashing operations. This behavior has been present since v21.2 to prevent password hashing from increasing CPU load. The metric is expected to be `0`, or close to `0`, in a healthy setup. If the metric is consistently high and connection latencies are high, then an operator should do one or more of the following:
  - Ensure applications using the cluster have properly configured connection pools.
  - Add more vCPU or more nodes to the cluster.
  - Increase the password hashing concurrency using the `COCKROACH_MAX_PW_HASH_COMPUTE_CONCURRENCY` environment variable. #104441
- CockroachDB now uses response data rather than just the request span in the load-based splitter to pass more accurate data about the keys iterated over to the load splitter to find a suitable split key, enabling the load splitter to find a split key under heavy range query workloads. #104563
- Added observability for when load-based splitting cannot find a key to indicate the reasons why the load splitter could not find a split key, enabling us to have more observability and insight to debug why a range is not splitting more easily. #104563

### Command-line changes

- Failures during descriptor validity checks during cluster upgrades are now more detailed in redacted logs. #104048

### DB Console changes

- The database details page now supports a large number of tables for a single database. Sorting will be disabled if more than 40 tables are present in a database. #103860
- Added metrics for merge queue failures and merge queue processing time to the `Queue Processing Failures` and `Queue Processing Times` charts on the Queues Dashboard. #104033
- Added more search criteria options to the SQL Activity page.
  - For Top: `1000`, `5000`, and `10000`.
  - For By on the Statements tab: Last Execution Time, Max Memory, Network, Retries, Rows Processed.
  - For By on the Transactions tab: Max Memory, Network, Retries, Rows Processed. #104056
- Added a Created SQL Connections chart to the Metrics page and the SQL Dashboard. #104070
- Added a new link on the Range Status page that opens the Enqueue Ranges page with the node ID already filled in. #104099
- On the Active Executions table of the Transactions page, transaction status will be 'Idle' if the executing transaction is not currently executing a statement. Previously, it would have had a status of 'Executing'. #104333
- Added a warning to the DB Console overview page when all nodes are running on a new version of CockroachDB, but the cluster upgrade is not finalized. #104878

### Bug fixes

- `DROP ROLE` now correctly returns the error code `2BP01` when the given role has been granted privileges on a schema. #103545
- Fixed a bug whereby disk space used by deleted and garbage collected data would not be reclaimed in a timely manner by the storage engine, especially when a store had low write workload. #103867
- Fixed a problem that could lead to erroneously refused lease transfers with the error message: `"refusing to transfer lease to [...] because target may need a Raft snapshot: replica in StateProbe"`. #103877
- Fixed a bug where `cockroach node status` could incorrectly report nodes as `is_live = false` in v22.1/v22.2 mixed-version clusters. The bug still exists between v22.2 patch versions before and after v22.2.3. #103788
- Fixed a bug whereby running a debug command that manipulates a store (e.g., `cockroach debug compact`) without first terminating the node using the store could result in corruption of the node's store if encryption-at-rest was enabled. #103959
- Fixed a bug where `SHOW DEFAULT PRIVILEGES` did not work correctly if the database name or schema name being inspected had upper-case or special characters. #103951
- Fixed a bug that could cause queries with joins or subqueries to omit rows where column values are `NULL` in very rare cases. This bug was present since v20.2. #104073
- Fixed a bug that could cause goroutines to hang during SCRAM authentication. #104196
- Fixed a bug which could cause nodes in a CockroachDB cluster to terminate with the following message: `server startup failed: cockroach server exited with error: ‹migration-job-find-already-completed›: key range id:X is unavailable: ‹failed to send RPC: no replica node information available via gossip for rX›`. #104250
- Fixed a rare bug where stale multi-column table statistics could cause table statistics forecasts to be inaccurate, leading to non-optimal query plans. #104241
- Fixed a bug that caused incorrect results to return in cases where predicates on computed columns are derived when an `OR`ed predicate on a column in the computed column expression is present. This bug only affects CockroachDB when the session setting `optimizer_use_improved_computed_column_filters_derivation` is `true`. This setting defaults to `false` in releases v22.1.10 and v23.1.2. This bug does not affect releases prior to v22.1.10 and v23.1.2. #104289
- Fixed a rare race condition that could allow large `RESTORE`s to fail with the error message `unable to find store`. #100957
- Fixed a bug which would cause `CREATE FUNCTION` (which uses the `setval` builtin function) to panic. #104408
- Fixed a Postgres wire protocol (pgwire) bug where CockroachDB would not ignore the messages that it should if there was an error while in the pgwire extended protocol. #104505
- The `sys.cpu.combined.percent-normalized` metric now uses `GOMAXPROCS` when calculating CPU utilization (if lower than the number of CPU shares). #104498
- Fixed an issue where `admin` or `root` user privileges were required to use `SHOW SYSTEM GRANTS`. #104735
- Fixed a bug where the column selector on the Jobs page was getting cut. #104737
- Fixed a bug that could prevent `RESTORE` from working if the backup had a refresh materialized view mutation in it. #103231
- Fixed a bug where CockroachDB was double-counting disk read/write bytes in disk metrics in volumes that were likely to be duplicated in reported disk counters, such as RAID logical vs physical volumes. #104807
- Fixed a bug where transient failures could occur during upgrades if a table/view were dropped while the internal upgrade step "upgrade sequences to be referenced by ID" was executing. #104903
- Fixed a bug in upstream etcd/raft which could result in pulling unlimited amounts of log into memory and lead to out-of-memory (OOM) situations. With the fix, the log scan has a limited memory footprint. #104956
- Fixed a bug where SQL queries could return unexpected errors when a SQL row was split across two ranges. This bug is resolved, as we now inspect the real keys, rather than just request keys to determine load-based split points. #104563
- Updated the DB Console to show more precision in small percentage values on the percentage bars. #105077
- Fixed a bug where `SHOW BACKUP` would fail to show a locality-aware backup that contained incremental backups. #103830

### Performance improvements

- If the session setting `transaction_rows_read_err` is set to a non-zero value, we now ensure that any single scan never reads more than `transaction_rows_read_err`+1 rows. This prevents transactions that would error due to the `transaction_rows_read_err` setting from causing a large performance overhead due to large scans. For some queries in rare cases this change may end up disabling cross-range parallelism of the scan operation which can result in increased query latency. #104368

### Contributors

This release includes 85 merged PRs by 36 authors.

## v22.2.10

Release Date: May 30, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.10

### Changelog

View a detailed changelog on GitHub: v22.2.9...v22.2.10

### SQL language changes

- Responses from the Cluster API now escape special characters in database names, such as spaces. #102896
- The new session variable `optimizer_use_improved_computed_column_filters_derivation` allows the optimizer to derive filters on computed columns in more cases. This session variable defaults to `false`. #103424
- Keys are now redacted in the following internal tables, and can be viewed only by users with the `VIEWACTIVITYREDACTED` role:
  - `crdb_internal.transaction_contention_events`
  - `crdb_internal.node_contention_events`
  - `crdb_internal.cluster_locks`
  The `crdb_internal.node_contention_events` table can now be viewed only by users with the `admin`, `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` role. #103644

### Operational changes

- Timeseries metric counts now show the cumulative count for histograms rather than the windowed count. A `-sum` timeseries is now exported for each histogram, to keep track of the cumulative sum of all samples in the histogram. #103447

- The following metrics are now exported to help troubleshoot issues when the `admission.elastic_cpu.enabled` cluster setting is enabled:

  - `admission.elastic_cpu.nanos_exhausted_duration`
  - `admission.elastic_cpu.over_limit_duration`
  - `admission.elastic_cpu.pre_work_nanos`
  - `admission.elastic_cpu.available_nanos`

  #103648

### DB Console changes

- The Cluster Overview page now shows an alert immediately when the cluster setting `cluster.preserve_downgrade_option` is set, instead of only after 48 hours. #102912
- If a DB Console page crashes in a web browser, a forcible refresh is no longer required to view other DB Console pages. #103327
- The Databases and Table Details pages now allow you to filter and search results. #103593

### Bug fixes

- Fixed a bug introduced in v21.1 that allowed values to be inserted into an `ARRAY`-type column that did not conform to the inner type of the array. For example, it was possible to insert `ARRAY['foo']` into a column of type `CHAR(1)[]`. This could cause incorrect results when querying the table. Such an insert now results in an error. #102810
- Fixed a bug where backup or restore of a synthetic schema such as `system.public` would cause the node to crash. #102782
- Fixed a bug where an asymmetric network partition could allow a node to erroneously update a closed timestamp for a lease that was transferred away from it after it missed a liveness heartbeat. This could lead to a closed timestamp invariant violation and could cause a node to crash. In an extreme case, it could cause inconsistencies in read-only queries. This fix changes the the order of availability checks so that a node's `PROSCRIBED` status is now evaluated before its `UNAVAILABLE` status. #102599
- Fixed a rare bug introduced in v22.2.0, where a job that uses row-level TTL could incorrectly process a table that spans multiple ranges. When you encounter this bug, an error such as the following is logged: `error decoding EncDatum ...: did not find terminator ... in buffer ...`. #102914
- The value of `pg_constraint.conparentid` is now `0` rather than `NULL`. CockroachDB does not support constraints on partitions. #103232
- Fixed a bug where a previously-added filter condition could not be removed in the Statements page. #103415
- Fixed a rare bug introduced in v22.1.0, where a query that uses the `ORDER BY` clause could return incorrect results if all of the following conditions are met:
  - The query has both an `ORDER BY` clause and a `LIMIT` clause.
  - The sort operation uses enough memory that it spills to disk.
  - The `ORDER BY` clause contains multiple columns.
  - The ordering on the prefix of the `LIMIT BY` columns was already provided by an index.
  #102789
- Fixed a rare bug introduced in v22.2.9 that could cause a node due to a `NULL` pointer in the code that populates SQL Activity pages. #103522
- Fixed a bug that could cause excessively slow backups when the `admission.elastic_cpu.enabled` cluster setting was enabled. #103648
- Fixed a bug where a node could crash if placeholder arguments are used with the `to_timestamp()` built-in function (for example, `with_min_timestamp(to_timestamp($1))`). #103646
- Fixed a bug where removed regions are not cleaned up correctly after a restore. To fix this issue, `SET PRIMARY REGION` and `SET SECONDARY REGION` now validate transactionally. #103634

### Performance improvements

- Improved performance of SQL audit logging, which now uses the lease cache to help resolve the names of tables, views, and sequences. #102831
- A query can now be constrained using a computed column expression that is part of an index, as long as the query's `IN` clause or `OR` clause includes the columns referenced by the computed column expression. #103424

### Contributors

This release includes 53 merged PRs by 36 authors.

## v22.2.9

Release Date: May 8, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.9

### Changelog

View a detailed changelog on GitHub: v22.2.8...v22.2.9

### General changes

- SQL queries with invalid syntax are now logged at the `INFO` level in the `SQL_EXEC` log channel. Previously these were logged at the `ERROR` level. #101093

### Enterprise edition changes

- Changefeeds using the `WITH confluent_schema_registry` option will make fewer duplicate schema registrations. #100843

### SQL language changes

- Added a new session variable `unbounded_parallel_scans` which controls whether scans will be parallelized across ranges in more cases. Note that using this feature can lead to increased likelihood of out-of-memory errors (OOMs), so it should be used with care (namely when you expect that the scan should read a "reasonable" number of rows - probably less than 10k). Also note that queries with `LIMIT`s aren't affected by this variable; cross-range parallelism of scans continues to be disabled for such queries. #100949
- Tables with Row-level TTL settings can now have outbound foreign keys. These outbound foreign keys are unaffected by rows being deleted from tables with Row-level TTL. cockroachdb/cockroach#101876

### Operational changes

- When local corruption of data is encountered by a background job in the storage engine, a node will now exit immediately. cockroachdb/cockroach#102273
- Added a metric `leases.requests.latency` recording a histogram of lease request latencies. cockroachdb/cockroach#100474

### Command-line changes

- The utility script `hot-ranges.sh` that is included in `cockroach debug zip` output now partitions its output by the following statistics: `queries_per_second`, `writes_per_second`, `read_bytes_per_second`, `write_bytes_per_second`. It also decreases the number of ranges shown under each heading from 20 to 10. cockroachdb/cockroach#93638

### DB Console changes

- When no data is persisted to the `crdb_internal.transaction_statistics` and `crdb_internal.statement_statistics` tables (because no disk flush happened yet or because the flush is disabled), the endpoints powering the SQL Activity page in the DB Console should fall back to the combined view that contains the in-memory data. cockroachdb/cockroach#101167
- `SET` statements are no longer displayed on the DB Console's Insights page. #101684
- On the SQL Activity fingerprints pages, users will not see stats that have not yet been flushed to disk. #99403
- Users can request Top-K statements by their percentage of runtime on the SQL Activity fingerprints pages. #99403
- Added a Search Criteria heading to the Statements page and the Transactions page. cockroachdb/cockroach#99403
- Added badges for each selected filter on the SQL Activity page and the Insights page. #101138
- The data on the Statement Fingerprints page no longer refreshes every 5 minutes. cockroachdb/cockroach#101102
- The default request sort for the Statement fingerprints overview page is now '% of All Runtime' #101102
- Added a warning for when a user selects a sorting column on tables on the Statements page and Transactions page that were not the originally selected sorting column in the search criteria, so they know they are looking at a subset of the data. cockroachdb/cockroach#101167
- Some of the names of sort labels on Search Criteria headings on the Statements page and the Transactions page were not a match for the column name in the resulting tables, which could cause confusion. This change updates the values of "P99" to "P99 Latency" and "Service Latency" to "Statement time" and "Transaction time". cockroachdb/cockroach#101167
- By default, CockroachDB now shows the Application Name column on the Statement Fingerprints and Transaction Fingerprints overview pages. Statement fingerprints and transaction fingerprints will now be displayed per application on the overview pages rather than grouped into a single fingerprint ID. #101475
- When going from the Statement Fingerprint or Transaction Fingerprint overview pages, or the Insights details pages to the fingerprint details page, the details page will fetch data for the statement with the provided application name. For the fingerprint overview pages, this is the app name of the selected row. For the insight details page, it is the app name of the statement execution that generated the insight. #101475

### Bug fixes

- Fixed a bug where running `ALTER TABLE ... DROP COLUMN ... CASCADE` when that column is used in an index that includes other columns caused a panic. #100956
- Fixed a bug whereby some tables' physical disk space usage could not be calculated by the storage engine. cockroachdb/cockroach#100953
- Fixed a bug that caused suboptimal query plans to be generated by the optimizer when the table being queried contained infinite values, e.g., `'+Infinity'::DECIMAL`. This bug was present since v22.1 (and likely earlier). It could also be triggered in rare cases when table statistics forecasts created a forecasted bucket with an infinite value. #101133
- Fixed a bug so that the `crdb_internal.deserialize_session` internal function works properly with prepared statements that have more param type hints than params. Before this bugfix, deserializing a session containing a prepared statement with more parameter type hints than parameters would panic. For example: `PREPARE p (int) AS SELECT 1`. These extra type hints are now ignored by `crdb_internal.deserialize_session`. cockroachdb/cockroach#101365
- Fixed a rare bug with distributed plans shutdown that could make the draining of CockroachDB nodes keep retrying indefinitely. If you see messages in the logs like `drain details: distSQL execution flows:` with a non-zero number of flows that isn't going down over a long period of time, the drain process for your cluster is affected by this bug. The bug had been present since before v22.1. cockroachdb/cockroach#100840
- Fixed a rare condition that could allow a transaction to get stuck indefinitely waiting on a released row-level lock if the per-range lock count limit was exceeded while the transaction was waiting on another lock. cockroachdb/cockroach#100945
- Fixed a bug in the optimizer that could cause an internal error in rare cases for a query with outer joins that could be simplified to non-outer joins and at least one semi-join. This bug was present since sometime before the v22.1 release. cockroachdb/cockroach#100668
- Fixed a bug in the optimizer that could cause queries containing a subquery with a lateral join, in which the right side of the lateral join was an aliased data source, to return an internal error in some cases. For example, it could cause an error if the subquery was provided as an argument to an aggregate function. This bug was introduced in v22.2.0. cockroachdb/cockroach#101862
- Fixed a bug where CockroachDB was incorrectly evaluating `EXPORT` statements that had projections or rendering on top of the `EXPORT` (e.g., the common table expression `WITH cte AS (EXPORT INTO CSV 'nodelocal://1/export1/' FROM SELECT * FROM t) SELECT filename FROM cte;`). Such statements would result in panics or incorrect query results. Note that the exported data wasn't affected, only the presentation of the query result to the user. This bug had been present since v22.1 or earlier. #101807
- Fixed a bug where `RESTORE` statements passed the `skip_localities_check` option would still fail with errors if regions were missing from the cluster. cockroachdb/cockroach#101893
- Fixed a very rare bug in the storage engine that could cause keys to be unexpectedly deleted locally within a store by replica rebalancing during a write heavy workload. cockroachdb/cockroach#102176
- Fixed a bug where CockroachDB's pgwire implementation would incorrectly parse arrays if they were sent as placeholder arguments to a prepared statement, and the argument had spaces in between the array elements. cockroachdb/cockroach#102140
- Fixed a potential bug whereby a failed or cancelled `IMPORT` could leave some of the imported rows behind after it was cancelled, in the rare event that the writing processes were slow enough to continue writing after the cleanup process had started. #101444
- Fixed an `Uncaught TypeError` that could occur on the Insights Page in the DB Console. cockroachdb/cockroach#102381
- Fixed a bug where a backup with a key's revision history split across multiple SST files may not have correctly restored the proper revision of the key in all cases. cockroachdb/cockroach#102363
- Previously, CockroachDB could encounter a "command is too large" error when evaluating `UPSERT` statements such that the new values combined exceeded the size of the `kv.raft.command.max_size` cluster setting. This bug had been present since before v21.1 and initially all write operations (`INSERT`, `UPDATE`, `DELETE`) were affected; however, in v21.2 those three were fixed, but `UPSERT` was forgotten about. This is now fixed. #102515
- Fixed a bug introduced in v22.1.19, v22.2.8, and pre-release versions of v23.1 that could cause queries to return spurious insufficient privilege errors. For the bug to occur, two databases would need to have duplicate tables, each with a foreign key reference to another table. The error would occur if the same SQL string was executed against both databases concurrently by users that each had privileges over only one of the tables. cockroachdb/cockroach#102652
- Fixed a bug where `ALTER TABLE ... RENAME COLUMN` was incorrectly allowed on internal system columns such as `crdb_internal_mvcc_timestamp`. cockroachdb/cockroach#102644
- Fixed a bug where the `ALTER DEFAULT PRIVILEGES ... GRANT USAGE ON SEQUENCES` statement would fail because the sequence object was mapped to an incorrect internal privilege object. #102730

### Performance improvements

- This change adds an opt-in pacing mechanism to rangefeed closed timestamp notifications. Pacing is controlled by the `kv.rangefeed.closed_timestamp_smear_interval` cluster setting, which defaults to the value of `kv.rangefeed.closed_timestamp_refresh_interval`. Lowering the smear interval can be used to make rangefeed closed timestamp delivery less spikey, which ultimately reduces its impact on foreground SQL query latencies. #99945
- Queries that have subqueries in equality expressions are now more efficiently planned by the optimizer when the `optimizer_hoist_uncorrelated_equality_subqueries` session setting is set to `true`. #101744

### Build changes

- A binary called "Cypress" used by the UI end-to-end testing framework is now only downloaded and installed when those tests are run, instead of being eagerly downloaded on all platforms at build time. This restores the ability for non-{Windows, Darwin, Linux} platforms like FreeBSD and IllumOS to build CockroachDB without modifications, which broke in the initial v22.2 release. cockroachdb/cockroach#93803

### Contributors

This release includes 73 merged PRs by 38 authors.

## v22.2.8

Release Date: April 17, 2023

### Downloads

Warning:

This patch release has been withdrawn. We've removed the links to the downloads and Docker image.All the changes listed as part of this release will be in the next release. Do not upgrade to this release.

### Enterprise edition changes

- Backup schedules created or altered to have the option `on_previous_running` will now have the full backup schedule created with the user specified option, but will override the incremental backup schedule to always default to `on_previous_running = wait`. This prevents duplicate incremental jobs from racing against each other, and ensures correctness of the backup chains created by the incremental schedule. #98860
- AVRO schema registry URIs now allow you to change the default timeout for contacting the schema registry by setting the query parameter `timeout=T`. #99505

### SQL language changes

- Fixed the helper message on the UPDATE statement to correctly position the optional `FROM` clause. #99299
- Added a new `prepared_statements_cache_size` session setting which, when set to a non-zero number of bytes, causes the least recently-used prepared statements to be automatically deallocated when prepared statement memory usage goes above the cache size. This setting can be used to avoid prepared statement leaks from long-lived connections which never `DEALLOCATE` prepared statements. #99259
- Added two new cluster settings that enable users to change the number of histogram samples and buckets collected when building histograms as part of table statistics collection: `sql.stats.histogram_samples.count` and `sql.stats.histogram_buckets.count`. #100544

### Operational changes

- The `kv.trace.slow_request_stacks.threshold` cluster setting can be used to attach available stack history from tracer snapshots to slow requests traces. #99738

### DB Console changes

- New data on the Statement and Transaction pages is now automatically fetched every five minutes. #99271
- On DB Console Stats pages, you can now issue a new request for stats while a previous request is pending. The new request replaces the previous request. #99271

### Bug fixes

- Fixed a bug where prepared statements using placeholders in recursive common table expressions did not always re-optimize after placeholders were resolved. #100327
- Fixed a bug where glob patterns that did not match tables in `GRANT` or `REVOKE` statements would return an internal error with a confusing message instead of the `"no objects matched"` error. #99437
- Fixed a bug introduced in v22.2.0, where the node could crash with the error `"attempting to append refresh spans after the tracked timestamp has moved forward"` in some rare cases. To work around this issue until you can upgrade, run `SET CLUSTER SETTING sql.distsql.use_streamer.enabled = false;`. #99445
- Fixed a bug where the node could crash when a statement used `cluster_logical_timestamp()` as a `DEFAULT` expression. #99662
- The `ALTER DEFAULT PRIVILEGES ... ON FUNCTIONS ...` statement is no longer allowed unless all nodes are running on v22.2 and the upgrade is finalized. This command could cause a node still running v22.1 to crash in a mixed-version cluster. #99845
- Fixed a bug where the `TRUNCATE TABLE` GC job can be stuck in running status if the table descriptor has been GCed. This happened because `TRUNCATE TABLE` creates new empty indexes, then replaces and drops the original indexes. The dropped indexes data are deleted and GCed within the `TRUNCATE TABLE` GC job, which are needed to see the table descriptor make progress. However, if the table data has been GCed, the job couldn't make progress. This patch makes the GC job able to handle the missing descriptor edge case and let the `TRUNCATE TABLE` GC job succeed. #100137
- Fixed a bug introduced before v21.2 that would cause the gateway node to crash if there are self-referencing views. #100163
- Fixed a bug where queries reading from virtual tables such as those in the `crdb_internal` and `pg_catalog` system catalogs could hang indefinitely if the query would result in an error. #99968
- In rare cases involving schema changes on an overloaded cluster, users could sometimes, transiently, see errors of the form `"deadline below read timestamp is nonsensical; txn has would have no chance to commit"`. These errors carried an internal pgcode and could not be retried. This form of error is now classified as a retryable error and will be retried automatically either by the client or internally. #100255
- Fixed a bug where the `sql.mem.distsql.current` metric would count the memory usage of remote DistSQL flows twice. #100254
- Fixed a bug that could prevent a cached query with a user-defined type reference from being invalidated, even after a schema change that should prevent the type from being resolved. #100223
- Fixed a bug that could prevent a cached query from being invalidated when a user-defined function referenced by that query was altered or dropped. #100223
- Fixed a bug where user-defined functions were introduced that could cause a function call to resolve to the wrong function after changes to the schema search path. #100223
- Fixed a rare bug introduced before v22.1 that could cause a projected expression to replace column references with incorrect values. #97593
- Fixed a bug in the new declarative schema changer that could cause primary index corruption when an `ALTER TABLE..ADD COLUMN` statement executed concurrently with an `UPDATE` or `INSERT` statement if the schema change failed and was rolled back. #100187

### Performance improvements

- Removed prettify usages that could cause out-of-memory (OOM) errors on the Statements and Transactions pages. #99453
- Audit logging now looks up the names of tables, views, and sequences in a node's lease cache to reduce network latency. #99661
- The execution of multiple `FOREIGN KEY` and `UNIQUE` constraint checks can be parallelized in some cases to help them complete more quickly. faster, especially so in multi-region environments where the checks require cross-region reads. This feature is disabled by default. To enable it, set the private (undocumented) `sql.distsql.parallelize_checks.enabled` cluster setting to `true`. #100520

### Doc updates

### Contributors

This release includes 56 merged PRs by 37 authors. We would like to thank the following contributors from the CockroachDB community:

- Eric.Yang

## v22.2.7

Release Date: March 27, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.7

### Changelog

View a detailed changelog on GitHub: v22.2.6...v22.2.7

### Security updates

- The default value for the `server.user_login.password_hashes.default_cost.scram_sha_256` cluster setting is now `10610`. (Previously the default was `119680`.) The previous value was too high for many types of client hardware, and in some cases could cause regressions in connection latency. The new value was chosen by running tests with clients that have 1 or 2 vCPUs provisioned. Additionally, the new cluster setting `server.user_login.rehash_scram_stored_passwords_on_cost_change.enabled` was added, and defaults to `true`. If it is `true` and the stored SCRAM password for a user has a different cost than the configured default cost, then the next time the user logs in, their password will automatically be rehashed using the configured default cost. If the rehashing is not desired, then operators should update the `server.user_login.password_hashes.default_cost.scram_sha_256` cluster setting to the value they desire before upgrading. #98318
- Previously, users could gain unauthorized access to statement diagnostic bundles they did not create if they requested the bundle through an HTTP request to `/_admin/v1/stmtbundle/<id>` and correctly guessed its (non-secret) `ID`. This change now locks down the endpoint behind the SQL gating and uses the correct SQL user in the HTTP session as identified by their cookie. #99053

### Enterprise edition changes

- Fixed a bug preventing the creation of changefeeds to `external:// URIs`. #98680

### SQL language changes

- Added a new session setting, `optimizer_use_improved_split_disjunction_for_joins`, which enables the optimizer to split disjunctions (`OR` expressions) in more `JOIN` conditions by building a `UNION` of two `JOIN` expressions. If this setting is `true`, all disjunctions in inner, semi, and anti `JOIN`s will be split. If `false`, only disjunctions potentially containing an equi `JOIN` condition will be split. #97823
- Added support for the syntax `CREATE DATABASE IF NOT EXISTS ... WITH OWNER`. #97974
- Introduced a new internal virtual table `crdb_internal.node_memory_monitors`, which exposes all of the current reservations with the memory accounting system on a single node. Access to the table requires `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` permissions. #97865
- Added a new session setting `optimizer_always_use_histograms`, which ensures that the optimizer always uses histograms when available to calculate the statistics of every plan that it explores. Enabling this setting can prevent the optimizer from choosing a suboptimal index when statistics for a table are stale. #98230
- Added a new aggregate builtin function `array_cat_agg`. It behaves similarly to `array_agg(unnest(array_column))`: it takes arrays as its input and unnests them into array elements, which are then aggregated into a single result array. This is similar to concatenating all input arrays into a single one. #98171
- Added the cluster setting `sql.auth.modify_cluster_setting_applies_to_all.enabled` to regulate whether `MODIFYCLUSTERSETTING` can edit non `sql.defaults` settings. #98234
- Fixed a bug where CockroachDB panicked when a user tried to truncate a table that has an ongoing row-level TTL change. CockroachDB still does not support table truncates in this scenario, but instead of panicking, an unimplemented error is returned. #98591
- Added two views to the `crdb_internal` catalog: `crdb_internal.statement_statistics_persisted`, which surfaces data in the persisted `system.statement_statistics` table and `crdb_internal.transaction_statistics_persisted`, which surfaces the `system.transaction_statistics` table. #98684
- The SQL Activity page now displays only persisted stats when selecting to view fingerprints. This means data recently executed might take up to 10 minutes to show on the DB Console. #99044

### Operational changes

- Added a new metric `changefeed.schema_registry.retry_count`. This measures the number of request retries performed when sending requests to the changefeed schema registry. Observing a non-zero value may indicate improper configuration of the schema registry or changefeed parameters. #98349

### Command-line changes

- Workloads that take a `--seed` argument used to default to `1`. Now, they use a randomly generated seed in each run. Users can still pass a custom seed with the `--seed` flag. #95695
- The `--drain-wait` argument to the `cockroach node drain` command will be automatically increased if the command detects that it is smaller than the sum of the cluster settings `server.shutdown.drain_wait`, `server.shutdown.connection_wait`, `server.shutdown.query_wait` times two, and `server.shutdown.lease_transfer_wait`. If the `--drain-wait argument` is `0`, then no timeout is used. #98577

### DB Console changes

- Fixed the error `Cannot read properties of undefined (reading 'length')`, which can cause DB Console pages fail to load. #98236
- Updated the column selector icon to show the gear icon and "Columns". #98915
- Updated the Jobs table column name from "Last Modified Time" to "Last Execution Time". #99036
- Fixed link encoding on links to database/table/index pages. #97930
- The Jobs page now displays an error state when an error occurs during data fetching. #97723

### Bug fixes

- Fixed a bug introduced in v22.1 that caused the internal error `no bytes in account to release ...`. #97773
- Fixed a bug that caused an internal error when trying to execute a UDF with an empty function body. This bug was present since UDFs were introduced in v22.2.0. #93834
- Fixed the `SHOW CREATE ALL {TYPES|SCHEMAS|TABLES}` commands to handle database names that have mixed-case, hyphens, or quotes. #97937
- Changed the database used for SQL API calls to no longer use `defaultdb`, which was causing error messages on some pages when that database no longer exists. #98052
- The owner of the public schema can now be changed using `ALTER SCHEMA public OWNER TO new_owner`. #98065
- Fixed a bug where the experimental scrub command did not handle type descriptors in the database. #91459
- Fixed a bug where common table expressions (CTEs) marked as `WITH RECURSIVE` that were not actually recursive could return incorrect results. This could happen if the CTE used a `UNION ALL`, because the optimizer incorrectly converted the `UNION ALL` to a `UNION`. This bug has existed since suppport for recursive CTEs was first added in v20.1. #98115
- Since v22.1 when rangefeed enablement overrides in span configs were introduced, rangefeed requests that reached spans outside the range would not cause range cache invalidation due to the setting being checked first, thus requests could repeatedly hit the same incorrect range, causing errors until cache invalidation or node restart. This fix correctly checks that the span is within the range prior to checking the enablement settings, thus invalidating the cache when a request reaches an incorrect range and causes subsequent requests to successfully reach the correct range. #97659
- Previously, the declarative schema changer would emit alarming messages of the form: `failed building declarative schema change targets for...`. These were non-severe in nature and are now disabled by default. #98258
- Fixed a bug where new schema changes that used the declarative schema changer in a mixed version state upgrading from v22.1 did not execute properly. The impacted schema changes are `ADD COLUMN`, `DROP COLUMN`, `ALTER PRIMARY KEY`, `CREATE INDEX`, `DROP OWNED BY`, `COMMENT ON`, and `DROP INDEX`. #98379
- Fixed a bug where if an `UPDATE` was performed during an ongoing `ADD COLUMN` or `DROP COLUMN` on a table, the update could incorrectly fail due to a duplicate key error. #98505
- Fixed a bug where CockroachDB could encounter the internal error `concurrent txn use detected`. The bug was introduced in v22.2.0. #98406
- Fixed a bug that could crash the process when a query contained a literal tuple expression with more than two elements and only a single label, e.g., `((1, 2, 3) AS foo)`. #98315
- Fixed a bug where the stats columns on the Transaction Fingerprint overview page did not not continuously increment. #98336
- Fixed a bug in evaluation of `ANY`, `SOME`, and `ALL` sub-operators that would cause expressions like `NULL = ANY(ARRAY[]::INT[])` to return `NULL` instead of `false`. #98165
- Users with `VIEWACTIVITY`/`VIEWACTIVITYREDACTED` permissions are now allowed to access the `crdb_internal.ranges_no_leases` table, necessary to view important DB Console pages (such as the Databases page, including database details, and database tables). #98645
- Fixed a bug where it was possible for CockroachDB to temporarily not respect zone configurations other than the default zone configuration. This could only happen for a short window after nodes with existing replicas were restarted (measured in seconds), and self-rectified (also within seconds). These issues lasted a few seconds post node-restarts, and any zone configuration violations were rectified shortly after. This manifested in a few ways: #98803
  - If `num_replicas` was set to something other than 3, CockroachDB would still add or remove replicas to get to 3x replication.
  - If `num_voters` was set explicitly to get a mix of voting and non-voting replicas, it would be ignored. CockroachDB could possibly remove non-voting replicas.
  - If `range_min_bytes` or `range_max_bytes` were changed from their default values of 128 MiB and 512 MiB respectively, CockroachDB would instead try to size ranges to be within [128 MiB, 512MiB]. This could appear as an excess amount of range splits or merges, as visible in the Replication Dashboard under "Range Operations".
  - If `gc.ttlseconds` was set to something other than 90000 seconds, CockroachDB would still only GC data older than 90000s/25h. If the GC TTL was set to something larger than 25h, `AS OF SYSTEM TIME` queries going further back could start failing. For GC TTLs less than the 25h default, clusters would observe increased disk usage due to more retained MVCC garbage.
  - If `constraints`, `lease_preferences`, or `voter_constraints` were set, they would be ignored. Range data and leases would possibly be moved outside where prescribed.
- Fixed a bug where using the `ST_Transform` function could result in a memory leak. #98836
- Fixed a bug that caused incorrect results of tuples using the `ANY` operator. For example, an expression like `(x, y) = ANY (SELECT a, b FROM t WHERE ...)` could return `true` instead of the correct result of `NULL` when `x` and `y` were `NULL`, or `a` and `b` were `NULL`. This could only occur if the subquery was correlated, i.e., it references columns from the outer part of the query. This bug was present since the cost-based optimizer was introduced in v2.1. #98769
- Set `bulkio.restore.use_simple_import_spans` to `true`. If the setting is `false`, a `RESTORE` job can emit missed files from the first few spans of the job resume. #99068
- Fixed a bug introduced in v22.2.6 in which a `RESTORE` job, on `RESUME`, can miss files for the first few spans being restored. #99046
- Fixed a bug in which `RESTORE TABLE` may fail to restore a table with a missing sequence despite `skip_missing_sequences` being used. #99071
- Reverted a series of changes, introduced in v22.2.6, in which a `RESTORE` job that was retried or resumed after being paused could fail to correctly restore some rows. #99066

### Performance improvements

- If the session setting `optimizer_use_improved_split_disjunction_for_joins` is `true`, the optimizer now creates a better query plan in some cases where an inner, semi, or anti `JOIN` contains a join predicate with a disjuction (`OR` condition). #97823

### Build changes

- Upgraded Golang to version 1.19.6 #97761
- Starting with Cockroach v22.2.7, a FIPS compliant tarball and docker image is published for the Linux `x86_64` platform. The build uses OpenSSL libraries for crypto operations by `dlopen`ing the corresponding dynamic libraries. #97625
- Changes to source files in `pkg/ui/workspaces/db-console` now properly bust the build cache, and are consistently included in local builds. #97960

### Contributors

This release includes 84 merged PRs by 36 authors. We would like to thank the following contributors from the CockroachDB community:

- David López

## v22.2.6

Release Date: March 3, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.6

### Changelog

View a detailed changelog on GitHub: v22.2.5...v22.2.6

### Security updates

- Introduced the `server.user_login.downgrade_scram_stored_passwords_to_bcrypt.enabled` cluster setting, which defaults to `true`. If it is `true` and `server.user_login.password_encryption` is set to `crdb-bcrypt`, then during login, the stored hashed password will be converted from `SCRAM` to `bcrypt`. Previously, the process to change passwords from `SCRAM` hashing to `bcrypt` hashing was always manual and took a long time. An operator may wish to use this cluster setting if their tools do not support `SCRAM`. #97628

### Enterprise edition changes

- Fixed a bug in changefeeds where long running initial scans would fail to generate checkpoints. Failure to generate checkpoints is particularly bad if the changefeed restarts. Without checkpoints, the changefeed will restart from the beginning, and in the worst case, when exporting substantially sized tables, the changefeed initial scan may not complete. #97049

- Added support for an optional external ID when assuming a role. You can use this by extending the `ASSUME_ROLE` parameter to the format `ASSUME_ROLE={role};external_id={id}`. When using role chaining, you can associate each role in the chain with a different external ID. For example:

      ASSUME_ROLE={roleA};external_id={idA},{roleB};external_id=<idB>,{roleC}

  This will use external ID `{idA}` to assume delegate `{roleA}`, then use external ID `{idB}` to assume delegate `{roleB}`, and finally no external ID to assume the final role `{roleC}`. #96531

- Changefeeds no longer require the `COCKROACH_EXPERIMENTAL_ENABLE_PER_CHANGEFEED_METRICS` environment variable in order to use the `metrics_label` option. #97509

### SQL language changes

- Previously, setting a table's locality was not allowed if the table contained any hash-sharded indexes. This restriction is now removed. #96688
- Introduced the `declare_cursor_statement_timeout_enabled` session variable, which disables statement timeouts during `FETCH` when using `DECLARE CURSOR`. #97089
- Added a hard limit of how much data can be flushed to system tables for SQL stats. #97399
- Added support for expressions of the form `COLLATE "default"`, `COLLATE "C"`, and `COLLATE "POSIX"`. Since the default collation cannot be changed currently, these expressions are all equivalent. The expressions are evaluated by treating the input as a normal string, and ignoring the collation. This means that comparisons between strings and collated strings that use `"default"`, `"C"`, or `"POSIX"` are now supported. Creating a column with the `"C"` or `"POSIX"` collations is still not supported. #97415
- Previously, you could use user-defined function usage from tables with `SET DEFAULT` and `SET ON UPDATE` even though they are disallowed from `CREATE TABLE` and `ADD COLUMN`. This patch disallows those two cases from `ALTER TABLE ... ALTER COLUMN`. #97430
- Increased the default value of `sql.stats.cleanup.rows_to_delete_per_txn` to 10000, to increase efficiency of the cleanup job for SQL statistics. #97724

### Operational changes

- A `BACKUP` that encounters too many retryable errors will now fail instead of pausing to allow subsequent backups the chance to succeed. #96716

### DB Console changes

- Previously, when the SQL API returned a "max size reached" error, the DB Console would only show the error, but not the data that was also being returned. Now, the Statement Insights, Transaction Insights, and Schema Insights pages also show the data. #97470
- Added 22 new metrics to track memory usage of prepared statements in sessions. #97654
- Updated the description for Suboptimal Insight and added a Learn more link to it. #97718
- Added page controls to the recent execution overview pages so that users can go to the next page when the page limit of results is reached. #97250
- Fixed the pagination on the Schema Insights view, which was incorrectly showing the number of page results. #97641

### Bug fixes

- Fixed a bug that caused the server to crash if trying to restore a table from a backup generated by `BACKUP TABLE` from a schema that includes user-defined functions, and the restore target database does not have a schema with the same name. #96977
- Fixed the `SHOW GRANTS FOR public` command so that it no longer returns an error that the `public` role does not exist. #96998
- Fixed a bug where the `AS OF SYSTEM TIME` clause was handled incorrectly in an implicit transaction that had multiple statements. #97146
- When upgrading from a v22.1 to v22.2.4 cluster, logging in with a new user would error with `system.privilege does not exist`. When the user privilege was checked during starting up was causing this error. Only v22.2.4 is affected. This change adds a version gate for this check. #97183
- Fixed a syntax error for `SELECT ... QUERY` (without `AS`) statement. #97156
- Removed the following log message that was produced frequently: `lease [...] expired before being followed by lease [...]; foreground traffic may have been impacted`. #97377
- Fixed a bug in the query engine that could cause incorrect results in some cases when a zigzag join was planned. The bug could occur when the two indexes used for the zigzag join had a suffix of matching columns, but with different directions. For example, planning a zigzag join with `INDEX(a ASC, b ASC)` and `INDEX(c ASC, b DESC)` could cause incorrect results. This bug has existed since at least v19.1. It is now fixed, because the optimizer will no longer plan a zigzag join in such cases. #97441
- Fixed a bug in `ALTER TABLE ... ADD COLUMN` when the new column name requires quoting due to mixed case or special characters and the statement is not run in an explicit or multi-statement transaction. #97568
- Fixed a bug when formatting create statements for user-defined types that require quoting, which might prevent those statements from round-tripping. #97568
- Added support for disabling cross-descriptor validation on lease renewal, which can be problematic when there are lots of descriptors with lots of foreign key references. In these cases, the cross-reference validation could block schema changes. This can be enabled with `sql.catalog.descriptor_lease_renewal_cross_validation`. #97635
- Fixed a bug that would cause node failure when the `kv.snapshot_delegation.enabled` setting is set to `true`. Further, this unsupported setting is now hidden. #97648
- Fixed a bug that could cause reverse scans to serve stale reads when clocks in a cluster are skewed. Transaction uncertainty intervals are correctly configured for reverse scans again. #97518
- Columns referenced in partial index predicates and partial `UNIQUE` constraint predicates can no longer be dropped. The `ALTER TABLE .. DROP COLUMN` statement now returns an error with a hint suggesting to drop the indexes and constraints first. This is a temporary safeguard to prevent users from hitting #96924. This restriction will be lifted when that bug is fixed. #97678
- Fixed a bug where a backup of keys with many revisions would fail with `pebble: keys must be added in order`. #97062
- Previously, `ALTER TABLE ... INJECT STATISTICS` command would fail if a column with `COLLATED STRING` type had histograms to be injected. This is now fixed. The bug has been present since at least v21.2. #97491

### Contributors

This release includes 71 merged PRs by 32 authors.

## v22.2.5

Release Date: February 16, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.5

### Changelog

View a detailed changelog on GitHub: v22.2.4...v22.2.5

### Bug fixes

- Fixed a bug that prevented non-admin users from connecting to a cluster that was upgraded to v22.2.4 after a previous major version upgrade to v22.2.x was not finalized. #97183

## v22.2.4

Release Date: February 13, 2023

### Downloads

Warning:

This patch release has been withdrawn. We've removed the links to the downloads and Docker image.All the changes listed as part of this release will be in the next release. Do not upgrade to this release.

### Security updates

- Added the `COCKROACH_TLS_ENABLE_OLD_CIPHER_SUITES` environment variable, which re-enables some less secure TLS 1.2 cipher suites for use with legacy clients. #95705

### Enterprise edition changes

- Fixed a bug in `ALTER CHANGEFEED` that would panic when altering a changefeed to remove a table that had already been dropped. #95785
- Updated memory accounting for changefeeds to improve cluster stability. #96182
- The `confluent_schema_registry` URI for avro changefeeds now supports the `client_cert` and `client_key` parameters. #96533

### SQL language changes

- The `SHOW GRANTS ON EXTERNAL CONNECTION` and `SHOW SYSTEM GRANTS` commands now use a column name of `privilege_type` rather than `privilege`. For external connections, the `name` column was changed to `connection_name`. This makes the commands consistent with other `SHOW GRANTS` commands. #95567
- If `copy_from_retries_enabled` is set, `COPY` is now able to retry under certain safe circumstances - namely when `copy_from_atomic_enabled` is false, there is no transaction running `COPY`, and the error returned is retryable. This prevents users who keep running into `TransactionProtoWithRefreshError` from having issues. #95496
- Improved the performance of trigram operations. #94199
- `COPY ... FROM ... QUOTE '"'` will no longer error. #96766

### Operational changes

- Histogram metrics in the DB Console (such as on the SQL Dashboard, for example) can now optionally use the legacy `HdrHistogram` model by setting the environment variable `COCKROACH_ENABLE_HDR_HISTOGRAMS=true` on CockroachDB nodes. Note that this is not recommended unless users are having difficulties with the newer Prometheus-backed histogram model. Enabling these metrics can cause performance issues with timeseries databases like Prometheus, as processing and storing the increased number of buckets is taxing on both CPU and storage. Note that the `HdrHistogram` model is slated for full deprecation in an upcoming release. #96514
- Prometheus histograms will now export more buckets across the board to improve precision & fidelity of information reported by histogram metrics, such as quantiles, as shown in multiple pages in the DB Console. This will lead to an increase in storage requirements to process these histogram metrics in downstream systems like Prometheus, but should still be a marked improvement when compared to the legacy `HdrHistogram` model. If users have issues with the precision of these bucket boundaries, they can set the environment variable `COCKROACH_ENABLE_HDR_HISTOGRAMS=true` to revert to using the legacy `HdrHistogram` model instead, although this is not recommended otherwise as the `HdrHistogram` metrics strain systems like Prometheus with excessive numbers of histogram buckets. Note that the `HdrHistogram` model is slated for full deprecation in an upcoming release. #96514

### DB Console changes

- Removed `reset sql stats` and `reset index stats` from the SQL Activity Page when the user is not an `admin` user. #95331
- The statement fingerprint details page in the DB Console no longer gets stuck infinitely loading after 5 minutes. #95246
- Graphs in the DB Console can be clicked on to toggle legend "stickiness" and make the points stop following the mouse. This makes it easier to read dense graphs with many series plotted together. #94787
- Removed `reset sql stats` from the Transactions page for non-admin users. #95520
- The DB Console now hides the apply option for index recommendation when user is not an `admin` user. #95598

### Bug fixes

- Fixed a crash that could happen when formatting a tuple with an unknown type in the pgwire protocol. #95401
- Fixed a rare bug which could cause upgrades from v22.1 to v22.2 to fail if the job coordinator node crashes in the middle of a specific upgrade migration. #93553
- Fixed a bug where `CLOSE ALL` on a cursor would not respect the `ALL` flag and would instead attempt to close a cursor with no name. #95442
- DB Console features that check for the `VIEWACTIVITYREDACTED` privilege now also account for global privileges. #95457
- Fixed a bug where a database restore would not grant `CREATE` and `USAGE` privileges on the public schema to the public role. #95531
- Reduced contention between registering and deregistering sessions, as well as query cancellation inside a session. #95626
- Fixed the `pg_get_indexdef` function so that it shows the expression used to define an expression-based index. Also fixed a bug where the function was previously including columns stored by the index, which was incorrect. #95584
- Fixed a bug present since v22.2 when adding new columns to a table with `DEFAULT` expressions that differ from the type of the column. In CockroachDB you can do this as long as the expression's type can be cast in an assignment context. When adding a new column, the code in the backfill logic was not sophisticated enough to know to add the cast; when such a default expression was added to a new column it would result in a panic during the backfill. #95451
- Fixed a bug where a DNS lookup was performed during gossip remote forwarding while holding the gossip mutex. This could cause processing stalls if the DNS server was slow to respond, since we need to acquire gossip read locks in several performance critical code paths, including Raft processing. #95443
- Operations like `BACKUP` can now reuse a previously created AWS KMS client if the client was created with the same parameters. This addresses the `NoCredentialProviders` errors on EC2 with for backups with long incremental chains. #95534
- Fixed a bug where trigrams ignored Unicode (multi-byte) characters from input strings. #94199
- Fixed the `array_to_string` built-in function so that nested arrays are traversed without printing 'ARRAY' at each nesting level. #95843
- Fixed a bug in which `RESTORE SYSTEM USERS` would fail to restore role options. #95293
- Fixed a bug causing ranges to remain without a leaseholder in cases of asymmetric network partitions. #95221
- Fixed a bug whereby a stalled disk would sometimes be undetected, hanging the CockroachDB process indefinitely. Now the stall is detected and the process is terminated if the `storage.max_sync_duration.fatal.enabled` cluster setting is enabled, and the stall is observed to last longer than the value in the `storage.max_sync_duration` cluster setting. #96036
- Fixed a bug where `COPY`ing into a column with collated strings would result in an error similar to `internal error: unknown type collatedstring`. #96035
- Fixed a bug in temporary schemas whereby `DISCARD ALL` or `DISCARD TEMP` could prevent temporary tables from working. #96102
- Fixed an internal error which may occur in the `SHOW RANGE FROM TABLE` statement when the `FOR ROW` clause specifies a `BYTE` literal and the corresponding column data type is `BIT`. #96106
- Fixed a bug whereby a system check constraint on `statement_diagnostics_requests` was not properly added when upgrading from the previous CockroachDB release. #96220
- Fixed a bug where a node with a disk stall would continue to accept new connections and preserve existing connections until the disk stall abated. #96145
- Fixed a bug where the global `NOSQLLOGIN` privilege was ignored entirely, so it had no effect. The bug was introduced in v22.2.0-alpha.1. The `NOSQLLOGIN` role option is unaffected by this bug. #96520
- Fixed a bug where a disk stall could go undetected under the rare circumstance that several goroutines simultaneously sync the data directory. #96662
- The SQL Activity page will no longer crash (showing the error page) when upgrading to v22.2. #96454

### Performance improvements

- In v22.2, we introduced support for `DISCARD TEMP` and made `DISCARD ALL` actually discard temp tables. This implementation ran expensive logic to discover temp schemas rather than consulting in-memory data structures. As a result, `DISCARD ALL`, which is issued regularly by connection pools, became an expensive operation when it should have been cheap. This problem is now resolved. #96102
- In v22.2, logic was added to make `SET SESSION AUTHORIZATION DEFAULT` not a no-op. This implementation used more general code for setting the role for a session which made sure that the role exists. The check for whether a role exists is currently uncached. We don't need to check if the role we already are exists. This improves the performance of `DISCARD ALL` in addition to `SET SESSION AUTHORIZATION DEFAULT`. #96102

### Contributors

This release includes 85 merged PRs by 40 authors.

## v22.2.3

Release Date: January 23, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.3

### Changelog

View a detailed changelog on GitHub: v22.2.2...v22.2.3

### Backward-incompatible changes

- CockroachDB no longer populates or supports the `crdb_internal.gossip_network` table. #94074

### SQL language changes

- Previously, error messages for missing users sometimes had different forms. This is now unified as `role/user "{user}" does not exist`. #94735
- It is now possible to create and alter not visible indexes using the alias `INVISIBLE`. The alias can be used anywhere `NOT VISIBLE` is used when creating or altering indexes. Note that the `INVISIBLE` alias is not supported for `NOT VISIBLE` columns. #95018
- `COPY` now logs an error during the insert phase on the `SQL_EXEC` logging channel. #95176

### Bug fixes

- In the PostgreSQL extended protocol mode, it was possible for auto-commits to not execute logic for DDL statements, when certain DML (insert/update/delete) and DDL statements were combined in an implicit transaction. Auto-commits are now disabled inside the planner if any DDL statements were executed earlier. #93717
- Fixed a panic that occurred when using a SQL cursor to access tables in the `crdb_internal` schema. #94443
- Fixed a bug that caused incorrect selectivity estimation for queries with ORed predicates all referencing a common single table. #94664
- Fixed a bug in join queries that involves tables with unique constraints using `LIMIT`, `GROUP BY`, and `ORDER BY` clauses to ensure the optimizer considers streaming group-by with no TopK operation when possible. This is often the most efficient query plan. #94603
- Fixed a bug where certain `GRANT` or `REVOKE` commands on a user that does not exist would error with the incorrect PG code. #94735
- Fixed a crash that occurs on the gateway node when collecting a statement diagnostics bundle (e.g., `EXPLAIN ANALYZE (DEBUG)`) on a statement that fails with certain errors. This crash has existed in various forms since the introduction of statement bundles in v20.1.0. #94869
- Fixed a v22.1 compatibility bug in clusters with mixed v2.1/v22.1 nodes where range replica changes (moving replicas, up/down replication, splits, and merges) could sometimes fail on v22.1 leaseholders with an error of the form `"change replicas of r47 failed: descriptor changed: [expected] != [actual]"`, without showing any apparent difference between the listed descriptors. This would not affect the upgrade itself, and either continuing to upgrade all nodes to v22.2 or rolling nodes back to v22.1 (possibly with an additional restart) will resolve the issue. #94888
- It is now possible to run `cockroach version` and `cockroach start` (and possibly other sub-commands) when the user running the command does not have permission to access the current working directory. #94927
- Fixed a bug that caused `pg_function_is_visible` to always report that any user-defined function was visible. It now correctly uses the `search_path` to determine visibility. #94959
- Fixed a bug that caused an internal error occurring in `CASE` expressions when a column present in a `THEN` or `ELSE` expression is of an inequivalent type compared to that of a constant this column is compared to in an equality predicate. For example, `(CASE WHEN false THEN int_col ELSE 1 END) IN (int_col) AND int_col=3/2`. #95178

### Performance improvements

- Significantly reduced CPU usage of the underlying gossip network in large clusters. #94074
- Refactored the query logic when fetching database index recommendations for the Database Details API endpoint. This greatly reduces the query time and cost, particularly for large schemas. #94923

### Contributors

This release includes 44 merged PRs by 28 authors.

## v22.2.2

Release Date: January 4, 2023

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.2

### Changelog

View a detailed changelog on GitHub: v22.2.1...v22.2.2

### SQL language changes

- Fixed a bug where CockroachDB could crash if a user creates a user-defined function (UDF) whose function signature includes an implicit record type which has a column using a user-defined enum type. #94241
- Added the `log_timezone` session variable, which is read only and always UTC. #94346
- Implemented the `pg_timezone_names` pg_catalog table, which lists all supported timezones. #94346

### DB Console changes

- Updated metric graph tooltip styling to prevent content collapse. #93928
- Updated UI to show correct login information in the top right corner for secure clusters, and fixed documentation links to correctly reference the current cluster version as necessary. #94067

### Bug fixes

- Fixed a bug where the `session_id` session variable would not be properly set if used from a subquery, #93856
- Fix a bug that would result in incomplete backups when non-default, non-public resource limiting settings (`kv.bulk_sst.max_request_time` or `admission.elastic_cpu.enabled`) were enabled. #93728
- Updated the volatility of the `hmac`, `digest`, and `crypt` built-in functions to be immutable. #93924
- Server logs will now correctly `fsync` at every `syncInterval`. #93995
- The `stxnamespace`, `stxkind` and `stxstattarget` columns are now defined in `pg_statistics_ext`. #94009
- The `CREATE ROLE`, `DROP ROLE`, `GRANT`, and `REVOKE` statements no longer work when the transaction is in read-only mode. #94103
- Fixed a bug where CockroachDB could crash in rare circumstances when evaluating lookup and index joins. The bug has been present since the 22.2.0 release. #94100
- Fixed a bug where, when experimental MVCC range tombstones are enabled (they are disabled by default), a bulk ingestion (e.g., an `IMPORT`) could fail to take a committed-but-unresolved write intent into account during conflict checks when written above an MVCC range tombstone. It was therefore possible in very rare circumstances for the ingestion to write a value below the timestamp of the committed intent, causing the ingested value to disappear. #94006
- Fixed a bug that could prevent CASE expressions that used placeholder return values from type-checking correctly. #93923
- Fixed a bug where, when experimental MVCC range tombstones are enabled (they're disabled by default), a bulk ingestion (e.g., an `IMPORT`) could in some situations fail to properly check for conflicts with existing MVCC range tombstones. This could cause the ingestion to write below a recently written MVCC range tombstone, in turn losing the ingested data. This could only happen in rare circumstances where a bulk ingestion was applied concurrently with an import cancellation. #94115
- Fixed a bug that could happen when type-checking an array expression that only contains NULLs and placeholder values. The bug was only present in v22.2.1. #94243
- Fixed a bug introduced in 22.1 where tables which receive writes concurrent with portions of an `ALTER TABLE ... SET LOCALITY REGIONAL BY ROW` statement may fail with an error: `duplicate key value violates unique constraint "new_primary_key"`. #94251
- Previously, CockroachDB could encounter an internal error when evaluating window functions with `RANGE` window frame mode with `OFFSET PRECEDING` or `OFFSET FOLLOWING` boundary when an `ORDER BY` clause has the `NULLS LAST` option set. This will now result in a regular error since the feature is marked as unsupported. #94352
- Record types can now be encoded with the binary encoding of the PostgreSQL wire protocol. Previously, trying to use this encoding could case a panic. #94419
- Fixed a bug where CockroachDB could delay the release of the locks acquired when evaluating `SELECT FOR UPDATE` statements in some cases. This delay (up to 5s) could then block the future readers. The bug was introduced in 22.2.0. #94401

### Performance improvements

- The optimizer can now avoid planning a sort in more cases with joins that perform lookups into an index with one or more columns sorted in descending order. This can significantly decrease the number of rows that have to be scanned in order to satisfy a `LIMIT` clause. #93770
- Improved the performance of `crdb_internal.default_privileges` population. #94336

### Contributors

This release includes 46 merged PRs by 25 authors.

## v22.2.1

Release Date: December 22, 2022

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.1

### Changelog

View a detailed changelog on GitHub: v22.2.0...v22.2.1

### General changes

- CockroachDB Docker images are now multi-architecture manifests supporting the `x86_64` (`amd64`) and `arm64` architectures. #90307
- Bulk operations now log the destinations they are connecting to in redacted form. For example, `backup planning to connect to destination gs://test/backupadhoc?AUTH=specified&CREDENTIALS=redacted`. #92208

### Backward-incompatible changes

- Changefeeds will now treat all errors, unless otherwise indicated, as retryable errors. As a result, a changefeed may not fail when it previously would have, which could cause it to become stuck retrying. #92824

### Enterprise edition changes

- Changefeeds connected to Kafka sinks no longer automatically retry when emitting a message batch that is rejected by the server. This is a temporary rollback of the functionality. #90036
- Improved the performance of the changefeed JSON encoder by 50%. #91002
- Added the cluster setting `changefeed.event_consumer_workers` that allows changefeeds to process events concurrently. #91002
- Improved the throughput of changefeeds emitting to cloud storage sinks. #91002
- Changefeeds can now emit files compressed with the `zstd` algorithm, which provides good compression, and is much faster than `gzip`. In addition, a new faster implementation of `gzip` is used by default. #91002
- Changefeed exports are up to 25% faster due to uniform work assignment. #91002
- The JWT authentication cluster setting can now be modified from within tenants to better support CockroachDB Serverless use cases. #92755
- Added the optional JSON field `"Compression"` to the changefeed `kafka_sink_config` option. This field can be set to `"none"` (default), `"GZIP"`, `"SNAPPY"`, `"LZ4"`, or `"ZSTD"`. Setting this field will result in the specified compression algorithm being used when emitting events. #91275

### SQL language changes

- Changed the `backup.restore_span.target_size` cluster setting to default to `384MiB` so that a restore merges up to that size of spans when reading from the backup before actually ingesting data. This should reduce the number of ranges created during restore and thereby reduce the merging of ranges that needs to occur after the restore completes. #89351
- Added the `SHOW FUNCTIONS` command, which lists user-defined functions. The `SHOW FUNCTIONS FROM <schema>` syntax is supported too. #89760
- `SHOW CREATE TABLE` now shows the hash-sharded index check constraints if it is set to `NOT VALID`. #89750
- Marked the `sql.defaults.experimental_auto_rehoming.enabled` cluster setting as hidden. Also, renamed the `experimental_enable_auto_rehoming` session variable to `enable_auto_rehoming` and created an alias `experimental_enable_auto_rehoming` for the renamed session variable. #90182
- Star expressions, e.g., `SELECT * FROM ...` are no longer allowed in statements in user-defined functions. They were allowed in early betas of v22.2 from v22.2.0-beta.1 to v22.2.0-beta.4, but have been disallowed because they do not behave correctly. Issue #90080 tracks re-enabling star expressions in UDFs. #90170
- The cluster setting `sql.ttl.default_range_concurrency` and the table storage parameter `ttl_range_concurrency` are no longer configurable. #90294
- Added the cluster setting `cloudstorage.azure.concurrent_upload_buffers` that configures the number of concurrent buffers used when uploading files to Azure storage. #90409
- Added the new column `plan_gist` to `crdb_internal.{node,cluster}_queries` in order to represent the compressed logical plan. #90557
- Added the `sql.auth.change_own_password.enabled` cluster setting, which defaults to `false`. When set to `true`, any user is allowed to change their own password to a non-null value. Changing other role options still has the same privilege requirements as before (either `CREATEROLE` or `CREATELOGIN`, depending on the option). #90626
- Added a new column `implicit_txn` (boolean) to `crdb_internal.cluster_execution_insights` and `crdb_internal.node_execution_insights`. #90860
- Previously, if a primary key name was a reserved SQL keyword, attempting to use the `DROP CONSTRAINT, ADD CONSTRAINT` statements to change a primary key would result in a constraint already exists error. This is now fixed.#90992

- Currently the `AS OF SYSTEM TIME` value is set at the start of the TTL job (TTL cutoff - `30s`), but this results in an error similar to the following for TTL jobs that run longer than `gc.ttlseconds`:

      error selecting rows to delete: ttl select defaultdb.public.events: batch timestamp 1666883527.780656000,0 must be after replica GC threshold 1666883574.542825089,0

  Now, the `AS OF SYSTEM TIME` value is relative to when each `SELECT` query is run (query time - `30s`), which will prevent the error. However, each `SELECT` query will run against a different table state. If records are missed during one job invocation, they should still be picked up in the next. #91111

- Changed the default value of the `sql.metrics.statement_details.plan_collection.enabled` cluster setting to `false`. #89920

- Implemented the `to_char(timestamp, string)` and `to_char(interval, string)` built-in functions. #91541

- Added an estimate for the number of request units consumed by a query to the output of `EXPLAIN ANALYZE` for tenant sessions. #93179

- `to_char` now has caching for parse formats. This shows a speed improvement when running `to_char` with the same format between sessions. #93330

- SQL queries running on remote nodes now show up in CPU profiles with `distsql.*` labels. Currently, these include `appname`, `gateway`, `txn`, and `stmt`. #93516

- Implemented the `parse_ident` built-in. The function splits a qualified identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is `false`, then such extra characters are ignored. #93635

### Operational changes

- Introduced a cluster setting `kv.mvcc_gc.queue_interval` that controls how long the MVCC GC queue waits between processing replicas. It was previously hardcoded to `1s`, but is now configurable (`1s` continues to be the default value). Incidents were observed where a large volume of MVCC GC work can prove to be disruptive to foreground traffic. Previously, this GC work had been reduced in priority to make it less disruptive. This cluster setting can serve as a manual form of pacing if the automatic approach proves insufficient. #89423
- Renamed the following TTL metrics:
  - `jobs.row_level_ttl.range_total_duration` to `jobs.row_level_ttl.span_total_duration`
  - `jobs.row_level_ttl.num_active_ranges` to `jobs.row_level_ttl.num_active_spans` #90359
- The cluster setting `kv.store.admission.provisioned_bandwidth` was renamed to `kvadmission.store.provisioned_bandwidth`. #92439
- Made the consistency check failure message more informative by suggesting a few actions that operatios should/could do in the event it occurs. #90564
- Logs produced by setting an increased `vmodule` setting for `s3_storage` are now directed to the `DEV` channel rather than `STDOUT`. #89140
- Introduced the metric `replicas.leaders_invalid_lease` that indicates how many replicas are raft group leaders, but holding invalid leases. #91193

### Command-line changes

- The `\df` metacommand was added to the SQL shell, which will list all user-defined functions in the current database. #89760

### DB Console changes

- Fixed a bug that prevented usage of profiling links on the Advanced Debug page. #89197
- The High Contention Time insight description now accurately reflects the event's contention duration in the DB Console. #89125
- Requests to fetch table and database statistics now have limited concurrency. This may make loading these pages slower, but should result in reducing any performance impact these pages might have on the database under high-traffic scenarios. #90496
- The Jobs Page now includes a column picker. #90484
- Added Overview and Explain Plan tabs to the Active Statement Details and Statement Insight Details pages. #90557
- Added a link for each fingerprint ID value for statements and transactions on Insights page to its respective Details page, displaying the time period of the execution of that statement/transaction. #90860
- Fixed the Transaction filter label on the SQL Activity page. #91314
- Changed the height of the column selector to better indicate when there are more options to be selected once scrolled. #91909
- Added the fingerprint ID in hexadecimal format to the Statement Details page and Transaction Details page. #91938
- Added the `Service Latency: SQL Statements, 99.9th percentile` and `Service Latency: SQL Statements, 99.99th percentile` charts to the Metrics page, under the SQL view. #92715
- Updated the tooltip in the `SQL Statement Errors` chart on the Metrics page. #92712
- Graphs on the Metrics page now downsample using maximum value instead of average value. Previously, zooming out on a graph would cause any spikes in the graph to smooth out, potentially hiding anomalies. By using the maximum value, these anomalies are visible even when looking at a zoomed-out interval. #92296
- Renamed the chart on the Statement Details page from `Statement Execution and Planning Time` to `Statement Times`. #92780
- Switched the order of Transaction and Statement views on the Workload Insights tab. #92936
- The Insights pages in the DB Console now includes the unit of time (seconds and milliseconds) for all timestamp values. #92945
- Added a link to the fingerprint ID in the high contention table on the Transaction Insights Details page. #92922
- Added an Apply button on Table Details page when there is a recommendation to drop an unused index. #92921
- Added a Goroutine scheduling latency graph to the Overload dashboard in the UI. It shows what the per-node p99 scheduling latency is for Goroutines. #93235
- Removed the feedback survey link from the DB Console. #93279
- Previously, graphs that displayed totals based on `totalCount` could show an "undefined value" if no value was passed. Now, a default value of `0` is used for the total if no value is otherwise provided. #89931
- Adjusted the Statement Details page so that displayed charts no longer overlap. #90088
- Sending the proper start/end values to the endpoint used on SQL Activity page now returns the full hour as described on the UI. #90860
- Displayed filters in the DB Console will now scroll if a filter is large, ensuring that the Apply button is always reachable. #90480
- Added a horizontal scroll to the table on the Explain Plan tab under the Statement Details page. #91326
- The filter is no longer cut on the Sessions page. #91327
- Added a horizontal scroll to the Waited On table on Transaction Insight details page. #91480
- Fixed the Statement Activity page so that it no longer shows multi-statement implicit transactions as "explicit." #92429
- Added a sort setting to tables on the Transaction and Statement Insights Details pages. #92752
- Fixed a bug where selecting a relatively small timeframe in the past causes no data to render on graphs. #93621
- Loading the Database Details page in the UI is now somewhat less expensive when there are a large number of databases and a large number of tables in each database and a large number of ranges in the cluster. #91015

### Bug fixes

- Fixed a bug in the legacy schema changer where database comments were not dropped together with the database. #91708
- Fixed a bug that could cause crashes when parsing malformed change data capture transformations. #90843
- Fixed a bug that could cause changefeeds to fail during a rolling restart. #90661
- Previously, when a statement bundle was collected for a query that resulted in an error due to a `statement_timeout` the bundle would not be saved. This is now fixed. #89129
- Fixed a bug that has existed since v2.1.0 where queries containing a subquery with `EXCEPT` could produce incorrect results. This could happen if the optimizer could guarantee that the left side of the `EXCEPT` always returned more rows than the right side. In this case, the optimizer made a faulty assumption that the `EXCEPT` subquery always returned at least one row, which could cause the optimizer to perform an invalid transformation, which could possibly cause the full query to return incorrect results. #89135
- Fixed a bug causing incorrect results from the floor division operator, `//`, when the numerator is non-constant and the denominator is the constant 1. #89262
- Fixed a bug causing missing automatic statistics collection at cluster startup when the `sql.stats.automatic_collection.enabled` cluster setting is `false`, but there are tables with the storage parameter `sql_stats_automatic_collection_enabled` set to `true`. #88763
- Fixed a bug in the `Concat` projection operators for arrays that could cause non-null values to be added to the array when one of the arguments was `NULL`. #89055
- Fixed a bug that has existed in v21.1 and earlier that could cause an internal error when executing a query with a limit ordering on the output of a window function. #87747
- Fixed a longstanding bug that could cause a panic when running a query with `EXPLAIN` that attempts to order on a non-output column. #88687
- Fixed a bug that could cause incorrect results in rare cases. The bug could only present if the following conditions were true:
  1. A query with `ORDER BY` and `LIMIT` was executed.
  2. The table containing the `ORDER BY` columns had an index containing those columns.
  3. The index in (2) contained a prefix of columns held to a fixed number of values by the query filter (e.g., `WHERE a IN (1, 3)`), a `CHECK` constraint (e.g., `CHECK (a IN (1, 3))`), inferred by a computed column expression (e.g., `WHERE a IN (1, 3)` and a column `b INT AS (a + 10) STORED`), or inferred by a `PARTITION BY` clause (e.g., `INDEX (a, ...) PARTITION BY LIST (a) (PARTITION p VALUES ((1), (3)))`). This bug was present since version v22.1.0. #89250
- Previously, when writing storage checkpoints on consistency checker failures, flushing WAL was disabled, so some checkpoints could be slightly out of date. This is now fixed. #89403
- Adjusted optimizer selectivity and cost estimates of zigzag joins in order to prevent query plans from using it when it would perform poorly (when many rows are qualified). #89427
- Updated hot ranges, problem ranges, data distribution, stores report, range status, Raft for all ranges features to require `VIEWCLUSTERMETADATA`. Updated the `SHOW CLUSTER SETTING` SQL command to require `VIEWCLUSTERSETTING/MODIFYCLUSTERSETTING` privileges. Fixed a visual bug on stores report where an error shows an infinite spinner only. #89508
- Fixed a bug introduced in v20.2 that could cause filters to be dropped from a query plan with many joins in rare cases. #89158
- Narrowed the conditions under which a `VOTER_DEMOTING_LEARNER` can acquire the lease in a joint configuration to: a) There must be a `VOTER_INCOMING` in the configuration, and b) The `VOTER_DEMOTING_LEARNER` was the last leaseholder. This prevents it from acquiring the lease unless it is the only one that can acquire it. Transferring the lease away is necessary before exiting the joint configuration (without the fix the system can be stuck in a joint configuration in some rare situations). #89594
- Fixed tables created by userfile storage that have invalid foreign key constraints. #89371
- Fixed a crash that could occur when dropping a role that owned two schemas with the same name in different databases. The bug was introduced in v22.1.0. #89534
- Excluded check constraints of hash-sharded indexes from being invalidated when executing `IMPORT INTO`. #89525
- Avoided a source of internal connectivity problems that would resolve after restarting the affected node. #89597

- Previously, uncommitted privileges could be cached if a transaction is rolled back. This is now fixed. This bug was only present in the v22.2 alpha and beta versions. Example:

      BEGIN;
      GRANT SELECT ON crdb_internal.tables TO testuser;
      SELECT has_table_privilege('testuser', 'crdb_internal.tables', 'SELECT'); --- this caches the privilege ---
      ROLLBACK; --- SELECT IS STILL CACHED UNTIL ANOTHER GRANT/REVOKE HAPPENS TO INVALIDATE THE CACHE---

#89717

- Fixed a bug in `pg_catalog` tables that could result in an internal error if a schema is concurrently dropped. #88602
- Fixed a bug that caused queries with expressions like `'foo' LIKE col` to return incorrect values. The bug only occurs when an inverted trigram index exists on `col`. The bug is only present in beta versions of v22.2. #89700
- Fixed a bug that caused internal errors in rare cases when running common table expressions (statements with `WITH` clauses). This bug is only present in v22.2.0-beta.2, v22.2.0-beta.3, v21.2.16, and v22.1.9. #89855
- Fixed a bug that caused trailing characters to be silently truncated when attempting to convert corrupt JSON string input into JSONb. #89926
- Fixed a bug that caused changefeeds to permanently error on a `failed to send RPC` error. #89527
- Restoring a backup with a table containing `UniqueWithoutIndexConstraints` would fail because of incorrect tableIDs being referenced in the constraints stored on the restored table. This is now fixed. #89745
- Fixed a bug that caused incorrect results for queries with string similar filters (e.g., `col % 'abc'`) on tables with trigram indexes. This bug is only present in v22.2 pre-release versions up to and including v22.2.0-beta.3. #90163
- Fixed a bug where zone configs generated for a database with a secondary region were invalid. The `voter_constraints` and the lease preferences called for voters and leaseholders to exist in both the primary and secondary region, which is impossible. This bug was present since v22.2.0-alpha.1. #90184
- Fixed a bug causing an issue with the `enforce_home_region` session setting, which may cause `SHOW CREATE TABLE` or other non-DML statements to error out if the optimizer plan for the statement involves accessing a multi-region table. #90204
- Fixed a bug that could potentially cause changefeeds with `initial_scan_only` to miss messages. Now, the changefeed ensures that all messages have successfully flushed to the sink prior to completion. #90277
- Now, during JWT-based authentication, algorithm type is inferred if it is not specified by the JWKS. This enables support for a wider range of JWKS. #90289
- Fixed a bug that caused incorrect evaluation of comparison expressions involving time and interval types, like `col::TIME + '10 hrs'::INTERVAL' > '01:00'::TIME`. #90368
- Fixed a bug causing detection and erroring out of queries using a locality-optimized join when session setting `enforce_home_region` is `true` and the input table to the join has no home region or its home region does not match the gateway region. #90199
- Fixed a bug introduced in v22.1.9 that caused nodes to refuse to run jobs under rare circumstances. #90271
- Fixed the calculation of the `pg_attribute.attnum` column for indexes so that the `attnum` is always based on the order the column appears in the index. Also fixed the `pg_attribute` table so that it includes stored columns in secondary indexes. #90458
- Previously, during restore planning, the restoring cluster's codec was accidentally used to reason about spans in the backup manifest. When a backup was restored by a different tenant, two bugs described in #90475 and #90474 could occur. This is now fixed. #90527
- TTL decoding error messages now correctly contain hex-encoded key bytes instead of hex-encoded key pretty-printed output. #90723
- When running Cockroach inside of a Docker container on macOS and mounting a host filesystem into the container, the total available capacity calculation of the filesystem could be reported incorrectly. This is now fixed. #90873
- Fixed a bug that caused incorrect results and internal errors when a `LEFT JOIN` operated on a table with virtual computed columns. The bug only presented when the optimizer planned a "paired joiner". Only values of the virtual columns would be incorrect—they could be `NULL` when their correct value was not `NULL`. An internal error would occur in the same situation if the virtual column had a `NOT NULL` constraint. This bug was present since version v22.1.0. #90997
- In large, multi-region clusters, it was possible for the leasing mechanism used for jobs to get caught in a live-lock scenario whereby jobs could not be adopted. This bug has been resolved. #91037
- `REASSIGN OWNED BY` could run into errors, if any descriptor owned by the user is currently being dropped. This is now fixed #90388
- Fixed a bug that could cause `SELECT *` operations on tables with virtual computed columns undergoing schema changes to potentially fail. #91007
- Fixed a bug where point lookups on the `pg_catalog.pg_type` table would fail to find the implicit record type that gets created for tables in the `pg_catalog`, `information_schema`, and `crdb_internal` schemas. #91217
- Fixed a bug that prevented the usage of implicit record types for tables in the `pg_catalog`, `information_schema`, and `crdb_internal` schemas. #91217
- Fixed a bug which caused a migration in v22.1 to fail to drop an index on the `system.statement_diagnostics_requests` table. This caused upgrades from v22.1 to v22.2, which had used the previous, faulty upgrade migration to now fail to create a new index with the same name, as the index was assumed to have been dropped previously. #91308
- A nil pointer crash that could be encountered when interleaving `SELECT FOR UPDATE SKIP LOCKED` statements has been resolved. #91256
- Fixed a bug present only in v22.2 release candidates, in which an `ALTER PRIMARY KEY USING COLUMNS (x, x)` statement would result in an internal error instead of the expected user-facing error with a pg-code. #91478
- Fixed a bug in which panics triggered by certain DDL statements were not properly recovered, leading to the cluster node crashing. #91552
- Fixed a rare bug where concurrent follower read/split operations could lead to invalid read results. #90624
- Previously, certain aggregate histograms would appear in `_status/vars`, but not be available for graphing in the DB Console. These are now available. They include changefeed-related histograms and row-level TTL histograms. #90806
- Fixed a panic that could occur when calling `st_distancespheroid` or `st_distancesphere` with a spatial object containing an `NaN` coordinate. This now produces an error, `"input is out of range"`. #91535
- Fixed a bug that could result in a changefeed missing rows which occur around the time of a split in writing transactions that take longer than the closed timestamp target duration (defaults to 3s). #91748
- Fixed a bug that resulted in the regions listed for databases and tables including an incorrect list of regions due to the logic including information about tables which are adjacent in the keyspace. #91393
- Fixed a bug that would cause `SHOW BACKUP` and `RESTORE` of encrypted incremental backups to fail. #91925
- Added leading zeros to fingerprint IDs with less than 16 characters. #91938
- Fixed a bug that resulted in some retryable errors not being retried during an import. #89426
- Fixed a bug in `changefeed.batch_reduction_retry` that erroneously limited retries to a single level. #90205
- Fixed a bug pre-v21.1 wherein `cgroup` memory limit was undetected when using `systemd`. #92032
- Fixed a bug introduced in v21.2 that could cause an internal error in rare cases when a query required a constrained index scan to return results in order. #87735
- Fixed an unhandled error that could happen if `ALTER DEFAULT PRIVILEGES` was run on the system database. #92079
- Fixed a bug existing since v20.2 that could cause incorrect results in rare cases for queries with inner joins and left joins. For the bug to occur, the left join had to be in the input of the inner join and the inner join filters had to reference both inputs of the left join, and not filter `NULL` values from the right input of the left join. Additionally, the right input of the left join had to contain at least one join, with one input not referenced by the left join's `ON` condition. #92033
- Fixed a bug causing changefeeds to fail when a value is deleted while running on a non-primary column family with multiple columns. #91956
- Fixed a bug to prevent schema changes on the `crdb_internal_expiration` column. #92291
- The `sql.metrics.statement_details.dump_to_logs` cluster setting no longer causes a mutex deadlock when set to true. #92279
- Fixed incorrect cancellation logic when attempting to detect stuck rangefeeds. #92704
- Fixed the `attidentity` value for the `GENERATED BY DEFAULT AS IDENTITY` column should be `d`. #92836
- Fixed a rare panic only present in v22.2.0 that occurs when using particular forms of old statistics in table statistics forecasting. This panic can also be mitigated by deleting old statistics, or by disabling forecasting with either `SET CLUSTER SETTING sql.stats.forecasts.enabled = false;` or, if the specific table with the problematic statistics is known: `ALTER TABLE t SET (sql_stats_forecasts_enabled = false);`. #92969
- CockroachDB previously could incorrectly evaluate queries that performed left-semi and left-anti "virtual lookup" joins on tables in `pg_catalog` or `information_schema`. These join types can be planned when a subquery is used inside of a filter condition. The bug was introduced in v22.1.0 and is now fixed. #92880
- Improved the speed of slow listing calls that could manifest as restore queries hanging during execution in the presence of several backup files. #93204
- Previously, empty `COPY` commands would not escape after an EOF character or error if encountering a `\.` with no input. This is now resolved. #93261
- Prepared statements that use type hints can now succeed type-checking in more cases when the placeholder type is ambiguous. #93332
- Fixed an error that could occur when dropping a user/role before the upgrade to v22.2 was finalized. #93435
- Fixed a bug in which the non-default nulls ordering, `NULLS LAST`, was ignored in window and aggregate functions. This bug could cause incorrect query results when `NULLS LAST` was used, and was introduced in v22.1.0. #93566
- Fixed a bug that could lead to errors when running multiple schema change statements in a single command using a driver that uses the extended pgwire protocol internally (Npgsql in .Net as an example). These errors would have the form `"attempted to update job for mutation 2, but job already exists with mutation 1"`. #92305
- Fixed an internal error that could occur when comparing a column of type void to `NULL` using `col IS NULL` or `col IS NOT NULL`. #93680
- Fixed an issue where `DISTINCT ON` queries would fail with the error `"SELECT DISTINCT ON expressions must match initial ORDER BY expressions"` when the query included an `ORDER BY` clause containing `ASC NULLS LAST` or `DESC NULLS FIRST`. #93609
- Previously, CockroachDB would error when receiving Geometry/Geography using binary parameters. This is now resolved. #93685
- Fixed a rare bug where CockroachDB could encounter an internal error when evaluating the `crdb_internal.range_stats` built-in (which is used in the `SHOW RANGES` command, among others). The bug was introduced in v22.2.0. #93869
- Fixed a bug where some input to `crdb_internal.trim_tenant_prefix` would cause a node crash. #90541

### Performance improvements

- The optimizer now explores plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions. #88864
- Some types of queries with comparisons between one or more constant values now execute faster. #89554
- `pg_catalog.col_description` is now much faster when resolving columns for tables in the `pg_catalog`, `crdb_internal`, or `information_schema` namespaces. #89496
- HTTP requests with `'Accept-encoding: gzip'` previously resulted in valid gzip-encoded, but uncompressed responses. This resulted in inefficient HTTP transfer times, as far more bytes were transferred than necessary. Those responses are now properly compressed, resulting in smaller network responses. #89510
- The optimizer now does less copying of histograms while planning queries, which will reduce memory pressure. #89957
- Tables in `pg_catalog` and `information_schema` (when not explicitly referenced as `"".information_schema`) may now be much faster if the current database has a small number of relations relative to the total number in the cluster. #91054
- Adjusted the garbage collection score threshold needed to trigger a MVCC garbage collection run to be more aggressive. This should result in garbage collection runs being triggered earlier on average. #92675
- CockroachDB in some cases now correctly incorporates the value of the `OFFSET` clause when determining the number of rows that need to be read when the `LIMIT` clause is also present. Note that there was no correctness issue here—only that extra unnecessary rows could be read. #92840
- In v22.2, privileges on virtual tables (system catalogs like `pg_catalog`, `information_schema`, and `crdb_internal`) were introduced. A problem with this new feature is those privileges must be fetched into a cache before using those tables or determining their visibility in other system catalogs. This process used to occur on-demand, when the privilege was needed. Now these privileges are fetched proactively during startup to mitigate the latency when accessing `pg_catalog` right after the server boots up. #93655

### Build changes

- Added support for MacOS `arm64`. #89304

### Contributors

This release includes 448 merged PRs by 73 authors. We would like to thank the following contributors from the CockroachDB community:

- quanuw (first-time contributor)

## v22.2.0

Release Date: December 6, 2022

With the release of CockroachDB v22.2, we've added new capabilities in CockroachDB to help you build, optimize, and operate more effectively and efficiently. Check out a summary of the most significant user-facing changes and then upgrade to CockroachDB v22.2.

For demos and videos on the new features, see the v22.2 launch page. Join our webinar on schema design and query tuning in a distributed SQL database.

### Downloads

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach:v22.2.0

### Changelog

View a detailed changelog on GitHub: v22.2.0-rc.3...v22.2.0

### CockroachDB Cloud

- Get a free v22.2 cluster on CockroachDB Serverless.
- Learn about recent updates to CockroachDB Cloud in the CockroachDB Cloud Release Notes.

### Feature highlights

This section summarizes the most significant user-facing changes in v22.2.0. For a complete list of features and changes, including bug fixes and performance improvements, see the release notes for previous testing releases. You can also search for what's new in v22.2 in our docs.

Note:

The features highlighted below are freely available in CockroachDB self-hosted and do not require an enterprise license, unless otherwise noted. CockroachDB Cloud clusters include all enterprise features. You can also use `cockroach demo` to test enterprise features in a local, temporary cluster.

- SQL
- Recovery and I/O
- Database operations
- Security
- Backward-incompatible changes
- Deprecations
- Known limitations
- Additional resources

#### SQL

Feature | Description
--- | ---
User-defined functions (UDFs) | Migrate existing workloads that use user-defined functions (UDFs) to CockroachDB without the need to change them. CockroachDB supports invoking UDFs in `SELECT`, `FROM`, and `WHERE` clauses of DML statements.
Schema Conversion Tool | Use the Schema Conversion Tool on the CockroachDB Cloud Console to analyze your schema for SQL incompatibilities. The tool will identify and help you resolve errors in your schema, and then create a new CockroachDB database with the converted schema.
Row-level time to live (TTL) | Row-level TTL is now generally available (GA). The GA release of row-level TTL support builds upon the existing ability to configure the lifespan of data by extending its configurability down to the row level. This release also improves TTL performance, and adds additional syntax to make calculating TTL expiration easier.
Trigram indexes | Perform text search directly within CockroachDB with trigram indexes. Trigram indexes enable "fuzzy" search (i.e., match a search term closely instead of exactly) within CockroachDB without the need to change your queries or set up complex full text search systems.
Invisible indexes | Create and test indexes without affecting your overall application.
Insights page | View problems that CockroachDB has detected in your workloads and schemas on the Insights page. The page helps identify SQL statements with high retry counts, slow execution, or suboptimal plans. It also helps identify indexes that should be created, altered, replaced, or dropped to improve performance.

#### Recovery and I/O

Feature | Description
--- | ---
Change data capture (CDC) transformations (Preview, Enterprise-only) | Simplify your event-based applications and data pipelines by extracting only the data you need from CockroachDB. Use standard SQL expressions to specify up-front transformations to the data you want to stream to the rest of your system.
CDC performance improvements (Enterprise-only) | Get up to 9x faster changefeed performance for certain workloads or deployments with large storage volumes (in the order of terabytes).
Backup validation | Use backup validation tools to check that backups you have in storage are restorable.

#### Database operations

Feature | Description
--- | ---
Secondary regions | Use secondary regions to specify which region the leaseholders move to in the event of a failure. Secondary regions let you improve latency during failures, plan better for outages, and routinely test failover scenarios without impacting performance.
PostgREST support | Perform basic CRUD operations in CockroachDB using a REST API with new support for the open source tool PostgREST. Docs coming soon.
Hasura integration | Build APIs, secure access, deploy and scale the app server, and optimize performance with the CockroachDB integration with Hasura. Hasura acts as a middleware for translating GraphQL queries into SQL and provides an easy way to offer GraphQL functionality, so you do not have to write application logic for these capabilities. In addition to GraphQL, Hasura also provides an easy way to build REST APIs on top of CockroachDB.

#### Security

Feature | Description
--- | ---
Private CockroachDB Dedicated clusters | Secure your CockroachDB Dedicated clusters with private IPs to protect your data from potential threats. Updates to give your cluster end-to-end protection include the ability to secure clusters with only node-level private IPs, to access public external resources using a NAT Gateway, and to access cloud storage over your provider’s private connectivity.
Egress perimeter controls on CockroachDB Dedicated clusters (Preview) | Use egress perimeter controls to reduce risk by configuring allowed external destinations for data. This new feature lets admins specify where users are allowed to send backups, data exports, changefeeds, etc. The virtual firewall capability significantly reduces the risk of data exfiltration and lets you restrict a cluster’s access to only organizationally allowed resources.
Cloud cluster Single-Sign On (SSO) (Preview) | Use cluster SSO to allow application-level SQL identities to use JSON web tokens (JWT) to authenticate on CockroachDB Cloud clusters. SSO also lets SQL users access their cluster using the same SSO provider that you’ve already set up for the CockroachDB Cloud Console.
Role-Based Access Control (RBAC) for backup, restore, and observability | Combine or inherit task-specific permissions into SQL roles for backup, restore, and observability. Roles can be assigned to relevant SQL users to simplify management of access control and prevent escalation of privilege.
Role-Based Access Control (RBAC) for CDC (Enterprise-only) | Combine or inherit task-specific permissions into SQL roles for CDC. Roles can be assigned to relevant SQL users to simplify management of access control and prevent escalation of privilege.
Identity and Access Management (IAM) roles for bulk operations | Create secure IAM roles in your cloud provider to access your cloud resources, so developers and operators can configure backups, restores, import, export, and CDC without requiring direct access to those resources or to relevant credentials.
Cloud organization audit logs | Export information on your team’s actions in your Cloud organization (e.g., managing users and their access, creating and deleting clusters, and configuring IP allowlisting). Access these logs as needed, or create simple pull-based clients to incrementally send those to your Security Information and Event Management (SIEM) tools.
SCRAM password authentication method | Avoid CPU bottlenecks during password authentication, and avoid sending passwords to CockroachDB in cleartext. This feature was first introduced in v22.1, and is now enabled by default.

#### Backward-incompatible changes

Before upgrading to CockroachDB v22.2, be sure to review the following backward-incompatible changes and adjust your deployment as necessary.

- CockroachDB no longer performs environment variable expansion in the parameter `--certs-dir`. Uses like `--certs-dir='$HOME/path'` (expansion by CockroachDB) can be replaced by `--certs-dir="$HOME/path"` (expansion by the Unix shell). #81298
- In the Cockroach CLI, `BOOL` values are now formatted as `t` or `f` instead of `True` or `False`. #81943
- Removed the `cockroach quit` command. It has been deprecated since v20.1. To shut down a node gracefully, send a `SIGTERM` signal to it. #82988
- Added a cluster version to allow the Pebble storage engine to recombine certain SSTables (specifically, user keys that are split across multiple files in a level of the log-structured merge-tree). Recombining the split user keys is required for supporting the range keys feature. The migration to recombine the SSTables is expected to be short (split user keys are rare in practice), but will block subsequent migrations until all tables have been recombined. The `storage.marked-for-compaction-files` time series metric can show the progress of the migration. #84887

- Using separate ports for TCP and RPC listeners is now encouraged for new clusters. Using a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the future. To split traffic, you can use one of the following approaches:

  - Preferred: keep port `26257` for SQL, and allocate a new port, e.g., `26357`, for node-node RPC connections. For example, you might configure a node with the flags `--listen-addr=:26357 --sql-addr=:26257`, where subsequent nodes seeking to join would then use the flag `--join=othernode:26357,othernode:26257`. This will become the default configuration in the next version of CockroachDB. When using this mode of operation, care should be taken to use a `--join` flag that includes both the previous and new port numbers for other nodes, so that no network partition occurs during the upgrade.
  - Optional: keep port `26257` for RPC, and allocate a new port, e.g., `26357`, for SQL connections. For example, you might configure a node with the flags `--listen-addr=:26257 --sql-addr=:26357`. When using this mode of operation, the `--join` flags do not need to be modified. However, SQL client apps or the SQL load balancer configuration (when in use) must be updated to use the new SQL port number.

  #85671

- If no `nullif` option is specified while using `IMPORT CSV`, then a zero-length string in the input is now treated as `NULL`. The quoted empty string in the input is treated as an empty string. Similarly, if `nullif` is specified, then an unquoted value is treated as `NULL`, and a quoted value is treated as that string. These changes were made to make `IMPORT CSV` behave more similarly to `COPY CSV`. If the previous behavior (i.e., treating either quoted or unquoted values that match the `nullif` setting as `NULL`) is desired, you can use the new `allow_quoted_null` option in the `IMPORT` statement. #84487

- `COPY FROM` operations are now atomic by default instead of being segmented into 100 row transactions. Set the `copy_from_atomic_enabled` session setting to `false` for the previous behavior. #85986

- The `GRANT` privilege has been removed and replaced by the more granular `WITH GRANT OPTION`, which provides control over which privileges are allowed to be granted. #81310

- Removed the ability to cast `int`, `int2`, and `int8` to a `0` length `BIT` or `VARBIT`. #81266

- Removed the deprecated `GRANT` privilege. #81310

- Removed the `ttl_automatic_column` storage parameter. The `crdb_internal_expiration` column is created when `ttl_expire_after` is set and removed when `ttl_expire_after` is reset. #83134

- Removed the byte string parameter in the `crdb_internal.schedule_sql_stats_compaction` function. #82560

- Changed the default value of the `enable_implicit_transaction_for_batch_statements` to `true`. This means that a batch of statements sent in one string separated by semicolons is treated as an implicit transaction. #76834

#### Deprecations

- The `--redact-logs` flag to `cockroach debug zip` has been deprecated in favor of the `--redact` flag, which applies to a broader scope than just logs, and also includes logs. The new `--redact` flag triggers the redaction of all sensitive data in debug zip bundles, with the exception of range keys. Range keys must remain unredacted because they are essential to support CockroachDB. The `--redact-logs` flag is still available but displays a redaction warning and is interpreted as `--redact` instead. #88266
- Previously, `BACKUP` allowed the user to specify a custom subdirectory name for their backups via `BACKUP .. INTO {subdir} IN {collectionURI}`. This is no longer supported. Users can only create a full backup via `BACKUP ... INTO {collectionURI}` or an incremental backup on the latest full backup in their collection via `BACKUP ... INTO LATEST IN {collectionURI}`. This deprecation also removes the need to address a bug in `SHOW BACKUPS IN`, which cannot display user-defined subdirectories. #79447
- The `debug unsafe-remove-dead-replicas` CLI command has been deprecated, and will be removed in v23.1. Users should use the new `debug recover` set of commands instead. #88765

#### Known limitations

For information about new and unresolved limitations in CockroachDB v22.2, with suggested workarounds where applicable, see Known Limitations.

#### Additional resources

Resource | Topic | Description
--- | --- | ---
Cockroach University | Getting Started with SQL for Application Developers | In this course, you will learn some basic, single-table, SQL operations. Starting from a business use case, you will learn how to translate a simple entity/object into a corresponding database table. From there, you will see how you can populate that table with data and retrieve it afterward. By the end of the course, you should feel comfortable with taking your own simple entities, mapping them to your relational database, and performing basic queries.
Cockroach University | Modeling Object Relationships in SQL (Preview) | In this course, you will learn to map your business critical data from your application code to a SQL database efficiently and elegantly, and learn key SQL features to help minimize application complexity.
Cockroach University | Getting Started with Node.js and node-postgres | In this course, you will learn how to properly use CockroachDB inside of a simple microservice. You will start with a pre-built microservice and add the necessary components to communicate with the database using the node-postgres driver.
Cockroach University | Intro to Multi-Region Databases in Geo-distributed Applications (Preview) | This course will introduce simple, elegant, and practical solutions for designing a database that optimizes for resilience, responsiveness while also being sensitive to data locality.
Docs | Migration Overview | This page summarizes the steps of migrating a database to CockroachDB, which include testing and updating your schema to work with CockroachDB, moving your data into CockroachDB, and testing and updating your application.
Docs | Unsupported Features in CockroachDB Serverless | This page describes the features that are either unsupported or partially supported in CockroachDB serverless clusters
Docs | Sample apps with ccloud | Sample application docs now includes steps to create a CockroachDB Serverless cluster using the `ccloud` CLI tool.
Docs | API Support Policy | This page includes the following information: our API support policies, our definitions of backward-incompatible and backward-compatible changes, and a summary of APIs that CockroachDB makes available.
Docs | CockroachDB Kubernetes Operator release notes | The CockroachDB Kubernetes Operator-specific release notes are now surfaced on this page.
Docs | HashiCorp Vault tutorial | This pages reviews the supported integrations between CockroachDB and HashiCorp's Vault, which offers tooling to extend CockroachDB's data security capabilities.
Docs | Backup architecture | This page describes the backup job workflow with a high-level overview, diagrams, and more details on each phase of the job.

## v22.2.0-rc.3

Release Date: November 21, 2022

### Downloads

Warning:

CockroachDB v22.2.0-rc.3 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach-unstable:v22.2.0-rc.3

### Changelog

View a detailed changelog on GitHub: v22.2.0-rc.2...v22.2.0-rc.3

### Bug fixes

- Fixed a bug where panics triggered by certain DDL statements were not properly recovered, leading to the cluster node crashing. #91553
- Fixed a bug that would cause `SHOW BACKUP` and `RESTORE` of encrypted incremental backups to fail. #91926

### Contributors

This release includes 3 merged PRs by 3 authors.

## v22.2.0-rc.2

Release Date: November 14, 2022

### Downloads

Warning:

CockroachDB v22.2.0-rc.2 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach-unstable:v22.2.0-rc.2

### Changelog

View a detailed changelog on GitHub: v22.2.0-rc.1...v22.2.0-rc.2

### Bug fixes

- Fixed a bug which caused a migration in v22.1 to fail to drop an index on `system.statement_diagnostics_requests`. This caused upgrades from v22.1 to v22.2 to fail to create a new index with the same name as the index that was supposed to have been dropped. #91309
- A nil pointer crash that could be experienced when interleaving `SELECT FOR UPDATE SKIP LOCKED` statements has been resolved. #91257
- Fixed a bug present only in earlier v22.2 release candidates, in which an `ALTER PRIMARY KEY USING COLUMNS (x, x)` statement would result in an internal error instead of the expected user-facing error with a pgcode. #91482
- Previously, certain aggregate histograms would appear in `_status/vars` but not be available for graphing in the DB Console. These are now available. They include changefeed-related histograms and row-level-TTL histograms. #91410

### Contributors

This release includes 10 merged PRs by 8 authors.

## v22.2.0-rc.1

Release Date: November 7, 2022

### Downloads

Warning:

CockroachDB v22.2.0-rc.1 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach-unstable:v22.2.0-rc.1

### Changelog

View a detailed changelog on GitHub: v22.2.0-beta.5...v22.2.0-rc.1

### SQL language changes

- Added the boolean column `implicit_txn` to `crdb_internal.cluster_execution_insights` and `crdb_internal.node_execution_insights`. #90872

### Bug fixes

- Fixed a bug that could cause crashes when parsing malformed changefeed expressions. #90844
- TTL decoding error messages now correctly contain hex-encoded key bytes instead of hex-encoded key pretty-print output. #90726
- Remove redundant assertion that `ExportRequests` should have the parameter `ReturnSST`. #90833
- Fixed a bug that resulted in some retriable errors not being retried during `IMPORT`. #90429
- Fixed a bug that caused incorrect results and internal errors when a `LEFT JOIN` operated on a table with virtual computed columns. The bug only presented when the optimizer planned a "paired joiner" and could cause values of the virtual columns to be incorrectly `NULL`. An internal error would occur in the same situation if the virtual column had a `NOT NULL` constraint. #90999

### Contributors

This release includes 27 merged PRs by 20 authors.

## v22.2.0-beta.5

Release Date: November 1, 2022

### Downloads

Warning:

CockroachDB v22.2.0-beta.5 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

Multi-platform images include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.

Within the multi-platform image:

- The ARM image is Experimental and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is Generally Available for production use.

To download the Docker image:

    docker pull cockroachdb/cockroach-unstable:v22.2.0-beta.5

### Changelog

View a detailed changelog on GitHub: v22.2.0-beta.4...v22.2.0-beta.5

### Enterprise edition changes

- Changefeed Kafka sinks no longer automatically retries when emitting a message batch that gets rejected by the server. This is a temporary rollback of the functionality. #90037

### SQL language changes

- Star expressions, e.g., `SELECT * FROM ...` are no longer allowed in statements in user-defined functions (UDFs). These were allowed in `v22.2.0-beta.1` to `v22.2.0-beta.4`, but have been disallowed because they do not behave correctly. Issue #90080 tracks re-enabling star expressions in UDFs. #90171
- The `sql.defaults.experimental_auto_rehoming.enabled` cluster setting is now marked as hidden. Also, the `experimental_enable_auto_rehoming` session variable has been renamed to `enable_auto_rehoming`; the alias `experimental_enable_auto_rehoming` has been created for the renamed session variable. #90185
- Added a new `cloudstorage.azure.concurrent_upload_buffers` cluster setting to configure the number of concurrent buffers used when uploading files to Azure. #90539

### Operational changes

- The following TTL metrics have been renamed:
  - `jobs.row_level_ttl.range_total_duration` -> `jobs.row_level_ttl.span_total_duration`
  - `jobs.row_level_ttl.num_active_ranges` -> `jobs.row_level_ttl.num_active_spans` #90381

### Bug fixes

- Fixed a bug that caused internal errors in rare cases when running CTEs (statements with WITH clauses). This bug is only present in `v22.2.0-beta.2`, `v22.2.0-beta.3`, `v21.2.16`, and `v22.1.9`. #89856
- CockroachDB will no longer silently truncate trailing characters when attempting to convert corrupt JSON string input into JSONB. #89927
- Fixed a bug where zone configs generated for a database with a secondary region were invalid, as the `voter_constraints` and the lease preferences called for voters and leaseholders to exist in both the primary and secondary region, which is impossible. This bug has been present since `v22.2.0-alpha.1`. #90186
- Changefeeds configured with `initial_scan_only` now ensure that all messages have successfully flushed to the sink prior to completion instead of potentially missing messages. #90278
- Fixed a bug where restoring a backup with a table containing `UniqueWithoutIndexConstraints` would fail because of incorrect `tableID` values being referenced in the constraints stored on the restored table. #90052
- Fixed a bug that caused incorrect results for queries with string similar filters (e.g., `col % 'abc'`) on tables with trigram indexes. This bug is only present in 22.2 pre-release versions up to and including `v22.2.0-beta.3`. #90164
- Fixed a bug that caused incorrect evaluation of comparison expressions involving time and interval types, e.g., `col::TIME + '10 hrs'::INTERVAL' > '01:00'::TIME`. #90367
- Fixed a bug where a `REASSIGN OWNED BY`statement could experience errors if any descriptor owned by the user is currently being dropped. #90389
- Fixed a bug introduced in CockroachDB v22.1.9 that caused nodes to refuse to run jobs under rare circumstances. #90464
- Fixed the bug described in #90475 and #90474 where a restoring cluster's codec was accidentally used to reason about spans in the backup manifest during restore planning. #90528
- Fixed a bug where specific input to `crdb_internal.trim_tenant_prefix` would cause a node crash. #90542

### Build changes

- Added ARM64 binaries for macOS systems with Apple Silicon. #89944
- Docker images for CockroachDB are now multi-architecture manifests, supporting both the `x86_64` (`amd64`) and `arm64` architectures. #90304

### Contributors

This release includes 45 merged PRs by 29 authors.

## v22.2.0-beta.4

Release Date: October 17, 2022

### Downloads

Warning:

CockroachDB v22.2.0-beta.4 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-beta.4

### Changelog

View a detailed changelog on GitHub: v22.2.0-beta.3...v22.2.0-beta.4

### DB Console changes

- Fixed a bug that prevented usage of profiling links on the Advanced Debug page. #89310

### Bug fixes

- Fixed a bug in `Concat` projection operators for arrays that could cause non-`NULL` values to be added to the array when one of the arguments was `NULL`. #89347
- `VIEWCLUSTERMETADATA` permissions are now required to view hot ranges, problem ranges, data distribution, stores report, range status, and Raft for all ranges. #89509
- Updated `SHOW CLUSTER SETTING` sql command to require `VIEWCLUSTERSETTING` or `MODIFYCLUSTERSETTING` permissions. #89509
- Fixed a visual bug on Store Report sub-pages where an error shows an infinite spinner only. #89509
- Fixed a bug that could cause a failed upgrade if tables created by userfile storage have invalid foreign key constraints. #89370

- Fixed a bug that could prevent the system from exiting a joint configuration. A `VOTER_DEMOTING_LEARNER` can now acquire the lease in a joint configuration only when there is a `VOTER_INCOMING` in the configuration and the `VOTER_DEMOTING_LEARNER` was the last leaseholder. #89595

- Fixed a bug, introduced in a 22.2.x alpha version, in which uncommitted privileges could be cached if a transaction is rolled back. #89718

- Fixed a bug introduced in a v22.2.0 beta version, that could cause queries with expressions like `'foo' LIKE col` to return incorrect values when an inverted trigram index existed on `col`. #89701

### Contributors

This release includes 32 merged PRs by 22 authors.

## v22.2.0-beta.3

Release Date: October 10, 2022

### Downloads

Warning:

CockroachDB v22.2.0-beta.3 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-beta.3

### Changelog

View a detailed changelog on GitHub: v22.2.0-beta.2...v22.2.0-beta.3

### Enterprise edition changes

- Added user mapping and support for multiple audiences to JWT-based authentication to better integrate with third-party JWT issuers. #89022
- Changefeeds with the `format=csv` option now use the same format as other CSV exports. #88669

### SQL language changes

- Changed the default value of the `sql.metrics.statement_details.plan_collection.enabled` cluster setting to `true`. #89038
- Previously, the `has_function_privilege` built-in function and `::regprocedure` casting only considered function names and ignored argument types. An ambiguous error was returned if a function name matched more than one overload. Now, argument types are used to narrow down an overload to avoid ambiguity. Both `has_function_privilege` and `::regprocedure` require a valid full function signature as input. That is, a parenthesis is required even if the function takes zero arguments. `::regproc` has also been modified to consider a whole string as a function name. As a result, every non-leading or non-trailing white space(s) are considered as part of the function name. This is to match PostgreSQL behavior. #89037

### Operational changes

- Added the metric `admission.io.overload` that tracks the store's `IOThreshold`. #88170

### Bug fixes

- Fixed a bug that prevents changefeeds from retrying when emitting to a Google Pub/Sub sink. #88865
- Audit logs and other structured logs will now use the session user for the `user` field of the log entry, rather than the current user. The session user is the user who originally logged in, and it is immutable. The current user can be modified by `SET ROLE` commands, which means it is not appropriate for this kind of logging. #88899
- Index usage stats are now properly captured for database names with hyphens. #88998
- Changefeeds no longer deadlock when canceling during an internal Kafka sink retry. #89196
- Fixed a bug that caused `ALTER CHANGEFEED` to fail if the changefeed was created with the `cursor` option and had been running for more than the `gc.ttlseconds`. #89206
- Fixed a bug where the floor division operator, `//`, would report incorrect results when the numerator is non-constant and the denominator is the constant `1`. #89314
- Fixed a bug introduced in v20.2 that could, in rare cases, cause filters to be dropped from a query plan with many joins. #89350

- Fixed a bug that could cause incorrect results. The bug would only present if the following conditions were true:

  1. A query with `ORDER BY` and `LIMIT` was executed.
  2. The table containing the `ORDER BY` columns had an index containing those columns.
  3. The index in (2) contained a prefix of columns held to a fixed number of values. The columns can be held to these values by any of the following:
     - A query filter (e.g., `WHERE a IN (1, 3)`).
     - A `CHECK` constraint (e.g., `CHECK (a IN (1, 3))`).
     - A filter inferred from a computed column expression (e.g., `WHERE a IN (1, 3)` and a column `b INT AS (a + 10) STORED`).
     - A filter inferred from a `PARTITION BY` clause (e.g., `INDEX (a, ...) PARTITION BY LIST (a) (PARTITION p VALUES ((1), (3)))`).

  This bug has been present since version v22.1.0. #89344

- Fixed a bug that has existed since v2.1.0 where queries containing a subquery with `EXCEPT` could produce incorrect results. This could happen if the optimizer could guarantee that the left side of the `EXCEPT` always returned more rows than the right side. In this case, the optimizer made a faulty assumption that the `EXCEPT` subquery always returned at least one row, which could cause the optimizer to perform an invalid transformation, possibly causing the full query to return incorrect results. #89305

### Contributors

This release includes 49 merged PRs by 32 authors.

## v22.2.0-beta.2

Release Date: October 3, 2022

### Downloads

Warning:

CockroachDB v22.2.0-beta.2 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-beta.2

### Changelog

View a detailed changelog on GitHub: v22.2.0-beta.1...v22.2.0-beta.2

### Enterprise edition changes

- Introduced a new JWT-based authentication method as an option. #88588

### SQL language changes

- Changed the default value of `sql.metrics.statement_details.plan_collection.enabled` to `false`, since we no longer use this information anywhere. #88416
- The pgwire protocol implementation can now accept arguments of the JSON type (`oid=114`). Previously, it could only accept JSONB (`oid=3802`). Internally, JSON and JSONB values are still identical, so this change only affects how the values are received over the wire protocol. #88576
- Added the `sql.metrics.statement_details.gateway_node.enabled` cluster setting, which controls whether the gateway node ID should be persisted to the `system.statement_statistics` table as is or as a `0`, to decrease cardinality on the table. The node ID is still available on the statistics column. #88616
- Added the `cloudstorage.gs.chunking.retry_timeout` cluster setting, which can be used to configure the per-chunk retry timeout of files to Google Cloud Storage. The default value is 60 seconds. #87720
- Previously `SHOW GRANTS` only supported `db`, `schema`, `table`, and `types`. This release adds supports for user-defined functions (UDFs), so that `SHOW GRANTS` returns a UDF's privilege info, and statements like `SHOW GRANTS ON FUNCTION <udf name/signatures>` are now supported. The full function signature must be provided if the function name is not unique. #88866

### Operational changes

- Added two new sets of per-LSM-level time-series metrics, one for level size and another for level score. These metrics are of the form `storage.{level}-level-{size,score}`. #88592

### Command-line changes

- The `debug unsafe-remove-dead-replicas` CLI command has been deprecated, and will be removed in v23.1. Users should use the new `debug recover` set of commands instead. #88765

### DB Console changes

- Removed the Full Scan field from the Active Transaction Details page. #88404
- Added support for multiple blocking transactions on the Insights > Transaction Executions page, merged displayed cards into the table, and fixed the reported total contention time. #88522
- Renamed the Insights Overview table column Execution ID to Latest Execution ID. This will help avoid confusion since the UI only shows the latest ID per fingerprint. #88591

### Bug fixes

- CockroachDB no longer fetches unnecessary rows for queries that use the `LIMIT` statement. The bug was introduced in v22.1.7. #88417
- Active transactions with a non-zero executed statement count now always have populated statement text, even when no statement is being executed. #88404
- Fixed a bug where offline tables were included in full-cluster backups, as reported in #88043. #88474
- Implemented a change to ensure that time elapsed for active transactions and statements is never negative. #88467
- Fixed a rare internal error with message `estimated row count must be non-zero`, which could occur during planning when a predicate included values close to the maximum or minimum `int64` value. #88529
- Upgraded `grpc` to v1.49.0 which fixed a few panics on nil pointers #88630
- Fixed missing automatic statistics collection on system tables during cluster startup. This bug only affects the v22.2.0-alpha releases. #88689
- Fixed a bug that could cause nodes to crash in rare cases when executing apply joins in query plans. #88483
- Fixed a bug that caused errors in rare cases when executing queries with correlated `WITH` expressions. This bug has been present since the introduction of correlated `WITH` expressions in v21.2.0. #88483
- Fixed unintended recordings of index reads caused by internal executor/queries. #88867
- Fixed a bug that caused incorrect evaluation of expressions in the form `col +/- const1 ? const2`, where `const1` and `const2` are constant values and `?` is any comparison operator. The bug was caused by operator overflow when the optimizer attempted to simplify these expressions to have a single constant value. #88895
- Adjusted sending and receiving Raft queue sizes to match. Previously the receiver could unnecessarily drop messages in situations when the sending queue is bigger than the receiving one. #88406

### Performance improvements

- The `SHOW BACKUP` statement is now more performant when working with a backup containing several table descriptors. #88711

### Contributors

This release includes 82 merged PRs by 43 authors.

## v22.2.0-beta.1

Release Date: September 26, 2022

### Downloads

Warning:

CockroachDB v22.2.0-beta.1 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-beta.1

### Changelog

View a detailed changelog on GitHub: v22.2.0-alpha.4...v22.2.0-beta.1

### Security updates

- Redacted logs now reveal pretty-printed keys, except for the index key values themselves. For example `/Table/42/1222/‹x›` is shown instead of `‹x›` (which was shown previously). This improved redaction is available for the `/Table` keyspace for both system and application tenants. Other keyspaces such as `/Meta1`, `/Meta2`, `/Local`, etc. are not yet supported. #87647
- The following types of data are now considered "safe" for reporting from within debug.zip:
  - Range start/end keys, which can include data from any indexed SQL column.
  - Key spans, which can include data from any indexed SQL column
  - Usernames and role names
  - SQL object names (including database, schema, table, sequence, view, type, and UDF names) #88266

### SQL language changes

- PostgresSQL wire-level `PREPARE` statements now support the case where the number of the type hints is greater than the number of placeholders in the given query. #88146
- `Information_schema.role_routine_grants` is now populated properly with both built-in functions and user-defined functions. #88233
- The PostgreSQL compatibility function `obj_description` now supports retrieving comments on schemas. #88263
- The index of a placeholder is now replaced with `$1` to limit fingerprint creations. #88365

### Command-line changes

- `debug zip`'s `--redact-logs` flag has been deprecated in favor of the `--redact` flag, which applies to a broader scope than just logs, and also includes logs. The new `--redact` flag triggers the redaction of all sensitive data in debug zip bundles, with the exception of range keys. Range keys must remain unredacted because they are essential to support CockroachDB. The `--redact-logs` flag is still available but displays a redaction warning and is interpreted as `--redact` instead. #88266

### DB Console changes

- On the SQL Activity Session Details page, the Most Recent Statement section shows the last active query instead of "No Active Statement". #88057
- On the SQL Activity page, the App filter label has been renamed to Application Name, and the Username label has been renamed to User Name. #88144
- The Transaction Execution ID column is now shown in the Blocked Statements table on the Active Statement Details page. #88135

### Bug fixes

- Fixed a problem that could cause incorrect results from queries that use locality-optimized search on the inverted index of a table with `REGIONAL BY ROW` partitioning. #88114
- The `pg_catalog.pg_get_viewdef` function now works properly for materialized views. #88143
- When restoring a cluster or tenant from a backup, only tenants that were activated during the backup are activated during the restore. #88157
- Fixed a problem where checking a custom session setting with the `current_setting` built-in function resulted in an error if the setting was not set and the `missing_ok` argument was true. #88158
- Fixed a problem where `CREATE TABLE {TABLE_NAME} as (SELECT * FROM crdb_internal.check_consistency(...))` could cause a panic on the gateway node. #88227
- Fixed a problem where CockroachDB could not fetch rows with `NULL` values when reading from the unique secondary index when multiple column families were defined for the table and the index did not store some of the `NOT NULL` columns. #88210
- Fixed a problem that could cause `CHANGEFEED` to crash when running on recent Go versions. #88231
- On the Statement Execution Insights page, the end time of a query is now calculated based on statement execution latency, rather than the statement end time. #88276
- Completed statement diagnostics bundles now persist in the UI in Statement Diagnostics Bundle pages. #88286
- Privileges are now checked before dropping temporary tables and sequences. #88305
- Fixed a problem where the pgwire `DESCRIBE` step could fail with an error when attempting to look up cursors declared with names containing special characters. #88316
- Jobs pages now refresh data at an interval of 10 seconds. #88285
- Fixed a crash in `CHANGEFEED` expressions when an empty projection was encoded in JSON. #88311
- CockroachDB now reacts more quickly to query cancellations (such as when a statement timeout is exceeded) after a query is spilled to disk. #88378

### Contributors

This release includes 54 merged PRs by 30 authors.

## v22.2.0-alpha.4

Release Date: September 22, 2022

### Downloads

Warning:

CockroachDB v22.2.0-alpha.4 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-alpha.4

### Changelog

View a detailed changelog on GitHub: v22.2.0-alpha.3...v22.2.0-alpha.4

### Enterprise edition changes

- Changefeeds are more efficient during initial scan and backfill. The impact on runtime garbage-collection is significantly reduced, resulting in significantly reduced impact of the changefeed on foreground SQL latency. #87796
- Backup and restore now can back up and restore user-defined function descriptors at database and cluster level. #88023

### SQL language changes

- A hint is now provided when importing from a CSV file fails because a null value has leading or trailing whitespace or because a value is quoted when the `allow_quoted_null` option is not set. #87443
- Instead of always recommending to replace the first existing index with the same explicit columns, the index recommendation now considers all existing indexes in the table to decide the best one to replace. #87174
- Owners of a backup schedule can now control their schedule using the supported pause, resume, drop, and alter queries. #87600
- Previously, a schema could be renamed even when a child table was referenced by a view or UDF. Renaming the schema breaks the view or UDF since tables are referenced by name in views and UDFs. A check has been added that disallows renaming a schema that is referenced in views or UDFs. #87540
- Users may now be granted the `CHANGEFEED` privilege on a table, allowing them to create changefeeds for that table even if they don't have the `CONTROLCHANGEFEED` role option or the `SELECT` privilege. Note that this still in effect gives them the ability to read the data in the table. Users with the `CONTROLCHANGEFEED` role option still need `SELECT` on each table, even if they also have `CHANGEFEED`. #87887

### Command-line changes

- The `\c` metacommand no longer shows the password in cleartext. #87538

### DB Console changes

- Fixed the active transaction description. Removed transaction insights details elapsed time because it is not available and was the contention time. #87604
- Internal sessions and active executions are now surfaced in the UI only when the cluster setting `sql.stats.response.show_internal.enabled` is set to true. #87608
- When there is no insight problem detected, a message indicates that the statement was slow and how long it took to execute. #87799
- The Insight Statement Detail page is linked from the Insight Statement page but no longer includes a link to itself. #87800
- Added the ability to copy an index recommendation to the clipboard. #87794
- Fixed the index and grant sorting on the Databases page to have default column and to have URL match the user selection. #87832
- Added Application Name to Statement Overview, Transaction Overview (and their respective column selectors), Transaction Details. Updated label from "App" to "Application Name" on Statement Details page. #87868
- The value for percentage of all runtime is now calculated based on all data from the time period. Previously, it was calculated based on only the filtered data. #88027
- The Contention column has been renamed to Contention Time. This matches other columns such as Elapsed Time. #88040
- An Insights link has been added to all insights pages, and the message on the Schema Insights page has been updated to match the message on the Workload Insights page. #88012

### Bug fixes

- The statement bundle produced for statements that use no tables (e.g., `select 123`) now properly includes an empty `schema.sql`. #86484
- Some upgrade migrations perform schema changes on system tables. Previously, those upgrades which added indexes could, in some cases, get caught retrying because they failed to detect that the migration had already occurred due to the existence of a populated field. When that happened, the finalization of the new version could hang indefinitely and require manual intervention. This issue no longer occurs. #87623
- Previously, the `is_generated` column in the `information_schema.column` table returned either `YES` or `NO`, depending on whether the column is computed. The column now returns either `ALWAYS` or `NEVER`. This matches the behavior of PostgreSQL. #87670
- In rare cases, the value of a cluster setting could revert soon after it was updated. This no longer happens for a given gateway node. #87732
- The UI no longer crashes when no text is being passed to the limit text function. #87797
- In some scenarios, when a `DROP INDEX` statement was run around the same time as a `DROP TABLE` or `DROP DATABAS`E statement covering the same data, the `DROP INDEX` garbage-collection job could get caught retrying indefinitely. This has been fixed. #87721
- A consistency check is now skipped/stopped when the collection request is canceled before/while running the check computation. Previously such checks would start and run until completion, and, due to the limited size of the worker pool, prevent the useful checks from running. #87841
- Consistency checks are now sent to all replicas in parallel. Previously, they were blocked on processing the local replica first. This reduces the latency of one check by 2x and allows better propagation of the cancellation signal, resulting in fewer abandoned tasks on remote replicas and more resources spent on useful checks. #87841
- Because of a misused query optimization involving tables with one or more `PARTITION BY` clauses and partition zone constraints which assign region locality to those partitions, in some cases the optimizer would pick a `locality-optimized search` query plan which is not truly locality-optimized and has higher latency than competing query plans which use distributed scan. Locality-optimized search is now avoided in cases which are known not to benefit from this optimization. #87866
- Improved the default output when using a `SELECT` clause with a `CHANGEFEED`. #87961
- A bug has been fixed that caused scheduled backups to fail after a pre-22.2 cluster was upgraded to v22.2 because they could not find the proto messages when unmarshalling the scheduled job records. #87999
- An active replication report update could prevent a node from shutting down until it completed. The report update is now cancelled on node shutdown instead. #87925

### Build changes

- Upgrade to go 1.19.1 #86049

### Miscellaneous

#### Missing category

- Index recommendations now consider not visible indexes and can also make index recommendations for `ALTER INDEX ... VISIBLE`. #87174

### Contributors

This release includes 94 merged PRs by 39 authors.

## v22.2.0-alpha.3

Release Date: September 12, 2022

### Downloads

Warning:

CockroachDB v22.2.0-alpha.3 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-alpha.3

### Changelog

View a detailed changelog on GitHub: v22.2.0-alpha.2...v22.2.0-alpha.3

### Enterprise edition changes

- The new `kv.rangefeed.range_stuck_threshold` (default 60s) cluster setting instructs RangeFeed clients (used internally by changefeeds) to restart automatically if no checkpoint or other event has been received from the server for some time. This is a defense-in-depth mechanism which will log output as follows if triggered: `restarting stuck rangefeed: waiting for r100 (n1,s1):1 [threshold 1m]: rangefeed restarting due to inactivity`. #86820
- Fixed a null pointer exception when `ALTER BACKUP SCHEDULE` was called after a dependent schedule was dropped. #87293

### SQL language changes

- Allowed mismatched type numbers in `PREPARE` statements. #86904
- Users can grant a new `EXTERNALIOIMPLICITACCESS` system-level privilege that allows a user to interact with an external storage resource that has implicit authentication. E.g., `gs`, `s3`, `nodelocal`, etc. Previously, this was an `admin`-only operation. #87066
- We now support `DISCARD {TEMP,TEMPORARY}`, which drops all temporary tables created in the current session. The command does not drop temporary schemas. #86246
- `CREATE SCHEDULE` is no longer an admin only operation. Users should grant the appropriate `BACKUP` privileges on the targets they wish to back up as part of the schedule. Cluster backups require admin or system privilege `BACKUP`; DB backups require database privilege `BACKUP`; table backups require table privilege `BACKUP`. #87188
- Decreased the cardinality of the number on `__moreN__` when replacing literals. #87202
- When adding a `SECONDARY REGION` to a multi-region database, the region is implicitly added to the regions list of the database if it was not present already. #87108
- Added the `pg_get_function_def` function, which returns the `CREATE` statement that can be used to create the given user-defined function. For built-in functions, it only returns the name of the function. #87439

### Operational changes

- This change introduces a new histogram implementation that will reduce the total number of buckets and standardize them across all usage. This should help increase the usability of histograms when exported to a UI (i.e., Grafana) and reduce the storage overhead. After applying this patch it is expected to see fewer buckets in prometheus/grafana, but still have similar values for histogram percentiles due to the use of interpolated values by Prometheus. #86671

### Command-line changes

- Improved the output of sinkless changefeeds in the `cockroach sql` terminal. #85181

### DB Console changes

- In the Session Details page, users can click on a transaction fingerprint id from the list of cached transaction fingerprints to go to that transaction's details page. The app will also change the selected date range to that of the session's start (rounded down to the hour) and end time (rounded up to the hour) on click. #86919
- Properly formatted the execution count under Statement Details page. Increased the timeout for Statement Details page, which now shows a proper timeout error when this happens, no longer crashing the page. #87153
- Added a column selector to the Statement Insights page and add new contention, full scan, transaction ID, transaction fingerprint ID, and rows read/written info. #87171
- Added warning about performance being affected when executing an index recommendation. #87185
- Fixed the time spent waiting on insights on the Transaction Details page. Changed transaction insights overview column from elapsed time to contention time. Added 3 dots to the query text to show there is more to the query. #87239
- Introduced new graphs on metrics to the Replication Dashboard to improve decommissioning observability. #86702
- The Statement Details page and the Insights page now show index recommendations of `ALTER INDEX` type. #87458

### Bug fixes

- The statement tag for the `SHOW` command results in the pgwire protocol no longer containing the number of returned rows. #87047
- Fixed a bug where the options given to the `BEGIN TRANSACTION` command would be ignored if the `BEGIN` was a prepared statement. #87047
- When printing keys and range start/end boundaries for time series, the displayed structure of keys was incorrect. This is now fixed. #86563
- A bug has been fixed that caused internal errors like `"unable to vectorize execution plan: unhandled expression type"` in rare cases. #86816
- Fixed a race condition where some operations waiting on locks can cause the lockholder transaction to be aborted if they occur before the transaction can write its record. #83688
- The Explain tab inside the Statement Details page now groups plans that have the same shape but a different number of spans in corresponding scans. #87152
- `DISCARD ALL` now deletes temporary tables. #86246
- A bug in the column backfiller, which is used to add or remove columns from tables, failed to account for the need to read virtual columns which were part of a primary key. Hash-sharded indexes, starting in v22.1, use virtual columns. Any hash-sharded table created in v22.1 or any table created with a virtual column as part of its primary key would indefinitely fail to complete a schema change which adds or removes columns. This bug has been fixed. #87207
- Reduce the amount that `RESTORE` over-splits. #86496
- Table `system.replication_constraint_stats` is no longer showing erroneous voter constraint violations when `num_voters` is configured. #84727
- This patch fixes a bug in lookup join selectivity estimation involving hash-sharded indexes which may cause lookup joins to be selected by the optimizer in cases where other join methods are less expensive. #86622

### Performance improvements

- Raft snapshots use a fair round-robin approach for choosing which one to send next. This allows decommissioning to complete much faster. #86701
- The optimizer is now less likely to choose an expensive lookup join with a complex `ON` condition over a less selective join that is cheaper to perform. #87393

### Miscellaneous

- Added basic validation for zone config extension setting. #86538

### Contributors

This release includes 146 merged PRs by 51 authors.

## v22.2.0-alpha.2

Release Date: September 6, 2022

### Downloads

Warning:

CockroachDB v22.2.0-alpha.2 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-alpha.2

### Changelog

View a detailed changelog on GitHub: v22.2.0-alpha.1...v22.2.0-alpha.2

### Enterprise edition changes

- The `ALTER BACKUP SCHEDULE` statement will now fail if the new backup statement does not pass planning. #86819

### SQL language changes

- Added the `enforce_home_region` session setting, which when `true` causes queries which have no home region or which may scan rows via a database connection outside of the query's home region to error out. Also, only tables in multi-region databases with `ZONE` survivability may be scanned without error when this setting is `true`, because ranges in an offline region may be served non-locally to the gateway region when using `REGION` survivability, and therefore cannot be guaranteed to have low latency. #85704
- Introduced a new `BACKUP` privilege that is grantable as a system, database or table/type/schema level privilege. You can opt-in to the new privilege model by granting the appropriate privileges as per the following model:
  1. Cluster backups - user requires the `BACKUP` system-level privilege.
  2. Database backups - user requires the database `BACKUP` privilege.
  3. Table backups - user requires the table `BACKUP` privilege. In CockroachDB v22.2, the previous privilege model will continue to be respected, but will be completely replaced with the `BACKUP` system-level privilege in a future version of CockroachDB. #86495
- Added the `optimizer_use_forecasts` session setting, which can be set to `false` to disable usage of statistics forecasts when optimizing a query. #86834
- Added the `json{b}_to_record{set}` built-in function, which transforms JSON into structured SQL records. #82435
- Added the `sql.stats.forecasts.enabled` cluster setting, which controls whether statistics forecasts are generated by default for all tables. This behaves differently than the `optimizer_use_forecasts` session setting, which controls whether statistics forecasts are used when optimizing the current query. If `sql.stats.forecasts.enabled` is disabled, then even if `optimizer_use_forecasts` is `true` for a given query it won't have any forecasts to use to generate its output. #86932
- Added the `sql_stats_forecasts_enabled` table setting, which controls whether statistics forecasts are generated for a specific table. When set, this overrides the `sql.stats.forecasts.enabled` cluster setting. #86986
- Introduced a new `RESTORE` privilege that is grantable as a system or database level privilege. You can opt-in to the new privilege model by granting the appropriate privileges as per the following model:
  1. Cluster backups - user requires the `RESTORE` system-level privilege.
  2. Database backups - user requires the `RESTORE` system-level privilege.
  3. Table backups - user requires the database `RESTORE` privilege. In CockroachDB v22.2, the previous privilege model will continue to be respected, but will be completely replaced with the `RESTORE` system-level privilege in a future version of CockroachDB. #86918
- The `SHOW REGIONS` statement now shows information about secondary regions. #86924
- The `SHOW SYSTEM GRANTS [FOR ROLE ...]` statement now allows you to see the grants done by `GRANT SYSTEM ...` #86700
- Added support for the `SHOW GRANTS` syntax: `SHOW GRANTS ON EXTERNAL CONNECTION "name" FOR [users...]`. #86700

### Operational changes

- Added logging on replicate queue processing in the presence of errors or when the duration exceeds 50% of the timeout. #86007
- Full cluster restores now fail if an upgrade may be in progress. #86848

### DB Console changes

- Added the Insights Overview page for statements to show if there are index recommendations, high retry count, and unknown for scenarios that don't fall into those categories. #86688
- Added the Schedules page to the DB Console. #86409
- Added the Statement Insight Details page to DB Console. #86779
- Added transaction and statement fingerprint IDs to their correlating tabs on the SQL Activity page in the DB Console. New columns are hidden by default. #85464
- Change column name from `User` to `User Name` on the Table Details and Grants pages in the DB Console. #86990
- Update "Sub-Optimal" label to "Suboptimal". #87068

### Bug fixes

- The timescale object is now properly constructed from session storage, preventing bugs and crashes in pages that use the timescale object when reloading the page. #86909
- Previously, escaping a double quote (`"`) with `COPY` in `CSV` mode could ignore all subsequent lines in the same `COPY` if an `ESCAPE` clause were specified. This is now resolved. #86929
- Changefeeds emitting to Kafka upon receiving a "message too large" error will now halve the size of their batches until it either succeeds or a batch size of 1 fails. #86138
- Added a missing memory accounting call when appending a KV to the underlying `kvBuf`. #86738
- Fixed the latency that is reported for `COPY` commands in the CLI and statistics reporting. #86991

### Contributors

This release includes 69 merged PRs by 35 authors.

## v22.2.0-alpha.1

Release Date: August 30, 2022

### Downloads

Warning:

CockroachDB v22.2.0-alpha.1 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.

Note:

Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.

#### Full CockroachDB executable

#### SQL-only command-line client executable

Warning:

CockroachDB executable for Windows is experimental and not suitable for production deployments. Windows 8 or higher is required.

### Docker image

To download the Docker image (Intel-only):

    docker pull cockroachdb/cockroach-unstable:v22.2.0-alpha.1

### Backward-incompatible changes

- CockroachDB no longer performs environment variable expansion in the parameter `--certs-dir`. Uses like `--certs-dir='$HOME/path'` (expansion by CockroachDB) can be replaced by `--certs-dir="$HOME/path"` (expansion by the Unix shell). #81298
- In the Cockroach CLI, `BOOL` values are now formatted as `t` or `f` instead of `True` or `False`. #81943
- Removed the `cockroach quit` command. It has been deprecated since v20.1. To shut down a node gracefully, send a `SIGTERM` signal to it. #82988
- Added a cluster version to allow the Pebble storage engine to recombine certain SSTables (specifically, user keys that are split across multiple files in a level of the log-structured merge-tree). Recombining the split user keys is required for supporting the range keys feature. The migration to recombine the SSTables is expected to be short (split user keys are rare in practice), but will block subsequent migrations until all tables have been recombined. The `storage.marked-for-compaction-files` time series metric can show the progress of the migration. #84887

- Using separate ports for TCP and RPC listeners is now encouraged for new clusters. Using a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the future. To split traffic, you can use one of the following approaches:

  - Preferred: keep port `26257` for SQL, and allocate a new port, e.g., `26357`, for node-node RPC connections. For example, you might configure a node with the flags `--listen-addr=:26357 --sql-addr=:26257`, where subsequent nodes seeking to join would then use the flag `--join=othernode:26357,othernode:26257`. This will become the default configuration in the next version of CockroachDB. When using this mode of operation, care should be taken to use a `--join` flag that includes both the previous and new port numbers for other nodes, so that no network partition occurs during the upgrade.
  - Optional: keep port `26257` for RPC, and allocate a new port, e.g., `26357`, for SQL connections. For example, you might configure a node with the flags `--listen-addr=:26257 --sql-addr=:26357`. When using this mode of operation, the `--join` flags do not need to be modified. However, SQL client apps or the SQL load balancer configuration (when in use) must be updated to use the new SQL port number.

  #85671

- If no `nullif` option is specified while using `IMPORT CSV`, then a zero-length string in the input is now treated as `NULL`. The quoted empty string in the input is treated as an empty string. Similarly, if `nullif` is specified, then an unquoted value is treated as `NULL`, and a quoted value is treated as that string. These changes were made to make `IMPORT CSV` behave more similarly to `COPY CSV`. If the previous behavior (i.e., treating either quoted or unquoted values that match the `nullif` setting as `NULL`) is desired, you can use the new `allow_quoted_null` option in the `IMPORT` statement. #84487

- `COPY FROM` operations are now atomic by default instead of being segmented into 100 row transactions. Set the `copy_from_atomic_enabled` session setting to `false` for the previous behavior. #85986

- The `GRANT` privilege has been removed and replaced by the more granular `WITH GRANT OPTION`, which provides control over which privileges are allowed to be granted. #81310

- Removed the ability to cast `int`, `int2`, and `int8` to a `0` length `BIT` or `VARBIT`. #81266

- Removed the deprecated `GRANT` privilege. #81310

- Removed the `ttl_automatic_column` storage parameter. The `crdb_internal_expiration` column is created when `ttl_expire_after` is set and removed when `ttl_expire_after` is reset. #83134

- Removed the byte string parameter in the `crdb_internal.schedule_sql_stats_compaction` function. #82560

- Changed the default value of the `enable_implicit_transaction_for_batch_statements` to `true`. This means that a batch of statements sent in one string separated by semicolons is treated as an implicit transaction. #76834

### Security updates

- HBA configuration can now restrict admin logins originating from `localhost`. This allows security conscious users to better restrict access to their instance. To restrict admins from logging in to `localhost` insert the following as the first line of your HBA configuration: `host all root 127.0.0.1/32 cert-password`. #77955
- Certain less-secure TLS 1.2 cipher suites are no longer supported. Clients more than five years old may fail to connect. CockroachDB now matches the "recommended" cipher list of the IETF defined in RFC 8447. #82362
- Changed access requirements to some observability features. Databases/tables/schema endpoints for admin UI require `admin` or `VIEWACTIVITY`. `EXPERIMENTAL_AUDIT` requires `admin` or `MODIFYCLUSTERSETTING`. SQL login requires that the `NOSQLLOGIN` or equivalent role are not set. #85769
- HTTP API endpoints under the `/api/v2/` prefix now allow requests through when the cluster is running in insecure mode. When the cluster is running in insecure mode requests to these endpoints will have the username set to `root`. #86417
- SCRAM is now the default password authentication method. Existing users with passwords will be automatically upgraded to use SCRAM when they next authenticate to the cluster. #74301

### General changes

- When using Azure Cloud Storage for data operations, CockroachDB now calculates the storage account URL from the provided `AZURE_ENVIRONMENT` query parameter. This defaults to `AzurePublicCloud` if not specified, to maintain backward compatibility. #80511
- CockroachDB now collects schema info if telemetry logging is enabled. This schema info is added to the telemetry log by a built-in scheduled job which runs on a weekly basis by default. This recurrence can be changed via the `sql.schema.telemetry.recurrence` cluster setting. The schedule can also be paused via `PAUSE SCHEDULE` followed by its ID, which can be retrieved by querying `SELECT * FROM [SHOW SCHEDULES] WHERE label = 'sql-schema-telemetry'`. #84761
- Changefeeds without a specified sink will no longer terminate when schema changes occur. #85458
- Core changefeeds are now more resilient to transient errors (ex. network blips) by adding checkpointing. Previously, transient errors would result in a Core changefeed stopping and terminating the underlying SQL statement. This would require the user to restart the SQL statement. Furthermore, if the Core changefeed were restarted during an initial scan, the initial scan would start from the beginning. For large initial scans, transient errors are more likely, so restarting from the beginning would likely see more transient errors and restarts, which would not progress the changefeed. Now, a Core changefeed will automatically take frequent checkpoints and retry from the last checkpoint when a transient errors occurs. #86253
- Scheduled backups now ensure that data to be backed up is protected from garbage collection until it has been successfully backed up. This active management of protected timestamps means that scheduled backups can run at a cadence independent from the GC TTL of the data. [#79976][#79976]

### Enterprise edition changes

- Incremental backups with mismatched localities are now blocked. #79135
- Users can now authenticate to AWS by passing in the argument `AUTH=assume` and specifying an `AWS_ROLE_ARN={role-ARN}`. A user with `AssumeRole` can optionally be specified with `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY` or left blank to use the default credentials chain. #79968
- Introduced the `primary_key_filter` option to restrict the span watched by a changefeed only to the portion that satisfies the filtering predicate. #80499
- Changefeed restarts and changefeeds started with the `cursor` option are now more efficient by using the catchup scan progress checkpoint. #77763
- Storage and KMS URIs for Google Cloud Storage in `BACKUP` and `RESTORE` now accept an `ASSUME_ROLE` parameter, which informs the current service account authenticated by either implicit or specified credentials to obtain temporary credentials for the service account specified by the `ASSUME_ROLE` parameter in order to access the resource specified by the URI. #80417
- The `CREATE CHANGEFEED` statement now supports general expressions: predicates and projections. Projections allow users to emit specific columnar data, including computed columns. While predicates (i.e., filters) allow users to restrict the data that emits to only those events that match the filter. For example: `CREATE CHANGEFEED INTO 'kafka://' AS SELECT * FROM t WHERE NOT cdc_is_delete()`. #82562
- Added replanning functionality for changefeeds when topology changes by adding a new replanning counter metric. This functionality is only supported for CockroachDB Serverless clusters.. #83143
- Allowed the `ASSUME_ROLE` parameter in Amazon S3 and Google Cloud Storage KMS URIs to specify a list of roles with a comma-separated string. The roles in the list can chain assume to access the resource specified by the URI. #83712
- The URI for Google Cloud Pub/Sub now accepts an `ASSUME_ROLE` parameter, which specifies a comma-separated list of service accounts to chain assume by the service account authenticated by the implicit or specified credentials. #84619
- Previously, if you dropped a column with the `schema_change_policy='stop'` option, the changefeed would stop. Dropping a column with a different policy would result in previous rows retransmitting with the dropped column omitted. In some cases, a changefeed may target specific columns (a column family) of a table. In these cases, if a non-target column is dropped, the changefeed should not stop or retransmit values, because the column was not visible to a consumer sink to begin with. With this change, dropping a non-target column from a table will not stop the changefeed when `schema_change_policy` is set to `stop`. With any other policy, dropping a non-target column will not trigger a backfill. #84674
- Implemented functionality to determine the number of column families that are referenced by a `SELECT` statement in changefeed expressions and handle appropriately. #84764
- `BACKUP`, `RESTORE`, and backup schedule creation now have corresponding events that emit to the telemetry channel. #82463
- Added the `ALTER BACKUP SCHEDULE` SQL statement to modify existing backup schedules. #85489
- `CREATE CHANGEFEED` statements with `AS SELECT ...` will require the option `schema_change_policy='stop'`. This means that the changefeed will stop if schema changes occur. #85896
- Introduced a new rangefeed RPC called `MuxRangeFeed`. Rangefeeds now use a common HTTP/2 stream per client for all range replicas on a node, instead of one per replica. This significantly reduces the amount of network buffer memory usage, which could cause nodes to run out of memory if a client was slow to consume events. The caller may opt in to use the mechanism by specifying `WITH MuxRangefeed` option when starting the rangefeed. However, a cluster wide `COCKROACH_ENABLE_MULTIPLEXING_RANGEFEED` environment variable may be set to `false` to inhibit the use of this new RPC. #75581
- `ALTER BACKUP SCHEDULE` now supports additional commands like `SET WITH`, `SET SCHEDULE OPTION`, `SET LABEL`, and `SET INTO`. #86190
- Changefeeds may opt in via `changefeed.mux_rangefeed.enabled` setting to use `MuxRangeFeed` RPC which multiplexes multiple rangefeed streams onto a single RPC stream per node. #86448
- Changefeeds now rate limit log messages related to resolved timestamps. #82838
- Adjusted `per_changefeed_limit` to `128MiB`. The previous default of `1GiB` could pressure garbage collection, which would potentially affect foreground traffic. #84686

### SQL language changes

- Core users that schedule a backup without the `FULL BACKUP ALWAYS` clause will receive a warning. #77506
- Implemented the `pg_options_to_table` built-in, which converts an options array format to a table. #77883
- `COMMENT ON SCHEMA` can now use qualified schema names. For example, `COMMENT ON SCHEMA sc_name ...` and `COMMENT ON SCHEMA db_name.sc_name ...`. #79055
- Added the `OVERLAPS` syntax and `overlaps()` built-in function. The semantics is the same as the `OVERLAPS` syntax in PostgreSQL. This expression yields `true` when two time periods (defined by their endpoints) overlap, `false` when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or timestamp followed by an interval. When a pair of values is provided, either the start or the end can be written first. `OVERLAPS` automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval `start <= time < end`, unless `start` and `end` are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap. #77015
- Direction is now explicit for inverted indices in `SHOW CREATE TABLE` output. #78549
- Previously, the delimiters for PostgreSQL `geography` and `geometry` was inconsistent. It has been updated to use the `:` delimiter. #82304
- `ALTER PRIMARY KEY` will no longer create a secondary index on the previous PK columns if they're a strict prefix of an existing secondary index. #78046
- The `to_regclass`, `to_regnamespace`, `to_regproc`, `to_regprocedure`, `to_regrole`, and `to_regtype` built-in functions are now supported, improving compatibility with PostgreSQL. #78652
- Changefeed statements now detect duplicate targets and throw an error. #79465
- Previously, `BACKUP` allowed the user to specify a custom subdirectory name for their backups via `BACKUP .. INTO {subdir} IN {collectionURI}`. This is no longer supported. Users can only create a full backup via `BACKUP ... INTO {collectionURI}` or an incremental backup on the latest full backup in their collection via `BACKUP ... INTO LATEST IN {collectionURI}`. This deprecation also removes the need to address a bug in `SHOW BACKUPS IN`, which cannot display user-defined subdirectories. #79447
- Added a session variable, `enable_multiple_modifications_of_table`, which can be used instead of cluster variable `sql.multiple_modifications_of_table.enabled` to allow statements containing multiple `INSERT ON CONFLICT`, `UPSERT`, `UPDATE`, or `DELETE` subqueries to modify the same table. As with `sql.multiple_modifications_of_table.enabled`, with this session variable enabled there is nothing to prevent the table corruption seen in issue #70731 from occurring if the same row is modified multiple times by different subqueries of a single statement. We recommend rewriting these statements, but the session variable is provided as an aid if this is not possible. #79677
- Previously, if a column in a table has a comment, `SHOW CREATE TABLE` would fail after the column type is changed. This is now fixed. #79998
- Added the built-in functions: `uuid_nil`, `uuid_ns_dns`, `uuid_ns_url`, `uuid_ns_oid`, and `uuid_ns_x500` provided by the `uuid-ossp` extension in PostgresSQL. #80204
- Added the built-in functions: `uuid_generate_v1`, `uuid_generate_v1mc`, `uuid_generate_v3`, and `uuid_generate_v5`. #80204
- The command `CREATE EXTENSION "uuid-ossp"` no longer fails, since CockroachDB now includes all the built-in functions from this extension. #80204
- Users can now pass locality-aware backup URIs to `SHOW BACKUP`. This change only affects `SHOW BACKUP` with the new syntax: e.g., `SHOW BACKUP FROM LATEST IN ({collectionURI}, {localityURI1}, {localityURI2})`. Users cannot run `SHOW BACKUP` for locality-aware backups created using the `incremental_location` parameter. #79121
- Table scans performed as a part of index joins, lookup joins, inverted joins, and zigzag joins now respect the row-level locking strength and wait policy specified by the optional `FOR SHARE/UPDATE NOWAIT` clause on `SELECT` statements. #60719
- The `pg_cast` table was populated in order to match PostgreSQL behavior. #79537
- Constraints that only include hidden columns are no longer excluded in `SHOW CONSTRAINTS`. You can enable the previous behavior using the `show_primary_key_constraint_on_hidden_columns` session variable. #80154
- Introduced the `ST_XMin`, `ST_XMax`, `STYMin`, and `ST_YMax` geospatial built-ins. #80363
- Introduced the `st_makeenvelope` built-in. #80408
- Added the `pgcrypto` `gen_salt` built-in with support for the `des`, `xdes`, `md5`, `bf` algorithms. #80318
- Added a new `check_files` option to `SHOW BACKUP`. This option checks that all SST files and metadata in a backup chain are in their expected location in external storage. If `SHOW BACKUP` cannot read from a file, an error message with the problematic file path returns. A successful `SHOW BACKUP` with `check_files` will also return the additional `file_bytes` column that indicates the estimated bytes on external storage storing a table object in the backup, analogous to the return pattern of the `rows` and `size_bytes` columns. #80491
- Previously, when a hash-sharded index was dropped, the accompanying shard column would also drop if no other index used this shard column. For hash-sharded indexes created in v21.2 and earlier, this shard column is a physical, `STORED` column. Dropping such a physical column can be very expensive since it requires a full table rewrite. For hash-sharded indexes created in v22.1 and later, this shard column is a virtual computed column. Dropping a virtual column is not as costly. Now, if the to-be-dropped sharded index has a physical shard column (and no other index uses that column), CockroachDB will drop only the index if not `CASCADE`, or will drop both the index and the column if `CASCADE`. #80806
- Allowed wildcards to `SHOW GRANTS` for all schemas in a database. #80861
- Added the `pgcrypt` `crypt` built-in with support for the `md5` and `bf` algorithms. #80809
- Added a notice to the `SET CLUSTER SETTINGS sql.defaults...` statement that recommends using the `ALTER ROLE` syntax instead: the `ALTER ROLE` syntax allows users to set default values for session variables making `SET CLUSTER SETTINGS sql.defaults...` redundant.. #80548
- The `JSON` `?` string operator is now index accelerated if there is an inverted index over the `JSON` column referred to on the left-hand side of the expression and a constant on the right. #81253
- The `?&` and `?|` operators are now index accelerated if the left-hand side is an inverted indexed `JSON` column and the right-hand side is a constant. #81253
- Added a new `RESTART` option to `ALTER SEQUENCE` and `CREATE SEQUENCE` that sets the `nextval()` to the given number, or back to the original `START` value. This implements the `ALTER SEQUENCE` PostgreSQL behavior. This is similar to calling the `setval()` function with `is_called = false`. That is, the specified value will return by the next call of `nextval()`. Writing `RESTART` with no restart value is equivalent to supplying the start value that was recorded by `CREATE SEQUENCE` or last set by `ALTER SEQUENCE START WITH`. In contrast to a `setval()` call, a `RESTART` operation on a sequence is transactional and blocks concurrent transactions from obtaining numbers from the same sequence. If this is not the desired mode of operation, `setval()` should be used. #81377
- Added syntax support for `{GRANT|REVOKE} ... ON {SEQUENCE | ALL SEQUENCES IN SCHEMA}`. #79862
- `SHOW EXPERIMENTAL_FINGERPRINTS` now supports tables with expression indexes. #81042
- Expanded the capabilities of the `EXPERIMENTAL SCRUB` statement to include checking unique constraints for primary keys, unique indexes, and unique columns without indexes. The usage and output of `SCRUB` is unchanged, but if there is a unique constraint violation, users will see the error message `unique_constraint_violation` for all rows that violate the constraint, along with information about the row. #78297
- Added the `pg_trgm.similarity_threshold` session setting that controls the threshold at which the trigram similarity operator `%` returns `true` versus `false`. #81418
- Added support for the `pg_trgm` built-ins `show_trgm`, for showing the trigrams in a string and a measure of how similar two strings are based on their trigrams. #81418
- Added the `%` string trigram similarity overload. #81418
- The `extra_float_digits` session variable now defaults to `1`. The meaning of the variable has also changed. Now, any value greater than `0` causes floats to be formatted in their shortest precise decimal representation. That is, the string representation produced is closer to the actual binary value than to any other value. (Previously, this was only the behavior when `extra_float_digits` was set to `3`.) This change was made in accordance with an equivalent change that was part of the PostgreSQL 12.0 release. The behavior of a non-positive `extra_float_digits` value is unchanged: such a value will still reduce the number of float digits shown in the output string. The formula to compute the number of digits shown is `max(1, (DIGITS + extra_float_digits))`, where `DIGITS=6` for `FLOAT4` values, and `DIGITS=15` for `FLOAT8` values. #82022
- Added a new `full_scan` column to the `crdb_internal.{cluster,node}_queries` table, which describes whether a query contains a full table or index scan. This column is included in the `SHOW QUERIES` command. Note that this information is only valid when the query is in the `executing` phase. The `ListSessions` API includes this information under the field `is_full_scan` in the active query for a session. #81531
- `STRING` columns now support inverted indexes using trigrams. These indexes can be searched using the `=`, `LIKE`, `ILIKE`, and `%` (similarity) predicates. #79705
- Permitted usage of `jsonb_ops`, `array_ops`, `gin_trgm_ops`, and `gist_trgm_ops` as an operator class in inverted index creation. #79705
- Casting from an `INT` to `OID`, or calling the `oid` built-in function, and using an integer that is larger than `32` bits now results in an error. Specifically, the range for valid inputs for these uses is `[MinInt32, MaxUint32]`. #82430
- `SHOW BACKUP` WITH `check_files` will display up to 10 missing SST files. #82274
- Index recommendations are now supported for spatial indexes. #82293
- Added the `to_timestamp` function that converts Unix epoch of `FLOAT`, `INT`, `DECIMAL`, and `TEXT` to `TIMESTAMPTZ`. #82523
- A column's `DEFAULT/ON UPDATE` clause can now have a type that differs from the column type, as long as that type can be assignment-cast into the column's type. This change increases compatibility with PostgreSQL. #81071
- `COPY ... FROM CSV HEADER` is now supported. #82457
- Added `rowCount` to TTL job progress. #81917
- Added logic for `GRANT ... ON` sequence names. #82458
- Introduced system-level privileges, which apply cluster-wide. Example: `GRANT SYSTEM MODIFYCLUSTERSETTING TO foo`. Currently `MODIFYCLUSTERSETTING` is the only system-level privilege, it allows users to query the `crdb_internal.cluster_settings` table. #82166
- Added a `cluster.preserve-downgrade-option.last-updated` metric that reports the Unix timestamp of the last updated time of the `cluster.preserve_downgrade_option` setting. This metric is now also emitted to Prometheus, and used to display a banner to the DB Console if `cluster.preserve_downgrade_option` has been set for greater than 48 hours. This change provides increased observability into upgrade finalization. #82633
- Added support for `DROP OWNED BY`. #82936
- Created two invariants for the `stream_ingestion_stats` built-in, for protobuf and JSON respectively, and extended them to return more details. #83066
- Added support for `JSONB` subscripting in `SELECT`-style cases, e.g., `SELECT json_field['a'] ... WHERE json_field['b'] = ...`. #82877
- Added a new execution statistic that tracks the number of gRPC calls issued to perform read operations to the output of `EXPLAIN ANALYZE`, which exposes low-level details that might aid with debugging the performance of queries. #83365
- Added a new `ttl_expiration_expression` expression for `CREATE TABLE` and `ALTER TABLE`. `ttl_expiration_expression` accepts an expression that returns a `TIMESTAMP` to support custom TTL calculation. The following are supported:
  - `CREATE TABLE ... WITH (ttl_expiration_expression='...')`
  - `ALTER TABLE ... SET (ttl_expiration_expression='...')`
  - `ALTER TABLE ... RESET (ttl_expiration_expression)` #82686
- Added a new column, `locality` to the `system.sql_instances` table, which stores the locality of a SQL instance if it was provided when the instance was started. This exposes a SQL instance's locality to other instances in the cluster for query planning. #82915
- Implemented `DROP INDEX` under the declarative schema changer. #80133
- Renamed `oldest_query_start` in the `crdb_internal.cluster_sessions` and `crdb_internal.node_sessions` tables to `active_query_start`, as this column contains the time at which the currently active query was started, not the time at which the session's first query was started. #83451
- The `CREATE CHANGEFEED AS` statement no longer requires `WITH DIFF` when using `cdc_prev()`. #83717
- CockroachDB can now parse the `CREATE FUNCTION` statement, but an unimplemented error will return since the statement is not fully implemented. #83891
- `DROP` statements performed by the declarative schema changer (which is the case by default) now transition descriptor states to `OFFLINE` in the initial schema change transaction before transitioning them to `DROP` in a subsequent transaction executed by the schema change job. Changefeeds watching tables that are dropped will now reflect this descriptor state in the returned error (i.e., either dropped or taken offline). Additionally, a concurrent backup will see the table as `OFFLINE` before it reaches `DROP`. potentially causing the offline table to be included in the backed-up data. #83915
- Extended the `CREATE MATERIALIZED VIEW` statement to support the `WITH NO DATA` clause, which allows the creation of materialized views with no data. Such views require a refresh at least once prior to access. #83347
- Added the `sql.metrics.statement_details.index_recommendation_collection.enabled` cluster setting that can be disabled if index recommendation generation is causing performance issues. #84282
- Added sequence option info for identity columns under `information_schema`. #84034
- The `inet` function has been added to support the conversion of a supplied type to that of the `INET` type family. If the conversion fails, a SQL error will be output. #83668
- The last column of an `INVERTED INDEX` can no longer have the `DESC` option. If `DESC` was used in prior versions, it could cause internal errors. #84516
- Introduced `CREATE EXTERNAL CONNECTION` syntax that can create an external connection representing a resource that resides outside of CockroachDB. The only supported resource at the moment is a `nodelocal` URI that can be represented as an external connection object using: `CREATE EXTERNAL CONNECTION foo AS 'nodelocal://1/foo'`. #84310
- Added `DROP EXTERNAL CONNECTION` to drop a previously created external connection object. #84751
- Cluster `BACKUP` and `RESTORE` no longer includes job records, which previously were usually only restored in a canceling state with the exception of schema changes, which restored to their initial running state. Instead, any schema change jobs required for restored tables are recreated after restoring the tables. #84886
- Introduced an `EXTERNALCONNECTION` system-level privilege that is required to create an external connection object to represent an underlying resource. #85007
- Added a new `is_visible` column to the `crdb_internal.table_indexes` and `information_schema.statistics` tables. Also, added a new `visible` column to the output of `SHOW INDEX`, `SHOW INDEXES`, and `SHOW KEYS`. The `is_visible` or `visible` columns indicates whether the index is visible to the optimizer. #84776
- Bulk operations and changefeeds will accept an `external` scheme URI that points to a previously created external connection object. These operations can then interact with the underlying resource represented by the object as they did before. #84931
- Introduced `VIEWACTIVITY`, `VIEWACTIVITYREDACTED`, `VIEWCLUSTERSETTING`, `CANCELQUERY`, and `NOSQLLOGIN` as system privileges. #84198
- The `SHOW DEFAULT PRIVILEGES` command now has a column that indicates if the default privilege will give the `GRANT` option to the grantee. #85027
- Previously, `ALTER DEFAULT PRIVILEGES` would error out on functions. Now, the `ALTER DEFAULT PRIVILEGES` statement performs the `GRANT`/`REVOKE` with the newly added `EXECUTE` privilege from default privileges. #84471
- Added the explicit `"true"` and `"false"` values for `detached` and `revision_history` arguments in `BACKUP` and `CREATE SCHEDULE FOR BACKUP`. #85146
- CockroachDB now supports secondary regions. Secondary regions makes it possible to specify a failover region, which will receive the leaseholder if the primary region fails. #84450
- The parser now supports creating an index marked as invisible. However, this is not fully implemented and executing it returns an `unimplemented` error immediately. #84783
- Renamed `statement` to `stmt` and `transaction` to `txn` in columns in the `crdb_internal.node_execution_insights` table. Added `txn_fingerprint_id`, `query`, `status`, `start_time`, `end_time`, `full_scan`, `user_name`, `app_name`, `database_name`, `plan_gist`, `rows_read`, `rows_written`, `priority`, and `retries` columns. #85131
- The `CREATE VIEW` statement can now have a constant `NULL` column definition. The resulting column is of type `TEXT`. #85134
- A Google Cloud Storage KMS can be represented as an external connection object, which can be used during `BACKUP` or `RESTORE` using the `external` URI. #85075
- The `IMPORT INTO` statement now supports importing from `CSV`, `AVRO`, and delimited formats into a table with partial indexes. This was previously disallowed. #85244
- Introduced a new `crdb_internal` virtual table, `cluster_execution_insights`, offering a cluster-wide view of the same node-local information available in `node_execution_insights`. Currently, the insights subsystem is still under development and disabled by default. #85339
- Changed `EXPLAIN` output of full scans with soft limits to `FULL SCAN (SOFT LIMIT)` instead of `FULL SCAN`, to distinguish them from unlimited full scans. Unlimited full scans always scan the entire index. Full scans with soft limits could scan the entire index, but usually halt early once enough rows have been found to satisfy their parent operator. #85421
- Added support for privileges on virtual tables. Previously users were unable to `GRANT` on virtual tables, including `crdb_internal`, `pg_catalog`, and `information_schema`. Now users can `GRANT/REVOKE` `SELECT` privilege on virtual tables. `SELECT` is needed to query a virtual table. Note that virtual table privileges are not per database. Executing `GRANT SELECT ON crdb_internal.tables TO foo` allows `foo` to select on `crdb_internal.tables` across all databases. Though executing `GRANT SELECT ON dbname.crdb_internal.tables TO foo` completes without error, the database is ignored. #83604
- Added the `crdb_internal.request_statement_bundle` built-in, which allows the statement bundle to be requested from the SQL CLI. The new built-in takes three parameters: statement fingerprint text, minimum execution latency for the statement, and the duration the statement bundle request will stay valid for. The `VIEWACTIVITY` or `admin` role option is required to use this built-in. A user with the `VIEWACTIVITYREDACTED` role option is not allowed to use this built-in. #79693
- Added the column `index_recommendations` to `crdb_internal.node_statement_statistics`, `crdb_internal.cluster_statement_statistics`, `system.statement_statistics`, and `crdb_internal.statement_statistics`. #84618
- The `pg_proc.proisstrict` column is now correctly populated instead of always being `false`. If this column is `true`, it indicates that the function will not be called if any of its inputs are `NULL`. Instead, it will directly evaluate to `NULL`. #85676
- When statistics are refreshed for a table, CockroachDB now deletes any existing statistics on that table from columns or sets of columns that do not have their statistics refreshed by default. This ensures that stale statistics are removed and do not impact the ability of the optimizer to create a high quality query plan. The retention time for these statistics is controlled by a new cluster setting, `sql.stats.non_default_columns.min_retention_period`, which defaults to 24 hours. #85586
- Introduced the `ALTER DATABASE database_name ALTER LOCALITY {GLOBAL|REGIONAL|REGIONAL IN} set_zone_config` syntax, which allows setting the zone config extension. #83605
- Added `last_retry_reason` and `exec_node_ids` columns to the `crdb_internal.node_execution_insights` table. #85634
- The `EXPLAIN` output no longer annotates simple operations (like `render` and `project`) with the execution statistics or estimates since that information is redundant (it is copied from the child operations). #85649
- Users can now `GRANT USAGE ON EXTERNAL CONNECTION` and `REVOKE USAGE ON EXTERNAL CONNECTION` to grant and revoke the `USAGE` privilege. This privilege is required by all operations that interact with external connections. #85556
- Previously, the `pg_proc` table was only populated with built-in functions. With the added support for user-defined functions creation, the `pg_proc` table has now been extended to include user-defined function data as well. #85656
- Added a new `SHOW CREATE FUNCTION` statement, taking a function name as an argument. If the given function name is qualified, the explicit schema will be searched. If the function name is not qualified, the schemas on the search path are searched and functions from the most significant schema are returned. #85656
- Previously, `::regproc` casting only supported built-in functions. Now it is extended to support user-defined functions as well. #85656
- Added a new virtual table `crdb_internal.create_function_statements` which can be used to query `CREATE` statements of user-defined functions, as well as parent db and schema ids. #85656
- Added the `schema_only` option to `RESTORE`, which enables you to run a regular restore without restoring any user table data. This can be used to quickly validate that a given backup is restorable. A `schema_only` restore takes a fraction of a regular restore's runtime. Note that during a cluster level, `schema_only` restore, the system tables are read from storage and written to disk, as this provides important validation coverage without much runtime cost (system tables should not be large). After running a successful `schema_only` restore, you can revert the cluster to its pre-restore state by dropping the descriptors added by the `schema_only` restore (e.g., if you restored a database, you can drop the database after the restore completes). #85231
- Added the `VIEWDEBUG` and `VIEWCLUSTERMETADATA` system privileges. #85280
- Added new index recommendations that are generated every hour and available from `system.statement_statistics` and `crdb_internal.statement_statistics`. Added a new `sql.metrics.statement_details.max_mem_reported_idx_recommendations` cluster setting with a default value of `100k`. #85343
- `SELECT ... FOR {UPDATE,SHARE} SKIP LOCKED` is now supported. The option can be used to skip rows that cannot be immediately locked instead of blocking on contended row-level lock acquisition. #85720
- Implemented `DROP FUNCTION` in the legacy schema changer. Now users can drop a function with a function name or a function signature. #85718
- Users can now `GRANT DROP ON EXTERNAL CONNECTION` and `REVOKE DROP ON EXTERNAL CONNECTION` to grant and revoke the `DROP` privilege. This privilege is required by the user to `DROP` a particular external connection. #85770
- The `CREATE EXTERNAL CONNECTION` statement can be now used to represent a `kafka` sink. Subsequently, users can run `CREATE CHANGEFEED` with an `external:///<external-connection-object-name` URI as the sink to use the Kafka resource represented by the external connection object. #85410
- Added the `strptime` and `strftime` built-in functions as aliases for `experimental_strptime` and `experimental_strftime`. #85756
- The `CREATE EXTERNAL CONNECTION` statement can now be used to represent an Amazon S3 URI. #85680
- Added the `format` built-in function. `format` interpolates arguments into a string in the style of C's `sprintf`. For example, `format('Hello, %s', 'world')` returns `'Hello, world'`. #84107
- The declarative schema changer now falls back to the legacy schema changer when a user-defined function is found in the dependency graph when encountering a `DROP` statement. This no longer throws an unimplemented error. #85981
- Arrays can now be imported in a CSV file using the `{}` format, similar to `COPY FROM`. Importing array expressions (e.g., `ARRAY[1, 2, 3]`) is still supported as well. #85850
- Creating a not visible index using `CREATE TABLE …(INDEX … NOT VISIBLE)` or `CREATE INDEX … NOT VISIBLE` is now supported. #85794
- The output from `SHOW STATISTICS` is now more deterministic. #77070
- Added a new `WITH FORECAST` option to the `SHOW STATISTICS` statement, which calculates and displays forecasted statistics along with the existing table statistics. #77070

- Added the `trunc(decimal, int)` built-in function, which truncates the given decimal value to the specified number of decimal places. A negative value can be used for the scale parameter, which will truncate to the left of the decimal point. Example: #85890

      SELECT trunc(541.234, 2), trunc(541.234, 0), trunc(541.234, -1);
      trunc  | trunc | trunc
      -------+-------+---------
      541.23 |   541 | 5.4E+2.

- The `CREATE EXTERNAL CONNECTION` statement can be used to represent an underlying `userfile` resource. #86006

- Altering an index to visible or not visible using `ALTER INDEX … VISIBLE` or `NOT VISIBLE` is now supported. #86032

- When performed by the declarative schema changer (as is the case by default) the `ALTER PRIMARY KEY` statement now also drops the `rowid` column when no references are held to it anywhere. The `rowid` column is a hidden column which is implicitly added and serves as primary key on any table created without explicitly specifying a primary key. #86071

- Session setting `optimizer_use_not_visible_indexes` can be used to disable not visible index features. When this setting is enabled, the optimizer treats not visible indexes as if they were visible and can choose to use them for query planning. By default, this setting is disabled. #86033

- Google Cloud KMS will now accept the `gcp-kms` scheme along with the existing `gs` scheme. External Connections will only recognize the `gcp-kms` scheme when being created to represent a KMS resource. #85957

- The asynchronous garbage collection process has been changed such that quickly after dropping a table, index, or database, or after refreshing a materialized view, the system will issue range deletion tombstones over the dropped data. These tombstones will result in the KV statistics properly counting these bytes as garbage. Before this change, the asynchronous "gc job" would wait out the TTL and then issue a lower-level operation to clear out the data. That meant that while the job was waiting out the TTL, the data would appear in the statistics to still be live. #85878

- The `CREATE EXTERNAL CONNECTION` statement can be used to represent an underlying Google Cloud Storage resource. #85964

- When running `ALTER TABLE ... ADD PRIMARY KEY` or `ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY` in a single-statement, implicit transaction, where no primary key had previously been added to the table, the previous `rowid` column which had been automatically created as the table's `PRIMARY KEY` will now be dropped. #86195

- Added contention time to `execution_insights`. #85959

- Added a new `alter_primary_region_super_region_override` setting, which must be enabled to be able to move a secondary region either inside or outside of a super region. The primary region must be moved before moving the secondary region. #84999

- Added support for the `IF EXIST` syntax on the `DROP SECONDARY REGION`statement. Using it will avoid returning an error if a secondary region is not defined on a database. #84999

- Enabled a new subsystem, `insights`, for gathering slow statement executions in the `crdb_internal.cluster_execution_insights` table along with possible reasons for the slowness: full scans or missing indexes, contention, plan changes, retries, etc. This system may be tuned by a handful of new cluster settings and monitored with a handful of new metrics, all in the `sql.insights` namespace. #86216

- The `CREATE EXTERNAL CONNECTION` statement can be used to represent an Azure Storage URI. #86257

- Added the `SHOW CREATE EXTERNAL CONNECTION` and `SHOW CREATE ALL EXTERNAL CONNECTIONS` statements, which display the connection name and the unredacted query used to create the external connection. Currently, this can only be run by users of the `admin` role. #86161

- Added index recommendations to `execution_insights`. #86055

- Added support for the `verify_backup_table_data` option to the `RESTORE` statement. When using this option, along with the required `schema_only` option, a `schema_only` restore will run and all user data will be read from external storage, checksummed, and discarded before getting written to disk. This option provides two additional validation steps that a regular `schema_only` restore and `SHOW BACKUP` with `check_files` cannot provide:

  - `RESTORE` will verify that all data can be read and rekeyed to the restoring cluster
  - `RESTORE` will verify that all data passes a checksum check #86136

- The `CREATE EXTERNAL CONNECTION` statement can be used to represent an `aws-kms` scheme that represents an Amazon S3 KMS resource. #86402

- `DROP OWNED BY` can no longer be performed if the user has system-level privileges defined (in `system.privileges`). #86619

- Added support for `DISCARD SEQUENCES`, which discards all sequence-related state data such as `currval`/`lastval`. `DISCARD ALL` now also discards sequence-related state. #86230

- `EXPLAIN ANALYZE` output now contains a warning when the estimated row count for scans is inaccurate and includes a hint to collect the table statistics manually. #86677

- The new `sql.stats.response.show_internal` cluster setting can be used to display information about internal stats on the SQL Activity page, with the `fingerprint` option. The setting defaults to `false`. #86679

### Operational changes

- Introduced the `kv.allocator.l0_sublevels_threshold` and `kv.allocator.L0_sublevels_threshold_enforce` cluster settings, which enable excluding stores as targets for allocation and rebalancing of replicas when they have high-read amplification, indicated by the number of L0 sub-levels in level 0 of the store's LSM. By default, `kv.allocator.l0_sublevels_threshold` is set to `20` and `kv.allocator.l0_sublevels_threshold_enforce` is set to `block_none_log`. When both `kv.allocator.l0_sublevels_threshold` and the cluster average is exceeded, the action corresponding to `kv.allocator.l0_sublevels_threshold_enforce` is taken, as follows: #78608
  - `block_none` will exclude no candidate stores
  - `block_none_log` will exclude no candidates but log an event
  - `block_rebalance_to` will exclude candidates stores from being targets of rebalance actions
  - `block_all` will exclude candidate stores from being targets of both allocation and rebalancing.
- Added `requests-per-second`, exposed through the `rebalancing.requestspersecond` metric. `requests-per-second` tracks the average number of requests received per store, aggregated over the ranges it contains. Also added `reads-per-second`, exposed through the `rebalanacing.readspersecond` metric. `reads-per-second` tracks the count of keys read per second, on a replica basis. #76609
- `HottestRanges` will now report additional range statistics for the reported ranges. These statistics are:
  - requests per second: the number of requests received by this range recently per second.
  - writes per second: the number of keys written to in this range recently per second.
  - reads per second: the number of keys read from this range recently, per second.
  - write bytes per second: the number of bytes written to this range recently, per second.
  - read bytes per second: the number of bytes read from this range recently, per second. #76609
- Increased the default value of `kv.transaction.max_refresh_span_bytes` from 256KB to 4MB. #80115
- Added metrics `range.snapshots.shapshots.(unknown|recovery|rebalancing).sent-bytes` and `range.snapshots.shapshots.(unknown|recovery|rebalancing).rcvd-bytes` to the metrics dashboard. This allows tracking the number of bytes sent/received for each type of metric in addition to the total bytes sent/received. #81860
- `httpSink` and `fluentSinks` will now, by default, have buffered writes enabled. This means that writes to these sinks will be asynchronous. This will show in the output of `debug check-log-config` as well as impact the default behavior of these two types of network sinks. This is enabled via a new default `buffering` configuration for both the `httpSink` and `fluentSink`, where the default values are as follows: #82893
  - `max-staleness`: The maximum amount of time between flushes to the underlying http or fluent sink. Default: `5s`
  - `flush-trigger-size`: The size in bytes of accumulated messages in the buffer that will trigger a flush. 0 disables this trigger. Default: `1MiB`
  - `max-buffer-size`: Limits the size of the buffer. When a new message is causing the buffer to overflow beyond this limit, existing messages are dropped. Default: `50MiB`
- I/O admission control now reduces the likelihood of storage layer write stalls, which can be caused when memtable flushes become a bottleneck. This is done by limiting write tokens based on flush throughput, so as to reduce storage layer write stalls. Consequently, write tokens are now limited both by flush throughput, and by compaction throughput out of L0. This behavior is enabled by default. The `admission.min_flush_util_fraction` cluster setting, defaulting to `0.5`, can be used to disable or tune flush throughput-based admission tokens. Setting it to a value greater than `1`, e.g., `10`, will disable flush-based tokens. Tuning the behavior, without disabling it, should be done only on the recommendation of a domain expert. #82440
- The `admission.kv.pause_replication_io_threshold` cluster setting can be set to a nonzero value to reduce I/O throughput on followers that are driven toward an inverted LSM by replication traffic. The functionality is disabled by default. A suggested value is `0.8`, meaning that replication traffic to non-essential followers is paused before these followers will begin throttling their foreground traffic. #83851
- Adjusted the way memory is tracked against `kv.transaction.max_intents_bytes` and `kv.transaction.max_refresh_spans_bytes` to be more precise. As a result, the stability of CockroachDB has improved. However, this change effectively reduces the budgets determined by those cluster settings. In practice, this means that:
  - the intents might be tracked more coarsely (due to span coalescing), which makes the intent resolution less efficient.
  - the refresh spans become more coarse too, making it more likely that `ReadWithinUncertaintyIntervalError`s are returned to the user rather than retried transparently. #84230
- Added the storage metrics `rangekeycount`, `rangekeybytes`, `rangevalcount`, and `rangevalbytes` for MVCC range keys (i.e., MVCC range tombstones). These are analogous to the corresponding point key metrics (e.g., `keycount`). #85453
- Added new metrics `range.snapshots.(send|recv)-queue` and `range.snapshots.(send|recv)-in-progress` to track the number of queued and in-progress snapshots being sent or received on a store. #84947
- The cluster settings `bulkio.restore_at_current_time.enabled` and `bulkio.import_at_current_time.enabled`, which were introduced in v22.1 and defaulted to `true`, have been retired. They are now always enabled. #85757
- Added new metrics for tracking the successes/errors of a replica being processed by the replicate queue, using the allocator action as a method of categorizing these actions.
  - `queue.replicate.addreplica.(success|error)`
  - `queue.replicate.removereplica.(success|error)`
  - `queue.replicate.replacedeadreplica.(success|error)`
  - `queue.replicate.removedeadreplica.(success|error)`
  - `queue.replicate.replacedecommissioningreplica.(success|error)`
  - `queue.replicate.removedecommissioningreplica.(success|error)` #85844
- Clusters can now run nodes with different `--max-offset` settings at the same time. This enables operators to perform a rolling restart to change the value of each node's `--max-offset` flag. #85983
- Introduced a new `server.secondary_tenants.redact_trace` cluster setting that controls if traces should be redacted for operations run on behalf of secondary tenants. #85853
- The `admission.kv.pause_replication_threshold` cluster setting is now set to a default value of `0.8`. On a fully migrated v22.2+ deployment, this will allow the KV layer to pause replication streams to followers located on stores that are close to activating their I/O admission control subsystem (thereby protecting these followers from additional overload). This cluster setting can be disabled by setting it to `0`. #86147
- Added a `sql.insights.execution_insights_capacity` cluster setting, which limits the number of SQL execution insights retained in memory per node. #86272
- The new `sql.insights.high_retry_count.threshold` cluster setting may be used to configure how many times a slow statement (as identified by the execution insights system) must have been retried to be marked as having a high retry count. #86415
- Finalizing an upgrade to v22.2 requires that all in-flight schema changes enter a terminal state. This may mean that finalization takes as long as the longest-running schema change. #76154
- The option `sql.mvcc_compliant_index_creation.enabled` has been removed. #76154
- Added a new time series metric `storage.keys.range-key-set.count` for observing the count of internal range key set keys in the storage engine. In v22.2, these RangeKeySet keys are only used during `DROP`/`TRUNCATE` table operations, or when canceling an import. #86570
- The `sql.insights.anomaly_detection.enabled` cluster setting now defaults to `true`, and the `sql.insights.anomaly_detection.latency_threshold` cluster setting now defaults to `50ms`, down from `100ms` to complement the fixed-threshold detector's default of `100ms`. #86673
- The disk bandwidth constraint can now be used to control admission of elastic writes. This requires configuration for each store, via the `--store` flag, that now contains an optional provisioned-rate field. The provisioned-rate field, if specified, needs to provide a disk-name for the store and optionally a disk bandwidth. If the disk bandwidth is not provided the cluster setting `kv.store.admission.provisioned_bandwidth` will be used. The cluster setting defaults to `0` (which means that the disk bandwidth constraint is disabled). If the effective disk bandwidth is `0` (including if using the possibly overridden cluster setting), the disk bandwidth constraint is disabled. Additionally, the admission control cluster setting `admission.disk_bandwidth_tokens.elastic.enabled` (which defaults to `true`) can be used to turn off enforcement even if other settings enable it. Turning off enforcement will still output all the relevant information about disk bandwidth usage, so can be used to observe part of the mechanism in action. To summarize, to enable this for a cluster with homogeneous disk, provide a disk-name in the provisioned-rate field in the store-spec, and set the `kv.store.admission.provisioned_bandwidth` cluster setting to the bandwidth limit. To only get information about disk bandwidth usage by elastic traffic (currently via logs, not metrics), perform the above actions and also set `admission.disk_bandwidth_tokens.elastic.enabled` to `false`. #86063
- The `admission.kv.pause_replication_io_threshold` cluster setting now defaults to `0` (off). #86776
- Clusters that are upgraded to an alpha or other manual build from the development branch will not be able to subsequently upgrade to a release build. #86345
- Added the `rebalancing.writebytespersecond` and `rebalancing.readbytespersecond` time series metrics. These metrics reflect the average number of bytes written and read across all replicas per store, over the last 30 minutes. #80245

### Command-line changes

- Added support for the `\password` CLI command that enables secure alteration of the password for a user. The given password will always be pre-hashed with the password hash method obtained via the session variable `password-encryption`, e.g., `scram-sha-256` as the default hashing algorithm. #77975
- Changed the default `debug compact` maximum compaction concurrency to the number of processors, and added a `--max-concurrency` flag for overriding the new default. #78987
- The standalone `cockroach-sql` executable now has more compatibility with `cockroach sql`, so it can be used as a drop-in replacement. For example, it supports running without a URL, using connection defaults. It also supports overriding `--certs-dir` and other client-side options also supported by `cockroach sql`. #82020
- `BYTEA` values are now formatted according to the `bytea_output` session setting. #81943
- The statement tag displayed for `INSERT` statements now has the full information returned by the server: the string `"INSERT"`, followed by the `OID` of the row that was inserted (which is currently always `0` in CockroachDB), followed by the number of rows inserted. #81943

- CLI commands that use a SQL connection (e.g., `cockroach sql` and `cockroach node status`) now support connecting with `PGPASSFILE` and `PGSERVICEFILE`. The behavior is compatible with how `libpq` (the psql C library) behaves. The `PGPASSFILE` file defaults to the filepath `~/.pgpass`, and has the format `hostname:port:database:username:password`, where the password field from the first line that matches the current connection parameters will be used to connect to the database. The `PGSERVICEFILE` file defaults to the filepath `~/.pg_service.conf`, and has the format:

      [myservice]
      host=somehost
      port=26257
      user=someuser

  - Any connection parameters (including `passfile` or `password`) can be specified in this file as well. Then, a connection string that specifies the `service=myservice` connection parameter will use the values in `PGSERVICEFILE` to connect. #82389

- CLI commands that use a SQL connection (e.g., `cockroach sql`, `cockroach node status`) now default to using the file in `~/.postgresql/root.crt` for the `sslrootcert` when connecting. The file can still be configured using the `PGSSLROOTCERT` environment variable or the `sslrootcert` URL parameter. #82389

- Using `COPY` in the SQL shell is now supported while inside an explicit transaction. #82101

- `CTRL+C` (the interrupt signal) can now be used in the CLI to attempt to cancel the currently executing SQL query. #82101

- `cockroach sql` (and thus `cockroach demo` too) now support the client-side commands `\o` and `\qecho`, like `psql`:

  - The `\o` command can redirect the output of SQL queries to a file.
  - The `\qecho` command adds arbitrary text to the current query output file. #83118

- CockroachDB now produces a clearer error when the path specified via `--socket-dir` is too long. #84532

- When the `--background` flag is specified, CockroachDB now makes three attempts to find a suitable directory to create the notification socket: the value of `--socket-dir` if specified, the value of `$TMPDIR` (or `/tmp` if the environment variable is empty), and the current working directory. If none of these directories has a name short enough, an explanatory error is printed. #84532

### API endpoint changes

- Added logic to support dropping unused index recommendations. #77642
- `ListSessions` now returns closed sessions in addition to open sessions. `ListSessionsRequest` now has a `exclude_closed_sessions` flag, which is a `BOOL` to exclude closed sessions. `serverpb.Session` now has `end` and `status` fields, which specify the time the session ended and the status (`opened`, `closed`) of the session, respectively. #78650
- Updated the `api/v2/rules` endpoint to include additional rules for events to alert on. #80274
- Added a new `last_auto_retry_reason` field under the `active_txn` field for a session to the `ListSessions` API. This field contains the string describing the retry reason or `nil` if none exists. This is also surfaced in the `crdb_internal.{cluster,node}_transactions` tables and in the output of the `SHOW TRANSACTIONS` statement under the `last_auto_retry_reason` column. #81531
- `serverpb.Session` now has three new fields: number of transactions executed, transaction fingerprint IDs, and total active time. #82352
- Added information about total bytes, live (non-MVCC) bytes and live (non-MVCC) percentage to the table details endpoint. #83677
- Added support for index recommendations to be returned on the statement details API. #85863

### DB Console changes

- Added index created time as an option on the DB Console Databases page. #78283
- Users can now see actively running queries and transactions in the SQL Activity page. The transactions and statements tabs in SQL activity now have a menu to show either active or historical transactions and statements data. #76753
- Added the last modified timestamp and coordinator ID to the Jobs page to aid in debugging jobs issues. #78501
- Added index recommendations to the Databases page for the Databases, Database Details, Database Table, and Index Details graphs. #79365
- Fixed resizing of tables on the Hot Ranges page. #80481
- Sessions Overview and Session Details pages now display closed sessions. The Sessions Overview Page now has username and session status filters. #80410
- The Learn more link on an empty transactions link now mentions transactions. #81530
- The Circuit Breaker Tripped events chart now displays the rate of events per interval instead of accumulated number of events. #81438
- The Jobs page now shows the oldest time (in UTC) that jobs are shown for. #81148
- The Cluster Overview page now displays a banner containing the previous versions of the cluster with a message `cluster_version - Mixed Versions` when a cluster runs nodes with different versions. #82118
- Fixed grammar on the mixed-version banner alert. #83150
- On the SQL Activity page, the selection to view historical or active executions will now persist between tabs. #83903
- The Active Statements and Active Transactions pages now have a single filter option for internal apps. These pages no longer display internal statements and transactions by default. #83014
- Added MVCC information to the tables list on the Databases page and on the Tables Details page. #84037
- Updated the Jobs Details page to a new design and added information about last execution time, next execution time, and execution count. #84498
- Updated the style in the Statement Details, Active Statement Details, Transaction Details, and Active Transaction Details summary component to be consistent with other existing styles. #84500
- Updated the time picker options to remove `"1"` on the hour and day options. #84510
- Added the Last Execution Time column to the SQL Activity overview, which allow users to sort by when queries were executed. This column is hidden by default. #84501
- Added `Range Key Bytes` and `Range Value Bytes` stats on the Node Details page. #85599
- A new section, Wait Time Insights has been added to the Active Statement and Transaction Details pages. The section is included if the transaction being viewed is experiencing contention and includes information on the blocked schema, table, index name, time spent blocking, and the transactions blocking or waiting for the viewed transaction. Only users having `VIEWACTIVITY` or higher can view this feature. The column Time Spent Waiting has been added to the active executions tables that shows the total amount of time an execution has been waiting for a lock. #85081
- The Explain Plans tab on Statement Details page now displays insights of index recommendations. #85863
- Added new Insights page to the DB Console. #84612
- Added the following fields to the Active Statement and Transaction Details pages:
  - Full Scan: indicates if the execution contains a full scan.
  - Last Retry Reason (Transactions page only): the last recorded reason the transaction was retried.
  - Priority (Transactions page only): the transaction priority. #85974
- The following fields have been added to the Sessions Overview page:
  - Transaction Count: the number of transactions executed by the session.
  - Session Active Duration: the time a session spent executing transactions.
  - Most recent Session fingerprint ids. #85974
- Removed the back to sessions link on Session Details "not found" page. #86050
- The statements and transaction fingerprint now refreshes data every 5 minutes for non-custom time ranges. #85772
- The `time spent waiting` columns for active execution tables has been hidden in CockroachDB Cloud. #86264
- Transactions and statements in active execution pages that are waiting for a lock will now have the status `Waiting`. #86329
- Added new Workload Insight Details page to the DB Console. #86325
- Added a button on the Statement Details page under the Explain Plan tab to perform the index recommendation directly from the DB Console. #86382
- Removed the `Next Planned Execution Time` label, when the job doesn't have a next planned execution scheduled. #86486
- Added a filter for live nodes based on `MembershipStatus` to resolve an issue where decommissioned nodes would in rare cases display as live in the DB Console. #86252
- Added new styles of summary cards on Session Details page to align with other details pages. #86572
- Added a link to the Explain Plan table linking to `EXPLAIN` documentation. #86581
- Surfaced paused replicas to Range Report, Problem Ranges, and Replication Metrics pages. #86407
- Changed the Plans table within the Explain Plan tab of the Statement Details page to use a plan gist instead of the plan ID. Also added the plan gist as the first line on the Explain Plan display. #86653
- Added clarification of the compression used to the tooltip of table size. #86821
- Changed the height of the SQL Box on Session Details, Active Transaction Details, Job Details, and Active Statement Details pages. #86812
- Added the new Schema Insights page to the DB Console, which displays a table of schema insights including different types of index recommendations (i.e., `DROP`/`CREATE`/`REPLACE` index recommendations). Each schema insight row offers the user the ability to execute the corresponding SQL query that realizes their schema insight via a clickable button. Filters are available to filter the surfaced schema insights by database and insight type, as well as search. #86317

### Bug fixes

- Fixed the insight execution priority to display the correct value instead of always being `default`. Changed the column to string to avoid converting it in the UI. #86901
- Fixed the `has_sequence_privilege()` built-in function checking on the `USAGE` privilege. #82458
- Fixed a bug where backups in the base directory of a Google Storage bucket would not be discovered by `SHOW BACKUPS`. These backups will now appear correctly. #80182
- Fixed an optimizer bug that prevented expressions of the form `(NULL::STRING[] <@ ARRAY['x'])` from being folded to `NULL`. #77995
- Fixed the implementation of the function `substr()` in the vectorized execution engine for UTF-8 encodings. #77308
- A lookup join on `pg_type.oid` no longer results in an error. Example: `SELECT pg_type.oid FROM (SELECT null::OID AS b) AS a INNER LOOKUP JOIN pg_type ON pg_type.oid=a.b`. #78960
- Previously, queries reading from an index or primary key on `FLOAT` or `REAL` columns `DESC` would read `-0` for every `+0` value stored in the index. Fixed this to correctly read `+0` for `+0` and `-0` for `-0`. #79473
- Previously, queries with many joins and projections of multi-column expressions, e.g., `col1 + col2`, either present in the query or within a virtual column definition, could experience very long optimization times or hangs, where the query is never sent for execution. This has now been fixed. #80212
- Previously, queries which involve an `ORDER BY` clause, a `DISTINCT ON` clause and a `GROUP BY` clause could sometimes error out depending on the columns referenced in those clauses. This is now fixed. #80447
- Updated the type reported in the wire protocol for `STRING(n)` types to match `VARCHAR(n)`. #80414
- Previously, creating a table with a locality of `REGIONAL BY ROW` could intermittently fail with a missing type error. This is now fixed. #80590
- `SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE` now works on tables with partial indexes. #80539
- Fixed a rare race condition that could allow for a transaction to serve a stale read and violate real-time ordering under moderate clock skew. #80706
- The hex encoding for `BYTEA` values now works properly when used in `COPY FROM ... CSV` statements. #81120
- Constants in SQL query fields are now correctly removed for `VIEWACTIVITYREDACTED` users. #80707
- Fixed a gap in disk-stall detection. Previously, disk stalls during filesystem metadata operations could go undetected, inducing deadlocks. Now stalls during these types of operations will correctly fail the process. #81389
- Fixed a bug that caused duplicated schema change job description messages. #81268
- Fixed false negatives produced by the `JSON` `?` operator when invoked on a `JSON` array with the vectorized engine set explicitly to `off`. #81648
- Fixed a bug where sequences could return values that are out-of-bounds in some cases. #81123
- Fixed the formatting of floats in arrays and tuples sent over the client-server pgwire protocol so that they respect the `extra_float_digits` parameter, and correctly format infinity values. #82022
- The `DateStyle` session setting is no longer ignored using the CLI when set in the `options` URL parameter. #82101
- Previously, dropping tables with foreign key dependencies would generate the wrong `pgcode` (`Uncategorized` versus `DependentObjectsStillExist`). This is now fixed. #80142
- Previously, if a foreign key was concurrently added while the referenced table was dropped before validation was completed, CockroachDB could potentially hang on the rollback. Now, CockroachDB will generate appropriate errors when the referenced table is dropped and gracefully rollback the change. #80142
- Views are no longer allowed to reference types that are defined in different databases. Even though this was allowed at view-creation time previously, it would cause errors, since cross-database type references are not supported. #82763
- `CREATE TABLE AS` in explicit transactions would fail with an error if the size of the source table exceeded the Raft command size limit. #82951
- Range lease transfers are no longer permitted to follower replicas that may require a Raft snapshot. This ensures that lease transfers are never delayed behind snapshots, which could previously create range unavailability until the snapshot completed. Lease transfers are now only allowed when the outgoing leaseholder can guarantee that the incoming leaseholder does not need a snapshot. #82758
- Fixed a bug where creating a unique, expression index on a `REGIONAL BY TABLE` could result in an error. #83125
- Fixed a bug where in rare cases a stale read could be returned. This is fixed by introducing a new in-memory field to a `LeaseStatus`, which is when it most recently acquired data to a different store. Any uncertain observed timestamps before this time are ignored. #83345
- Previously, the `querySummary` metadata field in the `crdb_internal.statement_statistics` table was inconsistent with the query metadata field for executed prepared statements. These fields are now consistent for prepared statements. #83673
- Fixed a bug where `BACKUP` may be missing data when the cluster was configured with very low values for `kv.bulk_sst.max_allowed_overage` and `kv.bulk_sst.target_size` cluster settings. #83102
- Fixed an issue where some exports would receive `"unexpected closure of consumer"` rather than the actual error the export encountered. #77938
- Fixed a bug causing a graceful node shutdown to stall forever. #83824
- The `PASSWORD` option of the `CREATE`/`ALTER ROLE` commands now requires the password to be surrounded with single quotes. This fixes confusion that could arise when a mixed-case string is used, since previously that would cause the password to be normalized to lowercase. #83924
- Fixed a bug causing the `row_to_json` SQL function to error out when used with input having the `VOID` data type. #83876
- The Active Transactions page no longer shows transactions from closed sessions. #83896
- Fixed a bug that could cause an optimizer panic in rare cases when a query had a left join in the input of an inner join. #83875
- `DROP SCHEMA ... CASCADE` in the legacy schema changer now correctly fails when a backup, restore, or an import of an underlying table or type is concurrently underway. #84189
- `DROP ... CASCADE` of a database or a schema in the declarative schema changer now correctly fails when a when a backup, restore, or an import of an underlying table or type is concurrently underway. #84189
- Fixed a bug that caused internal errors in rare cases when performing `DELETE`s on a table that had foreign key references to it with the `ON DELETE CASCADE` option. For example, imagine tables `a` and `b` already exist, and `b` has a foreign key `ON DELETE CASCADE` column referencing `a`. If table `c` is added with a foreign key `ON DELETE CASCADE` column referencing table `b` and a `DELETE` statement is performed on table `a` in the same transaction, an internal error could occur. This bug has been present since v21.1.0. #84219
- Fixed a bug that could corrupt indexes and cause incorrect query results with `INTERVAL` values greater than about `290` years or less than about `-290` years. #84045
- Fixed a bug that led to the `querySummary` field in the `crdb_internal.statements_statistics` metadata column being empty. #84170
- Previously, CockroachDB could deadlock when evaluating analytical queries if multiple queries had to spill to disk at the same time. This is now fixed by making some of the queries error out instead. #84398
- Fixed a bug where an ephemeral I/O error could crash a node. #84449
- Fixed a bug where, in an `ALTER PRIMARY KEY` statement, if the new primary key columns is a subset of the previous primary key columns, CockroachDB would not rewrite existing secondary indexes, and hence those secondary indexes continue to have some of the previous primary key columns in their `suffixColumns`. But the user might, reasonably think those columns are not used anymore and proceed to drop them. The bug then caused those dependent secondary indexes to be dropped, unexpectedly for the user. #84303
- Fixed a bug where the CLI `cockroach` commands could produce spurious `"latency jump"` warnings when connecting to a remote server. This bug had been introduced in CockroachDB v21.2. #84031
- Fixed vectorized evaluation of `COALESCE` when involving expressions of type `VOID`, and enhances type checking of `NULLIF` expressions with `VOID`, so incompatible comparisons can be caught during query compilation instead of during query execution. #83868
- In the DB Console, changing the time window using arrow buttons and the Now button will now properly turn the timeframe into a moving window when `endTime = now`. #84649
- The `cockroach` process no longer announces that it is shutting down to `stdout` when running with the `--background` flag. #84532
- The `public` role can no longer be granted default privileges with the `grant` option. This was a bug because the `public` role already cannot have the grant option on regular privileges. #85027
- Fixed a bug where CockroachDB should initialize a `schemaChangerState` of `connExecutor` from the corresponding session variable (`use_declarative_schema_changer`), which can cause DDL statements to be executed under the legacy schema changer unknowingly. #85344
- When a CockroachDB node is being drained, all queries that are still running on that node are now forcefully canceled after waiting the `server.shutdown.query_wait` period. #82752
- The SQL Unix socket, when requested, now contains a port number compatible with the connection URL when `--listen-addr` is configured to auto-allocate a port number. This bug had existed since CockroachDB v1.0. #84910
- Previously, if a Unix socket was requested but it already existed on disk, CockroachDB would exit with an error even if the original owner process was not running. This limitation would, for example, prevent reuse of a Unix socket after an abnormal shutdown. It had been present since CockroachDB v1.0. This is now fixed. #84910
- Fixed a panic when loading tenant HTTP endpoints for statement statistics. #85407
- The `crdb_internal.range_statistics` function now uses a vectorized implementation that allows the lookup of range metadata to occur in parallel. #85442
- Fixed a bug where `EXECUTE` did not accept placeholder arguments if the type did not exactly match. #85861
- Fixed a bug where, in a stage of validation operations in the declarative schema changer, only the first validation operation is properly handled and the rest are skipped. #85781
- Fixed a bug internal to drawing dependency graph of a DDL statement under the declarative schema changer. #85773
- Fixed a rare bug where errors could occur related to the use of arrays of type `ENUM`. #85940
- CockroachDB now more precisely respects the `distsql_workmem` setting, which improves the stability of each node and makes out-of-memory issues less likely. #85440
- Fixed a bug in post deserialization changes where CockroachDB might incorrectly change constraint ID of a constraint that lives in the mutation slice of a table descriptor. #85778
- Active Execution pages will no longer crash if there are no filters set in local settings. #86139
- Fixed a bug where an incorrect parameter name for database was used in the SQL API. The correct parameter name `database` is now used. #86169
- The statements and transaction fingerprint will no longer get stuck on the loading page in the CockroachDB Cloud Console after 5 minutes idling on the page. #85772
- Intersection spatial operations could previously return incorrect results on the ARM processor. This is now resolved. #86126
- Sequence integer bounds are now consistent with the cluster setting `default_int_size`. #84555
- Users that create an external connection are now granted `ALL` privileges on the object. #86336
- Fixed a vulnerability in the optimizer that could cause a panic in rare cases when planning complex queries with `ORDER BY`. #86193
- Fixed a bug in backup where spans for views were being backed up. Because ranges are not split at view boundaries, this can cause the backup to send export requests to ranges that do not belong to any backup target. #85158
- Previously, `SET SESSION AUTHORIZATION DEFAULT` would have no effect. Now, it causes the current role to be reset to the original user who logged into the session. #86485
- Fixed a bug with Search in the Active Execution Overview pages, where providing a search string did not properly filter out statements and transactions that do not contain the search string. #86764
- Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions `st_makeline` or `st_extent` were called with invalid-type and empty inputs respectively. #86722
- Fixed a crash that could occur when formatting queries that have placeholder `BitArray` arguments. #86607
- Fixed a crash/panic that could occur if placeholder arguments were used with the `with_min_timestamp()` or `with_max_staleness()` functions. #86605
- Fixed a bug that caused some special characters to be misread if `COPY ... FROM into a TEXT[]` column was reading them. #86712
- Previously, CockroachDB would return an internal error when evaluating the `json_build_object()` built-in when an `ENUM` or `VOID` data type was passed as the first argument. This is now fixed. #86675
- Rollback of materialized view creation left references inside dependent objects. This fix adds clean up to the back/forward references for materialized views. #82087
- User-defined functions are disallowed in any expressions (column, index, constraint) in tables. #85718

### Performance improvements

- Performance of inner, semi, or anti joins between two tables with `OR`ed equi-join predicates is improved by enabling the optimizer to select a join plan in which each equi-join predicate is evaluated by a separate join, with the results of the joins as a union or intersected together. #74303
- Expressions using the overlaps (`&&`) operator for arrays now support index-acceleration for faster execution in some cases. #77418
- Improved the ability of the optimizer to detect contradictions in filter conditions of the form `x IS NULL` when `x` can never be `NULL`. This enables the optimizer to simplify query plans. #80211
- Added per-span checkpointing to cases when the high-water mark lags excessively behind the leading edge of the frontier in order to avoid re-emitting the majority of spans due to a small minority that is experiencing issues progressing. This helps to enable changefeeds to operate on very large tables when performing large catchup scan. #77763
- The optimizer cost model is now more aware of the cost of executing expensive functions (such as spatial functions) in filter conditions. This may lead to improved query plans. #81924
- Changefeed catchup scans now use time-bound iterators, which improves their performance by avoiding accessing data that is outside the catchup scan time interval. Previously, this was controlled by the `kv.rangefeed.catchup_scan_iterator_optimization.enabled` cluster setting, which defaulted to `off`. This change removes this cluster setting, as its functionality is in effect now always enabled. #82450
- The optimizer now explores more efficient query plans when indexing computed columns and expressions that have `IS NULL` expressions. #83619
- The optimizer can now return the results of a join in sorted order in more cases. This can allow the optimizer to avoid expensive sorts that need to buffer all input rows. #84689
- The optimizer is now less likely to take an excessive amount of time to plan queries with many joins. #85100
- The optimizer can detect contradictory filters in more cases, leading to more efficient query plans. #85351
- The row-level TTL job has been modified to distribute work using DistSQL. This usually results in the leaseholder nodes managing deletes of the spans they own. #84728
- The execution engine can now short-circuit execution of lookup joins in more cases, which can decrease latency for queries with limits. #85731
- `ILIKE` and `NOT ILIKE` filters can now be evaluated more efficiently in some cases. #85695
- MVCC garbage collection should now be much less disruptive to foreground traffic than previously. #83213
- The execution engine can now perform lookup joins in more cases. This can significantly improve join performance when there is a large table with an index that conforms to the join `ON` conditions, as well as allow joins to halt early in the presence of a limit. #85597
- Point deletes in SQL are now more efficient during concurrent workloads. #63416
- Enabled table statistics forecasts, which predict future statistics based on historical collected statistics. Forecasts help the optimizer produce better plans for queries that read data modified after the latest statistics collection. CockroachDB only uses the forecasts that fit the historical collected statistics very well, meaning it has high confidence in their accuracy. Forecasts can be viewed using `SHOW STATISTICS FOR TABLE ... WITH FORECAST`. #86078
- Optimized the execution of `COPY FROM`. #83840
- Long-running SQL sessions are now less likely to maintain large allocations for long periods of time, which decreases the risk of OOM and improves memory utilization. #85949
- SQL statements that cause events to be logged to `system.eventlog` are now able to complete faster. #86174
- Planning time has been reduced for queries over tables with a large number of columns and/or indexes. #86606
- Introduced the `kv.log_range_and_node_events.enabled` cluster setting to disable transactionally logging range events (e.g., merges, splits, and rebalancing) and node join and restart events to system tables, to remove the dependency on such tables and improve performance. #85593
- The default L0 sub-level enforcement for rebalancing and allocation decisions is now set to `block_rebalance_to`. This has the effect of stopping rebalancing to stores that have high read amplification. #79794
- Changed the MVCC garbage collection queue to recompute MVCC statistics on a range, if after doing a garbage collection run it still thinks there is garbage in the range. #83194

### Build changes

- Upgraded to Go 1.18.4. #84590
- Build experimental Linux ARM64 binary. #86043

### Contributors

This release includes 2637 merged PRs by 141 authors. We would like to thank the following contributors from the CockroachDB community:

- Eng Zer Jun (first-time contributor)
- Farye Nwede (first-time contributor)
- Frediano Ziglio (first-time contributor)
- Frédéric BIDON (first-time contributor)
- Nathan Lowe (first-time contributor)
- Prashant Khoje (first-time contributor)
- Rajiv Sharma (first-time contributor)
- Tim Graham
- changhan (first-time contributor)
- dandotimujahid (first-time contributor)
- likzn (first-time contributor)
- lyubomirkyuchukov (first-time contributor)
- mosquito2333
- nnaka2992 (first-time contributor)