# What's New in v20.2

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

### Contributors

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

- Jane Xing

## 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 v20.1.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

### Contributors

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

- joesankey (first-time contributor)

## 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
- `BACKUP`s 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

### Contributors

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

- Max Neverov
- Mohammad Aziz (first-time contributor)

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

### Contributors

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

- Michał Łazowik (first-time contributor)

## 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 v20.1.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

### Contributors

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

- Rupesh Harode (first-time contributor)

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

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

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

### Contributors

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

- Tharun (first-time contributor)

## v20.2.6

Release Date: March 15, 2021

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

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

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

### 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 `ENUM`s 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

### Contributors

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

- Cheng Jing (first-time contributor)

## v20.2.4

Release Date: January 21, 2021

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

### 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 `EXPLAIN`ing 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 `JOIN`s 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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

### 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 `UPDATE`s and `DELETE`s. 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

### Contributors

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

- Joshua M. Clulow (first-time contributor)

## v20.2.2

Release Date: November 25, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

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

Metric | Description
--- | ---
`jobs.{job_type}.currently_running` | Number of `{job_type}` jobs currently running in Resume or OnFailOrCancel state
`jobs.{job_type}.fail_or_cancel_completed` | Number of `{job_type}` jobs which successfully completed their failure or cancellation process
`jobs.{job_type}.fail_or_cancel_failed` | Number of `{job_type}` jobs which failed with a non-retryable error on their failure or cancellation process
`jobs.{job_type}.fail_or_cancel_retry_error` | Number of `{job_type}` jobs which failed with a retryable error on their failure or cancellation process
`jobs.{job_type}.resume_completed` | Number of `{job_type}` jobs which successfully resumed to completion
`jobs.{job_type}.resume_failed` | Number of `{job_type}` jobs which failed with a non-retryable error
`jobs.{job_type}.resume_retry_error` | Number of `{job_type}` jobs which failed with a retryable error

#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 `CHANGEFEED`s 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 `BACKUP`s. #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 `IMPORT`s 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 `INSERT`s 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 `IMPORT`s 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

### Contributors

This release includes 88 merged PRs by 26 authors.

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

- Max Neverov (first-time contributor)

## v20.2.1

Release Date: November 20, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

### 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.

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.2.0 - v20.2.7. If a backup coincides with an in-progress index creation (backfill), `RESTORE`, or `IMPORT`, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.2.8 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

### 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.

Note:

"Core" features are freely available in the core version and do not require an enterprise license. "Enterprise" features require an enterprise license. CockroachCloud clusters include all enterprise features.

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

#### SQL

Version | Feature | Description
--- | --- | ---
Core | Third-Party Tool Support | CockroachDB now fully supports several additional third-party database tools, including Spring Boot, Hibernate, and ActiveRecord.
Core | Spatial Support | CockroachDB now supports spatial data types, spatial indexes, and spatial functions, as well as the ability to migrate spatial data from various formats such as Shapefiles, GeoJSON, GeoPackages, and OpenStreetMap.
Core | User-Defined Schemas | You can now create user-defined logical schemas, as well alter user-defined schemas, drop user-defined schemas, and convert databases to user-defined schemas.
Core | Partial Indexes | You can now use partial indexes to specify a subset of rows and columns in a table that evaluate to true on a `WHERE` filter defined at index creation.
Core | `ENUM` data types | CockroachDB now supports the creation and management of user-defined `ENUM` data types consisting of sets of enumerated, static values.
Core | Materialized Views | CockroachDB now supports materialized views, or views that store their selection query results on-disk.
Core | View Replacement | CockroachDB now supports replacing an existing view with the `CREATE OR REPLACE VIEW` syntax.
Core | Foreign Key Performance and Compatibility | When adding the `FOREIGN KEY` constraint, it is no longer required to have an index on the referencing columns.
Core | `EXPLAIN` Improvements | The response of the `EXPLAIN` statement now includes the estimated number of rows scanned by the query as well as other usability improvements.
Core | Disallowing Full Table Scans | You can use the new `disallow_full_table_scans` session variable to disallow full table and secondary index scans.
Core | Altering Column Data Types | You can now alter the data type of table column. Note that this feature is experimental and is subject to change.

