What's New in v23.1

Docker image

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

docker pull cockroachdb/cockroach:v23.1.2

Source tag

To view or download the source code for CockroachDB v23.1.2 on Github, visit v23.1.2 source tag.

Changelog

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

Backward-incompatible changes

• Schema names in the search_path session variable now respect case and must be delimited with double quotation marks. Previously, if a search_path was specified in the connection string parameters, it would be treated case insensitive by default. #101493

#101493

General changes

• Queries with invalid syntax are now logged at the INFO level in the SQL_EXEC log channel. Previously, these were logged at the ERROR level. #101094

Enterprise edition changes

• CDC queries now support wrapped envelope with diff (envelope='wrapped', diff). #101392
• Changefeeds using the WITH confluent_schema_registry option will make fewer duplicate schema registrations. #100844
• Added logic to set the cluster's multi-region system database's survival goal to the max non-system database's survival whenever an ALTER DATABASE...SURVIVE...FAILURE is issued. #102379
• Cluster SSO using JSON web tokens (JWT) can now read SQL usernames from any JWT claims instead of requiring the subject claim to be used. The claim can be controlled by the server.jwt_authentication.claim cluster setting with an empty string or "sub" equivalent to the previous behavior. #103526

SQL language changes

• Added a new session variable unbounded_parallel_scans, which controls whether scans will be parallelized across ranges in more cases. Note that using this feature can lead to increased likelihood of out-of-memory errors (OOMs), so it should be used with care (namely when you expect that the scan should read a "reasonable" number of rows - probably less than 10k). Also note that queries with LIMITs aren't affected by this variable; cross-range parallelism of scans continue to be disabled for such queries. #100948
• Statements of type SET ... are not longer displayed on the Insights page. #101669
• Fixed crdb_internal.transaction_contention_events, so it generates even if an error occurs when getting the names. #101870
• Added a new SQL activity updater job which updates the system.transaction_activity and system.statement_activity tables based on the statistics tables. #101996
• Added two views to the crdb_internal catalog: crdb_internal.statement_activity, which surfaces data in the persisted system.statement_activity table and crdb_internal.transaction_activity, which surfaces the system.transaction_activity table. #102002
• Tables with Row-level TTL settings can now have outbound foreign keys. #101874
• Span statistics are now unavailable on mixed-version clusters. #101877
• Introduced the to_char(date, format) built-in function, which converts a given date to a string using the given format string. #102989
• Renamed an existing metric changefeed.table_metadata_nanos to changefeed.schemafeed.table_metadata_nanos and introduced a new metric changefeed.schemafeed.table_history_scans, which records the number of table history scans the schema feed performs. #102977
• Changed the OID generation for pg_catalog. For example column, index and constraint OID's will have different values. Relation, type and function OID's remain unchanged. #103556
• Added a new session setting optimizer_use_improved_computed_column_filters_derivation, which defaults to FALSE. When TRUE, the optimizer will derive filters on computed columns in more cases. #103412
• Crdb_internal.transaction_contention_events, crdb_internal.node_contention_events, and crdb_internal.cluster_locks will now redact keys provided the user has VIEWACTIVITYREDACTED. Crdb_internal.node_contention_events can only be viewed if the user has any of admin, VIEWACTIVITY or VIEWACTIVITYREDACTED. #103637

Operational changes

• Added the rebalancing.replicas.cpunanospersecond histogram metric, which provides insight into the distribution of replica CPU usage within a store. #100509
• Added the rebalancing.replicas.queriespersecond histogram metric, which provides insight into the distribution of queries per replica within a store. #100509
• 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. #101507
• Added the metric leases.requests.latency, which records a histogram of lease request latencies. #100475
• When local corruption of data is encountered by a background job in the storage engine, a node will now exit immediately. #102274
• When the --experimental-dns-srv flag is enabled, the crdb node will always attempt to query SRV records of an address when dialing remote targets, and fall back to use the address verbatim if the SRV query fails. Previously, SRV queries were only attempted once when a node starts. #102468
• The default Raft scheduler concurrency, controlled by COCKROACH_SCHEDULER_CONCURRENCY and defaulting to 8 per CPU core capped at 96, is now divided evenly across stores instead of applying individually per store. This avoids excessive Go scheduler pressure and memory usage on nodes with many stores. The common case of 1 store per node is not affected. #103073
• Workload generators now export Go runtime metrics via Prometheus endpoint. #102392
• Multi-region Serverless databases that are created without a primary region will now inherit regions from the Serverless cluster's regions. #102627

