What's New in v20.2

On this page

CockroachDB v20.2 is a required Regular Release.

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

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

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

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

• Be sure to also review the Release Support Policy.

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

Get future release notes emailed to you:

Thanks!

v20.2.19

Release Date: February 9, 2022

Bug fixes

• Previously, schema changes running during node shutdown could sometimes fail permanently when they should not. This is now fixed. #71557
• Fixed a bug which prevented the Data Distribution page from working on clusters which were upgraded from 19.2 or earlier. #72506
• The CancelSession endpoint now correctly propagates gateway metadata when forwarding requests. #75885

v20.2.18

Release Date: November 8, 2021

This page lists additions and changes in v20.2.18 since v20.2.17.

DB Console changes

• Non-admin users of the DB Console have regained the ability to view the Cluster Overview page. Users without the admin role will still see most data about their nodes, but information such as command-line arguments, environment variables, and IP addresses and DNS names of nodes will be hidden. #71793

Bug fixes

• Fixed an internal error with joins that are both LATERAL and NATURAL/USING. #70806
• Previously, in v21.1.x and earlier, CockroachDB could incorrectly read the data of a unique secondary index that used to be a primary index created via ALTER PRIMARY KEY. This has been fixed. #71589
• CockroachDB now avoids dialing nodes in performance-critical code paths, because doing so could cause substantial latency when encountering unresponsive nodes (e.g., when a VM or server was shut down). #70487
• CockroachDB could crash if network connectivity was impaired. The stack trace (in cockroach-stderr.log) would contain server.(*statusServer).NodesUI in that case. This has been fixed. #71793
• Fixed a panic that could occur with invalid GeoJSON input using ST_GeomFromGeoJSON/ST_GeogFromGeoJSON. #71307
• Fixed a bug which caused ALTER COLUMN TYPE statements to fail when they should not have. #71167
• Connect timeout for grpc connections is now set to 20s to match the pre-v20.2 default value. #71514
• Fixed a bug which caused incorrect results for some queries that utilized a zig-zag join. The bug could only reproduce on tables with at least two multi-column indexes with nullable columns. The bug was present since v19.2.0. #71850

Performance improvements

• Reduced memory usage slightly during ANALYZE or CREATE STATISTICS statements. #71773

Contributors

This release includes 16 merged PRs by 16 authors.

v20.2.17

Release Date: October 11, 2021

This page lists additions and changes in v20.2.17 since v20.2.16.

DB Console changes

• Changed references to the UI console from "Admin UI" to "DB Console". #70882

Bug fixes

• Fixed a bug that caused internal errors with set operations (e.g., UNION) and columns with tuple types that contained constant NULL values. This bug was introduced in version 20.2.0. #69270
• Fixed a bug causing table stats collection issued via an EXPLAIN ANALYZE statement or via a CREATE STATISTICS statement to run into a "flow: memory budget exceeded" error if the statement was executed without specifying the AS OF SYSTEM TIME option. #69590
• Fixed a bug causing a prepared statement to incorrectly reuse the query plan of a different prepared statement that had similar, but not identical type hints. #67687
• Fixed a bug causing other nodes in the cluster to fail to connect to a restarted node due to their circuit breakers not resetting after a temporary node outage. This would manifest in the logs in messages of the form "unable to dial nXX: breaker open", where XX is the ID of the restarted node. Note that such errors are expected for nodes that are truly unreachable, and could still occur around the time of the restart, but for no longer than a few seconds. #70353
• RESTORE now correctly ignores dropped databases that may have been included in cluster backups with revision history. #69795
• DNS unavailability during range 1 leaseholder loss no longer causes significant latency increases for queries and other operations. #70133
• Addressed a memory corruption issue in Pebble when running with CGO_ENABLED=0. #70995
• Addressed an issue in Pebble where a key can be dropped from an LSM snapshot if the key was deleted by a range tombstone after the snapshot was acquired. #70995
• Addressed an issue in Pebble where a crash between the creation of the WAL and the MANIFEST could leave the second most WAL with an unclean tail that would be considered corruption on a subsequent Open. #70995

Contributors

This release includes 11 merged PRs by 11 authors.

v20.2.16

Release Date: September 13, 2021

DB Console changes

• Added a Customer Effort Score (CES) survey link component to support being able to get client feedback. #68516

Bug fixes

• Fixed a bug causing changefeeds to sometimes get stuck. #68009
• Fixed an error in which cascaded drops of views could incorrectly run into table ... is being dropped errors. #68617
• Fixed an oversight in the data generator for TPC-H which was causing a smaller number of distinct values to be generated for p_type and p_container in the part table than the spec calls for. #68709
• Fixed a bug in full cluster restores where dropped descriptor revisions would cause the restore to fail. #69639
• Fixed a bug that created non-partial unique constraints when a user attempted to create a partial unique constraint in ALTER TABLE statements. #68747
• Fixed a bug where the schedules.backup.succeeded and schedules.backup.failed metrics would sometimes not be updated. #69257
• Fixed a bug that could cause prolonged unavailability due to lease transfer to a replica that may be in need of a Raft snapshot. #69801

Contributors

This release includes 10 merged PRs by 9 authors.

v20.2.15

Release Date: August 23, 2021

Enterprise edition changes

• The kafka_sink_config changefeed option can now include RequiredAcks. #69016

DB Console changes

• Fixed tooltip behavior on the Sessions, Statements, and Transactions pages. #68477

Bug fixes

• Fixed a bug that caused incorrect query results when querying tables with multiple column families and unique secondary indexes. The bug only occurred if 1) vectorized execution was enabled for the query, 2) the query scanned a unique secondary index that contained columns from more than one column family, and 3) the rows fetched by the query contained NULL values for some of the indexed columns. This bug was present since version v20.1. #68238
• Fixed a bug where CockroachDB could incorrectly evaluate LIKE expressions when the pattern contained the escape characters \ if the expressions were executed via the vectorized execution. #68354

Contributors

This release includes 5 merged PRs by 5 authors.

v20.2.14

Release Date: August 16, 2021

Security updates

