# What's New in v20.1

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

- For key feature enhancements in v20.1 and other upgrade considerations, refer to the notes for v20.1.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.1.17

Release Date: May 17, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

### 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. #64604
- Fixed a bug where users of OSS builds of CockroachDB would see "Page Not Found" when loading the DB Console. #64126

### Contributors

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

- Joshua M. Clulow (first-time contributor)

## v20.1.16

Release Date: May 10, 2021

This page lists additions and changes in version v20.1.16 since version v20.1.15.

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

### Bug fixes

- Fixed a correctness bug which caused partitioned index scans to omit rows where the value of the first index column was `NULL`. This bug was present since v19.2.0. #64050
- Fixed a bug where multiple concurrent invocations of `cockroach debug zip` could lead to cluster instability. This bug was present since CockroachDB v20.1. #64086
- 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. #64347
- 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. #64377
- Fixed a bug where encryption-at-rest metadata was not synced and might become corrupted during a hard reset. #64498

### Performance improvements

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

### Contributors

This release includes 9 merged PRs by 10 authors.

## v20.1.15

Release Date: April 26, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

### Bug fixes

- Fixed a bug where incremental cluster backups may have missed data written to tables while they were `OFFLINE`. In practice this can happen if a `RESTORE` or `IMPORT` was running across incremental backups. #63494
- Fixed a bug where cluster restore would sometimes (very rarely) fail after retrying. #63773
- Fixed a bug where some of the writes of the jobs while they were running may have been missed by the backup. `IMPORT` and `RESTORE` jobs are now restored as reverting so that they cleanup after themselves. #63773
- Fixed a rare issue that caused replica divergence. When it occurred, it was reported by the replica consistency checker, typically within 24 hours of occurrence, which would terminate the nodes. #63475

### Contributors

This release includes 7 merged PRs by 6 authors.

## v20.1.14

Release Date: April 19, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

### Bug fixes

- Fixed a bug where some import failures would cause tables to stay `OFFLINE` when they should have been brought back to `PUBLIC`. #61481
- Fixed a bug where an invalid tuple comparison using `ANY` was causing an internal error. CockroachDB now returns "unsupported comparison operator". #61725
- Changed the behavior of the `kv.closed_timestamp.target_duration` cluster setting when set to 0. This would make follower reads more aggressive instead of disabling them. Setting `kv.closed_timestamp.target_duration` to 0 will now disable routing requests to follower replicas. #62442
- Fixed a bug where `RESTORE` using `AS OF SYSTEM TIME` of tables that included foreign key constraints from backups created by v19.x or earlier would lead to malformed schema metadata. #62493
- Fixed an internal error that could occur during planning when a query used the output of the `RETURNING` clause of an `UPDATE`, 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). #62964
- 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 v20.1. #62881
- Fixed a bug where index backfill data might have been missed by `BACKUP` in incremental backups. #63303

### Performance improvements

- SQL statistics collection has been made more efficient by avoiding an accidental heap allocation per row for some schemas. #58199

### Contributors

This release includes 16 merged PRs by 13 authors.

## v20.1.13

Release Date: March 15, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Bug fixes

- Previously, if `RELEASE SAVEPOINT cockroach_restart` was followed by `ROLLBACK`, the `sql.txn.rollback.count` metric would be incremented. This was incorrect, because the transaction had already committed. The metric is no longer incremented in this case. #60251
- Fixed a bug where an error in protecting a record could be incorrectly reported, preventing some backups of very large tables from succeeding. #60961
- Fixed a bug where high-latency global clusters could sometimes fall behind in resolving timestamps for changefeeds. #60926
- Creating interleaved partitioned indexes is now disallowed. Previously, the database would crash when trying to create one. Note that interleaved tables will be deprecated altogether in a future release. #61423
- In the Advanced Debugging section of the Admin UI (DB Console), manually enqueueing a range to the garbage collection (GC) queue now properly respects the `SkipShouldQueue` option. This ensures that you can force the GC of a specific range. #60746

### Contributors

This release includes 10 merged PRs by 10 authors.

## v20.1.12

Release Date: February 16, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Bug fixes

- Fixed a bug in URL handling of HTTP external storage paths on Windows. #59268
- Fixed a bug where CockroachDB could encounter an internal error when executing queries with `BYTES` or `STRING` types via the vectorized engine. #59257
- Fixed a bug where CockroachDB could crash when executing an `ALTER INDEX ... SPLIT/UNSPLIT AT` statement when more values are provided than are explicitly specified in the index. #59272

### Contributors

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

- Cheng Jing (first-time contributor)

## v20.1.11

Release Date: January 25, 2021

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### SQL language changes

- Fixed a bug in `RESTORE` where some unusual range boundaries in interleaved tables caused an error. #58260

### Bug fixes

- In v20.1.8, we attempted to fix `age`'s lack of normalization of `H:M:S` into the years, months and days field. However, this was also broken for values greater than 1 month, as well as breaking `a::timestamp(tz) - b::timestamp(tz)` operators. This has now been resolved. #57956
- Fixed an assertion error caused by some DDL statements used in conjunction with common table expressions (`WITH`). #57952
- Fixed a bug that caused temp tables to not be cleaned up after the associated session was closed. #58167
- Added a safeguard against crashes while running `SHOW STATISTICS USING JSON`, which is used internally for statement diagnostics, and `EXPLAIN ANALYZE (DEBUG)`. #58264
- Previously, CockroachDB could return non-deterministic output when querying the `information_schema.statistics` virtual table (internally used by the `SHOW INDEXES` command)—namely, the implicit columns of the secondary indexes could be in arbitrary order. This is now fixed, and the columns will be in the same order as they are in the primary index. #58215
- Previously, CockroachDB could crash when performing a `DELETE` operation after an alteration of the primary key when in some cases, and now it is fixed. The bug was introduced in v20.1. #58267
- Fixed a panic in protobuf decoding. #58861
- Fixed a bug that caused errors when accessing a tuple column (`tuple.column` syntax) of a tuples that could be statically determined to be null. #58899
- Fixed an internal error involving string literals used as arrays. #59066
- GC jobs now populate the `running_status` column for `SHOW JOBS`. This bug has been present since v20.1. #59138
- 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 index. This scenario could cause the rows that did not conflict to be erroneously discarded. This is now fixed. #59172

### Contributors

This release includes 24 merged PRs by 17 authors.

## v20.1.10

Release Date: December 21, 2020

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1.

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Backward-incompatible changes

- The file names for heap profile dumps are now `memprof.<date-and-time>.<heapsize>`, where previously they were named `memprof.<heapsize>.<date-and-time>`. #55260

### SQL language changes

- Fixed a bug where temporary tables may be included in `BACKUP` commands. #56475

### Command-line changes

- CockroachDB now better attempts to retain heap profile dumps after a crash due to an out-of-memory error. #55260
- CockroachDB now better attempts to retain memory statistics corresponding to increases in total memory usage, not just heap allocations. #55260

### Bug fixes