#### Recovery and I/O

Version | Feature | Description
--- | --- | ---
Core | Backup/Restore in Core Version | The core version of CockroachDB now lets you perform full cluster backups, all restore options, as well as bulk exports. Incremental backups, locality-aware backups, and other advanced backup functionality continue to require an enterprise license.
Core | Backup Scheduling | You can now create schedules for CockroachDB backups, as well as view, pause, resume, and drop backup schedules. Once a scheduled backup is created, you can use `SHOW SCHEDULE` to inspect the schedule status and any errors and then use `SHOW BACKUPS IN` and `SHOW BACKUP` to inspect the details of individual backups. Note that incremental backups, locality-aware backups, and other advanced backup functionality require an enterprise license.
Core | Import with User-Scoped Storage | In addition to supporting bulk imports from cloud storage, CockroachDB now lets you upload CSV files from your local machine to user-scoped file storage in your cluster. Once uploaded, a userfile can be reference by the `IMPORT` command to import data into a table. Userfiles can also be listed and deleted via CLI commands.
Core | Import with Default Expressions | You can now use `IMPORT INTO` to import supported `DEFAULT` expressions as well as computed columns.
Enterprise | KMS Support for Encrypted Backups | You can now use AWS Key Management Service (KMS) to encrypt the files that full or incremental backups generate.

#### Deployment and Operations

Version | Feature | Description
--- | --- | ---
Core | Kubernetes Operator | The CockroachDB Kubernetes Operator eases deployment of secure CockroachDB clusters on Kubernetes. The Operator can be used to create StatefulSets, authenticate pods, scale CockroachDB clusters, and perform rolling upgrades. The Operator is in beta and is not yet production-ready.
Core | Log Redaction | When gathering log files via the `cockroach debug zip` or `cockroach debug merge-logs` command, you can use the new `--redact-logs` flag to redact sensitive data. Note that this flag removes sensitive information only from the log files; other items collected by the `debug zip` command may still contain sensitive information.
Core | Certificate Revocation with OCSP | CockroachDB now supports certificate revocation for custom CA certificate setups running an OCSP server.
Enterprise | SSO in the DB Console | The DB Console now supports single sign-on (SSO) via OpenID Connect (OIDC), an authentication layer built on top of OAuth 2.0. When SSO is configured and enabled, the DB Console login page displays an OAuth login button in addition to the password access option. Note that this feature is experimental and is subject to change.
Core | Permission-Based Object Ownership | All database objects now have owners. By default, the user who created an object is the owner of the object and has all privileges on the object. Any roles that are members of the `owner` role have all privileges on the objects the role owns. The admin is the default owner for all non-system objects without owners. System objects without owners have `node` as their owner.
Core | Fine-Grained SQL Privileges | CockroachDB now allows you to grant users administrative abilities without giving them full admin access.

#### Observability

Version | Feature | Description
--- | --- | ---
Core | Transaction Details | The new Transactions page of the DB Console shows you details about all client-initiated transactions in the cluster that help you identify and troubleshoot frequently retried and high-latency transactions.
Core | Sessions Details | The new Sessions page of the DB Console shows you details about all active and idle sessions in the cluster, with session age, memory usage, SQL statement, and other details available for active sessions.

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

