What's New in v23.1

On this page Carat arrow pointing down

Get future release notes emailed to you:

To upgrade to v23.1, see Upgrade to CockroachDB v23.1.

v23.1.2

Release Date: May 25, 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:v23.1.2

Changelog

View a detailed changelog on GitHub: v23.1.1...v23.1.2

Note:

This is an incomplete list of the changes included in the v23.1.2 release. The full list will be published soon.

Bug fixes

  • A bug was fixed where, under high CPU load, HTTP requests to certain API endpoints such as the health endpoint could start failing and then never succeed again until the node was restarted. This bug was introduced in v23.1. #103775

Contributors

This release includes 329 merged PRs by 70 authors.

v23.1.1

Release Date: May 16, 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:v23.1.1

Changelog

View a detailed changelog on GitHub: v23.1.0...v23.1.1

Bug fixes

  • In CockroachDB v23.1.0 and its testing releases, executing COPY to a target table that has multiple column families could corrupt the table. If data was copied into a table with existing rows, the data in those rows may be irrecoverable. The data would need to be dropped and re-copied to be encoded correctly. This has now been fixed. See Technical Advisory 103220 for more information. #103323

Contributors

This release includes 2 merged PRs by 2 authors.

v23.1.0

Release Date: May 15, 2023

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

Downloads

Warning:

This patch release has been withdrawn due to this technical advisory. All the changes listed as part of this release will be in the next release. Do not upgrade to this release.

Docker image

Warning:

This release was withdrawn, and we've removed the links to the downloads and Docker image.

Changelog

View a detailed changelog on GitHub: v23.1.0-rc.2...v23.1.0

Feature highlights

This section summarizes the most significant user-facing changes in v23.1.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 v23.1 in our docs.

Note:

The features highlighted below are freely available in CockroachDB Self-Hosted and do not require an enterprise license to test enterprise features in a local, temporary cluster.

SQL

Queries
Feature Description

Full-text search using TSVector and TSQuery

A full-text search is used to perform queries on natural-language documents such as articles, websites, or other written formats, with results often sorted by relevance.

You can rely on new built-in functions to make use of the new TSVECTOR and TSQUERY data types.

Improved developer experience for multi-region apps

If you have functionality that requires low latency and cannot tolerate delays between regions, you can enable the enforce_home_region option, which ensures that queries are executed within a single region. If a query doesn't have a home region or is running outside of its home region, the optimizer now provides improved feedback and suggestions for executing the query within a single region.

Streamline migrations with improved COPY performance

Enhancements to the functionality behind COPY statements resulted in 2x faster migrations using AWS DMS.

Redact PII from statement bundles

Leverage statement bundles for debugging without introducing data privacy concerns. You can now redact personally identifiable information (PII) from statement bundles for PCI compliance.

User-Defined Function (UDF) enhancements

User-defined functions offer enhanced flexibility, performance, and reusability. This release brings a number of UDF enhancements, including: Inlining of supported UDFs within the query plan to improve performance; support for subqueries in statements, support for expressions with a * such as SELECT * , and support for returning a set of results (using SETOF).

UDFs can now also be used in Changefeed expressions (Enterprise) and CHECK constraints, and referenced from other objects. Validations have been added to guarantee that all statements in the function body should be as strict as the expected UDF volatility. UDFs are also now included in backup and restore operations.

DELETE FROM ... USING

We have added support for the USING clause on DELETE, which allows joining multiple tables for a DELETE clause. This change is in line with PostgreSQL functionality and extends the flexibility of DELETE.

Schemas
Feature Description

Support user-defined composite types

You can now create your own enumerated data types.

For example:

CREATE TYPE t AS (a INT, b INT);
CREATE TABLE a (a t);
INSERT INTO a VALUES((1, 2))
SELECT (a).b FROM a

Declarative schema changer supports user-defined functions UDFs

The statements CREATE FUNCTION and DROP FUNCTION are now supported by the declarative schema changer.

Declarative schema changer fully supports constraints

The statements ALTER TABLE...ADD CONSTRAINT and ALTER TABLE...DROP CONSTRAINT are now supported by the declarative schema changer.

Add configurable setting to adjust grant lease options

A new session variable allow_role_memberships_to_change_during_transaction has been introduced which, when true, will make granting and revoking of role memberships faster at the cost of allowing some in-progress transactions to observe the previous role membership.

By default, when granting or revoking a role from another role, the system waits until all transactions that are consulting the current set of role memberships to complete. This is done to preserve CockroachDB’s default isolation level. However, the downside of this wait is that grant and revoke will take longer than the longest currently executing transaction.

In some cases, you may not care about whether concurrent transactions will immediately see the side-effects of the role grant or revoke operation, but would instead prefer that the operation finish quickly.

For more information about this setting and how it works, see the

Limitations

section of the GRANT documentation.
Sessions
Feature Description

New SQL shell editor with tab completion

The SQL shell has a new user interface that allows tab completion and more advanced navigation, streamlining developer workflows.

After pressing tab, you can navigate database objects, keywords, and functions using arrow keys, pressing tab again to select one and return to the console. You can also use pattern matching to filter these entities and find what you need faster.

Support multiple active portals (Preview)

The multiple active portals feature of the Postgres wire protocol (pgwire) is available in CockroachDB, with limitations. This allows for more efficient data retrieval by reducing the number of roundtrips required between the client and server.

Third-party tools such as asyncpg use this feature to implement efficient asynchronous communication between the PostgreSQL server and client. This can allow for faster, more scalable applications that can handle large amounts of data without slowing down the user experience.

Full support for asyncpg

CockroachDB now offers full support for asyncpg, a PostgreSQL database interface library designed specifically for Python's asyncio framework. It provides an efficient implementation of the PostgreSQL server binary protocol for high-performance asynchronous database applications.

Asyncpg is commonly used with ORM libraries such as SQLAlchemy to provide a simple, flexible, and efficient adapter for working with PostgreSQL databases. This makes it ideal for handling large volumes of data and scaling applications to meet demanding performance requirements.

Security and compliance

Feature Description

Improvements to the redaction of data in observability artifacts

We have made a number of improvements to ensure that sensitive data can be redacted in observability artifacts produced by CockroachDB, such as debug.zip and statement bundles. These improvements, available to all Self-Hosted customers, also help Cockroach Labs to comply with PCI DSS in CockroachDB Dedicated.

FIPS-ready CockroachDB binaries

FIPS-ready binaries and Docker images are available for CockroachDB 23.1.0 and above. Federal Information Processing Standards (FIPS) 140-2 is a standard used to approve cryptographic modules by the U.S. and Canadian governments for systems maintained by relevant agencies and organizations working with them for the purposes of encrypting and decrypting sensitive data.

FIPS-ready CockroachDB binaries are designed for workloads that require FIPS 140-2. FIPS-ready CockroachDB delegates cryptographic operations to the OpenSSL library available on the host operating system, rather than Go's cryptographic libraries. We recommend that OpenSSL has a FIPS 140-2 certificate. FIPS mode must be enabled in the Linux kernel to ensure that FIPS 140-2 is enforced by the operating system.

Support Oauth authentication protocol for changefeeds

See this item in Change Data Capture (Changefeeds).

Support encrypted backups with keys stored in Azure Key Vault

You can now take and restore encrypted backups using RSA keys stored in Azure Key Vault.

Expand on External Connections for Changefeeds

See the Changefeeds section for more information.

New fine-grained system privilege to view all jobs

The new VIEWJOB system privilege allows a user to view all jobs when running commands like SHOW JOBS, without granting additional capabilities. This helps ensure alignment with the principle of least privilege by removing the need to assign broader privileges or role options like CONTROLJOB.

Recovery and I/O

Change data capture (Changefeeds)
Note:

The following are enterprise-only features. Request a 30-day trial license to try them out.

Feature Description

Create scheduled exports using changefeeds

Changefeeds can offer benefits over existing export functionality for customers who need higher levels of scale and observability. You can now create Changefeeds as Export functionality.

Use a webhook as a changefeed sink

The use of a webhook sink to deliver changefeed messages to an arbitrary HTTPS endpoint has been promoted from Preview to GA.

CDC Queries

CDC Queries (formerly CDC Transformations) have been promoted from Preview to GA.

Use External Connections (GA) to remove a data exfiltration vector

Use external connections to specify and interact with resources that are external from CockroachDB. With CREATE EXTERNAL CONNECTION, you define a name for an external connection while passing the provider URI and query parameters. BACKUP, RESTORE, IMPORT, EXPORT, and CREATE CHANGEFEED queries can interact with the defined external connection instead of a required, provider-specific URI. As a result, you can decouple the management and permissions of the external resource from the operation in which you're using them.

With the move from Preview to GA, this feature brings many new capabilities, such as fine-grained permission and support for schema registries, webhook and GC PubSub sinks, and the SHOW command.

Changefeed locality

CREATE CHANGEFEED now accepts a WITH execution_locality option to restrict execution of the changefeed process to nodes within the specified locality filter.

Improved changefeed resilience

Changefeeds are more stable as the result of improved error handling. Changefeeds now default to retrying requests when encountering any error, except those deemed terminal.

Support Oauth authentication protocol for changefeeds

Oauth authentication uses a third-party software provider to authenticate with Kafka instead of providing CockroachDB with direct access to Kafka cluster credentials. The third-party authentication server provides a temporary credential token that CockroachDB then uses to connect to a customer’s Kafka cluster. This represents a security best practice, allowing users to authenticate without directly storing or sharing their credentials.

Disaster Recovery
Feature Description

Support longer incremental backup chains

We now support up to 400 incremental backups, an 8x increase, enabling you to preserve your data even more effectively (reducing RPO) while being more cost-efficient. Incremental backups contain only the data that has changed since the last backup, so they are smaller and faster to produce.

Restrict backups to a locality

You can now [restrict backup execution](take-locality-restricted-backups.html) to a specific region/locality. Only the nodes in the specified locality filter will execute the backup, so only those nodes will need access to the backup storage bucket.

If the node executing the backup does not have a specific range to be backed up, it will read it from the closest replica it can.

Support implicit authentication on Azure

You can now use implicit authentication on Azure for cloud storage authentication.

If the node executing the backup does not have a specific range to be backed up, it will read it from the closest replica it can.

Support encrypted backups with keys stored in Azure Key Vault

You can now take and restore encrypted backups using RSA keys stored in Azure Key Vault.

Enforce supported backup versions

To help ensure backups and restores are successful, CockroachDB now enforces its previous support for restoring backups from up to two minor versions prior. Previously, restoring backups produced from even earlier versions was possible, but unreliable. Now, this operation is prevented with an error.

Database Operations

Observability
Feature Description

Key Visualizer (Preview)

Using a visual heatmap of the latest historical range activity across the cluster, you can quickly identify hot spots and full table scans, enabling you to target ranges for performance investigations.

Enhanced Intelligent Insights experience includes transaction-level insights and metrics

We have expanded the Insights section of the Console, offering improved discoverability and data for tuning and optimizing your workload. Contention insights reveal the waiting statement for cases where blocking conflicts occur. Transaction-level insights help you identify impacted areas of your application and prioritize your investigations, enabling you to drill down to individual statements with additional insights such as suboptimal plans.

Enhanced statement metrics

Easily correlate high-level cluster metrics (e.g., CPU, latency, etc.) with per-statement CPU utilization, latency metrics (P50, P90, P99, min, max), idle/client wait time, and MVCC garbage statistics.

Faster performance and an enhanced UX for the SQL Activity pages

Reliably and quickly find SQL activity information using a new interactive ‘Search Criteria’ capability in the console.

Supplement troubleshooting flows with additional observability into indexes used per statement

Observability information is available to correlate statements (and their plans) to indexes. You can now map index usage statistics with statements and transactions which streamlines troubleshooting flows such as dropping infrequently used indexes, creating or updating table statistics, reducing MVCC garbage, and alleviating resource hot spots.

KV Layer
Feature Description

Decommission Pre-Flight Checks

Decommissioning operations now check that each replica on a node that is slated to be decommissioned can be moved to another node.

Any ranges that are not yet fully upreplicated will block the decommission process.

When errors are detected that would prevent successful decommissioning,, the errors are printed to STDERR and the decommissioning command exits.

Delegated Snapshots: Send Raft snapshots between follower replicas

Delegated Snapshots make multi-region deployments more cost-efficient by decreasing the use of remote snapshots if there is a local snapshot with the data.

Sending data locally reduces your network costs and frees up the WAN bandwidth for the important data that must be transferred

Previously, customers with multi-site deployments paid more for network bandwidth than strictly necessary due to system operations that required snapshots. Additionally, because of congested TCP channels between regions, transferring snapshots could negatively impact user traffic and slow snapshot transfers.

Delegated Snapshots fixes this problem by sending snapshots from a local replica whenever a close-by replica exists and isn’t busy.

Faster leaseholder recovery

The default CockroachDB lease duration has been reduced from 9 seconds to 6 seconds, to reduce range unavailability following leaseholder loss. Some other related settings have also had their defaults reduced, including heartbeat intervals, Raft election timeouts, and network timeouts.

Lower default TTL for garbage collection (GC)

The default GC TTL value in 23.1.0 is being reduced from 25 hrs to 4 hrs for new clusters.

This change is being made to improve read performance, storage utilization, and cluster stability in high write traffic scenarios. Scheduled backups will not be affected by this change as protected timestamps will ensure data isn't garbage-collected until it has been backed up. Changefeeds will also not be affected. If you want a 25-hour or larger GC TTL value (for example, to support AS OF SYSTEM TIME queries that go further back in time), you can explicitly set GC TTL to the desired value.

This change will only apply to new clusters. Existing clusters will retain the 25-hour default when upgrading, unless you have previously overridden it with an explicit value, in which case, that value will be retained. Backups taken from clusters running versions prior to v23.1 will similarly retain the GC TTL configured when the backup was taken.

Backward-incompatible changes

