What's New in v22.2

On this page Carat arrow pointing down
Warning:
CockroachDB v22.2 is no longer supported as of June 5, 2024. For more details, refer to the Release Support Policy.

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

Get future release notes emailed to you:

To upgrade to v22.2, see Upgrade to CockroachDB v22.2.

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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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

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 ORed 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 RESTOREs 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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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

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 LIMITs 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

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

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

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:

The 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:

icon/buttons/copy
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

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 JOINs 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 dlopening 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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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

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

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

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 COPYing 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:

The 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:

icon/buttons/copy
docker pull cockroachdb/cockroach:v22.2.3

Changelog

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

Backward-incompatible changes

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 v22.2/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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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

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:

The 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:

icon/buttons/copy
docker pull cockroachdb/cockroach:v22.2.0

Changelog

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

CockroachDB Cloud

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

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:

The 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:

icon/buttons/copy
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

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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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:

The 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:

icon/buttons/copy
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 BYstatement 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:

The 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):

icon/buttons/copy
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

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:

The 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):

icon/buttons/copy
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:

The 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):

icon/buttons/copy
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:

The 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):

icon/buttons/copy
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:

The 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):

icon/buttons/copy
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 DATABASE 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

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:

The 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):

icon/buttons/copy
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

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

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:

The 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):

icon/buttons/copy
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

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:

The 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):

icon/buttons/copy
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 REGIONstatement. 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 ReadWithinUncertaintyIntervalErrors 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 DELETEs 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 ORed 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)

Yes No
On this page

Yes No