- 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. #56515
- The file names for generated `goroutine`, CPU and memory profiles were sometimes incorrect, resulting in repeated warnings like `strconv.ParseUint: parsing "txt": invalid syntax` in log files. This has been corrected. #55260
- Fixed a bug when the Pebble storage engine is 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. #56680
- Previously, dumps of tables with a `BIT` type column would result in an error. This column type is now supported. #56452
- In 20.1.8, we attempted to fix `age`'s lack of normalization of `H:M:S` into the years, months, and days field. However, this was also broken for values greater than 1 month, as well as breaking `a::timestamp(tz) - b::timestamp(tz)` operators. This has now been resolved. #56769
- CockroachDB previously would crash when executing a query with an `AS OF SYSTEM TIME` clause that used a placeholder (note that it wasn't a prepared statement, it was an attempt to use an unspecified placeholder value on a non-prepared statement). This is now fixed. #56781
- CockroachDB previously could encounter an internal error when `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` values that used year 1 BC were sent between nodes for execution. Additionally, previously it was not possible to specify `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` values with year 1 BC without using AD/BC notation. This is now fixed. #56743
- Fixed internal error when collecting a statement diagnostic bundle in some cases where the query hits an error. #56785
- Some boolean session variables would only accept string (`"true"` or `"false"`) values. Now they also accept unquoted true or false values. #56814
- Fixed a bug which would prevent the dropping of hash sharded indexes if they were added prior to other columns. #55823
- Fixed a race condition in the `tpcc` workload with the `--scatter` flag where tables could be scattered multiple times or not at all. #56979
- Previously if a cluster backup that was taken during a schema change, a cluster restore of that backup would create duplicates of the ongoing schema changes. #56450
- Fixed a case where attempting to start a second `BACKUP` to the same location while the first was running using passphrase-based encryption could overwrite the metadata required to decrypt it and thus render it unreadable. #57025
- Fixed an internal error when using aggregates and window functions in an `ORDER BY` for a `UNION or VALUES` clause. #57522
- The `CREATE TEMP TABLE AS` statement previously created a non-temporary table. Now it makes a temporary one. #57550
- Fixed a bug where schema change jobs to add foreign keys to existing tables, via `ALTER TABLE`, could sometimes not be successfully reverted (either due to being canceled or having failed). #57810
- Fixed 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. #57810
- Fixed a bug which can occur when canceling schema changes when there are multiple queued schema changes that could result in future schema changes being stuck. #55058
- Fixed a bug which can lead to canceled schema change jobs ending in the failed rather than canceled state. #55058

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

### Backward-compatible change

- The reserved, non-documented cluster settings `server.heap_profile.xxx` have been renamed to `server.mem_profile.xxx`. They now control collection of multiple sorts of memory profiles besides just Go heap allocations. #55260

### Contributors

This release includes 34 merged PRs by 16 authors.

## v20.1.9

Release Date: December 1, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Enterprise edition changes

- Added a setting to opt in or out of including SQL statistics in backups. #55879

### SQL language changes

- Parsing intervals with fractional years now produces intervals with no more precision than months, to match the behavior of PostgreSQL. #56247
- Table names are now listed before index names in `EXPLAIN (DISTSQL)` diagram output. Previously, the diagrams used `index@table`, and now they use `table@index`. #56396

### Bug fixes

- Changefeeds were previously incompatible with the vectorized execution engine, and creating changefeeds with the vectorized engine enabled could cause a server to hang. This could happen in v20.2 releases with `SET vectorize_row_count_threshold=0;`, and in v20.1 releases with `SET vectorize=on`. This bug is now fixed. #55754
- Fixed 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. #55874
- CockroachDB previously could return incorrect results when computing aggregation functions when some of the functions contained a `DISTINCT` clause and some did not. This bug is now fixed. #55873
- CockroachDB previously could incorrectly evaluate the `sqrdiff` function when used as a window function in some cases. This bug is now fixed. #55999
- Fixed a `top-level relational expression cannot have outer columns` error in some queries that involve `WITH`. #56086
- Fix a bug causing `IMPORT`s of malformed Avro records to hang forever. #56097
- Fixed a bug causing CockroachDB to crash when a `BACKUP` query was unable to count the total nodes in the cluster. #56096
- Fixed an error that could occur at the end of a restoration of a backup that had ongoing schema change jobs. #56021
- Previously, cluster backups created in releases before v20.2 could not be restored in 20.2 clusters, and would produce an error message about failing to restore a system table. This bug is now fixed. #56024
- Options set on users (e.g., `ALTER USER <username> CREATEDB`) were not included in cluster backups and thus not restored. Role options are now included in cluster backups. #55442
- Fixed a bug that did not respect disabling protected timestamp settings with zero values. #56454
- Fixed a bug which that result in a failed restore when restoring into a database with a different set of privileges than the backup privileges. #55880
- Fixed a race between job completion and sending the result of the job to the client. CockroachDB now sends results to the client after a job completes. #56146
- In v20.1.8, we attempted to fix the `age()` function's normalization of `H:M:S` input into years, months, and days. However, the v20.1.8 fix was broken for values greater than 1 month, and for `a::timestamp(tz) - b::timestamp(tz)` expressions. This bug has been resolved. commit 59b2bc218

### Contributors

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

- Max Neverov (first-time contributor)

## v20.1.8

Release Date: October 21, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Bug fixes

- Fixed a bug where schema changes, which affect referenced tables, might not have propagated to other nodes. #55375
- Fixed a bug where inscrutable errors were returned on failed backup creation. #54968
- Fixed a bug where CockroachDB crashed when executing a query via the vectorized engine when most of the SQL memory (determined via `--max-sql-memory` startup argument) had already been reserved. #55458
- Fixed a bug where the `age()` function did not normalize the duration for large day or H:M:S values in the same way PostgreSQL does. #55527
- Fixed a bug where CockroachDB did not account for all the memory used by the vectorized hash aggregation which could lead to an OOM crash. #55571
- Fixed a bug where using the `MIN`/`MAX` aggregates in a prepared statement did not report the correct data type size. #55621

### Contributors

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

- kev (first-time contributor)

## v20.1.7

Release Date: October 12, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Security updates

- Fixed a case where connections to Google Cloud storage would ignore the `--external-io-disable-implicit-credentials` flag to `cockroach start`. #55091

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

### Bug fixes

- Fixed a bug where columns used in an index which contained the columns of a foreign key as a prefix could lead to all of the index columns being set to NULL or the default value on cascade. #54543
- 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. #54282
- Fixed a rare bug which can lead to index backfills failing in the face of transaction restarts. #54859
- Fixed a race condition propagating post-query metadata in the vectorized execution engine. #55168
- Fixed a bug causing nodes running version 20.1 to not be able to serve follower reads in mixed-version clusters running versions 19.2 and 20.1. #55089
- The first timing column in the trace.txt file collected as part of a statement diagnostics bundle has been fixed.

### Contributors

This release includes 19 merged PRs by 8 authors.

## v20.1.6

Release Date: September 24, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### SQL language changes

- The concurrency of the evaluation of `UNION ALL` queries has been reduced. Previously, such queries could crash a server (in extreme cases, due to memory shortage). That bug is now fixed, at the expense of possible minor reduction in performance. #53444

### Bug fixes

- The cluster Node Map and the debug page for cluster locality reports are now again available to non-admin users. #53331
- Previously, CockroachDB could return incorrect results when performing `LEFT ANTI` hash joins when the right equality columns formed a key and the query was executed with the vectorized engine. This bug has been fixed. #53346
- Fixed a rare internal error related to foreign key checks. #53648
- CockroachDB could previously crash when evaluating queries containing window functions with the `GROUPS` framing mode when `<offset> FOLLOWING` boundary was used and the offset was a very large value such that it could result in an integer overflow. This is now fixed. #53755
- Fixed the "no binding for WithID" internal error when using `WITH RECURSIVE` in queries with placeholders. #54037
- 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. #53561
- Fixed a bug that could cause garbage collection jobs for tables dropped as part of a `DROP DATABASE CASCADE` to never complete. #54129
- Fixed a bug that caused a crash when using a `RANGE`-mode window function with an offset (e.g, `OVER (PARTITION BY b ORDER BY a RANGE 1 PRECEDING)`). #54075
- Fixed a bug that could cause the asynchronous migration to upgrade jobs from v19.2 to fail to complete and keep retrying indefinitely upon encountering a dropped database where some, but not all, of the tables have already been cleaned up. This bug can only occur if an upgrade to v20.1 happened while a database was in the process of being dropped or a set of tables was being truncated. #51176
- Asynchronous schema change migrations now mark a job as failed instead of retrying indefinitely when a descriptor referenced by a schema change job does not exist. #51176
- Fixed a potential race condition in the schema change job migration from v19.2 that could cause spurious errors and retries due to the wrong transaction being used internally. #51176
- Fixed a bug that allowed new types to be used in an array type during a version upgrade. #53962
- Database creation and deletion was previously not correctly tracked by `revision_history` cluster backups. This is now fixed. #53806
- Fixed two bugs that caused CockroachDB to return errors when attempting to add constraints in the same transaction in which the table was created:
  1. Adding a `NOT NULL` constraint no longer fails with the error `check ... does not exist`.
  2. Adding a `NOT VALID` foreign key constraint no longer fails with the internal error `table descriptor is not valid: duplicate constraint name`. #54288
- Fixed a bug that could lead to out-of-memory errors when dropping large numbers of tables at a high frequency. #54285
- CockroachDB could previously crash in rare circumstances when many queries running in the cluster were consuming a lot of memory and at least one query was running through the vectorized execution engine. This is now fixed. #54406
- In releases v20.1.4 and v20.1.5, CockroachDB might finish `UPSERT` operations too early. A simple `UPSERT` would correctly insert up to 10,000 rows and then ignore the rest. An `UPSERT` with a `RETURNING` clause would process up to 10,000 rows but return no rows. For more information, see Technical Advisory 54418. #54418

### Contributors

This release includes 27 merged PRs by 15 authors.

## v20.1.5

Release Date: August 31, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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:

CockroachDB introduced a critical bug in the v20.1.4 release that affects `UPSERT` and `INSERT … ON CONFLICT DO UPDATE SET x = excluded.x` statements involving more than 10,000 rows. All deployments running CockroachDB v20.1.4 and v20.1.5 are affected. A fix is included in v20.1.6.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

For more information, see Technical Advisory 54418.

### SQL language changes

- Reduced memory used by table scans containing JSON data. #53318

### Bug fixes

- 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". #52142
- Previously, subtracting months from a `TIMESTAMP`/`DATE`/`TIMESTAMPTZ` whose date value is greater than 28 could subtract an additional year. This bug is now fixed. #52156
- Previously, CockroachDB could return incorrect results on queries that encountered `ReadWithinUncertaintyInterval` errors. This bug is now fixed. #52045
- Fixed instances of slow plans for prepared queries involving CTEs or foreign key checks. #52205
- Large write requests no longer have a chance of erroneously throwing a "transaction with sequence has a different value" error. #52267
- Type OIDs in the result metadata were incorrect for the `bit`, `bpchar`, `char(n)`, and `varchar(n)` types, and the corresponding array types. They are now correct. #52351
- CockroachDB now prevents deadlocks on connection close with an open user transaction and temporary tables. #52326
- Fixed a bug that could prevent schema changes for up to 5 minutes when using the `COPY` protocol. #52455
- Executing a large number of statements in a transaction without committing could previously crash a CockroachDB server. This bug is now fixed. #52402
- Fixed a bug causing the temporary object cleaner to get stuck trying to remove objects that it mistakenly thought were temporary. Note that no persistent data was deleted. The temporary cleaner simply returned an error because it thought certain persistent data was temporary. #52662
- Previously, CockroachDB would erroneously restart the execution of empty, unclosed portals after they had been fully exhausted. This bug is now fixed. #52443
- Fixed a bug causing the Google Cloud API client used by `BACKUP`, `RESTORE` and `IMPORT` to leak memory when interacting with Google Cloud Storage. #53229
- CockroachDB no longer displays a value for `gc.ttlseconds` if not set. #52813

### Performance improvements

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

### Contributors

This release includes 31 merged PRs by 15 authors.

## v20.1.4

Release Date: August 3, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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:

CockroachDB introduced a critical bug in the v20.1.4 release that affects `UPSERT` and `INSERT … ON CONFLICT DO UPDATE SET x = excluded.x` statements involving more than 10,000 rows. All deployments running CockroachDB v20.1.4 and v20.1.5 are affected. A fix is included in v20.1.6.

For more information, see Technical Advisory 54418.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### General changes

- Links in error messages that point to unimplemented issues now use the Cockroach Labs redirect/short-link server. #50310
- Schema changes are now logged in greater detail. #50373

### Enterprise edition changes

- `RESTORE` now 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, a bug causing ownership relationships to not be remapped after a restore has been fixed. #51629

### SQL language changes

- CockroachDB no longer writes to slow query log unless explicitly enabled. #50941

### Command-line changes

- The new `statement-diag` `cockroach` command can now be used to manage statement diagnostics. #51229
- The `statement-diag` command now shows all times in UTC. #51457

### Bug fixes

- 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 if a table descriptor that has already been deleted by a different GC job is not found. #50556
- Previously, if a full cluster restore failed while restoring the system table data, it would not clean up after itself properly and would leave some temporary tables public and not dropped. This bug has been fixed. #50209
- Fixed a bug causing a cluster restore to fail when the largest descriptor in the backup was a database. This was typically seen when the last action in backing up a cluster was a database creation. #50817
- Cluster backup would previously appear as `BACKUP TABLE TO` rather than `BACKUP TO` in the jobs table. This bug has been fixed. #50818
- Fixed a bug causing a badly timed power outage or a system crash to report an error upon process restart. #50847
- Some `pg_catalog` queries that previously returned an error like "`crdb_internal_vtable_pk` column not allowed" now work again. #50843
- Fixed "column not in input" internal error in some corner cases. #50859
- Fixed a rare bug causing a multi-range `SELECT FOR UPDATE` statement containing an `IN` clause to fail to observe a consistent snapshot and violate serializability. #50816
- Fixed regression where granting privileges and dropping objects would be slow when performed on a large number of objects due to unnecessary queries for looking up jobs in the `system.jobs` table. Previously, CockroachDB executed a quadratic number of queries based on the number of objects. CockroachDB now executes a linear number of queries based on the number of objects, which significantly improves the speed of dropping multiple objects or granting multiple privileges to a user. #50923
- Previously, CockroachDB could crash when internal memory accounting hit a discrepancy. Now it will report an error instead. #51014
- Improved support for large statement diagnostic bundles. #51031
- CockroachDB now prevents spurious "SimpleQuery not allowed while in extended protocol mode" errors. #51249
- Renaming a temporary table no longer converts it to a persistent table. The table continues to remain temporary after a rename. This patch also prevents users from converting a temporary table to a persistent table by renaming the table with a fully-qualified name and a schema referring to `public`. #51309
- Fixed incorrect results in some cases involving joins on interleaved tables with limits. #51432
- `cockroach dump` no longer errors out 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. #51457
- Fix a bug causing `cockroach dump` to improperly escape quotes within table comments. #51510
- Fix a bug causing `cockroach dump` to not emit a correct statement for comments on indexes. #51510
- There is a known issue where `BACKUP`s may get stuck when nearly completed. When this happens, we prevent garbage collection of old data from the targets that are being backed up, until the job is cancelled. This change stops the garbage build-up while the `BACKUP` is stuck. #51519
- Previously, CockroachDB could hit an internal error when executing `regexp_replace` builtin. This bug has been fixed. #51347
- Previously, CockroachDB could hit a "command is too large" error when performing `UPSERT` operations with many values. This bug has been fixed. #51626
- Fixed a bug that prevented a table from being dropped if a user created a sequence owned by the table's column and then dropped the sequence. #51629
- `DROP DATABASE CASCADE` now works as expected even when the database has a sequence with an owner in it. #51629
- Fixed a bug causing descriptors to be in an invalid state due to ownership issues. #51629
- Previously, orphaned `system.namespace/system.descriptor` entries were left if a `DROP DATABASE CASCADE` was issued, and the database contained dependency relations. For example, if the database included a view that depended on a table in the database, dropping the database would result in an orphaned entry for the view. This bug is now fixed, and cleanup happens as expected. #51895
- CockroachDB now returns proper error messages for index creation statements that use a column that does not exist. #51892
- Fixed a bug preventing `NULL` index members from being added to hash-sharded indexes. #51906
- In earlier testing releases, columns that 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. #51906
- 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". #51869
- Fixed bug in the Pebble storage engine that in rare circumstances could construct corrupted store, resulting in a node crash. #51915
- Fixed a bug causing traces collected through the `sql.trace.txn.enable_threshold` setting to be incomplete sometimes. #51845
- Increased the robustness of restore against descriptors which may be in an unexpected state. #51925
- Previously, CockroachDB could encounter benign internal "context canceled" errors when queries were executed by the vectorized engine. #51933
- Fixed a bug causing `BACKUP` jobs to block when finished backing up data. #52003
- Fixed a bug causing `RESTORE` jobs to sometimes block at the end of the job when sending its results back if the connection that started the job disconnected. #52003
- Fixed a bug causing CockroachDB to crash on some queries with merge joins. #52046
- An unknown condition previously caused CockroachDB to crash 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. #51843

### Performance improvements

- Introduced a new `server.consistency_check.max_rate` setting, expressed in bytes/seconds, 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, in the 10TB+ range. #50066

### Contributors

This release includes 41 merged PRs by 18 authors.

## v20.1.3

Release Date: June 29, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Security updates

- HTTP endpoints beginning with `/debug/` now require a valid `admin` login session. #50487

### Enterprise edition changes

- Full cluster restore is now more resilient to transient transaction retry errors during restore. #50004
- 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. #50251

### SQL language changes

- Previously, using `infinity` evaluated to a negative, i.e., "-292277022365-05-08T08:17:07Z". This has been fixed to be the maximum supported timestamp in PostgreSQL that is not infinity. Likewise, `-infinity` is the smallest supported value. Note this does currently does not behave exactly like `infinity` in PostgreSQL (this is a work in progress and may be resolved later). #50365

### Bug fixes

- Previously, `extract(epoch from timestamptz)` from a session time zone not in UTC would return a value which was incorrectly offset by the session time zone. This is now fixed. #50075
- Previously, the parallel importer could get stuck due to a race between emitted import batches and checking for context cancellation (either due to an unforeseen error, or due to explicit context cancallation). This is now fixed. #50089
- Previously, using separate groups for producer and consumer could lead to a situation where consumer would exit (due to an error, or explicit context cancellation) without the producer realizing, leading to a deadlock. Producer and consumer are now correctly linked during data import. #50089
- Casting to width-limited strings now works correctly for strings containing Unicode characters. #50159
- Fixed some cases in which casting a string to a width-limited string array was not truncating the string. #50168
- Fixed a bug in which restarting CockroachDB with the Pebble storage engine after a crash during write-ahead logging could, in some rare cases, return an "unexpected EOF" error. #50282
- Previously, the Admin UI Statements page was incorrectly displaying information about whether or not a statement was distributed (it was always `Yes`). This is now fixed. #50347
- Fixed a RocksDB bug that could result in inconsistencies in rare circumstances. #50397
- Fixed a bug that broke the data distribution Advanced Debug page in the Admin UI on clusters that had upgraded from 19.2 to 20.1. #49987
- Previously, when a changefeed would fail to set up its flows due to a node draining, the changefeed would be marked as failed. These errors are now retryable. #50088

### Performance improvements

- CockroachDB now optimizes reading of files when doing backups and storage-level compactions of files. This should deliver a performance improvement for some read-heavy operations on an IOPS-constrained device. #50105
- Limited `SELECT` statements now do a better job avoiding unnecessary contention with `UPDATE` and `SELECT FOR UPDATE` statements. #50119
- 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. #50470

### Build changes

- Release Docker images are now built on Debian 9.12. #50482

### Doc updates

- Updated guidance on node decommissioning. #7304
- Added node density guidance to the Production Checklist. #7514
- Renamed "whitelist/blacklist" terminology to "allowlist/blocklist". #7535
- Updated the Releases navigation in the sidebar to expose the latest Production and Testing releases. #7550
- Fixed scrollbar visibility on Chrome. #7487

### Contributors

This release includes 22 merged PRs by 15 authors.

## v20.1.2

Release Date: June 17, 2020

This page lists additions and changes in v20.1.2 since v20.1.1.

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Bug fixes

- Corrected the replicas count for table details in the Admin UI. #49206
- The `rolcanlogin` value for roles is now correctly populated in `pg_roles` and `pg_catalog`. #49622
- Fixed a rare bug in the Pebble storage engine that could lead to storage engine inconsistencies. #49378
- Corrected how engine type is reported in bug reports when using `cockroach demo`. #49377
- Fixed a bug where `cockroach quit` would not proceed to perform a hard shutdown when the value passed to `--drain-wait` was very small, but non-zero. This bug existed since v19.1.9, v19.2.7 and v20.1.1. #49363
- Fixed a bug where `demo node restart` would not work due to an invalid certificate directory. #49390
- Fixed some benign errors that were being reported as unexpected internal errors by the vectorized execution engine. #49534
- Fixed a rare bug in the Pebble storage engine where keys were being returned out-of-order from large sstable files. #49602
- When run via the vectorized execution engine, queries with a hash routed in the DistSQL plan no longer return an internal error or incorrect results. #49624
- When run via the vectorized execution engine, queries that have columns of the `BYTES` type in the output no longer result in an internal error. #49384
- CockroachDB no longer leaks file descriptors during GSS authentication. #49614
- Attempting to perform a full cluster `RESTORE` on a backup that did not contain any user data no longer fails. #49745
- 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. #49835
- Fixed the descriptions for `--socket-dir` and `--socket` in the CLI help. They were incorrect since v20.1.0. #49906
- Adjusted Pebble's out of memory error behavior to match that of the Go runtime in order to make the condition more obvious. #49874
- When performing incremental backups with revision history on a database (or full cluster), and a table in the database was dropped and then other tables were later created, the backup no longer returns an error. #49925
- Fixed an internal planning error for recursive CTEs (`WITH RECURSIVE` expressions) in which the left side of the `UNION ALL` query used in the CTE definition produced zero rows. #49964

### Doc updates

- Added a CockroachCloud Quickstart on creating and connecting to a 30-day free CockroachCloud cluster and running your first query. #7454
- Updated the Active Record tutorial to use a new CockroachDB adapter version. #7480
- Changed instances of "whitelist"/"blacklist" to "allowlist"/"blocklist" throughout the documentation. #7479
- Updated all mentions of `range_min_size` and `range_max_size` to use the new default values of `134217728` and `536870912`, respectively. #7449
- Updated the hardware storage recommendations. #7514
- Revised the node decommissioning guidance. #7304

### Contributors

This release includes 30 merged PRs by 20 authors.

## v20.1.1

Release Date: May 26, 2020

This page lists additions and changes in v20.1.1 since v20.1.0.

- For a comprehensive summary of features in v20.1, see the v20.1 GA release notes.
- To upgrade to v20.1, see Upgrade to CockroachDB v20.1

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Backward-incompatible changes

- 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. #48094
- Updated the textual error and warning messages displayed by `cockroach quit`. #47692
- `cockroach quit` now prints progress details on its standard error stream, even when `--logtostderr` is not specified. Scripts that wish to ignore this output can redirect the standard error stream. #47692
- CockroachDB v20.1 introduced an experimental new rule for the `--join` flag causing it to prefer SRV records, if present in DNS, to look up the peer nodes to join. However, it is also found to cause disruption in in certain deployments. To reduce this disruption and 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. #49129
- Added a new cluster setting, `server.shutdown.lease_transfer_wait`, that allows you to configure the server shutdown timeout period for transferring range leases to other nodes. Previously, the timeout period was not configurable and was set to 5 seconds, and the phase of server shutdown responsible for range lease transfers would give up after 10000 attempts of transferring replica leases away. The limit of 10000 attempts has been removed, so that now only the maximum duration `server.shutdown.lease_transfer_wait` applies. #47692

### General changes

- The statement diagnostics bundle now contains a new file, `trace-jaeger.json`, that can be manually imported in Jaeger for visualization. #47432

### Enterprise edition changes

- Fixed a bug where the job ID of a lagging changefeed would be omitted, and instead it would be reported as sinkless. #48562

### SQL language changes

- The `pg_collation`, `pg_proc`, `pg_database`, and `pg_type` tables in the `pg_catalog` database no longer require privileges on any database in order for the data to be visible. #48080, #48765
- 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. #48343
- `ROLLBACK TO SAVEPOINT` is no longer permitted after miscellaneous internal errors. #48305
- Fixed an issue with optimizing subqueries involving set operations that can prevent queries from executing. #48680
- CockroachDB now correctly reports the type length for the `char` type. #48642
- The `RowDescription` message of the wire-level protocol now contains the table ID 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. The message also 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. #48748, #49087

### Command-line changes

- `cockroach debug zip` now tries multiple times to retrieve data using SQL if it encounters retry errors and skips over fully decommissioned nodes. It also 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 `cockroach 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`. #48094
- 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. #48016
- It is now possible to drain a node without shutting down the process, using the `cockroach node drain` command. 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. Without this new mode, there is a risk to misconfigure the service manager to auto-restart the node after it shuts down via `quit`, in a way that's surprising or unwanted. The new command `node drain` also recognizes the new `--drain-wait` flag. #47692
- The time that `cockroach quit` waits client-side for the node to drain (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. #47692
- 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 the 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. #47692
- 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. #47692
- Added support for `list cert` with certificates which require `--cert-principal-map` to pass validation. #48177
- Added support for the `--cert-principal-map` flag in the `cockroach cert`, `cockroach sql`, `cockroach init`, and `cockroach quit` commands. #48177
- Made `--storage-engine` sticky (i.e., resolve to the last used engine type when unspecified) even when specified stores are encrypted at rest. #49073

### Admin UI changes

- Fixed a bug where `Raft log too large` was reported incorrectly for replicas for which the raft log size is not to be trusted. #48286
- Fixed a bug where a multi-node cluster without localities defined wouldn't be able to render the Network Latency page. #49191
- Fixed a bug where link to specific problem ranges had an incorrect path. Problem ranges are now linked correctly again. #49188

### Bug fixes

- Fixed a bug where vectorized queries on composite datatypes could sometimes return invalid data. #48463
- 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 exist inside RocksDB. This scenario is rare, but possible. #48321
- Fixed a bug 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. #48838
- Fixed an error where instead of returning a parsing error in queries with `count(*)` CockroachDB could incorrectly return no output (when the query was executed via row-by-row engine). #47485
- Fixed a bug where CockroachDB was incorrectly releasing memory used by hash aggregation. #47518
- `cockroach debug zip` can now successfully avoid out-of-memory errors when extracting very large `system` or `crdb_internal` tables. It will also report an error encountered while writing the end of the output ZIP file. #48094
- Removed redundant metadata information for subqueries and postqueries in `EXPLAIN (VERBOSE)` output. #47975
- `TRUNCATE` can now run on temporary tables, fixing a bug in v20.1 where temporary tables could not be truncated, resulting in an error `unexpected value: nil`. #48078
- Fixed a bug in which `(tuple).*` was only expanded to the first column in the tuple and the remaining elements were dropped. #48290
- Fixed case where `PARTITION BY` and `ORDER BY` columns in window specifications were losing qualifications when used inside views. #47715
- CockroachDB will no longer display a severe `internal error` upon certain privilege check failures via `pg_catalog` built-in functions. #48242
- Fixed a bug where a read operation in a transaction with a past savepoint rollback would give an internal error for exceeding the maximum count of results requested #48165
- The distinction between delete jobs for columns and dependent jobs for deleting indices, views and sequences is now better defined. #48259
- Fixed incorrect results that could occur when casting negative intervals or timestamps to type `decimal`. #48345
- Fixed an error that occurred when statistics collection was explicitly requested on a column with type `array`. #48343
- Fixed a nil pointer dereference in Pebble's block cache due to a rare "double free" of a block. #48346
- Fixed Pebble to properly mark `sstables` for compaction which contain range tombstones. This matches the behavior when using RocksDB and ensures that space used for temporary storage is reclaimed quickly. #48346
- Fixed a bug introduced in v20.1 that could cause multiple index GC jobs to be created for the same schema change in rare cases. #47818
- 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. #48072
- Fixed a rare bug where stats were not automatically generated for a new table. #48027
- Fixed a panic that could occur when `SHOW RANGES` or `SHOW RANGE FOR ROW` was called with a virtual table. #48347
- Made SRV resolution non-fatal for join list records to align with the standard and improve reliability of node startup. #48349
- Fixed a rare bug causing a range to deadlock and all the writes to the respective range to timeout. #48303
- 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. #48456
- When processing `--join`, invalid SRV records with port number `0` are now properly ignored. #48527
- Fixed a bug where `SHOW STATISTICS USING JSON` contained incorrect single quotes for strings with spaces inside histograms. #48544
- Fixed a bug where the two settings `kv.range_split.by_load_enabled` and `kv.range_split.load_qps_threshold` were incorrectly marked as non-public in the output of `SHOW CLUSTER SETTINGS`. #48585
- You can no longer drop databases that 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. #48606
- Fixed a bug preventing timestamps from being closed which could result in failed follower reads or failure to observe resolved timestamps in changefeeds. #48682
- Fixed `debug encryption-status` and the Admin UI display of encryption status when using Pebble. #47995
- CockroachDB now deletes the partially imported data after an `IMPORT` fails or is canceled. #48605
- Fixed a bug where the `SHOW CREATE` statement would sometimes show a partitioning step for an index that has been dropped. #48768
- Re-allowed `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). #48760
- 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. This is likely to occur only with `vectorize=on` setting. #48751
- Fixed a bug where running `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 reimport dumped data without manual editing. #48776
- Fixed a bug where running `cockroach dump` on a table with collated strings would omit the collation clause for the data insertion statements. #48832
- CockroachDB now properly restores tables that were backed up while they were in the middle of a schema change. #48850
- Manually writing a `NULL` value into the `system.users` table for the `hashedPassword` column will no longer cause a server crash during user authentication. #48836
- Fixed a bug where in rare circumstances, CockroachDB may fail to open a store configured to use the Pebble storage engine. #49080
- Fixed a bug where the storage engine, when configured to use the Pebble storage engine, would return duplicate keys, causing incorrect or inconsistent results. #49080
- Fixed a bug where columns of a table could not be dropped after a primary key change. #49088
- Fixed a bug which falsely indicated that `kv.closed_timestamp.max_behind_nanos` was almost always growing. #48716
- 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 rebuilt as part of a primary key change would keep their partitions but lose the zone configurations attached to those partitions. #48827
- Fixed costing of lookup join with a limit on top, resulting in better plans in some cases. #49137
- Fixed a bug where on dropping a database, it would not drop the entry for its public schema in the `system.namespace` table. #49139
- `SHOW BACKUP SCHEMAS` no longer shows table comments as they may be inaccurate. #49130
- Fixed a memory leak which can affect changefeeds performing scans of large tables. #49161
- Prevented namespace orphans (manifesting as `database "" not found` errors) when migrating from v19.2. #49200
- Fixed a bug that caused query failures when using arrays in window functions. #49238

### Performance improvements

- Disabled the Go runtime block profile by default which results 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. #48153
- 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. #47818
- 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. The histograms also have improved cardinality estimates. This results in better plans in some cases. #48626, #48646
- Fixed a bug that caused a simple schema change to take more than 30s. #48621
- Queries run via the vectorized execution engine are now processed faster, with most noticeable gains on the queries that output many rows. #48732
- Reduced time needed to run a backup command when it is built on a lot of previous incremental backups. #48772

### Doc updates

- Added a tutorial on using Flyway with CockroachDB. #7329

### Contributors

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

- Drew Kimball (first-time contributor)

## v20.1.0

Release Date: May 12, 2020

With the release of CockroachDB v20.1, we've made a variety of productivity, performance, and security improvements. Check out a comprehensive summary of the most significant user-facing changes and then upgrade to CockroachDB v20.1. You can also read more about these changes in the v20.1 blog post or watch our 20.1 release demo and overview.

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1.0 - v20.1.13. 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.1.15 or later, which includes resolutions.

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

### Summary

This section summarizes the most significant user-facing changes in v20.1.0. For a complete list of features and changes, including bug fixes and performance improvements, see the release notes for previous testing releases.

- CockroachCloud
- Core features
- Enterprise features
- Backward-incompatible changes
- Known limitations
- Education

### CockroachCloud

- You can now use the code `CRDB30` for a free 30-day trial of CockroachCloud.

- CockroachCloud pricing is now available on our website.

- VPC peering is now supported for CockroachCloud clusters running on GCP. Contact us to set up a VPC peering-enabled CockroachCloud cluster.

### Core features

These features are freely available in the core version and do not require an enterprise license.

Area | Feature | Description
--- | --- | ---
SQL | Online Primary Key Changes | The new `ALTER TABLE ... ALTER PRIMARY KEY` statement lets you change a table’s primary key with no interruption to data access. The old primary key is converted to a `UNIQUE` secondary index to help optimize the performance of queries that still filter on the old key. However, if this conversion is not desired, you can drop and add a primary key constraint instead.
SQL | Schema Change Controls | Online schema changes can now be paused, resumed, and cancelled via `PAUSE JOB`, `RESUME JOB`, and `CANCEL JOB`.
SQL | Foreign Key Improvements | CockroachDB now supports multiple foreign key constraints on a single column. Also, it's now possible to drop the index on foreign key columns, or on the referenced columns, if another index exists on the same columns and fulfills indexing requirements.
SQL | `SELECT FOR UPDATE` | The new `SELECT FOR UPDATE` statement lets you order transactions by controlling concurrent access to one or more rows of a table. It works by locking the rows returned by a selection query, such that other transactions attempting to `SELECT` the same data and then `UPDATE` the results of that selection are forced to wait for the transaction that locked the rows to finish. This prevents transaction retries that would otherwise occur and, thus, leads to increased throughput and decreased tail latency for contended operations.
SQL | Nested Transactions and Savepoints | CockroachDB now supports the nesting of transactions using savepoints. These nested transactions, also known as sub-transactions, can be rolled back without discarding the state of the entire surrounding transaction. This can be useful in applications that abstract database access using an application development framework or ORM. Different components of the application can operate on different sub-transactions without having to know about each others' internal operations, while trusting that the database will maintain isolation between sub-transactions and preserve data integrity.
SQL | Hash-Sharded Indexes | For tables indexed on sequential keys, CockroachDB nows offers hash-sharded indexes to distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance. This feature is currently experimental.
SQL | Slow Query Log | You can now enable a slow query log to record SQL queries whose service latency exceeds a specified threshold.
SQL | `EXPLAIN` Improvements | The new `EXPLAIN ANALYZE (DEBUG)` option executes a query and generates a link to a ZIP file that contains the physical query plan, execution statistics, statement tracing, and other information about the query. Also, the `(DISTSQL, TYPES)` option on `EXPLAIN` and `EXPLAIN ANALYZE` include the data types of the input columns in the physical plan.
SQL | Recursive Common Table Expressions | CockroachDB now supports common table expressions that contain subqueries that refer to their own output.
SQL | `TIMETZ` Data Type | CockroachDB now supports the `TIMETZ` variant of the `TIME` data type for SQL standard compliance and increased compatibility with ORMS.
SQL | Precision in Time Values | CockroachDB now supports precision levels from 0 (seconds) to 6 (microseconds) for `TIME`/`TIMETZ` and `INTERVAL` values. Precision in time values specifies the number of fractional digits retained in the seconds field.
SQL | Vectorized Execution Improvements | Vectorized execution now supports hash joins, merge joins, most window functions, as well as the `TIMESTAMPTZ` data type in addition to several other previously supported data types.
SQL | Column Families in Secondary Indexes | Secondary indexes now respect the column family definitions applied to tables. When you define a secondary index, CockroachDB breaks the secondary index key-value pairs into column families, according to the family and stored column configurations.
SQL | Temporary Tables | CockroachDB now supports session-scoped temporary tables, views, and sequences. Unlike persistent objects, temp objects can only be accessed from the session in which they were created, and they are dropped at the end of the session. This feature is currently experimental.
Dev Tools | Expanded ORM Support | CockroachDB now supports additional Postgres-compatible ORMs, including Django and pewee for Python developers, and jOOQ for Java developers.
I/O | Bulk Import Improvements | The `IMPORT` and `IMPORT INTO` statements now support bulk importing from Avro files. This makes it easier to migrate from systems like Spanner that export data in the Avro format. Also, the new `cockroach nodelocal upload` command makes it easier to upload a file to a node's external IO directory for import from the node rather than from cloud storage. Finally, paused imports, when resumed, now continue from their internally recorded progress instead of starting over.
Security | RBAC Changes | All role-based access control (RBAC) features (`CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`, `GRANT ROLE`, `REVOKE ROLE`) are now covered by the BSL license and available to non-enterprise users.
Security | Various Improvements | Several security features have been added to CockroachDB v20.1, including the ability to customize the mapping between TLS certificates and principals, to name user/roles with periods so as to reflect the structure of internet domain names, to allow or disallow users from authenticating, and to allow or disallow users from creating, altering, and dropping other users. Also, CockroachDB's support for the PostgreSQL Host-Based Authentication (HBA) configuration language, which enables sites to customize the principal/client address/authentication method matrix, has been extended and unified.
CLI | Demo Cluster Improvements | Several features have been added to the `cockroach demo` command, including the ability to start a demo cluster in secure mode using TLS certificates to encrypt network communication (via the `--insecure=false` flag), to return the client connection URLs for all nodes in a demo cluster (via the `demo ls` shell command), to shut down/restart/decommission/recommission individual nodes in a multi-node demo cluster (via the `demo shutdown
UI | Various Improvements | The Network Latency page of the Admin UI is now easier to access and has been redesigned to help you understand the round-trip latencies between all nodes in your cluster. Also, the Statement Details page now allows you to write information about a SQL statement to a diagnostics bundle that you can download. This bundle consists of a JSON file that contains a distributed trace of the SQL statement, a physical query plan, execution statistics, and other information about the query.
Internals | Various Improvements | CockroachDB's storage layer now uses protected timestamps to ensure the safety of historical data while also enabling shorter GC TTLs. A shorter GC TTL means that fewer previous MVCC values are kept around. This can help lower query execution costs for workloads which update rows frequently throughout the day, since the SQL layer has to scan over previous MVCC values to find the current value of a row. Also, Cockroach's transaction layer now uses a concurrency manager to sequence incoming requests and provide isolation between the transactions that issued those requests that intend to perform conflicting operations.

### Enterprise features

These features require an enterprise license. Register for a 30-day trial license here, or consider testing enterprise features locally using the `cockroach demo` CLI command, which starts an in-memory CockroachDB cluster with a temporary enterprise license pre-loaded. CockroachCloud clusters also include all enterprise features.

Area | Feature | Description
--- | --- | ---
Recovery | Full-cluster backup and restore | CockroachDB's `BACKUP` feature now supports backing up an entire cluster's data, including all configuration and system information such as user privileges, zone configurations, and cluster settings. In rare disaster recovery situations, CockroachDB's `RESTORE` feature can now restore a cluster backup to a new cluster. Restoring a cluster backup to an existing cluster is not supported.
Recovery | Encrypted backups | CockroachDB now supports using an encryption passphrase to encrypt data in Enterprise `BACKUP` files and to decrypt the data upon `RESTORE`.
SQL | Improved follower reads | Follower reads are now available for `AS OF SYSTEM TIME` queries at least 4.8 seconds in the past, a much shorter window than the previous 48 seconds.

### Backward-incompatible changes

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

- The `extract()` built-in function with sub-second arguments (millisecond, microsecond) is now Postgres-compatible and returns the total number of seconds in addition to sub-seconds instead of returning only sub-seconds.

- Casting intervals to integers and floats is now Postgres-compatible and values a year at 365.25 days in seconds instead of 365 days.

- The combination of the `CHANGEFEED` options `format=experimental_avro`, `envelope=key_only`, and `updated` is now rejected. This is because the use of `key_only` prevents any rows with updated fields from being emitted, which renders the `updated` option meaningless.

- The `cockroach init` CLI command now waits for server readiness and thus no longer fails when a mistaken server address is provided.

- The `cockroach user` CLI command has been removed. It was previously deprecated in CockroachDB v19.2. Note that a v19.2 client (supporting `cockroach user`) can still operate user accounts in a v20.1 server.

- CockroachDB now creates files without read permissions for the "others" group. Sites that automate file management (e.g., log collection) using multiple user accounts now must ensure that the CockroachDB server and the management tools running on the same system are part of a shared unix group.

- The `GRANT` and `REVOKE` statements now require that the requesting user already have the target privileges themselves. For example, `GRANT SELECT ON t TO foo` requires that the requesting user already have the `SELECT` privilege on `t`.

- During an upgrade to v20.1, ongoing schema changes will stop making progress, and it will not be possible to manipulate them via `PAUSE JOB`/`RESUME JOB`/`CANCEL JOB` statements. Once the upgrade has been finalized, these schema changes will run to completion.

- During an upgrade to v20.1, new schema changes will be blocked and return an error, with the exception of `CREATE TABLE` statements without foreign key references and no-op schema change statements that use `IF NOT EXISTS`. Also, ongoing schema changes started will stop making progress, and it will not be possible to manipulate them via `PAUSE JOB`/`RESUME JOB`/`CANCEL JOB` statements. Once the upgrade has been finalized, ongoing schema changes will run to completion and new schema changes will be allowed.

### Known limitations

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

### Education

Area | Topic | Description
--- | --- | ---
Training | Videos Lessons on YouTube | Added two Cockroach University playlists to YouTube, one with the entire set of video lessons from "Getting Started with CockroachDB", and the other featuring the first batch of video lessons from the upcoming course, "CockroachDB for Python Developers".
Docs | Developer Guide | Added guidance on common tasks when building apps on CockroachDB, such as installing Postgres clients; connecting to the database; effectively inserting, querying, updating, and deleting; handling errors; and making queries fast. For convenience, much of the guidance is offered across various popular languages (Java, Python, Go) in addition to straight SQL.
Docs | "Hello World" Repos | Added several language-specific GitHub repos with the simple starter applications featured in our "Hello World" tutorials.
Docs | Multi-Region Sample App and Tutorial | Added a full-stack, multi-region sample application (GitHub repo) with an accompanying tutorial on building a multi-region application on a multi-region CockroachCloud cluster. Also added a video demonstration as a YouTube playlist.
Docs | Streaming Changefeeds to Snowflake Tutorial | Added an end-to-end tutorial on how to use an Enterprise changefeed to stream row-level changes from CockroachCloud to Snowflake, an online analytical processing (OLAP) database.
Docs | Improved Backup/Restore Docs | Updated the backup/restore docs to better separate broadly applicable guidance and best practices from more advanced topics.
Docs | Release Support Policy | Added a page explaining Cockroach Labs' policy for supporting major releases of CockroachDB, including the phases of support that each major release moves through, the currently supported releases, and an explanation of the naming scheme used for CockroachDB.

## v20.1.0-rc.2

Release Date: April 21, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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 new ability to specify `TIME`/`TIMETZ` and `INTERVAL` precision is available only after finalizing an upgrade to v20.1. Previously, it was allowed to specify precision for these data types in clusters with mixed v19.2 and v20.1 nodes, but nodes running v19.2 would not respect the precision. #47438

### Command-line changes

- The new `--clock-device` flag for `cockroach start` and `cockroach start-single-node` identifies a PTP hardware clock for querying current time. This is supported on Linux only and may be needed in cases where the host clock is unreliable or prone to large jumps (e.g., when using vMotion). #47379

### Bug fixes

- Fixed a bug causing some schema change rollbacks to fail permanently even on transient errors. #47575
- Fixed an incompatibility between Pebble and RocksDB bloom filters that could result in keys disappearing or reappearing when switching storage engines. #47611
- Fixed a panic that would result in "invalid truncation decision" error messages. #47346
- Fixed a backward incompatibility between RocksDB and Pebble that prevented RocksDB from opening a Pebble created WAL file under certain conditions. #47383
- Fixed a mishandling of truncated WAL records in Pebble that could prevent Pebble from opening a DB after a crash. #47383
- Fixed a bug in the new schema change GC job implementation that caused unnecessary table descriptor lookups whenever a table was updated. #47490
- Fixed a bug introduced in an earlier v20.1 release that could cause a workload to stall under heavy load. #47493
- Fixed a bug introduced with the new schema change job implementation in v20.1.0-beta.3 that caused errors when rolling back a schema change to be swallowed. #47499
- Fixed a bug that could could trigger an assertion with the text "received X results, limit was Y". #47501

### Contributors

This release includes 15 merged PRs by 10 authors.

## v20.1.0-rc.1

Release Date: April 14, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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

- The new `--unencrypted-localhost-http` flag for `cockroach start` and `cockroach start-single-node` forces the HTTP listener to bind to `localhost` addresses only and disables the TLS protocol. In secure clusters, this makes the Admin UI reachable with an `http://` URL without requiring certificate or CA setup. #46472

### General changes

- Transactions reading a lot of data behave better when exceeding the memory limit set by the `kv.transaction.max_refresh_spans_bytes` cluster setting. 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. #46803
- Before upgrading from v19.2 to v20.1, it is best practice to make sure there are no schema changes in progress. However, if any are still running when the upgrade is started, note that they will stop making progress during the upgrade and will run to completion once the upgrade has been finalized. #47073

### Enterprise edition changes

- The new `protect_data_from_gc_on_pause` `CHANGEFEED` option ensures that the data needed to resume a `CHANGEFEED` is not garbage collected. #46345
- `BACKUP`s and `RESTORE`s now collect some anonymous telemetry on throughput and feature usage. #46755
- `SHOW BACKUP` now shows whether a `BACKUP` is a cluster backup or not. #46768
- `SHOW BACKUP` now shows the privileges assigned to tables and databases in a backup and, if the `WITH privileges` option is specified, lists which users and roles had which privileges. #46853
- Incremental backups and restores using HTTP storage now require explicitly specifying incremental storage locations. #46967
- The new, appending incremental backup syntax does not allow converting a cluster backup to a specific table or database backup. #46966

### SQL language changes

- Outer columns (columns in a subquery that reference a higher scope) can now be used in the `SELECT` list of an aggregation or grouping expression without explicitly including them in the `GROUP BY` list, for improved Postgres compatibility. #46417
- Renamed the `EXPLAIN BUNDLE` statement to `EXPLAIN ANALYZE (DEBUG)`. #46534
- The `EXPLAIN ANALYZE (DEBUG)` statement now contains all the information available via `EXPLAIN (OPT,ENV)`. #46441
- The `length()`, `octet_length()` and `bit_length()` built-in functions are now supported on `BIT` and `VARBIT`. #46524
- The `EXPLAIN ANALYZE` response now includes memory and disk usage details and omits allocation stats if they are zero. #46316
- The `CREATE TEMPORARY TABLE` statement now supports the `ON COMMIT` syntax. #46594
- The `IMPORT` statement now records additional anonymous telemetry about its performance and reliability. #46763
- `CREATE INDEX CONCURRENTLY` and `DROP INDEX CONCURRENTLY` are now supported as no-ops, as all indexes are created concurrently. #46802
- The type checking code now prefers aggregate overloads with string inputs if there are multiple possible candidates due to arguments of unknown type. #46898
- Added an unimplemented error when attempting to `ADD CONSTRAINT` with the `EXCLUDE USING` syntax. #46909
- Added support for `CREATE INDEX .... INCLUDE (col1, col2, ...)`, which is an alias that PostgreSQL uses that is analogous to CockroachDB's `STORING (col1, col2, ...)` syntax. #46909
- Added support for parsing the `REINDEX` syntax, which results in an unimplemented error that explains that `REINDEX`ing is not required in CockroachDB. #46909
- The vectorized execution engine now only runs queries with streaming operators. To enable vectorized execution for buffering operators, use `SET vectorize=on`. #46925
- The `EXPLAIN` response now shows `SPANS | FULL SCAN` for full table scans and `SPANS | LIMITED SCAN` if there is a limit. Previously, both cases would return `SPANS | ALL`. #47013
- The `CREATE ROLE`/`ALTER ROLE`/`DROP ROLE` results no longer show the rows affected, as this number could be misleading. #46819
- Added a hint to use `ALTER ROLE` when trying to `GRANT` a role option directly to a user using the `GRANT ROLE` syntax. #46819
- Improved the error message for `ALTER COLUMN ... SET DATA TYPE` for data type conversions that involve overwriting existing values. #47170

### Command-line changes

- The `cockroach` commands that internally use SQL, including `cockroach sql` and `cockroach demo`, 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'`. #47007
- The `cockroach workload` command now sets its `application_name` based on the chosen workload. #46546
- The `cockroach debug zip` command now creates valid zip files even if some of its requests encounter an error. #46634
- The `cockroach demo` command now displays a TCP-based connection URL and unix datagram socket for each node of a demo cluster. #46935
- It is now possible to pre-configure the secure mode of `cockroach demo` using the `COCKROACH_INSECURE` environment variable like other client commands. #46959
- When running `cockroach demo` in secure mode, the generated SQL URL now embeds the password so that commands using this URL can run without a request for password. #47007
- The SQL URL generated by `cockroach demo` no longer requires TLS client certificates in particular directory locations. #47007
- The new experimental client-side command `demo ls` for `cockroach demo` displays the connection parameters for every node in a demo cluster. #47007
- 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. #47007

### Admin UI changes

- The ALL filter on the Statements page now excludes internal statements. #45646
- Tooltips showing statements and jobs are now limited in size for very long statements. #46982
- The default timescale on metrics pages is now 10 minutes. Previously, the timescale defaulted to the age of the longest running node. #46980
- Improved tooltips for existing capacity and storage metrics. #46987
- Added analytics tracking for table sorts, searches and diagnostics activation on the Statements page, and navigation on the Statement Details page. #47003
- The download link for statement diagnostics now points to the bundle zip file. #47016
- Removed the Statements tab from the Databases > Table Details page. #47102
- Cleaned up barcharts on the Statements page. #47129

### Bug fixes

- Fixed an Admin UI bug where sort columns were only being applied per-page instead of for the entire multi-page list of statements #46978
- Fixed a performance bug where truncate would take 2*num columns + 2*num indexes round trips. This could lead to slow truncate performance in distributed clusters. #46334
- It is no longer possible to inject stats within an explicit transaction. #46567
- Casting a bit array to a bigger varbit array no longer adds extra 0 padding at the end. #46532
- Fixed a bug where `pg_catalog.pg_indexes` showed the wrong index definition for inverted indexes. #46527
- Fixed incorrect query results in some corner cases involving variance/stddev/corr. #46436
- Fixed an internal error or incorrect evaluation of check constraints in certain cases involving `UPSERT` and foreign key checks. #46409
- `cockroach debug zip` now properly collects heap profiles. #46469
- The goroutine dump facility now functions properly when logging to files is disabled, e.g., via `--log-dir=` or `--logtostderr`. #46469
- Fixed an internal error that could occur during planning for some queries with a join and negative `LIMIT`. #46440
- Fixed a bug where the vectorized engine could sometimes give an incorrect result when reading from interleaved parents or children. #46456
- Fixed a bug where the vectorized engine would throw an internal error when executing a query that utilized an inverted index. #46267
- Fixed a bug where operations on an index that contained a collated string in descending order would fail. #46570
- Fixed a bug with `SET TIME ZONE`, where a string prefixed with `UTC` or `GMT` and with time zones with colons had it's offset inverted the wrong way. #46510
- CockroachDB no longer incorrectly accounts for some RAM usage when computing aggregate functions. #46545
- `SHOW INDEXES ... WITH COMMENT` no longer shows duplicate rows for certain tables if indexes are identically named. #46621
- Fixed an internal error that could happen during planning when a column with a histogram was filtered with a predicate of a different data type. #46552
- Scans that lock rows (via `FOR UPDATE`) are no longer elided when the results are unused. #46676
- Fixed a bug (introduced in v20.1.0-beta.3) in the new schema change GC job implementation which would cause the execution of GC jobs to be incorrectly delayed in the presence of other table descriptor updates. #46691
- Fixed a bug with distinct aggregations on `JSONB` columns. #46711
- Fixed a rare bug causing the assertion failure "caller violated contract: discovered non-conflicting lock". #46744
- Ensured that index and table GC happen closer to their GC deadline. #46743
- Statement diagnostics created through `EXPLAIN ANALYZE (DEBUG)` now show up in the UI page. #46804
- Benign "outbox next" errors are now only logged when log verbosity is set to 1 or greater. #46838
- Failed or canceled `IMPORT`s now properly clean up partially imported data. #46856
- Failed or canceled `RESTORE`s now properly clean up partially imported data. #46855
- Fixed a rare bug causing errors to be returned for successfully committed transactions. The most common error message was "TransactionStatusError: already committed". #46848
- The "insecure cluster" indicator is once again displayed at the top right of Admin UI for insecure clusters. #46865
- Fixed a rare assertion failure that contained the text "existing lock cannot be acquired by different transaction". This assertion was only present in earlier v20.1 releases and not in any earlier releases. #46896
- Fixed an incorrect query result that could occur when a scalar aggregate was called with a null input. #46898
- Fixed incorrect result with count(*) when grouping on constant columns. #46891
- `cockroach demo` now properly cleans up its temporary files if the background license acquisition fails. #47007
- Tooltips for statement diagnostics are now only shown on hover. #46995
- Fixed a bug when queries with projections of only `INT2` and/or `INT4` columns were executed via the vectorized engine. #46977
- CockroachDB no longer considers a non-`NULL` value from an interleaved parent table to be `NULL` when the interleaved child has a `NULL` value in the row with the corresponding index key. #47103
- Incremental, full-cluster `BACKUP`s with revision history are no longer disallowed in some cases where system tables have changed. #47132
- Fixed a bug when adding a self-referencing foreign key constraint in the same transaction that creates a table. #47128
- Change data capture no longer combines with long running transactions to trigger an assertion with the text "lock timestamp regression". #47139
- As part of migrating to the new schema change job implementation in 20.1, failed `IMPORT` and `RESTORE` jobs that 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. #47144
- Fixed a bug preventing clusters from creating `TIMETZ` columns before an upgrade to v20.1 is finalized. #47169
- Fixed a data race on AST nodes for `SELECT` statements that include a `WINDOW` clause. #47175
- Fixed the behavior of `crdb_internal.zones` in mixed-version clusters. #47236
- Fixed reads from `system.namespace` and `crdb_internal.zones` on 19.2 nodes in a mixed-version cluster. #47236
- Fixed incompatibility with v19.2 nodes for tables with computed columns. #47274
- Restoring a backup from v2.1 to v20.1 with a timestamp column no longer results in incomplete type data. #47240
- Fixed some cases where limits were applied incorrectly when pushed down into scans (resulting in some queries returning more results than they should). #47296
- Fixed an assertion failure with the text "expected latches held, found none". #47301

### Performance improvements

- Improved execution plans involving filters with `OR` expressions. #46371
- Improved execution plans for queries containing a division by a constant. #46861
- Virtual tables that access all table descriptors now make fewer round trips. #46949

### Build changes

- Building from source now requires Go 1.13.19. #46619
- It is now possible to build CockroachDB with the Clang++ v10 compiler. #46860

### Doc updates

- Improved the documentation on viewing and controlling backup jobs and added documentation on showing a backup with privileges. #7101
- Documented key/passphrase-based backup encryption. #7085
- Documented how to use `EXPLAIN(DISTSQL, TYPES)` to include the data types of the input columns in the generated physical plan. #7045
- Updated Azure hardware recommendations. #7005
- Documented `INTERVAL` duration fields and updated the syntax and precision details. #7000
- Various updates related to role-based access control (RBAC) moving under the BSL license. #7003

### Contributors

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

- Andrii Vorobiov
- Shaker Islam (first-time contributor)

## v20.1.0-beta.4

Release Date: March 30, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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

- The `admin` role is now required to use the new `cockroach nodelocal upload` functionality. #46265

### Enterprise edition changes

- Incremental `BACKUP` can now quickly skip unchanged data. This makes frequent incremental backups 10-100x faster depending on data size and frequency. #46390

### SQL language changes

- Added `get_bits()` and `set_bit()` builtin functions for bits. #45957
- Modified the `get_bits()` and `set_bit()` builtin functions to support byte array. #46380
- Arrays can now be compared using the `<`, `<=`, `>`, and `>=` operations. #46254
- `EXPLAIN BUNDLE` now contains distsql diagrams. #46225
- Previously, when creating a non-partitioned index on a partitioned table with the `sql_safe_updates` session variable set to `true`, CockroachDB would error out. CockroachDB now sends a NOTICE stating that creating a non-partitioned index on a partitioned table is not performant. #46223
- Added new internal tables `crdb_internal.node_transactions` and `crdb_internal.cluster_transactions` that contain some metadata about active user transactions. #46206
- Added the column `txn_id` to the `crdb_internal.node_queries` and `crdb_internal.cluster_queries` tables. These fields represent the transaction ID of each query in each row. #46206
- Columns in the process of being added to or removed from a table are now always set to their default or computed value if another transaction concurrently `INSERT`s, `UPDATE`s, or `UPSERT`s a row. This fixes an issue where a column being backfilled would not get properly set by concurrent transactions. #46285
- `ROLLBACK TO SAVEPOINT` (for either regular savepoints or "restart savepoints" defined with `cockroach_restart`) now causes a "feature not supported" error after a DDL statement in a HIGH PRIORITY transaction, in order to avoid a transaction deadlock. See issue #46414 for details. #46415
- Added support for the `stddev_samp` aggregate builtin function, which is the same as `stddev` (according to PostgreSQL documentation, the latter is actually the historical alias of the former). #46279

### Command-line changes

- Ensured the correct error messages are shown to the user when using `cockroach nodelocal upload`. #46311

### Bug fixes

- Fixed a crash when `IMPORT`ing a table without a table definition. #46193
- Added support for queries with qualified stars that refer to tables in outer scopes. #46233
- Fixed an incorrect "no data source matches prefix" error in some cases involving subqueries that use views. #46226
- Previously, the `experimental_strftime` and `experimental_strptime` builtin functions used the non-POSIX standard `%f` for nanosecond display. However, as CockroachDB only supports up to microsecond precision and Python's `strftime` has `%f` to microsecond, we have similarly switched %f to use microsecond instead of nanosecond precision. #46263
- Added a check that detects invalid sequence numbers in the RocksDB write-ahead log and returns an error during node startup instead of applying the invalid log entries. #46328
- Follower reads that hit intents no longer have a chance of entering an infinite loop. This bug was present in earlier versions of the v20.1 release. #46234
- Fixed an internal error that could occur when an aggregate inside the right-hand side of a `LATERAL` join was scoped at the level of the left-hand side. #46227
- Fixed an error that incorrectly occurred when an aggregate was used inside the `WHERE` or `ON` clause of a subquery but was scoped at an outer level of the query. #46227
- Reverted performance improvements to incremental `BACKUP`s until a potential correctness issue is addressed. #46385
- CDC no longer combines with long running transactions to trigger an assertion. Previously, this 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. #46391
- Fixed a race in the vectorized execution engine. #46360
- Fixed a rare bug causing transactions that have performed schema changes to deadlock after they restart. #46384

### Doc updates

- Added docs for `cockroach nodelocal upload`, which uploads a file to the external IO directory on a node's (the gateway node, by default) local file system. #6871
- Added guidance on using hash-sharded indexes. #6820
- Updated production checklist and Azure deployment guides to recommend compute-optimize F-series VMs in Azure deployments. #7005

### Contributors

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

- Amit Sadaphule (first-time contributor)
- Andrii Vorobiov
- Marcus Gartner (first-time contributor, CockroachDB team member)
- abhishek20123g

## v20.1.0-beta.3

Release Date: March 25, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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 beta release includes the following major highlights:

- SELECT FOR UPDATE: CockroachDB now supports `SELECT FOR UPDATE` for ordering transactions. Use `SELECT FOR UPDATE` to lock the rows returned by a selection query, to control concurrent access to one or more rows of a table.
- SQL savepoints: CockroachDB now fully supports SQL savepoints. New syntax for savepoints includes `SAVEPOINT <foo>`, `RELEASE SAVEPOINT <foo>`, and `ROLLBACK TO SAVEPOINT <foo>`. To inspect the current stack of active savepoints, use `SHOW SAVEPOINT STATUS`.
- Hash-sharded indexes: CockroachDB now supports new syntax for defining hash-sharded indexes. Hash-sharded indexes improve write performance to indexes on sequential keys. To define a hash-sharded index, use `CREATE INDEX ... USING HASH WITH BUCKET_COUNT = <number of buckets>`.
- RBAC now under BSL: All role-based access control (RBAC) features (`CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`, `GRANT ROLE`, `REVOKE ROLE`) are now BSL features and available to non-enterprise users.
- Improved vectorized execution: Vectorized execution now supports hash joins, merge joins, and most window functions. By default, the `vectorized` session variable is set to `auto`, which uses vectorized execution for all queries except those including unordered `DISTINCT` clauses or calls to the `percent_rank` or `cume_dist` window functions. To turn vectorized execution on for all operations, set `vectorized` to `on`.
- Statement tracing in the Admin UI: Statement diagnostic information is now available in the Admin UI. When viewing a statement fingerprint in the Admin UI, you can now trigger a trace on the next query execution matching that statement fingerprint.

### Backward-incompatible changes

- The `GRANT` and `REVOKE` statements now require that the requesting user already have the target privileges themselves. For example, `GRANT SELECT ON t TO foo` requires that the requesting user already have the `SELECT` privilege on `t`. #45697
- During the upgrade process from 19.2 to 20.1, almost all schema changes will now be disallowed on 20.1 nodes until the upgrade has been finalized, as part of ensuring consistency while the cluster undergoes a significant, backward-incompatible change in how schema changes are executed. Attempting these schema changes will return an error to the client. #45990
- To ensure consistency for schema changes throughout the upgrade process to 20.1, schema changes initiated in 19.2 now cannot be adopted, paused, or resumed from 20.1 nodes until an internal migration is run. #46214

### Security updates

### Identity management changes

- All role-based access control (RBAC) features (`CREATE ROLE`, [`ALTER ROLE`], `DROP ROLE`, `GRANT ROLE`, `REVOKE ROLE`) are now available to non-enterprise users. All RBAC features are now BSL features. #46042
- `USER`s and `ROLE`s are now the same (which is PostgreSQL behavior). This means `CREATE`/`ALTER`/`DROP` `ROLE`/`USER` can be used interchangeably. #44968
- The validation rule for principal names was extended to support periods and thus allow domain name-like principals. For reference, the validation rule is currently the regular expression `^[p{Ll}0-9_][---p{Ll}0-9_.]*$`, and limited to a size of 63 UTF-8 bytes (larger usernames are rejected with an error); for comparison, PostgreSQL allows many more characters and truncates at 63 characters silently. #45575
- Usernames can now contain periods, for compatibility with certificate managers that require domain names to be used as usernames. #45575
- User and role principals can now be prevented from logging in using the `NOLOGIN` attribute, which can be set using the `CREATE USER/ROLE` or `ALTER USER/ROLE`. When using the `CREATE ROLE` syntax, `NOLOGIN` is enabled by default whereas when using `CREATE USER`, it is not. #45541
- The `CREATEROLE` option can now be granted to users/roles using `CREATE USER/ROLE` or `ALTER USER/ROLE`. This delegates the permission to create additional roles. #44232
- Any role created prior to v20.1 will be able to log into clusters started with `--insecure`, unless/until they are given the `NOLOGIN` attribute. For secure clusters, roles cannot log in by virtue of not having a password nor a client certificate. #45541

### Authentication changes

- CockroachDB now offers the ability to SQL client connection events (connection established and connection terminated) and SQL client authentication events (authentication method selection, authentication method application, authentication method result, and session termination) to a distinct `cockroach-auth.log` file in each node's main log directory. To enable SQL client connection logging, set the `server.auth_log.sql_connections.enabled` cluster setting. To enable SQL client authentication event logging, set the new `server.auth_log.sql_sessions.enabled` cluster setting cluster setting. Note that this feature is experimental; as such, the interface and output are subject to change. #45193
- The password field (used exclusively for password-based authentication) can now be configured to have an expiration date using the `VALID UNTIL` attribute, which can be set with `ALTER USER/ROLE`. Note that the attribute sets an expiration date for the password, not the user account. This is consistent with PostgreSQL. #45541
- Client and node certificates are now allowed to specify the principal in either the SubjectCommonName field or the SubjectAlternateNames field. Previously, the principal could only be specified in the SubjectCommonName field. This facilitates the use of Amazon Certificate Manager (ACM) and other Cloud-based certificate management tools. #45819

### Authorization changes

- Admin users can now grant `ZONECONFIG` to non-admin users on specific SQL objects (databases/tables). When set, the user is authorized to modify that object's zone configuration and decide data placement on specific nodes or groups thereof. #45201
- The `GRANT` statement has been changed to be more like a capability-based system: it can only propagate privileges that the requesting user already has. Previously, it could be used to grant any other bit to any user, even to the requesting user. Note that the pseudo-privilege `ALL` includes `GRANT`, so `GRANT ALL`, in effect, preserves the previous behavior: it grants `GRANT` and every over privileges, so all privileges can be re-granted transitively. #45697
- It is now possible for operators to disable the use of implicit credentials when accessing external cloud storage services for various bulk operations (e.g, `BACKUP`, `IMPORT`, etc.). The use of implicit credentials can be disabled by using the `--external-io-disable-implicit-credentials` flag. #45969

### Security bug fixes

- All users can now view any comments on any object (bypassing other privileges), but modifying comments require write privilege on the target object. Previously, any user could modify any database/table/view/index comment via direct SQL updates to `system.comments`. This was unintended and a form of privilege escalation, and is now prevented. The privileges required for the `COMMENT` statement, `pg_description`, `col_description()`, `obj_description()`, and `shobj_description()` are operating as in PostgreSQL and are unaffected by this change.#45712
- The `--external-io-dir=disabled` now applies to `nodelocal upload` requests. #45858
- The non-authenticated `/health` HTTP endpoint was previously exposing the private IP address of the node, which can be privileged information in some deployments. This has been corrected. Deployments using automation to retrieve a node build details and address details should use `/_status/details/local` instead and use a valid admin authentication cookie. #45119

### Enterprise edition changes

- CDC to cloud-storage sinks now supports optional `gzip` compression. #45326
- `BACKUP` can be re-run with the same destination path to automatically append an incremental backup to that path. #45255
- `RESTORE` now allows using `AS OF SYSTEM TIME` to pick a target backup from a larger list of incremental backups. #45368
- Changefeeds now have new options to control the types of schema change events the changefeed should respond to (`schema_change_events`), and the behavior to take when such an event occurs (`schema_change_policy`). This functionality allows users to halt changefeeds upon schema changes or skip over the logical backfill that is performed by default. #45652
- Two new `CHANGEFEED` options, `initial_scan` and `no_initial_scan`, have been added. The `initial_scan` can be used only if no cursor had been specified (indicating the desire to start the `CHANGEFEED` from `now()`). The new options allow clients to override this default behavior and create changefeeds from the present without an initial scan, or from a point in time with one. You cannot specify both options simultaneously. #45663
- `BACKUP` will no longer fail if the GC window for a table is exceeded while backing the table up. #45859
- Incremental `BACKUP` can quickly skip unchanged data, making frequent incremental backups 10-100x faster, depending on data size and frequency. #46108

### Storage changes

- Improved the ability of garbage collection to process ranges exhibiting abnormally large numbers of transaction records and/or abort span entries. #45444
- Improved a debug message that is printed when a range is unavailable (i.e., unable to accept writes). #45580
- The timing of garbage collection for historical data is defined by the `gc.ttlseconds` variable in the applicable zone configuration. However, in practice, data is not garbage collected immediately after the TTL passes. The new `kv.gc_ttl.strict_enforcement` cluster setting makes sure that `AS OF SYSTEM TIME` queries older than the TTL, but before GC has happened, return an error. #45826
- A bug in the range metrics collection would fail to correctly identify a range that had lost quorum, causing it to not be reported via the "unavailable ranges" metric. This is now fixed. #45253
- Range garbage collection will now trigger based on a large abort span, adding defense-in-depth against ranges growing large (and eventually unstable). #45573
- Fixed a bug that could cause requests to a quiesced range to hang in the KV replication layer. This bug would cause the message "have been waiting ... for proposing" to appear. even though no loss of quorum occurred. #46045

### SQL changes

### SQL language additions

- CockroachDB now supports string and byte array literals using the dollar-quoted notation, as documented here. #44130
- CockroachDB now supports expanding all columns of a tuple using the `.*` notation, for example: `SELECT (t).* FROM (SELECT (1,'b',2.3) AS t)`. This is a CockroachDB-specific extension. #45609
- CockroachDB now supports accessing the Nth column in a column with tuple type using the syntax `(...).@N`, for example: `SELECT (t).@2 FROM (SELECT (1,'b',2.3) AS t)`. This is a CockroachDB-specific extension. #45609
- Duplicate rows in the input to an `INSERT..ON CONFLICT DO NOTHING` statement will now be ignored rather than triggering an error. #45443
- SQL savepoints are now supported. `SAVEPOINT <foo>`, `RELEASE SAVEPOINT <foo>`, and `ROLLBACK TO SAVEPOINT <foo>` now work. `SHOW SAVEPOINT STATUS` can be used to inspect the current stack of active savepoints. #45566

- CockroachDB still considers the name `cockroach_restart` special in `SAVEPOINT`s. A savepoint defined with the name `cockroach_restart` is a "restart savepoint" and has different semantics than standard savepoints:

  1. Restart savepoints must be opened immediately when the transaction starts. Opening a restart savepoint after other statements have been executed is not allowed. In contrast, standard savepoints can be opened after other statements have already been executed.
  2. After a successful `RELEASE`, a restart savepoint does not allow further use of the transaction. `COMMIT` must immediately follow the RELEASE.
  3. Restart savepoints cannot be nested. Issuing `SAVEPOINT cockroach_restart` two times in a row only creates a single savepoint marker. This can be seen with `SHOW SAVEPOINT STATUS`. Issuing `SAVEPOINT cockroach_restart` after `ROLLBACK TO SAVEPOINT cockroach_restart` reuses the marker instead of creating a new one. In contrast, two `SAVEPOINT` statements with a standard savepoint name, or a `SAVEPOINT` statement immediately after a `ROLLBACK`, create two distinct savepoint markers.

  Note: The session variable `force_savepoint_restart` still works and causes every savepoint name to become equivalent to `cockroach_restart` with the special semantics described above. #46194

- `SELECT FOR UPDATE` now hooks into a new leaseholder-only locking mechanism. This allows the feature to be used to improve performance of transactions that read, modify, and write contended to rows. Similarly, `UPDATE` statements now use this new mechanism by default, meaning that their performance under contention is improved. This is only enabled for `UPDATE` statements that can push their filter all the way into their key-value scan. To determine whether an `UPDATE` statement is implicitly using `SELECT FOR UPDATE` locking, look for a `locking strength` field in the `EXPLAIN` output for the statement. #45701

- The statement `CREATE SCHEMA IF NOT EXISTS` is now accepted, and ignored, if it targets one of the pre-defined schemas (`public`, `pg_temp`, `pg_catalog`, etc.) #42703

- Added the `every` aggregate function. #46059

- The `ceil` and `floor` built-in functions now accept integer inputs. #46166

### Query planning changes

- CockroachDB previously allowed `TIMESTAMP`/`TIMESTAMPTZ` and `TIME`/`TIMETZ` to be converted to `TIMESTAMP(0)`/`TIMESTAMPTZ(0)`, but this does not actually change any of the precision within it. CockroachDB now prohibits converting any precision of `TIMESTAMP`/`TIMESTAMPTZ`/`TIME`/`TIMETZ` to a lower precision value. #45314
- Operators on two arrays with different element types now fail at type-check time instead of evaluation time. #45260
- Improved the error message for the unsupported interaction between correlated subqueries and `WITH` clauses #45227
- `UPSERT` and `INSERT..ON CONFLICT` statements now (occasionally) need to do an extra check to ensure that they never update the same row twice. This may adversely affect performance in cases where the optimizer cannot statically prove the extra check is unnecessary. #45372
- The optimizer now considers the likely number of rows an operator will need to provide, and might choose query plans based on this. In particular, the optimizer might prefer lookup joins over alternatives in some situations where all rows of the join will probably not be needed. #45604
- JSONB columns can now used in `GROUP BY` and `DISTINCT ON` clauses. #45229
- The inverted index implementation now supports indexing array columns. This permits accelerating containment queries (`@>` and `<@`) on array columns by adding an index to them. #45157
- `EXPLAIN BUNDLE` can now be used to run a query and collect execution information in a support bundle, which can be downloaded via the Admin UI. #45735
- `EXPLAIN BUNDLE` now works when the client driver prepares the statement. #46111
- Renamed `experimental_optimizer_foreign_keys` session variable and `sql.defaults.optimizer_foreign_keys.enabled` cluster setting to remove the experimental prefix. #46174
- Fixed the `"negative limit hint"` internal query error. #45879
- Fixed query errors in cases where a CTE was used inside a recursive CTE. #45877
- Fixed an internal error that could occur in the optimizer when a `WHERE` filter contained at least one correlated subquery and one non-correlated subquery. #46153
- Improvements in session settings reporting in the `EXPLAIN (OPT,ENV)` output. #46212

### Execution changes

- The new `diagnostics.sql_stat_reset.interval` cluster setting controls the rate at which SQL statement statistics are refreshed. Additionally, the setting `diagnostics.forced_stat_reset.interval` was renamed to `diagnostics.forced_sql_stat_reset_interval`. #45082
- `UPDATE` 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. #45159
- Hash joins and sorts are now run using the vectorized engine when `vectorize=auto` (default configuration). #45582
- CockroachDB now collects separate sets of metrics for usage of `SAVEPOINT`: one set for regular SQL savepoints and one set for uses dedicated to CockroachDB's client-side transaction retry protocol. #45566
- Vectorized distributed flows and disk spilling now support the `INTERVAL` type. #45776
- Queries with `MERGE` join can now run via the vectorized engine when `vectorize` is set to `auto`. #45784
- Hash aggregation is now supported in `vectorize=auto` mode. #45832
- Added telemetry reporting for usages of inverted and hash-sharded indexes. #46060
- The statement tag returned to the client upon success for `CREATE USER`, `ALTER USER`, and `DROP USER` now include the word "ROLE" instead of "USER", for compatibility with PostgreSQL. These three statements are now aliases for `CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`. #46042
- `experimental_on` option for `vectorize` session variable has been renamed to `on`. The only things that will not run with `auto`, but will run with `on`, are unordered `DISTINCT` and two window functions (`percent_rank` and `cume_dist`). The two options are otherwise identical. #46080
- `NOTICE` commands can now be sent by CockroachDB servers using the Postgres client/server protocol. These notices will print when using the CockroachDB CLI. These notices can be disabled using the cluster setting `sql.notices.enabled = false`. #45679
- Fixed an internal error that could occur when `NULLIF` was called with one null argument. #45354
- Fix a bug where `EXPERIMENTAL SCRUB TABLE` on a timestamp/timestamptz key does not work. #45410
- Significantly reduced the amount of memory allocated while scanning tables with a large average row size. #45323
- Some vectorized execution plans that used lookup joins with decimals would previously return incorrect results. This is now fixed. #45536
- Previously, drivers that did not truncate trailing zeroes for decimals in the binary format end up having inaccuracies of up to 10^4 during the decode step. Fixed the error by truncating the trailing zeroes as appropriate. This fixes known incorrect decoding cases with Postgres in Elixir. #45613
- Previously, an internal error could occur in CockroachDB when executing queries via the vectorized engine in queries that contained unordered synchronizers. This has been fixed. #45690
- Fixed a bug where the distinct operation on `ARRAY[NULL]` and `NULL` could sometimes return an incorrect result and omit some tuples. #45229
- Previously, CockroachDB could crash when computing window functions with the `RANGE` mode of framing when one of the bounds was either `'offset PRECEDING'` or `'offset FOLLOWING'` and when there were `NULL` values in the single column from `ORDER BY` clause. Additionally, also in `RANGE` mode, bounds `'0 PRECEDING'` and `'0 FOLLOWING'` could be handled incorrectly. This is now fixed. #44666
- Verify column is now visible before accessing its datums. #45801
- Expected errors from the vectorized execution engine are no longer mistakenly annotated as unexpected errors. #45673
- Mixed type comparison or binary expressions that could have previously returned wrong results in the vectorized execution engine now fall back to using the row execution engine. #45724
- Fixed decimal rounding errors in the vectorized execution engine. #45950
- Fixed a bug where various session variables whose value would display as "`on`" or "`off`" could not be set to the values "`on`" or "`off`", only `true` or `false`. #46163
- Fixed a bug that caused transactions that have performed schema changes to deadlock after they restart. #46170

### Updates to schema change / DDL statements

- It is now possible to create a table and then add or alter a primary key within the same transaction. #46015
- The `ALTER TABLE .. ADD PRIMARY KEY ...` command can now be used when the target table has the default rowid primary key. #45514
- Fixed a bug where a table without a primary key and a column named `rowid` would throw an error when being created. #45507
- The use of schema changes when a primary key change is in progress is now disabled. This includes the following: (1) running a primary key change in a transaction and then starting another schema change in the same transaction, and (2) starting a primary key change on one connection and then starting a schema change on the same table on another connection while the initial primary key change is currently executing. #45397
- CockroachDB has now disabled primary key changes when a concurrent schema change is executing on the same table, or if a schema change has been started on the same table in the current transaction. #45513
- You can now drop a primary key as long as you add another primary key within the same transaction. This feature is intended to be used when you do not want the existing primary key to be rewritten as a secondary index by `ALTER PRIMARY KEY`. #44511
- The experimental variable gating around usages of online primary key changes is now removed. #45753
- Previously, when a database was renamed, any table referencing a sequence would be blocked from being able to rename the table. This is to block cases where if the table's reference to the sequence contains the database name, and the database name changes, we have no way of overwriting the table's reference to the sequence in the new database. However, if no database name is included in the sequence reference, we should continue to allow the database to rename, as is implemented with this change. #45502
- Previously, renaming a database with dependent views returned a misleading error message that implies it was a dependent view on a dependent table. Now the error message generically says `cannot rename relation ... as it depends on relation ...` instead. #45427
- Long-running transactions which attempt to `TRUNCATE` can now be pushed and will commit in cases where they previously could fail or retry forever. #44091
- It is now possible to create inverted indexes on columns whose names are mixed-case. #45621

### Updates to Bulk I/O statements

- Improved error messages when importing MySQL dump data. #45958
- `nodelocal://` URIs now require a node ID be specified in the hostname field. The special node ID of `'self'` is equivalent to the old behavior of when the node ID was unspecified. #45764
- Fixed cases where target column specifications in `IMPORT INTO` were ignored. #45747
- `IMPORT` now correctly handles columns named with reserved keywords and/or other special characters. #45944
- Google storage client is now resilient to transient connection errors. #46000
- The creation of a database and table between incremental cluster backups is now allowed. #46066
- Better error reporting when importing data. #46165

### Changes to background job management

- Schema changes are now scheduled and run fully like other jobs: they now can be canceled, paused, and resumed. Some other UI differences come with this implementation change; notably, all schema changes now have an associated job, failed schema changes are now rolled back within the "Reverting" phase of the same job, and GC for dropped indexes and tables is deferred to a later job. #45870
- Non-running `jobs` are now considered for adoption in randomized order instead of in a determistic order by creation time, to avoid potential deadlocks when schema change jobs need to execute in a specific order. This is a preemptive change, not a bug fix, but it affects all jobs. #45870
- On new clusters, the internal `system.jobs` table now uses the default `zoneconfig` and `TTL` (25h). #45767
- The cleanup job spawned by `ALTER .. PRIMARY KEY` in some cases cannot be cancelled. #45595
- Introduced a temporary table cleanup job that runs once every periodically per cluster. It removes any temporary schemas and their related objects that did not get removed cleanly when the connection closed. This period can be changed by the cluster setting `sql.temp_object_cleaner.cleanup_interval`, which defaults to 30 minutes. #45669
- Previously, after deleting an index, table, or database, the relevant schema change job would change its running status to waiting for GC TTL. The schema change and the GC process are now decoupled into two jobs. #45962
- Fixed a bug where, in some rare cases, a job was not cancellable when in state "Reverting". #45320
- When considering if a job should be orphaned, CockroachDB use to take the conservative approach when a descriptor ID pointing to non-existent descriptor was found. This caused jobs to hang forever and be garbage collected. CockroachDB now disregards these IDs when considering if a job has still work to do. #45353
- Logs emitted from jobs are now tagged with the job ID to improve visibility and aid debugging. #45728
- `IMPORT INTO` jobs which are canceled or fail can no longer get stuck in an unrecoverable state if data from the previous state of the table had expired relative to the GC TTL. #44739
- `CHANGEFEED` jobs that take a long time to perform backfills will no longer encounter failures due to garbage collection, so long as they begin before the data has expired. #45778

### Updates to APIs and introspection

- Telemetry reporting has been added for the commands `SHOW INDEXES`, `SHOW QUERIES`, `SHOW JOBS`, and `SHOW CONSTRAINTS`. #45897
- `SHOW USERS` and `SHOW ROLES` are now the same, as `USERS` is now an alias for `ROLES`. `SHOW USERS` and `SHOW ROLES` now match PostgreSQL `du` command. `SHOW ROLES` now displays three columns: `username`, `options`, and `member_of`. #45827
- HTTP endpoints now report status `403 (Forbidden)` instead of `500 (Internal server error)` when the authenticated user has insufficient privileges to use the endpoint. #45325
- The end point `/_status/job/{job_id}` will now display status info about a job. #45094
- The pprof endpoints now allow downloading the binary profiles. To do so, attach `?download=true` to the URL. #45790
- Improved the debuggability of C++-level issues by providing access to thread stack traces via a new `/debug/threads` endpoint, which is exposed on the Admin UI advanced debug page. Now include thread stack traces in the info collected by `debug zip`. Thread stack traces are currently only available on Linux. #45321
- Accesses to `/health` using a non-root authentication token no longer hang when a node is currently under load, or if a system range is unavailable. #45119
- Statement diagnostics traces now contain processor statistics. #46132

### Command-line changes

### Changes to operational workflows

- Hostnames from the `cockroach start --join` flag can now be resolved as SRV record. This simplifies cluster deployment in Kubernetes. #45815
- The `--decommission` flag for `cockroach quit` is now deprecated. It will be removed altogether in a next stable release. Deployments should use `cockroach node decommission` followed by either `cockroach quit` or an equivalent form of server shut down. #45903
- The `--socket` flag for `cockroach start` is now deprecated in favor of `--socket-dir`. CockroachDB now automatically chooses a name for the socket in the specified directory based on the configured port number. `--socket` will be removed in a later version. #45931
- Added the `--cert-principal-map` flag to `cockroach start`, which specifies a comma-separated list of `cert-principal`:`db-principal` mappings that map the principals found in certificates to DB principals. This allows the usage of "node" and "root" certificates where the common name contains dots or a host name, which allows such certificates to be generated by certificate authorities that place restrictions on the contents of the common name field. #45819

### Configuration changes

- Added a new `default` option for the `--storage-engine` flag that respects the engine used last. #45512

### Usability improvements

- Some CLI commands now provide more details and/or a hint when they encounter an error. #45575
- CockroachDB now refuses to start if named time zones are not properly configured. It is possible to override this behavior for testing purposes, with the understanding that doing so will cause incorrect SQL results and other inconsistencies, using the environment variable `COCKROACH_INCONSISTENT_TIME_ZONES`. #45640
- The `cockroach sql` and `cockroach demo` client commands now display out-of-band server notices at the end of execution. #46144 #46124
- When some result rows have already been received from the server when an error is encountered, the CockroachDB SQL shell now presents both the result rows and the error in the output, regardless of the selected table formatter. Previously, only the error was reported with some formatters, or both with other formatters. #45872
- Added the flag `--disable-demo-license` to provide another option to disable `cockroach demo` from attempting to acquire a demo license. #46126
- The parameter `--set` for `cockroach sql` and `cockroach demo` is now able to override all client-side options, as advertised. #46118
- Fixed a bug that caused `cockroach demo -e` to display a `connection refused` error. #46126

### Change to troubleshooting facilities

- The `cockroach debug zip` output now contains hex representation of marshaled jobs payload and progress as well table descriptors. This allows you to copy this string and unmarshal it when debugging. #45721
- CockroachDB will now dump the stacks of all goroutines upon receiving `SIGQUIT` prior to terminating. This feature is intended for use while troubleshooting misbehaving nodes. #36378

### Admin UI changes

- Removed mention of RocksDB from the Read Amplification, SSTables, Compactions/Flushes, and Compaction Queue graphs. #45398
- The display options are now saved separately for each authenticated user. Note: When upgrading to a version with this change, all current display customizations for admin users are lost. #45127
- Customizations of the Admin UI are again properly saved across sessions. #45127
- Refactored redux data flow in enqueue range. #45667
- Increased enqueue range timeout to an hour to prevent operations from timing out before completion. #45667
- The Admin UI now reports a clearer message when a non-admin user uses an admin-only feature. #45122
- Added the Statement Diagnostics History page. #45799
- You can now access the Activate Diagnostics dialog from the Statements page, through the Activate link. #45799
- Added a Release Notes subscription form on the Cluster Overview page. #45143
- The jobs status filter now includes "running", which was previously omitted by mistake. #45937

### Performance improvements

- Importing an Avro file is now faster. #45269
- Improved the execution plans of foreign key checks for `UPSERT` and `INSERT .. ON CONFLICT` in some cases (in particular multi-region). #45520
- Importing delimited data now has improved throughput. #45543
- Improved the selectivity estimation of some predicates containing `OR`, leading to better plan selection by the optimizer. #45732
- Improved cardinality estimation in the optimizer for relations with a small number of rows. This leads to the optimizer choosing a better query plan in some cases. #45771
- `crdb_internal.jobs` now loads less data into memory. #45914

### Doc updates

- Upgraded the search on the docs site. #6692
- Added docs for `--storage-engine` flag on node start, which can be `pebble`, `rocksdb`, or `default`, which makes the `--storage-engine` flag sticky for future runs when no engine is specified. #6769
- Added examples of `COMMENT ON TABLE` in `SHOW CREATE TABLE` output to `COMMENT ON`, `SHOW TABLES`, and `SHOW CREATE`. #6789
- Added the "Duplicate Indexes" Youtube video to the Duplicate Indexes Topology doc. #6796
- Updated the `cockroach demo` doc to include new flags. #6841

### Contributors

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

- Andrii Vorobiov
- Artem Barger
- Damien Hollis (first-time contributor)
- Jaewan Park
- Ziheng Liu (first-time contributor)
- pohzipohzi (first-time contributor)

## v20.1.0-beta.2

Release Date: March 2, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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 v20.1 beta release includes the ability to log slow SQL queries. By setting the `sql.log.slow_query.latency_threshold` cluster setting, each node of your cluster will log queries that exceed the specified service latency to a new file called `cockroach-sql-slow.log`.

### Security updates

- Operators can now disable external HTTP access when performing certain operations (`BACKUP`, `IMPORT`, etc.). The external HTTP access, as well as custom HTTP endpoints, are disabled by providing an `--external-io-disable-http` flag. This flag provides a light-weight option to disable external HTTP access in environments where running a full-fledged proxy server may not be feasible. If running a proxy server is acceptable, operators may choose to start the `cockroach` binary while specifying the `HTTP(S)_PROXY` environment variable. #44900

### General changes

- Added a slow query log facility to CockroachDB, configurable by setting the `sql.log.slow_query.latency_threshold` cluster setting. When used, each node of your cluster will record queries that exceed the specified service latency to a new file called `cockroach-sql-slow.log`. #44816
- New clusters will have a larger default range size of 512 MB, which will result in fewer ranges for the same amount of data. #45209

### Enterprise edition changes

- Row counts in `BACKUP` and `RESTORE` now include rows in system tables. #44965

### SQL language changes

- Disallowed changing the primary key of a table in the same transaction as its `CREATE TABLE` statement. #44815
- Introduced the ability to create views using `CREATE VIEW IF NOT EXISTS`, which does nothing if the view already existed. #44913
- If temporary table creation is enabled, users now have the ability to create temporary sequences as well. #44806
- Added built-in support of hash-sharded indexes with new `USING HASH WITH BUCKET_COUNT = <n>` syntax for indices (including the primary index of a table). This feature allows users to easily relieve write hot-spots caused by sequential insert patterns at the cost of scan time for queries over the hashed dimension. #42922
- Added support for primary key changes into hash sharded indexes. #44993
- Disabled creating a hash sharded index that is also interleaved. #44996
- An `UPDATE` returning a serialization failure error (code `40001`) now leaves behind a lock, helping the transaction succeed if it retries. This prevents starvation of transactions where an `UPDATE` is prone to conflicts. #44654
- Added a builtin function `getdatabaseencoding()`, which returns the current encoding name used by the database. #45129
- The SQL:2008 syntax `OFFSET <x> ROWS` and `FETCH FIRST <y> ROWS ONLY` now accept parameter values. #45112
- Disallowed primary key changes on tables that are currently undergoing a primary key change. #44784
- Added support for the aggregate function `corr()` #44628
- `INSERT..ON CONFLICT` index column names can now be specified in any order, rather than only in the same order as the index. #45280

### Command-line changes

- Previously, `cockroach debug zip` would only print an informational message about a piece of data it was retrieving after the data was retrieved (or an error was observed). This patch changes it to print a message beforehand as well. This enables better troubleshooting of hanging queries. #44342
- `cockroach debug zip` now properly supports special characters in database and table names. #44342
- `cockroach debug zip` will now apply the `--timeout` parameter to the SQL queries it performs (there was no timeout previously, causing `cockroach debug zip` to potentially hang). #44342
- `cockroach debug zip` is now able to tolerate more forms of cluster unavailability. Nonetheless, in case system ranges are unavailable, it is recommended to run `cockroach debug zip` towards each node address in turn to maximize the amount of useful data collected. #44342
- `cockroach debug zip` now includes secondary log files in the main log directory, for example the RocksDB logs. Log files in alternate log directories (e.g., `--sql-audit-dir`, if different from the main log directory) are not included. #45200

### Admin UI changes

- Changed Decommissioned Node History view to accommodate the case when there are no decommissioned nodes. #44205
- Changed styling of the Cluster Overview view. #44212
- Endpoint `/_status/registry/{node_id}` will now display status info about the jobs running on this `node_id`. #45030
- The "Log file list" endpoint now includes secondary log files in the main log directory, for example the RocksDB logs. Log files in alternate log directories (e.g., `--sql-audit-dir`, if different from the main log directory) are not included. #45200

### Bug fixes

- Fixed a bug where CockroachDB could return an internal error on the queries that return `INT` columns when the default integer size has been changed. #44930
- Fixed a bug where CockroachDB could crash when running `EXPLAIN (VEC)` in some edge cases. Now, an internal error is returned instead. #44931
- Fixed a bug where CockroachDB would return an internal error when the merge join operation was performed via the vectorized execution engine in a case when two sides of the join had comparable but different types in the equality columns (for example, `INT2` on the left and `INT4` on the right). #44942
- Fixed internal query errors in some cases involving negative limits. #45009
- Fixed a bug where the distinct operation in the row execution engine would fail to properly account for its memory usage, potentially leading to OOMs on large tables. #45254
- Correctly handle Avro byte datums when converting them to the expected string column families (such as `VARCHAR`, `CHAR`, etc.). #45242
- Fixed a potential error occurring when loading the movr dataset with a large number of rows in the promo codes column. #45035

### Performance improvements

- The cost-based-optimizer now generates faster execution plans in some cases that involve `IN` / `NOT IN` with an empty tuple (or `= ANY` with an empty array). #45170

### Doc updates

- Added a tutorial for developing and deploying a multi-region web application with Flask, SQLAlchemy, CockroachCloud, and Google Cloud Platform. #5732
- Added a Developer Guide that shows how to do common application development tasks in several languages: Go, Java, and Python. #6362
- Added information about how to access the Admin UI on secure clusters. #6640
- Overhauled the documentation on authorization, roles, and grants. #6332
- Added docs for troubleshooting node liveness. #6322
- Added docs for online primary key changes with `ALTER TABLE ... ALTER PRIMARY KEY`. #6513
- Added a tutorial for using PonyORM with CockroachDB. #6531
- Added a tutorial for using the jOOQ ORM with CockroachDB. #6684

### Contributors

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

- Andrii Vorobiov
- Artem Barger (first-time contributor)
- Jaewan Park
- abhishek20123g (first-time contributor)

## v20.1.0-beta.1

Release Date: February 17, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.1 beta release includes the following major highlights:

- Online primary key changes: You can now change a table’s primary key using the `ALTER TABLE ... ALTER PRIMARY KEY` statement. Changing a table’s primary key rewrites its primary and some secondary indexes behind-the-scenes and can take a while, but the table remains online with no interruption to data access. For now, this feature is considered experimental and is behind a cluster setting. To try it out, run `SET experimental_enable_primary_key_changes = true`. The syntax is `ALTER TABLE table_name ALTER PRIMARY KEY USING COLUMNS (x, y)`.
- Full cluster restore: You can now use CockroachDB's Enterprise `RESTORE` feature to restore a full cluster `BACKUP` to a new cluster, including all configuration and system information such as user privileges, zone configurations, and cluster settings. Restoring a cluster backup to an existing cluster is not supported.
- Encrypted backup files: You can now use an encryption key to encrypt data in Enterprise `BACKUP` files, and to decrypt the data upon `RESTORE`.

### Backward-incompatible changes

- `cockroach init` now waits for server readiness and thus no longer fails when a mistaken server address is provided. #43904
- The `cockroach user` CLI command has been removed. It was previously deprecated in CockroachDB 19.2. Note that a 19.2 client (supporting `cockroach user`) can still operate user accounts in a 20.1 server. #43903
- CockroachDB now creates files without read permissions for the "others" group. Sites that automate file management (e.g., log collection) using multiple user accounts now must ensure that the CockroachDB server and the management tools running on the same system are part of a shared unix group. #44043
- Previously, intervals cast to integers and floats would rely on a year being 365 days. To match `extract('epoch' from interval)` behavior in PostgreSQL/CockroachDB to 365.25 days, casting to integers and floats now values a year at 365.25 days in seconds instead of 365 days. #43923

### Security updates

- An admin user is now required to access statement details in the Admin UI and HTTP endpoint. Previously, any user could access these details, which could result in users accessing data that they didn't have privileges to see. #44349
- CockroachDB now properly rejects control characters in the value of the `server.host_based_authentication.configuration` cluster setting. Previously, these characters were accepted and would silently result in unintended rule matching. Deployments careful to strip control characters from their HBA configurations are not affected. #43811

- Connections using unix sockets are now subject to the HBA rules defined via the setting `server.host_based_authentication.configuration`, in a way compatible with PostgreSQL: incoming unix connections match `local` rules, whereas incoming TCP connections match `host` rules. The default HBA configuration used when the cluster setting is empty is now:

      host      all root all cert
      host      all all  all cert-password
      local     all all      password

  #43848

- Previously, when setting a user's password to enable password authentication for the user, it was not possible to revert this choice. The only way to disable password authentication was to either drop the user or add a specific per-user HBA rule. This has been fixed and the PostgreSQL-compatible `ALTER USER WITH PASSWORD NULL` statement can now be used to clear the user's password. #43892

- A CockroachDB node process (`start`/`start-single-node`) now configures its umask to create all its files without unix permission bits for "others", so that data/log/etc files do not become world-readable by default in systems that do not otherwise customize umask to enforce log file visibility. The files produced by other `cockroach` commands (e.g., the CLI commands) do not force their umask. Note that after upgrading to this release, in sites where permissions matter, administrators should be careful to run `chmod -R o-rwx` in directories where files were created by a previous version. #44043

- The new command `cockroach auth-session login` (reserved for administrators) creates authentication tokens with an arbitrary expiration date. Operators should be careful to monitor `system.web_sessions` and enforce policy-mandated expirations by either using SQL queries or the new command `cockroach auth-session logout`. #43872

- The `root` user can now have a password, like any other member of the admin role. However, as in previous versions, the HBA configuration cannot be overridden to prevent `root` from logging in with a valid TLS client certificate. This special rule remains enforced in order to ensure that users cannot "lock themselves out" of administrating their cluster. #43893

- The `root` user remains special with regard to authentication when some system ranges are unavailable. In that case, password authentication will fail, subject to a timeout set to the minimum of 5 seconds and the configured value of `system.user_login.timeout`, because the password cannot be retrieved from a system table. However, certificate authentication remains available. #43893

### General changes

- `SHOW JOBS` and the Jobs page in the Admin UI now show the parameters used for connecting to external storage, with only the values of parameters classified as secrets redacted. #44737
- It's now possible to disable job execution on a node in emergency cases. To do so, place a `DISABLE_STARTING_BACKGROUND_JOBS` file in the node's first store directory. #44786
- A node no longer declares itself unready through the `/health?ready=1` endpoint while in the process of decommissioning. It continues to declare itself unready while draining. #43889
- CockroachDB will now report a timeout error when a client attempts to connect via SQL or the Admin UI and some system ranges are unavailable. The previous behavior was to wait indefinitely. The timeout is configurable via the cluster setting `server.user_login.timeout` and is set to 10 seconds by default. The value "0" means "indefinitely" and can be used to restore the pre-v20.1 behavior. This timeout does not apply to the `root` user, which is always able to login on unavailable clusters. #44022
- The `kv.allocator.range_rebalance_threshold` cluster setting, which controls how far away from the mean a store's range count must be before it is considered for rebalance, is now subject to a 2-replica minimum. If, for example, the mean number of replicas per store is 5.6 and the setting is 5%, the store will not be considered for rebalance unless the number of replicas is lesser than 3 or greater than 8. Previously, the bounds would have been 5 and 6. #44247

### Enterprise edition changes

- Added the ability to restore a cluster backup to a new cluster, including all configuration and system information such as user privileges, zone configurations, and cluster settings. Restoring a cluster backup to an existing cluster is not supported. #43828
- Added support for encrypting `BACKUP`/`RESTORE` files via the `encryption_passphrase` option. #44177

### SQL language changes

- Foreign key checks for insertions performed by `UPSERT`s are now handled by the optimizer. #43824
- Added a rough estimation of execution progress to `SHOW QUERIES`. #42518
- Added `NOT NULL` columns as check constraints to `information_schema.table_constraints`, for PostgreSQL compatibility. #44731
- Added support for temporary view creation, if temporary tables are enabled. Temporary views disappear at the end of a connection. Views that depend on temporary tables are automatically temporary. #44729
- Added the `require_explicit_primary_keys` and `sql.defaults.require_explicit_primary_keys.enabled` cluster settings to control whether CockroachDB should error out when tables are created without explicit primary keys. #44702
- The `enable_primary_key_changes` cluster setting has changed to `experimental_enable_primary_key_changes`. #43818
- Primary key columns are no longer required to be in column family `0`. #43742
- Primary key changes are now enabled on tables with multiple column families. #43821
- The primary key of a table can now be altered to one that is interleaved in another table. #44038
- Primary key changes can now be performed on interleaved children. #44075
- Primary key changes are now enabled on tables that have foreign key relationships. #43830
- Extract can now be called on an interval (e.g., `extract(day from interval '254 days')`). This follows the PostgreSQL implementation. Furthermore, this deprecates `extract_duration`, which will be removed at a later date. #43293
- CockroachDB previously did not support `AT TIME ZONE` parsing for anything other than precise location strings (e.g., `AT TIME ZONE 'Australia/Sydney'`). CockroachDB now supports parsing `AT TIME ZONE` with various other offset behaviors supported by `SET TIME ZONE` (e.g., `AT TIME ZONE '+3'`, `AT TIME ZONE 'GMT+4'`). #43414
- CockroachDB now supports `SET TIME ZONE` with colons (e.g., `+4:00`). #43414
- Previously, `SELECT interval '1-2 1' DAY TO HOUR` would fail. This is now permitted as per the SQL standard. #43379
- Previously, spaces added to intervals with qualifiers (e.g., `SELECT interval ' 1 ' YEAR`) would be evaluated as seconds. The qualifier is now used as the multiplier. #43379
- Previously, adding a decimal point to days (e.g., `SELECT interval '1.5 01:00:00'`) would return `1 day 01:00:00`, unlike PostgreSQL, which returns `1 day 13:00:00`. The behavior now matches PostgreSQL. #43379
- Previously, using the `Y-M constant` format for intervals (e.g., `SELECT INTERVAL '1-2 3'`) would always resolve the constant component (3) as seconds, even for items such as `SELECT INTERVAL '1-2 3' DAY`. The behavior has been corrected and now matches PostgreSQL. #43379
- Some tools generate SQL that includes the `fillfactor` storage parameter, e.g., `CREATE TABLE ... WITH (fillfactor=100)`. This syntax is now supported, but has no effect, since the parameter has no meaning in CockroachDB. #43307
- `SHOW RANGES` now shows locality information consistent with the range descriptor when node ID and store ID do not match. #43807
- Ranges are now considered under-replicated by the `system.replication_stats` report when one of the replicas is unresponsive (or the respective node is not running). #43825
- `CREATE USER` and `ALTER USER` now accept the parameter `[WITH] PASSWORD NULL` to indicate the user's password should be removed, thus preventing them from using password authentication. This is compatible with PostgreSQL. #43892
- Previously, a panic could occur when a table had a default column and a constraint in the `CREATE TABLE` statement. This is now fixed. #43959
- Previously, `DECIMAL` types could not be sent over the network when the computation was performed by the vectorized engine. This has been fixed, and the vectorized engine now fully supports `DECIMAL` type. #43311
- Previously, there was a restriction that foreign keys could only reference one outbound column at any given point in time (e.g., in `CREATE TABLE test(a int)`, having two foreign keys on column `a` was not allowed). This restriction is now removed. #43417
- Invalid usages of `FOR UPDATE` locking clauses are now rejected by the SQL optimizer. #43887
- Added `to_hex(string)` string functionality. #44016
- Previously, `to_hex(-1)` would return `-1` instead of the negative hex representation (`FFFFFFFFFFFFFFFF`). This is now fixed. #44016
- The new global default cluster setting `sql.defaults.temporary_tables.enabled` can be used to enable temporary tables. #43816
- An optimization has been added to scan over only 1 row when finding the MIN/MAX of a single aggregate group, as long as the correct index is present. #43547
- `SHOW CREATE TABLE` now also emits the `COMMENT` statements sufficient to populate the table's user-defined comments, if any, alongside the `CREATE` statement proper. #43152
- More invalid usages of `FOR UPDATE` locking clauses are now rejected by the SQL optimizer. #44015
- Added the `timeofday` functionality supported by PostgreSQL, which returns the time on one of the nodes as a formatted string. #44050
- Added `localtime`, which by default returns the current time as the `TIME` data type (as opposed to `current_time`, which returns the `TIMETZ` data type). #44042
- Added `localtimestamp`, which by default returns the current timestamp as the `TIMESTAMP` data type (as opposed to `current_timestamp`, which returns the `TIMESTAMPTZ` data type). #44042
- Added support for having `D:H:M`, `D:M:S.fff`, or `D:H:M:S.fff` for interval parsing if the first element is a decimal or empty (e.g., `:04:05` and `1.0:04:05` would be `04:05:00` and `1 day 04:05:00` respectively). #43924
- Previously, floats were supported in `H:M:S` formats for interval parsing (e.g., `1.0:2.0:3.0`), which did not make sense. Floats are no longer allowed for the M field. #43924
- Previously, CockroachDB would return an internal error when using `SET tracing` with any type other than string. Now it will return a regular query error. Additionally, boolean arguments are now supported in `SET tracing`, and `true` is mapped to `on` mode of tracing whereas `false` is mapped to `off`. #44260
- Indexes that reference, or are referenced by, a foreign key constraint can now be dropped if there is another suitable index for the constraint. #43332
- Added a `log` builtin for any base (e.g., `log(2.0, 4.0)`). #41848
- Non-admin users can now query `SHOW JOBS` and `crdb_internal.jobs` and see their own jobs. #44345
- Vectorized execution engine now supports `DISTINCT` on unordered input. #42522
- `pg_catalog` access method information is now more accurate. Added inverted index to the access methods listed in `pg_am` and set `pg_class.relam` to zero for sequences and views, which is more consistent with PostgreSQL. #43715
- An overload has been added to the `unnest` builtin function in order to support multiple array arguments. #41557
- Duplicate labels are allowed when declaring a tuple, but a "column reference is ambiguous" error is now returned if a duplicate label is accessed (e.g.,`SELECT ((1, '2') AS a, a);` is successful, but `SELECT (((1,2,3) AS a,b,a)).a;` returns an error). #41557
- Telemetry information is now collected for uses of secondary indexes that use column families. #44506
- Telemetry information is now collected for uses of the `SHOW RANGE ... FROM ROW` command. #44502
- CockroachDB now supports `AT TIME ZONE` and the `timezone` builtin for `time` and `timetz` methods. #44099
- `AT TIME ZONE` now supports the POSIX standard. Offsets such as `UTC+3` and `+3` are interpreted to be timezones west of UTC instead of east of UTC (e.g., `America/New_York` is equivalent to `UTC+5` instead of `UTC-5`). #44099
- CockroachDB supports `timezone(timestamp(tz), str)`, but PostgreSQL supports the inverse `timezone(str, timestamp(tz))`. Both are now supported, and the former version will be deprecated at a later stage. - CockroachDB now supports `str AT TIME ZONE str`, removing the need for an explicit cast. #44099
- The vectorized execution engine now supports `bool_and`/`bool_or` builtin aggregation functions. #44164
- Non-admin users can now use the `ZONECONFIG` privilege to create, edit, and delete zone configurations. #43941

### Command-line changes

- When running `cockroach demo` with multiple nodes, each node now takes up to 128MB for SQL memory and 64MB for cache by default. Previously, each node would take up to 25% of total memory, which could cause OOM problems. These defaults can be modified via the `--max-sql-memory` and `--cache` flags. #44478
- Connections using unix sockets are now accepted even when the server is running in secure mode. Consult `cockroach start --help` for details about the `--socket` parameter. #43848
- The `cockroach init` command now waits until the node at the provided server address is ready to accept initialization. This also waits for network readiness. This makes it easier to implement initialization scripts by removing the need for a loop. In addition, implementing such a loop is operationally unsafe and is not recommended. #43904
- The MovR dataset will now be split among all nodes in the demo cluster. #43798
- `cockroach demo --with-load` can now round robin queries to all nodes in the demo cluster. #43474
- The SQLSmith workload now accepts an argument `error-sensitivity` which controls what types of errors the workload exits on. #43925
- `cockroach gen haproxy` now excludes decommissioned nodes. #43908
- The `cockroach node decommission` and `cockroach node recommission` commands now produce a warning on the standard error if one of the node(s) specified is already (d/r)ecommissioned. #43915
- The `start` and `start-single-node` commands no longer initiate a 1-minute hard shutdown countdown after a request to gracefully terminate. This means that graceful shutdowns are now free to take longer than one minute. It also means that deployments where a maximum shutdown time must be enforced must now use a service manager that is suitably configured to do so. #44074
- The new `cockroach auth-session login`, `cockroach auth-session list`, and `cockroach auth-session logout` commands are now provided to facilitate the management of web sessions. The command `auth-session login` also produces a HTTP cookie which can be used by non-interactive HTTP-based database management tools. It also can generate such a cookie for the `root` user, who would not otherwise be able to do so using a web browser. #43872

### Admin UI changes

- Decommissioned node history is now viewable on a dedicated page. This reduces the amount of information on the Cluster Overview page. #42817
- Execution Latency graph is now renamed to "KV Execution Latency". #43290
- Redesigned the Cluster Overview page. #43552
- We previously introduced a fix on the Admin UI to prevent non-admin users from executing queries. However, this inadvertently caused certain pages requiring table details not to display. This issue has now been resolved. #44167
- Non-admin users can now use the Jobs page of the Admin UI to see their own jobs. #44345

### Bug fixes

- When running a query with the `LIKE` operator using custom `ESCAPE` symbols, patterns containing Unicode characters no longer result in an internal error. #44633
- Fixed a server crash caused by some queries with outer joins and negative limits. #44590
- When cleaning up schema changes, CockroachDB no longer repeatedly looks for non-existing jobs, which could cause high memory usage. #44607
- Calling `NULLIF` with one null argument no longer results in an internal error. #44718
- Fixed a "no indexes" internal error in some cases when GROUP BY is used on a virtual table. #44692
- Fixed invalid query results in some cases involving stored columns with `NULL` values. #44728
- Fixed invalid query results in some cases where part of a `WHERE` clause is incorrectly discarded. #44668
- Fixed bugs around `cockroach dump` and `IMPORT`/`EXPORT` where columns of arrays or collated strings were not able to be roundtripped between `cockroach` and the dump. #44464
- `CASE` operators with an unknown `WHEN` type no longer return an error. #44756
- Fixed a type checking error where `BETWEEN` would sometimes allow boundary expressions of a different type. #44775
- Fixed a "cannot map variable" error in some rare cases involving joins. #44788
- Fixed a bug causing lost update transaction anomalies. #44507
- Fixed an occasional "concurrent map write" crash. #44872
- Fixed a bug where `DROP INDEX` jobs waiting for garbage collection might deleted before the data was actually removed from disk. #44831
- CockroachDB no longer returns an internal error when executing a `substring()` function with non-INT8 start and length arguments via the vectorized engine. #44887
- Fixed incorrect deduplication of impure expressions (e.g., `gen_random_uuid`) in projections and default values. #44890
- `TIMESTAMPTZ` operations now correctly take context timezone (set by `SET TIME ZONE`) into account. Previously, not doing so lead to bugs involving daylight saving in arithmetic. For example, with `America/Chicago`, evaluating `'2010-11-06 23:59:00-05'::timestamptz + '1 day'::interval` would return incorrect results as it assumed it was a fixed offset of `-5` instead. Also, text conversion from `TIMESTAMPTZ` TO `STRING` sometimes used the wrong timezone offset if the location of the session did not match the location when the `TIMESTAMPTZ` was parsed, and `to_json()` built-ins with `TIMESTAMPTZ` did not take session timezone into consideration. #44812
- Previously, when vectorized execution engine was used with `vectorize=experimental_on`, CockroachDB could incorrectly report some values as NULL. This has now been fixed. #43785
- When casting a string to bytes, octal escapes greater than `377` will now generate an error, rather than silently wrapping around. #43806
- A job can be running but shown in a pending state. #43814
- On Linux machines, we now respect the available memory limit as dictated by the cgroup limits which apply to the `cockroach` process. #43137
- Previously, CockroachDB would return incorrect results for some aggregate functions when used as window functions with non-default window frame. This is now fixed. Note that MIN, MAX, SUM, AVG, and "pure" window functions (i.e., non-aggregates) were not affected. #39308
- Previously, CockroachDB could return an internal error when running a query with a CAST operation (`:::`) in some cases when using the vectorized execution engine. This is now fixed. #43857
- Previously, a query shutdown mechanism could fail to fully cleanup the infrastructure when the query was executed via the vectorized engine and the query plan contained wrapped row-by-row processors (in v19.2, this applies to Lookup joins and Index joins). This is now fixed. #43579
- Fixed a bug introduced in v19.2 that would allow foreign keys to use a unique index on the referenced columns that indexed more columns than were included in the columns used in the FK constraint, which allows potentially violating uniqueness in the referenced columns themselves. #43793
- `RESTORE` cleanup is now run exactly once. #43933
- A benign error previously logged at the `ERROR` level is now logged at the `INFO` level behind a `verbosity(2)` flag. This error might have been observed as "context canceled: readerCtx in Inbox stream handler". #44020
- A bug causing lost update transaction anomalies was fixed. #42969
- Previously, an internal error could occur when a query with an aggregate function MIN or MAX was executed via the vectorized engine when the input column was either INT2 or INT4 type. This is now fixed. #43985
- CDC is no longer susceptible to a bug where a resolved timestamp might be published before all events that precede it have been published in the presence of a Range merge. #44035
- `cockroach debug zip` now emits the `goroutine` file in the proper sub-directory when the corresponding call fails with an error. #44064
- `cockroach debug zip` is again able to operate correctly and continue to iterate over all nodes if one of the nodes does not deliver its goroutine dumps. It would previously prematurely and incorrectly stop with an incomplete dump; this was a regression introduced in v19.2. #44064
- The file generated by `cockroach gen haproxy` no longer gets an executable bit. The executable bit was previously placed in error. #44043
- Fixed internal error of the form "x FK cols, only y cols in index" in some cases when inserting into a table with foreign key references. #44031
- CockroachDB now ensures internal cleanup after `IMPORT` is only run once. #43960
- Converted a panic in `golang.org/x/text/language/tags.go` when using collated strings to an error. #44103
- SQL mutation statements that target tables with no foreign key relationships now correctly read data as per the state of the database when the statement started execution. This is required for compatibility with PostgreSQL and to ensure deterministic behavior when certain operations are parallelized. Prior to this fix, a statement could incorrectly operate multiple times (i.e., the Halloween Problem) on data that itself was writing, and potentially never terminate. This fix is limited to tables without FK relationships, and for certain operations on tables with FK relationships; in other cases, the fix is not active and the bug is still present. A full fix will be provided in a later release. #42862
- CockroachDB now properly supports using `--url` with query options (e.g., `application_name`) but without specifying `sslmode`. The default of `sslmode=disable` is assumed in that case. This applies to the CLI commands that use SQL, including (but not limited to) `cockroach sql`, `cockroach node`, `cockroach auth-session`, and `cockroach debug zip`. #44113
- The GC process has been improved to paginate the key versions of a key to fix OOM crashes, which can occur when there are extremely large numbers of versions for a given key. #43862
- Removed statistics information from backup jobs' payload information to avoid excessive memory utilization when issuing commands such as `SHOW JOBS`. #44180
- Previously, CockroachDB could crash in special circumstances when using the vectorized execution engine with the `vectorize=experimental_on` setting. This is now fixed. #44144
- Fixed planning bug related to FULL joins between single-row relations. #44156
- Fixed "CopyFrom requires empty destination" internal error. #44114
- Fix a bug where multiple nodes attempted to populate the results for `CREATE TABLE ... AS` leading to duplicate rows. #43840
- All admin users are now allowed to use `BACKUP`/`RESTORE` and `IMPORT`. #44250
- `to_english(-2^63)` previously errored. This is now fixed to return the correct result. #44251
- Fixed internal error when mixed types are used with `BETWEEN`. #44216
- Fixed an error that could occur in very specific scenarios involving mutations and foreign keys. #44314
- Previously, CockroachDB would return an internal error when a query with CASE operator that returns only NULL values was executed via the vectorized engine. This is now fixed. #44346
- Fixed a bug when cascade deleting thousands of rows across interleaved tables. #44159
- Fixed incorrect plans in very rare cases involving filters that aren't constant folded in the optimizer but that can be evaluated statically when running a given query. #44307
- Fixed an internal error that could happen in the planner when table statistics were collected manually using `CREATE STATISTICS` for different columns at different times. #44430
- Fixed "no output column equivalent to" and "column not in input" errors in some cases involving `DISTINCT ON` and `ORDER BY`. #44543
- Fixed possibly incorrect query results in various cornercases, especially when `SELECT DISTINCT` is used. #44386
- Previously, CockroachDB would return an internal error when a `substring` function with a negative length was executed via the vectorized engine. This is now fixed (it now returns a regular query error). [#44627][#44627]

### Performance improvements

- Secondary indexes that store columns on tables with column families can now perform reads on only the needed columns in single row reads. #43567
- CockroachDB now uses better execution plans in some cases where there is an ordering on an expression that can be constant-folded to a simple column reference. #43724
- Histograms are now collected automatically for all boolean columns, resulting in better query plans in some cases. For tables that aren't being modified frequently, it might be necessary to run `CREATE STATISTICS` manually to see the benefit. #44151

### Build changes

- Building CockroachDB now requires Node.js version 12 or greater. #44024

### Doc updates

- Added a new Technical Advisories section with information about major issues with CockroachDB that may impact security or stability in production environments. #6492
- Added a tutorial on streaming an Enterprise changefeed from CockroachCloud to Snowflake. #6317
- Documented the `TIMETZ` data type. #6391
- Fixed the JavaScript code sample for connecting to a CockroachCloud cluster. #6393
- Clarified the behavior of default values when using `IMPORT INTO`. #6396
- Clarified the behavior of decommissioning in clusters of various sizes. #6402
- Documented `LATERAL` joins and subqueries. #6425
- Improved the Django "build an app" code sample. #6404, #6412
- Updated Change Data Capture examples to show more than one table in a changefeed. #6511

### Contributors

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

- Andrii Vorobiov
- George Papadrosou
- Jaewan Park
- Jason Brown
- Y.Horie (first-time contributor)

## v20.1.0-alpha.20200123

Release Date: January 30, 2020

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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 security updates and various enhancements and bug fixes, this v20.1 alpha release includes some major highlights:

- Cluster backup: You can now use CockroachDB's Enterprise `BACKUP` feature to back up an entire cluster's data, including configuration and system information such as user privileges, zone configurations, and cluster settings. At this time, you can restore individual databases and tables from a cluster backup. In a future release, you'll be able to restore an entire cluster as well.
- Fresher follower reads: Follower reads are now available for reads at least 4.8 seconds in the past, a much shorter window than the previous 48 seconds.
- Import from Avro format: You can now use the `IMPORT` and `IMPORT INTO` statements to bulk import SQL data from Avro files. This makes it easier to migrate from systems like Spanner that export data in the Avro format.
- Vectorized execution for `TIMESTAMPTZ`: Vectorized execution now supports the `TIMESTAMPTZ` data type in addition to several other previously supported data types.
- CockroachDB backend for Django: Developers using the Django framework can now leverage the `django-cockroachdb` adapter to run their Python apps on CockroachDB.

### Security updates

- The authentication code for new SQL connections has been simplified to always use the HBA configuration defined per `server.host_based_authentication.configuration`. The format of this file generally follows that of `pg_hba.conf`. This behavior remains equivalent to previous CockroachDB versions, and this change is only discussed here for clarity:

  Upon each configuration change, CockroachDB automatically inserts the entry `host all root all cert` as a first rule, to ensure the root user can always log in with a valid client certificate.

  If the configuration is set to empty or found to be invalid in the cluster setting, the following default configuration is automatically used:

      host all root all cert
      host all all  all cert-password

  At any moment the current configuration on each node can be inspected using the `/debug/hba_conf` URL on the HTTP endpoint. The list of valid authentication methods is currently:

  - `cert`, for certificate-based authentication over an SSL connection exclusively
  - `cert-password`, which allows either cert-based or password-based authentication over an SSL connection
  - `password` for password-based authentication over an SSL connection
  - `gss` for Kerberos-based authentication over an SSL connection, enabled when running a CCL binary and an Enterprise license

  In effect, CockroachDB treats all the `host` rules as `hostssl` and behaves as per a default of `hostnossl all all all reject`.

  It is not currently possible to define authentication rules over non-SSL connections. As of this writing, non-SSL connections are only possible when running with `--insecure`, and on insecure nodes all the authentication logic is entirely disabled. #43726

- CockroachDB now supports the authentication methods `'trust'` and `'reject'` in the cluster setting `server.host_based_authentication.configuration`. They are used to unconditionally allow and deny matching connection attempts. #43731

- Users `GRANT`ing and `REVOKE`ing admin roles must be members of the admin role with `ADMIN OPTION`. This check was previously bypassed. #41218

- Fixed a bug in the parsing logic for `server.host_based_authentication.configuration`, where both single-character strings, and quoted strings containing spaces and separated by commas were not properly parsed. This would cause rules for usernames consisting of a single character or usernames containing spaces to apply improperly. #43713

### General changes

- Added system tables `system.protected_ts_meta` and `system.protected_ts_records` to support the implementation of protected timestamps, a subsystem used to ensure that data required for long-running jobs is not garbage collected. #42829

### Enterprise edition changes

- Shortened the default interval for the `kv.closed_timestamp.target_duration` cluster setting from `30s` to `3s`, which allows for follower reads at 4.8 seconds in the past rather than the previous 48 seconds. #43147
- CockroachDB now supports importing Avro data. #43104
- Importing data into CockroachDB from external HTTP servers is now more resilient to connection interruption. #43374 #43558
- Added `BACKUP TO <location>`, which allows you to backup all relevant system tables as well as all user data in a cluster. #43767

### SQL language changes

- CockroachDB now provides a link to the relevant GitHub issue when clients attempt to use certain features that are not yet implemented. #42847
- Vectorized queries that execute only on supported types (even if those types form part of a table with unsupported types) are now run through the vectorized engine. This would previously fall back to the row-by-row execution engine. #42616
- CockroachDB now allows stored columns in secondary indexes to respect the column family table definitions that they are based on. #42073
- The error message reported when a client specifies a bulk I/O operation that uses an incompatible SQL function or operator now avoids the confusing and inaccurate term "backfill". This error is also now reported with code `22C01`. #42941
- The `CURRENT_TIME` function was added, which can be used with precision, e.g., `SELECT CURRENT_TIME, CURRENT_TIME(3)`. #42928
- `CREATE TABLE pg_temp.abc(a int)` now creates a temporary table. See temp tables RFC (guide-level explanation) for more details about the search path semantics. #41977
- A new boolean column `'is_inverted'` has been added to `crdb_internal.table_indexes` virtual table which indicates whether the index is inverted or not. #43102
- The output of `EXPLAIN` now shows joins where there are no equality columns as "cross" instead of "hash". Cross joins can be very expensive and should be avoided. #43061
- The error code for backups, which would overwrite files, changed from class 58 (`"system"`) to class 42 (`"Syntax or Access Rule Violation"`). #43221
- CockroachDB now allows the usage of `TIMETZ` throughout the cluster. #43023
- Column types are now be displayed in the box for the input synchronizer in the flow diagram obtained via `EXPLAIN (DISTSQL, TYPES)`. #43193
- CockroachDB now supports interval types with precision (e.g., `INTERVAL(5)`, `INTERVAL SECOND(5)`), and storing intervals with duration fields (e.g., `INTERVAL x TO y`). #43130
- When a session that has created temporary tables exits gracefully, the tables and temporary schema are now deleted automatically. #42742
- Foreign key checks that do not involve cascades are now performed after the mutation is complete, allowing self-referential foreign keys, or referential cycles. The execution plans for foreign key checks are now driven by the optimizer, which can make better planning decisions. In particular, if there is a suitable duplicated index, the one in the current locality will be used for foreign key checks. #43263
- Better estimates for the number of rows needed by `SELECT` and `DISTINCT` operations may now result in faster queries when the results of these queries are limited (e.g., `SELECT DISTINCT * FROM t LIMIT 10`). #42895
- `MINUTE TO SECOND` is now parsed as `MM:SS` instead of `HH:MM`. Additionally, interval syntax, such as `INTERVAL "01:02.123"`, is now parsed correctly as `MM:SS.fff`. This matches Postgres behavior. #43292
- Previously, CockroachDB returned error code `42830` and `23503` for duplicate foreign key names. It now returns `42710`, which matches Postgres. #43210
- Clients can now retrieve system user information from the `pg_authid` virtual table, which is Postgres-compatible. #43437
- The optimizer can now derive constant computed columns during index selection. This enables more efficient `HASH` indexes. #43450
- Vectorized engine now supports the `TIMESTAMPTZ` data type. #43514
- CockroachDB now provides more descriptive error messages and an error hint when an unsupported rule is provided via `server.host_based_authentication.configuration`. #43711 #43710
- Added an experimental prototype for altering the primary key of a table. #42462

### Command-line changes

- Added a `nodelocal` command that can be used to upload file: `cockroach nodelocal upload location/of/file destination/of/file`. #42966
- The `table` format, used to display the results of CLI shell queries, has been updated. #43728
- Telemetry is now recorded for whenever the command `cockroach demo` is used. #43795

### Admin UI changes

- Added page search and pagination to the Statements page. #41641
- A graph of changefeed restarts due to retryable errors is now included in the Admin UI. #43213

### Bug fixes

- Fixed a bug that caused some jobs to be left indefinitely in a pending state and never run. #42880
- Fixed the row count estimate during query planning for some queries with multiple predicates where the selectivity of one predicate was calculated using a histogram. #42916
- CockroachDB now more reliably produces an error message when a client mistakenly uses a directory name instead of a file name with `nodelocal://` in bulk I/O operations. #42542
- Fixed a bug where an error would occur when trying to export data using a `nodelocal://` URL. CockroachDB now properly handles cases where the system's temporary directory lives on a different filesystem from the external I/O directory. #42542
- CockroachDB now avoids using `$TMPDIR` (often set `/tmp`) during bulk I/O operations. This prevents errors occurring when the `$TMPDIR` disk capacity is small compared to the configured external I/O directory. #42542
- Temporary files created during certain bulk I/O operations are now properly deleted when an error occurs. This prevents left-over temporary files being retained in the system and leaking disk usage over time. #42542
- Empty arrays are now correctly encoded and decoded over the binary protocol. #42949
- CockroachDB now ensures that databases being restored are dropped if the `RESTORE` is canceled or fails. #42946
- Fixed a bug with some existing caching issues surrounding role memberships, where users could see out-of-date role membership information. #42998
- Fixed a bug where scanning an index of an unsupported type with the vectorized engine would lead to an internal error. #42999
- Fixed a bug where comparisons between `DATE` and `TIMESTAMP` vs. `TIMESTAMPTZ` broke because CockroachDB tried to normalize the `TIMESTAMPTZ` to UTC. CockroachDB now converts the `DATE` and `TIMESTAMP` to the `context` timezone and compares the `TIMESTAMPTZ` without altering its timezone. #42927
- Previously, CockroachDB did not handle `date` casts from `timestamp`/`timestamptz` with time attached to it for times before the unix epoch correctly. For example, `'1969-12-30 01:00:00'::timestamp` would round to `'1969-12-31'` instead of `'1969-12-30'`. This fix addresses that change. #42952
- Fixed a bug where `current_timestamp` did not correctly account for `SET TIME ZONE` in the background when storing results, and stored the timestamp as `UTC` instead. #43012
- The range rebalancing logic now considers stores with very close diversity scores equal (all other things being the same) and does not attempt to rebalance. #43041
- The range rebalancing logic now considers the new store being added when looking for target in case of rebalance. #43041
- Previously, gracefully terminating a node with `SIGINT` printed an error banner to the console. This was misleading, since the node responded to the signal correctly and terminated cleanly. This patch converts the error banner to a less-alarming informational message. #42848
- Fixed a bug that could lead to follower reads or CDC updates that did not reflect the full set of data at the timestamp. This bug was never observed in practice and should rarely cause issues, one of the necessary ingredients being an aggressive closed timestamp interval. #42939
- Fixed a bug where a well-timed write could slip in on the right-hand side of a range merge. This would allow it to improperly synchronize with reads on the post-merged range. #43138
- Previously, the optimizer could panic in a specific situation where it would prune all the columns of multiple scans of the same CTE and then try to define different required physical properties for each scan. This seems to have been a possible bug since the addition of multi-use CTEs in v19.2, but is hard to trigger without the not-yet-released `LimitHint` physical property. This patch makes all CTE scans uniquely identifiable, even after column pruning. #43161
- Some incorrect issue links referenced to by error hints have been corrected. #43232
- CockroachDB no longer fails on an expression of the form `NOT(a && b)`. #43242
- Improved support for `OID` column types in tables. #42973
- `EXPLAIN` can now be used with statements that use `AS OF SYSTEM TIME`. #43296
- Fixed an internal error that could be returned when performing `MIN`/`MAX` aggregation over a `STRING` column that contains `NULL` values when executed via the vectorized engine. Only the previous v20.1 alpha releases were affected. #43429
- Fixed an internal error that could occur when `CASE` operator operating on distinct although compatible types was executed via the vectorized engine. For example, a query similar to `SELECT CASE WHEN false THEN 0:::INT2 ELSE 1:::INT8 END` previously would error out. #43557
- CockroachDB now ensures that a transaction running into multiple intents from an abandoned conflicting transaction cleans them up more efficiently. #43563
- CockroachDB now writes less metadata about aborted transactions to disk. #42765
- The concept of lax constant functional dependencies was previously removed. There was a left-over case when a key is downgraded: if there was a strong empty key, the result is a lax empty key (which is no longer a concept). This change fixes this by removing the key altogether in this case. #43722
- It is now possible to perform `ALTER COLUMN SET/DROP NULL` on multiple (different) columns of the same table inside a single transaction. #43644
- CockroachDB now properly rejects `ALTER COLUMN DROP NOT NULL` on a column that is part of the primary key. #43644
- When the fourth column of a rule in the setting `server.host_based_authentication.configuration` is an IP address without a mask length (e.g., `1.2.3.4` instead of `1.2.0.0/16`), CockroachDB now properly interprets the fifth column as an IP netmask, as per https://www.postgresql.org/docs/current/auth-pg-hba-conf.html. #43779
- CockroachDB no longer tries to issue HTTP requests as part of an import once the import job has been canceled. #43789

### Performance improvements

- When resumed, paused imports now continue from their internally recorded progress instead of starting over. #42476 #43053
- Adjusted the optimizer's cost of lookup join when the lookup columns aren't a key in the table. This will cause some queries to switch to using a hash or merge join instead of a lookup join, improving performance in most cases. #43003
- Removed an unused field from Raft command `protobuf`, resulting in a 16% reduction in the overhead of each Raft proposal. #43042
- Range splits are now less disruptive to foreground reads. #43048
- CockroachDB now uses better execution plans when a `VALUES` clause is used as the right-hand side of `IN` or `ANY`. #43154
- The optimizer can now infer additional filter conditions in some cases based on transitive equalities between columns. #43194
- Improved the estimated row count for some lookup joins during planning, which can lead to a better plan. #43325
- The optimizer now generates better execution plans in some cases where there is an `ORDER BY` expression that simplifies to a simple variable reference. #43465

### Build changes

- Go 1.13.5 is now required to build CockroachDB from source. #43289

### Doc Updates

- Added a Django app development tutorial. #6359 #6365
- Updated the Hibernate app development tutorial with client-side retry logic. #5760
- Documented how to use keyset pagination to iterate through query results. #6114
- Improved the GSSAPI authentication instructions for configuring Active Directory and MIT and for configuring the client. #6115
- Expanded the Kubernetes tutorial to show how to use a custom CA instead of Kubernetes built-in CA when using manual configs. #6232
- Updated the Kubernetes tutorial for compatibility with Helm 3.0. #6121
- Added language-specific connection strings to the instructions on connecting to a CockroachCloud cluster. #6077
- Added Docker as a download option on the full release notes list. #5792
- Updated the `IMPORT` documentation with an example usage of `DELIMITED` with escaping, a note about `DEFAULT` values, and an explanation of the `strict_quotes` option. #6244
- Added an FAQ on why Cockroach Labs changed the license for CockroachDB. #6154
- Corrected the description of the possible result of clock skew outside the configured clock offset bounds. #6329
- Expanded the data types overview to indicate whether or not a type supports vectorized execution. #6327

### Contributors

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

- Akshay Shah (first-time contributor)
- Andrii Vorobiov
- Antoine Grondin
- Jason Brown (first-time contributor)

## v20.1.0-alpha20191216

Release Date: December 16, 2019

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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

- CockroachDB previously allowed non-authenticated access to privileged HTTP endpoints like `/_admin/v1/events`, which operate using `root` user permissions and can thus access (and sometimes modify) any and all data in the cluster. This security vulnerability has been patched by disallowing non-authenticated access to these endpoints and restricting access to admin users only.

  Note:

  Users who have built monitoring automation using these HTTP endpoints must modify their automation to work using an HTTP session token for an admin user.

- Some Admin UI screens (e.g., Jobs) were previously incorrectly displayed using `root` user permissions, regardless of the logged-in user's credentials. This enabled insufficiently privileged users to access privileged information. This security vulnerability has been patched by using the credentials of the logged-in user to display all Admin UI screens.

- Privileged HTTP endpoints and certain Admin UI screens require an admin user. However, `root` is disallowed from logging in via HTTP and it is not possible to create additional admin accounts without an Enterprise license. This is further discussed here and will be addressed in an upcoming patch revision.

  Note:

  Users without an Enterprise license can create an additional admin user using a temporary evaluation license, until an alternative is available. A user created this way will persist beyond the license expiry.

- Some Admin UI screens currently display an error or a blank page when viewed by a non-admin user (e.g., Table Details). This is a known limitation mistakenly introduced by the changes described above. This situation is discussed further here and will be addressed in an upcoming patch revision. The list of UI pages affected includes but is not limited to:

  - Job details
  - Database details
  - Table details
  - Zone configurations
  Note:

  Users can access these Admin UI screens using an admin user until a fix is available.

The list of HTTP endpoints affected by the first change above includes:

HTTP Endpoint | Description | Sensitive information revealed | Special (see below)
--- | --- | --- | ---
`/_admin/v1/data_distribution` | Database-table-node mapping | Database and table names |
`/_admin/v1/databases/{database}/tables/{table}/stats` | Table stats histograms | Stored table data via PK values |
`/_admin/v1/drain` | API to shut down a node | Can cause DoS on cluster |
`/_admin/v1/enqueue_range` | Force range rebalancing | Can cause DoS on cluster |
`/_admin/v1/events` | Event log | Usernames, stored object names, privilege mappings |
`/_admin/v1/nontablestats` | Non-table statistics | Stored table data via PK values |
`/_admin/v1/rangelog` | Range log | Stored table data via PK values |
`/_admin/v1/settings` | Cluster settings | Organization name |
`/_status/allocator/node/{node_id}` | Rebalance simulator | Can cause DoS on cluster | yes
`/_status/allocator/range/{range_id}` | Rebalance simulatoor | Can cause DoS on cluster | yes
`/_status/certificates/{node_id}` | Node and user certificates | Credentials |
`/_status/details/{node_id}` | Node details | Internal IP addresses |
`/_status/enginestats/{node_id}` | Storage statistics | Operational details |
`/_status/files/{node_id}` | Retrieve heap and goroutine dumps | Operational details | yes
`/_status/gossip/{node_id}` | Gossip details | Internal IP addresses | yes
`/_status/hotranges` | Ranges with active requests | Stored table data via PK values |
`/_status/local_sessions` | SQL sessions | Cleartext SQL queries | yes
`/_status/logfiles/{node_id}` | List of log files | Operational details | yes
`/_status/logfiles/{node_id}/{file}` | Server logs + entries | Many: names, application data, credentials, etc. | yes
`/_status/logs/{node_id}` | Log entries | Many: names, application data, credentials, etc. | yes
`/_status/profile/{node_id}` | Profiling data | Operational details |
`/_status/raft` | Raft details | Stored table data via PK values |
`/_status/range/{range_id}` | Range details | Stored table data via PK values |
`/_status/ranges/{node_id}` | Range details | Stored table data via PK values |
`/_status/sessions` | SQL sessions | Cleartext SQL queries | yes
`/_status/span` | Statistics per key span | Whether certain table rows exist |
`/_status/stacks/{node_id}` | Stack traces | Application data, stored table data |
`/_status/stores/{node_id}` | Store details | Operational details |

Note:

"Special" endpoints are subject to the cluster setting `server.remote_debugging.mode`. Unless the setting was customized, clients are only able to connect from the same machine as the node.

### Backward-incompatible changes

- The combination of the `CHANGEFEED` options `format=experimental_avro`, `envelope=key_only`, and `updated` is now rejected. This is because the use of `key_only` prevents any rows with updated fields from being emitted, which renders the `updated` option meaningless. #41793

### General changes

- Client usernames can now be defined to start with a digit; in particular, all-digit usernames are now permitted. #42464
- Changed the default value of the `--max-sql-memory` limit from 128mb to 25% of system memory. #42480
- Nodes that have been terminated as the result of a failed consistency check now refuse to restart, making it more likely that the operator notices that there is a persistent issue in a timely manner. #42401
- CockroachDB will now advertise some previously-hidden cluster settings, such as `enterprise.license`, in reports such as the one generated by `SHOW ALL CLUSTER SETTINGS`. Only the names are listed; the values are still redacted out. The values can be accessed/modified using the specific statements `SET`/`SHOW CLUSTER SETTING` (singular). #42520
- It is now possible to easily identify cluster settings for which tuning effects are known and documented, via the new column `public` in the output of `SHOW ALL CLUSTER SETTINGS` and the virtual table `crdb_internal.cluster_settings`. #42520

### Enterprise edition changes

- `RESTORE` now supports the restoration of empty databases. #42005

### SQL language changes

- Filters of the form `x = D` (as in `SELECT * FROM t WHERE x = D AND f(x)`), where `D` is a constant and `x` is a column name, will now cause `D` to be inlined for `x` in other filters. #42151
- The ID of the current session is now available via a `session_id` variable. Session IDs are also now shown in `SHOW QUERIES` results. #41622
- The `extract()` function now returns values of type float and includes fractional parts in the values for element 'second', 'millisecond', 'julian', and 'epoch'. This improves compatibility with PostgreSQL's `extract()` which returns values of type double precision. #42131
- `pg_index.indoption` now correctly conveys ascending/descending order and nulls the first/last positioning of columns in an index. #42343
- Updated pgwire to send `ParameterStatus` messages when certain server parameters are changed for the given session over pgwire. #42376
- Added the ability to run the `avg()` function over intervals. #42457
- It is now supported to specify selection target aliases as `GROUP BY` columns. Note that the `FROM` columns take precedence over the aliases, which are only used if there are no columns with those names in the current scope. #42447
- Updated the error message hint when a client attempts to add a sequence-based column to an existing table (which is an unimplemented feature) to refer to Github issue #42508. #42509
- CockroachDB now returns a more accurate error message, hint, and error code when an error is encountered while adding a new column. #42509
- `EXPLAIN (VERBOSE)` now indicates if auto-commit will be used for mutations. #42500
- Mutations in CTEs not at the top level are no longer allowed. This restriction is also implemented by Postgres. #41033
- `WITH` expressions are now hoisted to the top level in a query when possible. #41033
- Made the `date_trunc` function follow Postgres more closely by truncating to Monday when used with `week`. Previously, it truncated to Sunday. #42622
- Introduce precision support for `TIMESTAMP` and `TIMESTAMPTZ`, supporting precisions from 0 to 6 inclusive. Previous versions of `TIMESTAMP` and `TIMESTAMPTZ` defaulted to 6 units of precision. Note that if you downgrade while having a precision set, you will have full precision (6) again, but if you re-upgrade you will find your precisions truncated again. #42580
- `CREATE/ALTER SEQUENCE` now support the `OWNED BY` syntax. #40992
- Changed `extract()` on a `TIMESTAMPTZ` to match the environment's location in which `extract()` is executed. Previously, it would always perform the operation as if it was in UTC. Furthermore, `timezone`, `timezone_hour` and `timezone_minute` are added to the `extract()` command. #42632
- `CHANGEFEED` now supports a `WITH diff` option, which instructs it to include a `before` field in each publication. #41793
- The fields in the Avro format for `CHANGEFEED` records have been re-ordered to allow for optimized parsing. This is a backwards compatible change. #41793
- Users can now use the `current_timestamp()` function with a given precision from 0-6, e.g., `SELECT current_timestamp(4)`. #42633
- When executed via the vectorized engine, make each buffering operator use at most `sql.distsql.temp_storage.workmem` memory (which is 64MB by default). Previously, all buffering operators (like hash and merge joins, sorts) could use arbitrary amounts of memory which could lead to OOM panics. #42468
- Added a new statement `SHOW PUBLIC CLUSTER SETTINGS` (abbreviated as `SHOW CLUSTER SETTINGS`), which can be used to list only the public cluster settings that are supported for tuning and configuration. #42520
- Added the `kv.allocator.min_lease_transfer_interval` cluster setting, which allows the minimum interval between lease transfers initiated from each node to be configured. #42724
- Made string to interval conversion more strict. For example, strings such as `'&#123;&#123;'` and `'{1,2}'` were previously interpreted as the 00:00 interval. They are now rejected. #42739
- Some columns in `pg_type` (`typinput`, `typoutput`, `typreceive`, `typsend`, `typmodin`, `typmodout`, `typanalyze`) were incorrectly typed as `OID` instead of `REGPROC`. This issue has been resolved. #42782
- Users can now use the `cast()` function to cast strings to `int[]` or `decimal[]`, when appropriate. #42704
- `SET TIME ZONE` now accepts inputs beginning with `GMT` and `UTC`, such as `GMT+5` and `UTC-3:59`. This was previously unsupported. #42781
- It is now possible to reference tables by table descriptor ID in mutations using `INSERT`/`UPSERT`/`UPDATE`/`DELETE`, in a similar way to what is already allowed in `SELECT` statements. For example: `INSERT INTO [53 AS foo] VALUES (1, 2, 3)`. #42683
- Added new support for precision for `TIME` types (e.g., `TIME(3)` will truncate to milliseconds). Previously this would raise syntax errors. #42668

### Command-line changes

- Users can now use `cockroach demo` to shut down and restart nodes. This is available in `cockroach demo` only as `demo <decommission|recommission|shutdown|restart> <node_num>`. This command is not available in other CLIs, e.g., `cockroach sql`. This feature is experimental. #42230
- The various CLI commands that use SQL now display errors using a new display format that emphasizes the 5-digit `SQLSTATE` code. Users are encouraged to combine these codes together with the error message when seeking help or troubleshooting. #42779

### Admin UI changes

- Fixed typo that breaks statements page loading. #42577
- Certain web UI pages (like the list of databases or tables) now restrict their content to match the privileges of the logged-in user. #42563
- The event log now presents all cluster settings changes, unredacted, when an admin user uses the page. #42563
- Customization of the UI by users is now only properly saved if the user has write privilege to `system.ui` (i.e., is an admin user). Also, all authenticated users share the same customizations. This is a known limitation and should be lifted in a future version. #42563
- Access to table statistics are temporarily blocked from access by non-admin users until further notice, for security reasons. #42563
- Certain debug pages have been blocked from non-admin users for security reasons. #42563
- The cluster settings page now lists public and reserved settings in two separate tables. #42520
- Added a new range selector that supports custom time/date ranges. #41327

### Bug fixes

- Reduced the likelihood of out-of-memory errors during histogram collection. #42357
- Fixed a bug which could result in ranges becoming unavailable while a single node is unreachable. The symptoms of this would closely resemble that of a range that has lost a majority of replicas, i.e., the log files would likely include messages of the form "have been waiting [...] for proposing command", except that a majority will be available, though not reflected in the surviving replicas' range status. #42251
- Fixed a Makefile bug that would prevent building CockroachDB from sources in rare circumstances. #42363
- Fixed an out-of-memory error that could occur when collecting statistics on tables with a string index column. #42372
- Changed the return type of (date +- interval) and (interval + date) to be timestamp instead of timestamptz, to be in line with Postgres. Furthermore, this change fixed a bug where these calculations would be incorrect if the current timezone is not UTC. #42324
- Fixed a bug when using `experimental_save_rejected` for CSV `IMPORT` that would cause the rejected row file to overwrite the original input file. #42398
- For tables with dropped indexes, the `SHOW RANGE FOR ROW` command sometimes returned incorrect results or an error. Fixed the underlying issue in the `crdb_internal.encode_key` built-in function. #42456
- Fixed a bug in scenarios where we have `UPDATE` cascades, and we are updating a table that has `CHECK` constraints, and the table is self-referencing or is involved in a reference cycle. In this case an `UPDATE` that cascades back in the original table was not validated with respect to the `CHECK` constraints. #42231
- The movr workload now populates table columns with randomly generated data instead of nulls. #42483
- Fixed a bug where if a sequence is used by two columns of the same table, the dependency relation with the first column can be lost. #40900
- Fixed a bug where memory was leaking when counting rows during backup. #42529
- Fixed a bug where, if one were to cast the same type into two or more different precisions/widths from a table in the same `SELECT` query, they would only get the first precision specified. For example, `SELECT a::decimal(10, 3), a::decimal(10, 1) FROM t` would return both results as `a::decimal(10, 3)`. #42574
- CockroachDB will now be less likely hang in an inconvenient/inoperative state if it attempts to access an external HTTP server that blocks or is overloaded. A possible symptom of the bug is a node failing to shut down upon `cockroach quit`. This bug is present since at least version 2.0. #42536
- Stopped including tables that are being restored or imported as valid targets in backups and changefeeds. #42606
- Fixed a bug that would produce a spurious failure with the error message "incompatible `COALESCE` expressions" when adding or validating `MATCH FULL` foreign key constraints involving composite keys with columns of differing types. #42528
- When a custom `nullif` is provided during `IMPORT`, always treat it as a null value. #42635
- Changefeeds now emit backfill row updates for a dropped column when the table descriptor drops that column. #42053
- It's now possible to transfer range leases to lagging replicas. #42379
- Long running transactions which attempt to `TRUNCATE` can now be pushed and will commit in cases where they previously could fail or retry forever. #42650
- Fixed multiple existing bugs: a panic on performing cascade updates on tables with multiple column families; a bug where a self referential foreign key constraint with a `SET DEFAULT` would not be maintained on a cascading update; a bug where multiple self-referential foreign key constraints would cause all the rows in the referenced constraint columns to be set to NULL or a default value on a cascading update. #42624
- Fixed a case where we incorrectly determined that a query (or part of a query) which contains an `IS NULL` constraint on a unique index column returns at most one row, possibly ignoring a `LIMIT 1` clause. #42760
- Fixed a bug with incorrect handling of top K sort by the vectorized engine when K is greater than 1024. #42831
- `ALTER INDEX IF EXISTS` no longer fails when using an unqualified index name that does not match any existing index. Now it is a no-op. #42797
- Prevent internal error in some cases when a NULL literal is passed to the `OVERLAPS` operator. #42877
- CockroachDB now prevents a number of panics from the SQL layer caused by an invalid range split. These would usually manifest with messages mentioning encoding errors (including "found null on not null column", but also possibly various others). #42833
- The result column names for the JSON functions `json{b}_array_elements`, `json{b}_array_elements_text`, `json{b}_each`, `json{b}_each_text` were fixed to be compatible with Postgres. #41861
- Fixed a bug where selecting columns by forcing an `INTERLEAVING` index would error instead of returning the correct results. #42798
- Fixed a bug where attempting to parse `0000-01-01 00:00` when involving `time` did not work, as `pgdate` does not understand `0000` as a year. #42762

### Performance improvements

- Transactions are now able to refresh their read timestamp even after the partial success of a batch. #35140
- Some retryable errors are now avoided by declining to restart transactions on some false conflicts. #42236
- CockroachDB now detects the case when the right-hand side of an `ANY` expression is a NULL array (and determine that the expression is always false). #42698
- CockroachDB now generates better plans in many cases where the query has `LEFT` / `RIGHT JOIN`s and also has a `LIMIT`. #42718

### Build changes

- Go version 1.12.10+ is now required to build CockroachDB successfully. #42474
- `make buildshort` is now able to produce valid CCL binaries with all enterprise features (minus UI). #42541

### Contributors

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

- Adam Pantel (first-time contributor, CockroachDB team member)
- Ananthakrishnan (first-time contributor)
- Andrii Vorobiov (first-time contributor)
- George Papadrosou
- Jaewan Park
- Roga Pria Sembada
- Ryan Kuo (first-time contributor)
- Vlad
- georgebuckerfield (first-time contributor)

## v20.1.0-alpha.20191118

Release Date: November 18, 2019

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 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.

### General changes

- Guidance on how to contribute to CockroachDB has been moved to the public wiki at wiki.crdb.io. #41542
- Removed the `kv.bulk_io_write.addsstable_max_rate` cluster setting. #41745
- Improved the consistency checker's log output. #41893
- When the replicas within a range are found to be corrupted, the outliers will be terminated. Previously, the leaseholder replica would terminate, regardless of which replicas disagreed with each other. This is expected to curb the spread of corrupted data better than the previous approach. #41902

### Backward-incompatible changes

- The `extract()` built-in function with sub-second arguments (millisecond, microsecond) is now Postgres-compatible, and will return the total number of seconds in addition to sub-seconds. Anyone who was previously relying on `extract()` to return only sub-second data will need to adjust their applications. #41069

### Enterprise edition changes

- `IMPORT` statements now support specifying CSV filenames using wildcard characters. This behavior can be disabled with the `WITH disabled_glob_matching` option. #40714
- When using a `nodelocal` file URL for `IMPORT`/`BACKUP`/`RESTORE`/`EXPORT`, you can now specify which node's local file system to use by including the node's ID in the URL: `nodelocal://<nodeID>/path/file.csv`. #41990
- Added the new `WITH experimental_save_rejected` option for skipping faulty rows during `IMPORT`, saving the faulty rows in a file called `<original_csv_file>.rejected`. After fixing the problems in this file, you can use it with `IMPORT INTO`. #41430

### SQL language changes

- The `extract()` built-in function now supports millennium, century, decade, isoyear, isodow, and julian for `DATE`, `TIMESTAMP`, and `TIMESTAMPTZ`. The `date_trunct()` function now supports millennium, century, and decade for `DATE`, `TIMESTAMP`, and `TIMESTAMPTZ`. #41784
- `extract()` now supports a string constant as element argument. #41429
- Added support for the `JOIN LATERAL` syntax. #40945
- Added support for `WITH RECURSIVE` with `UNION ALL`. #41368
- Added support for `bit_and()` and `bit_or()` aggregate functions. #41334
- `NULL` values are now allowed to be among tuples when being compared against a tuple. #40298
- Added syntax-only support for `ORDER BY ... NULLS FIRST | LAST`. #41544
- Comments can now be associated to indexes with `COMMENT ON INDEX` and can be checked with `SHOW INDEXES FROM ... WITH COMMENT`. #41555
- `SELECT` and `HAVING` can now refer to ungrouped columns when the grouped columns contain the primary key of the table containing the ungrouped columns. #41732
- Dropping a unique index that was created via `CREATE UNIQUE INDEX` statement no longer requires the `CASCADE` option. #42001
- Added the `pg_prepared_statements` table. #42018

### Command-line changes

- The new `--storage-engine` flag for `cockroach start`, and equivalent `COCKROACH_STORAGE_ENGINE` environment variable, specify the type of storage engine a node should use. Options are `rocksdb` (default) and `pebble`. #41453
- Enhanced the error message produced by `cockroach init` when it encounters an already-initialized cluster to recommend adding `--join` to the `cockroach start` commands. #42318

### Bug fixes

- The Admin UI no longer mixes unit sizes in timeseries graph tooltips. #40970
- The `pg_collation_for()` function now correctly quotes its output. #41545
- Fixed an internal error when subqueries are used in arguments to commands like `SET`. #41581
- CockroachDB now properly emits the cluster ID, once known, to all secondary log files (SQL audit logging, statement execution logging, and RocksDB events) and properly removes excess secondary log files. #40993
- Other callers to `acquireNodeLease` will not get erroneously cancelled just because the context of the first caller was cancelled. #41785
- Vectorized execution no longer errors when adding an ordinality column to an expression with a limit. For example, `SELECT * FROM (SELECT * FROM foo LIMIT 1) WITH ORDINALITY` no longer throws an index out of range error. #41782
- Fixed a bug causing rare crashes when using built-in functions. #41970
- The `date_trunc()` function now correctly considers timezones for `TIMESTAMPTZ` and `DATE` types. #42006
- Fixed a bug causing `CREATE TABLE AS` statements to fail with the message "unexpected concurrency for a flow that was forced to be planned locally". #42013
- Fixed a bug where `SHOW ZONE CONFIGURATION` and `crdb_internal.zones` would show results for resources the user does not have permission to view. #42066, #42080
- Fixed a bug during planning for some queries that could cause an infinite loop and prevent the query from being cancelled. #42082
- Fixed a bug that caused jobs for dropping tables to report an inaccurate status. #42121
- Fixed a bug where rapid network disconnections could lead to cluster unavailability. #41533
- Fixed a stack overflow that could occur with certain patterns of queries. #41984
- Fixed some casts from `OID` to `TEXT`. #41928
- Fixed a bug where some cluster setting changes were not reflected during currently running `IMPORT`s. #42268
- Fixed bugs where: `<date>:date` would result in the previous day (`<date-1>::date>`) when context local timestamp is set and the timezone is less than UTC+00:00; `date_trunc()` for `TIMESTAMP` would produce incorrect results if a local timezone was set; and `date_trunc()` for `DATE` would produce an incorrect negative timezone offset in a local timezone. #42267
- Casting `TIMESTAMPTZ` data to the `TIME` type NOW properly respects time zone information. #42269
- Fixed a crash when using `EXPLAIN (VEC)` on some index joins. #40897

### Performance improvements

- Improved performance for some join queries due to improved filter inference during query planning. #41250
- Improved statistics estimation during query planning for columns with many `NULL` values. #41520
- The `cockroach debug check-store` command is now faster. #41805
- Improved the low-level performance of short range reverse scans. #42092
- Individual response messages in a response batch no longer each contain information about transaction state changes. #42139
- `BACKUP` work is now more evenly spread across clusters that have non-uniform leaseholder distributions. #42274

### Contributors

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

- Aayush Shah (first-time contributor)
- Andrea Sosso (first-time contributor)
- Arber Avdullahu (first-time contributor)
- Elliot Courant
- George Papadrosou
- Roga Pria Sembada (first-time contributor)
- Salvatore Tomaselli (first-time contributor)
- lzhfromustc (first-time contributor)
- sumeerbhola (first-time contributor)