Before upgrading to CockroachDB v23.1, be sure to review the following backward-incompatible changes, as well as key cluster setting changes, and adjust your deployment as necessary.

  • Replaced the cdc_prev() function in favor of a cdc_prev tuple. This is an incompatible change that may break changefeeds that use the previous cdc_prev() function. #85177
  • SHOW RANGES FOR TABLE now includes rows for all indexes that support the table. Prior to this change, SHOW RANGES FOR TABLE foo was an alias for SHOW RANGES FOR INDEX foo@primary. This was causing confusion, as it would miss data for secondary indexes. It is still possible to filter to just the primary index using SHOW RANGES FOR INDEX foo@primary. The statement output now also includes the index name. #93545
  • CockroachDB now supports sharing storage ranges across multiple indexes/tables. As a result, there is no longer a guarantee that there is at most one SQL object (e.g., table/index/sequence/materialized view) per storage range. Therefore, the columns table_id, database_name, schema_name, table_name and index_name in crdb_internal.ranges and .ranges_no_leases have become nonsensical: a range cannot be attributed to a single table/index anymore. For the v23.1 release, the default behavior of SHOW RANGES is retained, but users should consider setting the cluster setting sql.show_ranges_deprecated_behavior.enabled to false. This will have the following effects that will become the defaults in a future release:

    • The aforementioned columns in the crdb_internal virtual tables have been removed. Existing code can use the SHOW RANGES statement instead, optionally using WITH KEYS to expose the raw start/end keys.
    • SHOW RANGES FROM DATABASE continues to report one row per range, but stops returning the database / schema / table / index name.
    • SHOW RANGES FROM TABLE continues to report one row per range, but stops returning the index name. Suggested replacements:
      • Instead of: SELECT range_id FROM crdb_internal.ranges WHERE table_name = 'x', use: SELECT range_id FROM [SHOW RANGES FROM TABLE x]
      • Instead of SELECT range_id FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2 (variable / unpredictable table name or ID), use: SELECT range_id FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES] WHERE table_name = $1 OR table_id = $2
      • Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = 'x', use: SELECT raw_start_key FROM [SHOW RANGES FROM TABLE x WITH KEYS]
      • Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2 (unpredictable / variable table name or ID), use: SELECT raw_start_key FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES, KEYS] WHERE table_name = $1 OR table_id = $2 #93644
  • When the cluster setting sql.show_ranges_deprecated_behavior.enabled is set to false (recommended in v23.1), the format of the columns start_key and end_key for SHOW RANGES FROM DATABASE and SHOW RANGES FROM TABLE have been extended to include which table/index the key belongs to. This is necessary because a range can now contain data from more than one table/index. #93644

  • When the cluster setting sql.show_ranges_deprecated_behavior.enabled is set to false (recommended in v23.1), the output of SHOW RANGES no longer includes range_size, range_size_mb, lease_holder, or lease_holder_localities by default. This ensures that SHOW RANGES remains fast in the common case. Use the new option WITH DETAILS to include these columns. #93644

  • The format of the columns start_key and end_key for SHOW RANGE ... FOR ROW has been changed to be consistent with the output of SHOW RANGES FROM INDEX. #93644

  • If a SQL database is created with a name that starts with cluster:... (e.g., CREATE DATABASE "cluster:foo", clients will no longer be able to connect to it directly via a pre-existing URL connection string. The URL will need to be modified in this case. For example:

    • Previously: postgres://servername/cluster:foo; now: postgres://servername/cluster:foo?options=-ccluster=system
    • This syntax selects the tenant named system and then the database named cluster:foo inside it. When the -ccluster:system option is not specified, cluster:foo in the database name position is interpreted as a request to connect to a tenant named foo, which likely does not exist. Connections to databases whose name does not start with cluster: (the most common case) are not affected by this change. #92580
  • Changefeeds using "preview" expressions (released in v23.1.0) and that access the previous state of the row using the cdc_prev() function will no longer work and will need to be recreated with new syntax. #94429

  • Some of the transformations specific to changefeeds have been deprecated and replaced. These functions were released in limited access in v22.2. Deprecated changefeed transformations continue to function. Closely monitor changefeeds that are created during upgrade. While effort was made to maintain backward compatibility, the updated changefeed transformation may produce slightly different output, such as different column names. #96295

  • Fixed a bug where, when server.identity_map.configuration was used, CockroachDB did not verify the client-provided username against the target mappings. Note that this means that the client must now provide a valid DB username. This requirement is compatible with PostgreSQL; it was not previously required by CockroachDB but it is now. This does not apply when identity maps are not in use. #94915

  • Previously, the type of the replicas, voting_replicas,non_voting_replicas and learner_replicas in crdb_internal.ranges were overridden to INT2VECTOR causing incompatible indexing between .ranges and .ranges_no_leases. Now the types of those columns in the two tables are set to INT[]. #96287

  • The output of the SHOW RANGES command for the crdb_internal.ranges and crdb_internal.ranges_no_leases tables has been updated, and the previous output is deprecated. To enable the new command output, set the sql.show_ranges_deprecated_behavior.enabled cluster setting to false. The new output will become default in v23.2. #99618

  • Previously, if a user specified a search_path in the connection string parameters, it would always be treated as case sensitive. Now, in order to have the schema names in the search_path respect case, the user must include double quotes around the name. #101492

  • The deprecated CLI command debug unsafe-remove-dead-replicas has been removed. Use debug recover instead. #89150

Key Cluster Setting Changes

The following changes should be reviewed prior to upgrading. Default cluster settings will be used unless you have manually set a value for a setting. This can be confirmed by checking the system.settings table (select * from system.settings) to view the non-default settings.

Category Description Change Type Backport version
SQL language change The cluster setting sql.ttl.default_range_concurrency and table storage parameter ttl_range_concurrency are no longer configurable. #89392 No longer configurable v22.2.1
SQL language change The sql.distsql.max_running_flows cluster setting has been removed. #84888 Removed None
Operational change The cluster settings server.web_session.purge.period and server.web_session.purge.max_deletions_per_cycle, which were specific to the cleanup function for system.web_sessions, have been replaced by server.log_gc.period and server.log_gc.max_deletions_per_cycle which apply to the cleanup function for system.eventlog, system.rangelog and system.web_sessions equally. #90789 Removed, repurposed None
Operational change The cluster setting server.web_session.auto_logout.timeout has been removed. #90789 Removed, defaults to true None
Operational change The load-based splitter now supports using request CPU usage to split ranges. This is introduced with the previous cluster setting kv.allocator.load_based_rebalancing.objective, which when set to cpu, will use request CPU usage. The threshold above which CPU usage of a range is considered for splitting is defined in the cluster setting kv.range_split.load_cpu_threshold, which has a default value of 250ms. (Relates to #100211 in this table.) #96128 Repurposed None
Operational change The kv.range_split.load_cpu_threshold cluster setting now has a minimum setting value of 10ms. Previously there was no minimum so, while unlikely, this could have an impact if you had chosen a custom setting lower than the established minimum. #98250 New minimum None
Security update The new cluster setting server.user_login.downgrade_scram_stored_passwords_to_bcrypt.enabled, which allows you to migrate passwords from SCRAM to bcrypt during user authentication, defaults to true. If it is true and if server.user_login.password_encryption is crdb-bcrypt, then during login, the stored hashed password will be migrated from SCRAM to bcrypt. #97429 New setting v22.2.6
Security update The default value for the server.user_login.password_hashes.default_cost.scram_sha_256 cluster setting is now 10610. (Previously the default was 119680.) The old value was found to have been too high for many types of client hardware, and in some cases could cause regressions in connection latency. The new value was chosen by running tests with clients that have 1 or 2 vCPUs provisioned. Additionally, the new cluster setting server.user_login.rehash_scram_stored_passwords_on_cost_change.enabled was added, and defaults to true. If it is true and the stored SCRAM password for a user has a different cost than the configured default cost, then the next time the user logs in, their password will automatically be rehashed using the configured default cost. If the rehashing is not desired, then operators should update the server.user_login.password_hashes.default_cost.scram_sha_256 cluster setting to the value they desire before upgrading. This change is being backported to v22.2. #98254 Changed default v22.2.7
Command-line change The --drain-wait argument to the cockroach node drain command will be automatically increased if the command detects that it is smaller than the sum of the cluster settings server.shutdown.drain_wait, server.shutdown.connection_wait, server.shutdown.query_wait times two, and server.shutdown.lease_transfer_wait. If the --drain-wait argument is 0, then no timeout is used. This recommendation was already documented, but now the advice will be applied automatically. #98390 New effect v22.2.1
Bug fix RPC connections between nodes now require RPC connections to be established in both directions, otherwise the connection will be closed. This is done to prevent asymmetric network partitions where nodes are able to send outbound messages but not receive inbound messages, which could result in persistent unavailability. This behavior can be disabled by the cluster setting rpc.dialback.enabled. #94778 New setting, enabled by default None
Bug fix Fixed a rare bug introduced in v22.2.0 that could cause a node to crash with an attempting to append refresh spans after the tracked timestamp has moved forward error when querying virtual tables in the crdb_internal or pg_catalog system catalogs. If you are experiencing this bug, set the sql.distsql.use_streamer.enabled cluster setting to false before upgrading a cluster to v23.1. #99443 New guidance v22.2.8
Bug fix The Hot Ranges page DB Console page would show hot ranges by CPU and not QPS (queries per second), depending on the value of the kv.allocator.load_based_rebalancing.objective cluster setting (default cpu). Now the page will always collect statistics based on QPS. (Relates to #96128 in this table.) #100211 Repurposed setting No

Deprecations

  • Ordinal column references (e.g., SELECT @1, @2 FROM t) are now deprecated. By default, statements using this syntax will now result in an error. If desired, such statements can be allowed using the session setting SET allow_ordinal_column_references=true. Support for ordinal column references is scheduled to be removed in upcoming version v23.2. #93754
  • The CONTROLCHANGEFEED role option will be deprecated in the future (see issue #94757). With this change, usages of the CONTROLCHANGEFEED role option will come with a deprecation warning. Its existing behavior remains the same. The SELECT and CHANGEFEED privileges will be used for changefeeds henceforth:
    • The SELECT privilege on a set of tables allows a user to run core changefeeds against them.
    • The CHANGEFEED privilege on a set of tables allows a user to run enterprise changefeeds on them, and also manage the underlying changefeed job (ie. view, pause, cancel, and resume the job). Notably, a new cluster setting changefeed.permissions.enforce_external_connections is added and set to false by default. Enabling this setting restricts users with CHANGEFEED on a set of tables to create enterprise changefeeds into external connections only. To use a given external connection, a user typically needs the USAGE privilege on it. Note that ALTER DEFAULT PRIVILEGES can be used with both the CHANGEFEED and SELECT privileges to assign coarse-grained permissions (i.e., assign permissions to all tables in a schema rather than manually assign them for each table). #94796
  • Deprecated the PGDUMP and MYSQLDUMP formats for IMPORT. They are still present, but will be removed in a future release. See the Migration Overview page for alternatives. #96386
  • Ordinal column references (e.g., SELECT @1, @2 FROM t) are now deprecated. By default, statements using this syntax will now result in an error. If desired, such statements can be allowed using the session setting SET allow_ordinal_column_references=true. Support for ordinal column references is scheduled to be removed in upcoming version v23.2. #93754

Known limitations

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

Additional resources

Resource Topic Description
Cockroach University Introduction to Distributed SQL and CockroachDB This course introduces the core concepts behind distributed SQL databases and describes how CockroachDB fits into this landscape. You will learn what differentiates CockroachDB from both legacy SQL and NoSQL databases and how CockroachDB ensures consistent transactions without sacrificing scale and resiliency. You'll learn about CockroachDB's seamless horizontal scalability, distributed transactions with strict ACID guarantees, and high availability and resilience.
Cockroach University Practical First Steps with CockroachDB This course will give you the tools you need to get started with CockroachDB. During the course, you will learn how to spin up a cluster, use the Admin UI to monitor cluster activity, and use SQL shell to solve a set of hands-on exercises.
Cockroach University Building a Highly Resilient Multi-region Database using CockroachDB This course is part of a series introducing solutions to running low-latency, highly resilient applications for data-intensive workloads on CockroachDB. In this course we focus on surviving large-scale infrastructure failures like losing an entire cloud region without losing data during recovery. We’ll show you how to use CockroachDB survival goals in a multi-region cluster to implement a highly resilient database that survives node or network failures across multiple regions with zero data loss.
Cockroach University Introduction to Serverless Databases and CockroachDB Serverless This course introduces the core concepts behind serverless databases and gives you the tools you need to get started with CockroachDB Serverless. You will learn how serverless databases remove the burden of configuring, sizing, provisioning, securing, maintaining and dynamically scaling your database based on load. This means you simply pay for the serverless database resources you use.
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 Developer Guide Overview This page provides an overview of resources available to developers building applications on CockroachDB.
Docs Security Overview The 23.1 release encapsulates a number of security milestones. See the security overview for a summary.
Docs Architecture Overview This page provides a starting point for understanding the architecture and design choices that enable CockroachDB's scalability and consistency capabilities.
Docs SQL Feature Support The page summarizes the standard SQL features CockroachDB supports as well as common extensions to the standard.
Docs Change Data Capture Overview This page summarizes CockroachDB's data streaming capabilities. Change data capture (CDC) provides efficient, distributed, row-level changefeeds into a configurable sink for downstream processing such as reporting, caching, or full-text indexing.
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.

v23.1.0-rc.2

Release Date: May 4, 2023

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.

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-unstable:v23.1.0-rc.2

Changelog

View a detailed changelog on GitHub: v23.1.0-rc.1...v23.1.0-rc.2

Bug fixes

  • Fixed a bug introduced in versions 22.1.19, 22.2.8, and pre-release versions of 23.1 that could cause queries to return spurious insufficient privilege errors. For the bug to occur, two databases would need to have duplicate tables, each with a foreign key reference to another table. The error would then occur if the same SQL string was executed against both databases concurrently by users that have privileges over only one of the tables. #102651

Contributors

This release includes 4 merged PRs by 4 authors.

v23.1.0-rc.1

Release Date: May 2, 2023

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.

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-unstable:v23.1.0-rc.1

Changelog

View a detailed changelog on GitHub: v23.1.0-beta.3...v23.1.0-rc.1

SQL language changes

  • Two views have been added to the crdb_internal system catalog: crdb_internal.statement_activity surfaces data in the persisted system.statement_activity table, and crdb_internal.transaction_activity surfaces the system.transaction_activity table. #102047
  • Span statistics are now unavailable on mixed-version clusters. #101878

DB Console changes

  • Timestamps are now displayed in DB Console in the timezone specified in the ui.display_timezone cluster setting, if set. Currently supported timezones are Coordinated Universal Time and America/New_York. #102241
  • DB Console now limits historical data for SQL Activity pages to 200000 rows by default, based on the new sql.stats.activity.persisted_rows.max cluster setting. A background job automatically prunes the system tables transaction_activity and statement_activity. #101995
  • Improved performance when viewing the crdb_internal.transaction_contention_events table in DB Console if there are a large number of rows. #101871

Bug fixes

  • Fixed a bug introduced prior to v22.1 where an EXPORT statement could be incorrectly evaluated and result in a node panic or incorrect query results if it had projection or rendering on top of the EXPORT, such as the statement WITH cte AS (EXPORT INTO CSV 'nodelocal://1/export1/' FROM SELECT * FROM t) SELECT filename FROM cte;. Only the presentation of the query result was affected, not the exported data. #101806
  • The descriptions of the rebalancing.readbytespersecond and rebalancing.writebytespersecond metrics now correctly reference bytes read and bytes written, respectively. #101710
  • Fixed a rare bug introduced prior to v22.1 where distributed plans could cause the graceful drain of a node to become stuck retrying forever during node shutdown. This bug leads to errors like drain details: distSQL execution flows:, together with a non-zero number of flows that is not reducing over a long period of time. #101884
  • Fixed a bug where a RESTORE operation with skip_localities_check could fail with errors if regions were missing on a cluster. #101798
  • Fixed a bug introduced in testing releases of v23.1 that could cause incorrect results for queries with STRICT user-defined functions. #101951
  • Fixed a rare bug that could cause keys to be unexpectedly deleted locally within a store by replica rebalancing during a write heavy workload. #102166
  • Fixed a bug where a failed or cancelled IMPORT operation could leave some of the imported rows behind after it was cancelled, if the writing processes were slow enough to continue writing after the cleanup process started. #102246
  • Fixed a bug in the behavior of the enforce_home_region session variable that may have allowed a hash join to be favored over a lookup join, or failed to error out remote accesses done by uniqueness checks for mutations on REGIONAL BY ROW tables. #102287
  • Fixed a bug introduced in testing releases of v23.1 where a node could crash when evaluating a COPY command when the schema had INT2 or INT4 type. #102307
  • Fixed a bug where a backup of a key's revision history may not correctly be restored to the proper revision of the key if it is split across multiple sorted string tables. #102342
  • Fixed a bug introduced in testing releases of v23.1 where a user could be prevented from logging in or viewing or changing GRANTs if the cluster had a long period of inactivity. #102488
  • Fixed a bug where a node that transferred a lease away and missed a liveness heartbeat could then erroneously update the closed timestamp during the stasis period of its liveness. This could lead to closed timestamp invariant violation and could cause the node to crash. In extreme cases, this could lead to inconsistencies in read-only queries. #102602
  • Fixed a bug that caused memory leaks when a closed or cancelled connection was released. #101810

Performance improvements

  • SQL Activity dashboards now default to using a table optimized with the top data for the most used cases. Otherwise, they use persisted data if available, and in-memory data otherwise. #102310
  • Static erroring of some locality-optimized lookup joins is now handled dynamically during query execution. #102287

Miscellaneous

  • Two new statistics help to track the efficiency of snapshot transfers and to keep the number of failures due to system-level races as low as possible. range.snapshots.recv-failed shows the number of snapshot send attempts that are initiated but not accepted by the recipient. range.snapshots.recv-unusable shows the number of snapshots that were fully transmitted but not used. #101835

Contributors

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

  • ajwerner

v23.1.0-beta.3

Release Date: April 24, 2023

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.

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-unstable:v23.1.0-beta.3

Changelog

View a detailed changelog on GitHub: v23.1.0-beta.2...v23.1.0-beta.3

Backward-incompatible changes

  • Previously, if a user specified a search_path in the connection string parameters, it would always be treated as case sensitive. Now, in order to have the schema names in the search_path respect case, the user must include double quotes around the name. #101492

SQL language changes

Operational changes

  • The amount of replication traffic in flight from a single Raft leader to a follower has been reduced from 256 MB to 32 MB. This reduces the chance of running out of memory during bulk write operations. This can be controlled via the environment variable COCKROACH_RAFT_MAX_INFLIGHT_BYTES. #101508

DB Console changes

  • When a user activates diagnostics on the SQL Activity page, there is now an option to select the trace rate for statement diagnostics collection. #101762
  • Updated the Network Latency side navigation name and Network Diagnostics page title to Network. Updated the Advanced Debugging page title to Advanced Debug. #101761
  • Introduced a "draining node" Node Status on the Cluster Overview panel so that "draining node" has it own value instead of counting as a "dead node". #101794
  • Added a time scale selector to the Diagnostics tab under the Statement Details page. This allows users to view bundles from the selected period only. #101803

Bug fixes

  • Fixed a bug that caused internal errors when executing user-defined functions with empty bodies. This bug was only present in alpha pre-release versions of 23.1. #101383
  • Fixed a bug that caused a restore to fail occasionally due to incorrect schema ID resolution when restoring a backup with user-defined schemas. #101310
  • Fixed a bug in parsing a search_path with a quote in it when specified in the connection string. Also, the search_path session variable now supports schema names that have commas in them. #101492
  • Fixed a bug that has existed since user-defined functions were introduced that could cause a function call to resolve to the wrong function after changes to the schema search path. #101513
  • Previously, CockroachDB v23.1 alpha and beta versions would panic on cockroach start command when the GOMEMLIMIT environment variable was set and the --max-go-memory flag wasn't specified. This is now fixed. #101565
  • Fixed a bug that caused errors in test builds and potentially incorrect results in release builds when invoking a user-defined function with a subquery argument. This bug was only present in v23.1 alpha versions. #101639
  • Fixed an internal error that could occur when the enforce_home_region session setting is on and the input to the lookup join is a SELECT of scalar expressions (e.g., 1+1). Also, subqueries with no home region now error out with enforce_home_region set. #101504
  • Point inserts and updates that write to a remote region of a table created with the REGIONAL BY ROW AS clause will now error out. #101708
  • Fixed a bug in the built-in functions pg_get_indexdef and col_description that could cause the functions to return errors if the user created tables named pg_indexes or pg_attribute. Or, if the user created a schema named system with a table named comments. This bug was only present in pre-release versions of v23.1. #101690
  • Fixed a bug where, when CockroachDB failed to retrieve contention information, the full Insights page would return an error. Now the Insights page will load even when there is an issue with decoding contention information. #101784
  • Fixed a bug to ensure that the list of fingerprints used per index is shown even when there is a max-size limit on the SQL API. #101785

Contributors

This release includes 36 merged PRs by 15 authors.

v23.1.0-beta.2

Release Date: April 17, 2023

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.

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-unstable:v23.1.0-beta.2

Changelog

View a detailed changelog on GitHub: v23.1.0-beta.1...v23.1.0-beta.2

Enterprise edition changes

SQL language changes

  • Added the session variable multiple_active_portals_enabled. This setting is only for a preview feature. When set to true, it allows multiple portals to be open at the same time, with their execution interleaved with each other. In other words, these portals can be paused. The underlying statement for a pausable portal must be a read-only [SELECT](../v23.1/selection-queries.html) query without sub-queries or postqueries (such as executed by foreign key checks), and such a portal is always executed with a local query plan. #101026

DB Console changes

  • Update sort label on Search Criteria to match the name on the table columns on the Statements and Transactions pages. #101126
  • By default, we now show the Application Name column in the fingerprints overview pages. Statement fingerprints and Transaction fingerprints will be displayed per application on the overview pages rather than grouped into a single fingerprint ID. #101235
  • When going from the Fingerprint Overview pages or the Insight Details pages to the Fingerprint Details page for statements or transactions, the details page will fetch data for the statement with the provided application name. For overview pages, this is the app name of the selected row. For insight details, it is the app of the execution that generated the insight. #101235

Bug fixes

  • Fixed a bug so that the text search @@ operator ("matches") can work with variable expressions. Fixed a bug where incorrect results were returned when one of the arguments was a TEXT expression and the other argument was a TEXT or TSQuery expression. #100918
  • Fixed a bug where running DROP COLUMN ... CASCADE when that column is used in an index that includes other columns caused a panic. #100856
  • Fixed a rare race condition on node startup that could cause an invalid memory address or nil pointer dereference error. #100626
  • Fixed a bug that was causing nodes running on Windows to crash on startup. This bug only existed in v23.1 alphas. #101091
  • Fixed a rare condition that could allow a transaction to get stuck indefinitely waiting on a released row-level lock if the per-range lock count limit was exceeded while the transaction was waiting on another lock. #100946
  • Fixed a rare internal error in the optimizer that has existed since before version v22.1, which could occur while enforcing orderings between SQL operators. #101355
  • Fixed a bug so that the crdb_internal.deserialize_session internal function works properly with prepared statements that have more param type hints than params. Before this bugfix, deserializing a session containing a prepared statement with more parameter type hints than parameters would panic. For example: PREPARE p (int) AS SELECT 1. These extra type hints are now ignored by crdb_internal.deserialize_session. #101368

Performance improvements

Contributors

This release includes 57 merged PRs by 29 authors.

v23.1.0-beta.1

Release Date: April 13, 2023

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.

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-unstable:v23.1.0-beta.1

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.9...v23.1.0-beta.1

Enterprise edition changes

  • The Avro schema registry URI now allows an additional timeout=T query parameter, which allows you to change the default timeout for contacting the schema registry. #99300

SQL language changes

  • Changed the GC TTL on the SQL Stats table to 1h on CockroachDB Dedicated and Self-Hosted clusters. This change is not applicable to CockroachDB Serverless clusters or clusters running CockroachDB v23.1 with secondary tenants. #100359
  • When there is no data persisted, show the in-memory data. #100505
  • Added two new cluster settings that enable users to change the number of histogram samples and buckets collected when building histograms as part of table statistics collection. While the default values should work for most cases, it may be beneficial to increase the number of samples and buckets for very large tables to avoid creating a histogram that misses important values: #100662
    • sql.stats.histogram_samples.count
    • sql.stats.histogram_buckets.count
  • Added two new table storage parameters, sql_stats_histogram_buckets_count and sql_stats_histogram_samples_count. These parameters can be used to override the cluster settings sql.stats.histogram_buckets.count and sql.stats.histogram_samples.count at the table level, allowing you to change the number of histogram samples and buckets collected when building histograms as part of table statistics collection. While the default values should work for most cases, it may be beneficial to increase the number of samples and buckets for very large tables to avoid creating a histogram that misses important values. #100662

Operational changes

  • Introduced seven new timeseries metrics for better visibility into the behavior of storage engine iterators and their internals. #100445
  • Added a new metric range.snapshots.delegate.in-progress and renamed two metrics:
    • range.snapshot.delegate.successes -> range.snapshots.delegate.successes
    • range.snapshot.delegate.failures -> range.snapshots.delegate.failures #100421
  • Added two new timeseries metrics, providing some observability into the volume of keys preserved by open LSM snapshots: #100878
    • storage.compactions.keys.pinned.count
    • storage.compactions.keys.pinned.bytes

DB Console changes

  • Fixed an issue with properly rendering placeholders on the Node Map view for insecure clusters. #100214

Bug fixes

  • Fixed a bug which could cause SHOW CLUSTER SETTING version to hang and return an opaque error while cluster finalization is ongoing. #100259
  • Fixed a bug that could cause internal errors and corrupt partial indexes when deleting rows with the DELETE FROM .. USING syntax. This bug is only present in alpha versions of v23.1.0. #100307
  • The Hot Ranges page DB Console page would show hot ranges by CPU and not QPS (queries per second), depending on the value of the kv.allocator.load_based_rebalancing.objective cluster setting (default cpu). Now the page will always collect statistics based on QPS. #100211
  • In rare cases involving overload and schema changes, users could sometimes, transiently, see errors of the form deadline below read timestamp is nonsensical; txn has would have no chance to commit. These errors carried and internal pgcode and could not be retried. This form of error is now classified as a retriable error and will be retried automatically either by the client or internally. #100256
  • Fixed a bug in the declarative schema changer in v23.1 where unique without index can be incorrectly added in tables with duplicate values if it was added with a [ALTER TABLE ... ADD/DROP COLUMN](/docs/v23.1/alter-table.html in one ALTER TABLE statement. #100535
  • Fixed an issue where the enforce_home_region session setting did not prevent a locality-optimized anti-join from looking up rows in remote regions. This bug is only present in alpha versions of v23.1.0. #100735

Performance improvements

  • Audit logging should no longer incur extra latency when resolving table/view/sequence names. #99548
  • The webhook sink is now able to handle a drastically higher maximum throughput by enabling the changefeed.new_webhook_sink_enabled cluster setting. #100639

Contributors

This release includes 116 merged PRs by 44 authors.

v23.1.0-alpha.9

Release Date: April 4, 2023

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.

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-unstable:v23.1.0-alpha.9

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.8...v23.1.0-alpha.9

Backward-incompatible changes

  • The output of the SHOW RANGES command for the crdb_internal.ranges and crdb_internal.ranges_no_leases tables has been updated, and the previous output is deprecated. To enable the new command output, set the sql.show_ranges_deprecated_behavior.enabled cluster setting to false. The new output will become default in v23.2. #99618

Enterprise edition changes

  • The CREATE CHANGEFEED statement now allows you to limit the nodes that can execute a changefeed by including a locality filter in the WITH clause. A node can execute the changefeed only if it was started with a matching --locality flag. Replace {locality} with a comma-separated list of key-value pairs. #99935

SQL language changes

  • The new prepared_statements_cache_size session setting helps to prevent prepared statement leaks by automatically deallocating the least-recently-used prepared statements when the cache reaches a given size. #99254

Operational changes

  • The new COCKROACH_DISABLE_NODE_AND_TENANT_METRIC_LABELS environment variable allows you to suppress metrics from a cluster's Prometheus endpoint if they conflict with labels that are applied by external tools that collect metrics from the endpoint. Set the environment variable to a comma-separated list of key-value pairs. #99820

DB Console changes

  • The Index Details section of the Databases page now displays the list of most-frequently-used index fingerprints to all users, rather than only to admin users, because the page now queries a view rather than a system table directly. #99485
  • When you search or filter within the Statements page or Transactions page, if you interactively sort the results using a column that was not part of the original query, a warning displays if you are viewing only a subset of the results, along with a suggestion to update the original query. #99795

Miscellaneous

  • Several computed columns have been added to the statement_statistics_persisted and transaction_statistics_persisted views in the crdb_internal system catalog and indexed in the corresponding system tables:

    • execution_count
    • service_latency
    • cpu_sql_nanos
    • contention_time
    • total_estimated_execution_time
    • p99_latency

    #99417

Bug fixes

  • Fixed pagination bugs when searching or filtering within the Databases page or viewing the details of a database. #99513
  • Fixed a rare bug introduced in v22.2.0 that could cause a node to crash with an attempting to append refresh spans after the tracked timestamp has moved forward error when querying virtual tables in the crdb_internal or pg_catalog system catalogs. If you are experiencing this bug, set the sql.distsql.use_streamer.enabled cluster setting to false before upgrading a cluster to v23.1. #99443
  • Fixed a bug that could erroneously cause multiple garbage-collection jobs to be created when executing a DROP SCHEMA ... CASCADE command, one job for each table and one for the cascaded DROP itself. #99706
  • Fixed a bug in the Insights page that prevented a recommendation to drop an index from being executed if the index's name contained a space. #100023
  • Fixed a rare bug that prevented the garbage-collection job for a TRUNCATE command from successfully finishing if the table descriptor had already been garbage-collected. The garbage-collection job now succeeds in this situation. #100009
  • Fixed a rare bug that could cause a query of a virtual table in the crdb_internal or pg_catalog system catalog to hang indefinitely if the query returned an error. #99969
  • Fixed a bug introduced prior to v21.2 that could cause the SQL gateway node to crash if you created a view with a circular or self-referencing dependencies. This situation no longer crashes the node, and a cyclic view dependency for relation error is now logged. #100159
  • Several rare bugs have been fixed that could cause corruption in the existing primary index when a rollback occurs concurrent to adding or removing a column family. This could lead to subsequent unavailability of the table. #100030
  • Fixed a bug that could cause a node to crash with an out-of-memory (OOM) exception when viewing details in the Statements page or Transactions page. #99550

Contributors

This release includes 75 merged PRs by 41 authors.

v23.1.0-alpha.8

Release Date: March 27, 2023

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.

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-unstable:v23.1.0-alpha.8

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.7...v23.1.0-alpha.8

Security updates

  • Previously, users could gain unauthorized access to statement diagnostic bundles they did not create if they requested the bundle through an HTTP request to /_admin/v1/stmtbundle/<id> and correctly guessed its (non-secret) ID. This change ensures that only authorized SQL users can request statement diagnostic bundles. #99051

General changes

Enterprise edition changes

  • Enabled the changefeed.mux_rangefeed.enabled cluster setting for MuxRangeFeed clients to increase the efficiency when running against large-scale workloads. #97957
  • The server.oidc_authentication.claim_json_key cluster setting for DB Console SSO now accepts list-valued token claims. #98522
  • Added the WITH key_column option to override the message metadata key for changefeeds. This changes the key hashed to determine Kafka partitions. It does not affect the output of key_in_value or the domain of the per-key ordering guarantee. #98806
  • The Node Map now shows normalized CPU usage. #98225

SQL language changes

  • Fixed a bug where the check constraint on an OID type column results in a panic in the legacy schema changer. #98800
  • Added a new WITH REDACT option to the following statements: SHOW CREATE, SHOW CREATE TABLE, and SHOW CREATE VIEW which replaces constants and literals in the printed CREATE statement with the redacted marker, ‹×›. #98251
  • Added support for the REDACT flag to the following variants of EXPLAIN: EXPLAIN (OPT), EXPLAIN (OPT, CATALOG), EXPLAIN (OPT, MEMO), EXPLAIN (OPT, TYPES), and EXPLAIN (OPT, VERBOSE). These EXPLAIN statements will have constants, literal values, parameter values, and any other user data redacted in the output. #97549
  • Disallowed the RESTORE of backups taken on a cluster version older than the minimum binary version the current cluster can interoperate with. This is described in an updated version of the policy outlined in "Restoring Backups Across Versions". #98597
  • Bulk COPY FROM statements are now processed with a vectorized insert and can be anywhere from 0.5x to 5x faster. Typical hardware and schemas should see a 2x improvement. Vectorized inserts are only used for COPY statements and are not yet applied to regular inserts. Both the vectorize and copy_fast_path_enabled session variables can be used to disable this feature. #98605
  • Added stemming and stopword-eliminating text search configurations for English, Danish, Dutch, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Russian, Spanish, Swedish, and Turkish. #97677
  • Added the system.statement_activity and system.transaction_activity tables. These tables are used to populate the SQL Activity pages, and contain the top N statements and transactions based on different key columns. #99179
  • Added helper text for UPDATE to include FROM <source>. #99301
  • Added the default_text_search_config variable for compatibility with single-argument variants to the following text search functions: to_tsvector, to_tsquery, phraseto_tsquery, and plainto_tsquery, which use the value of default_text_search_config instead of expecting one to be included as in the two-argument variants. The default value of this setting is english. #99323
  • Added the ts_rank function for ranking text search query results. #99323
  • Renamed the coordinator_locality option in BACKUP to EXECUTION LOCALITY. This option allows the user to control the nodes involved in the execution of a backup job, including the processing of row data and job coordination. For example, defining an execution locality for a backup job could reduce latency when a cluster is running heavy workloads and has a frequent backup schedule. #99176

Operational changes

  • Checkpoint directories that can be created in the rare event of range inconsistency are now clearly indicated as pending until they are fully populated. This helps operators distinguish valid checkpoints from corrupted ones. #99119
  • Prometheus metrics available at the _status/vars path now contain a node_id label that identifies the node they were scraped from. #99235

DB Console changes

  • The Statement & Transaction Fingerprints pages no longer poll data, to simplify the user experience and reduce any performance impact. #98331
  • Data on the Statement Fingerprint Details page no longer updates automatically every 5mins. #99298
  • Updated the Jobs table column name from "Last Modified Time" to "Last Execution Time". #99021
  • On the SQL Activity Fingerprints pages, users will not see stats that have not yet been flushed to disk. #98815
  • Users can now request top-k statements by % runtime on the SQL Activity Fingerprints pages. #98815
  • Added Search Criteria to the Statements and Transactions pages, and updated the UX with improvements. #98815
  • Added badges for each selected filter on the SQL Activity and Insights pages. #98988
  • The default request sort for the Statement Fingerprints Overview page is now % of All Runtime. #99298
  • Fixed a bug where the table's CREATE statement would not display correctly on the Table Details page. #99434
  • Added an assertion on the KV side to prevent other existing or future attempts of LeafTxn issuing locking requests. This ensures the KV API is used as agreed upon and can be helpful in debugging latency issues caused by holding locks. #99412

Bug fixes

  • Fixed a rare panic in upstream etcd-io/raft when a message appends race with log compaction. #98721
  • In the DB Console Stats pages, issuing a new request for stats while one is pending is now allowed and will replace the pending request. #98331
  • Fixed a bug in which SET avoid_buffering = true could produce a crash on subsequent operations. #98290
  • Fixed a bug where using ST_Transform could result in a memory leak. #98740
  • Fixed internal errors in SHOW JOBS statements that have a WITH clause. #98389
  • Previously, the ADD COLUMN ... DEFAULT cluster_logical_timestamp() statement would crash the node and leave the table in a corrupt state. The root cause is a nil pointer dereference. The bug is now fixed by returning an unimplemented error and hence disallowing using the builtin function as the default value when backfilling. #98696
  • Fixed a bug that could prevent a cached query with a user-defined type reference from being invalidated even after a schema change that should prevent the type from being resolved. #96045
  • Fixed a bug that could prevent a cached query from being invalidated when a UDF referenced by that query was altered or dropped. #96045
  • Fixed the replacement of in-flight requests for KeyedCachedDataReducers to prevent permanent loading on requests stuck on an inFlight status. #99095
  • Improved the reliability of latency data in v23.1 clusters. #99294

Performance improvements

  • The Raft tick interval has been increased from 200ms to 500ms in order to reduce per-replica CPU costs, and can now be adjusted via COCKROACH_RAFT_TICK_INTERVAL. Dependant parameters such as the Raft election timeout (COCKROACH_RAFT_ELECTION_TIMEOUT_TICKS), reproposal timeout (COCKROACH_RAFT_REPROPOSAL_TIMEOUT_TICKS), and heartbeat interval (COCKROACH_RAFT_HEARTBEAT_INTERVAL_TICKS) have been adjusted such that their wall-time value remains the same. #98584
  • The Raft scheduler is now sharded to relieve contention during range Raft processing, which can significantly improve performance at high CPU core counts. #98854

Build changes

  • Running ./dev ui test (or bazel test //pkg/ui/workspaces/db-console:jest) now uses less memory. #98880

Contributors

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

  • Eric.Yang

v23.1.0-alpha.7

Release Date: March 20, 2023

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.

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-unstable:v23.1.0-alpha.7

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.6...v23.1.0-alpha.7

Security updates

  • The default value for the server.user_login.password_hashes.default_cost.scram_sha_256 cluster setting is now 10610. (Previously the default was 119680.) The old value was found to have been too high for many types of client hardware, and in some cases could cause regressions in connection latency. The new value was chosen by running tests with clients that have 1 or 2 vCPUs provisioned. Additionally, the new cluster setting server.user_login.rehash_scram_stored_passwords_on_cost_change.enabled was added, and defaults to true. If it is true and the stored SCRAM password for a user has a different cost than the configured default cost, then the next time the user logs in, their password will automatically be rehashed using the configured default cost. If the rehashing is not desired, then operators should update the server.user_login.password_hashes.default_cost.scram_sha_256 cluster setting to the value they desire before upgrading. This change is being backported to v22.2. #98254

General changes

  • CockroachDB now uses the soft memory limit of Go runtime by default. This feature of Go has been available since v22.2 by setting the GOMEMLIMIT environment variable. Now it is enabled by default, which should reduce the likelihood of the CockroachDB process OOMing. This soft memory limit can be disabled by specifying --max-go-memory=0 to cockroach start. #97666
  • Previously, the output of SHOW CHANGEFEED JOBS was limited to show unfinished jobs and finished jobs from the last 14 days. This change makes the command show all changefeed jobs, regardless of if they finished and when they finished. Note that jobs still obey the cluster setting jobs.retention_time. Completed jobs older than that time are deleted. Fixes: https://github.com/cockroachdb/cockroach/issues/97883 #98175

Enterprise edition changes

  • Sinkless changefeeds that use the AS SELECT syntax now require an enterprise license. #98241
  • External connections can now be used as the URI value for a Confluent schema registry. For example, CREATE EXTERNAL CONNECTION reg AS "https://example.cloud?opt=val"; CREATE CHANGEFEED FOR foo WITH format='avro',confluent_schema_registry='external://reg' #97140
  • Backup schedules created or altered to have the option on_previous_running will have the full backup schedule created with the user specified option, but will override the incremental backup schedule to always default to on_previous_running = wait. This ensures correctness of the backup chains created by the incremental schedule by preventing duplicate incremental jobs from racing against each other. #98249
  • Changefeeds to a Kafka sink now support the OAUTHBEARER SASL_MECHANISM. #98053
  • Changefeeds running with the changefeed.mux_rangefeed.enabled cluster setting set to true are more efficient, particularly when executing against large tables. #96756

SQL language changes

  • A regions field was added to the statistics column of crdb_internal.statement_statistics, reporting the regions of the nodes on which the statement was executed. #95449
  • The enforce_home_region session setting was extended with a new optional preview feature and session setting, which is disabled by default, to dynamically detect and report the home region for SELECT queries based on the locality of the queried rows, if different from the region of the gateway node. cockroachdb/cockroach#97827
  • Added a URL to errors related to the enforce_home_region session setting that users can view to see additional information about the error. #97827
  • Added a new session setting enforce_home_region_follower_reads_enabled as a preview feature to allow errors triggered by the enforce_home_region session setting to perform reads using AS OF SYSTEM TIME follower_read_timestamp() in order to find and report a query's home region. #97827
  • Added a new aggregate builtin function array_cat_agg. It behaves similarly to array_agg(unnest(array_column)): it takes arrays as its input, unnests them into the array elements which are then aggregated into a single result array. It's similar to concatenating all input arrays into a single array. #97826
  • Added a new session setting optimizer_always_use_histograms, which ensures that the optimizer always uses histograms when available to calculate the statistics of every plan that it explores. Enabling this setting can prevent the optimizer from choosing a suboptimal index when statistics for a table are stale. #98194
  • Added the MODIFYSQLCLUSTERSETTING system privilege. This privilege allows users the ability to view all cluster settings, but only modify those settings with the sql.defaults.* prefix. This re-introduces the modify functionality seen with MODIFYCLUSTERSETTING CockroachDB v22.2. #97521
  • Added a status column to the following crdb_internal virtual tables: crdb_internal.cluster_txn_execution_insights and crdb_internal.node_txn_execution_insights. cockroachdb/cockroach#98217
  • Added a new session variable allow_role_memberships_to_change_during_transaction which can be used to make the granting and revoking of role memberships faster at the cost of some isolation claims. By default, when granting or revoking a role from another role, CockroachDB waits for all transactions that are consulting the current set of role memberships to complete. This means that by the time the transaction which performed the grant or revoke operation returns successfully, the user has a proof that no ongoing transaction is relying on the state that existed prior to the change. The downside of this waiting is that it means that GRANT and REVOKE will take longer than the longest currently executing transaction. In some cases, users do not care about whether concurrent transactions will immediately see the side-effects of the operation, and would instead prefer that the grant or revoke finish rapidly. In order to aid in those cases, the session variable allow_role_memberships_to_change_during_transaction has been added. Now, the grant or revoke will only need to wait for the completion of statements in sessions which do not have this option set. One can set the option as enabled by default in all sessions in order to accelerate and grant and revoke role operations. #98370
  • Fixed a bug where CockroachDB panicked when a user tried to truncate a table which had an ongoing Row-level TTL change. CockroachDB still does not support truncating a table in this scenario, but instead of panicking an "unimplemented" error is returned. #98537

Operational changes

  • Range leases will no longer be transferred to stores which are IO overloaded. #97587
  • The environment variable COCKROACH_IGNORE_CLUSTER_SETTINGS can be used to start a node so that it ignores all stored cluster setting values in an emergency. #97805
  • Introduce two cluster settings to control disk utilization thresholds for replica allocation: kv.allocator.rebalance_to_max_disk_utilization_threshold, which controls the maximum disk utilization before a store will never be used as a rebalance target, and kv.allocator.max_disk_utilization_threshold, which controls maximum disk utilization before a store will never be used as a rebalance or allocation target and will actively have replicas moved off of it. #97409
  • The cluster setting kv.trace.slow_request_stacks.threshold can be used to attach available stack history from tracer snapshots to traces of slow requests. #97895
  • Added a new metric changefeed.schema_registry.retry_count. This measures the number of request retries performed when sending requests to the changefeed schema registry. Observing a nonzero value may indicate improper configuration of the schema registry or changefeed parameters. #98338
  • The kv.range_split.load_cpu_threshold cluster setting now has a minimum setting value of 10ms. #98250
  • The kv.allocator.lease_io_overload_threshold_enforcement cluster setting value which disables enforcement is updated to be spelled correctly as "ignore" rather than "ingore". cockroachdb/cockroach#98543
  • The range lease duration can now be adjusted via the environment variable COCKROACH_RANGE_LEASE_DURATION. Users are advised to exercise caution when adjusting this, and consider the relationship with e.g. Raft election timeouts and network timeouts. #98616

Command-line changes

  • cockroach sql and cockroach demo now support the client-side command \s to display the previous command history. #98035
  • Added a new flag --max-go-memory to the cockroach start command. It controls the soft memory limit on the Go runtime which adjusts the behavior of the Go garbage collector to try keeping the memory usage under the soft memory limit (the limit is "soft" in a sense that it is not enforced if live objects (RSS) exceed it). Similar to the --max-sql-memory flag, the new flag --max-go-memory accepts numbers interpreted as bytes, size suffixes (e.g. 1GB and 1GiB) or a percentage of physical memory (e.g. .25). If left unspecified, the flag defaults to 2.25x of --max-sql-memory (subject to --max-go-memory + 1.15 x --cache not exceeding 90% of available RAM). Set to 0 to disable the soft memory limit (not recommended). If the GOMEMLIMIT env var is set and --max-go-memory is not, then the value from the env var is used; if both are set, then the flag takes precedence. #97666

    • Here are a few examples of how the default value is calculated on a machine with 16GiB of RAM. In the first two lines we use the default formula 2.25x --max-sql-memory. In the third line, the default formula results in exceeding the upper bound on total usage (including the cache), so we use the upper bound determined as 0.9 * total RAM - 1.15 * cache size. In the fourth line, the default formula results in 225MiB which is smaller than the lower bound of 256MiB, so we bump the value to that lower bound. In the fifth line, we use the value specified by the user (even though it is smaller than the lower bound on the default value).
    Command line flags Computed max SQL memory Computed cache size Computed max Go memory
    --max-sql-memory=.25 --cache=.25 4GiB 4GiB 9GiB
    --max-sql-memory=.1 --cache=.5 1.6GiB 8GiB 3.6GiB
    --max-sql-memory=.25 --cache=.4 4GiB 6.4GiB 7.04GiB
    --max-sql-memory=100MiB 100MiB 128MiB 256MiB
    --max-sql-memory=.4 --cache=.2 --max-go-memory=100MiB 6.4GiB 3.2GiB 100MiB
  • The --drain-wait argument to the cockroach node drain command will be automatically increased if the command detects that it is smaller than the sum of the cluster settings server.shutdown.drain_wait, server.shutdown.connection_wait, server.shutdown.query_wait times two, and server.shutdown.lease_transfer_wait. If the --drain-wait argument is 0, then no timeout is used. This recommendation was already documented, but now the advice will be applied automatically. #98390

DB Console changes

  • Fixed the error Cannot read properties of undefined (reading 'length') which could cause DB Console pages to fail to load. #98222
  • Added a new metric to the Hardware dashboard showing the system-wide CPU usage in addition to the existing CockroachDB CPU usage. #98187
  • Users will see an upgrade error message when a response from the SQL-over-HTTP API (from /api/v2/sql/) says that a relation or column does not exist. cockroachdb/cockroach#98312
  • The description of the average QPS graph in the Replication Metrics Dashboard no longer claims the average is exponentially weighted. #98270
  • The metric rebalancing.cpunanospersecond is now included in the Replication Metrics Dashboard. #98270
  • Added an error code column to the insights table for a failed execution to the statement and transaction detail views on the Insights Page. Added a status column to the statement and transaction workload insights tables on the Insights Page. cockroachdb/cockroach#97138

Bug fixes

  • RPC connections between nodes now require RPC connections to be established in both directions, otherwise the connection will be closed. This is done to prevent asymmetric network partitions where nodes are able to send outbound messages but not receive inbound messages, which could result in persistent unavailability. This behavior can be disabled by the cluster setting rpc.dialback.enabled. #94778
  • The owner of the public schema can now be changed using ALTER SCHEMA public OWNER TO new_owner. #98000
  • Fixed a bug in which common table expressions (CTEs) marked as WITH RECURSIVE which were not actually recursive could return incorrect results. This could happen if the CTE used a UNION ALL, because the optimizer incorrectly converted the UNION ALL to a UNION. This bug had existed since support for recursive CTEs was first added in v20.1. #98042
  • Internal queries that are executed in order to serve a client-initiated query already appeared in statistics with an application_name prefixed by the string $$. But this name was not used in the output of SHOW QUERIES. Now, SHOW QUERIES also shows the $$ prefix for these types of queries.
  • Fixed a bug in evaluation of ANY, SOME, and ALL sub-operators that would cause expressions like NULL = ANY(ARRAY[]::INT[]) to return NULL instead of False. cockroachdb/cockroach#97948
  • Fixed a bug that could crash the process when a query contained a literal tuple expression with more than two elements and only a single label, e.g., ((1, 2, 3) AS foo). #98142
  • Fixed a bug where certain special character combinations in the options field in connection URLs were not properly supported by CockroachDB. #98302
  • Fixed a bug where the stats columns on the Transaction Fingerprints overview page was continuously incrementing. The fix was to ensure we don't re-use the stats object between re-renders by creating a new copy of the stats for every aggregation. cockroachdb/cockroach#98307
  • Fixed a bug where transactions that performed a SELECT FOR UPDATE across multiple ranges but never performed writes could fail to eagerly clean up their locks after commit. Future transactions that encountered these abandoned locks could be delayed for 50ms before unlocking them. #98044
  • Fixed a bug which could result in some CREATE INDEX statements to fail with the error failed to verify keys for Scan. #98262
  • Fixed a bug where CockroachDB could encounter an internal error concurrent txn use detected. The bug was introduced in v22.2.0. #98120
  • Fixed a bug where if an UPDATE was performed during an on-going ADD COLUMN or DROP COLUMN on a table, the update could incorrectly fail due to a duplicate key error. #98354
  • Fixed a bug where it was possible for CockroachDB to temporarily not respect zone configurations other than the default zone config. This could only happen for a short window of a few seconds after nodes with existing replicas were restarted, and self-rectified (also within seconds). #98422
    • This manifested in a few ways:
    • If num_replicas was set to something other than 3, we would still add or remove replicas to get to 3x replication.
    • If num_voters was set explicitly to get a mix of voting and non-voting replicas, it would be ignored. CockroachDB could possibly even remove non-voting replicas.
    • If range_min_bytes or range_max_bytes were changed from their default values of 128 MiB and 512 MiB respectively, we would instead try to size ranges to be within [128 MiB, 512MiB]. This could appear as an excess amount of range splits or merges, as visible in the Replication Dashboard under "Range Operations".
    • If gc.ttlseconds was set to something other than the default 90000 seconds (25h), we would still only GC data older than 90000s. If the GC TTL was set to something larger than 25h, AS OF SYSTEM TIME queries going further back could start failing. For GC TTLs less than the default, clusters would observe increased disk usage due to more retained MVCC garbage.
    • If constraints, lease_preferences or voter_constraints were set, they would be ignored. Range data and leases would possibly be moved outside where prescribed.
  • Allow users with the VIEWACTIVITY/VIEWACTIVITYREDACTED permissions to access the crdb_internal.ranges_no_leases table, necessary to view important DB Console pages (specifically, the Databases Page, including database details, and database tables). #98535
  • Fixed a bug that caused incorrect results when comparisons of tuples were done using the ANY operator. For example, an expression like (x, y) = ANY ([SELECT](../v23.1/selection-queries.html) a, b FROM t WHERE ...) could return true instead of the correct result of NULL when x and y were NULL, or a and b were NULL. This could only occur if the subquery was correlated, i.e., it references columns from the outer part of the query. This bug was present since the cost-based optimizer was introduced in v2.1. #98700

Miscellaneous

  • Added two views to the crdb_internal catalog: crdb_internal.statement_statistics_persisted, which surfaces data in the persisted system.statement_statistics table, and crdb_internal.transaction_statistics_persisted, which surfaces the system.transaction_statistics table. cockroachdb/cockroach#98261

Contributors

This release includes 245 merged PRs by 65 authors.

v23.1.0-alpha.6

Release Date: March 13, 2023

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.

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-unstable:v23.1.0-alpha.6

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.5...v23.1.0-alpha.6

SQL language changes

  • Previously, an ALTER TABLE .. ADD UNIQUE .. NOT VALID statement would be processed by ignoring the NOT VALID qualifier. This is not in keeping with PostgreSQL, which would throw an error instead. Now, CockroachDB throws the same error as PostgreSQL for such a statement: "UNIQUE constraints cannot be marked NOT VALID". #97758
  • Introduced a new internal virtual table crdb_internal.node_memory_monitors, which exposes all of the current reservations with the memory accounting system on a single node. Access to the table requires the VIEWACTIVITY system privilege (or the legacy VIEWACTIVITY role option), or the VIEWACTIVITYREDACTED system privilege (or the legacy VIEWACTIVITYREDACTED role option) defined. #97657
  • Each type cast is now expressible as a function, e.g., now()::date can be expressed as date(now()). #97093
  • Added support for a new syntax to provide options to the COPY statement. The options can now be given in a comma-separated list enclosed by parentheses. The old syntax is still supported. #97863
  • Added a new built-in function tenants_span_stats, which retrieves the span statistics for the current tenant. #97534
  • Added support for the syntax CREATE DATABASE IF NOT EXISTS ... WITH OWNER. #97951
  • Added a new internal built-in function, crdb_internal.redactable_sql_constants, which can be used to redact SQL statements passed in as strings. #97834
  • Added an error_code column to the crdb_internal.cluster_execution_insights and crdb_internal.node_execution_insightsvirtual tables, which contains the error code for a failed execution. Also added a last_error_code column to the crdb_internal.cluster_txn_execution_insights and crdb_internal.node_txn_execution_insights virtual tables, which contains the error code of the last failed statement in that transaction. #97046
  • Added a new internal built-in function, crdb_internal.redact, which replaces substrings surrounded by redaction markers with the redacted marker. #98008

Command-line changes

  • The cockroach node decommission operation now validates the ability of the node to complete a decommission before attempting it, given the cluster configuration and the ranges with replicas present on the node. When errors are detected that would result in the inability to complete node decommission, they will be printed to stderr and the command will exit, instead of marking the node as decommissioning and beginning the node decommission process. Strict readiness evaluation mode can be used by setting--checks=strict. In this case, any ranges that need preliminary actions prior to replacement for the decommission process (e.g., ranges that are not yet fully up-replicated) will block the decommission process. Validation can be skipped by using the flag --checks=skip. #96100

DB Console changes

Bug fixes

  • The unquoted value none is now allowed as the value in a SET statement. #97816
  • IMPORT INTO ... DELIMITED DATA will now correctly handle quoted fields that contain unescaped newlines. #97545
  • Previously, casting an inet to a string type omitted the mask if a mask was not provided. This was not in keeping with PostgreSQL and is now resolved. #97093
  • Fixed link encoding on links to database/table/index pages. #97893
  • Fixed the SHOW CREATE ALL {TYPES|SCHEMAS|TABLES} commands so they handle database names that have mixed-case, hyphens, or quotes. #97915
  • Tables referenced in index recommendations are now fully qualified, ensuring that they are not ambiguous. #97262
  • Changed the database used for SQL API calls, to no longer use defaultdb, which was causing error messages on some pages when that database no longer exists. #97990

Build changes

  • Changes to source files in pkg/ui/workspaces/db-console now properly bust the build cache, and are consistently included in local builds. #97956

Contributors

This release includes 69 merged PRs by 39 authors.

v23.1.0-alpha.5

Release Date: March 6, 2023

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.

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-unstable:v23.1.0-alpha.5

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.4...v23.1.0-alpha.5

Security updates

  • The new cluster setting server.user_login.downgrade_scram_stored_passwords_to_bcrypt.enabled, which allows you to migrate passwords from SCRAM to bcrypt during user authentication, defaults to true. If it is true and if server.user_login.password_encryption is crdb-bcrypt, then during login, the stored hashed password will be migrated from SCRAM to bcrypt. #97429

General changes

Enterprise edition changes

  • Jobs that utilize a protected timestamp system (such as BACKUP, CHANGEFEED, or IMPORT) now produce metrics that can be monitored to detect cases when a job leaves a stale protected timestamp that will prevent garbage collection from occurring. #97148
  • Changefeeds now automatically expire protected timestamp records for paused jobs if the changefeed is configured with the gc_protect_expires_after option. #97148
  • User-defined functions (UDFs) can now be referenced from column DEFAULT expressions when creating a new table or issuing the SET DEFAULT command. Backup and restore operations also back up and restore UDF IDs that are referenced in a column's DEFAULT expression. If UDF dependencies are missing and the skip_missing_udfs flag is provided, the DEFAULT expressions are dropped during a restore operation. #97501

SQL language changes

  • String literals are now allowed for region names in DDL syntax, in addition to quoted syntax. #97021
  • It is now possible to use * inside a CREATE VIEW statement. The list of columns is expanded at the time the view is created, so that new columns added after the view was defined are not included in the view. This behavior is the same as in PostgreSQL. #97515
  • The default value of sql.stats.cleanup.rows_to_delete_per_txn has been increased to 10000 to increase efficiency of the cleanup job for SQL statistics. #97642
  • The new session setting optimizer_use_improved_split_disjunction_for_joins allows the optimizer to split disjunctions (OR expressions) in more JOIN conditions by building a UNION of two JOIN expressions. If this setting is true, all disjunctions in inner, semi, and anti JOINs will be split. Otherwise, only disjunctions that potentially contain an equijoin condition will be split. #97696
  • Builtins have been added for tsvector, to_tsquery, phraseto_tsquery, and plainto_tsquery, which parse input documents into tsvectors and tsqueries, respectively. The new ts_parse builtin is used to debug the text search parser. #92966
  • The new session variable inject_retry_errors_on_commit_enabled returns a transaction retry error if it is run inside of an explicit transaction when it is set to true. The transaction retry error continues to be returned until inject_retry_errors_on_commit_enabled is set to false. This setting allows you to test your transaction retry logic. #97226
  • Previously, ADD PRIMARY KEY NOT VALID ignored the NOT VALID qualifier. This behavior was not compatible with PostgreSQL. CockroachDB now throws the error PRIMARY KEY constraints cannot be marked NOT VALID. #97746

Operational changes

  • The following cluster settings, which control rebalancing and upreplication behavior in the face of IO-overloaded storage, have been deprecated:

    • kv.allocator.l0_sublevels_threshold
    • kv.allocator.l0_sublevels_threshold_enforce These cluster settings have been replaced by internal mechanisms. #97142
  • Max timeout-to-intent resolution has been added to prevent intent resolution from becoming stuck indefinitely and blocking other ranges attempting to resolve intents. #91815

  • Nodes are now considered suspect when rejoining a cluster and cannot accept lease transfers for one server.time_after_store_suspect window, which defaults to 30 seconds. #97532

Command-line changes

  • The SQL shell (cockroach sql, cockroach demo) now supports the client-side commands \l, \dn, \d, \di, \dm, \ds, \dt, \dv, \dC, \dT, \dd, \dg, \du and \dd in a similar manner to PostgreSQL, including the modifier flags S and +, for convenience for users migrating from PostgreSQL. A notable difference is that when a pattern argument is specified, it should use the SQL LIKE syntax (with % representing the wildcard character) instead of PostgreSQL's glob-like syntax (with * representing wildcards). #88061

DB Console changes

  • The following new metrics track memory usage of prepared statements in sessions:

    • sql.mem.internal.session.prepared.current
    • sql.mem.internal.session.prepared.max-avg
    • sql.mem.internal.session.prepared.max-count
    • sql.mem.internal.session.prepared.max-max
    • sql.mem.internal.session.prepared.max-p50
    • sql.mem.internal.session.prepared.max-p75
    • sql.mem.internal.session.prepared.max-p90
    • sql.mem.internal.session.prepared.max-p99
    • sql.mem.internal.session.prepared.max-p99.9
    • sql.mem.internal.session.prepared.max-p99.99
    • sql.mem.internal.session.prepared.max-p99.999
    • sql.mem.sql.session.prepared.current
    • sql.mem.sql.session.prepared.max-avg
    • sql.mem.sql.session.prepared.max-count
    • sql.mem.sql.session.prepared.max-max
    • sql.mem.sql.session.prepared.max-p50
    • sql.mem.sql.session.prepared.max-p75
    • sql.mem.sql.session.prepared.max-p90
    • sql.mem.sql.session.prepared.max-p99
    • sql.mem.sql.session.prepared.max-p99.9
    • sql.mem.sql.session.prepared.max-p99.99
    • sql.mem.sql.session.prepared.max-p99.999

    #97590

  • Active execution information is now shown on the Statements page even when there is a max size limit error. #97662

  • "Retrying" is no longer a status shown in the Jobs page. #97505

Bug fixes

  • Transaction uncertainty intervals are now correctly configured for reverse scans, to prevent reverse scans from serving stale reads when clocks in a cluster are skewed. #97443
  • The formatting of uniqueness violation errors now matches the corresponding errors from PostgreSQL. #96914
  • Previously, when a new column name would require quoting due to mixed-case or special characters, ALTER TABLE ... ADD COLUMN would not run in an explicit or multi-statement transaction. This is now fixed. #97514
  • Fixed a bug when formatting CREATE TYPE statements for user-defined types which require quoting which might prevent those statements from round-tripping. #97514
  • Using subqueries in user-defined functions without an AS clause is now supported, for consistency with the syntax supported outside of user-defined functions. #97515
  • Fixed a rare bug introduced before v22.1.x that could cause a projected expression to replace column references with the wrong values. #97554
  • Cross-descriptor validation on lease renewal is now disabled, because it can starve online schema changes when there are many descriptors with many foreign key references. #97630
  • Fixed a bug with pagination on the Insights page. #97640
  • Columns referenced in partial index predicates and partial unique constraint predicates can no longer be dropped. The ALTER TABLE .. DROP COLUMN statement now returns an error with a suggestion to drop the indexes and constraints first. This is a temporary safeguard to prevent users from hitting #96924. This restriction will be lifted when that bug is fixed. #97372
  • The Jobs page now displays an error state when an error is encountered during data fetching. #97486
  • Fixed a bug introduced in v22.1 that caused the internal error no bytes in account to release .... #97750
  • The COPY FROM command now respects the statement_timeout and transaction_timeout cluster settings. #97808
  • COPY FROM commands now appear in the output of the SHOW STATEMENTS command. #97808
  • Fixed an error where querying a pg_catalog table included information about a temporary table created in another session. #97727

Performance improvements

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

Miscellaneous

  • UDFs can now return the RECORD result type, which represents any tuple. For example, CREATE FUNCTION f() RETURNS RECORD AS 'SELECT * FROM t' LANGUAGE SQL; is equivalent to CREATE FUNCTION f() RETURNS t AS 'SELECT * FROM t' LANGUAGE SQL;. #97199
  • The parameters for delegated snapshots have been marked as internal. #97408
  • Fixed an error when calling CREATE OR REPLACE FUNCTION with a user-defined return type if the user-defined type was modified after the original user-defined function was created. The command now succeeds as long as the function body returns output that matches the modified user-defined type. #97274
  • Columns with referenced constraints can now be dropped. #97579
  • Index cascades with a dependent inbound foreign key can now be dropped. #97065

Contributors

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

  • Eric.Yang (first-time contributor)

v23.1.0-alpha.4

Release Date: February 27, 2023

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.

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-unstable:v23.1.0-alpha.4

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.3...v23.1.0-alpha.4

Enterprise edition changes

  • Some of the transformations specific to changefeeds have been deprecated and replaced. These functions were released in limited access in v22.2. Deprecated changefeed transformations continue to function. Closely monitor changefeeds that are created during upgrade. While effort was made to maintain backward compatibility, the updated changefeed transformation may produce slightly different output, such as different column names. #96295
  • Add support for implicit authentication to Azure Storage and KMS. #96825
  • Add support for CREATE EXTERNAL CONNECTION ... AS "postgresql://" or "postgres://". These external connections may be specified as the source in streaming replication. #96551
  • Add support for referencing user defined functions (UDFs) from other objects. Backup and restore operations can now read and write UDF descriptors. #97038

SQL language changes

  • UDFs can now return a set of results by setting the return type to SETOF. #96698
  • UDFs with implicit record return types will return an error when called if the return type has been altered and is no longer compatible with the body of the UDF. #96696
  • The COPY ... TO STDOUT statement allows you to export a table or arbitrary query in the PostgreSQL wire-compatible format. Text and CSV formats are supported. #94408
  • Add the read-only "ssl" session variable. Value is "off" if the server was started in insecure mode (which disables TLS), or "on" otherwise. This is based on the Postgres variable of the same name. #97257
  • Add a hard limit of how much data can be flushed to system tables for sql stats. #97123
  • Add support for the REDACT flag to the following variants of EXPLAIN:

    • EXPLAIN
    • EXPLAIN (PLAN)
    • EXPLAIN (VEC)
    • EXPLAIN ANALYZE
    • EXPLAIN ANALYZE (PLAN)

    These explain statements (along with EXPLAIN ANALYZE (DEBUG), which already supported REDACT) will have constants, literal values, parameter values, and any other user data redacted in output. #95136

  • Previously UDFs are not allowed in tables and any other object. This patch enables UDF usage in CHECK constraints of tables in both legacy schema changer and delcarative schema changer. Circular ependencies are not allowed, namely if a UDF depends on a table, then the table can't use that UDF. #97038

  • A version gate has been added to prevent UDF usage in CHECK constraints before a cluster is fully upgraded to v23.1 or above. #97038

  • Previously users were able to use UDFs from tables with SET DEFAULT and SET ON UPDATE even when they are disallowed from CREATE TABLE and ADD COLUMN. This patch disallows those two cases from ALTER TABLE ALTER COLUMN. #97390

    Operational changes

  • Add COCKROACH_RAFT_LOG_QUEUE_CONCURRENCY env var which controls the number of parallel workers doing Raft log truncations. It can be used to make the in-memory log truncations more agressive and reduce the amount of Raft log data flushed to disk. #97029

  • The new timeseries metric storage.keys.tombstone.count shows the current count of point and range deletion tombstones across the storage engine. #97306

  • The value of kv.range_split.load_cpu_threshold controls the CPU per-second threshold above which a range will be split. Its default value has been increased from 250ms to 500ms, based on performance experiments. #97113

  • CPU balancing is enabled as the default load based rebalancing objective. This can be reverted by setting kv.allocator.load_based_rebalancing.objective to qps. #97424

DB Console changes

  • Add columns p50, p90, p99, max and min latency for Statement table on SQL Activity page. #97082
  • Show a warning for Statement Insights when the SQL API returns a "max size exceed" error. #97153
  • Show a warning for Transaction Insights when the SQL API returns a "max size exceed" error. #97277
  • Show a warning for Schema Insights when the SQL API returns a "max size exceed" error. #97312

Bug fixes

  • Fixed a bug where the AS OF SYSTEM TIME clause was handled incorrectly in an implicit transaction that had multiple statements. #97063
  • This patch fixes asymmetric typing of > and < expressions which may cause erroring of expressions which are legal. #97022
  • This patch fixes possible internal errors in SOME, ANY and ALL expressions of the form: expr > SOME(expr1, expr2, expr3...) #97022
  • Fixed the syntax error for SELECT ... QUERY (without AS) statement. #97041
  • Decommissions that would violate constraints set on a subset of replicas for a range (e.g. num_replicas = 3, <constraint>: 1) will no longer be able to execute, respecting constraints during and after the decommission. #94810
  • Adjusted the size reported for TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, and DATE types in the pgwire protocol. #97145
  • The ParameterStatus message is now only sent over the pgwire protocol if the value of the parameter changed. (The parameters that are sent this way are timezone, intervalstyle, datestyle, is_superuser, and application_name.) #97145
  • Users can now go to the next page of results when there are more than 20 active statements or transactions in the Active Execution page. #97122
  • Since 22.1 when rangefeed enablement overrides in span configs were introduced, rangefeed requests that reached spans outside the range would not cause range cache invalidation due to the setting being checked first, thus requests could repeatedly hit the same incorrect range, causing errors until cache invalidation or node restart. This fix correctly checks that the span is within the range prior to checking the enablement settings, thus invalidating the cache when a request reaches an incorrect range and causing subsequent requests to successfully reach the correct range. #97212
  • A bug has been fixed that caused errors when creating multiple user-defined functions with the same name and different argument types in the same type family. For example, it was impossible to create both functions f(i INT2) and f(INT4). #96481
  • The following spammy log message was removed: > lease [...] expired before being followed by lease [...]; foreground traffic may have been impacted #97358
  • Previously, ALTER TABLE ... INJECT STATISTICS command would fail if a column with COLLATED STRING type had histograms to be injected, and this is now fixed. The bug has been present since at least 21.2. #96695
  • Fixed a bug in the query engine that could cause incorrect results in some cases when a zigzag join was planned. The bug could occur when the two indexes used for the zigzag join had a suffix of matching columns but with different directions. For example, planning a zigzag join with INDEX(a ASC, b ASC) and INDEX(c ASC, b DESC) could cause incorrect results. This bug has existed since at least v19.1. It is now fixed, because the optimizer will no longer plan a zigzag join in such cases. #97151

Performance improvements

  • The optimizer now plans inverted index scans for queries that filter by JSON fetch value operators (->) that contain both a string and an integer index value after the IN operator. For example the optimizer plans inverted index scans for the following filters: json_col->0 IN ('1', '2'), json_col->'a' IN ('1', '2'). #96471

Build changes

  • Starting with CockroachDB v23.1.0-alpha.4, a FIPS compliant tarball and Docker image are produced for the Linux x86_64 platform. The tarball uses OpenSSL libraries for crypto operations by dynamically loading the corresponding FIPS-validated dynamic libraries. The Docker image comes with the FIPS-validated OpenSSL library pre-installed.#96107

Miscellaneous

  • #97229
  • Fix a bug in which RESTORE, BACKUP, and IMPORT jobs would fail if the coordinator node of the job was drained. #97033

Contributors

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

  • DQ (first-time contributor)

v23.1.0-alpha.3

Release Date: February 21, 2023

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.

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-unstable:v23.1.0-alpha.3

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.2...v23.1.0-alpha.3

General changes

  • Added new metrics to count paused jobs for every job type. For example, the metric for paused changefeed jobs is jobs.changefeed.currently_paused. These metrics are updated at an interval defined by the cluster setting jobs.metrics.interval.poll, which defaults to 10 seconds. #89752

Enterprise edition changes

  • Added support for Azure Key Vault KMS, which will allow users to encrypt and decrypt their backups using keys stored in Azure Key Vault. App Registration authentication (Azure RBAC) for Azure Storage is also now supported.#96459
  • Introduced a new locality filter option (coordinator_locality) that can be specified when a backup job is created. This option restricts the backup metadata writing to the designated nodes. #95791
  • Fixed a bug that caused the server to crash if trying to restore a table from a backup generated by BACKUP TABLE from a schema that includes user-defined functions, and the restore target database does not have a schema with the same name. #96911
  • Changefeeds with the unordered option can use multi-region Google Cloud Pub/Sub topics. #96567
  • Fixed a bug in changefeeds where long running initial scans will fail to generate checkpoints. Failure to generate checkpoints is particularly bad if the changefeed restarts. Without checkpoints, the changefeed will restart from the beginning, and in the worst case, when exporting substantially sized tables, the changefeed initial scan may not complete. #96995

SQL language changes

  • Added latency information in seconds to the statement statistics on crdb_internal.statement_statistics, system.statement_statistics, and crdb_internal.cluster_statement_statistics, with information about: min, max, p50, p90, and p99. Also added the columns:latency_seconds_min,latency_seconds_max,latency_seconds_p50,latency_seconds_p90, andlatency_seconds_p99tocrdb_internal.node_statement_statistics`.#96396
  • Deprecated the PGDUMP and MYSQLDUMP formats for IMPORT. They are still present, but will be removed in a future release. See the Migration Overview page for alternatives. #96386
  • COPY ... FROM ... QUOTE '"' will no longer error. #96572
  • Added last_error_code column to the crdb_internal.node_statement_statistics table. Added last_error_code field to the statistics JSON blob in the crdb_internal.statement_statistics and system.statement_statistics tables. #96436
  • Added support for expressions of the form COLLATE "default", COLLATE "C", and COLLATE "POSIX". Since the default collation cannot be changed currently, these expressions are all equivalent. The expressions are evaluated by treating the input as a normal string, and ignoring the collation. This means that comparisons between strings and collated strings that use "default", "C", or "POSIX" are now supported. Creating a column with the "C" or "POSIX" collations is still not supported. #96828
  • The insights subsystem in sqlstats is now able to detect failed executions, regardless if they were slow or not. #97039
  • The internal statement_statistics and transaction_statistics tables now include sampled execution statistics on storage iteration. #96016
  • Introduced the declare_cursor_statement_timeout_enabled session variable that disables statement timeouts during FETCH when using DECLARE CURSOR. #96607

Operational changes

  • A BACKUP that encounters too many retryable errors will now fail instead of pausing to allow subsequent backups the chance to succeed. #96673
  • Added an option to balance CPU time (cpu) instead of queries per second (qps) among stores in a cluster. This is done by setting kv.allocator.load_based_rebalancing.objective='cpu'. kv.allocator.cpu_rebalance_threshold is also added, similar to kv.allocator.qps_rebalance_threshold to control the target range for store CPU above and below the cluster mean. #96127
  • The load-based splitter now supports using request CPU usage to split ranges. This is introduced with the previous cluster setting kv.allocator.load_based_rebalancing.objective, which when set to cpu, will use request CPU usage. The threshold above which CPU usage of a range is considered for splitting is defined in the cluster setting kv.range_split.load_cpu_threshold, which has a default value of 250ms. #96128
  • Added the flag --disable-max-offset-check to disable node self-termination when it detects clock skew with the rest of the cluster beyond --max-offset. The operator assumes responsibility for ensuring that real clock skew never exceeds --max-offset. #96141

DB Console changes

Bug fixes

  • Fixed a bug where casting a TIMETZ to an ARRAY results in displaying second offsets, even if they are zero. #96583
  • Allowed ALTER TABLE .. ADD/DROP CONSTRAINT .. NOT VALID and VALIDATE CONSTRAINT .. to behave consistently with PostgreSQL. Previously, the VALIDATE CONSTRAINT would fail and cause the whole statement to fail.#96648
  • Resolved the TIMESTAMPTZ to match PostgreSQL. We previously included the minute/second offset for TIMESTAMPTZ in certain places when casting it to STRING, even when they were zero. #96833
  • Resolved using negative years instead of BC when casting a TIMESTAMPTZ to a STRING. #96833
  • Fixed the SHOW GRANTS FOR public command so it works correctly. Previously, this would return an error saying that the public role does not exist. #96957
  • Statement source (square bracket) syntax is no longer allowed in user-defined functions. Prior to this fix, using this syntax in a UDF would cause a panic. This restriction will be lifted in the future. #96824

Performance improvements

  • The execution of multiple FOREIGN KEY and UNIQUE constraint checks have been parallelized in some cases. As a result, these checks should be completed faster, particularly in multi-region environments where the checks require cross-region reads. #96123

Contributors

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

  • Ivan Gorbachev (first-time contributor)

v23.1.0-alpha.2

Release Date: February 13, 2022

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.

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-unstable:v23.1.0-alpha.2

Changelog

View a detailed changelog on GitHub: v23.1.0-alpha.1...v23.1.0-alpha.2

Backward-incompatible changes

  • CockroachDB now supports sharing storage ranges across multiple indexes/tables. As a result, there is no longer a guarantee that there is at most one SQL object (e.g., table/index/sequence/materialized view) per storage range. Therefore, the columns table_id, database_name, schema_name, table_name and index_name in crdb_internal.ranges and .ranges_no_leases have become nonsensical: a range cannot be attributed to a single table/index anymore. As a result:

    • The aforementioned columns in the crdb_internal virtual tables have been removed. Existing code can use the SHOW RANGES statement instead, optionally using WITH KEYS to expose the raw start/end keys.
    • SHOW RANGES FROM DATABASE continues to report one row per range, but stops returning the database / schema / table / index name.
    • SHOW RANGES FROM TABLE continues to report one row per range, but stops returning the index name. Suggested replacements:
      • Instead of: SELECT range_id FROM crdb_internal.ranges WHERE table_name = 'x', use: SELECT range_id FROM [SHOW RANGES FROM TABLE x]
      • Instead of SELECT range_id FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2 (variable / unpredictable table name or ID), use: SELECT range_id FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES] WHERE table_name = $1 OR table_id = $2
      • Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = 'x', use: SELECT raw_start_key FROM [SHOW RANGES FROM TABLE x WITH KEYS]
      • Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2 (unpredictable / variable table name or ID), use: SELECT raw_start_key FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES, KEYS] WHERE table_name = $1 OR table_id = $2 #93644
  • The format of the columns start_key and end_key for SHOW RANGES FROM DATABASE and SHOW RANGES FROM TABLE have been extended to include which table/index the key belongs to. This is necessary because a range can now contain data from more than one table/index. #93644

  • The format of the columns start_key and end_key for SHOW RANGE ... FOR ROW has been changed to be consistent with the output of SHOW RANGES FROM INDEX. #93644

  • The output of SHOW RANGES no longer includes range_size, range_size_mb, lease_holder, or lease_holder_localities by default. This ensures that SHOW RANGES remains fast in the common case. Use the new option WITH DETAILS to include these columns. #93644

  • If a SQL database is created with a name that starts with cluster:... (e.g., CREATE DATABASE "cluster:foo", clients will no longer be able to connect to it directly via a pre-existing URL connection string. The URL will need to be modified in this case. For example:

    • Previously: postgres://servername/cluster:foo; now: postgres://servername/cluster:foo&options=-ccluster=system
    • This syntax selects the tenant named system and then the database named cluster:foo inside it. When the -ccluster:system option is not specified, cluster:foo in the database name position is interpreted as a request to connect to a tenant named foo, which likely does not exist. Connections to databases whose name does not start with cluster: (the most common case) are not affected by this change. #92580
  • Changefeeds using "preview" expressions (released in v22.2.0) and that access the previous state of the row using the cdc_prev() function will no longer work and will need to be recreated with new syntax. #94429

  • Fixed a bug where, when server.identity_map.configuration was used, CockroachDB did not verify the client-provided username against the target mappings. Note that this means that the client must now provide a valid DB username. This requirement is compatible with PostgreSQL; it was not previously required by CockroachDB but it is now. This does not apply when identity maps are not in use. #94915

  • Previously, the type of the replicas, voting_replicas,non_voting_replicas and learner_replicas in crdb_internal.ranges were overridden to INT2VECTOR causing incompatible indexing between .ranges and .ranges_no_leases. Now the types of those columns in the two tables are set to INT[]. #96287

Security updates

  • Added an option to re-enable "old" cipher suites for use with very old clients. Fixes issue #1989. #95091
  • Previously, the ENCRYPTION_PASSPHRASE option passed to RESTORE would appear as 'redacted'. It now appears as '******' which is consistent with SHOW BACKUP and BACKUP. #95562

General changes

The garbage collection TTL previously defaulted to 25h. This value was configurable using ALTER RANGE DEFAULT CONFIGURE ZONE USING gc.ttlseconds = <value>, but it was also possible to scope to specific schema objects using ALTER {DATABASE,TABLE,INDEX} CONFIGURE ZONE USING .... The GC TTL value determined how long overwritten values were retained before being garbage collected.

With CockroachDB v23.1, the RANGE DEFAULT value is lowered to 4h but only for freshly created clusters. When existing clusters upgrade to this release, CockroachDB will respect whatever value was in use before the upgrade for all schema objects. This value will therefore be 25h if the GC TTL was never altered, or some specific value if the GC TTL had been set explicitly. Full cluster backups taken on earlier version clusters, when restored to clusters that started off at v23.1, will use the GC TTL recorded in the backup image.

Cockroach Labs has found the 25h value to translate to higher-than-necessary storage costs, especially for workloads where rows are deleted frequently. It can also make for costlier reads with respect to CPU since we currently have to scan over overwritten values to get to the one of interest. Finally, we've also observed cluster instability due to large unsplittable ranges that have accumulated an excessive amount of MVCC garbage.

We chose a default of 25h originally to accommodate daily incremental backups with revision history. But with the introduction of scheduled backups introduced in v22.2, we no longer need a large GC TTL. Scheduled backups "chain together" and prevent garbage collection of relevant data to ensure coverage of revision history across backups, decoupling it from whatever value is used for GC TTL. The GC TTL determines how far back AS OF SYSTEM TIME queries can go, which now if going past now()-4h, will start failing informatively. To support larger windows for AS OF SYSTEM TIME queries, users are encouraged to pick a more appropriate GC TTL and set it using ALTER ... CONFIGURE ZONE using gc.ttlseconds = <value>. The earlier considerations around storage use, read costs, and stability still apply. #93836

Enterprise edition changes

  • The changefeed.active_protected_timestamps.enabled cluster setting has been removed and is now always treated as if it was true. #89975
  • Improved changefeed expressions logic to rely on the optimizer to evaluate star expansion. #93979
  • Changefeed expressions now support system columns. #93979
  • Changefeed expressions now have access to the cdc_prev tuple which contains the previous state of the row. #94429
  • Changefeed expressions now support non-volatile user defined functions (UDFs). #94429
  • Changefeed transformations (e.g., CREATE CHANGEFEED ... AS SELECT ...) no longer require the schema_change_policy=stop option. #94653
  • Changefeed transformations introduced in the v22.2 release in preview mode are no longer experimental. This feature can now be considered to be fully production-ready. #94806
  • The CREATE EXTERNAL CONNECTION statement now supports URIs with the prefixes azure, gs, gcpubsub, http, https, webhook-https, nodelocal, s3, and kafka for use by changefeeds. #86061
  • The CONTROLCHANGEFEED role option will be deprecated in the future (see issue #94757). With this change, usages of the CONTROLCHANGEFEED role option will come with a deprecation warning. Its existing behavior remains the same. The SELECT and CHANGEFEED privileges will be used for changefeeds henceforth:
    • The SELECT privilege on a set of tables allows a user to run core changefeeds against them.
    • The CHANGEFEED privilege on a set of tables allows a user to run enterprise changefeeds on them, and also manage the underlying changefeed job (ie. view, pause, cancel, and resume the job). Notably, a new cluster setting changefeed.permissions.enforce_external_connections is added and set to false by default. Enabling this setting restricts users with CHANGEFEED on a set of tables to create enterprise changefeeds into external connections only. To use a given external connection, a user typically needs the USAGE privilege on it. Note that ALTER DEFAULT PRIVILEGES can be used with both the CHANGEFEED and SELECT privileges to assign coarse-grained permissions (i.e., assign permissions to all tables in a schema rather than manually assign them for each table). #94796
  • Changefeeds created/altered with a metrics_label set while server.child_metrics.enabled is set to false will now provide the user a notice upon creation. #94948
  • Fix a bug in ALTER CHANGEFEED that would panic when altering changefeeds to remove a table that has already been dropped. #95739
  • Reduced the default size of scan RPC replies to improve cluster stability during changefeed accounting for transient memory. #95798
  • Changefeed expressions now support the changefeed_created_timestamp function. #95179
  • Increased the default changefeed.memory.per_changefeed_limit cluster setting to 1/2GiB. This should result in changefeeds being able to produce larger files. #96340
  • The confluent_schema_registry URI for avro changefeeds now supports CLIENT_CERT and CLIENT_KEY parameters. #96510

SQL language changes

  • Added the pg_get_function_arguments built-in function. This returns the argument list (with defaults) necessary to identify the function with a given OID. #93675
  • Added voting_replicas and non_voting_replicas columns to the output of SHOW RANGE and SHOW RANGES statements. #93513
  • 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: the INVISIBLE alias is not supported for NOT VISIBLE columns. #93750
  • The system.table_statistics table now contains a column called fullStatisticsID to store an id referencing the full table statistic the partial statistic was derived from. #93751
  • Ordinal column references (e.g., SELECT @1, @2 FROM t) are now deprecated. By default, statements using this syntax will now result in an error. If desired, such statements can be allowed using the session setting SET allow_ordinal_column_references=true. Support for ordinal column references is scheduled to be removed in upcoming version v23.2. #93754
  • The optimizer will now use table statistics that are merged combinations of the newest partial statistic and latest full statistic collection. And, if forecasting is enabled, the merged statistic will be used in the forecast. #91933
  • Added the column indexes_usage and the index indexes_usage_idx on value on the table system.statement_statistics. #93089
  • Add the log_timezone session variable, which is read-only and always UTC. #94123
  • Added Two new virtual tables crdb_internal.index_spans and .table_spans, which list the logical keyspace used by each index/table. #93644
  • The following new statements are introduced:
    • SHOW RANGES FROM CURRENT_CATALOG and SHOW RANGES without a parameter: functions as an alias for SHOW RANGES FROM DATABASE on the session's current database.
    • SHOW RANGES FROM DATABASE ... WITH TABLES: reports at least one row per table. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple tables.
    • SHOW RANGES FROM DATABASE ... WITH INDEXES: reports at least one row per index. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes.
    • SHOW RANGES FROM TABLE ... WITH INDEXES: reports at least one row per index. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes.
    • SHOW CLUSTER RANGES [ WITH { INDEXES | TABLES } ]: reports ranges across the entire cluster, including ranges that do not contain table data. The behavior of WITH INDEXES and WITH TABLES is the same as SHOW RANGES FROM DATABASE. Additionally, the following new options have been added to the SHOW RANGES statement: - WITH KEYS: produces the raw bytes of the start/end key boundaries.
    • WITH DETAILS: produces more details, using computations that require extra network roundtrips. This option will make the operation slower overall.
    • WITH EXPLAIN: produces the text of the SQL query used to run the statement. #93644
  • Implemented the pg_timezone_names pg_catalog table, which lists all supported timezones. #94122
  • Improved the performance of trigram operations. #93757
  • Previously, CockroachDB would crash if a user creates a user-defined function (UDF) whose function signature includes a implicit record type (essentially a table) which has a column using a user defined enum type. The root cause was a hydration deadloop when looking up descriptors during hydration. This fix adds a new flag to avoid hydration in order to avoid the deadloop. #94106
  • Previously, error messages for missing users sometimes had different forms. This is now unified in the form role/user "user" does not exist. #94677
  • Added cluster settings to control tenant capabilities relying on KV admin functions. #94314
  • User-defined functions (UDFs) with subqueries in the body of the function are now supported. #94962
  • Previously, setting a table's locality was not allowed if the table contained any hash sharded index. This restriction is now removed. #94436
  • Users can now add a super region when creating a database. #93939
  • COPY now logs an error during the insert phase on the SQL_EXEC logging channel. #95038
  • Added a new REDACT flag to EXPLAIN which causes constants, literal values, parameter values, and any other user data to be redacted in explain output. Redacted statement diagnostics bundles can now be collected with EXPLAIN ANALYZE (DEBUG, REDACT). #94950
  • Added two new virtual tables displaying execution insights for transactions:
    • crdb_internal.cluster_txn_execution_insights
    • crdb_internal.node_txn_execution_insights #94720
  • Some queries which previously resulted in the error "could not decorrelate subquery" now succeed. #95234
  • If copy_from_retries_enabled is set, COPY is now able to retry certain safe circumstances: namely when copy_from_atomic_enabled is false, there is no transaction running COPY and the error returned is retriable. This prevents users who keep running into TransactionProtoWithRefreshError from having issues. #95275
  • Fixed the databases list API when the database name has special characters. #95209
  • The pgwire protocol implementation can now accept arguments of the JSON[] type (oid=199). Previously, it could only accept JSONB[] (oid=3804). Internally, JSON[] and JSONB[] values are still identical, so this change only affects how the values are received over the wire protocol. #94705
  • CPU time spent during SQL execution is now visible in the output of queries run with EXPLAIN ANALYZE. This measure does not include CPU time spent while serving KV requests, and CPU time is not shown for queries that perform mutations or for plans that aren't vectorized. This can be useful for diagnosing performance issues and optimizing SQL queries. #93952
  • The SHOW GRANTS ON EXTERNAL CONNECTION and SHOW SYSTEM GRANTS statements now use a column name of privilege_type rather than privilege. For external connections, the name column has been changed to connection_name. This makes the commands consistent with other SHOW GRANTS commands. #95532
  • The SHOW INDEXES statement will now show the expression used to define an index, if one was used. #95413
  • It is now possible to run efficient tsvector @@ tsquery searches when there is an inverted index on the tsvector column being searched. #93769
  • Added a cpuNanos field to the statistics column of the crdb_internal.statement_statistics and system.statement_statistics tables that reports the amount of CPU time in nanoseconds during SQL execution for queries that track CPU time. #95639
  • Previously, SHOW BACKUP options would get parsed as kv_options, which meant that a user could not pass multiple values to a SHOW BACKUP option, causing feature gaps in SHOW BACKUP relative to BACKUP and RESTORE. This patch rewrites the show backup option parser, closing the following feature gaps:
    • A user can now pass and check multiple KMS URIs in SHOW BACKUP
    • A user can pass locality-aware incremental_locations, allowing a user to also pass the check_files parameter to a locality-aware backup chain that also specifies the backup incremental location. #95562
  • Updated the name of the cpuNanos column to cpuSQLNanos on crdb_internal.statement_statistics and system.statement_statistics #96278
  • Some queries with EXISTS subqueries which previously resulted in the error "could not decorrelate subquery" now succeed. #95883
  • Users can query the crdb_internal.kv_dropped_relation table to see which tables, materialized views and sequences are currently already dropped but have not yet been garbage collected, along with the garbage collection TTL setting that is currently in force. This setting originates from the table's own zone configuration, or from its parent database which it inherits, or in turn from the default zone configuration. These settings are typically set using ALTER TABLE ... CONFIGURE ZONE USING gc.ttlseconds = ...;. #96217
  • Administrators may now call a new built-in function crdb_internal.upsert_dropped_relation_gc_ttl to retroactively set the garbage collection TTL on a table, materialized view, or sequence which has already been dropped. Effectively, this retroactively performs ALTER TABLE ... CONFIGURE ZONE USING gc.ttlseconds = ...;. Note that this statement is prevented from being executed on dropped tables because they can no longer be referenced by name at that point. Usage of this built-in is typically in conjunction with the recently-added crdb_internal.kv_dropped_relations virtual table. For example, garbage collection can be triggered ASAP for all dropped relations by querying: SELECT crdb_internal.upsert_dropped_relation_gc_ttl(id, '1 second') FROM crdb_internal.kv_dropped_relations;. Doing so for all tables in a dropped database requires filtering on the parent_id column, the database name being lost at that point. #96217
  • Allow * expressions in user-defined functions (UDFs). #95710
  • Previously, user-defined functions (UDFs) could be created with any volatility no matter if the function body statements contained any expression which would violate the target volatility. For example, an immutable function might use random() in it. This change added validations to guarantee that all statements in the function body should be as strict as the expected UDF volatility. #96476

Operational changes

  • Updated the metrics queue.gc.info.clearrangesuccess and queue.gc.info.clearrangefailed to include statistics about GC operations that perform ClearRange on parts of the range keyspace. Previously those metrics only included requests to remove range data completely when performing a schema change. #90830
  • The load-based splitter has been redesigned to be more consistent with CPU-based rebalancing rather than QPS-based rebalancing to improve range splits. This redesign is disabled by default currently. #93838
  • The crdb_v2 log format will now always contain a tenant ID in the logging tags for each log entry. This is to distinguish log entries between tenants when logs for multiple tenants are being written. These tenant ID tags will always come first in the list of logging tags, with a tag key of T. For example:
    • For the system tenant (tenant ID = 1), a log entry would look like: I060102 15:04:05.654321 11 util/log/format_crdb_v2_test.go:123 ⋮ [T1,noval,s‹1›] I'm the system tenant
    • For a secondary tenant, where tenant ID = 2, a log entry would look like: I060102 15:04:05.654321 11 util/log/format_crdb_v2_test.go:123 ⋮ [T2,noval,s‹1›] I’m an app tenant #94432
  • The cluster setting storage.value_blocks.enabled, when set to true, writes the values of older versions of the same key to separate value blocks in the same sstable. For workloads that create many versions, this can improve the performance of reads by increasing locality. It can also help scan performance with single versions due to an optimization that avoids a key comparison (useful if the key are not very short). The default value of this setting is currently false. #94634
  • The bytes read from SQL clients prior to authentication are now reported via the metric sql.pre_serve.bytesin. Previously, this was reported via the sql.bytesin metric. #92580
  • Logs formatted with the crdb_v1 log format will now always contain a tag containing the tenant ID that the log entry pertains to. For example, for tenant ID 2, we include the tag T2, as in: W000101 00:00:12.300000 456 somefile.go:136 [T2,nsql?,othertag=somevalue] 2 this is a log message. If decoding an older crdb_v1 log message that does not contain a tenant ID tag, CockroachDB defaults to the primary system tenant. #94777
  • Transaction errors will contain more detailed information in redacted logs. #93760
  • Added the COCKROACH_RAFT_MAX_INFLIGHT_BYTES environment variable, which helps strictly limit inflight traffic from a Raft leader to its followers, particularly in situations when many large messages are sent and significantly exceed COCKROACH_RAFT_MAX_SIZE_PER_MSG * COCKROACH_RAFT_MAX_INFLIGHT_MSGS which is a softer limit. #94692
  • Metrics output via _status/vars now contain tenant labels, allowing the user to distinguish between metrics emitted by the system tenant vs other app tenants identified by their IDs. #94670
  • Log messages for write stalls have been adjusted slightly. #95436
  • Histogram metrics can now optionally use the legacy HdrHistogram model by setting a new environment variable COCKROACH_ENABLE_HDR_HISTOGRAMS to true on CockroachDB nodes. Note that this is not recommended unless users are having difficulties with the newer Prometheus-backed histogram model. Enabling legacy historgrams can cause performance issues with timeseries databases like Prometheus, as processing and storing the increased number of buckets is taxing on both CPU and storage. Note that the legacy HdrHistogram model is slated for full deprecation in upcoming releases. #96029
  • Prometheus histograms will now export more buckets across the board to improve precision & fidelity of information reported by histogram metrics, such as quantiles. This will lead to an increase in storage requirements to process these histogram metrics in downstream systems like Prometheus, but should still be a marked improvement when compared to the legacy HdrHistogram model. If users have issues with the precision of these bucket boundaries, they can set the environment variable COCKROACH_ENABLE_HDR_HISTOGRAMS=true to revert to using the legacy HdrHistogram model instead, although this is not recommended otherwise as the HdrHistogram strains systems like Prometheus with excessive numbers of histogram buckets. Note that HdrHistograms are slated for full deprecation in upcoming releases. #96029
  • In the rare event of a range inconsistency, the consistency checker now saves a storage checkpoint on each storage the range belongs to. Previously, this was a full checkpoint, so its cost could quickly escalate on the nodes that went on running. This change makes the checkpoints partial, i.e., they now only contain the relevant range and its neighbors. This eliminates the time pressure on the cluster operator to remove the checkpoints. #95963
  • The count of new SQL connections is now also reported on sql.pre_serve.new_conns. #92580
  • The count of network bytes sent to report re-authentication errors to a SQL client is now reported via the metric sql.pre_serve.bytesout (instead of sql.bytesout previously). The count of pre-authentication errors is now reported via the metric sql.pre_serve.conn.failures (instead of sql.conn.failures previously). #92580

Command-line changes

  • The TLS parameters to connect to a cockroach demo cluster from SQL clients have been simplified. #94421
  • The password assigned to the demo user in cockroach demo can now be overridden by the env var COCKROACH_DEMO_PASSWORD. This is meant for use in automated tests, when tests cannot be configured to use TLS client certificate authentication using the client certificates in $HOME/.cockroach-demo. #94421
  • cockroach sql and cockroach demo now support an \info client-side command to print the server details again. #94421
  • cockroach demo is now able to preserve open web sessions across restarts of the cockroach demo command. The sessions are saved in the directory ~/.cockroach-demo alongside the TLS certificates and keys. #94455
  • The cockroach debug merge-logs CLI command now supports a new --tenant-ids flag, which can be used to provide the command with a comma-separated list of tenant IDs on which to filter logs. For example, to only include logs for secondary tenants 2 and 3, you can now run: cockroach debug merge-logs ... --tenant-ids=2,3. Note that when processing logs from older cluster versions, where a tenant ID tag may not be present on the log entry, the command will interpret any log without a tenant ID as belonging to the system tenant (tenant ID = 1). #95016
  • The file format used for transient loss of quorum recovery files has changed. It is not possible to use replica info files generated by earlier versions to be used with current and future versions. #93157
  • Workloads that take a --seed argument previously defaulted to 1. Now, they use a randomly generated seed in each run. Users can still pass a custom seed with the --seed flag. #95326
  • Added cpu_time_per_second to the hot-ranges.sh and hot-ranges-tenant.sh utility debug zip scripts. #96213

DB Console changes

  • Secure clusters now show correct login information in the top right corner. #94021
  • The databases filter dropdown in the Statements page now uses the getDatabasesList() API call, resulting in all cluster databases showing up. #93657
  • Added information about the selected plan to the Explain Plan tab under Statement Details. #94719
  • The Databases page and the Databases Details pages now each contain search and filter components, allowing the ability to search and filter through databases and their tables. #92589
  • Graphs can now be clicked on to toggle legend "stickiness" and make the points stop following the mouse. This makes it easier to read dense graphs with many series plotted together. #92949
  • The Statement Fingerprint Details page in the DB Console no longer infinitely loads after 5 minutes. #92596
  • Added a time picker to the Workload Insights Overview pages in the DB Console. #92285
  • Added all three session status options (Active, Closed, and Idle) and an empty results placeholder. #95005
  • Added a list of all statement fingerprints that use a given index to the Index Details page. #94863
  • The DB Console will now clear the tenant cookie on an API call returning a 401 error in order to allow for multitenant login once more. #95020
  • Removed reset sql stats and reset index stats from the DB Console when the user is a non-admin. #95303
  • Remove reset sql stats from the Transactions page for non-admins. #95461
  • Added an insights type filter for the Workload Insights page, and added an execution status filter for the Active Execution pages. #94002
  • Hid the apply option for index recommendation when the user is not an admin. #95516
  • The Database list filter now shows all databases in the cluster on CC console. #95450
  • Hid the filter for the Databases and Database Details pages if both the node and regions dropdowns are also hidden. #95376
  • The introduction of the Key Visualizer makes it easy to identify historical hotspots. To support this new feature, three new cluster settings are introduced:
    • keyvisualizer.enabled: enables the Key Visualizer.
    • keyvisualizer.sample_interval: controls the frequency at which the Key Visualizer collects samples.
    • keyvisualizer.max_buckets controls the maximum number of buckets in a sample. #88353
  • Added a CPU Time chart to the Statement Details page. #95796
  • Added CPU time as a column on the Statement and Transaction tables. #95795
  • Hid the list of used fingerprints per index on the Index Details page for non-admin users. #95997
  • Added write bytes, write keys, read bytes, read keys and cpu statistics to the /hotranges DB Console page. These statistics are the rated average over the last 30 minutes. #95190
  • The loading spinner on the Insights Overview page now only shows when a request is pending and either the data is nullish or invalid (i.e., a new time range selected). #93219
  • Previously, transaction start time for the Transaction Insights page was only available for transaction insights with high contention as the issue. This information is now available for all transaction insights: start time, end time, and time elapsed. #93219
  • Added search, filter and time picker for the list of most-used statement fingerprints on the Index Details page. #96112
  • Added CPU Time to the Statement and Transaction Insights page. #96279
  • Previously, the stale node metrics displayed in the Cluster Overview Nodes table may mislead users in to thinking that they are current values when in fact they are stale. Now these metrics are displayed with a stale tag, allowing for users to be informed about the staleness of the data displayed to them regarding dead nodes. #95868

Bug fixes

  • Fixed a bug where the session_id session variable would not be properly set if used from a subquery. #93748
  • Fixed a bug where CockroachDB could, in rare cases, encounter an internal error when evaluating the crdb_internal.range_stats built-in function (which powers the SHOW RANGES command among other things). The bug was introduced in v22.2.0 and is now fixed. #93868
  • Fixed a bug that could prevent CASE expressions that used placeholder return values from type-checking correctly. #93855
  • Updated the volatility of the hmac, digest, and crypt built-ins to be immutable. #93628
  • Server logs are now correctly fsynced at every syncInterval. #93956
  • The stxnamespace, stxkind and stxstattarget columns are now defined in pg_statistics_ext. #93274
  • 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. #93938
  • This patch fixes JOIN queries involving tables with unique constraints using LIMIT, GROUP BY, and ORDER BY clauses to ensure that the optimizer considers streaming group-by with no TopK operation, when possible. This is often the most efficient query plan. #93858
  • Secure clusters now show correct login information in the top right corner. #94021
  • Previously, CockroachDB could crash in rare circumstances when evaluating lookup and index joins. The bug has been present since the v22.2.0 release. You can also employ a temporary workaround without upgrading to the release with this fix by changing the value of the undocumented cluster setting sql.distsql.use_streamer.enabled to false. #94031
  • The CREATE ROLE, DELETE ROLE, GRANT, and REVOKE statements no longer function when the transaction is in read-only mode. #93991
  • Fixed a bug where, when experimental MVCC range tombstones are enabled (they are 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. #94045
  • Fixed a bug where, in some cases, the start/end key columns of the output of SHOW RANGES was missing. #93644
  • Fixed a bug where trigrams ignored unicode (multi-byte) characters from input strings. #93757
  • 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. #94207
  • Fixed a bug where tables which receive writes concurrently with portions of an ALTER TABLE ... SET LOCALITY REGIONAL BY ROW may fail with an error: duplicate key value violates unique constraint "new_primary_key". This bug was introduced in 22.1. #94151
  • Fixed a bug where CockroachDB could encounter an internal error when evaluating window functions with RANGE window frame mode with an OFFSET PRECEDING or OFFSET FOLLOWING boundary set 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. #94342
  • Previously, 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 future readers. The bug was introduced in 22.2.0, and the temporary workaround without upgrading to a release with this fix is to set the undocumented cluster setting sql.distsql.use_streamer.enabled to false. #94399
  • Record types can now be encoded with the binary encoding of the Postgres wire protocol. Previously, trying to use this encoding could case a panic. #94405
  • Fixed a panic that could be caused when using a SQL cursor to access tables in the crdb_internal schema. #94425
  • Fixed a bug in which RESTORE SYSTEM USERS would fail to restore role options. #94134
  • Fixed a crash that could occur 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. #94440
  • Fixed a recursive CTE expressions which cause internal errors when explicit CASTs of initial expressions to output types are missing. #94581
  • Previously, certain GRANT or REVOKE commands on a user which does not exist would error with the correct PG code. This is now fixed. #94677
  • The pg_proc and pg_type virtual OID indexes no longer incorrectly show cross-db objects. #94339
  • 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. #94894
  • It is now possible to use a directory whose name is "disabled" via --external-io-dir. To actually disable external I/O, use --external-io-dir="". #94985
  • This patch fixes 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, e.g., (CASE WHEN false THEN int_col ELSE 1 END) IN (int_col) AND int_col=3/2. #94791
  • The pronamespace column of the pg_proc table now correctly reports the crdb_internal schema for built-in functions that have the "crdb_internal" prefix. #95029
  • In the SQL shell (cockroach sql / cockroach demo), when using \c/\connect to connect to a different server, CockroachDB would previously report an error if the new server had a different cluster ID. This has been fixed: this situation is merely a warning. #95219
  • When using identity maps (via server.identity_map.configuration), authentication now correctly verifies that the client-provided username matches at least one of the mappings for the system identity. Previously, the client-provided username was incorrectly ignored and authentication could fail if the first candidate mapping did not result in a valid DB username. #94915
  • The prokind column of pg_catalog.pg_proc is now populated properly. #95289
  • pg_catalog.pg_description and pg_catalog.obj_description() are now able to retrieve the descriptive help for built-in functions. #95294
  • Fixed a crash that could occur when formatting a tuple with an unknown type. #95009
  • Fixed a bug where a DNS lookup was performed during gossip remote forwarding while holding the gossip mutex. This could cause processing stalls if the DNS server was slow to respond. #95426
  • Fixed a bug where CLOSE ALL would not respect the "ALL" flag and would instead attempt to close a cursor with no name. #95414
  • Fixed a bug where default expressions could have a type which differs from the type of the column as long as the expression's type could be cast in an assignment context, which could lead to a panic during a backfill if such a default expression was added to a new column. This bug was introduced in v22.2.0. #95398
  • DB Console features that check for the VIEWACTIVITYREDACTED privilege now also account for system-level privileges. #95258
  • CockroachDB now supports receiving regXXX-type values in binary extended protocol. #94355
  • Operations like BACKUP can now reuse a previously created AWS KMS client if the client was created with the same parameters. This addresses the NoCredentialProviders errors on EC2 for backups with long incremental chains. #95026
  • The syntax EXPLAIN (DDL) COMMENT ON is now possible. #95467
  • Fixed a bug where a database RESTORE would not grant CREATE and USAGE on the public schema to the public role. #95466
  • Fixed the formatting of JSON values inside of a SQL array so they no longer have improper quoting. #94705
  • Fixed a bug which could cause expressions with side-effects (e.g., volatile expressions or data-modifying statements like INSERT) in NOT MATERIALIZED CTEs to be executed more than once. This bug was present since NOT MATERIALIZED was first supported in version 20.2.0. #95454
  • Fixed pg_get_indexdef so that it shows the expression used to define an expression-based index. In addition, the function was previously including columns stored by the index, which was incorrect and has now also been fixed. #95413
  • Reduced register session, deregister session, and session cancel query contention. #95553
  • Fixed a bug that could cause arguments of a COALESCE statement to be evaluated when previous arguments always evaluated to non-NULL values. This bug could cause query errors to originate from arguments of a COALESCE that should have never been evaluated. #95599
  • Fixed the array_to_string built-in function so that nested arrays are traversed without printing 'ARRAY' at each nesting level. #95802
  • Fixed a bug where using the COPY statement to copy data into a column with collated strings would result in an error similar to internal error: unknown type collatedstring. #95894
  • The content of column_default in information_schema.columns no longer has type annotations. #94153
  • Fixed a long-standing bug that caused incorrect parsing of double-quoted identifiers separated by one or more newlines. #96019
  • Fixed a rare internal errors in LATERAL queries with redundant function calls. #96048
  • Fixed an internal error which may occur in the SHOW RANGE FROM TABLE statement when the FOR ROW clause specifies a BYTE literal and the corresponding column data type is BIT. #96002
  • Fixed an internal errors which may occur on some AS OF SYSTEM TIME expressions. #96113
  • Fixed a bug where a node with a disk stall would continue to accept new connections and preserve existing connections until the disk stall abated. #96371
  • Fixed a bug where a RESTORE flow could hang indefinitely in the face of a context cancellation, manifesting as a stuck restore job. #96302
  • Fixed a bug where the NOSQLLOGIN system-level privilege had a bug that made CockroachDB ignore it entirely, so it had no effect. The bug was introduced in v22.2.0-alpha.1. The NOSQLLOGIN role option is unaffected by this bug. #96466
  • The compatibility scalar functions in information_schema are now listed in the proper namespace in pg_catalog.pg_proc. #96562
  • Fixed a bug in which the CREATE SCHEDULE statement would not properly handle a placeholder for the revision_history option. #95675

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. #93673
  • Significantly reduced CPU usage of the underlying gossip network in large clusters. #89613
  • Refactored the query logic when fetching database index recommendations for the DatabaseDetails API endpoint, greatly reducing the query time and cost, particularly for large schemas. #93937
  • Improved performance when populating crdb_internal.default_privileges. #94247
  • Some types of user-defined functions (UDFs) are now inlined in query plans as relation expressions, which speeds up their evaluation. UDFs must be non-volatile and have a single statement in the function body to be inlined. #92955
  • Improved the performance of pg_{function,table,type}_is_visible. #94339
  • Long chains of incremental backups and restore of such chains will now allocate less memory during the unmarshaling of metadata. #93997
  • Extended the RPC compression encoding with a length prefixing format, allowing more efficient decompression on receivers. #93871
  • When planning lookup joins with a LIMIT clause from a REGIONAL BY ROW input table, the optimizer will now explore a locality-optimized-search plan of two lookup joins to possibly avoid high latency of reading rows in a remote region. #93377
  • Improve the performance of the SHOW FUNCTIONS statement. #94771
  • Improved the performance of several PostgreSQL compatibility built-ins. #94771
  • In some cases, when planning an inverted zigzag join, the optimizer can now detect whether it is necessary to re-apply the filter after the zigzag join. If it is not necessary, the optimizer can produce a more efficient plan. #95638
  • Fixed a bug which could lead to very slow drop when tables or views have a very large number of columns (greater than 1000). #95850
  • In 22.2, CockroachDB introduced support for DISCARD TEMP and made DISCARD ALL actually discard temp tables. This implementation ran expensive logic to discover temporary schemas rather than consulting in-memory data structures. As a result, DISCARD ALL, which is issued regularly by connection pools, became an expensive operation when it should be cheap. This problem is now resolved. #95876
  • In 22.2, logic was added to make SET SESSION AUTHORIZATION DEFAULT not a no-op. This implementation used more general code for setting the role for a session which made sure that the role exists. This improves the performance of the DISCARD ALL and SET SESSION AUTHORIZATION DEFAULT statements. #95876
  • The optimizer now plans inverted index scans for queries that filter by JSON fetch value operators (->) with integer indices, e.g, json_col->0 = '{"b": "c"}'. #95848
  • The optimizer now plans inverted index scans for queries that filter by JSON fetch value operators (->) with integer indices alongside the "contains" or the "contained by" operators, e.g, json_col->0 @> '{"b": "c"}' or json_col->0 <@ '{"b": "c"}'. #96202
  • Added delegated snapshots which can reduce WAN traffic for snapshot movement. If there is another replica for this range with a closer locality than the delegate, the leaseholder will attempt to have that delegate send the snapshot. This is particularly useful in the case of a decommission of a node where most snapshots are transferred to another replica in the same locality. #83991
  • The Raft proposal pipeline has been optimized to reduce interference between Raft proposals. This improves average and tail write latency at high concurrency. #94165

Build changes

  • The native binary for Cypress is now only downloaded and installed when UI end-to-end tests are run, instead of eagerly downloading it on all platforms at build-time. This restores the ability for non-{Windows, Darwin, Linux} platforms like FreeBSD and illumos to build CRDB without modifications, which broke in the initial 22.2 release. #93800
  • Required NPM dependencies are now fetched from a Google Cloud Storage bucket managed by Cockroach Labs instead of from a git submodule committed in-tree. #94152

Contributors

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

  • HighPon (first-time contributor)
  • Vivian Kong

v23.1.0-alpha.1

Release Date: December 19, 2022

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.

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-unstable:v23.1.0-alpha.1

Backward-incompatible changes

  • Replaced the cdc_prev() function in favor of a cdc_prev tuple. This is an incompatible change that may break changefeeds that use the previous cdc_prev() function. #85177
  • SHOW RANGES FOR TABLE now includes rows for all indexes that support the table. Prior to this change, SHOW RANGES FOR TABLE foo was an alias for SHOW RANGES FOR INDEX foo@primary. This was causing confusion, as it would miss data for secondary indexes. It is still possible to filter to just the primary index using SHOW RANGES FOR INDEX foo@primary. The statement output now also includes the index name. #93545

Security updates

  • It is now possible to create TLS client certificates for identity principals that are not a valid SQL username using cockroach cert create-client and the new flag --disable-username-validation. This is meant to be used in combination with the cluster setting server.identity_map.configuration and the map option in HBA rules (server.host_based_authentication.configuration). To test this feature, use cockroach sql. Cockroach Labs recommends passing the username separately from the connection URL. #90439
  • There is a new SQL session variable system_identity defined to contain the identity principal presented by the SQL clients during the initial connection. This may be different from session_user when the identity was mapped, either using GSSAPI and include_realm=0, or when using an identity map. #90439

General changes

  • Upgraded gRPC to v1.49.0 #88562
  • The connection timeout for cluster-internal connections between nodes has been reduced to 5s to potentially reduce the impact of network issues. Previously, CockroachDB employed a 20s connection timeout. #88625
  • CockroachDB no longer shares a TCP connection for the KV and Gossip subsystems. Each subsystem now uses their own connection, so the total number of outgoing and incoming TCP connections at each node in the cluster will increase by 30 to 50 percent. #88625
  • Bulk operations now log the (sanitized) destinations they are connecting to. For example: backup planning to connect to destination gs://test/backupadhoc?AUTH=specified&CREDENTIALS=redacted. #89531

Enterprise edition changes

  • SHOW CHANGEFEED JOBS no longer reveals Confluent schema registry user information, including a user's secret key. This information is now redacted, meaning it will not be stored in CockroachDB internal tables at all. #86603
  • Changefeeds JSON encoder performance is improved by 50%. #88064
  • Changefeeds, using cloud storage sink, now have better throughput. #88395
  • Added the cluster setting changefeed.event_consumer_workers which allows changefeeds to process events concurrently. #87994
  • Changefeed) exports are up to 25% faster due to uniform work assignment. #88672
  • Changefeeds can 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. #88635
  • When a changefeed is run with the option virtual_columns = "null", the virtual column will be ordered last in each row. #89329
  • For Kafka sinks, you can now add the optional JSON field "Compression" to the 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 protocol to be used when emitting events. #90270
  • Fixed a bug that could cause changefeeds to fail during a rolling restart. #89913
  • Changefeeds now have support for the Parquet format. #89451
  • Changefeeds will now treat all errors, unless otherwise indicated, as retryable errors. #90810
  • CockroachDB now supports passing in the optional external ID when assuming a role. This is done by extending the values of the comma-separated string value of the ASSUME_ROLE parameter to the format <role>;external_id=<id>. Users can still use the previous format of just <role> to specify a role without any external ID. When using role chaining, each role in the chain can be associated with a different external ID. #91040
  • JWT authentication cluster settings can now be modified from within tenants to better support serverless use cases. #92406
  • CDC queries are now planned and evaluated using the SQL optimizer and distSQL execution. The state of the previous row is now exposed as the cdc_prev tuple. #85177
  • Changefeeds no longer require the COCKROACH_EXPERIMENTAL_ENABLE_PER_CHANGEFEED_METRICS environment variable to be set in order to use the metrics_label option. #93423
  • Changefeeds can now be scheduled at intervals specified in crontab notation. #92232

SQL language changes

  • Added the crdb_internal.to_json_as_changefeed_with_flags function to help debug JSON changefeeds. #84509
  • Added the regions column to the SHOW BACKUP command which will output a string of ALTER DATABASE commands if the database is a multi-region database and NULL for everything else. Previously, the user did not have an easy way to see if a backed up database is multi-region. #88136
  • Types with length modifiers can now be used to prefix literals. #82028
  • A new column plan_gist was added to crdb_internal.{node,cluster}_queries representing the compressed logical plan. #88770
  • You can generate easy-to-read CREATE INDEX statements for a table's (secondary) indexes using the SHOW CREATE INDEXES FROM <table_name> and SHOW CREATE SECONDARY INDEXES FROM <table_name> statements. #88861
  • enum_first, enum_last, and enum_range may now take NULL arguments as long as their type can be inferred from the expression. #89124
  • Declarative schema changer support for ALTER PRIMARY KEY statements now extends to tables which have secondary indexes. #86176
  • The backup.restore_span.target_size cluster setting now defaults to 384 MiB. This should reduce the number of ranges created during restore and thereby reduce the merging of ranges that needs to occur after the restore. #89333
  • The transaction_timeout session variable was added. transaction_timeout aborts an explicit transaction when it runs longer than the configured duration. When the timer times out, the current statement is cancelled and the transaction enters an aborted state. This timeout does not have any effect when no statement is being executed, so it should be used with idle_in_transaction_timeout for the best results. #89033
  • The crdb_internal.check_consistency function now does not include the diff between inconsistent replicas, should they occur. If an inconsistency occurs, the storage engine checkpoints should be inspected. This change is made because the maximum range size limit has been increased from 64 MiB to 512 MiB, so inlining diffs in consistency checks does not scale. #89502
  • CockroachDB now shows a hash-sharded check constraint in SHOW CREATE TABLE if it is set to NOT VALID. #89517
  • Added the SHOW FUNCTIONS and SHOW FUNCTIONS FROM <schema> statements, which list user-defined functions. #89557
  • The default value of sql.metrics.statement_details.plan_collection.enabled is now false. #89847
  • CockroachDB now supports executing statements of the form DELETE FROM ... USING. #88974
  • The cluster setting sql.ttl.default_range_concurrency and table storage parameter ttl_range_concurrency are no longer configurable. #89392
  • CockroachDB drops the associated scheduled incremental backup when DROP SCHEDULE or DROP SCHEDULES is called. Previously, whenever a user dropped a scheduled full backup, the corresponding scheduled incremental backup would not be dropped. #89768
  • Added the sql.auth.change_own_password.enabled cluster setting. It 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). #90485
  • The sql.distsql.max_running_flows cluster setting has been removed. #84888
  • The query field in the crdb_internal.node_queries, crdb_internal.cluster_queries, and SHOW QUERIES commands now includes the original comments in the queries. #86968
  • Added a new descriptor_validation session variable which can be set to read_only or off to disable descriptor validation, which may be useful when mitigating or recovering from catalog corruption. #90488
  • CockroachDB now supports using DROP CONSTRAINT, ADD CONSTRAINT to add a new primary key without moving the existing primary key to a secondary index if the primary key name is a reserved SQL keyword. Previously, a constraint already exists error was returned. #90865
  • Added the contention_events column to the cluster_execution_insights table to see which transaction is blocking the specific statement. #90660
  • crdb_internal.scan and crdb_internal.list_sql_keys_in_range return the timestamp for the time at which the value for a key was written, in addition to the raw key and value. #90956
  • Previously, the AS OF SYSTEM TIME value was set at the start of the TTL job (with a TTL cutoff - 30s), but this results in an error 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. CockroachDB now makes the AS OF SYSTEM TIME value relative to when each SELECT query is run (query time - 30s) to prevent this error from happening, but each SELECT query will run against a different table state. This should be ok because if records are missed during one job invocation they should still be picked up the next. #90981
  • Upgrading a cluster to a major release now endow each system tenant's system database with a descriptor_id_seq sequence which will be used to generate new descriptor IDs, as is already the case for non-system tenants. #91205
  • The system.sql_instances table now includes pre-allocated ID entries, where all the fields except id will be NULL. #90427
  • The to_char(timestamp, string) function has been added. #91382
  • The to_char(interval, string) built-in function has been added. #91382
  • The system.table_statistics table now contains a column called partialPredicate to store a predicate for a partial statistic collection. #91248
  • A new NumericStat, idleLat, was introduced to the statistics column of crdb_internal.statement_statistics. This reports the time spent waiting for the client to send the statement while holding a transaction open. Developers may use this stat to identify opportunities for restructuring their apps to reduce contention. #91098
  • The set of supported compression algorithms in compress/decompress built-in functions is expanded to include lz4, snappy, and zstd. #91162
  • A new column database was added to crdb_internal.{node,cluster}_queries and list sessions endpoint. #91629
  • Fixed a bug in the legacy schema changer where comments were not dropped together with the database. #91689
  • The round(decimal) built-in function no longer returns negative 0 for any input. #86106
  • Added an estimate for the number of request units consumed by a query to the output of EXPLAIN ANALYZE for tenant sessions. #89256
  • Enabled forward indexes on arrays. #91762
  • Users can now manually create partial single-column statistics at the extreme values on columns that are prefixes of their index. The output of SHOW STATISTICS now includes a column indicating the partial predicate for a partial statistic, or NULL for a full statistic. #91228
  • A new SQL statement SHOW COMMIT TIMESTAMP has been added. This statement can be used to retrieve the commit timestamp of the current explicit transaction, current multi-statement implicit transaction, or previous transaction. The statement may be used in a variety of settings to maximize its utility in the face of connection pooling. When used as a part of an explicit transaction, the statement implicitly commits the transaction internally before being able to return a causality token. #80848
  • Added support for the pg_blocking_pids built-in function. It is hardcoded to return an empty array because CockroachDB has no equivalent concept of PIDs as in PostgreSQL. #92253
  • Added a list of indexes used by the query on the statistics column on the system.statement_statistics and crdb_internal.statement_statistics tables. The format is tableID@indexID. #92351
  • Added a list of used indexes (with the format tableID@indexID) to the sampled query telemetry log. #92464
  • A new NumericStat, idleLat, was introduced to the statistics column of crdb_internal.transaction_statistics. It reports the time spent waiting for the client to send statements while holding a transaction open. #92695
  • Added an in-memory-only evaluation of tsvector and tsquery datatypes and the @@ matches operator. #90842
  • Implemented the ALTER TABLE ... ADD CHECK statement in the declarative schema changer. #91153
  • The crdb_internal.ranges{_no_leases} and SHOW RANGES statements now work on secondary tenants. #92131
  • to_char now has caching for parse formats, marking a speedup when running to_char with the same format between sessions. #91564
  • Casts from index name to REGCLASS are now supported. Previously, only table names could be cast to REGCLASS. #90649
  • Added user-defined composite column types. #90491
  • SQL queries running on remote nodes now show up in CPU profiles with distsql.* labels. Currently this includes appname, gateway, txn, and stmt. #92775
  • CockroachDB now permits non-indexed storage of tsvector and tsquery datatypes #92957
  • Implemented the parse_ident built-in function, which 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. #93300

Operational changes

  • Reduced the length of the raft.process.handleready.latency metric help text to avoid it being rejected by certain Prometheus services. #87166
  • Logs produced by increasing the vmodule setting for s3_storage are now directed to the DEV channel rather than STDOUT. #88798
  • Added observability for when load-based splitting cannot find a key to indicate the reasons why the load splitter could not find a split key, which provides more insight into why a range is not splitting easier. #88720
  • Added five new fields to the sampled_query telemetry events:
    • ScanCount: Number of scans in the query plan.
    • ScanWithStatsCount: Number of scans using statistics (including forecasted statistics) in the query plan.
    • ScanWithStatsForecastCount: Number of scans using forecasted statistics in the query plan.
    • TotalScanRowsWithoutForecastsEstimate: Total number of rows read by all scans in the query, as estimated by the optimizer without using forecasts.
    • NanosSinceStatsForecasted: The greatest quantity of nanoseconds that have passed since the forecast time (or until the forecast time, if it is in the future, in which case it will be negative) for any table with forecasted stats scanned by this query. #88539
  • Added a new debug tool to allow for decrypting files in a store using encryption-at-rest. This tool is intended for use while debugging, or for providing debug artifacts to Cockroach Labs to aid with support investigations. It is intended to be run "in-situ" (i.e., on site), as it prevents having to move sensitive key material. #89668
  • Added a new command that can be used by an operator to list the files present in the encryption-at-rest file registry. #89873
  • Release version binaries can now be instructed via the enviroment variable COCKROACH_FORCE_DEV_VERSION to override their cluster version support to match that of development builds, which can allow a release binary to be started in a cluster that is run (or has previously run) a development build. #90002
  • The consistency check failure message is now more informative, and suggests a few actions that operators should perform in the unlikely event a failure occurs. #89899
  • Updated metric descriptions of rebalancing.* to include the recording period. #90619
  • CockroachDB now prioritizes non-voters in voter additions, meaning that when selecting a store to add a voter on (in the allocator), CockroachDB will prioritize candidate stores that contain a non-voter replica higher. This reduces the number of snapshots that need to be sent over the WAN. #89650
  • CockroachDB now uses response data rather than just the request span in the load-based splitter to pass more accurate data about the keys iterated over to the load splitter to find a suitable split key, enabling the load splitter to find a split key under heavy range query workloads. #89217
  • Added the replicas.leaders_invalid_lease metric, which indicates how many replicas are Raft group leaders but holding invalid leases. #91179
  • 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. The JSON response contains the results. This endpoints works identically as on a non-tenant server, except that it naturally scopes to the target tenant for SQL execution. #91323
  • The cluster settings server.web_session.purge.period and server.web_session.purge.max_deletions_per_cycle, which were specific to the cleanup function for system.web_sessions, have been replaced by server.log_gc.period and server.log_gc.max_deletions_per_cycle which apply to the cleanup function for system.eventlog, system.rangelog and system.web_sessions equally. #90789
  • The cluster setting server.web_session.auto_logout.timeout has been removed. #90789
  • Generating a debug.zip for a tenant server will now include logs in the ZIP file. #91604
  • Splunk dashboard templates are available in the public repository under /monitoring/splunk-dashboard/. #92330
  • The network timeout for RPC connections between cluster nodes has been reduced from 3 seconds to 2 seconds, with a connection timeout of 4 seconds, in order to reduce unavailability and tail latencies during infrastructure outages. This can now be changed by setting the COCKROACH_NETWORK_TIMEOUT environment variable, which defaults to 2s. #92542
  • The Raft election timeout has been reduced from 3 seconds to 2 seconds, and the lease interval from 9 seconds to 6 seconds, with a corresponding reduction in the node heartbeat interval from 4.5 seconds to 3 seconds. This reduces the period of unavailability following leaseholder loss, but places tighter restrictions on network latencies (no more than 500ms roundtrip time). This can be adjusted by setting the COCKROACH_RAFT_ELECTION_TIMEOUT_TICKS environment variable, which now defaults to 10 and will scale all of these intervals proportionally. #91947
  • The RPC heartbeat and gRPC keepalive ping intervals have been reduced to 1 second to detect failures faster. This is adjustable via the new COCKROACH_PING_INTERVAL environment variable. The timeouts remain unchanged. #93399

Command-line changes

  • The interactive SQL shell now retains a maximum of 1000 entries. There was no limit previously. #88173
  • The deprecated CLI command debug unsafe-remove-dead-replicas has been removed. Use debug recover instead. #89150
  • The \df metacommand was added to the SQL shell, which will list all user-defined functions in the current database. #89557
  • In the kv workload, you can now enable --splits with the --sequential flag and adjust splitting to uniformly partition the keyspace. #90000
  • Added the --insert-count flag to insert rows before the kv workload begins. #90055
  • CockroachDB will now include recommended remediation actions alongside log messages for some errors. #82891
  • The input syntax of \set is now more flexible: it is now more accepting of space characters in various positions of the syntax and it supports quoted values, e.g., via \set prompt1 "a b c". #90520
  • cockroach demo --global will now start up more quickly. The latency that will be injected will not be injected until after the initial cluster is set up internally. #92231
  • The engine used as line editor in the interactive shell (cockroach sql and cockroach demo) has been updated. It includes numerous bug fixes and new features. The previous engine can still be accessed by setting the COCKROACH_SQL_FORCE_LIBEDIT environment variable to true. This support will be removed in a later version. #86457
  • The interactive SQL shell now supports an advanced debug mode for troubleshooting when --debug-sql-cli is specified on the command line. The debug mode can be enabled with Ctrl+@ or Ctrl+_ (Ctrl+space on macOS). #86457
  • The following fields have been redacted and were added to the redacted debug zip:

    • crdb_internal.create_statements:
      • create_statement
        • create_nofks
      • alter_statements (each element is redacted)
    • crdb_internal.create_function_statements:
      • create_statement
    • crdb_internal.{node,cluster}_distsql_flows:
      • stmt
    • crdb_internal.{cluster,node}_sessions:
      • last_active
      • active_queries
    • crdb_internal.{cluster,node}_queries:
  • The interactive SQL shell now supports a rudimentary form of tab completion to input the name of SQL objects and functions. #87606

  • The command-line flag --empty to cockroach demo is not marked as deprecated anymore; it is more convenient than --no-example-database. However, the latter remains supported as an alias. #93255

  • The command-line flags --logtostderr, --log-file-verbosity, --no-color, --redactable-logs, --log-file-max-size, --log-group-max-size, --log-dir, --sql-audit-dir are not marked as deprecated anymore; instead, they are defined as convenience aliases for various --log specifications. #93255

  • cockroach demo now supports --pid-file for symmetry with cockroach start. #93343

  • The debug utility script hot-ranges.sh partitions output by statistics: queries_per_second, writes_per_second, read_bytes_per_second, write_bytes_per_second. It also decreased the number of ranges shown under each heading from 20 to 10. #93528

DB Console changes

  • The High Contention Time insight description now accurately reflects the event's contention duration in the DB Console. #89035
  • Overview and Explain Plan tabs were added to the Active Statement Details page. #89021
  • The Apply button was added on the Table Details page (DB Console only) when there is a recommendation to drop an unused index. #90070
  • Overview and Explain Plan tabs were added to the Statement Insight Details page. #90250
  • The Jobs Page now includes a column picker. #89678
  • The fingerprint ID values for statements and transactions on the Insights pages are links that open the respective details page on the time period of the execution of that statement or transaction. #90403
  • Requests to fetch table and database statistics now have limited concurrency. This may make loading the Databases page slower, but in return should result in making those pages less disruptive. #90210
  • The Transaction filter label on the SQL Activity page was fixed. #91255
  • The metric graph tooltip styling was fixed to prevent content collapse. #91290
  • Index recommendations were added to the Statement Active Execution Details page, and the plan gist was added as the first line of the explain plan. #91629
  • Transaction insights pages now show insights about slow execution with unknown causes, index recommendations, and failed executions. The following fields have also been added on the Details page, but are not available for transactions where the insight is "High Contention" (i.e., user name, session ID, rows processed, rows read, rows written, retries, last retry reason, full scan, and transaction priority). #91698
  • The fingerprint ID in hex format was added to the Statement Details page and Transaction Details page. #91885
  • The contention time, schema, database, table, and index info was added to the Insights Statement Details page. #91668
  • The query column in the insight recommendations table was removed. Instead, the statement is included in the description if the transaction being reported has multiple statements. #91955
  • Graphs on the Metrics page now downsample using max value instead of average. Previously, zooming out on a graph would cause any spikes in the graph to smooth out, potentially hiding anomalies. These anomalies are now visible even when looking at a zoomed out interval. #92017
  • The Statement Execution and Planning Time chart on the Statement Fingerprint page now includes a third value ("Idle") representing the time spent by the application waiting to execute this statement while holding a transaction open. #92284
  • A list of used index per explain plan was added, under the Explain Plan tab on the Statement Details page, with links to the table or index details pages. #92463
  • The Insights pages in the DB Console now show the seconds and milliseconds for all timestamp values. #92571
  • Links were added on the fingerprint ID in the High Contention table on the Transaction Insights Details page. #92612
  • The following new charts were added to the Metrics page, under SQL view: Service Latency: SQL Statements, 99.9th percentile and Service Latency: SQL Statements, 99.99th percentile. #92591
  • Renamed the chart on the Statement Details page from Statement Execution and Planning Time to Statement Times. #92765
  • The Transaction resource usage card on the Transaction Fingerprint page now includes an "Idle latency" row, representing the time spent by the application performing other work while holding this transaction open. #92951
  • The Databases table page now displays all the grants in a single row per user. #92871
  • Added a goroutine scheduling latency graph to the Overload dashboard in the DB Console. It shows what the per-node p99 scheduling latency is for goroutines. #93217
  • Added a top-level dropdown in the DB Console which lists tenants the user has logged into. If the cluster is not a multi-tenant host, the dropdown is not displayed. #92694
  • CockroachDB now prevents polling /settings, /nodes_ui, and /cluster endpoints on incorrect login. #93211
  • The Statement and Transaction pages for tenant clusters gained region columns and filters for multi-region tenants. #92357

Bug fixes

  • The flag --sql-advertise-addr now properly works even when the SQL and RPC ports are shared (because --sql-addr was not specified). Note that this port sharing is a deprecated feature in v22.2. #87412
  • 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. #87562
  • Fixed a bug that existed from before v21.1 that could cause an internal error when executing a query with a limit ordering on the output of a window function. #87320
  • Fixed an incorrect default value of cloudstorage.gs.chunking.retry_timeout to 60 seconds #87817
  • Fixed a bug in pg_catalog tables that could result in an internal error if a schema is concurrently dropped. #88568
  • Fixed a bug that caused ALTER CHANGEFEED to fail if the changefeed was created with a cursor option and had been running for more than gc.ttlseconds. #88402
  • Fixed a bug that could cause a panic when running a query with EXPLAIN that attempts to order on a non-output column. #88441
  • Fixed 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. #88673
  • Fixes an issue where when a statement bundle was collected for a query that results in an error due to a statement_timeout, the bundle would not be saved. #88080
  • CockroachDB now excludes check constraints of hash-shared indexes from being invalidated when executing IMPORT INTO. #89231
  • CockroachDB now flushes WAL when writing storage checkpoints on consistency checker failures. #89369
  • Fixed optimizer selectivity and cost estimates of zigzag joins in order to prevent query plans from using it when it would perform poorly (e.g., when many rows are qualified). #89261
  • Changefeeds will now never permanently error on a "failed to send RPC" error. #87763
  • Fixed a bug 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. #89504
  • CockroachDB now avoids a source of internal connectivity problems that would resolve after restarting the affected node. #89539
  • CockroachDB now shows the correct value on table stats on UI, when there are no values to show. #89867
  • Charts on the Statement Details page in the DB Console are no longer overlapping. #90014
  • It is now possible to create tables, views, columns, etc. with the name nothing (e.g., CREATE TABLE nothing...) without having to quote the name, like in PostgreSQL. This bug was introduced in CockroachDB v2.0. #89903
  • Fixed detection and erroring out of queries using locality-optimized joins when the 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. #90107
  • Fixed 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. #90007
  • Fixed a bug in changefeed.batch_reduction_retry which resulted in only a single level of retry being able to occur. #90153
  • During JWT based auth, CockroachDB now infers the algorithm type if it is not specified by the JWKS. This enables support for a wider range of keys. #89989
  • Fixed an extremely rare out of bounds crash in the protected timestamp subsystem. #90357
  • 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. #90287
  • Fixed a bug in the DB Console where when the height of the filter was big, you had to scroll to get to the Apply button. #90457
  • Fixed a bug in the DB Console to now send the proper start and end values to the endpoint used on the SQL Activity page so it returns the full hour as described on the UI. #90403
  • Fixed a rare bug where concurrent follower read/split operations could lead to invalid read results. #89886
  • Fixed a bug that could cause UPDATE .. FROM clauses to update the same row multiple times, resulting in incorrect UPDATED row counts and duplicate output rows for statements with a RETURNING clause. The bug only appeared when the target table had a hidden primary key column (e.g., an implicit rowid primary key column). The bug has been present since support for UPDATE .. FROM was added in v19.0. #89780
  • Protected timestamps are now created during index validation. Before, index validation could be starved if it took longer than any GC jobs for a given table. #89540
  • Fixed a bug where SELECT * operations on tables with virtual computed columns undergoing schema changes could potentially fail. #90670
  • Fixed a bug where 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. #90875
  • CockroachDB now ensures changefeeds shut down when one of the aggregator nodes returns an error. #90767
  • Fixed a bug the occurred when attempting to reduce the size of a fixed-size VARCHAR column. #91078
  • Fixed a bug that caused ranges to remain without a leaseholder in cases of asymmetric network partitions. #87244
  • Fixed a bug that would prevent data from a failed restore from being cleaned up quickly. #88342
  • Fixed a bug which, in rare cases, could result in a changefeed missing rows which occur around the time of a split in writing transactions which take longer than the closed timestamp target duration (defaults to 3s). #91116
  • 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. #90924
  • Fixed a bug that prevented the usage of implicit record types for tables in the pg_catalog, information_schema, and crdb_internal schemas. #90924
  • Fixed a bug that could result in transient errors when dropping a database and immediately recreating a database with the same name and connecting to it for use. #91174
  • Fixes 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. #91130
  • Fixed a bug where the experimental scrub command did not handle type descriptors in the database. #91085
  • 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, with the message "input is out of range". #90218
  • Fixed a bug that could result in infrequent progress updates for very large backup or restore jobs. #89971
  • Added leading zeros to fingerprint IDs with less than 16 characters. #91885
  • Fixed a bug causing changefeeds to fail when a value is deleted while running on a non-primary column family with multiple columns. #91870
  • Fixed a bug that existed since before v21.1 where the cgroup memory limit was undetected when using systemd. #91789
  • Fixed a bug that existed since v20.2 that could cause incorrect results in rare cases for queries with inner joins and left joins. #91425
  • Fixed an unhandled error that could happen if ALTER DEFAULT PRIVILEGES was run on the system database. #92075
  • CockroachDB now prevents schema changes on the crdb_internal_expiration table. #91720
  • When configured to true, the sql.metrics.statement_details.dump_to_logs cluster setting no longer causes a mutex deadlock. #92272
  • 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 (for example the Npgsql C# driver). The error messages was "attempted to update job for mutation 2, but job already exists with mutation 1". #92300
  • Fixed the Statement Activity page so that it no longer shows multi-statement implicit transactions as "explicit." #92408
  • Server crashes that occur during startup are now more clearly reported in logs and the standard error output. #91823
  • Fixed incorrect cancellation logic when attempting to detect stuck rangefeeds. #92582
  • Fixed an internal error when comparing a tuple type with a non-tuple type. #92635
  • Fixed incorrect selectivity estimation for queries with OR predicates all referencing a common single table. #89358
  • Added sort setting to tables on the Transaction and Statement Insights Details pages. #92573
  • Fixed an issue where changefeed.emitted_messages would be increased twice per message for changefeed cloud storage sinks. #92685
  • Fixed a bug where attidentity in pg_attribute for the GENERATED BY DEFAULT AS IDENTITY column should be d. #92545
  • 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 v20.2.0 and is now fixed. #92713
  • Fixed a link to index details on the Drop Index Insights in the Cloud Console. #92953
  • Fixed a bug where encoding of ARRAY type to Parquet format would fail in some cases during the EXPORT command. #92948
  • Fixed a rare panic only present in v22.2.0 that occurs when using particular forms of existing statistics in table statistics forecasting. #92707
  • In the presence of several backup files, CockroachDB now speeds up slow listing calls that could manifest as restore queries hanging during execution. #93072
  • Prepared statements that use type hints can now succeed type-checking in more cases when the placeholder type is ambiguous. #92834
  • Fixed a bug where glob patterns that matched no tables in GRANT or REVOKE statements would return an internal error with a confusing message as opposed to the appropriate "no objects matched" error. #93173
  • Fixed a bug where empty COPY commands would not escape after an EOF character or error if encountering a \. with no input. #93100
  • Fixed a bug where in PostgreSQL extended protocol mode it was possible for auto-commits to not execute certain logic for DDL, when certain DML (insert/update/delete) and DDL were combined in an implicit transaction. #93283
  • Fixed the pg_table_is_visible built-in function so it correctly reports visibility of indexes based on the current search_path. #90649
  • Fixed 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. #92825
  • The pg_function_is_visible function now correctly reports visibility based on the functions that are visible on the current search_path. #90657
  • Fixed a rare bug that could cause upgrades from v22.1 to v22.2 to fail if the job coordinator node crashes in the middle of a specific upgrade migration. #93487
  • Fixed a bug for queries with disjunctions (i.e., contains OR) where all the columns referenced in the disjunctions are known to have a single value. #93480
  • Fixed a bug introduced in v22.1.0 in which the non-default nulls ordering, NULLS LAST, was ignored in window and aggregate functions. This bug would cause incorrect query results when NULLS LAST was used. #93426
  • Fixed a bug that caused an internal error when trying to execute a UDF with an empty function body. This bug was present since UDFs were introduced in v22.2.0. #93331
  • 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. #93567
  • Fixed a bug where selecting a small timeframe in the past in a timeseries query resulted in no data in the graphs. #93293
  • Fixed a bug where CockroachDB would error when receiving Geometry/Geography using binary parameters. #93563
  • 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. #93652
  • Fixed a bug where a query would incorrectly pass if a given interval for AS OF SYSTEM TIME interval was a small positive duration. #93146

Performance improvements

  • The optimizer will now plan inverted index scans for queries with JSON subscripting filters, like json_col['field'] = '"value". #87957
  • CockroachDB now avoids wasteful contention on the gossip mutex caused by checking if the network needs tightening hundreds of times per second. #88472
  • Some types of queries with comparisons with constant values now execute faster. #88638
  • The optimizer now explores plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions. #88491
  • Consistency checks are now properly cancelled on timeout, preventing them from piling up. #86591
  • Raft ticks now adapt to scheduling delays. This helps preventing re-elections, and the corresponding performance effects, in the event of relatively short (sub-second) processing delays. #86240
  • 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. #88950
  • pg_catalog.col_description is now much faster when resolving columns for tables in the pg_catalog, crdb_internal, or information_schema namespaces. #89465
  • The optimizer now does less copying of histograms while planning queries, which will reduce memory pressure a little. #88526
  • Added early inlining of VALUES clauses and unnested arrays in WITH queries in order to eliminate unnecessary joins. #87790
  • Added significantly faster JSON parsing, which should help with any workloads that insert large amounts of JSON data, including IMPORT workloads. #89884
  • Loading the Database Details page in the DB Console 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. #90198
  • 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. #90116
  • The overhead of running EXPLAIN ANALYZE and EXPLAIN ANALYZE (DISTSQL) has been significantly reduced. The overhead of EXPLAIN ANALYZE (DEBUG) did not change. #91117
  • Enabled more efficient lookup joins by deriving new join constraints when equijoin predicates exist on the column(s) of a unique constraint on one table which are a proper subset of the referencing columns of a foreign key constraint on the other table. If an index exists on those foreign key constraint referencing columns, equijoin predicates are derived between the primary key and foreign key columns not currently bound by ON clause predicates. #90599
  • The setup of the distributed query execution is now fully parallelized which should reduce the query latencies, especially in multi-region setups. #89649
  • Performance of the LIKE and ILIKE operators using patterns without any wildcards has been improved. #91895
  • The optimizer can now better calculate the properties of recursive common table expressions in the presence of a LIMIT. #90725
  • Certain types of reads will now have a far smaller contention footprint with conflicting concurrent writers. #85993
  • In some cases, the key-value store client now needs to look up where to send requests. Prior to this change, such lookup requests were always routed to the leaseholder of the metadata range storing that information. Now the client can attempt to look up this metadata from followers. This can improve tail latency in multi-region clusters in cases where the range addressing cache is cold. #91638
  • The garbage collection score triggering a MVCC GC run has been lowered. The GC Score is a ratio computed from MVCC stats and uses the ratio of live objects and estimated garbage age to estimate collectability of existing data. The reduced score will trigger garbage collection earlier, lowering the interval between runs 3 times, giving 2 times reduced peak garbage usage at the expense of 30% increase of wasteful data scanning on constantly updated data. #92118
  • 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. #92779
  • In 22.2, privileges on virtual tables (system catalogs like pg_catalog, information_schema, and crdb_internal) were introduced. A problem with this new feature is that we now must fetch those privileges into a cache before we can use those tables or determine their visibility in other system catalogs. This process used to occur on-demand, when the privilege was needed. Now, CockroachDB will fetch these privileges eagerly during startup to mitigate latency when accessing pg_catalog right after the server boots up. #93557

Contributors

This release includes 1655 merged PRs by 101 authors.

We would like to thank the following contributors from the CockroachDB community:

  • Alex (first-time contributor)
  • Ambuj Kumar (first-time contributor)
  • Faizaan Madhani (first-time contributor)
  • GirishV (first-time contributor)
  • Klaus Rettinghaus (first-time contributor)
  • Vivian Kong (first-time contributor)
  • e-mbrown
  • likzn
  • nnaka2992
  • quanuw (first-time contributor)
  • rharding6373
  • shralex

Yes No
On this page

Yes No