• The cert-principal-map flag passed to cockroach commands now allows the certificate principal name to contain colons. #67811
• The node status retrieval endpoints over HTTP [/_status/nodes, /_status/nodes/<N> and the DB Console /#/reports/nodes) now require the admin role from the requesting user. This ensures that operational details such as network addresses and command-line flags do not leak to unprivileged users. This change means that the Overview page and Hardware dashboard of the DB Console will not show all details for non-admin users. #67069

Enterprise edition changes

• Incremental backups to a cloud storage location that already contains large existing backups now find their derived destination without listing as many remote files. #67289

SQL language changes

• Added a new EXPLAIN flag, MEMO, to be used with EXPLAIN (OPT). When the MEMO flag is passed, a representation of the optimizer memo will be printed along with the best plan. The MEMO flag can be used in combination with other flags such as CATALOG and VERBOSE. For example, EXPLAIN (OPT, MEMO, VERBOSE) will print the memo along with verbose output for the best plan. #67778

DB Console changes

• The DB Console will now redirect the user to the originally requested page after user login. #67859

Bug fixes

• Fixed a statement buffer memory leak when using suspended portals. #67370
• Correlated subqueries that couldn't be decorrelated and that have their own subqueries are now executed correctly when supported. Note that this is a very rare edge case. #67571
• Fixed a very rare unexpected error from the vectorized engine ("index out of bounds") when evaluating the CASE operator. #67785
• Catching up Raft followers on the Raft log is now more efficient in the presence of many large Raft log entries. This helps avoid situations where Raft leaders struggle to retain leadership while catching up their followers. #67126
• Fixed a rare bug when a lease change occurred during a range merge. This bug allowed AS OF SYSTEM TIME queries to serve inconsistent results. #65823
• The v20.2 SQL shell now properly reports detailed execution timings (using the client-side configuration setting verbose_times) when run against a v21.1+ server. #67853
• Fixed a deadlock that could occur when many replicas were rapidly queued for removal. #65861
• Fixed two bugs which affected geospatial queries with the st_distance function. The first caused errors for filters of the form st_distance(g1, g2, use_spheroid) = 0. The second incorrectly transformed filters of the form st_distance(g1, g2) = 0 when g1 and g2 are geographies to st_instersects(g1, g2). This is not a valid transformation because st_distance makes spheroid-based calculations by default while st_intersects only makes sphere-based calculations. #67412
• Fixed an issue with statistics estimation in the optimizer that could have caused it to over-estimate the number of rows for some expressions and thus choose a sub-optimal plan. This issue could happen when multi-column statistics were used in combination with histograms, the query contained a predicate on two or more columns where the columns were highly correlated, and the selected values were very common according to the histograms. #68012
• Fixed an issue where CockroachDB could encounter an internal error or crash when performing a cast of a NULL JSON value to Geography or Geometry types. #67903
• INSERT and UPDATE statements which operate on larger rows are now split into batches using the sql.mutations.mutation_batch_byte_size setting. #67963
• Fixed a rare bug that could result in a crash while creating a debug.zip. The bug would only occur if a debug.zip was captured during a period of rapid lease movement. #67727
• Fixed a rare crash when a backup writing to Google Cloud Storage failed while writing a file. #68258
• Fixed missing foreign key checks in some cases when there are multiple checks and the inserted data contains a NULL for one of the checks. #68521
• Fixed a bug that prevented upreplication of an underreplicated range while in a joint quorum configuration. #68576

Performance improvements

• Improved the selectivity estimate for array containing predicates (for example, arr @> ARRAY[1]) in the optimizer. This improves the optimizer's cardinality estimation for queries containing these predicates, and may result in better query plans in some cases. #67530
• Updated the cost model in the optimizer to make index joins more expensive and better reflect the reality of their cost. As a result, the optimizer will choose index joins less frequently, generally resulting in more efficient query plans. #67530
• Improved the performance of the pg_table_is_visible built-in function. #68113

v20.2.13

Release Date: July 12, 2021

Operational changes

• Added logs for important events during the server draining/shutdown process. #66882 Specifically, the following:
  • Log when the server closes an existing connection while draining
  • Log when the server rejects a new connection while draining
  • Log when the server cancels in-flight queries after waiting for the duration of the server.shutdown.query_wait cluster setting to elapse while draining.

DB Console changes

• Fixed an issue with displaying more than 100 hours of remaining time on the Jobs page. #66704
• Removed width styling on the multi bar. #66737 cockroachdb/cockroach#66737
• Fixed the Custom Chart debug page layout. #66623
• Added a drag-to-select timescale feature to the Custom Chart debug page. #66623
• Fixed a styling issue with a filter component on Cloud Console. #66566
• Removed an unnecessary call to console log. #66566 cockroachdb/cockroach#66566
• Bumped the cluster-ui version to v20.2.1. #66566
• Fixed a bug where metrics pages in the DB Console would lose their scroll position on chart data updates. #67088

Bug fixes

• Fixed a bug which prevented the optimizer from producing plans with partial indexes when executing some prepared statements that contained placeholders, stable functions, or casts. This bug was present since partial indexes were added in v20.2.0. #66641
• Fixed a panic that could occur in the optimizer when executing a prepared plan with placeholders. This could happen when one of the tables used by the query had computed columns or a partial index. #66832
• Fixed a bug that caused graceful drain to call time.sleep multiple times, which cut into the time needed for range lease transfers. #66852
• CockroachDB now allows a node with lease preferences to drain gracefully. #66714
• CockroachDB now avoids interacting with decommissioned nodes during DistSQL planning and consistency checking. #66951
• Changefeeds no longer interact poorly with large, abandoned transactions. It was previously possible for this combination to result in a cascade of work during transaction cleanup that could starve foreground traffic. #66814
• Changefeeds now properly invalidate cached range descriptors and retry when encountering decommissioned nodes. #67024

Performance improvements

• Added the ability to continue generating histograms when table statistics collection reaches memory limits, instead of disabling histogram generation. #67059

Contributors

This release includes 19 merged PRs by 16 authors.

v20.2.12

Release Date: June 28, 2021

This page lists additions and changes in v20.2.12 since version v20.2.11.

Docker image

$ docker pull cockroachdb/cockroach:v20.2.12

Enterprise edition changes

• Added new CHANGEFEED options that give you more control over topic naming: The full_table_name option lets you use a fully-qualified table name in topics, subjects, schemas, and record output instead of the default table name, and can prevent unintended behavior when the same table name is present in multiple databases. The avro_schema_prefix option lets you use a fully-qualified schema name for a table instead of the default table name, and makes it possible for multiple databases or clusters to share the same schema registry when the same table name is present in multiple databases. #66091
• Changefeeds with custom Kafka client configurations (using the kafka_sink_config object) that could lead to long delays in flushing messages will now produce an error. #66316
• The kafka_sink_config object now supports a version configuration item to specify Kafka server versions. This is likely only necessary for old (Kafka 0.11/Confluent 3.3 or earlier) Kafka servers. Additionally, settings not specified in kafka_sink_config now retain their default values. #66316

SQL language changes

• Creating STORED computed columns with expressions that reference foreign key columns is now allowed. #66169

Operational changes

• BACKUP now puts backup data files in a data sub-directory of the BACKUP path instead of directly in the backup path. #66164

Bug fixes

• CockroachDB now prevents intra-query leaks during disk spilling that could cause the database to run out of memory, especially during disk spilling operations on tables with wide rows. #66170
• Queries now use up to 1MB less actual system memory per scan, lookup join, index join, zigzag join, or inverted join in their query plans. This will result in improved memory performance for workloads with concurrent OLAP-style queries. #66170
• BACKUPs no longer risk the possibility of blocking conflicting writes while being rate limited by the kv.bulk_io_write.concurrent_export_requests concurrency limit. #66409
• BACKUP and other operations can now reuse a previously created S3 client session when operating on the same bucket, which can avoid NoCredentialProviders errors on EC2 when iterating with large incremental backups. #66260
• Fixed a crash when performing a cluster BACKUP with revision history of a cluster upgraded from 20.1 to 20.2 to 21.1 which contains tables that were truncated by 20.1. #66638
• Fixed the style of the DB Console password field on Safari. #66135

Performance improvements

• The COCKROACHDB_REGISTRY file is no longer rewritten whenever a new unencrypted file is created. #66424

Contributors

This release includes 19 merged PRs by 17 authors.

v20.2.11

Release Date: June 14, 2021

SQL language changes

• Floating point infinity values are now formatted as Infinity (or -Infinity if negative). This is for compatibility with PostgreSQL #65105
• INSERT INTO ... ON CONFLICT ... DO UPDATE SET statements without predicates now acquire locks using the FOR UPDATE locking mode during their initial row scan, which improves performance for contended workloads. This behavior is configurable using the enable_implicit_select_for_update session variable and the sql.defaults.implicit_select_for_update.enabled cluster setting #65364
• The ST_GeomFromGeoJSON(string) spatial function is now marked as the preferred overload, meaning it will resolve correctly in more contexts. #65441
• The cancellation behavior for DistSQL flows has been improved. #65046

DB Console changes

• Fixed a bug where empty series would show up in metrics graphs and legends, and when data was incorrectly attributed to the wrong nodes on graphs for clusters with decommissioned nodes. #66034
• Removed shading on line graphs, which improves legibility when viewing more than a few series on the same plot. #66034
• Drag to zoom on metrics graphs now supports time ranges under 10 minutes. #66034

Bug fixes

• BACKUP no longer resolves intents one by one. This eliminates the need to run a high-priority query to clean up intents to unblock BACKUP in case of intent buildup. #64931
• Fixed a bug that could cause backups to be slower. LockTableWaiter now checks the finalized transaction cache before pushing transactions to avoid duplicating work. #64999
• Fixed a bug where interval math on a TIMESTAMPTZ on a DST boundary would incorrectly add or subtract an extra hour. #65097
• Fixed a bug where date_trunc on a TIME value on a DST boundary could switch timezones and produce the incorrect result. #65097
• Fixed a bug causing the ZONECONFIG privilege on tables and databases to be incorrectly interpreted as USAGE, which could corrupt a table and/or database because USAGE is an invalid privilege for tables and databases. Also fixed the case when the ZONECONFIG privilege would be incorrectly restored as USAGE for tables and databases created and backed up in v20.1 and then restored through a full cluster RESTORE in v20.2 or later. #65159
• Fixed a bug which could cause a panic when running an EXECUTE of a previously prepared statement with a REGCLASS or REGTYPE parameter or a user-defined type argument after running BEGIN AS OF SYSTEM TIME with an invalid timestamp. #65150
• Fixed a bug which could cause a panic when issuing a query referencing a user-defined type as a placeholder. #65151
• Fixed a bug introduced in v20.2 that caused rows to be incorrectly de-duplicated from a scan with a non-unique index. #65288
• Fixed a bug causing revision_history cluster backups to not include dropped databases. This means that, previously, dropped databases could not be restored from backups that were taken after the database was dropped. #65317
• Fixed a bug where empty zone configurations get created for certain indexes during ALTER PRIMARY KEY. #65175
• Fixed a bug causing SHOW CREATE TABLE output to not display the zone configurations of a table or index if there were no partitions, even if there were zone configurations on the index or table. #65175
• Fixed a bug where ALTER DATABASE ... SET OWNER ... did not work if the database name was a keyword. #65367
• Fixed a bug where SHOW CREATE TABLE would show the zone configurations of a table with the same name from a different schema. #65369
• Previously, CockroachDB would crash when attempting to create a table using CREATE TABLE ... AS syntax where AS selects from crdb_internal.node_statement_statistics, crdb_internal.node_transaction_statistics, or crdb_internal.node_txn_stats virtual tables. #65544
• Fixed a bug where binary TIMETZ values were not being decoded correctly when sent as a parameter in the wire protocol. #65579
• A certain percentage of cases in which a node could have served a follower read were not handled correctly, resulting in the node routing the request to another nearby node for no reason. This has been fixed. #65470
• Fixed a race condition during transaction cleanup that could leave old transaction records behind until MVCC garbage collection. #65384
• Improved transaction cleanup for disconnected clients to reduce intent buildup. #65384
• Fixed a bug where the storage layer would under some rare write-heavy workloads start off by writing SSTable files that were too small for CockroachDB to manage effectively. The storage layer now creates fewer, larger files under those workloads, in line with expected behavior. #65481
• Scheduled backups with interleaved tables can now be created with the include_deprecated_interleaves option. #65730
• Calling get_bit or set_bit on a byte array argument now goes to the correct index of the underlying bitstring, which matches the Postgres behavior. #65787
• Previously, ALTER DATABASE ... CONVERT TO SCHEMA could potentially leave the schema with invalid privileges thus causing the privilege descriptor to be invalid. #65813
• Fixed a scenario in which a rapid sequence of splits could trigger a storm of Raft snapshots. This would be accompanied by log messages of the form would have dropped incoming MsgApp, but allowing due to ..., which tended to occur as part of RESTORE/IMPORT operations. #65500
• Previously, a schema's privilege descriptor could become corrupted upon executing ALTER DATABASE ... CONVERT TO SCHEMA. This is due to privileges that are invalid on a schema being copied over to the schema, rendering the schema unusable due to invalid privileges. #65999
• Previously, TRUNCATE transactionally scanned the meta range to find ranges to unsplit, creating contention with TRUNCATE's new behavior of preserving splits. #65942
• Fixed the error classification for duplicate index names where the later index was a UNIQUE index. #64002
• Fixed the error classification for ALTER TABLE ... ADD CONSTRAINT ... UNIQUE with the same name as an existing index. #64002
• Fixed a bug in SHOW RANGES that misattributed localities to nodes when using multiple stores. #66038
• Fixed a bug where incremental backups with revision_history would sometimes fail if a view was altered between incremental backups. #66072

Performance improvements

• Fixed an issue in the optimizer that prevented spatial predicates of the form (column && value) = true from being index-accelerated. These queries can now use a spatial index, if one is available. #65988

v20.2.10

Release Date: May 17, 2021

SQL language changes

• Added the INCLUDE_DEPRECATED_INTERLEAVES option to BACKUP. In v21.1, this option must be specified when backing up a cluster, database, or table that includes interleaved data. #64688
• Added the chunk_size option to EXPORT CSV to control the target CSV file size. #65009

Command-line changes

• The --help text for --max-disk-temp-storage now properly reports the default value. #55055

DB Console changes

• Implemented a new library for line graphs that renders metrics more efficiently. Customers with large clusters (above ~30 nodes) can now load and interact with the metrics dashboards much faster than before. #64493
• A legend is now shown underneath a timeseries graph if more than 10 series are being displayed. #64493

Bug fixes

• Fixed a race condition where read-write requests during replica removal (for example, during range merges or rebalancing) could be evaluated on the removed replica. These cases would not result in data being written to persistent storage, but could result in errors that should not have been returned. #64601
• Fixed a stack overflow that could happen in some corner cases involving partial indexes with predicates containing (x IS NOT NULL). #64739
• Building no longer depends on internet access. Only vendored modules are used. This bug was introduced in v20.2.0. #64507
• Limit scans are no longer counted as full scans. #64852
• Providing a constant value as an ORDER BY value in an ordered set aggregate, such as percentile_dist or percentile_cont, no longer errors. This bug has been present since order set aggregates were added in v20.2.0. #64903

v20.2.9

Release Date: May 10, 2021

This page lists additions and changes in v20.2.9 since v20.2.8.

SQL language changes

• Prior to this change, st_simplify with NaN caused a node to crash. The behavior has been changed to align with PostGIS. #63798
• Validation queries that run on behalf of schema changes, such as foreign key validations, unique constraint validations, and check constraint validations, will now use the vectorized engine and DistSQL distribution based on the defaults set in the cluster settings. This may speed up validation queries. #64005
• TRUNCATE is now less disruptive on tables with a lot of concurrent traffic. #64445

Bug fixes

• Fixed a correctness bug which caused partitioned index scans to omit rows where the value of the first index column was NULL. This bug was present since v19.2.0. #64049
• Fixed a bug where multiple concurrent invocations of cockroach debug zip could result in cluster instability. This bug had been present since CockroachDB v20.1. #64084
• Fixed a rare bug which could cause a crash if CREATE SCHEMA were run in a transaction which began prior to a cluster version update which enabled that statement. #63786
• Fixed a race condition where read-only requests during replica removal (e.g., during range merges or rebalancing) could be evaluated on the removed replica, returning an empty result. #64375
• Fixed a bug where encryption-at-rest metadata was not synced and might become corrupted during a hard reset. #64496
• Fixed a panic which could occur in cases after a RESTORE of a table using user-defined types. #63550
• Fixed bugs where TRUNCATE concurrent with index construction and other schema changes could result in corruption. #63153
• Fixed a bug where upon failure of the CREATE TABLE AS or CREATE MATERIALIZED VIEW statements, tables would be left in an invalid non-public state until GC instead of being marked as dropped, possibly causing spurious validation failures. The bug was introduced in earlier v20.2 testing releases. #63987
• Fixed crdb_internal.encode_key for user-defined types. This would previously return an error. #64031
• Fixed a theoretical issue in index backfills that could result in stale entries that would likely fail validation. #64045
• Fixed a bug where CockroachDB would either return an error or crash when comparing an infinite date coming from a subquery against a timestamp. #64075
• The CREATE STATISTICS statement no longer fails when creating statistics on a table with a partial index predicate containing references to an inverted-type column, such as JSON, ARRAY, GEOMETRY, or GEOGRAPHY. This bug was present since partial indexes were introduced in v20.2.0. #64226
• Fixed an issue where store information would be incorrectly redacted from the CockroachDB logs, when configured with redaction. #64333
• Previously, passwords in SQL statements in telemetry updates and crash reports were anonymized as *****. Passwords are now anonymized as '*****' so that the SQL statements do not result in parsing errors when executed. #64345
• Previously, the remote flows of execution in the vectorized engine could take quite a long time to shut down whenever a node participating in the plan dies. This has been fixed. #64220
• CockroachDB now correctly accounts for used memory when closing compressed files. #63916

Performance improvements

• Certain queries containing <tuple> IN (<subquery>) conditions may run significantly faster. #63869
• The Raft processing goroutine pool's size is now capped at 96. This was observed to prevent instability on large machines (32+ vCPU) in clusters with many ranges (50k+ per node). #64567
• The Raft scheduler now prioritizes the node liveness Range. This was observed to prevent instability on large machines (32+ vCPU) in clusters with many ranges (50k+ per node). #64567
• Reduced memory usage in some write-heavy workloads. Improved write performance when a lot of files are being deleted. Addressed one case where too many small sstable files were generated. #64599

Build changes

• CockroachDB now builds on Ubuntu 20.10 and other distros using gcc-10. #62201

v20.2.8

Release Date: April 23, 2021

SQL language changes

• RESTORE now re-validates restored indexes if they were restored from an incremental backup that was taken while the index was being created. #63314
• Fixed an ST_Segmentize panic when the number of segments to generate exceeded math.MaxInt64. #63759

Operational changes

• Added a new setting to set the garbage collector intent age threshold: kv.gc.intent_age_threshold. #62788

DB Console changes

• Fixed duplicates of statements on the Transaction Details page for multi-node clusters. #62797

Bug fixes

• The kv.closed_timestamp.target_duration setting set to 0 will now disable routing requests to follower replicas. #62441
• Added support for usernames with special characters to cockroach userfile. #60020
• Fixed an internal error that could occur during planning for queries involving tables with many columns and at least one GIN index. The error, "estimated distinct count must be non-zero", was caused by an invalid pointer access in the cardinality estimation code. #62561
• Fixed an error where writing files to cockroach userfile would sometimes result in an error claiming that the userfile table already existed. #62547
• Fixed a deficiency in the replication layer that could result in ranges becoming unavailable for prolonged periods of time (hours) when a write burst occurred under system overload. While unavailable, the range status page for the affected range would show a last index much larger than the committed index and no movement in these indexes on a quorum of the replicas. Note that this should be distinguished from the case in which enough replicas are offline to constitute a loss of quorum, where the replication layer can not make progress due to the loss of quorum itself. #61848
• Fixed a bug where tables that were created by CockroachDB v19.x or older that included foreign key constraints and were backed up with the 'revision_history' option would be malformed when restored by a v20.x cluster if the call to RESTORE used the AS OF SYSTEM TIME option. #62600
• Fixed "types cannot be matched for WITH RECURSIVE" error in cases where we can cast the type in the initial expression. #62825
• Fixed an error where CockroachDB could encounter an internal error in rare circumstances when executing queries via the vectorized engine that operate on columns of BOOL, BYTES, INT, and FLOAT types that have a mix of NULL and non-NULL values. #62916
• Fixed a rare bug that could cause a node to consume excessive CPU and become unresponsive. #62907
• Fixed an internal error that could occur during planning when a query used the output of an UPDATE's RETURNING clause, and one or more of the columns in the RETURNING clause were from a table specified in the FROM clause of the UPDATE (i.e., not from the table being updated). #62961
• Queries that reference tables with GEOMETRY or GEOGRAPHY GIN indexes and that call geospatial functions with constant NULL values cast to a type, like NULL::GEOMETRY or NULL::FLOAT8, no longer error. This bug was present since v20.2. #63005
• Dropping a foreign key that was added in the same transaction no longer triggers an internal error. This bug has been present since at least version v20.1. #62880
• Fixed a rare error "unexpected type *tree.DOidWrapper for AsJSON". #63232
• Fixed a bug where index backfill data may have been missed by BACKUP in incremental backups. #63265
• Fixed a bug where lease acquisitions of descriptors in an offline state may starve out bulk operations such BACKUP or RESTORE. #63229
• Fixed a bug in user-defined schemas whereby the dropping of any schema may prevent creation of schemas with the name of the database and may corrupt existing schemas of that name. #63397
• Incremental cluster backups may have missed data written to tables while they were OFFLINE. In practice, this could happen if RESTORE or IMPORT were running across incremental backups. This has now been fixed. #63392
• Fixed a rare issue that could cause replica divergence. These issues would be reported by the replica consistency checker, typically within 24 hours of occurrence, which would cause nodes to terminate. #63474
• CockroachDB now uses the existing primary key to validate indexes built for ALTER PRIMARY KEY changes. #63585
• Fixed an index out of range error that could occur when crdb_internal_mvcc_timestamp was selected as part of a view. It is now possible to select crdb_internal_mvcc_timestamp as part of a view as long as it is aliased with a different name. #63630
• Error gracefully when attempting to run ST_Segmentize and generating more than MaxInt64 points on a GEOGRAPHY. #63759
• Fixed a bug which could cause errors in DROP DATABASE CASCADE when the database contained temporary views in other sessions which were not explicitly marked as TEMPORARY. #63781
• Fixed an internal error that could occur when executing queries using a GIN index. The error was an index out of range error, and could occur in rare cases when a filter or join predicate contained at least two JSON, ARRAY, GEOMETRY or GEOGRAPHY expressions that were combined with AND. #63826
• IMPORT and RESTORE jobs are now restored as reverting so that they clean up after themselves. Previously, some of the writes of the jobs while they were running may have been missed by BACKUP. #63765

Performance improvements

• Improved logic in determining the configuration for data to avoid expensive work when there are a large number of user-defined schemas. #62356
• Improved performance of reverting IMPORT INTO jobs that import into empty tables. #63224

Change Data Capture

• Kafka and cloud storage sinks use a memory monitor to limit the amount of memory that can be used in internal buffers. #63633
• Connected the changefeed memory monitor to the parent SQL monitor to ensure that changefeeds do not try to use more memory than is available to the SQL server. #63410
• Made the Kafka library used in changefeeds configurable via the kafka_sink_config option to enable latency vs. throughput configuration. #63362

Contributors

This release includes 45 merged PRs by 22 authors.

v20.2.7

Release Date: March 29, 2021

SQL language changes

• The following cluster settings were added or changed:
  • sql.defaults.statement_timeout was added to control the default value for the statement_timeout session setting.
  • sql.defaults.idle_in_transaction_session_timeout was added to control the default value for the idle_in_transaction_session_timeout timeout setting.
  • sql.defaults.idle_in_session_timeout is now a public cluster setting. #62185

Bug fixes

• Previously, changefeeds were checking the SELECT privilege on every descriptor associated with targeted tables, including the database and custom types. They now only check SELECT on the targeted tables. #61661
• The names of custom types are no longer sent to Cockroach Labs in telemetry and crash reports. #61645
• Fixed a bug where some import failures would cause tables to stay OFFLINE when they should have been brought back to PUBLIC. #61480
• Fixed a runtime error observed with a SpanFromContext call in the stack trace. #61703
• Exporting data to userfile locations now works correctly. #61789
• The indexdef column in the pg_indexes table would always report that the index belonged to the public schema. Now it correctly reports user-defined schemas if necessary. #61753
• Previously, when using SHOW, the idle_in_session_timeout and idle_in_transaction_session_timeout settings would incorrectly display the value of the statement_timeout setting. This is now fixed. #61958
• Fixed a bug where random numbers generated as default expressions during an IMPORT would be likely to match (collide with) other generated values in the same column. #61630
• Previously, using EXPLAIN (OPT, ENV) would fail when used on a query that referenced a table in a user-defined schema. This is now fixed. #61890
• Fixed a bug that caused column does not exist errors in specific cases of UPDATE ... FROM statements. The error occurred when updating a DECIMAL column to a column in the FROM clause where the column had a CHECK constraint or was referenced by a partial index predicate. #61953
• Fixed command is too large errors in some cases when using EXPLAIN ANALYZE (DEBUG) or statement diagnostics on complex queries. #61910
• Previously, the target column of crdb_internal.zones would show names without properly accounting for user-defined schemas. This is now fixed. #62019
• Dropping and recreating a view/table/sequence in a transaction will now correctly error out if a conflicting object exists or if the drop is incomplete. #62343
• Fixed a bug that sometimes caused queries sent to a freshly restarted node to hang for a long time while the node caught up with replication. #62204
• Previously, the pg_type_is_visible built-in function did not correctly handle user-defined types. This is now fixed. #62231
• Fix a bug where full cluster restore would sometimes (very rarely) fail after retrying. #61217
• Fixed a bug where an enum with large numbers of values might cause unexpected errors when attempting to read from tables with columns using that enum. #62211

v20.2.6

Release Date: March 15, 2021

General changes

• tar is now included in the CockroachDB Dockerfile. This allows users to use kubectl cp on 20.2.x containers. #61403

SQL language changes

• Error messages for cross-database links now include a hint directing to the user to the deprecation docs. An example message looks like: ERROR: the view cannot refer to other databases; (see the 'sql.cross_db_views.enabled' cluster setting) SQLSTATE: 0A000 HINT: Note that cross database references will be removed in future releases. See: https://www.cockroachlabs.com/docs/releases/v21.1.html#deprecations #60009
• A new parse_timestamp function can now be used to parse absolute timestamp strings in computed column expressions or partial index predicates. #60814
• Most batches of data flowing through the vectorized execution engine will now be limited in size by sql.distsql.temp_storage.workmem (64MiB by default) which should improve the stability of CockroachDB clusters. #60571
• The ST_Buffer spatial function now requires at least 1 quadrant segment. #61360
• Prevented densifyFracs values of less than 1e-6 for ST_FrechetDistance and ST_HausdorffDistance spatial functions to protect panics and out of memory errors. #61434

Command-line changes

• The cockroach command now supports the command-line parameter --version which reports its version parameters. This makes cockroach --version equivalent to cockroach version. #59753
• The cockroach version command now supports a new parameter --build-tag. When --build-tag is specified, cockroach version displays the technical build tag, which makes it possible to integrate with automated deployment tools. #59753

DB Console changes

• Manually enqueueing a range in the GC queue now properly respects the SkipShouldQueue option. This can be useful to force the GC of a specific range. #60745

Bug fixes

• Re-enabled some file-level performance optimizations such as WAL preallocation and read-ahead that got inadvertently disabled in a past change. #59825
• Previously if RELEASE SAVEPOINT cockroach_restart was followed by ROLLBACK, the sql.txn.rollback.count metric would be incremented. This was incorrect, since the transaction had already committed. That metric is no longer incremented in this case. #60250
• Fixed a bug causing backups to fail with an error when trying to read a backup that was written. #59744
• Fixed a bug in the optimizer statistics code that could cause an unconstrained partial index scan to be preferred over a constrained scan of the same index. #60517
• Fixed a bug that caused errors for some queries on tables with GEOMETRY or GEOGRAPHY GIN indexes with filters containing shapes with zero area. #60599
• 20.2 introduced the ability to rebalance replicas between multiple stores on the same node. This change fixed a problem with that feature, where occasionally an intra-node rebalance would fail and a range would get stuck, permanently under-replicated. #60633
• Fixed an internal error caused in some cases involving JSON objects and arrays in a VALUES clause. #60808
• Previously, retryable errors in the cleanup phase of the type schema changer wouldn't be retried automatically in the background. This is now fixed. #60817
• Fixed a bug that could report that a protected timestamp limit was exceeded when the limit was disabled, if an error were to occur while protecting a record. #60960
• Previously, running DROP TYPE IF EXISTS on one existent type and another non-existent type would cause an unhandled error. This is now fixed. #60951
• Fixed a bug whereby high-latency global clusters could sometimes fall behind checkpointing resolved timestamps. #60925
• CockroachDB previously didn't account for some RAM used when disk-spilling operations (like sorts and hash joins) were using the temporary storage in the vectorized execution engine. This could result in out-of-memory crashes, especially when the rows are large in size. #61016
• Fixed execution errors for some queries that use set operations (UNION / EXCEPT / INTERSECT) where a column has types of different widths on the two sides (e.g., INT4 vs INT8). #61086
• Unexpected internal errors containing stack traces that reference a countingWriter nil pointer have now been fixed. #61072
• Made lease transfers during rebalancing adhere to the rate limit utilized in other lease transfer cases, which eliminates unexpected lease oscillations when adding a new node. #61038
• Previously, comparing a negative integer to an OID would fail to compare correctly because the integer was not converted to an unsigned representation first. This is now fixed for both comparisons and casts. #61166
• The SHOW CREATE output of a partitioned partial index now lists the PARTITION BY and WHERE clauses in the order accepted by the parser. The SHOW CREATE output of a partial interleaved index also now lists the INTERLEAVED and WHERE clauses in the order accepted by the parser. #61104
• Fixed a bug causing schema changes on databases and schemas to panic in 20.2 if they failed or were cancelled and entered the reverting state. These jobs are not actually possible to revert. With this change, the correct error causing the job to fail is now returned, and the job now enters the failed state with an error indicating that the job could not be reverted. #61160
• The SHOW TABLES FROM database command no longer shows a NULL estimated_row_count if inspecting a database that is not the current database. #61200
• Fixed a bug in crdb_internal.unsafe_upsert_namespace_entry related to tables and types in user-defined schemas. #61260
• Fixed a bug that prevented renaming a column that was referenced earlier in a transaction as part of a computed expression, index predicate, check expression, or not null constraint. #61257
• Schema change jobs associated with databases and schemas can no longer be canceled. Such jobs cannot actually be reverted successfully, so cancellation had no benefit and could have caused namespace corruption. #61254
• Fixed a bug with multi-store nodes where concurrent node startups could result in the re-use of store IDs. This could manifest in many different ways (e.g., replica thrashing due to the store ID collision). #61262
• Fixed a bug where DROP SCHEMA ... CASCADE could result in referenced types being dropped. #61259
• Fixed a bug whereby dropping a schema with a table that used a user-defined type which was not being dropped (because it is in a different schema) would result in a descriptor corruption due to a dangling back-reference to a dropped table on the type descriptor. #61259
• Fixed an internal error causing EXPLAIN statements on an INSERT with an input that was determined by the optimizer to produce no rows. #61312
• ALTER TYPE ... ADD VALUE changes are now picked up by the ARRAY type alias correctly. #61350
• Creating interleaved partitioned indexes is now disallowed. Previously, the database would crash when trying to create one. #61422
• A bug which caused UPSERT and INSERT ... ON CONFLICT ... DO UPDATE statements to fail on tables with both partial indexes and foreign key references has been fixed. This bug has been present since version 20.2.0. #61489
• An UPDATE ... FROM statement where the FROM clause contained column names that matched table column names previously returned an error if the table had a partial index predicate referencing those columns. This bug, present since partial indexes were released in version 20.2.0, has been fixed. #61557
• Fixed a bug causing an invalid tuple comparison using ANY to raise an internal error. In this case, CockroachDB now returns "unsupported comparison operator". #61658

Performance improvements

• Enabled some file-level optimizations such as WAL preallocation and read-ahead when encryption-at-rest is used. #59825
• Improved the optimizer's cost estimation of index scans that must visit multiple partitions. When an index has multiple partitions, the optimizer is now more likely to choose a constrained scan rather than a full index scan. This can lead to better plans and improved performance. It also improves the ability of the database to serve queries if one of the partitions is unavailable. #61069

Contributors

This release includes 52 merged PRs by 25 authors.

v20.2.5

Release Date: February 16, 2021

Security updates

• Added the ability to set region-specific callback URLs in the OIDC config. The server.oidc_authentication.redirect_url cluster setting can now accept JSON as an alternative to the basic URL string setting. If a JSON value is set, it must contain a redirect_url key that maps to an object with key-value pairs where the key is a region matching an existing locality setting, and the value is a callback URL. #59651

General changes

• CockroachDB now runs fewer threads in parallel if running inside a container with a CPU limit. #59184
• Added ability to further debug connections shut down automatically by the server. #59503
• Renamed instances of "Admin UI" to "DB Console" in the documentation of OIDC cluster settings. #59651

SQL language changes

• Introduced a cluster setting sql.show_tables.estimated_row_count.enabled, which defaults to true. If sql.show_tables.estimated_row_count.enabled=false, estimated_row_count will not display on SHOW TABLES which improves performance. #59776

Command-line changes

• Previously, for certain log files, CockroachDB would both flush individual writes (i.e., propagate them from within the cockroach process to the OS) and synchronize writes (i.e., ask the OS to confirm the log data was written to disk). The per-write synchronization part was unnecessary and, in fact, found to be possibly detrimental to performance and operating cost, so it was removed. Meanwhile, the log data continues to be flushed as previously, and CockroachDB periodically (i.e., every 30s) requests synchronization as previously. #58996

API endpoint changes

• The health API now checks that the SQL server is ready to accept clients when a readiness check is requested. #59383

Bug fixes

• CockroachDB could previously return an internal error when evaluating a binary expression between a DECIMAL and an INTERVAL that required a cast to a FLOAT when the value is out of range. Now a more user-friendly error is returned instead. #58882
• Fixed a bug that caused errors when accessing a tuple column (tuple.column syntax) of a tuple that could be statically determined to be null. #58896
• Fixed a nil pointer panic edge case in query setup code. #59003
• Garbage collection jobs now populate the running_status column for SHOW JOBS. #59137
• Fixed a bug in which some non-conflicting rows provided as input to an INSERT ... ON CONFLICT DO NOTHING statement could be discarded, and not inserted. This could happen in cases where the table had one or more unique indexes in addition to the primary index, and some of the rows in the input conflicted with existing values in one or more unique indexes. This scenario could cause the rows that did not conflict to be erroneously discarded. #59169
• Fixed a bug causing queries to hang when using large max_decimal_digits on ST_AsGeoJSON. #59167
• Improved the accuracy of reported CPU usage when running in containers. #59184
• Fixed a nil pointer panic bug involving catalog.FilterDescriptorState. This bug affected all 20.2 versions since v20.2.0. #58998
• Fixed a bug causing CockroachDB to encounter an internal error when executing queries with BYTES or STRING types via the vectorized engine in rare circumstances. #59151
• Fixed a bug that initialized the GEOS library incorrectly, preventing multi-tenancy SQL pods from starting up correctly. #59260
• Fixed a bug in the URL handling of HTTP external storage paths on Windows #59267
• Fixed a bug causing CockroachDB to encounter an internal error when executing queries with tuples containing NULL values and ENUMs in a distributed setting. #59025
• Fixed a bug causing CockroachDB to crash when executing ALTER INDEX ... SPLIT/UNSPLIT AT statements when more values were provided than explicitly specified in the index. #59271
• Fixed a panic where type hints mismatching placeholder names caused a crash. #59463
• Previously, CockroachDB did not pass a pointer to the bound account associated with the index backfiller. This would lead to incorrect memory accounting. This bug has been fixed. #59475
• Previously, the substring function on BYTES arrays would treat its input as unicode code points, which would cause the wrong bytes to be returned. Now it only operates on the raw bytes. #59170
• Previously, the substring(byte[]) functions were not able to interpret bytes that had the \ character, as the functions were treating the character as the beginning of an escape sequence. This is now fixed. #59170

v20.2.4

Release Date: January 21, 2021

Security updates

• When using a SQL proxy, in the default configuration CockroachDB only knows about the network address of the proxy. That peer address is then used for logging, authentication rules, etc. This is undesirable, as security logging and authentication rules need to operate on the actual (final) client address instead. CockroachDB can now be configured to solve this problem using the mechanism detailed below. When so configured, a SQL proxy can inform the CockroachDB server of the real address of the client via a server status parameter called crdb:remote_addr. The value must be the IP address of the client, followed by a colon, followed by the port number, using the standard Go syntax (e.g., 11.22.33.44:5566 for IPv4, [11:22::33]:4455 for IPv6). When provided, this value overrides the SQL proxy's address for logging and authentication purposes. In any case, the original peer address is also logged alongside the client address (overridden or not), via the new logging tag peer. Security considerations:
  • Enabling this feature allows the peer to spoof its address with respect to authentication and thus bypass authentication rules that would otherwise apply to its address, which can introduce a serious security vulnerability if the peer is not trusted. This is why this feature is not enabled by default, and must only be enabled when using a trusted SQL proxy.
  • This feature should only be used with SQL proxies which actively scrub a crdb:remote_addr parameter received by a remote client, and replaces it by its own. If the proxy mistakenly forwards the parameter as provided by the client, it opens the door to the aforementioned security vulnerability.
  • Care must be taken in host-based authentication (HBA) rules:
    • TLS client cert validation, if requested by a rule, is still performed using the certificate presented by the proxy, not that presented by the client. This means that this new feature is not sufficient to forward TLS client cert authn through a proxy. (If TLS client cert authn is required, it must be performed by the proxy directly.)
    • The protocol field (first column) continues to apply to the connection type between CockroachDB and the proxy, not between the proxy and the client. Only the 4th column (the CIDR pattern) is matched against the proxy-provided remote address override. Therefore, it is not possible to apply different rules to different client address when proxying TCP connections via a unix socket, because HBA rules for unix connections do not use the address column. Also when proxying client SSL connections via a non-SSL proxy connection, or proxying client non-SSL connections via a SSL proxy connection, care must be taken to configure address-based rule matching using the proper connection type. A reliable way to bypass this complexity is to only use the host connection type which applies equally to SSL and non-SSL connections. As of this implementation, the feature is enabled using the non-documented environment variable COCKROACH_TRUST_CLIENT_PROVIDED_SQL_REMOTE_ADDR. The use of an environment variable is a stop-gap so that this feature can be used in CockroachCloud SQL pods which do not have access to cluster settings. The environment variable will be eventually removed and replaced by another mechanism. #58380

SQL language changes

• Added an overload to crdb_internal.pb_to_json to suppress populating default values in fields. #58127
• A table can now be successfully dropped in a transaction following other schema changes to the table in the same transaction. #58255
• The crdb_internal.cluster_id function now returns the ID of the underlying KV cluster in multi-tenant scenarios rather than the Nil UUID. #58441
• Multi-tenant clusters will now send anonymous usage information to the central CockroachDB registration server. #58517

Command-line changes

• Added a flag to cockroach debug decode-proto to suppress populating default values in fields. #58127
• Some specific CLI usage situations now have dedicated exit status codes. #56724 The codes are defined as follows:
  • 0: Process terminated without error.
  • 1: An unspecified error was encountered. Explanation should be present in the stderr or logging output.
  • 2: Go runtime error, or uncaught panic. Likely a bug in CockroachDB. Explanation may be present in logging output.
  • 3: Server process interrupted gracefully with Ctrl+C / SIGINT.
  • 4: Command-line flag error.
  • 5: A logging operation to the process' stderr stream failed (e.g., stderr has been closed). Some details may be present in the file output, if enabled.
  • 6: A logging operation to file has failed (e.g., log disk full, no inodes, permission issue, etc.). Some details may be present in the stderr stream.
  • 7: Server detected an internal error and triggered an emergency shutdown.
  • 8: Logging failed while processing an emergency shutdown.

DB Console changes

• Made minor style changes to represent the new branding palette. #57978
• Changed the default per-page value on the Transactions page to 20; made minor style updates. #57978

Bug fixes

• Fixed a bug where cockroach demo --global was crashing with "didn't get expected magic bytes header". #58626
• Fixed a bug in RESTORE where some unusual range boundaries in interleaved tables caused an error. #58259
• Fixed a bug that was introduced in the v20.2.0 release where we mistakenly permitted users with the admin role to drop tables in the system database. This commit revokes that privilege. #57642
• Fix a bug where the ST_MakeLine and ST_Collect functions did not respect ordering when used over a window clause. #57750
• Fix a bug that could cause IMPORT to incorrectly read files stored on Google Cloud if uploaded using its compression option (gsutil -Z). #57748
• Fixed a bug where schema change jobs to add foreign keysto existing tables, via ALTER TABLE, could sometimes not be successfully reverted (either due to being canceled or having failed). #57809
• Fixes a bug where concurrent addition of a foreign key constraint and drop of a unique index could cause the foreign key constraint to be added with no unique constraint on the referenced columns. #57809
• Fixed an assertion error caused by some DDL statements used in conjunction with common table expressions (WITH). #57951
• Fixed a bug where canceled queries reading from virtual tables could cause a crashing panic. #57884
• Fixed a bug where tables in schemas other than "public" would not be displayed when running SHOW TABLES FROM <db>. #57814
• Fixed a bug that caused temp tables to not be cleaned up after the associated session was closed. #57922
• Fixed a bug which caused type information to be omitted when decoding descriptors using either crdb_internal.pb_to_json or cockroach debug decode-proto. #58127
• Fixed a bug where CockroachDB could return non-deterministic output when querying the information_schema.statistics virtual table (internally used by SHOW INDEXES command). Specifically, the implicit columns of the secondary indexes could be in arbitrary order. The columns will now be in the same order as they are in the primary index. #58214
• Fixed a crash with the message "column family 0 not found" caused by EXPLAINing or gathering statement diagnostics on certain queries involving virtual tables. #58243
• Fixed a potential "nil pointer dereference" panic when collecting diagnostics on certain queries. #58210
• Added a safeguard against crashes while running SHOW STATISTICS USING JSON, which is used internally for statement diagnostics and EXPLAIN ANALYZE (DEBUG). #58263
• Fixed a bug where prior schema changes on a table that failed and could not be fully reverted could prevent the table from being dropped. #58255
• Fixed a bug introduced in 20.1 where CockroachDB could crash when performing a DELETE operation after an alteration of the primary key in some cases. #58266
• Fixed a memory leak in the optimizer. The leak could have caused unbounded growth of memory usage for a session when planning queries on tables with partial indexes. #58308
• Fixed a bug which could cause incremental backups to a backup in a collection (i.e., BACKUP INTO ... IN ...) on some cloud storage providers to ignore existing incremental backups previously appended to that destination, and instead backup incrementally from the base backup in that destination. #58331
• Fixed a storage layer bug that could cause deleted system.jobs rows to remain on-disk indefinitely. The bloated system.jobs table could make jobs completely unavailable and prevent DDL statements from executing. This bug can be detected by examining the system.jobs table size from the DB Console. This change fixes the bug for Pebble only and the bug still persists on RocksDB. #58304
• Fixed a bug where prepared statements that included enums and used the binary format could result in an error. #58044
• Fixed a internal panic when using the SHOW STATISTICS USING JSON statement on a table containing ENUM types. #58413
• Fixed a bug where SHOW GRANTS ON DATABASE did not include privileges that were granted on a database. The output includes database_name, schema_name, grantee, and privilege_type. The schema_name is always "public" since these grants are not per-schema. #58096
• Fixed a bug where the information_schema.schema_privileges table did not includes the correct schema-level privileges for non-user-defined schemas. #58096
• The has_schema_privilege built-in function now works on user-defined schemas when checking for the USAGE privilege. #58096
• Fixed a bug which caused errors when querying a table with a disjunctive filter (an OR expression) that is the same or similar to the predicate of one of the table's partial indexes. #58437
• Fixed a bug where a CREATE TABLE statement with indexes with duplicate names resulted in an assertion failure. This bug was present since version 20.2. #58447
• Fixed a bug where the has_${OBJECT}_privilege built-in functions such as has_schema_privilege did not check whether roles the user is a direct or indirect member of also have privileges on the object. Previously only one user was checked which was incorrect. This bug has been present since version 2.0 but became more prominent as of v20.2 when role-based access control was included in CockroachDB Core. #58512
• Fixed a bug where CockroachDB would return an internal error when attempting to execute a hash join on a JSON column via the vectorized engine. Now a more user-friendly error is returned. #58709
• Fixed a panic in protobuf decoding. #58720

Performance improvements

• Fixed a performance regression where the user authentication flow was performing extraneous name lookups. This regression was present since v20.2. #58739
• Previously, when performing an unordered DISTINCT operation via the vectorized execution engine, CockroachDB would buffer up all tuples from the input, which is a suboptimal behavior when the query has a LIMIT clause. This behavior was introduced in v20.1, and has now been fixed. Note that the old row-by-row engine doesn't have this issue. #57643
• Partial indexes with IS NOT NULL predicates can be used in cases where JOIN filters implicitly imply the predicate. This results in more efficient query plans for JOINs and foreign key checks. #58272
• SQL statistics collection has been made more efficient by avoiding an accidental heap allocation per row for some schemas. #58197

Miscellaneous

• Correctly export schedules_BACKUP_* metrics as well as a backup RPO metric. #57512

Contributors

This release includes 58 merged PRs by 27 authors.

v20.2.3

Release Date: December 14, 2020

SQL language changes

• Added admin-only crdb_internal functions to enable descriptor repair in dire circumstances. #56937
• Introduced a hint when GEOS is improperly installed to read the documented instructions on installing CockroachDB #57033
• Added support for running IMPORT in a mixed-version cluster. #57599

API endpoint changes

• Added a new prometheus metric called seconds_until_license_expiry that reports on the number of seconds until the enterprise license on the cluster expires and 0 if there is no license. It will return a negative number if the expiration is in the past. #56463

DB Console changes

• Updated design for custom date range selector on the Cluster > Node Map and Metrics pages. #57115
• Job details page now shows description for failed job. #57116

Bug fixes

• Eliminated opportunity for live lock in jobs subsystem due to frequent updates to already-finished jobs. #56864
• Fixed a bug causing the LogFile reserved API, which was used by cockroach debug zip, to corrupt log entries. #56902
• Fixed a bug introduced in the 20.2 series that could cause CockroachDB to crash due to range scans over virtual tables with virtual indexes. #56924
• Fixed a race condition in the tpcc workload with the --scatter flag where tables could be scattered multiple times or not at all. #56978
• Fixed a bug causing tables and metadata to be unavailable due to spurious missing fk back reference validation errors. #57083
• Fixed a bug related to the validation of un-upgraded, pre-19.2 inbound foreign keys. #57133
• Creating a materialized view that references a column with a NULL value no longer results in an error. #57193
• Fixed a bug that caused an "ambiguous column reference" error during foreign key cascading updates. This error was incorrectly produced when the child table's reference column name was equal to the concatenation of the parent's reference column name and "_new", and when the child table had a CHECK constraint, a computed column, or a partial index predicate expression that referenced the column. This bug was introduced in version 20.2.0. #57234
• Fixed a bug that could cause a crash loop in rare circumstances. #57312
• ST_GeomFromGeoJSON now sets the SRID to 4326, matching PostGIS 3.0 / RFC7946 behavior. #57245
• SELECT FOR UPDATE now requires both SELECT and UPDATE privileges, instead of just UPDATE privileges. #57350
• Fixed a bug that caused errors or corrupted partial indexes of child tables in foreign key relationships with cascading UPDATEs and DELETEs. The corrupt partial indexes could result in incorrect query results. Any partial indexes on child tables of foreign key relationships with ON DELETE CASCADE or ON UPDATE CASCADE actions may be corrupt and should be dropped and re-created. This bug was introduced in version 20.2.0. #57325
• Fixed a bug that could cause new nodes to fail to start up when upgrading to 20.2, due to a startup migration which would fail to terminate due to incorrect pagination in the presence of at least 100 running jobs. #57437
• Fixed a bug causing an internal error when executing JSONB - String operations via the vectorized execution engine. This bug was introduced in version 20.2.0. #57388
• Fixed an internal error that could occur when using aggregate and window functions in an ORDER BY for a UNION or VALUES clause. #57521
• DROP TYPE and certain other statements that work over SQL scalar types now properly support type names containing special characters. #57558
• Fixed performance regression to reading virtual tables which introspect the schema. This regression was introduced in version 20.2.0. #57574
• Removed the system.jobs full table scan, which can be expensive with many completed jobs. #57591
• Users can now perform a cluster restore from old backup chains (i.e., incremental backups on top of full backups), when using the BACKUP INTO syntax. #57667
• Fixed a bug causing CockroachDB to crash when creating backup schedules writing to GCS buckets. #57651
• Fixed a bug causing users of the OSS builds of CockroachDB to see "Page Not Found" when loading the DB Console. #56776

Performance improvements

• Interactions between Raft heartbeats and the Raft goroutine pool scheduler are now more efficient and avoid excessive mutex contention. This was observed to prevent instability on large machines (32+ vCPU) in clusters with many ranges (50k+ per node). #57008

v20.2.2

Release Date: November 25, 2020

Security updates

• CockroachDB no longer reports the use of expired or invalid web auth cookies in the log file by default. #55298
• Updated the state validation for the OIDC login flow and replaced it with a stateless hash validation of the state parameter with the browser cookie using HMAC. #56502

General changes

• Added metrics to track job execution for various job types. These metrics include:

#55500

Enterprise edition changes

• The insecure_tls_skip_verify query string parameter may now be set on changefeed sinks. This disables client-side validation of responses and should be avoided if possible since it creates MITM vulnerabilities unless combined with another method of authentication. #56338
• Added metrics to track the current number of running CHANGEFEEDs and the number of failed changefeed jobs. #56456

SQL language changes

• Updated the TransactionRetryWithProtoRefreshError to include a HINT field that links to a web page containing useful information to resolve the error. #56049
• Parsing intervals with fractional years now produces intervals with no more precision than months, to match the behavior of Postgres. #56158
• The pg_attribute.atttypmod column in the pg_catalog is now populated for collated string types. This also populates the value of the TypeModifier in the RowDescription message of the pgwire protocol. #55154
• Added an option to scheduled backups to maintain a timeseries metric for last backed up timestamp. #54987
• Added an owner column to the following statements: SHOW DATABASES, SHOW ENUMS,SHOW TABLES, and SHOW SCHEMAS. #56325
• A maximum of 65336 quadrant segments is allowed for the ST_Buffer spatial function. This used to be unlimited. #56676
• A maximum of 65336 points can be interpolated for repeat=true option to the ST_InterpolatePoints spatial function. #56676
• ALTER TABLE ... SET DATA TYPE ... is no longer available for operations that involve conversion (e.g., STRING to TIMESTAMPTZ) or precision/width truncation (e.g., INT(4) -> INT(2)) due to a bug in validation. These features are already gated by the session variable enable_experimental_alter_column_type_general - setting this session variable is now a no-op. #56629

• Previously, timestamp/timestamptz - timestamp/timestamptz operators would normalize the interval into months, days, H:M:S (in older versions, this may be just H:M:S). This can give an incorrect result:

  select '2020-01-01'::timestamptz - '2018-01-01';
  

       ?column?
  -------------------
    2 years 10 days
  (1 row)
  

  This has now been fixed to be more Postgres compatible such that it is only normalized into days/H:M:S. #56751

• Previously, the age built-in would incorrectly normalize months and days based on 30 days a month (in older versions this may be just H:M:S). This can give an incorrect result:

  select age('2020-01-01'::timestamptz, '2018-01-01');
  

          age
  -------------------
    2 years 10 days
  (1 row)
  

  This is not as accurate as it could be, since age can use the given TIMESTAMPTZ arguments to be more accurate. This has been updated to be more Postgres compatible. #56751

API endpoint changes

• AWS_SERVER_ENC_MODE and AWS_SERVER_KMS_ID can now be specified as parameters in all S3 URIs that write to the store. This change affects both changefeeds and BACKUPs. #56132

DB Console changes

• Fixes a bug where the "Other Execution Statistics" box in the Statement Details page would be empty in situations where the same fingerprint had been processed by multiple nodes. #56774
• A link to the Transactions Page is now shown between the Sessions and Statements links in the left hand navigation. This more clearly reflects the hierarchy between the 3 concepts. #56652
• Rename Active Sessions to Sessions on the Session Details page. #56652
• The diagnostics column on the Statements Page has been changed and includes an Activate button and a dropdown list to download completed reports. Also, the diagnostics badge status is changed from WAITING FOR QUERY to WAITING. #55890
• Fixed an issue where the Statement Details page didn't scroll on top when navigating from the Statements Page. #55433
• Loading table-level statistics on the Databases Page now requires a button click per-database in order to prevent contention for clusters with many databases and/or tables. In addition, the loading of table data is staggered by table instead of triggered simultaneously for all tables. #55777
• Added a new cluster setting called server.oidc_authentication.autologin which enables an automatic redirect to the OIDC login flow instead of showing a password login prompt. A query parameter can force disable this feature in the browser by appending ?oidc_auto_login=false to the login path. #56510
• Adjusted the styles for the Session Details page. #55889
• Transaction statistics are no longer recorded if the sql.metrics.statement_details.threshold cluster setting has been enabled. #56380
• Fixed link colors for "Back" link on the Node Overview, Jobs, Sessions, and Statement Details pages. #55889

Performance improvements

• Adjusted the cost model in the optimizer so that the optimizer is less likely to plan a lookup join into a virtual table. Performing a lookup join into a virtual table is expensive, so this change will generally result in better performance for queries involving joins with virtual tables. #56349

Bug fixes

• Fixed a bug introduced in v20.2 where we failed to upgrade foreign keys that used the pre-19.2-style internal representation when validating cross-references for tables. This bug caused validation failures that made the referenced tables and metadata unavailable. The fix gets rid of the validation errors by accounting for the pre-19.2-style internal representation of foreign keys. #57083
• Fixed a bug where CockroachDB would crash when executing a query via the vectorized engine when most of the SQL memory (determined via --max-sql-memory argument to cockroach start) had already been reserved. #55457
• Fixed a rare bug which could lead to possible write skew in distributed queries that have both zigzag joins and table readers with the zigzag joins reading keys not read by the table readers. #55563
• The current implementation of changefeeds is incompatible with the vectorized engine. Therefore, whenever the vectorized engine was being used to run changefeeds, the command could hang indefinitely. This bug is now fixed. On v20.2 releases this could happen if the user ran SET vectorize_row_count_threshold=0;, and on v20.1 releases it could happen if the user ran SET vectorize=on. #55753
• Fixed a bug where CockroachDB could incorrectly evaluate the sqrdiff function when used as a window function in some cases. #55995
• Fixed a bug where CockroachDB could incorrectly compute some aggregate functions with DISTINCT clauses when the query projected other columns/functions and the vectorized engine was used. This bug was introduced in the v20.2.0.alpha.3 release. #55872
• Fixed a bug where CockroachDB could return incorrect results when computing the aggregate functions when some of the functions have DISTINCT clauses and some do not (the latter might not see all the necessary data). #55872
• Fixed a bug where the JSON fetch value operator -> would evaluate incorrectly in some cases. #55447
• Scheduled BACKUP now supports KMS encryption. #56099
• Fixed a bug where IMPORTs of malformed Avro records could hang forever. #56094
• Updated CockroachDB to avoid crashing when BACKUP is unable to count the total nodes in the cluster. #56050
• Fixed a bug where an IMPORT PGDUMP with INSERTs not targeting all columns in the database would panic. #56034
• Using the min or max aggregate functions in a prepared statement will now report the correct data type size. #55622
• Fixed an error "top-level relational expression cannot have outer columns" that could occur in some queries that involve a WITH expression. #56084
• Fixed an internal error that could occur during query planning when the use_spheroid parameter was used in the ST_Distance spatial function as part of a filter predicate. For example, SELECT ... WHERE ST_Distance(geog1, geog2, false) < 10 previously caused an error. #55852
• Fixed a bug where CockroachDB previously didn't account for all the memory used by the vectorized hash aggregation which could lead to an OOM crash. #55555
• Fixed a bug which allowed statements after a schema change to fail to observe side-effects of that change on referenced tables. #56327
• Fixed a bug where if a cluster backup was taken during a schema change, a cluster restore of that backup would create duplicates of the ongoing schema changes. #56390
• Fixed a bug where dumps of tables with a BIT type column would result in an error. This column type is now supported. #56391
• Fixed a bug which would cause transactions that modified roles and then attempted to read or modify other roles to encounter blocking and stale data. #55392
• Fixed a bug where CockroachDB did not respect disabling protected timestamp settings kv.protectedts.max_bytes and kv.protectedts.max_spans by setting them to zero values. #56453
• Fixed a panic that could occur when running SHOW STATISTICS USING JSON for a table in which at least one of the columns contained all NULL values. #56516
• Fixed a hypothesized bug that could have allowed a follower read to miss data on a range in the middle of being merged away into its left-hand neighbor. #55691
• Fixed a bug introduced in an alpha where IMPORTs of tables with foreign keys can fail in rare circumstances. #56457
• Fixed a bug which would prevent the dropping of hash sharded indexes if they were added prior to other columns. #55822
• Fixed a bug which cause CockroachDB to crash when executing a query with an AS OF SYSTEM TIME clause that attempted to use an unspecified placeholder value on a non-prepared statement. #56780
• Fixed an internal error when a DATE/TIMESTAMP/TIMESTAMPTZ from the year 1 BC was sent between nodes for execution. Also, fixed a bug where it was not possible to specify the DATE/TIMESTAMP/TIMESTAMPTZ of the year 1 BC without using the AD/BC notation. #56742
• Some boolean session variables would only accept quoted string values "true" or "false". Now they accept unquoted true or false values too. #56813
• Fixed an internal error that could occur when collecting a statement diagnostic bundle. #56784

v20.2.1

Release Date: November 20, 2020

Bug fixes

• Fixed a crash, introduced in the v20.2 series, caused by range scans over virtual tables with virtual indexes. #56459
• Fixed a bug the occurred when the Pebble storage engine was used with encryption-at-rest that could result in data corruption in some fairly rare cases after a table drop, table truncate, or replica deletion. #56678

Contributors

This release includes 3 merged PRs by 3 authors.

v20.2.0

Release Date: November 10, 2020

With the release of CockroachDB v20.2, we've made a variety of productivity, management, and performance improvements. Check out a summary of the most significant user-facing changes and then upgrade to CockroachDB v20.2.

To learn more, read the v20.2 blog post.

CockroachCloud

Get a free v20.2 cluster on CockroachCloud

Recent CockroachCloud improvements:

• Create a 30-day free CockroachCloud cluster.
• Add or remove nodes through the CockroachDB Cloud Console.
• Set up VPC peering for clusters running on GCP.
• View backups that Cockroach Labs has taken for your CockroachCloud cluster.

Feature summary

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

• SQL
• Recovery and I/O
• Deployment and Operations
• Observability
• Backward-incompatible changes
• Deprecations
• Known limitations
• Education

SQL

Recovery and I/O

Deployment and Operations

Observability

Backward-incompatible changes

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

• A CockroachDB node started with cockroach start without the --join flag no longer automatically initializes the cluster. The cockroach init command is now mandatory. The auto-initialization behavior had been deprecated in version 19.2.
• CockroachDB v20.1 introduced a new rule for the --join flag to cockroach start, causing it to prefer SRV records, if present in DNS, to look up the peer nodes to join. This feature is experimental, and has been found to cause disruption in certain deployments. To reduce this disruption and the resulting UX surprise, the feature is now gated behind a new command-line flag --experimental-dns-srv which must now be explicitly passed to cockroach start to enable it.
• The --socket flag of cockroach start was deprecated in v20.1 and has been removed in v20.2. Use --socket-dir instead.
• The textual error and warning messages displayed by cockroach quit under various circumstances have been updated. Meanwhile, the message "ok" remains as an indicator that the operation has likely succeeded.
• cockroach quit now prints out progress details on its standard error stream, even when --logtostderr is not specified. Previously, nothing was printed on standard error. Scripts that wish to ignore this output can redirect the stderr stream.
• Previously, the phase of server shutdown responsible for range lease transfers to other nodes would give up after 10000 attempts of transferring replica leases away, regardless of the value of server.shutdown.lease_transfer_wait. The limit of 10000 attempts has been removed, so that now only the maximum duration server.shutdown.lease_transfer_wait applies.
• Previously, issuing a SIGTERM signal twice or after another signal initiated a hard shutdown for a node. Now the first SIGTERM signal initiates a graceful shutdown and further occurrences of SIGTERM are ignored. To initiate a hard shutdown, issue SIGINT two times (or issue a SIGINT signal once after a SIGTERM signal).
• Clusters running alphas of 20.2 that use ENUM types will not be able to upgrade to betas or major releases of 20.2 due to internal representation changes.
• Specifying the same option multiple times in the WITH clause of the BACKUP or RESTORE statement now results in an error message. Additionally, quoted option names are no longer allowed.
• The copy of system and crdb_internal tables extracted by cockroach debug zip is now written using the TSV format (inside the zip file), instead of an ASCII-art table as previously.
• The SHOW RANGE FOR ROW statement now takes a tuple of the row's index columns instead of the full column set of the row.
• For expression typing involving only operations on constant literals, each constant literal is now assigned a type before calculation. Previously, a type was assigned only to the result.
• The file names for heap profile dumps now use the naming scheme memprof.<date-and-time>.<heapsize>. Previously, they were named memprof.<heapsize>.<date-and-time>.
• The Docker image is now based on RedHat's UBI instead of Debian.
• cockroach node decommission --wait=live is no longer supported. It was deprecated in an earlier release.

Deprecations

• The cockroach quit command is now deprecated. For decommissioning, use the cockroach node decommission command. To terminate the cockroach process, use signals.
• The cockroach dump command is now deprecated. Instead, back up your data in a full backup, export your data in plain text format, or view table schema in plaintext with SHOW CREATE TABLE.
• The --log-dir-max-size command-line flag is now deprecated and has been replaced with a new flag named --log-group-max-size. The flags limit the combined size of all files generated by one logging group inside CockroachDB.
• CockroachDB built-in SQL shell (cockroach sql and/or cockroach demo) no longer prompts for more lines of input after the user enters BEGIN before sending the input to the server. Instead, full lines of input are always sent to the server immediately. The corresponding client-side option smart_prompt is thus ineffective and deprecated. It will be removed in a later version.

• Cross-database references are deprecated in v20.2 (see tracking issue). In v20.2, creating cross-database references is disabled for foreign keys, views, and sequence ownership with the sql.cross_db_fks.enabled, sql.cross_db_views.enabled, and sql.cross_db_sequence_owners.enabled cluster settings set to false by default. Note that any cross-database references that were created prior to a v20.2 upgrade are still allowed and are unaffected by these cluster settings.

  After upgrading to v20.2, we recommend removing all cross-database references, and, if necessary, creating object references across user-defined schemas instead. For details on migrating a cluster that does not use user-defined schemas in its naming hierarchy, see Migrating namespaces from previous versions of CockroachDB.

• Interleaved tables are deprecated in CockroachDB v20.2, and will be permanently disabled in a future release (see tracking issue).

  After upgrading to v20.2, we recommend that you convert any existing interleaved tables to non-interleaved tables and replace any existing interleaved secondary indexes with non-interleaved indexes. For instructions, see INTERLEAVE IN PARENT Deprecation.

Known limitations

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

Education

v20.2.0-rc.4

Release Date: November 3, 2020

Bug fixes

• A bug in earlier v20.2 versions caused some GRANT and REVOKE commands on user-defined schemas to incorrectly fail with an "invalid privileges" error. This affected schemas that were created after granting privileges at the database level. This bug is now resolved.

Contributors

This release includes 1 merged PR by 1 author.

v20.2.0-rc.3

Release Date: October 26, 2020

DB Console changes

• Added the Transactions and Transactions Details pages. These pages allow for viewing stats at the transaction level. #55717

Bug fixes

• Previously, we used the HTTPS_PROXY variable for the "join RPC" when adding a node to the cluster (the RPC prevents new clusters from starting or adding nodes to an existing cluster). The proxy needed to be configured to transparently pass HTTP/2+GRPC inter-node traffic. This was an unintentional addition, and this patch ignores the proxies for all intra-node traffic. They were already ignored in releases prior to v20.2 testing releases. #55504
• Previously, the filenames for generated goroutine, CPU, and memory profiles were sometimes incorrect, which resulted in repeated warnings like strconv.ParseUint: parsing "txt": invalid syntax in log files. This has been corrected. #55366
• Fixed a bug that could occur for spatial queries involving a join between two spatial columns, when there was an additional filter on one of the spatial columns, and that column also had a GIN index defined. This bug could cause incorrect results to be returned, in which some rows were omitted from the output when they should have been included. #55673
• An INSERT into a table with a foreign key reference to a table with a partial index no longer causes an error. #55703
• Foreign keys can no longer reference columns that are only indexed by a partial unique index. A partial unique index does not guarantee uniqueness in the entire table; therefore, the column indexed is not guaranteed to be a unique key. #55703
• Previously, cluster backups taken from before v20.2 could not be restored in v20.2 clusters, and would result in an error message about failing to restore a system table. This is now fixed. #55719
• Previously, changing the parent database and schema of a table using RENAME was seemingly permitted but would lead to corruption of the table metadata. Now, an error is returned when attempting to rename a table to a different database— except in the case where both the source and target schemas are the public schema in each database, which continues to be supported. #55723
• Fixed a crash that would occur when performing a SHOW BACKUP against a backup that contains a table that references a type in another database. This state was only reachable in v20.2 testing releases. #55786
• Tables can no longer be moved to a different database using the ALTER TABLE ... RENAME TO statement if they have columns using user-defined types (i.e., ENUM). #55781

Contributors

This release includes 14 merged PRs by 14 authors.

v20.2.0-rc.2

Release Date: October 20, 2020

Backward-incompatible changes

• The Docker image is now based on RedHat's ubi8/ubi-minimal image (instead of ubi8/ubi). This image is smaller. #55519

Bug fixes

• Prevented a crash in pre-release v20.2 binaries in plans that use the new virtual table lookup join feature. #55321
• CockroachDB now returns an appropriate error when attempting to partition by an ENUM column instead of crashing. #55357
• Fixed an issue where DB Console screens were not working properly when the user was logged in with a username containing uppercase or non-normalized unicode characters. #55384
• The OIDC-based UI process now respects the LOGIN role option. #55384
• Added the hostname command to the Docker image so the image can be used with the CockroachDB Helm chart and cockroach-operator. #55390
• Fixed a bug that caused incorrect query results on tables with partial indexes. This bug did not affect any queries involving tables without partial indexes. #55394
• Previously, observer statements (e.g., SHOW SYNTAX) and PREPARE statements would display a negative execution time on the client. This is now fixed. #55431
• The CREATE USER statement without explicit NOLOGIN option implicitly grants LOGIN, and so requires the CREATELOGIN privilege. This was not checked properly, and is now enforced. This bug was introduced earlier in the v20.2 development cycle. #55369
• The information_schema.tables metadata table no longer ignores tables from other schemas when searching based on table name. #55522

Contributors

This release includes 11 merged PRs by 9 authors.

v20.2.0-rc.1

Release Date: October 15, 2020

Known limitations

• The new Docker image (see release note below) does not work with the CockroachDB Kubernetes manifest, so customers using Kubernetes cannot upgrade to this testing release. This will be fixed in the next testing release (see tracking issue).

Backward-incompatible changes

• CockroachDB Docker images are now based on the RedHat UBI 8 base image instead of Debian 9.12 slim. This will help on-premise customers from a security and compliance perspective. #55130
• For PostgreSQL compatibility, the CREATEROLE privilege is no longer inherited by children of a role which has that privilege. For example, say we run these statements: CREATE ROLE parent WITH CREATEROLE; CREATE ROLE child; GRANT parent TO child; Previously, the child role would have the CREATEROLE privilege. Now it will not. In order to grant this privilege to the child role, it is necessary to run ALTER ROLE child WITH CREATEROLE. #55305

Security updates

• Fixed a case where connections to Google Cloud Storage would ignore the --external-io-disable-implicit-credentials flag. #55090

General changes

• This change affects schema change jobs originally initiated on clusters running CockroachDB v19.2 or earlier which have not reached a terminal state (i.e., succeeded, failed, or canceled), and which have not finished undergoing an automatic internal migration to allow them to run in v20.1 clusters. These jobs will now be marked as failed upon upgrading to v20.2. Users who have ongoing schema changes initiated in v19.2 are advised to wait for them to finish running on v20.1 before upgrading to v20.2 (at the very least, wait until at least the v20.1 internal migration for the job has completed, which is indicated in the logs). This may also affect users who have schema change jobs from prior to v20.1 which are stuck in a non-terminal state due to bugs despite making no progress. In this case, marking the job as failed has no real effect. #54902

SQL language changes

• Implemented the geometry built-in functions ST_SwapOrdinates and ST_OrderingEquals. #54564
• Setting and retrieving zone configurations on tables from non-public schemas is now permitted. #54849
• A string literal like '{X, Y, Z}' is now automatically casted to an ARRAY when appropriate. Support is added for UUID, DATE, BOOL, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, FLOAT, INET, VARBIT, and INTERVAL arrays. Note that INT and DECIMAL arrays were already supported. #54944
• The sql.mem.root timeseries is now available to track the accounted memory usage of all memory in the cluster. #54904
• Populated the datdba field of pg_catalog.pg_database. This was previously DNull. #55069
• SHOW SEQUENCES now displays sequences in user-defined schemas. The schema is now displayed in a newly added sequence_schema column. #55175
• Added the parent_schema_id field to crdb_internal.tables. #55264

Command-line changes

• The --help texts and informational messages upon server start-up have been modified to better inform the user about the nature and risks of the --insecure flag. #55025

DB Console changes

• The DB Console's Databases page now includes tables that live within user-defined schemas. #54901
• Added a SQL Memory graph to the SQL Metrics dashboard that tracks the current number of bytes in all SQL memory accounts. This number is a current snapshot of the number whose maximum is set by --max-sql-memory. #54904

Bug fixes

• Fixed a bug where previous testing releases of v20.2 would not properly clear grants and owners on non-cluster restores. #54854
• Fixed a bug in v20.2 testing releases that under-accounted for scan memory. Note that the bug wasn't a regression from v20.1, which never had any scan memory accounting at all. #54894
• Fixed issue when jobs duration could be negative value or increased periodically for finished jobs. #54872
• Fixed a rare bug which could lead to index backfills failing in the face of transaction restarts. #54858
• Fixed a bug which led to inscrutable errors being returned when creating a backup failed; for example, due to protected timestamp limits. #54967
• Fixed a bug where a transaction restart at the wrong moment during a restore could leave descriptors offline after the restore completed successfully. #54965
• Fixed a bug where the presence of types or schemas in a database to be restored would prevent the database from being cleaned up on restore failure. #55060
• Previously, canceling schema changes when there were multiple queued schema changes could result in future schema changes being stuck. This has been fixed. #55056
• Fixed an error that could occur during planning when attempting to create or use a partial index. The error implied that the partial index could not be used (e.g., "my_index is a partial index that does not contain all the rows needed to execute this query") when in fact the partial index could be used correctly. #55080
• Cross-database temporary schemas are now properly resolved. #55198
• Previously, restoring a cluster backup that contained user-defined schemas or user-defined types in defaultdb would fail. #55249
• Options set on users (e.g., ALTER USER u CREATEDB) were not included in cluster backups and thus not restored. Role options have been introduced in v20.2. #55250
• Previously, all tables in any schema showed up as public in the schema_name column in the crdb_internal table. They now display the correct schema. #55264

v20.2.0-beta.4

Release Date: October 6, 2020

SQL language changes

• The default search path for all sessions is now $user, public (as opposed to just public). This affects our name resolution semantics; now, if a table is present in both the public schema and the schema named the current user's username, an unqualified object name will be searched/placed in the user's schema. This doesn't impact the search semantics of tables in pg_catalog/information_schema/temp_schema -- these continue to be searched before checking the $user schema and the public schema. #54586

Command-line changes

• The display of statement timings in the SQL shell (cockroach sql and cockroach demo) has been simplified. #54765

Bug fixes

• Previously, CockroachDB would crash when json_object_agg and jsonb_object_agg aggregate functions were used as window functions. Those functions were added in a v20.2 testing release, so only testing v20.2 releases were affected. This is now fixed. #54657
• Fixed a bug that could cause storage-level corruption under rare circumstances while using the Pebble storage engine. #54800

Performance improvements

• Optimized compactions in Pebble to improve read/write performance in some write-heavy workloads. #54800

Contributors

This release includes 13 merged PRs by 9 authors.

v20.2.0-beta.3

Release Date: September 30, 2020

Security updates

• Added ability to login via OIDC provider: Operators can now configure a single OIDC authentication provider using a collection of cluster settings that can provide authentication for users into the DB Console only (not for SQL). The user must exist as a SQL user in the database for the authentication to work. #54659

General changes

• Reduced the memory overhead of rangefeeds (i.e., long-lived requests) which reduces the memory overhead for running CHANGEFEEDs over large tables. #54631

SQL language changes

General SQL updates

• After renaming a database, schema, or type, the old names are now immediately inaccessible from other sessions when referred to in schema change statements as soon as the new name is committed. This matches the existing behavior for tables. This is a change from earlier 20.2 beta releases. #54384
• Added the ability to parse the ONLY and * (descendant) clauses in UPDATE. This is a no-op as we do not support table inheritance. #54426
• Added the ability to parse the CONNECTION LIMIT [=] iconst32 syntax for CREATE DATABASE. This is currently a no-op for -1, and errors for any other value. By default, the value is -1 (unlimited connections). #54421
• Creating cross-database foreign key references is now disallowed (and can be re-enabled via the sql.cross_db_fks.enabled cluster setting). #54520
• Creating views that refer to tables in other databases is now disallowed (and can be re-enabled via the sql.cross_db_views.enabled cluster setting). #54520
• Partial index definitions in pg_catalog are now formatted with parentheses around the WHERE clause. #54535
• Interleaved joins are now disabled by default and will be entirely removed in the 21.1 release, because they are often slower than the merge join. #54162
• Foreign key violation errors now fill in the "constraint name" error message field. #54576
• CREATE SCHEDULE output now has a column called label which was previously called name. #54397
• If a user/role has ownership over a type or a schema, it is no longer possible to drop them using DROP ROLE. #54511
• Implemented SHOW GRANTS ON SCHEMA <schema_list> #54596
• Added a custom information_schema.type_privileges table that displays type privileges for each supported type. #54596
• Implemented the SHOW GRANTS ON TYPE command, which shows grants for a specific type. #54596
• The SHOW GRANTS without any table/type/schema/database qualifier now also shows types. The table_name column is renamed to type_name. #54596
• Added support for the Postgres CREATE EXTENSION syntax. This no-ops for the postgis extension, and gives unimplemented errors for extensions we do not yet support. #54595
• Added unimplemented errors for using operator classes as parameters for creating an index. #54595
• Creating sequences that are OWNED BY columns in tables in other databases is now disallowed (and can be re-enabled via the sql.cross_db_sequence_owners.enabled cluster setting). #54585

Spatial support updates

• Implemented the geometry built-ins ST_Boundary, ST_Difference, ST_Relate (BNR variant), ST_MinimumClearance, ST_MinimumClearanceLine, ST_Polygon, ST_Angle, ST_FrechetDistance, and ST_HausdorffDistance. #54436
• Implemented ST_S2Covering, which returns the S2 coverings used for indexing for geometry and geography types. #54433

Command-line changes

• The timing details for query execution in the SQL shell (e.g., cockroach sql, cockroach demo) have been completed to also properly take query parsing and planning time into account. As previously, this timing display can be disabled with \set show_times off. #54623
• The format used to display query execution times has been simplified. #54623

DB Console changes

• Added ability to login via OIDC provider. #54659

Bug fixes

• Fixed a bug introduced in earlier v20.2 versions where attempting to drop a column which is referenced by multiple indexes fails to drop all relevant indexes. #54262
• Fixed a bug causing servers to crash with the message "committed txn with writeTooOld". Versions below 20.1.4 are susceptible to this bug. Versions 20.1.4+ will not crash, but instead print messages to the log files. #54280
• Fixed a bug which would cause an internal error when writing to a table with a recently (concurrent or in the same transaction) NOT NULL enum column. #54432
• CockroachDB now properly returns an appropriate error when the user attempts to rename a constraint to a name which conflicts with an existing index. #54430
• Fixed a bug from earlier alphas where dropping a database which contained tables using user-defined types could result in panics. #54431
• Fixed an internal error and/or panic that could occur when the ST_Distance or ST_MaxDistance functions were compared against a constant or variable with any type other than float. For example, previously a query with the predicate WHERE ST_Distance(g1, g2) < 10::int could cause an error. #54395
• There was a bug in transaction statistics collection that could let the data structure grow unboundedly large. This is now fixed, and the resetting happens at the same cadence as statement statistics. #54457
• Fixed an internal error in some cases when recursive common table expressions were used. #54419
• Fixed a bug in our implementation of the Postgres wire protocol where using CopyIn with an extremely large message would close the TCP connection with no discernible error. This is changed to now display that the message is too big. #54187
• Starting with v20.2.0-alpha.3, CockroachDB would crash when performing an UPSERT with a RETURNING clause of more than 10k rows. This is now fixed. #54490
• Fixed a bug where queries that could be automatically retried did not respect the statement_timeout session setting. #54370
• Fixed a bug where a failure while restoring data may have resulted in the restore job getting stuck. This bug was only present on 20.2 alphas and betas. #54447
• CockroachDB could previously crash in very rare circumstances when there were many queries running in the cluster that were consuming a lot of memory and at least one query was running via the vectorized execution engine. This is now fixed. #54404
• In a previous beta, backing up to an auto-appendable directory would not work if authentication parameters were specified in the URI. #54621
• Fixed a bug from earlier alphas whereby jobs would not properly populate their started timestamp. #54638
• The first timing column in the trace.txt file collected as part of a statement diagnostics bundle has been fixed. #54559

Performance improvements

• The optimizer can now use partial indexes for lookup join operations. This results in potentially more efficient query plans for joins on tables with partial indexes. #54362
• Lookup semi- and anti-joins are now explored by the optimizer in more cases when the ON filter implies a partial index predicate. This may lead to more efficient query plans in some cases. #54362
• Left outer spatial joins can now be index-accelerated, which can lead to performance improvements in some cases. #54110
• Spatial anti joins can now be index-accelerated, which can lead to performance improvements in some cases. #54471

v20.2.0-beta.2

Release Date: September 25, 2020

Security updates

• A new experimental flag --accept-sql-without-tls has been introduced for cockroach start and cockroach-start-single-node. When specified, a secure node will also accept secure SQL connections without TLS. When this flag is enabled:

  • Node-to-node connections still use TLS: the server must still be started with --certs-dir and valid TLS cert configuration for nodes.
  • Client authentication (spoof protection) and authorization (access control and privilege escalation prevention) are performed by CockroachDB as usual, subject to the HBA configuration (for authentication) and SQL privileges (for authorization).
  • Transport-level security (integrity and confidentiality) for client connections must then be provided by the operator outside of CockroachDB—for example, by using a private network or VPN dedicated to CockroachDB and its client app(s).

  • The flag only applies to the SQL interface. TLS is still required for the HTTP endpoint (unless --unencrypted-localhost-http is passed) and for the RPC endpoint.

    To introduce this feature into an existing cluster:

    1. Ensure the cluster ugprade is finalized.
    2. Set up the HBA configuration to reject `host` connections for any network other than the one that has been secured.
    3. Add the command-line flag and restart the nodes. Note that even when the flag is supplied, clients can still negotiate TLS and present a valid TLS certificate to identify themselves (at least under the default HBA configuration).

    Finally, this flag is experimental and its ergonomics will likely change in a later version. #54198

SQL language changes

• Previously, certain SQL commands sent over the PostgreSQL FE/BE protocol that were too big would error opaquely. This is now resolved for non-PREPARE related statements, clearly error messaging instead. #54067
• Databases being restored will now be in the offline state, invisible to users, until the data has been restored. This is the same as the existing behavior for restored tables. (This change is also applied to enums and user-defined schemas being restored, which is a change relative to only the v20.2 alpha releases.) #54296
• Implemented the ST_SnapToGrid built-in. #54054

Command-line changes

• It is now possible to use password authentication over non-TLS connections with cockroach client CLI commands that use only SQL, e.g., cockroach sql or cockroach node ls. #54198

Bug fixes

• Fixed a "no binding for WithID" internal error when using WITH RECURSIVE in queries with placeholders. #54063
• Fixed a bug whereby a crash during WAL rotation could cause CockroachDB to error on restart reporting corruption. #54185
• Previously, RESTOREs that were cancelled could crash a node. This is now fixed. #54289
• Fixed two bugs when attempting to add constraints in the same transaction in which the table was created: Adding a NOT NULL constraint no longer fails with the error check ... does not exist, and adding a NOT VALID foreign key constraint no longer fails with the internal error table descriptor is not valid: duplicate constraint name. #54287
• Fixed a bug that could lead to out of memory errors when dropping large numbers of tables at high frequency. #54286
• Fixed a bug introduced in a v20.2 alpha release where incorrect caching could incur extra writes to the store during RESTORE on user-defined schemas. #54296

Doc updates

• Added guidance on performing large deletes with DELETE. #7999

Contributors

This release includes 22 merged PRs by 14 authors.

v20.2.0-beta.1

Release Date: September 14, 2020

In addition to various updates, enhancements, and bug fixes, this first v20.2 beta release includes the following major highlights:

• Backup schedules: You can now set up a recurring schedule that lets CockroachDB handle scheduling, conflict resolution, and resilience for your backups. Previously, it was necessary to create your own scheduler service or cron job to kick off BACKUP jobs. To create a schedule for a cluster-level backup that runs a full backup every day with the first full backup taken "now", run CREATE SCHEDULE test_backup_schedule FOR BACKUP INTO ‘<your-backup-storage-location’ RECURRING ‘@daily’ FULL BACKUP ALWAYS WITH SCHEDULE OPTIONS first_run=NOW.
• The following SQL features improve CockroachDB's PostgreSQL compatibility:
  • User-defined schemas: Within a given database, you can now create schemas to organize your data and create finer-grained access controls. Previously, the recommendation was to create a database wherever you would normally create a schema. Now, an existing database can be converted to a schema under an existing database using ALTER DATABASE ... CONVERT TO SCHEMA WITH PARENT ....
  • Enums: ENUM types can now be created in CockroachDB using CREATE TYPE ... AS ENUM. A column type can be set to an ENUM type, which limits the column values to those defined in the ENUM.
  • Partial indexes: Partial indexes allow you to specify a subset of rows and columns to add to an index, using a WHERE filter defined at index creation.

Backward-incompatible changes

• A change to the on-disk representation for user-defined schemas, enums, and databases was made that is backward-incompatible with v20.2 alpha releases. Only schemas, enums, and databases in the middle of being dropped at the time of the upgrade are affected, and the upgrade may result in the drop never running to completion. If you are upgrading from a v20.2 alpha release to a v20.2 beta release, we advise that you avoid dropping schemas, enums, and databases. #53387
• The command-line flag --socket has been removed. It was deprecated since v20.1. Use --socket-dir instead. #53405

Security updates

• Certificate revocation is now supported via OSCP and the cluster setting security.ocsp.mode. This makes a call to the OCSP server on every connection attempt and may wait for up to value specified with the setting security.ocsp.timeout. #53218
• Defining or changing authentication principals or their credentials now requires the new CREATELOGIN option to be set for the requesting user or one of its roles. This includes setting/removing the CREATELOGIN option (whether the principal can log in), initializing or changing the password of a SQL user, and setting the expiration date for a password. Previously, only the CREATEROLE option could perform these changes. The pseudo-option NOCREATELOGIN can be used to revoke CREATELOGIN. The two predefined root and admin roles have the option CREATELOGIN set by default. #50601
• Only a user which already has the CREATELOGIN option (either itself or one of its roles) can grant this option or use NOCREATELOGIN. #50601
• Roles that had the CREATEROLE privilege prior to upgrading to this version are also automatically granted CREATELOGIN. After the upgrade, CREATELOGIN is no longer granted automatically. #50601

General changes

• Random() and gen_random_uuid() are now supported as default expressions for IMPORT. #52247
• IMPORT INTO is now supported for DELIMITED and PGCOPY file formats. #52628
• Computed columns are now supported in the IMPORT INTO operation. #51321
• As part of the transition to v20.2, the migration to upgrade schema change jobs started prior to v20.1 is no longer run. #52968
• In some cases where AmbiguousResultErrors were produced under high load, non-ambiguous, retryable errors are now returned. #53156

Enterprise edition changes

• SHOW BACKUP can be used to list backups in a backup collection created by BACKUP ... INTO. #52758
• BACKUPs with revision_history now support including UDTs. #53160
• User-defined schemas now support being backed up with revision history and restored with AS OF SYSTEM TIME. #53241
• Scheduled jobs are now included in cluster backups. #53203
• As part of making basic BACKUP and RESTORE free to use without an enterprise license, SHOW BACKUP and SHOW BACKUPS IN no longer require a license. #53356
• Backups run from v20.2 nodes are no longer run in an auto-appended backup location that was created by v20.1 nodes. #53762

SQL language changes

• Reduced memory used by scans of tables containing JSON data. #52738
• Introduced the box2d data type to be used for geospatial comparators with GEOMETRY/GEOGRAPHY. #52771
• EXPLAIN now shows estimated row count for scans (when statistics are available). EXPLAIN (VERBOSE) now shows the estimated row count for all operators. #52798
• Added support for ALTER TABLE <table> OWNER TO <owner>. The command changes the owner of a table. To use the conditions, the following conditions must be met: The user executing the command must be the owner of the table, or the member of the owner role. The user executing the command must be a member of the new owner role. The new owner role must have CREATE on the schema the table belongs to. #52659
• Added support for SELECT ... FOR {UPDATE,SHARE} NOWAIT. The option can be used to throw an error instead of blocking on contended row-level lock acquisition. #52522
• Added privileges to user-defined types. Users can grant privileges on a type by using GRANT {USAGE/GRANT/ALL} ON TYPE <type> TO <users>. Users must have CREATE privilege in a database to create a type in that database. Users must have USAGE privilege to create an object that depends on a type. Users must have GRANT privilege to grant more privileges on the type. Owning a type implicitly gives ALL privileges on the type (USAGE,GRANT). Users must be the owner of the type to drop or alter the type. After creating a type, the creator can delegate privileges on that type by GRANTing any of the USAGE/GRANT/ALL privileges. To allow another user to grant privileges, they must have GRANT privilege and the privilege they want to GRANT. #51622
• EXPLAIN now shows fewer "project" nodes. #52865
• Improved display of spans in EXPLAIN. #52865
• Added support for the DROP SCHEMA command. #52726
• Added a new cluster setting sql.log.slow_query.internal which, when turned on in conjunction with the slow query log, causes slow internal queries to be logged to a slow internal query log at cockroach-sql-slow-internal-only.log. Internal queries are no longer logged to the slow query log. This new setting is opt-in. The default behavior is to not log slow internal queries. #52377
• Removed some unnecessary EXPLAIN fields, and relegated others to the VERBOSE variant. #53003
• Improved EXPLAIN output for join nodes. #53003
• Improved EXPLAIN output for scalar groups. #53003
• Improved EXPLAIN output for set operations. #53003
• Box2d comparison operators are now gated by the cluster setting sql.spatial.experimental_box2d_comparison_operators.enabled. #52990
• Added the CONTROLJOB role option. When creating/altering roles, CONTROLJOB can be passed as a role option. For example, CREATE USER <user> CONTROLJOB CREATEROLE. NOCONTROLJOB can be used to remove CONTROLJOB from the role when altering the role. CONTROLJOB allows the user to pause/resume and cancel jobs owned by non-admin users. CONTROLJOB also allows seeing all jobs owned by non-admin users, i.e., when running SHOW JOBS. #52804
• Added a new public cluster setting sql.defaults.disallow_full_table_scans.enabled that defaults to false, which informs the session setting disallow_full_table_scans. If the session is configured to disallow full table/index scans, any user query that plans a full table/index scan is rejected. This does not apply to internal queries or queries over virtual schemas (e.g., pg_catalog). #52278
• Renaming and dropping databases now satisfy the same transactional guarantees as online schema changes on tables: Changes will become visible if and only if the transaction commits, and a successful result indicates changes having propagated to the entire cluster. This fixes some long-standing problems with inconsistent database state being visible after renames and drops. (As a side effect of coherent caching for databases, database names can no longer be recycled in multiple schema changes in the same transaction.) #52975
• The vectorized execution engine now supports DISTINCT and FILTERing hash aggregation. #50721
• Added support for ALTER DATABASE OWNER TO. This command changes the owner of a database. The user must be an owner of the database to run the command. The user must also be a member of the new owner role directly or indirectly. #52736
• UPSERT statements now acquire locks using the FOR UPDATE locking mode during their initial row scan, which improves performance for contended workloads. This behavior is configurable using the enable_implicit_select_for_update session variable and the sql.defaults.implicit_select_for_update.enabled cluster setting. #53132
• Users can now convert existing databases into schemas under other databases through the ALTER DATABASE ... CONVERT TO SCHEMA UNDER PARENT ... command. This command can only be run by admin and is only valid for databases that do not already have any child schemas other than public. #52997
• Implemented the IdleInTransactionSessionTimeout variable to allow terminating sessions that are idle in a transaction past the provided threshold. Set the variable by using SET idle_in_transaction_session_timeout = 'time'. Sessions that are idle in OPEN, ABORTED, and DONE(COMMITWAIT) transaction states will be terminated if the user idles longer than the threshold time. #52938
• Non-admin users with CREATEROLE are no longer permitted to drop users with the admin role. #52881
• Users can now specify which subdirectory they wish to write a BACKUP to, via BACKUP INTO 'subdir' IN x. This will write BACKUP data to x/subdir/. #53139
• Added support for ALTER SCHEMA <schema> OWNER TO <owner>. The command changes the owner of a schema. To use the conditions, the following conditions must be met: The user executing the command must be the owner of the schema. The user executing the command must be a member of the new owner role. The new owner role must have CREATE on the database the schema belongs to. #52781
• The vectorized execution engine now supports ordered aggregation with the DISTINCT clause. #53145
• Introduced a new CONTROLCHANGEFEED role option. This grants non-admin roles the ability to create new changefeeds, as long as they have SELECT privileges on the target table. It can be conferred via ALTER ROLE <role> CONTROLCHANGEFEED and revoked via ALTER ROLE <role> NOCONTROLCHANGEFEED. #52869
• Added support for ALTER TYPE <type> OWNER TO <owner>. The command changes the owner of a type. To use the conditions, the following conditions must be met: The user executing the command must be the owner of the type. The user executing the command must be a member of the new owner role. The new owner role must have CREATE on the schema the type belongs to. #52656
• Introduced a new CREATEDB role option. This grants non-admin roles the ability to create new databases. It can be conferred via ALTER ROLE <role> CREATEDB and revoked via ALTER ROLE <role> NOCREATEDB. #52831
• Schema owners can drop tables inside the schema without explicit DROP privilege on the table. #52740
• Queries logged in the slow query log come with a reason now, which can be any combination of LATENCY_THRESHOLD, FULL_TABLE_SCAN, and FULL_SECONDARY_INDEX_SCAN. There is also a new opt-in cluster setting sql.log.slow_query.experimental_full_table_scans.enabled which, when enabled, logs full table/index scans regardless of the query execution being over the latency threshold. This setting only works if the slow query log has been turned on, i.e., a non-zero latency threshold must be specified. #53164
• Introduced a new VIEWACTIVITY role option. This grants non-admin roles the ability to see other users' sessions and queries through SHOW SESSIONS, SHOW QUERIES, and the DB Console Statements page. #53291
• Introduced a new CANCELQUERY role option. This grants non-admin roles the ability to cancel other users' queries and sessions. Note that non-admins are not allowed to cancel the queries or sessions of admins. #53291
• Previously, selectivity information would be included in EXPLAIN ANALYZE diagrams if a query was executed via the vectorized execution engine. This has been removed due to being confusing and probably unhelpful. #53153
• stall time has been renamed to IO time in EXPLAIN ANALYZE diagrams for queries executed via the vectorized execution engine. #53153
• An INSERT ... ON CONFLICT DO UPDATE statement without a list of column names after ON CONFLICT now results in a SQL syntax error with the error code 42601. Previously, it errored with the message "there is no unique or exclusion constraint matching the ON CONFLICT specification" and the error code 42P10. #53067
• Added support for the WITH DATA and WITH NO DATA arguments for the REFRESH MATERIALIZED VIEW command. #53052
• Added support for the GRANT ... ON SCHEMA command. #53344
• Added the SHOW TYPES command to list all user-defined types. #53386
• Added support for the USAGE privilege on schemas. #53358
• Previously, UNION ALL queries could crash the server due to OOM in some extreme cases. This is now fixed, at the expense of possible minor reduction in performance, by reducing the concurrency of evaluation of such queries. #53343
• CockroachDB now recognizes the NOT DEFERRABLE and DEFERRABLE arguments to SET TRANSACTION. Note that the DEFERRABLE argument is still unimplemented. #53435
• Added an invisible cluster setting sql.conn.max_read_buffer_message_size, which allows users to configure SQL statement maximum sizes across the cluster. This effect will take place upon connection restart. #53445
• SHOW BACKUP now shows all schemas in a backup. Previously, databases, types, and schemas were not shown. Note: This introduces a breaking change in that it changes the schema of SHOW BACKUP and will no longer return the column table_name. This column is replaced by the more general object_name. #53321
• The memory used by disk scans is now accounted for, reducing the likelihood of out-of-memory conditions resulting in process crashes (as opposed to SQL out-of-memory error messages). #52496
• Populated the catalog table pg_catalog.pg_matviews with materialized view information. #53501
• A value of type OID can now be compared to a value of type INT. #53523
• Fixed a bug where temporary tables could be included in BACKUP commands. #53478
• Added 3 new columns to the crdb_internal.node_transactions and cluster_transactions tables: num_stmts, num_retries, and num_auto_retries. These keep track of the total number of statements executed on the transaction, the number of retries encountered, and the number of automatic retries encountered, respectively. #51902
• Added the SHOW TRANSACTIONS statement, similar to SHOW SESSIONS and variants. It shows the currently active transactions in the node or cluster, and some information about them. #51902
• The related owner columns are now populated in pg_catalog metadata tables. #53495
• ENUM types are no longer experimental. #53565
• Added the tableoid system column. #53562
• User-defined schemas are no longer experimental. #53674
• The EXPLAIN output for UPSERT and INSERT ON CONFLICT statements now includes a list of arbiter indexes. These arbiters are the indexes used for detecting conflicts between the insert row and the existing rows in the table. #53172
• Added support for the CREATE SCHEMA ... AUTHORIZATION command. #53583
• EXPLAIN ANALYZE diagrams now contain "bytes read" information for table readers and lookup joins when the queries are executed via the vectorized execution engine. #53371
• Disallowed the CONVERT TO SCHEMA command on the current database. #53564
• The TransactionStatistics protobuf will only include the first 1000 statement IDs that comprise a transaction. Any statement IDs beyond 1000 will be omitted. #53553
• Non-admin users are now permitted to execute RESTORE statements as long as the restore does not depend on implicit credentials and the user has the appropriate privileges to create all of the resulting database objects. For database restores, this means the user must have the CREATEDB role privilege. For table restores, the user must have CREATE privileges on the parent database. Full cluster restores still require admin privileges. #53650
• Populated the information_schema.column_udt_usage catalog table. #53699
• Previously, the pg_get_constraintdef function would return a result that included type annotations, which is a CockroachDB-specific syntax. Now it does not. #53865
• EXPLAIN ANALYZE diagrams now contain the information about the number of rows read by the vectorized table reader. #53775
• The new prefer_lookup_joins_for_fk session setting (and corresponding cluster setting) can be used to make foreign key checks use lookup joins if they incorrectly use hash or merge join. #53838
• Previously, there was no way to query the transaction-level metrics collected by individual nodes. A new crdb_internal table called node_transaction_statistics allows users to query transaction metrics collected on a particular node. #53759
• Introduced a new MODIFYCLUSTERSETTING role option. This grants non-admin roles the ability to modify certain cluster settings, currently limited to settings with the "sql.defaults" prefix. #53930
• SQL expressions that are shown in pg_catalog columns and related functions now will be formatted with a typecast for non-numeric constants. #53965
• Implemented geometry built-in ST_Reverse #52834
• Implemented the && and ~ operators for comparing bounding box objects and geometries. #52913
• Implemented the ST_CombineBBox built-in. #52903
• Introduced the s2_max_level, s2_level_mod, and s2_max_cells storage parameters for modifying the S2 parameters for indexing GEOMETRY and GEOGRAPHY data types in a GIN index. #52800
• Introduced the geometry_min_x, geometry_min_y, geometry_max_x, geometry_max_y storage parameters for indexing GEOMETRY data types in a GIN index. #52800
• Implemented the ALTER MATERIALIZED VIEW and DROP MATERIALIZED VIEW commands. #52840
• Implemented the ability to cast between box2d and GEOMETRY types. #52965
• Implemented the PostGIS_GetBBox built-in. #52963
• Implemented the ST_MakeBox2D built-in. #52961
• Implemented the ST_ClipByBox2D built-in. #52955
• Added the ability to resolve the spatial-backed built-ins in the public schema. For example, public.st_x works the same as st_x. #52983
• Implemented ST_Expand for the box2d type. #52957
• Implemented ST_PointFromGeoHash, which converts geohash to point. #52892
• Implemented the ST_Extent built-in for GEOMETRY aggregations into box2d. #53001
• Implemented the geometry built-in ST_Dimension. #53068
• Implemented the geometry built-in ST_CoordDim as an alias for ST_NDims. #53193
• Implemented the geometry built-ins ST_IsEmpty and ST_IsCollection. #53217
• Implemented the ST_GeomFromGeoHash and ST_Box2DFromGeoHash built-ins. #53162
• Implemented the ST_Union built-in as an aggregate. The previous alpha-available ST_Union for two arguments is deprecated. #53127
• Implemented ST_Expand for GEOMETRY-based types. #53326
• Implemented the geometry built-ins ST_Multi, ST_CollectionExtract, and ST_CollectionHomogenize. #53287
• Implemented the geometry built-in ST_SharedPaths #53307
• Implemented the geometry built-in ST_FlipCoordinates #53296
• Implemented the geometry built-in ST_SymDifference. #53636
• Implemented the geometry aggregate built-in ST_Collect. #53645
• Implemented the geometry built-in ST_ForceCollection. #53643
• Implemented the geometry built-in ST_SymmetricDifference. #53688
• Implemented the geometry aggregate built-ins ST_MemCollect and ST_MemUnion. #53708
• Implemented the geometry built-in ST_Normalize. #53726
• Implemented the geometry built-in ST_Rotate. #53709
• Implemented the geometry built-in ST_AddPoint. #53853
• Implemented the geometry built-in ST_Simplify and ST_SimplifyPreserveTopology. #53796
• Implemented the geometry built-in ST_Affine. #53856
• Implemented the geometry built-ins ST_LineFromMultiPoint and ST_LineMerge. #54013
• Implemented the geometry built-in ST_RemoveRepeatedPoints. #53999
• Implemented geometry built-in ST_RemovePoint #53244
• Implemented the ST_IsClosed, ST_IsSimple, and ST_IsRing built-ins. #53240
• Implemented the geometry built-in ST_Points. #53496
• Implemented the ST_IsPolygonCW, ST_IsPolygonCCW, ST_ForcePolygonCW, and ST_ForcePolygonCCW built-ins. #53243

Command-line changes

• The --storage-engine CLI flag no longer defaults to using the last-used storage engine when unspecified. Instead, Pebble is used by default unless otherwise specified using the --storage-engine flag. #52944
• Added alias commands ls and rm for userfile list and userfile delete. #53074
• The new debug command decode-proto reads descriptor from stdin in hex or Base64 format (auto-detected) and a flag --schema=<fully qualified name to decode> with default value cockroach.sql.sqlbase.Descriptor and outputs to stdout the deserialized protobuf in JSON format. If the input is not a hex/Base64-encoded protobuf, then it is outputted verbatim. #52972
• The cockroach start and cockroach start-single-node commands now enable --redactable-logs by default. The flag is also enabled by default in cockroach demo if --log-dir is passed. This causes log files to become redactable, so that cockroach debug merge-log --redact or cockroach debug zip --redact can remove sensitive information from log files. (Reminder: cockroach debug zip --redact only affects log files; other items collected by the command can still contain sensitive information.) #53263
• The command-line flag --insecure has been marked as deprecated. See issue #53404 for details. The flag will be removed in a later version in a staged fashion: first, additional security mechanisms will be added to enable more flexible deployments which were previously done using --insecure; then the flag will be removed from server commands; then finally, in a later version, also from client commands. #53405
• CockroachDB now recognizes the \dT alias for listing user-defined types. #53386
• The workload sub-commands are no longer marked as experimental. #53691
• The --geo-libs flag introduced in v20.2 alpha versions is now renamed to --spatial-libs. #53721
• The CLI no longer prints a blanket Time for queries. Instead, if show_times is turned on and the server version is v20.2 or later, the CLI prints two separate times: the server execution time and the network latency. #52233

DB Console changes

• Improved how SQL statement plans in the DB Console are populated, using the new EXPLAIN infrastructure. #52956
• Previously, in some cases, the Execution Stats page would show a confusingly high Overhead latency for a statement. This could happen due to multiple statements being parsed together or due to statement execution being retried. To avoid this, we now stop considering the time between when parsing ends and execution begins when determining service latency. #53846
• Added the Session list and Session detail pages. We now permit session and query termination from the UI. #51903

Bug fixes

• Fixed a bug for ALTER TABLE statements with multiple actions. In certain cases if the last action had no effect, the entire statement would be treated as a no-op. #52819
• Previously, unexpected context cancellation errors could sometimes be returned in the vectorized execution engine. This is now fixed. #52463
• Fixed a bug where tables would not be cleaned up after a failed creation through CREATE TABLE AS. #52832
• Fixed a bug whereby gc jobs for tables dropped as part of a DROP DATABASE CASCADE might never complete. #52818
• Fixed an internal error that could occur when using an index-accelerated geospatial function inside an EXISTS or NOT EXISTS correlated subquery. #52942
• Fixed a bug where if a table with a check constraint was truncated while being backfilled, the check constraint could get lost. #49399
• Fixed bug where non-committed DETACHED BACKUPS left files which falsely indicated that a BACKUP was in progress. #52980
• Fixed a crash that could occur when referencing a database that did not exist when trying to create a type or sequence, or when renaming a table. #53137
• Eliminated some rare AmbiguousCommitErrors happening when CDC was used. #53146
• A change in v20.1 caused a certain class of bulk UPDATE and DELETE statements to hang indefinitely if run in an implicit transaction. We now break up these statements to avoid starvation and prevent them from hanging indefinitely. #52885
• Previously, cluster restores would appear in the jobs table and DB Console Jobs page as RESTORE TABLE FROM ..., which was incorrect. They now appear as RESTORE FROM .... #53230
• Previously, CockroachDB could return incorrect results when performing LEFT ANTI hash joins when right equality columns would form a key when using the vectorized execution engine. This has been fixed. #53226
• Admin users are now permitted to cancel other users' queries and sessions. Previously only the root user was allowed to do so. #53291
• Fixed a possible server panic when using the nodelocal and userfile subcommands. #53359
• Fixed a bug where user-defined types could not be used with some ORMs due to an assertion failure within Cockroach. #53385
• The DB Console Node Map (enterprise feature) and the Advanced Debug page to list cluster localities are now again available to non-admin users. This bug had been introduced in v19.2.3. #53329
• Previously, if no table was referencing a schema, it would not be included. Now when backing up a database, all schemas will be included. #53224
• Fixed an internal error related to casts between tuples. #53682
• Virtual index scans now respect limits properly, as they did in v20.1 and prior. #53529
• CockroachDB could previously crash when evaluating queries with window functions with GROUPS mode of framing when OFFSET FOLLOWING boundary was used and when the offset was a very large value such that it could result in an integer overflow. This is now fixed. #53722
• Database creation/deletion was previously not correctly tracked by revision_history cluster backups. This is now fixed. #53667
• Fixed a bug that caused a crash when using a RANGE mode window function with an offset in some cases, e.g., OVER (PARTITION BY b ORDER BY a RANGE 1 PRECEDING). #53717
• Fixed a panic in ST_SetPoint when given empty point. #53857
• Fixed a rare internal error that could occur during planning of queries with many highly selective predicates. #53802
• CockroachDB now properly checks that a user has CREATE privileges on the target database before allowing them to create a schema. #53837
• CockroachDB could previously crash when performing an interleaved join in some cases. This is now fixed. The bug has only been present on v20.2 testing releases. #53863
• Fixed "use of crdb_internal_vtable_pk column not allowed" for some queries involving virtual tables. #53866
• Fixed a bug when running restore while upgrading the cluster from v20.1 to v20.2. #53848
• Fixed a bug where we allowed new types to be used in an array type during a version upgrade. #53961
• Fixed a rare bug where the optimizer incorrectly classified some columns as not-null, possibly leading to invalid query plans and incorrect results. #54024

Performance improvements

• Maximum and minimum values, represented as 2-bucket histograms, are now collected for all non-index columns (up to 100 columns per table) as part of automatic statistics collection. 200-bucket histograms are still collected for all index columns. This change enables the optimizer to make better cardinality estimates and may result in better query plans in some cases. #52905
• A large heap allocation performed during INSERT statements was removed, resulting in an increase to throughput for single-row INSERT statements. #53076
• Transient node liveness blips no longer cause up-to-date ranges to unquiesce, which makes these events less destabilizing. #51894
• The ~ and && geospatial bounding box operations can now benefit from index acceleration if one of the operands is an indexed geometry column. #53023
• Cleaning up after a failure during IMPORT INTO a table which was empty is now faster. #52754
• The allocation algorithm now supports intra-node rebalances, which means CockroachDB can fully utilize the additional stores on the same node. This removes the last roadblock to running CockroachDB with multiple stores (i.e., disks) per node. #51567
• Validation of optimistic reads is now performed earlier in transactions when doing so can save work. This eliminates certain types of transaction retry errors and avoids wasted RPC traffic. #52884
• Fixes a performance regression introduced in v20.2 alpha releases where the use of 2-part names to specify tables or types would incur extra reads from the store on every transaction. #53290
• The optimizer can now convert semi joins to inner joins in more cases, which may allow the optimizer to produce more efficient query plans. #53337
• The optimizer is now aware that the inverted join operator can preserve the ordering of its input. This enables the optimizer to eliminate unnecessary sort operations and produce more efficient plans in some cases. #53502
• The optimizer reduces filters applied after partial index scans in more cases where the filters are implicitly applied by the partial index predicate. This could lead to more efficient query plans in some cases. #53507
• Limited the frequency of an expensive operation due to schema changes. This makes workloads that perform schema changes at a high rate less resource-intensive. #53605
• Joins between very small tables are now more likely to use lookup join. #53849

Doc updates

• Published a tutorial on orchestrating a secure CockroachDB multi-region deployment on Amazon EKS. #7782
• Published best practices for optimizing import performance in CockroachDB. #8035

v20.2.0-alpha.3

Release Date: August 25, 2020

Backward-incompatible changes

• A CockroachDB node started with cockroach start without the --join flag no longer automatically initializes the cluster. The cockroach init command is now mandatory. The auto-initialization behavior had been deprecated in version 19.2. #51245
• Clusters running alphas of 20.2 that use ENUM types will not be able to upgrade to betas or major releases of 20.2 due to internal representation changes. #52483
• Improved WITH option parsing for RESTORE. Does not allow the same option to be specified twice, and also prevents usage of quoted option names. #52302
• The file names for heap profile dumps now use the naming scheme memprof.<date-and-time>.<heapsize>, where previously they were named memprof.<heapsize>.<date-and-time>. #50446

General changes

• cockroach node recommission has new semantics. Previously it was able to recommission any decommissioning node, regardless of how long ago its was decommissioned, or removed from the cluster. Now cockroach node recommission serves to only cancel an accidental inflight decommissioning process that wasn't finalized. #50329
• CPU profiles are now taken as part of the cockroach debug zip command. #50625
• CPU profiles taken from CockroachDB servers now contain more profiler labels related to ongoing SQL processing. #50625
• The CockroachDB tarballs now include the libgeos and libgeos_c libraries, which are required to be copied to /usr/local/lib/cockroach for UNIX/MacOS systems to perform certain geospatial operations. The Windows location will be finalized at a later date. This location is configurable with the --geo-libs flag to cockroach start. #51108
• The Docker container that ships with CockroachDB now includes the GEOS library needed for geospatial functionality in /usr/local/lib/cockroach (which is the default location where the cockroach binary looks for the GEOS libraries). #51392
• Timestamp functions are now supported by IMPORT INTO. #51390
• IMPORT INTO now supports unique_rowid() as a default expression. #50922
• Previously, CockroachDB would spam its logs when undergoing network connectivity issues. This patch reduces the frequency of said spam. #51937
• Reverted the Go version back to 1.13. #52348

Enterprise edition changes

• RESTORE has a new option skip_missing_sequence_owners that must be supplied when restoring only the table/sequence that was previously a sequence owner/owned by a table. Additionally, this fixes a bug where ownership relationships would not be remapped after a RESTORE. #50949
• Disabled the use of IMPORT with user defined types. Users should use IMPORT INTO instead. #51149
• Added support for BACKUP TENANT and RESTORE TENANT. #50967
• Implemented a CREATE SCHEDULE FOR BACKUP statement which allows the creation of periodic backup schedules. #51308
• Implemented schedule control statements to pause, resume, or delete scheduled jobs. #51896
• Added support for user-defined schemas in the IMPORT and IMPORT INTO statements. #51936
• Added a FOR SCHEDULES clause to the job control statements to enable management of the jobs created by schedules. #52038
• The SHOW SCHEDULES statement now displays information about the scheduled jobs. #52230
• Added the ability to display jobs started by a specified schedule. #52274
• Added a BACKUP ... INTO alternative to BACKUP TO which automatically picks a new path within the destination. #52329
• The BACKUP statement now takes priority over other transactions if its initial attempts to export a range fail. #51624
• Exported CSV files are now prepended with a long unique ID. This can help to mitigate situations where multiple export runs are written to the same directory, resulting in mixed data. This change does not prevent mixed data; rather, it makes it possible to identify files from distinct runs, so that an operator can clean up. #52547
• Updated IMPORT so that it cannot be used to create tables in user-defined schemas. Users should instead create the table with CREATE TABLE and then use IMPORT INTO. #52729
• The RESTORE statement can now run in "detached" mode. That is, instead of waiting for the RESTORE job to finish, the job ID is returned immediately, and the job itself runs in the background. #52779
• Enabled BACKUP and RESTORE when user-defined schemas are in use. #52588

SQL language changes

General SQL updates

• Added support for materialized views. #52530
• Indexes are no longer required on the origin side of a foreign key relationship, and are no longer automatically created. #50771
• Added a CREATE SCHEDULE FOR BACKUP statement. This statement can be used to specify a recurring backup schedule. #50631
• Removed the SYMVAR modifier to EXPLAIN (PLAN). #51349
• The vectorized engine now supports the aggregate function concat_agg. #51148
• Disallowed row-level locking in READ-ONLY transactions to be consistent with PostgreSQL behavior. #51335
• Implemented the datetime_precision column in the information_schema.columns table for time-stored values. #51352
• The sql.log.slow_query.latency_threshold cluster setting now requires an explicit unit when being set. (e.g., 500ms for 500 milliseconds, 5us for 5 nanoseconds, 5s for 5 seconds, etc.). An error with a hint pops up if the user fails to provide units. #50632
• Added a deprecation notice for the use of ALTER TABLE .. RENAME with a qualified name. #51433
• Disabled using ALTER TABLE .. RENAME to change the schema of a table. #51433
• Unqualified names in the target of ALTER TABLE ... RENAME are now treated as having the same database and schema as the table being renamed. #51433
• Interleaved table joins now show up in EXPLAIN (PLAN) output. #51469
• The pg_catalog.pg_attribute table now contains the columns attidentity (unimplemented) and attgenerated (implemented, 's' for computed/generated columns). #51466
• Made a minor change to remove the outdated CCL-only message from the GRANT and REVOKE ROLE help messages. #51465
• Added the num_nulls and num_nonnulls built-in functions, which count the number of arguments that are passed to them which are NULL or non-NULL, respectively. #51515
• EXPLAIN (PLAN) now shows any filter on a scan as a separate filter node. #51493
• Added the stddev_pop and var_pop aggregate functions for population standard deviation and variance calculations. #51447
• Added support for the idle_in_session_timeout variable to allow automatically terminating sessions that idle past a certain threshold. #51223
• Added the cluster setting sql.defaults.idle_in_session_timeout for setting a default idle_in_session_timeout value for new sessions. This gives users the ability to set idle_in_session_timeout values for all newly created sessions. #51223
• Added support for renaming a value of an ENUM using the ALTER TYPE <type> RENAME VALUE <old> TO <new> syntax. #51490
• Exposed the MVCC timestamp of each row as a system column on the table. This column is named crdb_internal_mvcc_timestamp and is accessible only in a limited set of contexts. #51494
• Transactions that have modified or created a type will execute queries on the local node, rather than distributing the queries to other nodes in the cluster. #51784
• Casting TIMESTAMP types to TEXT-related types now omits the timezone component. For example, '2001-12-15 15:14:13'::TIMESTAMP will now format as '2001-12-15 15:14:13' instead of '2001-12-15 15:14:13+00:00'. #51692
• Added the built-in function crdb_internal.approximate_timestamp to convert the DECIMAL returned from the crdb_internal_mvcc_timestamp system column into a TIMESTAMP. #51662
• EXPLAIN no longer shows the "hidden" annotation for columns. #51837
• Previously, matching with LIKE supported the "prefix" notation match(test%) and "suffix" notation match(%test) This commit adds the "contains" notation match(%test%). #51636
• Dropping a column always drops all indexes that index the column. It is no longer necessary to provide the CASCADE option to DROP COLUMN. #51661
• The SHOW CREATE statement now contains the schema of a table in the CREATE statement. #50889
• The EXPORT statement no longer requires an enterprise license. #52029
• EXPLAIN (DISTSQL) diagrams have been updated by switching table name with index name (previously, we had the syntax index@table, and now we have table@index). #52085
• The 'bytes' and 'rows read' metrics are now tracked with mean and variance, similar to the other per-statement metrics. crdb_internal.node_statement_statistics has been updated to remove the bytes_read and rows_read columns, replacing them with bytes_read_avg, bytes_read_var, rows_read_avg, and rows_read_var. #51630
• Added cosmetic improvements to EXPLAIN node naming. #52195
• Added support for ALTER TABLE/SEQUENCE/VIEW SET SCHEMA to set the schema of the table to the target schema. One must have DROP privileges on the table and CREATE privileges on the schema to perform the operation. #52090
• Added support for the setting and getting of the synchronous_commit and enable_seqscan variables, which do not affect any performance characteristics. These are no-ops enabled to allow certain tools to work. #52168
• EXPLAIN no longer shows grouping columns as "aggregations" for the group operator. #52232
• It's now possible to specify a KMS URI or a list of KMS URIs to encrypt BACKUP data/manifests with by passing the kms option to BACKUP. #52091
• Query statistics now include the disk bytes and rows read from lookup and index joins, in addition to ordinary table scans. #52237
• The value of pg_class.atttypmod and the TypeModifier in the RowDescription for array columns is now the same as the type modifier of the type of the array contents. This enhances compatibility with the PostgreSQL wire protocol. #52428
• Added support for ALTER TABLE ... ALTER COLUMN ... SET NULL syntax for IMPORT PGDUMP type backups. #52442
• Added a hint for "line too long" errors when importing a backup with long lines. #52432
• Added "ownership" concept objects. Objects must now have an owner; all objects that do not have owners currently will have admin set as the default owner except system objects. System objects without owners will have node as their owner. By default, owners are the creator of the object. Owners have all privileges to the objects they own. Similarly, any roles that are members of the owner role also have all privileges on the object. Roles cannot be dropped if they own objects. This PR does not add support for changing the ownership of objects; that will be added in a future PR to support dropping roles. #51856
• When using IMPORT PGDUMP with INSERT INTO clauses, specifying a column name that is case sensitive (e.g., "cApItAls") would previously error specifying the column name was not found. This has been fixed. #52485
• Added the pg_get_serial_sequence built-in function. #52436
• Added support for the ALTER TYPE SET SCHEMA command to set the schema of a user-defined type. The user must have CREATE privileges on the schema and DROP privileges on the type to set the schema. #52301
• Added a new statement SHOW ENUMS which displays information on existing enums. #52538
• Added support for the BINARY format for COPY FROM. #51890
• Previously, IMPORT PGDUMP using COPY required all rows to be present in the same order as the table definition. This restriction has been removed. #52488
• Added a notice that is displayed to the user when she tries to add a value which already exists in an ENUM. #52534
• Modified SHOW TABLES to return estimates of the number of rows in each table. The new column's name is estimated_row_count. The number of rows is taken from the system.table_statistics table (via crdb_internal.table_row_statistics, which shows only tables accessible to the current user). #52203
• Implemented the regexp_split_to_table and regexp_split_to_array built-in functions. #52479
• Added support for the CREATE UNLOGGED TABLE syntax to be parsed and recorded internally by CockroachDB. However, this syntax does not modify the behavior of the table. #52596
• Added support for the use of CREATE INDEX ... WITH ... syntax. #52640
• "no inbound stream connection" errors should happen less frequently due to the addition of connection retries. #52624
• Extended the RESTORE statement to support the 'KMS' option. This can be used to decrypt encrypted BACKUP(s). #52434
• Added support for parsing the syntax CREATE TABLE ... WITH (autovacuum_enabled = bool), which results in a no-op. #52652
• Added various improvements to EXPLAIN. #52730
• Implemented the ALTER SCHEMA RENAME TO command. #52349
• The vectorized execution engine now fully supports comparison operators (things like ILIKE, IS NOT DISTINCT FROM, SIMILAR TO, and several others). #52313
• The experimental_follower_read_timestamp() function has been renamed to follower_read_timestamp(), signifying more confidence in CockroachDB's follower read implementation. The previous name remains a supported alias. #52359
• The reserved, non-documented cluster settings server.heap_profile.* have been renamed to server.mem_profile.*. They now control collection of multiple types of memory profiles besides just Go heap allocations. #50446

Spatial support updates

• Implemented the ST_MakePolygon function for GEOMETRY types. #50979
• Added a variant of each index-backed geospatial function that is prefixed with a _ (e.g., ST_Covers gets a non-index-accelerated variant called _ST_Covers). These prefixed variants avoid using the spatial index while providing the same functionality. #51225
• Implemented the ST_DistanceSphere and ST_DistanceSpheroid operators for GEOMETRY types. #51461
• Implemented the ST_ConvexHull function on GEOMETRY types. #51446
• Implemented the ST_Disjoint built-in function for GEOMETRY types. #51444
• Added support for the USING GIST syntax to create a GIN index on GEOMETRY and GEOGRAPHY columns. #51516
• Implemented the ST_IsValid, ST_IsValidReason and ST_MakeValid operators for GEOMETRY types. #51484
• Implemented the ST_Force2D functionality for GEOMETRY types. #51514
• Updated geospatial functions that take in a string argument such that when an ambiguous function that could be referring to either GEOMETRY or GEOGRAPHY types is encountered, the GEOMETRY type is chosen. This is the case for the following functions: st_area, st_asewkt, st_asgeojson, st_buffer, st_coveredby, st_covers, st_distance, st_dwithin, st_intersects, and st_length. #51563
• Implemented ST_Length2D and ST_Perimeter2D for GEOMETRY types. #51556
• Implemented ST_Envelope for GEOMETRY types. #51604
• Added the ST_RelatePattern built-in function, which checks whether a given DE-9IM intersection matrix matches a given pattern. #51858
• Implemented ST_GeoHash for GEOMETRY and GEOGRAPHY types. #50611
• Implemented the ST_Buffer and ST_Intersection functions for GEOGRAPHY types. #51537
• Implemented the ST_Intersection function for STRING types. #51537
• When ordering by geospatial columns, they will now be ordered by the Hilbert Space-filling Curve index so that points which are geographically similar are clustered together. #51898
• Implemented the GEOMETRY built-in function ST_Translate. #51483
• Implemented the GEOMETRY built-in function ST_Scale. #52350
• Implemented the ST_MakeLine aggregate built-in function. #52631
• Implemented the GEOMETRY built-in function ST_SetPoint. #52655
• Added aliases for GeomFromEWKT to ST_GeomFromEWKT and GeomFromEWKB to ST_GeomFromEWKB. #52789
• Added ST_AsGeoJSON for recordsets, putting row contents into the properties field of a GeoJSON object. #52715

Command-line changes

• Added a set of statement-diag CLI commands that can be used to manage statement diagnostics. #50924
• Introduced a membership column to the output generated by cockroach node status --decommission. It should be used in favor of the is_decommissioning column going forward. #50329
• The v20.2 CLI cockroach node family of subcommands will not work with servers running older versions of cockroach, but the v20.1 CLI cockroach node subcommands will work against v20.2 servers. #50329
• The is_decommissioning column found in the output of cockroach node decommission is slated for removal in v20.1. Operators should instead use the new membership column to determine node membership status. #50329
• The --wait flag to cockroach node decommission now takes any of the following values:
  • all waits until all target nodes' replica counts have dropped to zero and marks the nodes as fully decommissioned. This is the default.
  • none marks the targets as decommissioning, but does not wait for the replica counts to drop to zero before returning. If the replica counts are found to be zero, nodes are marked as fully decommissioned. Use when polling manually from an external system. #50329
• The statement-diag CLI command will now show all times in UTC. #51185
• Added a userfile upload command that can be used to upload a file to the user scoped blob storage: userfile upload source/file /destination/of/file #50981
• SIGQUIT now causes a CockroachDB server to log its stack traces without shutting down. #50774
• Added Kerberos (GSS) support to cockroach sql and other CLI commands that only use the SQL protocol (such as node ls, or node status). Other cockroach CLI commands that also use the RPC protocol still cannot use Kerberos (such as node decommission, debug zip, etc.). #51570
• Improved the user semantics for userfile upload by supporting different patterns of specifying the source and destination CLI arguments. The source argument is required, while the destination argument is now optional. #51353
• Changed the Kerberos URL connection string parameters to the standard krbsrvname (previously service) and krbspn (previously spn). #51947
• Updated the label used for the commit ID in the printed version info. #52263
• cockroach dump now supports dumping databases that contain user-defined schemas, as well as accepting schema-qualified table names as arguments. #52079
• The userfile command now supports an ls command which allows users to list the files they have uploaded to the user-scoped FileTableStorage. userfile ls accepts a single, optional CLI argument which can either be a well-formed userfile URI or a glob pattern. The latter defaults to searching in the default FileTableStorage table defaultdb.public.userfiles_$USERNAME. #51482
• The userfile command now supports a delete command, which allows users to delete the files they have uploaded to the user-scoped FileTableStorage. userfile delete accepts one CLI argument which can either be a well-formed userfile URI or a glob pattern. The latter defaults to searching in the default FileTableStorage table defaultdb.public.userfiles_$USERNAME. #51610
• CockroachDB now better attempts to retain heap profile dumps after a crash due to an out-of-memory (OOM) error. #50446
• CockroachDB now better attempts to retain memory statistics corresponding to increases in total memory usage, not just heap allocations. #50446
• Crashes in cockroach demo sessions are now reported to telemetry, if telemetry is enabled. #52696

DB Console changes

• Removed the Now button for the "From" timepicker in the custom time range on the Metrics page. #51047
• Updated the node label in chart legends to make the node ID visible. #50434
• Updated DB Console database page styles to match the Cockroach Labs design system. #47753
• Updated link on login page for secure clusters to point to a moved documentation URL. #51862
• The Statement Details page now contains statistics for rows and disk bytes read. #51630
• The time range selector (on the Metrics page) now shows time in UTC instead of local time, with a "(UTC)" prefix after the time to highlight this in the UI. #51056

Bug fixes

• Fixed an internal error that occurred when AddGeometryColumn was called with NULL arguments. This now results in a no-op and returns NULL. #50992
• Previously, CockroachDB could crash when internal memory accounting hit a discrepancy. Now it will report an error instead. #50962
• Fixed the "column not in input" internal error in cases involving lookup semi/anti joins. #50993
• Added better support for large statement diagnostic bundles. #50974
• Previously, if there was a table t(a int, b int), and a sequence seq that was first owned by t.a and then altered to be owned by t.b, it would make the table t impossible to drop. This is now fixed. #50720
• The age function previously did not normalize the duration for large day or H:M:S values in the same way PostgreSQL does. This is now fixed. #51054
• Fixed an internal error in some cases involving COALESCE with NULL inputs. #51022
• Fixed a bug where very long-running incremental backups could fail if the data they were backing up was garbage collected. #51069
• Fixed a bug causing the raw trace file collected inside a statement diagnostics bundle to be sometimes empty when the cluster setting sql.trace.txn.enable_threshold was in use. #50914
• Prevented spurious "SimpleQuery not allowed while in extended protocol mode" errors. #51194
• Previously, CockroachDB could hit an internal error when executing the regexp_replace built-in function; this has been fixed. #51303
• Fixed an internal error involving CASE statements and boolean expressions with NULL operands. #51154
• IMPORT no longer fails when run in a database which has a user with privileges on the database. #51315
• cockroach dump no longer fails with an error when dumping temporary tables, views, or sequences. It either ignores them or throws an informative error if the temporary object is explicitly requested to be dumped via the CLI. #51185
• Fixed incorrect results in some cases involving joins on interleaved tables with limits. #51398
• Fixed a bug where cockroach dump would not properly escape quotes within table comments. #51463
• Fixed a bug where cockroach dump would not emit a correct statement for comments on indexes. #51463
• Fixed a bug in the pgwire protocol implementation where CockroachDB would not correctly populate the TableOID and TableAttributeNumber fields in the RowDescription message of a prepared statement correctly. #51479
• Some queries in the vectorized execution engine could previously hang during cleanup. This is now fixed. #51375
• Previously, users could not drop tables or sequences with invalid internal state due to circular ownership issues. This is now fixed. #51253
• Updated the vectorized engine to show the same statistics for 'rows'/'bytes read' as the row-at-a-time engine. #51591
• Previously, CockroachDB could throw a "command is too large" error when performing UPSERT operations with many values. Internally, we attempted to perform such operations by splitting them into "batches", but the batching mechanism was broken. This is now fixed. #51608
• It is no longer possible for rapid range lease movement to trigger a rare assertion failure under contended workloads. The assertion contained the text: "discovered lock by different transaction than existing lock". #51615
• CockroachDB now tracks the location of follower replicas for all ranges much more effectively than before. This means that more queries will be successfully served as "follower reads". #51437
• Before this change, we would leave orphaned system.namespace/system.descriptor entries if we ran a DROP DATABASE CASCADE and the database contained "dependency" relations. For example, if the database included a view which depended on a table in the database, dropping the database would result in an orphaned entry for the view. Same thing for a sequence that was used by a table in the database. (See #51782 for reproduction steps). This bug is now fixed, and cleanup of entries happens as expected. #51813
• Fixed instances of slow plans for prepared queries involving CTEs or foreign key checks. #51788
• Fixed the "unhandled op: json-object-agg" internal error. #51881
• CockroachDB would previously emit benign internal "context canceled" errors when queries were executed by the vectorized engine. This is now fixed. #51772
• Added better detection of invalid computed column or partial index predicates that contain context-dependent operators. #51886
• Increased robustness of RESTORE against descriptors which may be in an unexpected state. #51848
• Previously, CockroachDB could return incorrect results on query that encountered a ReadWithinUncertaintyInterval error, and this has been fixed. #51518
• Previously, a BACKUP job would block once it had finished backing up the data. This is now fixed. #51999
• Previously, RESTORE would sometimes block at the end of the job when sending its results back if the connection that started the job disconnected. This is now fixed. #51999
• COPY previously did not allow a backslash of any character other than the special table set. CockroachDB would emit an error in these cases. This behavior is changed to allow any character after a backslash and interpret it to mean the character itself to match the behavior of PostgreSQL (e.g., now \a will be interpreted as a). Furthermore, non hex-digits following a \x are now interpreted without the backslash, (e.g., \xH will be interpreted as xH). Strings ending with a single backslash will use the backslash (e.g., x\\ is interpreted as x\). #52068
• Fixed an internal error that could occur when an aggregate function argument contained a correlated subquery with another aggregate function referencing the outer scope. This now returns an appropriate user-friendly error: "aggregate function calls cannot be nested". #52092
• CockroachDB could previously crash on some queries with merge joins; this has now been fixed. #52042
• Resolved an internal error that occurred with 0-argument built-in functions in the vectorized engine. #52103
• Previously, subtracting months from a TIMESTAMP/DATE/TIMESTAMPTZ whose date value was greater than 28 could subtract a further year off. This is now fixed. #52138
• Fixed an issue where large write requests no longer have a chance of erroneously throwing a "transaction with sequence has a different value" error. #52234
• Previously, shutting down a connection with an open transaction that had active temporary tables caused deadlocks or general brokenness. This was because the table cleanup was done inside of the open transaction. This commit moves the table cleanup to be after the user transaction is shut down. #52148
• Fixed a rare bug that could cause actionable closed timestamps to effectively regress over a given keyspan. This could in turn lead to a serializability violation when using follower reads. This was due to ill-defined interactions between range merges and the closed timestamp subsystem. #50265
• Updated the functions crdb_version() and version() to return the same result. #52032
• Fixed a bug with the temporary object cleaner where it was stuck trying to remove objects that it mistakenly thought were temporary. Note that no persistent data was deleted -- the temporary cleaner would throw an error out because it thought certain persistent data was temporary. #51359
• Fixed a bug when using the COPY protocol which could prevent schema changes for up to 5 minutes. #52384
• Previously, CockroachDB would erroneously restart the execution of empty, unclosed portals after they had been fully exhausted. This has been fixed. #48842
• Updated IMPORT PGDUMP and IMPORT MYSQLDUMP to reject specifying multiple files. #52500
• Fixed an internal error involving string literals used as arrays. #52416
• Fixed a bug where jobs could fail to run during version upgrades. #52475
• Don't display a value for gc.ttlseconds in the DB Console if it is not set. #52721
• Updated CockroachDB's handling of an unknown condition that would previously crash a node with the message "committed txn with writeTooOld err". This condition no longer crashes a node; instead, an error message is printed to the logs asking for help in the investigation. #51619

Performance improvements

• SELECT IN performance on longer filter rows was improved in the vectorized engine by replacing a linear scan with binary search. #50337
• Improved the efficiency of garbage collection when there are a large number of versions of a single key, commonly found when utilizing sequences. #51184
• Scans over the virtual table pg_type by the OID column have improved performance in common cases. #51374
• Queries no longer block during planning if cached table statistics have become stale and the new statistics have not yet been loaded. Instead, the stale statistics are used for planning until the new statistics have been loaded. This improves performance because it prevents latency spikes that may occur if there is a delay in loading the new statistics. #51616
• Modified the cardinality estimate for index-accelerated geospatial functions to make it more likely for the optimizer to choose a geospatial GIN index join during query planning. This results in more efficient plans in most cases. #51963
• Added support for performing joins using geospatial GIN indexes in a wider variety of cases. Complex boolean predicates combining multiple index-accelerated geospatial functions are now supported as join conditions. #52376
• Previously, histogram statistics were only collected for the first column of each index. Now they are collected for each indexed column, allowing the optimizer to more accurately estimate costs of query plans. #52448
• Unnecessary mutex contention observed in heavy read workloads has been removed. #51055
• Ranges recover moderately faster when their leaseholder is briefly down before becoming live again. #51888

v20.2.0-alpha.2

Release Date: July 27, 2020

Backward-incompatible changes

• The SHOW RANGE FOR ROW statement now takes a tuple of the row's index columns instead of the full column set of the row. #50479
• Previously, issuing a SIGTERM signal twice or after another signal initiated a hard shutdown for a node. Now the first SIGTERM signal initiates a graceful shutdown and further occurrences of SIGTERM are ignored. To initiate a hard shutdown, issue SIGINT two times (or issue a SIGINT signal once after a SIGTERM signal). #50539
• Specifying the same option multiple times in the WITH clause of the BACKUP statement now results in an error message. Additionally, quoted option names are not allowed anymore. #50723
• For expression typing involving only operations on constant literals, each constant literal is now assigned a type before calculation. Previously, a type was assigned only to the result. #50254

Security updates

• HTTP endpoints beginning with /debug/ now require a valid admin login session. #50483

General changes

• Go 1.14 is now the minimum required version necessary to build CockroachDB. #50671
• The docker image available with CockroachDB now includes the GEOS files required to work with Geospatial types in CockroachDB. #50362
• Schema changes are now logged in greater detail. #50372
• Added new options to YCSB to mirror OpenSource YCSB options. #50546
• Links returned in error messages that point to unimplemented issues now use the CockroachLabs redirect/short-link server. #49836

Enterprise edition changes

• The default flush interval for changefeeds that do not specify a RESOLVED option is now 5s instead of 200ms to more gracefully handle higher-latency sinks. #49770
• Full cluster restore is now more resilient to transient transaction retry errors while it restores. #49950
• RESTORE now remaps user-defined types within the backup to existing types in the cluster. #50349
• BACKUP can now run in a new detached mode, which means the Job ID is now returned immediately without waiting for the BACKUP job to finish. #50775

SQL language changes

Geospatial changes

• Implemented the following built-ins: ST_Transform, ST_Intersection, ST_PointOnSurface, and ST_Union, ST_Segmentize({geometry, float8}), ST_Project({geography,float8,float8}), ST_LongestLine({geometry, geometry}), ST_Azimuth({geometry,geometry}), ST_ShortestLine({geometry, geometry}), ST_Centroid ({geometry,bool}) functions. #49783, #49833, #49827, #49949, #50018, #50188, #50292, #50484
• Implemented the following built-ins: GeometryType, PostGIS_AddBBox, PostGIS_DropBBox, PostGIS_Extensions_Upgrade, PostGIS_Full_Version, PostGIS_GEOS_Version, PostGIS_HasBBox, PostGIS_LibXML_Version, PostGIS_Lib_Build_Date, PostGIS_Lib_Version, PostGIS_Liblwgeom_Version, PostGIS_PROJ_Version, PostGIS_Scripts_Build_Date, PostGIS_Scripts_Installed, PostGIS_Scripts_Released, PostGIS_Version, PostGIS_Wagyu_Version. #50589
• Added support for casting GEOGRAPHY and GEOMETRY types to and from Bytes/JSONB. #50577
• Implemented ST_AsGeoJSON with options to show bbox and CRS information. #49888
• Introduced maxDecimalDigits arguments for ST_AsText and ST_AsEWKT, which allow rounding of the decimal digits output in the WKT representation. #49875
• Populated the spatial_ref_sys table with support SRID entries for geospatial data types. #49887
• Implemented the ST_Azimuth functionality for the geography operator. #50708
• GEOMETRY now coerce invalid geography coordinates into correct geometry. #50457
• Added support for AddGeometryColumn and other functions that mutate schema in IMPORT PGDUMP. #50850

General SQL updates

• For Postgres compatibility, CockroachDB now supports the ANALYZE <tablename> statement that collects statistics on the given table for use by the optimizer. The statement is functionally equivalent to the existing CREATE STATISTICS statement. #49816
• The vectorized execution engine now supports the JSONFetchVal(->) operator, the Pow operator, the Bitand (&), Bitor (|), Bitxor (^), FloorDiv (//), and Mod (%) binary operators, and the binary shift (>> and <<) operators. #49818, #49761, #50143, #50417
• The vectorized execution engine now supports AVG and SUM aggregate functions on Ints and Intervals. #49900
• The vectorized execution engine now fully supports the SUM aggregate function (previously, the summation of integers was incomplete). #49900
• The vectorized execution engine now supports the MIN and MAX aggregate functions on columns of INT2 and INT4 types. #49900
• The optimizer can now remove an unnecessary join from the input of a Project operator. #49788
• The optimizer can now fold two Limit operators together. #49931
• Improved performance for queries with a limit on a join that is guaranteed to preserve input rows. #49802
• Added the crdb_internal.databases virtual table. #49522
• Referencing types across databases has been disabled. #49841
• Views now only create a dependency on a table's column if the column is referenced in the view definition. Previously, all columns in a table referenced in a view were added to the view's dependencies. #49872
• The pgdate util can now parse db2 formatted timestamps. This can be used to import db2 dumps. #50011
• For some EXPLAIN variants, the property distributed for the output variable Field has been renamed as distribution and can have local, partial, or full values. The partial state is currently only possible with the experimental setting. #49857
• Added support for the ALTER COLUMN TYPE ... USING EXPRESSION statement to allow users to optionally provide an expression to convert a column to a new type. #49452
• Enhanced the optimizer's ability to reorder joins. #49948
• Enabled aggregate queries which collect key/value pairs into a JSON object. #48306
• Removed the optimizer_foreign_keys and experimental_optimizer_foreign_key_cascades session settings (and their corresponding cluster settings). #50185
• RegClass expressions are now tracked as dependencies in views. For example, CREATE VIEW v AS SELECT 't'::regclass; will now add a dependency on table t for view v. #50213
• Sequences passed as a string argument into views are now tracked as a dependency. For example, CREATE VIEW v AS SELECT nextval('s') will now add a dependency on sequence s. #50103
• Fixed an error for the ALTER COLUMN TYPE statement for situations where the data matches byte for byte but CockroachDB needs to validate the INT8 -> INT4 conversion. #50278
• Previously, infinity evaluated to a negative value, -292277022365-05-08T08:17:07Z. Now infinity is the maximum supported timestamp in Postgres that is not infinity. Likewise, -infinity is the smallest supported value. Note that this work-in-progress feature currently does not behave exactly like infinity in Postgres. #50311
• The CHAR column type will now truncate the trailing space characters in line with Postgres. Existing stored CHAR entries with spaces at the end of the CHAR column type will no longer return rows with trailing space characters. Use the LIKE query to find and modify these rows. #50492
• Disabled arrays in non-GIN indexes. #50662
• Enabled dropping indexes used in outbound foreign key relationships. #50769
• CockroachDB no longer writes to the slow query log unless explicitly enabled. #50898
• IMPORT INTO now supports columns with constant default expressions, and non-targeted columns with constant default expressions are no longer required to be nullable. #50295

Command-line changes

• Advanced troubleshooting information (such as internal details from the Go runtime) is now saved in a separate log file named <program>-stderr.xxx.log in the configured log directory. #48051
• The server CLI commands (start, start-single-node, demo) now support a new flag named --redactable-logs, which introduces markers in generated log entries to facilitate redaction of sensitive information by cockroach debug zip and other CockroachDB APIs. This is an experimental feature currently not enabled by default, but is recommended for all deployments and will be enabled by default in a later version. Note: the flag is advertised (in --help) by cockroach start and cockroach start-single-node, where log files are expected as a matter of course; it is also supported, yet hidden (from --help), by every other CLI command because every command can also produce log entries when passed --log-dir or --logtostderr. #48051
• The commands cockroach debug zip and cockroach debug merge-logs support a new flag --redact-logs. When specified, it causes log messages that potentially contain confidential data or PII to be redacted away. This feature is experimental. Note: Currently, this redaction is extremely aggressive and may hinder the ability of Cockroach Labs to investigate issues from log files, especially if --redactable-logs is not enabled server-side. Other potentially-confidential data may still be retrieved by cockroach debug zip; the new flag only applies to retrieved log files inside the output zip file. #48051
• cockroach node decommission --wait=live is no longer supported. It was deprecated in an earlier release. #49666
• cockroach quit is now deprecated. Use the subcommands for cockroach node instead, and use termination signals to quit the cockroach process. #49668
• Allowed cockroach dump to dump tables and databases that contain user-defined types. #49808
• The commands cockroach node decommission and cockroach node recommission now recognize a new flag named --self. This can be passed instead of an explicit list of node IDs, and indicates that the operation targets the node the command is connected to (either via --host if specified, or localhost). #50532
• The command-line flag --log-dir-max-size is now deprecated and has been replaced with a new flag named --log-group-max-size. The flags limit the combined size of all files generated by one logging group inside CockroachDB. #50599

DB Console changes

• CockroachDB now preserves the selected tab on the Statements page after reload. #48090
• Fixed Statements page crash on database names with /. #47423
• The Statements page now shows if the vectorized execution engine is used or not. #50255
• Updated the empty state design for the Statement diagnostics history page. #46921
• Updated the tooltip designs on the Overview. #46919
• Updated the Storage Dashboard. #47523
• Fixed icon alignments for the Timeframe navigation buttons. #50010
• The Queues dashboard in the DB Console no longer shows a Compaction Queue graph, and the Queue Processing Failures and Queue Processing Times graphs no longer include the Compaction queue metrics. #50245
• Updated the Login page design. #46783
• Added a tooltip for the Stats by Node metric. #50348
• Changed the label for std deviation from < to > for positive values. #50275
• Updated the message for the Node logs page when remote debugging is restricted for users by using the server.remote_debugging.mode cluster setting. #49553
• The sidebar navigation panel no longer displays the Network Latency menu for a single-node cluster. #50269
• Aligned the Units selector and Remove Chart buttons on the same line. Search input for metric name now properly shows the search term. #50334
• Added a collection of new metrics to track the method of requests sent as RPCs between cluster nodes. These metrics are named distsender.rpc.<method>.sent, with examples of <method> being scan and conditionalput. #46747
• The time range selector has been extended with a 30 minutes option for all Metrics. #50810

Bug fixes

• Previously, when doing a series of table creations and drops, subsequent incremental backups ran into an error. This is now fixed. #49776
• The description provided with --help on the CLI for --socket-dir and --socket has been fixed. They were invalid since v20.1.0. #49905
• Previously, in some cases, CockroachDB didn't check whether the right argument of the Div (/), FloorDiv (//), or Mod (%) operations was zero, so instead of correctly returning a division by zero error, CockroachDB returned NaN, and this is now fixed. Additionally, the error message of modulus by zero has been changed to division by zero to be inline with Postgres. #49761
• Fixed an internal planning error that occurred for recursive CTEs (WITH RECURSIVE expressions) in which the left side of the UNION ALL query used in the CTE definition produced zero rows. #49961
• CockroachDB now correctly handles import cancellation and errors. #49979
• CockroachDB now correctly links producer/consumer during data import in order to handle errors correctly. #49995
• extract(epoch from timestamptz) from a session time zone not in UTC would previously return a value incorrectly offset by the session time zone. This has now been rectified. #50071
• Previously, when streaming values from a column declared of type char(n), the length of the value could be <= n. Now all values streamed have length exactly n by padding spaces to the right if necessary. #49886
• Casting to width-limited strings now works correctly for strings containing Unicode characters. #50156
• Fixed some cases where casting a string to a width-limited string array was not truncating the string. #50153
• Previously, if a full cluster restore failed while restoring the system table data, it would not clean up properly and leave some temporary tables public and not dropped. This is now fixed. #50003
• The Statements page of the DB Console previously displayed incorrect information about whether or not the statement execution was distributed. #50256
• Fixed a bug where an unexpected EOF error would be returned at startup with the Pebble storage engine, if the last instance of cockroach crashed in a rare case of the write-ahead log being written to. #50280
• Fixed a recently introduced no volatility for cast unknown::tuple error. #50436
• CockroachDB now correctly populates the rolvaliduntil value for roles in pg_roles and pg_authid. #50307
• Fixed a bug affecting some DROP DATABASE schema changes where multiple GC jobs are created, causing the GC job for the database to fail. GC jobs will no longer fail upon failing to find a table descriptor already deleted by a different GC job. #50411
• Fixed an internal error when referencing a type that does not exist by ID. #50286
• Lookup join predicate now shows proper column names in EXPLAIN. #50441
• The result of interpreting relative timestamps (like now, today) is no longer stored in cached plans. #50320
• Renaming a temporary table no longer converts it to a persistent table. The table continues to remain temporary after a rename. Moreover, users can no longer move a temp table into the public schema by providing a fully qualified name while renaming the table. #50500
• A server node now properly dumps Go stacks to its log file for troubleshooting upon receiving SIGQUIT during node shutdown. Previously, SIGQUIT was only recognized for this purpose while the node had not started shutting down already. #50539
• Previously performing ALTER TABLE..ADD COLUMN with a computed expression allowed users to provide a column in a different table or database if the column name was the same. Similarly, users could provide an expression referring to another table's column when executing ALTER COLUMN TYPE USING EXPRESSION. This is no longer the case and all columns must refer to the column in the table being altered. #50582
• Fixed a bug where creating a sequence owned by a table's column and dropping the sequence would make it impossible to drop the table after. #50665
• Fixed a bug where Pebble iteration would skip over blocks that failed checksum verification rather than propagating the error to the caller. #50697
• Some pg_catalog queries that previously returned an error like crdb_internal_vtable_pk column not allowed now work again. #50682
• Fixed internal errors in some queries involving CASE expressions with NULL branches. #50626
• Fixed a rare bug where a multi-Range SELECT FOR UPDATE statement containing an IN clause could fail to observe a consistent snapshot and violate serializability. #50633
• Fixed a bug where a cluster restore would fail when the largest descriptor in the backup was a database. This was typically seen when the last action in backing up cluster was a database creation. #50759
• Fixed an internal error during planning for some queries with aggregate functions embedded in complex scalar expressions. #50815
• Fixed a bug where a badly timed power outage or system crash could result in an error upon process restart. #50831
• Fixed a bug where a cluster backup would appear as BACKUP TABLE TO rather than BACKUP TO in the jobs table. #50760
• Fixed the column not in input internal error for some corner cases. #50845
• DROP DATABASE CASCADE now works as expected even when the database has a sequence with an owner in it. #50744

Performance improvements

• Introduced a new flag named server.consistency_check.max_rate expressed in bytes/second to throttle the rate at which CockroachDB scans through the disk to perform a consistency check. This control is necessary to ensure smooth performance on a cluster with large node sizes (i.e., in the 10TB+ range) #49763
• Fixed a performance regression in the SHOW DATABASES command introduced in 20.1. #49522
• Limited SELECT statements now do a better job avoiding unnecessary contention with UPDATE and SELECT FOR UPDATE statements. #49980
• PGCOPY import is now ~3.5x faster by parallelizing the conversion of raw input data to Datums. #50016
• Allowed the optimizer to use enum information to generate better query plans. #49284
• Range merges are now delayed for a short time after load-based splitting to prevent load-based split points from being merged away immediately after load is removed. #50151
• Transaction liveness pushes are now delayed by 50ms, up from 10ms. This allows high contention workloads to sustain high throughput up to much larger concurrency levels. #50161
• Improved sstable files organization in the Pebble storage engine, which should significantly improve import performance. #50371
• Improved the optimizer's estimation of the selectivity of some filters involving a disjunction (OR) of predicates over multiple columns. This results in more accurate cardinality estimation and enables the optimizer to choose better query plans in some cases. #50456
• Many queries that involve now() are executed more efficiently; in particular, the result of now() can be used with an index. #50320
• Better execution plans in some cases involving EXISTS. #50846
• Smoothed out disk writes when transferring Range snapshots to avoid latency spikes for other concurrent operations. #50866

v20.2.0-alpha.1

Release Date: June 17, 2020

Backward-incompatible changes

• The textual error and warning messages displayed by cockroach quit under various circumstances have been updated. Meanwhile, the message "ok" remains as an indicator that the operation has likely succeeded. #45149
• cockroach quit now prints out progress details on its standard error stream, even when --logtostderr is not specified. Previously, nothing was printed on standard error. Scripts that wish to ignore this output can redirect the stderr stream. #45149
• The copy of system and crdb_internal tables extracted by cockroach debug zip is now written using the TSV format (inside the zip file), instead of an ASCII-art table as previously. #48096
• CockroachDB v20.1 introduced a new rule for the --join flag to cockroach start, causing it to prefer SRV records, if present in DNS, to look up the peer nodes to join. This feature is experimental, and has been found to cause disruption in certain deployments. To reduce this disruption and the resulting UX surprise, the feature is now gated behind a new command-line flag --experimental-dns-srv which must now be explicitly passed to cockroach start to enable it. #49077
• Prior to this patch, the phase of server shutdown responsible for range lease transfers to other nodes would give up after 10000 attempts of transferring replica leases away, regardless of the value of server.shutdown.lease_transfer_wait. The limit of 10000 attempts has been removed, so that now only the maximum duration server.shutdown.lease_transfer_wait applies. #45149

General changes

• Transactions reading a lot of data behave better when exceeding the memory limit set by kv.transaction.max_refresh_spans_bytes. Such transactions now attempt to resolve the conflicts they run into instead of being forced to always retry. Increasing kv.transaction.max_refresh_spans_bytes should no longer be necessary for most workloads. #46275
• Schema changes started in 19.2 will now be automatically migrated in the background in 20.1 after upgrade finalization so that they can run to completion. #46504
• Prior to this patch, the phase of server shutdown responsible for range lease transfers to other nodes had a hard timeout of 5 seconds. This patch makes this timeout configurable via the new cluster setting server.shutdown.lease_transfer_wait. #45149
• Statement diagnostics zip bundles now contain a representation of the statement trace that can be imported into Jaeger for visualization. #47303
• The system.replication_constraint_stats report now treats conjunctions of replication constraints as a single unit of reporting, whereas before there was a report entry for each constraint in the conjunction. For example, given a zone with a replication constraint like {"+region=us,+az=az1":1} (meaning, one replica in a node with locality region=us,az=az1), now there will be a report entry for "+region=us,+az=az1" where before there was a separate entry for "+region=us" and another one for "+az=az1". #47389
• The Kubernetes manifests now generate node certificates with additional addresses 'cockroachdb-public.<namespace>' and 'cockroachdb-public.<namespace>.svc'. #47610
• Changed the default engine type for new storage directories from RocksDB to Pebble. Existing stores will continue to use the previously specified storage engine, and an explicit specification (via --storage-engine=...) will override the default. #48145
• Removed the publication of musl libc CockroachDB builds. #49685
• Improved error reporting when trying to access non-existent Google Cloud storage blobs. #49089
• Improved reliability by handling interrupted downloads from Google Cloud external storage more gracefully. #48229
• Treat errors due to draining nodes as retryable when starting CDC. #49743

Enterprise edition changes

• BACKUP and RESTORE now collect some anonymous telemetry on throughput and feature usage. #46716
• SHOW BACKUP now shows whether a BACKUP is a full cluster backup or not. #45860
• The privileges assigned to a given table/database are now visible through SHOW BACKUP. SHOW BACKUP will list which users and roles had which privileges on each table/database in the backup if the WITH privileges option is specified. #45862
• The new appended incremental backup syntax does not allow converting a full cluster backup to a specific table or database backup. #46864
• BACKUP and RESTORE to HTTP storage locations now require explicitly specifying incremental storage locations (i.e., cannot use the new automatically appended incremental syntax). #46887
• Fixed a bug where the job ID of a lagging changefeed would be omitted and instead it would be reported as sinkless. #48493
• Changefeeds now retry after encountering transient errors contacting the Confluent Schema Registry. #48759

SQL language changes

Geospatial SQL updates

• Introduced the ability to parse, store, and display PostGIS style GEOGRAPHY and GEOMETRY types. #47171
• When doing name resolution via search path, the pg_extension schema (containing tables such as geometry_columns, geography_columns and spatial_ref_sys) will now have an attempted resolution before the public schema. This mimics PostGIS behavior where the aforementioned tables are in the public schema, and so by default are discoverable tables with a new CLI session. #49611
• Added support for the AddGeometryColumn function, which adds a new GEOMETRY column to an existing table and returns metadata about the column created. This improves compatibility with PostGIS. #49456
• Added support for ST_Covers, ST_CoveredBy, ST_Contains, ST_Crosses, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches and ST_Within for GEOMETRY geospatial types. #47584
• Added the following geospatial functions: ST_GeomFromText, ST_GeometryFromText, ST_GeogFromText, ST_GeographyFromText, ST_GeomFromWKB, ST_GeomFromEWKB, ST_GeomFromEWKT, ST_GeomFromGeoJSON, ST_GeogFromWKB, ST_GeogFromEWKB, ST_GeogFromEWKT, ST_GeogFromGeoJSON, ST_AsText, ST_AsBinary, ST_AsEWKB, ST_AsEWKT, ST_AsKML, ST_AsGeoJSON. #48015
• Added geospatial functions ST_Area, ST_Length, ST_Distance, and ST_Perimeter, which operate on GEOMETRY data types. #48074
• Added built-ins allowing parsing text and WKB for all supported shapes, e.g., ST_PointFromWKB and ST_PointFromText, which return NULL if the WKT/EWKT/WKB do not match the shape provided. #48441
• Implemented the GEOMETRY based built-ins ST_Relate and ST_ContainsProperly. #48552
• Introduced the following functions that work on GEOGRAPHY types: ST_Covers, ST_CoveredBy, ST_Intersects, ST_Distance, ST_DWithin, ST_Perimeter, ST_Area, and ST_Length. #48529
• Implemented the GEOMETRY based built-in ST_SRID. #49169
• Implemented the ST_Point and ST_MakePoint functions. #49194
• Implemented the ST_SetSRID function on GEOMETRY and GEOGRAPHY types. #49222
• Implemented the ST_MaxDistance and ST_DFullyWithin functions for geometries. #49094
• Implemented the ST_WKBToSQL and ST_WKTToSQL geospatial functions. #49237
• Implemented the GEOMETRY based built-in ST_AsBinary with encoding. #49481
• Implemented the following geospatial built-ins: ST_X (resolves #49069), ST_Y (resolves #49070), ST_NDims (resolves #48992), ST_NumPoints (resolves #49001), ST_NPoints (resolves #48995), ST_StartPoint (resolves #49047), ST_EndPoint (resolves #48924), ST_NumInteriorRings (resolves #48999), ST_NumInteriorRing (resolves #48998), ST_InteriorRingN (resolves #48949), ST_ExteriorRing (resolves #48930), ST_NumGeometries (resolves #48997), ST_GeometryN (resolves #48945). #49341
• Implemented the ST_Centroid built-in function which works for GEOMETRY and STRING arguments. #49613
• Implemented the GEOMETRY built-in ST_AsHexEWKB with encoding. #49644
• Implemented the following built-ins available on GEOMETRY types: ST_Area2D (resolves #48869), ST_NRings (resolves #48996), ST_PointN (resolves #49008), and ST_GeometryType (resolves #48946). #49649
• Implemented the GEOMETRY based built-in ST_Summary. #49738
• Implemented ST_Buffer for GEOMETRY and STRING variants. #49722
• Implemented the following built-in functions: ST_LineInterpolatePoint, ST_LineInterpolatePoints. #49742
• Implemented DWithin for GEOMETRY types. #49085
• Populated the pg_extension.geometry_columns and pg_extension.geography_columns virtual table with metadata for tables which store have columns relevant to GEOMETRY or GEOGRAPHY data types. #49424

General SQL updates

• Updated the type checking code to prefer aggregate overloads with string inputs if there are multiple possible candidates due to arguments of unknown type. #46649
• Added syntax for DECLARE and CLOSE. CLOSE ALL is a no-op, as there are no cursors to close. CLOSE <cursor> and DECLARE raise unimplemented errors. #45662
• Modified the substring() function to allow it support bit and byte arrays. #46710
• Added support for CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY syntax. These statements are no-ops, as all indexes are created concurrently. #46695
• Previously, we implemented the timezone built-in as timezone(<time type>, <timezone>). However, this is the opposite from the Postgres implementation (which allowed timezone(<timezone>, <time type>). In 20.1, we supported both permutations to aid with this migration, but with this change we will only support the timezone first. You can also use AT TIME ZONE, which is compatible with the timezone function in all versions. #46748
• The IMPORT statement now records some additional anonymous telemetry about its performance and reliability. #46731
• Removed the output showing the number of rows reported as affected by the CREATE/ALTER/DROP ROLE statements. The number doesn't make sense to include. Since the rows affected include some rows in system tables such as system.role_options rows, this number can be misleading (e.g., CREATE ROLE returning 3). This behavior matches Postgres, where no number is returned for these commands. #46795
• Added a hint to use ALTER ROLE when trying to "grant" a role option directly to a user (using the GRANT ROLE syntax). #46795
• Added a new unimplemented error that is signalled when attempting to ADD CONSTRAINT with the EXCLUDE USING syntax. #46787
• Added support for using CREATE INDEX .... INCLUDE (col1, col2, ...), which is an alias that PostgreSQL uses that is analogous to our STORING (col1, col2, ...) syntax. #46785
• Added support for parsing the REINDEX syntax, which results in an unimplemented error that explains that REINDEXing is not required in CockroachDB. #46850
• The vectorized execution engine will only run queries with streaming operators. Issue SET vectorize=on to enable the vectorized execution engine for buffering operators. #46845
• Added support for compressing output files using gzip. #46932
• If setting an invalid cluster setting, the invalid values are now returned. #46932
• CockroachDB will now be more verbose about full scans when executing EXPLAIN. Previously, we would output SPANS | ALL, now it will be SPANS | FULL SCAN or (if there is a limit) SPANS | LIMITED SCAN. #46708
• The bit_and() aggregate function now supports BIT and VARBIT data types. #46954
• The bit_or() aggregate function now supports BIT and VARBIT data types. #47023
• The error messages for duplicate columns in NATURAL and USING joins now match the error messages used by Postgres. #47099
• Modified the strpos() function to allow it to support BIT and BYTES arrays. #46875
• Improved the error message for ALTER COLUMN ... SET DATA TYPE ... for data type conversions that involve overwriting existing values. #47155
• Modified SHOW TABLES to return schema and table type. Furthermore, sequences will now appear in SHOW TABLES. Any user who relies on SHOW TABLES to return one column can use SELECT table_name FROM [SHOW TABLES] to get compatible behavior between previous versions of CockroachDB. #46756
• Made small UX improvements to hash sharded index creation. #47017
• Added CREATE TABLE LIKE specifiers. #47017
• Added support for the CREATE OR REPLACE VIEW command. #47051
• We previously allowed mixed type 20.1/19.2 upgrades to add precision types. However, the 19.2 nodes would disrespect the precision component and we documented that. To make this cleaner, we instead only allow precision to be specified in time-related types when the version upgrade is complete. #47425
• Histogram collection with CREATE STATISTICS is no longer supported on columns with type ARRAY. Only row count, distinct count, and NULL count are collected for array-type columns. #47281
• A new default_transaction_priority session variable is now supported, which configures the priority that SQL transactions use by default. The accepted options for the setting are LOW, NORMAL, and HIGH. #47641
• Removed two public cluster settings sql.distsql.temp_storage.joins and sql.distsql.temp_storage.sorts that can disable usage of temporary on disk storage of intermediate results of query execution. #47357
• Added support for collection of multi-column statistics. By default, statistics are now collected on all prefixes of each index, in addition to 100 other individual columns. This feature can be disabled by setting the cluster setting sql.stats.multi_column_collection.enabled to false. #47729
• Added square root and cube root unary operators |/ and ||/. #47680
• We previously supported any TIMESTAMP(TZ) representable in Go. However, this is incompatible with the Postgres protocol format, as well as resulting in sorting errors when using values such as (-9223372036854775808)::TIMESTAMP. We have changed our bounds for timestamps to be the same as Postgres - between 4714-11-24 00:00:00+00 BC and 294276-12-31 23:59:59.999999 inclusive. Users who have previously stored these values will be able to access them, but the WHERE clause must use < '4714-11-24 00:00:00+00 BC' or > 294276-12-31 23:59:59.999999. #47077
• Users now have the ability to control the level of NOTICE outputs with the session variable client_min_messages. #46650
• ROLLBACK TO SAVEPOINT is no longer permitted after miscellaneous internal errors. #47724
• Common table expressions (CTEs) were extended to support the MATERIALIZED/NOT MATERIALIZED syntax added in PostgreSQL 12. #47341
• The pg_collation, pg_proc, and pg_type tables in pg_catalog no longer require privileges on any database in order for the data to be visible. #47996
• Added two new session variables and corresponding cluster settings to control whether the optimizer uses histograms and multi-column statistics for cardinality estimation. The session variables are optimizer_use_histograms and optimizer_use_multicol_stats, with corresponding cluster settings sql.defaults.optimizer_use_histograms.enabled and sql.defaults.optimizer_use_multicol_stats.enabled. Both settings are enabled by default. #48105
• Reduced virtual table memory use. #48066
• Added virtual schema and populated the pg_catalog.pg_aggregate table. #48126
• The default vectorized execution mode is now "on", which means that all queries that can use vectorized execution will do so. To revert to previous behavior SET CLUSTER SETTING sql.defaults.vectorize='201auto'. #48059
• Added support for the ALTER TABLE ... ADD COLUMN ... REFERENCES ... syntax for tables that are empty. #47082
• Caused primary key changes to not create a copy of the old primary key if the primary key change only changes the bucket count of a hash sharded index. #45894
• Populated the pg_proc table's provolatile field based on the internal built-in volatility definition. This value used to always be NULL. #48491
• The RowDescription message of the pgwire protocol now contains the table OID and column ID for each column in the result set. These values correspond to pg_attribute.attrelid and pg_attribute.attnum. If a result column does not refer to a simple table or view, these values will be zero, as they were before. #48417
• Added support for indexing and ordering of arrays of indexable and orderable inner types. #48045
• Correctly report type length for the CHAR data type. #48602
• Changed the statistics collection logic to only include a row in the NULL count for a column statistic if all columns in the statistic are NULL. Previously, the row was included if any of the columns were NULL. This change makes it easier to use multi-column statistics for accurate cardinality estimation in the optimizer. #48528
• Fixed an issue with optimizing subqueries involving set operations that could prevent queries from executing. #48658
• The pg_database table in pg_catalog no longer require privileges on any database in order for the data to be visible. #48744
• Improved the optimizer's ability to inline constant values in filter conditions. #48629
• Improved compatibility of the pg_typeof built-in function. #48614
• Introduced the pg_extension virtual schema, which contains tables which are on the public schema when using Postgres style extensions. #48754
• Renamed the experimental_serial_normalization session variable to serial_normalization since the setting is no longer experimental. #48796
• The RowDescription message of the pgwire protocol now contains the type modifier for each column in the result set. This corresponds to pg_attribute.atttypmod. If it is not available, the value is -1, as it was before. #48762
• Added support for the percentile_disc and percentile_cont ordered-set aggregation functions. #47668
• Added support for Postgres ENUM types to be used in queries and stored in tables. #48556
• Added the Postgres supported ENUM built-ins enum_first, enum_last, and enum_range. #49086
• The catalog table pg_type is now populated with rows for user defined types (like ENUMs). #48693
• Populated the catalog table pg_catalog.pg_enum. #49213
• 24:00 time now displays correctly in the CLI, returning 0001-01-02 00:00:00. Furthermore, backups correctly emit and read in 24:00 time properly. #49330
• Vectorized execution engine now supports (although not "natively" - meaning we do not have optimized representations for the newly-supported types) all data types that CockroachDB supports. #48422
• Vectorized execution engine now supports set operation joins (queries with INTERSECT ALL and EXCEPT ALL). #49159
• Added the Postgres syntax GENERATED ALWAYS alias for computed column construction. #49525
• Correctly populated the rolcanlogin value for roles in pg_roles and pg_catalog. #49389
• Added support for the max/min aggregation functions on collated strings. #49530
• Added support for referencing static data types under the pg_catalog qualification like pg_catalog.int. #49307
• Added support for the max/min aggregation functions on ENUM types. #49558
• Added experimental support for the ALTER COLUMN TYPE online schema change for changing a column type's data. #46933
• Previously, using NULL (or tuples containing _NULL_s) as the left-hand-side of an IN operator would not typecheck unless the _NULL_s were explicitly casted. Now, the casting is not required. #49723
• The optimizer can now fold two grouping operators together when they are aggregating over functions like sum. #49627
• The optimizer can now eliminate an unnecessary join that is the input to a GroupBy operator. #49683
• Added the crdb_internal.create_type_statements virtual table. It holds create statements for user defined types. #49771
• The vectorized execution engine now supports the "Concat" ("||") operator. #49758

Command-line changes

• The cockroach demo command now displays a connection URL to the demo cluster, and in a multi-node cluster it displays connection strings for all of the nodes in the cluster. #46913
• It is now possible to pre-configure the secure mode of cockroach demo using the environment variable COCKROACH_INSECURE like other client commands. #46922
• The client-side commands specific to cockroach demo, starting with \demo, are now advertised in the output of \?. Note that this feature is currently experimental. #46962
• The cockroach CLI commands that internally use SQL, including cockroach sql, now can connect to a server using a unix datagram socket. The syntax for this is --url 'postgres://user@?host=/path/to/directory?port=NNNN'. #46968
• The cockroach demo command now displays a connection URL using a unix datagram socket before the TCP-based URL, as this may deliver better performance in clients running on the same system. #46979
• The SQL URL generated by cockroach demo, when running in the secure mode, now embeds the password. This way, a command ran by copy-pasting the URL is able to run without asking for a password first. #46979
• The SQL URL generated by cockroach demo is simplified to no longer require TLS client certificates in particular directory locations. #46979
• The new client-side command \demo ls (experimental) can now (re-)display the connection parameters for every node in the simulated cluster. #46979
• Added a new cockroach start option --clock-device that allows CockroachDB's hybrid logical clock (HLC) to use an IEEE 1588 PTP clock device for the HLC current time. #46942
• The time that cockroach quit waits client-side for the node to drain (i.e., remove existing clients and push range leases away) is now configurable via the command-line flag --drain-wait. Note that separate server-side timeouts also apply separately, check the server.shutdown.* cluster settings for details. #45149
• It is now possible to drain a node without shutting down the process, using cockroach node drain. This makes it easier to integrate with service managers and orchestration: it now becomes safe to issue cockroach node drain and then separately stop the service via a process manager or orchestrator. Previously, there was a risk to misconfigure the service manager to auto-restart the node after it had shut down via quit, in a way that was surprising or unwanted. The new command node drain also recognizes the new --drain-wait flag. #45149
• The commands cockroach quit and cockroach node drain now report a "work remaining" metric on their standard error stream. The value reduces until it reaches 0 to indicate that a graceful shutdown has completed server-side. An operator can now rely on cockroach node drain to obtain confidence of a graceful shutdown prior to terminating the server process. #45149
• The default value of the parameter --drain-wait for cockroach quit has been increased from 1 minute to 10 minutes, to give more time for nodes with thousands of ranges to migrate their leases away. #46396
• The error message displayed upon cockroach start / cockroach start-single-node when manual intervention is needed in the store directory is now clearer. #47756
• Client commands such as cockroach init and cockroach quit now support the --cluster-name and --disable-cluster-name-verification flags in order to support running them on clusters that have been configured to use a cluster name. Previously, it was impossible to run such commands against nodes configured with the --cluster-name flag. #48000
• Added support to cockroach cert list for certificates which require --cert-principal-map to pass validation. #48013
• cockroach debug zip now properly skips over fully decommissioned nodes. #48073
• cockroach debug zip now tries multiple times to retrieve data using SQL if it encounters retry errors. #48099
• Enabled cockroach dump to dump all databases with a single command using the new --all flag. #47387
• cockroach debug zip now supports two command-line parameters: --nodes and --exclude-nodes. When specified, they control which nodes are inspected when gathering the data. This makes it possible to focus on a group of nodes of interest in a large cluster, or to exclude nodes that debug zip would have trouble reaching otherwise. Both flags accept a list of individual node IDs or ranges of node IDs, e.g., --nodes=1,10,13-15. #48150
• Added support for the --cert-principal-map flag in the cockroach cert command, and various "client" commands such as cockroach sql, cockroach init, and cockroach quit. #47449
• CockroachDB's own SQL shell (cockroach sql and/or cockroach demo) no longer prompts for more lines of input after the user enters BEGIN before sending the input to the server. Instead, full lines of input are always sent to the server immediately. The corresponding client-side option smart_prompt thus becomes ineffective and is deprecated. It will be removed in a later version. #48433
• Made the --storage-engine flag to cockroach start sticky (i.e., resolve to the last used engine type when unspecified), even when specified stores are encrypted at rest. #48721
• The --decommission flag for cockroach quit is now removed. It was previously deprecated in CockroachDB v20.1. Users should be using cockroach node decommission instead. #49350

DB Console changes

• Dismissing the Release note signup form is now permanent (per user). For insecure mode, it is now dismissed globally. #46522
• Added a vertical scroll bar on the Cluster Overview main panel to allow: scroll down to Node Map section, and scroll down Node list table. #46741
• Changed the default sorting column on the Statements page to Execution Count. #46780
• Tooltips showing statements and jobs are now limited in size for very long statements. #46557
• The default timescale on the metrics page is now always 10m. Previously, it defaulted to the age of the longest running node. #46190
• Added a tracking call to Analytics, sending tracking data for Table sorts. #45920
• Added Analytics tracking for searches on the Statements page. #45920
• Added Analytics tracking for pagination on the Statements page. #45920
• Added a tracking call for diagnostics activation on the Statements page. #45920
• Changed a statement parameter type back to string from AggregateStatics. #45920
• Moved the diagnostics activation tracking call to a more accurate place. #45920
• Fixed a bug where sort columns were only being applied per-page instead of for the entire multi-page list of statements. #46416
• Added analytics tracking for the statement details sub-navigation. #46789
• Changed the download link for statement diagnostics to point to the diagnostics bundle zip file. #46790
• Improved tooltips for existing capacity and storage metrics. #46801
• Removed the Statements tab from the Databases > Table Details page. #47089
• Fixed and cleaned up bar charts code on the Statements page. #46574
• Fixed a bug on the Statements page that would not reset pagination state to page 1 after the app selection was changed. #46972
• Updated layout sizes. #46627
• Updated colors according to new designs. #45979
• Use new SourceSansPro font across all of the DB Console. #45979
• Updated font styles for monospaced text (SQL statements, etc.). #45979
• Added links to statement details from the Statement Diagnostics history page. #46923
• Added tooltips with full length statements on the Statement Diagnostics history page. #46923
• Updated the Database loading state design. #46857
• Updated the design of empty state on the Jobs, Diagnostics, Statements, and Statements Details pages. #45981
• Fixed a bug where "Raft log too large" was reported incorrectly for replicas for which the raft log size is not to be trusted. #48032
• Fixed a bug where a multi-node cluster without localities defined wouldn't be able to render the Network Latency page. #49138
• Fixed a bug where the link to specific problem ranges had an incorrect path. Problem ranges are now linked correctly again. #49122
• Fixed a bug where the metrics timepicker was always defaulting to the "Custom" date pane. Access to time window presets has been restored and is always shown first. The "Custom" pane is accessed by selecting "Custom" in the dropdown as before. #49133
• Fixed the incorrect display of the table replica count on the Table Details page. #49185
• Fixed the Jobs page header overlapping with scrollbars. #49552
• Updated the tooltip design on the Statements page. #46626
• Removed Capacity and Logical Bytes per Store graph names from storage tooltips to avoid redundancy. #47475
• The Databases page now shows a default error message about restricted permissions for non-admin users. #49592

Bug fixes

• Index and table GCs happen closer to their GC deadline. #46715
• A rare bug causing an assertion failure was fixed. The assertion error message was "caller violated contract: discovered non-conflicting lock". The bug could have theoretically allowed isolation violations between transactions without hitting the assertion, though this was never observed in practice. #46625
• Fixed an incorrect query result that could occur when a scalar aggregate was called with a NULL input. #46649
• Benign "Outbox Next" errors from the vectorized runtime are now only logged when log verbosity is set to 1 or greater. #46724
• Statement diagnostics created through EXPLAIN ANALYZE (DEBUG) now show up in the Statement Diagnostics page of the DB Console. #46759
• RESTORE will now properly clean up partially imported data after having failed or being cancelled. #46766
• Fixed a bug where failed or cancelled IMPORTs may not have cleaned up partially imported data. #46727
• Fixed a rare bug causing errors to be returned for successfully committed transactions. The most common error message was TransactionStatusError: already committed. #46596
• Fixed a bug for statement diagnostics that led to tooltips always showing, instead of only on hover. #46854
• The "insecure cluster" indicator is now displayed again for insecure clusters in the DB Console, at the top right of the screen. #46844
• A rare assertion failure that contained the text "existing lock cannot be acquired by different transaction" was fixed. This assertion was only present in earlier v20.1 releases and not in any earlier releases. This bug created a risk of servers crashing unexpectedly due to improper synchronization of KV requests. #46830
• Fixed incorrect result with count(*) when grouping on constant columns. #46879
• Fixed a bug where CockroachDB could hit an internal error when queries with projections only of INT2 and/or INT4 columns were executed via the vectorized engine. #46712
• cockroach demo now properly cleans up its temporary files if the background license acquisition fails. #46991
• Fixed a bug where, when using the TIME data type as an index, comparisons using the != operator would return incorrect results if '24:00:00' was located in the index. #46993
• Fixed a bug where CockroachDB was using an existing table name 100% of the time when creating a new table, which resulted in no tables being created. #47056
• Fixed a bug where CockroachDB could incorrectly consider a non-NULL value from an interleaved parent table to be NULL when the interleaved child had a NULL value in the row with the corresponding index key. #47035
• Fixed an error that could incorrectly occur when planning queries with NATURAL and USING joins containing multiple aliases for the same column. #47099
• CockroachDB now returns a proper error for index creation statements using a column which does not exist. #47090
• CDC no longer combines with long running transactions to trigger an assertion with the text "lock timestamp regression". This bug could crash a server if the right sequence of events occurred. This was typically rare, but was much more common when CDC was in use. #47101
• Fixed a bug where in some cases where system tables have changed, incremental, full-cluster BACKUPs with revision history were sometimes incorrectly disallowed. #47092
• As part of migrating to the new schema change job implementation in 20.1, failed IMPORT and RESTORE jobs which left behind table data in 19.2 that had not been completely garbage collected by the time the cluster was upgraded to 20.1 will now have GC jobs automatically created for them. #47136
• Fixed a data race on AST nodes for SELECT statements that include a WINDOW clause. It is unclear whether this could have resulted in incorrect results being returned for these queries. #47146
• Fixed a bug preventing clusters from creating TIMETZ columns before they accept an upgrade to 20.1 without downgrading. #47156
• Fixed an infinite loop when adding a self-referencing foreign key constraint in the same transaction which creates a table. #47113
• Fixed a bug where it was possible to drop a column that was referenced in a computed column expression. #47085
• Fixed a bug where a mechanism to detect stalled disks was erroneously left disabled. It is now enabled: nodes will verify that they can write to their engines before each attempt to announce themselves as live to the cluster. #46975
• Fixed the behavior of crdb_internal.zones in mixed-version clusters. #47173
• Fixed reads from system.namespace and crdb_internal.zones in 19.2 nodes in mixed cluster settings. #47173
• Fixed a bug where restoring a backup from 2.1 to 20.1 with a TIMESTAMP column would result in incomplete type data. This would crash commands such as SHOW COLUMNS FROM <new_table>. #47234
• Fixed an incompatibility with 19.2 nodes for tables with computed columns. #47269
• Fixed an internal "no output column equivalent to X" error that could occur in some very rare cases. #47159
• Fixed a bug that could cause CockroachDB processes to crash due to an assertion failure with the text "expected latches held, found none". The bug became louder (and crashed servers) due to recent changes that added new assertions to the code. #47247
• Fixed an incorrect error that occurred when executing UNION statements with hidden and non-hidden columns. #47094
• Fixed a bug where in some cases limits were applied incorrectly when pushed down into scans (resulting in some queries returning more results than they should). #47287
• Fixed a bug in the new schema change GC job implementation causing unnecessary table descriptor lookups whenever any table was updated. #47313
• Fixed a critical bug that would otherwise cause "invalid truncation decision" panics. #47143
• Fixed a bug preventing NULL index members from being added to hash sharded indexes. #47311
• Fixed a backwards incompatibility between RocksDB and Pebble that prevented RocksDB from opening a Pebble-created WAL file under certain conditions. #47350
• Fixed a mishandling of truncated WAL records in Pebble which could prevent Pebble from opening a DB after a crash. #47350
• Fixed a short (3s) period of unavailability that could occur on a range after removing a replica. #41122
• Fixed an error that occurred when statistics collection was explicitly requested on a column with type ARRAY. #47281
• Avoid a condition during rapid version upgrades where a node would refuse to start, claiming "a store is too old for running version". Before this bug fix, the workaround was to decommission the node, delete the store directory, and re-add it to the cluster as a new node. #47358
• TRUNCATE can now run on temporary tables, fixing a bug in 20.1.0 where temporary tables could not be truncated, resulting in an error unexpected value: nil. #47482
• Fixed a bug where instead of returning a parsing error in queries with count(*), CockroachDB could incorrectly return no output (when the query was executed via the row-by-row engine). #47036
• Fixed a bug that could cause a workload to stall under heavy load. This stall was due to a deadlock that was introduced in an earlier v20.1 release. #47465
• Fixed a bug where columns which were members of hash sharded indexes could not be renamed. Indexes created in prior releases will need to be dropped and recreated to resolve this limitation. #47431
• Fixed a bug that could trigger an assertion with the text "received X results, limit was Y". The underlying bug was only performance related and could not cause user-visible correctness violations. The bug could result in an assertion failure and a node crashing. Even though this was an old bug (present in many releases before v20.1), it became a lot easier to hit in v20.1 because we started performing ranged intent resolution more often due to implicit SELECT FOR UPDATE. #47492
• Fixed incorrect results that could occur when casting negative INTERVALs or TIMESTAMPs to type DECIMAL. #47483
• Fixed a bug introduced with the new schema change job implementation that caused errors when rolling back a schema change to be swallowed. #47446
• Fixed a panic that could occur when SHOW RANGES or SHOW RANGE FOR ROW was called with a virtual table. #47500
• Setting kv.closed_timestamp.target_duration to 0 now disables the closed timestamp as was previously documented. #47480
• Fixed a bug where CockroachDB could not dump tables that have no visible columns. #46406
• Fixed a bug causing some schema change rollbacks to fail permanently even on transient errors. #47553
• Fixed a bug introduced in 20.1 that could cause multiple index GC jobs to be created for the same schema change in rare cases. #46929
• Fixed an issue in RocksDB that could result in missing data in some very specific instances that are very unlikely to be encountered in practice. #47448
• Fixed an incompatibility between Pebble and RocksDB bloom filters that could result in keys disappearing or reappearing when switching storage engines. #47612
• Fixed a bug where concurrent schema change interactions could lead to schema changes being blocked for minutes. #47462
• Fixed a bug where PARTITION BY and ORDER BY columns in window specifications were losing qualifications when used inside views. #47709
• Removed redundant metadata information for subqueries and postqueries in EXPLAIN (VERBOSE) output. #47951
• Return a proper Postgres error code from DROP INDEX statements when dropping UNIQUE indices. #47585
• Fix a rare bug where stats were not automatically generated for a new table. #47718
• Fixed a planning error that could happen in rare cases when a histogram was used for a descending indexed column. #48033
• Fixed a bug where CockroachDB could return an internal error when performing a query with CASE, AND, OR operators in some cases when it was executed via the vectorized engine. #47938
• cockroach debug zip can now successfully avoid out-of-memory errors when extracting very large system or crdb_internal tables. #48096
• Return a proper SQLSTATE error for index creation statements using an existing index name. #48035
• cockroach debug zip will now properly report an error if some error is encountered while writing the end of the output zip file. #48109
• Fix cockroach debug encryption-status and the DB Console display of encryption status when using Pebble. #48151
• Fixed "non-values node passed as fixed value to zigzag join" internal error. #48128
• Return a proper CockroachDB-specific Postgres error code for trying to modify a table without a primary key instead of the generic XXUUU error code. #48125
• Fixed a bug where a read operation in a transaction would error out for exceeding the maximum count of results returned. #48160
• Fixed a bug in which (tuple).* was only expanded to the first column in the tuple and the remaining elements were dropped. #48225
• CockroachDB will now avoid producing a severe "internal error" upon certain privilege check failures via pg_catalog built-in functions. #48216
• Fixed a bug where more than 1 job was created from a DROP COLUMN statement, which was confusing clients. Now, we better distinguish between the delete jobs for columns and dependent jobs for deleting indices, views and sequences. #48163
• Fixed a bug where vectorized queries on composite data types could sometimes return invalid data. #48052
• Fixed a rare bug causing a range to deadlock and all the writes to the respective range to timeout. #48082
• Fixed a bug that could lead to data corruption or data loss if a replica was both the source of a snapshot and was being concurrently removed from the range, and certain specific conditions existed inside RocksDB. This scenario is rare, but possible. #48320
• Made SRV resolution non-fatal for join list records, to align with the standard and improve reliability of node startup. #47735
• Fixed a long-standing bug where HTTP requests would start to fail with error 503 "transport: authentication handshake failed: io: read/write on closed pipe" and never become possible again until the node is restarted. This bug existed since v2.1 or some time prior. #48369
• When processing the --join flag to cockroach start, invalid SRV records with port number 0 are now properly ignored. #48325
• Fixed a bug where SHOW STATISTICS USING JSON contained incorrect single quotes for strings with spaces inside histograms. #48514
• Return a proper SQLSTATE error when trying to ADDor RENAME a column with an existing name. #48120
• Prevent dropping of databases which contain tables which are currently offline due to IMPORT or RESTORE. Previously, dropping a database in this state could lead to a corrupted schema which prevented running backups. #48596
• The two settings kv.range_split.by_load_enabled and kv.range_split.load_qps_threshold are documented but were incorrectly marked as non-public in the output of SHOW CLUSTER SETTINGS. This has been corrected. This bug was introduced in v20.1. #48582
• Fixed a bug preventing timestamps from being closed, which could result in failed follower reads or failure to observe resolved timestamps in changefeeds. #48561
• Fixed a bug which falsely indicated that kv.closed_timestamp.max_behind_nanos was almost always growing. #48521
• Previously, when an IMPORT failed, it created a GC job which would behave as a no-op. Now the partially imported data after an IMPORT fails or is cancelled should be deleted. #48593
• Fixed a bug where CockroachDB could encounter an internal error when a query with LEFT SEMI or LEFT ANTI join was performed via the vectorized execution engine in some cases. This was likely to occur only with the vectorize=on setting. #48659
• Fixed a bug where the SHOW CREATE statement would sometimes show a partitioning step for an index that had been dropped. #48624
• Fixed a bug where cockroach dump on tables with interleaved primary keys would erroneously include an extra CREATE UNIQUE INDEX "primary" ... INTERLEAVE IN PARENT statement in the dump output. This made it impossible to re-import the dumped data without manual editing. #48688
• Re-allow diagnostics.forced_sql_stat_reset.interval, diagnostics.sql_stat_reset.interval and external.graphite.interval to set to their maximum values (24hr, 24hr and 15min respectively). This previously only excluded these values to be allowed. #48758
• Fixed a bug where changing the primary key of a table that had partitioned indexes could cause indexes to lose their zone configurations. In particular, the indexes that got rebuilt as part of a primary key change would keep their partitions, but lose the zone configurations attached to those partitions. #48510
• Fixed a bug where cockroach dump on a table with collated strings would omit the collation clause for the data insertion statements. #48687
• Manually writing a NULL value into the system.users table for the "hashedPassword" column will no longer cause a server crash during user authentication. #48773
• Fixed a bug introduced in v20.1.0 where the migration for ongoing schema change jobs would cause the node to panic with an index out of bounds error upon encountering a malformed table descriptor with no schema change mutation corresponding to the job to be migrated. #48823
• Properly support restoring tables that were backed up while they were in the middle of a schema change. #47588
• Fixed a bug where columns of a table could not be dropped after a primary key change. #49081
• Fixed an error that could occur when using NULL in some array indirections. #48830
• Fixed incorrect logic for IS NULL and IS NOT NULL operators with tuples, correctly differentiating them from IS NOT DISTINCT FROM NULL and IS DISTINCT FROM NULL, respectively. #48299
• Fixed costing of lookup join with a limit on top, resulting in better plans (that utilize lookup join) in some cases. #48862
• SHOW BACKUP SCHEMAS no longer shows table comments, as they may be inaccurate. #48167
• Fixed a bug where when a database was dropped, it would not drop the entry for its public schema in the system.namespace table. #49125
• Fixed a possible deadlock during vectorized query execution. #49106
• Fixed a memory leak which could affect CHANGEFEEDs performing scans of large tables. #49101
• Improved accuracy of column types returned from queries to improve PostgreSQL compatibility. #48619
• Fixed a bug where use of arrays combined with window functions was causing a query failure. #49234
• Fixed a small race in the vectorized execution engine. #49202
• Fixed a bug where contended DROP INDEX queries returned an assertion failure error rather than a retryable error. #49221
• Some benign errors were previously reported as unexpected internal errors by the vectorized execution engine; this is now fixed. #49260
• Prevent namespace orphans (manifesting as database "" not found errors) when migrating from 19.2. #49099
• Removed duplicate SQL memory metrics. #47750
• When the value passed to --drain-wait is very small, but non-zero, cockroach quit in certain cases would not proceed to perform a hard shutdown. This has been corrected. This bug existed since v19.1.9, v19.2.7 and v20.1.1. #49362
• Fixed a bug where in some cases an internal error could occur when queries that have columns of BYTES type in the output were executed via the vectorized engine. #49223
• CockroachDB now correctly reports engine type in bug reports when using cockroach demo. #49372
• Fixed a bug where \demo node restart would not work due to an invalid certificate directory. #49382
• Abandoned intents due to failed transaction coordinators are now cleaned up much faster. This resolves a regression in v20.1.0 compared to prior releases. #49218
• Fixed a bug where if one attempted to perform a full cluster RESTORE on a backup that did not contain any user data, it would fail. #49591
• CockroachDB no longer leaks file descriptors during GSS authentication. #49572
• Fixed a bug where CockroachDB could return an internal error or incorrect results on queries when they were run via the vectorized execution engine and had a hash router in the DistSQL plan. This could only occur with the vectorize=on setting. #49333
• Fixed a bug where executing a large number of statements in a transaction without committing could previously crash a CockroachDB server. #48859
• The schema change workload is meant for testing the behavior of schema changes on clusters with nodes with minimum version 19.2. It will deadlock on earlier versions. #49662
• Fixed a panic observed as "unexpected arg type tree.DOidWrapper". #49601
• Fixed a bug where if ctx.err() is nil, the CLI will panic when a workload returns an error. #48338

Performance improvements

• Some queries which contain a division by a constant have improved execution plans. #46809
• The query planner can now decorrelate correlated exists subqueries with LIMIT expressions, leading to better query plans. #46540
• Make virtual tables that access all table descriptors make fewer round trips. #46872
• Reduce time needed to run a BACKUP command when it is built on a lot of previous incremental backups. #47158
• The query optimizer now produces faster query plans for some disjunctions (OR expressions) by utilizing multiple indexes. #47094
• The query optimizer is now more selective in splitting disjunctions into unions, avoiding the overhead of unnecessary memo expansion. #47161
• Improve performance of UPDATE on tables with GIN indexed columns. #47354
• Query optimization for disjunctions was extended to support queries which do not select strict keys. #47342
• The query optimizer is now smarter when splitting disjunctions into unions by grouping disjunctions based on the columns referenced. #47361
• The query optimizer now uses a more efficient plan when splitting disjunctions into multiple sub-queries. #47454
• Query optimization for disjunctions was extended to support scans over GIN indexes. #47542
• Histograms are now used by the optimizer to estimate the cost of index scans with multiple constrained columns or descending columns. This enables better query plan selection in some cases. #47565
• Improved execution plans by removing unnecessary remaining filters in some cases where the filters are reflected by an index constraint. #47623
• The cleanup job which runs after a primary key change to remove old indexes, which blocks other schema changes from running, now starts immediately after the primary key swap is complete. This reduces the amount of waiting time before subsequent schema changes can run. #47624
• The query optimizer now uses a more efficient plan when splitting disjunctions into multiple sub-queries. #47617
• The query optimizer is now more efficient when generating plans for queries with many OR expressions. #48025
• Filtered scans over virtual tables have improved performance in common cases. #47316
• SHOW CREATE is much more efficient. #47316
• The query optimizer can now split disjunctions in more complex expressions into a union over two index scans. #48036
• Disabled the Go runtime block profile by default, resulting in a small but measurable reduction in CPU usage. The block profile has diminished in utility with the advent of mutex contention profiles and is almost never used during performance investigations. #48133
• The optimizer can now detect when an EXISTS subquery can be eliminated because the input has zero rows. This leads to better plans in some cases. #48162
• Before this change, a simple schema change could take 30s+. The reason was that if the schema change is not first in line in the table mutation queue it would return a retryable error and the jobs framework would re-adopt and run it later. The problem is that the job adoption loop timer is 30s. Instead of returning on retryable errors, we retry with an exponential backoff in the schema change code. This pattern of dealing with retryable errors in client job code is encouraged vs. relying on the registry, because the latter leads to slowness and additionally to more complicated test fixtures that rely on hacking with the internals of the job registry. #48608
• Fixed a bug in the histogram filtering logic in the optimizer which was causing inaccurate cardinality estimates for queries with equality predicates on UUIDs and strings, as well as some other types. This bug has existed since histograms were first introduced into the optimizer in the 19.2.0 release. Fixing it improves the optimizer's cardinality estimates and results in better query plans in some cases. #48580
• Histograms used by the optimizer for query planning now have more accurate row counts per histogram bucket, particularly for columns that have many NULL values. This results in better plans in some cases. #48528
• DROP TABLE performance is improved due to doing fewer roundtrips for object comment maintenance. #48265
• The optimizer and execution engine can now plan lookup joins into virtual indexes, avoiding full scans against virtual tables. #48226
• Lookup joins with no required ordering are now more efficient. #48439
• A performance inefficiency has been fixed in the vectorized execution engine which results in speed ups on all queries when run via the vectorized engine, with most noticeable gains on the queries that output many rows. #48669
• The optimizer now normalizes a comparison operator (=, >=, <=, <, >) with a nested "timezone" function applied to a variable. This results in the generation of constrained index scans in more cases, and therefore, better query plans. #48724
• Reduce memory allocations and improve performance of low-level KV scan operations on Pebble. #49121
• COUNT is now converted to COUNT(*) (aka COUNT_ROWS) in more cases. #49126
• Lookup join performance has been improved. #48058
• The optimizer can now determine more accurate costs for query plans with a combination of disjunctions and contradictions. #49462
• Default replication configurations have been tuned to support higher replication throughput in high-latency replication quorums. #49619
• Fixed a bug in the histogram logic in the optimizer which was causing an over-estimate for the cardinality of constrained index scans in some cases when multiple columns of the index were constrained. This problem was introduced early in the development for the 20.2 release, so should not have ever been part of a release. The fix improves the optimizer's cardinality estimates and may result in better query plan selection. #49134
• Fixed the optimizer's distinct count estimate for columns constrained by an index constraint, which was too low in some cases. The fix improves the optimizer's cardinality estimates, which can lead to better query plan selection. #49134
• Fixed the optimizer's estimated distinct count for a multi-column statistic when all of the columns in the statistic are constrained by a filter predicate. The fix can lead to improved cardinality estimates, leading to better query plan selection in some cases. #49134
• Added support for calculating the selectivity of filter predicates in the optimizer using multi-column statistics. This improves the cardinality estimates of the optimizer when a query has filter predicates constraining multiple columns. As a result, the optimizer may choose a better query plan in some cases. #49134
• Remove compaction-debt based SSTable ingestion backpressure which was artificially slowing down IMPORTs and RESTOREs on Pebble and not providing any utility on RocksDB. Removed the private rocksdb.ingest_backpressure.pending_compaction_threshold cluster setting. #49726

Build changes

• It's now possible to build CockroachDB with the Clang++ v10 compiler. #46859
• Release Docker images are now built on Debian 9.12. #49593

Was this helpful?