What's New in v22.2

On this page Carat arrow pointing down

Get future release notes emailed to you:

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

v22.2.3

Release Date: January 23, 2023

Downloads

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. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach: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

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. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach: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

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. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach: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
  • SQL tenants now support the HTTP endpoint under /api/v2/sql, which allows the caller to execute an HTTP request containing SQL statements to execute, and returns the results in the JSON response. This endpoint works identically as on a non-tenant server, except that it naturally scopes to the target tenant for SQL execution. #91994
  • 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

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. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach: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 a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the next version of CockroachDB. Instead, make one of the following configuration changes to your CockroachDB deployment:
    • 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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

Multi-platform images include support for both Intel and ARM. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

Multi-platform images include support for both Intel and ARM. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

Multi-platform images include support for both Intel and ARM. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

Multi-platform images include support for both Intel and ARM. CockroachDB on ARM systems is experimental and is not yet qualified for production use. Multi-platform images do not take up additional space on your Docker host.

To download the Docker image (multi-platform):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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](../v22.2/vectorized-execution.html) 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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

Docker image

To download the Docker image (Intel-only):

icon/buttons/copy
$ docker pull cockroachdb/cockroach-unstable: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

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.

Warning:
  • Testing releases are intended for testing and experimentation only. Testing releases are not recommended for production use, as they can lead to data corruption, cluster unavailability, performance issues, or other problems.
  • Experimental downloads are not qualified for production use, whether they are for testing releases or production releases.

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 a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the next version of CockroachDB. Instead, make one of the following configuration changes to your CockroachDB deployment:
    • 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 phoning home 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