Area | Topic | Description
--- | --- | ---
Training | Online Course for Python Developers | Launched a new self-paced course on Cockroach University, CockroachDB for Python Developers. This course walks you through building a full-stack vehicle-sharing app in Python using the popular SQLAlchemy ORM and a free CockroachCloud cluster as the back-end.
Docs | Interactive In-Browser Tutorials | Added tutorials that can be completed entirely in your browser, without downloads or installations, from Learning CockroachDB SQL to Storing and Querying JSON.
Docs | Transaction Retry Error Reference | Documented the various errors that developers encounter around transaction retries in CockroachDB, explaining why each error happens and what to do about it.
Docs | Disaster Recovery | Documented how to plan for and recover from various types of disasters, from hardware failure, to data failure, to compromised security keys.
Docs | Batch Deletes | Added guidance on performing large deletes across various scenarios.
Docs | Multi-Region Kubernetes on EKS | Added a tutorial on orchestrating a secure CockroachDB multi-region deployment on Amazon EKS.

## v20.2.0-rc.4

Release Date: November 3, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

### 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 v20.1 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 v20.1 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

### Contributors

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

- Azdim Zul Fahmi (first-time contributor)
- Erik Grinaker

## v20.2.0-beta.4

Release Date: October 6, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

### 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 `CHANGEFEED`s 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

### Contributors

This release includes 62 merged PRs by 24 authors.

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

- Erik Grinaker

## v20.2.0-beta.2

Release Date: September 25, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

### 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, `RESTORE`s 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

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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
- `BACKUP`s 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 `GRANT`ing 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 `FILTER`ing 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 v20.1.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

### Contributors

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

- Cyrus Javan (first-time contributor)
- Deven Bhooshan (first-time contributor)
- Erik Grinaker (first-time contributor)
- Michael Meng (first-time contributor)
- Themis Papavasileiou (first-time contributor)
- Vincent Xiao
- himanshuchawla009

## v20.2.0-alpha.3

Release Date: August 25, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

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

Note:

For instructions showing how to get started with CockroachDB Spatial, see Working with Spatial Data.

- 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

### Contributors

This release includes 634 merged PRs by 59 authors.

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

- Andrii Vorobiov
- Eugene Kalinin (first-time contributor)
- Juan Carlos (first-time contributor)
- Vaibhav (first-time contributor)
- Vincent Xiao (first-time contributor)
- Yongyang Lai
- dujin (first-time contributor)
- himanshuchawla009 (first-time contributor)
- manhhiep92 (first-time contributor)
- xuhui-lu (first-time contributor)

## v20.2.0-alpha.2

Release Date: July 27, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

### 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 `Int`s and `Interval`s. #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

### Contributors

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

- Andrii Vorobiov
- Artem Barger
- Arun Ranganathan (first-time contributor)
- George Papadrosou
- Jaewan Park
- John Kendall (first-time contributor, CockroachDB team member)
- Jordan Ryan Reuter (first-time contributor, CockroachDB team member)
- Madeline Liao (first-time contributor, CockroachDB team member)
- Robert S Lee (first-time contributor)
- Ryan Min (first-time contributor, CockroachDB team member)
- Tancredo Souza (first-time contributor)
- Yongyang Lai
- abhishek20123g
- gorjunov (first-time contributor)
- jieniu$ (first-time contributor)

## v20.2.0-alpha.1

Release Date: June 17, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.2.0 - v20.2.3 due to a bug in protobuf. This is resolved in CockroachDB v20.2.4 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

### 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 `REINDEX`ing 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 `ENUM`s). #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 `BACKUP`s 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 `INTERVAL`s or `TIMESTAMP`s 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 `ADD`or `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 `CHANGEFEED`s 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, v20.1.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 `IMPORT`s and `RESTORE`s 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

### Contributors

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

- Amit Sadaphule
- Andrii Vorobiov
- Anthony Huang (first-time contributor)
- Artem Barger
- BurtonQin (first-time contributor)
- Girish Ramnani (first-time contributor)
- Helen He (first-time contributor)
- Jaewan Park
- Jaime Soriano Pastor (first-time contributor)
- Petr Jediný (first-time contributor)
- Richard Tweed (first-time contributor)
- Yongyang Lai (first-time contributor)
- abhishek20123g
- lancerutkin (first-time contributor)