Command-line changes

• The cockroach debug zip command now accepts an --include-range-info flag, which determines whether CockroachDB retrieves individual nodes/*/ranges/*.json files, which was previously done by default. For large clusters, this can dramatically reduce the size of the generated artifacts. This flag defaults to FALSE. #102862
• Workload now jitters the teardown of connections to prevent a thundering herd of queries impacting P99 latency results. #102395
• Workload utility now has flags to tune the connection pool used for testing. See --conn-healthcheck-period, --min-conns, and --max-conn-* flags for details. #102395
• Workload now supports every PostgreSQL query mode available via the underlying pgx driver. #102395
• The \connect client-side command for the SQL shell (included in cockroach sql, cockroach demo, cockroach-sql) now recognizes the option autocerts as its last argument. When provided, \c will now try to discover a Transport Layer Security (TLS) client certificate and key in the same directory(ies) as used by the previous connection URL. This feature makes it easier to switch usernames when TLS client/key files are available for both the previous and new username. #103144

DB Console changes

• The Application Name column is now shown by default in the Fingerprints Overview pages. Statements and transactions fingerprints will be displayed per application on the Overview pages rather than grouped into a single fingerprint ID. #101164
• When going from the Fingerprints Overview pages or the Insight Details pages to the Fingerprint Details page, the Details page will fetch data for the statement with the provided application name. For Overview pages, this is the application name of the selected row. For Insight details, this is the application of the execution that generated the insight. #101164
• Updated the Network Latency side nav name and Network Diagnostics page title to Network. Updated the Advanced Debugging page title to Advanced Debug. #101758
• Added an option to select the trace rate for Statement Diagnostics collection. #101759
• Added a time scale selector to the Statement Diagnostics page, making it possible to see bundles only from the selected period. #101802
• Added draining node as its own value on the DB Console Overview page, instead of counting it as a dead node. #101793
• Added the ability for users to view timestamps in DB Console in their preferred timezone via the cluster setting ui.display_timezone. Previously, only the timezones Coordinated Universal Time and America/New_York were supported. #102195
• An alert on DB Console Overview page is shown when the cluster setting cluster.preserve_downgrade_option is set, and no longer waits 48 hours to show. #102913
• Added Transaction Insights for Serverless. #103363
• Added _status/load to the list of Raw Status Endpoints on the Advanced Debug page. #103420
• If a page crashed, a force refresh is no longer required to be able to see the other pages on DB Console. #103328
• The filter on the SQL Activity page is now working properly. Fixed the type on the JSON object from stmtTyp to stmtType. #103411
• Added missing information on the Index Details page about latency information of most used fingerprints. #103421

Bug fixes

• Fixed the sql.mem.distsql.current metric so it would no longer double count the memory usage of remote DistSQL flows. #100049
• 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 led to errors like drain details: distSQL execution flows:, together with a non-zero number of flows that does not reduce over a long period of time. #100841
• Fixed a bug that caused a restore to fail occasionally due to incorrect schema ID resolution when restoring a backup with user-defined schemas. #101309
• Fixed a bug that caused suboptimal query plans to be generated by the optimizer when the table being queried contained infinite values, e.g., '+Infinity'::DECIMAL. This bug was present since v22.1 (and likely earlier). It could also be triggered in rare cases when table statistics forecasts created a forecasted bucket with an infinite value. #101135
• Fixed a rare internal error in the optimizer that existed since before v22.1, which could occur while enforcing orderings between SQL operators. #101173
• Fixed a bug so that crdb_internal.deserialize_session internal function works properly with prepared statements that have more param type hints than params. #101367
• 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. #101382
• The search_path session variable now supports schema names that have commas in them. Also, fixed a bug in parsing a search_path with a quote in it when specified in the connection string. #101493
• 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. #101491
• 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. #101483
• Previously, CockroachDB alpha and beta versions of 23.1 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. #101564
• Fixed a bug whereby some tables' physical disk space could not be calculated by the storage engine. #100939
• 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. #101641
• 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.
• 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. #101688
• The descriptions of rebalancing.readbytespersecond and rebalancing.writebytespersecond metrics now correctly reference bytes read and bytes written, respectively. #101709
• 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. #101783
• 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. #101782
• Fixed a bug where a RESTORE operation with skip_localities_check could fail with errors if regions were missing on a cluster. #101797
• Fixed a bug in the optimizer that could cause an internal error in rare cases for a query with outer joins that could be simplified to non-outer joins and at least one semi-join. This bug was present since before v22.1. #100670
• Fixed a bug where CockroachDB previously incorrectly evaluated EXPORT statements that had projections or rendering on top of the EXPORT (e.g. the common table expression WITH cte AS (EXPORT INTO CSV 'nodelocal://1/export1/' FROM SELECT * FROM t) SELECT filename FROM cte;). Such statements would result in panics or incorrect query results. Note that the exported data wasn't affected, only the presentation of the query result. This bug had been present since v22.1 or earlier. #101805
• Fixed a bug in the optimizer introduced in v22.2.0 that could cause queries containing a subquery with a lateral join, in which the right side of the lateral join was an aliased data source, to return an internal error in some cases. For example, it could cause an error if the subquery was provided as an argument to an aggregate function. #101863
• Fixed handling of expressions in IN clauses such as (c1, c2) IN (SELECT c3+1, c4+1 FROM ...). Previously, such query expressions would error out during type checking. #102027
• Fixed a potential bug whereby a failed or cancelled IMPORT could leave some of the imported rows behind after it was cancelled, in the rare event that the writing processes were slow enough to continue writing after the cleanup process had started. #101443
• Fixed a bug that could cause incorrect results for queries invoking STRICT user-defined functions. This bug was only present in pre-release versions of 23.1. #101950
• Fixed a bug where CockroachDB's pgwire implementation would incorrectly parse arrays if they were sent as placeholder arguments to a prepared statement, and the argument had spaces in between the array elements. #101596
• Fixed a very rare bug that could cause keys to be unexpectedly deleted locally within a store by replica rebalancing during a write heavy workload. #102167
• 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. Also, fixed static erroring of some locality-optimized lookup joins to now be handled dynamically during query execution. #102207
• 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. #102306
• Fixed a bug where a backup with a key's revision history split across multiple SST files may not have correctly restored the proper revision of the key. #102343
• Fixed a bug 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 enough period of inactivity. #102489
• Previously, CockroachDB could encounter a "command is too large" error when evaluating UPSERT statements such that the new values combined exceeded the size of the kv.raft.command.max_size cluster setting. This bug had been present since before v21.1 and initially all write operations (INSERT, UPDATE, DELETE) were affected; however, in v21.2 those three were fixed, but UPSERT was forgotten about. This is now fixed. #102514
• Fixed a bug introduced in v22.1.19, v22.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. #102626
• Fixed a bug where RENAME COLUMN was incorrectly allowed and would modify these columns node-wide. #102460
• Fixed a bug where the internal node pseudo-role was not viewable through introspection of pg_catalog tables. #102662
• Fixed a bug where COPY ... TO statements would always fail when used in a prepared statement. CockroachDB now matches the pgwire handling of prepared COPY ... TO statements. #102663
• Fixed a bug where the ALTER DEFAULT PRIVILEGES ... GRANT USAGE ON SEQUENCES statement would fail because the sequence object was mapped to an incorrect internal privilege object. #102724
• Fixed a minor bug that caused an internal error for some queries with nested subqueries instead of the more appropriate "could not decorrelate subquery" error. This bug was only present in pre-release alpha and beta versions of 23.1. #102384
• Fixed a bug that allowed values to be inserted into an ARRAY-type column that did not conform to the inner-type of the array. For example, it was possible to insert ARRAY['foo'] into a column of type CHAR(1)[]. This could cause incorrect results when querying the table. The insert now errors, which is expected. This bug was present since v21.1. #102807
• Fixed an issue where running SHOW HISTOGRAM to see the histogram for an ENUM-type column could cause a panic and crash the cockroach process. This issue has existed since v20.2.0. #102828
• Fixed the behavior of user-defined functions (UDFs) to return its results as a row instead of a tuple when UDFs are called in a query as a data source. This is now compatible with PostgreSQL behavior. #102188
• Fixed a bug in closed timestamp updates within its side-transport. Previously, during asymmetric partitions, a node that transfers a lease away, and misses 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 node crashes; in extreme cases, this could lead to inconsistencies in read-only queries. #102601
• Fixed a bug where Row-level TTL jobs would incorrectly process a table that spanned multiple ranges in rare cases. This bug had been present since v22.2.0. You were affected by this bug if you saw the error message "error decoding EncDatum of ...: did not find terminator ... in buffer ...". #102881
• The Retry.Max field of the webhook_sink_config option for changefeeds was not correctly defined in the docs. The existing docs mention that it is "The maximum amount of time the sink will retry a single HTTP request to send a batch", but this is incorrect. It actually represents the maximum number of retries which will be attempted when sending a batch in an HTTP request fails. This is now fixed to correctly capture its behavior. Also, fixed a bug where the retry time would wait for 4 seconds, regardless of Retry.Max. With this change, the new maximum retry time is 30 seconds. The initial backoff will keep doubling every time a retry occurs until the maximum of 30 seconds is reached. E.g. If Retry.Max = 4 and the initial backoff is 10 seconds, at most 4 retries will be performed with the backoff times 10, 20, 30, 30 seconds respectively. #102958
• Stopped using a NULL value for pg_constraint.conparentid. Now the value is hard-coded to 0, since CockroachDB does not support constraints on partitions. #103234
• Fixed a bug where COPY in v23.1.0 and beta versions would incorrectly encode data with multiple column families. The data must be dropped and re-imported to be encoded correctly. #103355
• Optimized over-head of pg_catalog.pg_description and pg_catalog.pg_shdescription, which can lead to performance regression relative to v22.2 #103331
• Timeseries metric counts will now show cumulative counts for a histogram rather than a windowed count. A -sum timeseries is also exported to keep track of the cumulative sum of all samples in the histogram. #103444

• Fixed a bug where CockroachDB could produce incorrect results when evaluating queries with ORDER BY clause in rare circumstances. In particular, some rows could be duplicated if all of the following conditions were met:

  1. The query had a LIMIT clause.
  2. The SORT operation had to spill to disk (meaning that LIMIT number of rows used up non-trivial amounts of memory, e.g. the rows were "wide").
  3. The ORDER BY clause contained multiple columns and the ordering on the prefix of those columns was already provided by the index.

  The bug has been present since at least v22.1. #102790

• Fixed a bug where CockroachDB could previously encounter a nil pointer crash when populating data for SQL Activity page in some rare cases. The bug was present in v22.2.9 and v23.1.1 releases. #103521

• Fixed calls to undefined objects. #103520

• Fixed a bug where 0 with exponents < -6 would display as 0E(exponent) instead of printing all 0s, e.g. 0E-7 should be 0.0000000. #103640

• Fixed a bug that could prevent RESTORE from working if the backup had a refresh materialized view mutation in it. #103233

• In earlier patch releases of v23.1, it was possible for backups to be excessively slow, slower than they were in earlier releases. It was also possible for them to fail with errors of the following form: operation "Export Request for span ..." timed out after 5m0.001s. At least one of the reasons for this behavior is now addressed. This problem also affected v22.2 clusters if using a hidden-by-default, default-as-disabled admission.elastic_cpu.enabled cluster setting. #103626

• Fixed a crash/panic that could occur if placeholder arguments were used with the with_min_timestamp(to_timestamp($1)) functions. #103630

• Fixed a panic that could occur if a COPY TO statement had a subquery that was logged with redaction markers. #103689

• Fixed a bug where SET PRIMARY REGION and SET SECONDARY REGION did not validate transactionally, which could prevent cleaning up removed regions after a restore. #103631

• DROP ROLE now correctly returns a 2BP01 error when the given role has been granted privileges on a schema. #103546

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

• When the option WITH TABLES or WITH INDEXES is passed to SHOW RANGES, the per-object start/end key columns now properly refers to the part of the object included inside the range identified by the current row. Previously, they could incorrectly point to keys outside of the current range's boundaries. This bug had been introduced in v23.1. #103777

• Fixed a bug in VALUES clauses containing a call to a record-returning UDF that could manifest as an internal error in some queries. #103639

• Fixed a bug with choosing optimal plan where prepared statements using placeholders in recursive CTEs sometimes did not re-optimize correctly after plugging in the parameters. #99823

• Fixed a bug where kv was read when fetching a qualified name of a leased UDF. #103089

Performance improvements

• Added an opt-in pacing mechanism to rangefeed closed timestamp notifications. Pacing is controlled by the kv.rangefeed.closed_timestamp_smear_interval cluster setting, which defaults to kv.rangefeed.closed_timestamp_refresh_interval. Lowering the smear interval makes the rangefeed closed timestamp delivery less spikey, which ultimately reduces its impact on foreground SQL latencies. #99946
• Queries that have subqueries in equality expressions are now more efficiently planned by the optimizer when optimizer_hoist_uncorrelated_equality_subqueries is set to true. #101753
• SQL Activity dashboards now default to using a table optimized with the top data for the most used cases. Else, they use persisted data if available, and in-memory data otherwise. #102309
• Statistics on the system.jobs table are now automatically collected, which will enable the optimizer to produce better query plans for internal queries that access the system.jobs table. This may result in better performance of the system. #102637
• Improved changefeeds to decrease the commit-to-emit latency (i.e. The difference between an event's MVCC timestamp and the time it is ready to emit to a downstream sink). Previously, it is determined by a non-documented cluster setting (changefeed.experimental_poll_interval) which defaults to 1s. To enable this performance improvement, users should "lock" the watched table with ALTER TABLE <tbl> SET (schema_locked =t);, which would disallow schema changes on <tbl>. If a schema change statement is attempted on a locked table, CockroachDB will reject it and return an error. The user could lock the table either before creating a changefeed or, when a changefeed is running, CockroachDB will be able to detect that and enable this performance improvement automatically. If the user is running a changefeed on a locked table but wishes to perform schema changes to the table, they need to explicitly unlock the table first with ALTER TABLE <tbl> RESET schema_locked. After the schema change completes, the user can lock the table again to re-gain this performance improvement. The changefeed job itself does not need to be modified in any way by the user (e.g. the user does not need to pause the job when locking/unlocking a table). This change is a "pure" optimization in that if the table is not locked, everything should still work the way it used to. #102977
• Improved performance when joining with the pg_description table. #103331
• Added concurrency to speed up the phase of the restore that ingests backed up table statisitcs. #102694
• Added support for constrained scans using computed columns which are part of an index when there is an IN list or OR'ed predicate on the columns that appear in the computed column expression. #103412
• Added two new statistics which are useful for tracking the efficiency of snapshot transfers. Some snapshots will always fail due to system level "races", but the goal is to keep it as low as possible.
  • range.snapshots.recv-failed - The number of snapshots sent attempts that are initiated but not accepted by the recipient.
  • range.snapshots.recv-unusable - The number of snapshots that were fully transmitted but not used. #101837

Build changes

• Updated the reported Build Tag for nightly (non-release) builds. #101998

v23.1.1

Release Date: May 16, 2023

Downloads

docker pull cockroachdb/cockroach:v23.1.1

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

docker pull cockroachdb/cockroach-unstable:v23.1.0-rc.2

docker pull cockroachdb/cockroach-unstable:v23.1.0-rc.1

docker pull cockroachdb/cockroach-unstable:v23.1.0-beta.3

docker pull cockroachdb/cockroach-unstable:v23.1.0-beta.2