# What's New in v21.1

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

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

Get future release notes emailed to you:

Thanks!

## v21.1.21

Release Date: September 15, 2022

### Command-line changes

- Added the `--log-config-vars` flag to the `cockroach` CLI, which allows for environment variables to be specified for expansion within the logging configuration file. This change allows for a single logging configuration file to service an array of sinks without further manipulation of the configuration file. #85173

### Contributors

This release includes 1 merged PR by 2 authors.

## v21.1.19

Release Date: May 9, 2022

### Enterprise edition changes

- Fixed a bug where backups in the base directory of a Google Storage bucket would not be discovered by `SHOW BACKUPS`. These backups will now appear correctly. #80509

### SQL language changes

- Previously, a user could run an `AS OF SYSTEM TIME` incremental backup with an end time earlier than the previous backup's end time, which could lead to an out of order incremental backup chain. An incremental backup will now fail if the `AS OF SYSTEM TIME` is less than the previous backup's end time. #80500

### DB Console changes

- Added a dropdown filter on the Node Diagnostics page to view by active, decommissioned, or all nodes. #80340
- Added an alert banner on Overview list page for staggered node versions #80748

### Bug fixes

- Fixed a bug that caused an internal error when the inner expression of a column access expression evaluated to `NULL`. For example, evaluation of the expression `(CASE WHEN b THEN ((ROW(1) AS a)) ELSE NULL END).a` would error when `b` is `false`. This bug has been present since v19.1 or earlier. #79527
- Fixed a bug that caused an error when accessing a named column of a labelled tuple. The bug only occurred when an expression could produce one of several different tuples. For example, `(CASE WHEN true THEN (ROW(1) AS a) ELSE (ROW(2) AS a) END).a` would fail to evaluate. Although present in previous versions, it was impossible to encounter due to limitations that prevented using tuples in this way. #79527
- Addressed an issue where automatic encryption-at-rest data key rotation would get disabled after a node restart without a store key rotation. #80171
- The timeout when checking for Raft application of upgrade migrations has been increased from 5 seconds to 1 minute, and is now controllable via the cluster setting `kv.migration.migrate_application.timeout`. This makes migrations much less likely to fail in clusters with ongoing rebalancing activity during upgrade migrations. #80754
- Fixed a bug where, in rare circumstances, CockroachDB could incorrectly evaluate queries with `ORDER BY` clause when the prefix of ordering was already provided by the index ordering of the scanned table. #80732
- Fixed a goroutine leak when internal rangefeed clients received certain kinds of retryable errors. #80795

### Contributors

This release includes 18 merged PRs by 13 authors.

## v21.1.18

Release Date: April 12, 2022

### Bug fixes

- Fixed a bug where restores of data with multiple column families could be split illegally (within a single SQL row). This could result in temporary data unavailability until the ranges on either side of the invalid split are merged. #79207
- Fixed a bug where `SHOW SCHEMAS FROM <schema>` would not include user-defined schemas. #79306
- Previously, `LIMIT` queries with an `ORDER BY` clause which scan the index of a virtual system tables, such as `pg_type`, could return incorrect results. This has been corrected by teaching the optimizer that `LIMIT` operations cannot be pushed into ordered scans of virtual indexes. #79466
- Fixed a bug that caused the optimizer to generate invalid query plans which could result in incorrect query results. The bug, present since version v21.1.0, can appear if all of the following conditions are true:
  - The query contains a semi-join, e.g., with the format `SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE a.a @> b.b)`.
  - The inner table has a multi-column inverted index containing the inverted column in the filter.
  - The index prefix columns are constrained to a set of values via the filter or a `CHECK` constraint, e.g., with an `IN` operator. In the case of a `CHECK` constraint, the column is `NOT NULL`. #79508

### Contributors

This release includes 12 merged PRs by 10 authors.

## v21.1.17

Release Date: April 4, 2022

### Security updates

- Users can enable HSTS headers to be set on all HTTP requests, which force browsers to upgrade to HTTPS without a redirect. This is controlled by setting the `server.hsts.enabled` cluster setting, which is `false` by default, to `true`. #77863

### Enterprise edition changes

- Currently executing schedules are cancelled immediately when the jobs scheduler is disabled. #77314

### SQL language changes

- Added a `sql.auth.resolve_membership_single_scan.enabled` cluster setting, which changes the query for an internal role membership cache. Previously the code would recursively look up each role in the membership hierarchy, leading to multiple queries. With the setting on, it uses a single query. This setting is `false` by default. #77624

### Operational changes

- The `cockroach debug tsdump` command now downloads histogram timeseries it silently omitted previously. #78054

### Command-line changes

- The `cockroach debug tsdump` command now allows viewing timeseries data even in cases of node failure by allowing users to rerun the command with the import filename set to `"-"`. #78057
- Fixed a bug where starting `cockroach demo` with the `--global` flag would not simulate latencies correctly when combined with the `--insecure` flag. #78173

### Bug fixes

- Fixed a bug where draining nodes in a cluster without shutting them down could stall foreground traffic in the cluster. #77494
- Fixed a bug that caused errors when attempting to create table statistics with `CREATE STATISTICS` or `ANALYZE` for a table containing an index which indexed only virtual computed columns. #77566
- Added a limit of seven concurrent asynchronous consistency checks per store, with an upper timeout of one hour. This prevents abandoned consistency checks from building up in some circumstances, which could lead to increasing disk usage as they held onto Pebble snapshots. #77612
- Fixed a bug where the Details page was not loading for statements whose app name contains `/` were not properly loading their Details pages. #77946
- Fixed a memory leak in the Pebble block cache. #78262
- Fixed a bug that caused internal errors when `COALESCE` and `IF` expressions had inner expressions with different types that could not be cast to a common type. #78345
- Fixed a bug that caused errors when trying to evaluate queries with `NULL` values annotated as a tuple type, such as `NULL:::RECORD`. #78638
- Fixed a bug that caused the optimizer to generate invalid query plans which could result in incorrect query results. The bug, which has been present since version v21.1.0, can appear if all of the following conditions are true: 1) the query contains a semi-join, such as queries in the form: `SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.a = t2.a);`, 2) the inner table has an index containing the equality column, like `t2.a` in the example query, 3) the index contains one or more columns that prefix the equality column, and 4) the prefix columns are `NOT NULL` and are constrained to a set of constant values via a `CHECK` constraint or an `IN` condition in the filter. #78976

### Contributors

This release includes 20 merged PRs by 15 authors.

## v21.1.16

Release Date: March 7, 2022

### Bug fixes

- Fixed a bug that caused the query optimizer to omit join filters in rare cases when reordering joins, which could result in incorrect query results. This bug was present since v20.2. #76621
- Fixed a bug where some rows within query results could be omitted if the query references a column within a composite key with a null value. Previously, CockroachDB could incorrectly omit a row from query results against a table with multiple column families when that row contains a NULL value when a composite type (FLOAT, DECIMAL, COLLATED STRING, or any arrays of such a type) is included in the PRIMARY KEY. For the bug to occur, a composite column from the PRIMARY KEY must be included in any column family other than the first one. #76637
- Fixed a race condition that, in rare circumstances, could cause a node to panic with `unexpected Stopped processor` during shutdown. #76828
- There is now a 1 hour timeout when sending Raft snapshots to avoid stalled snapshot transfers. Stalled snapshot transfers could prevent Raft log truncation, thus growing the Raft log very large. This timeout is configurable via the `COCKROACH_RAFT_SEND_SNAPSHOT_TIMEOUT` environment variable. #76830
- `CASE` expressions with branches that result in types that cannot be cast to a common type now result in a user-facing error instead of an internal error. #76618
- A bug has been fixed that could corrupt indexes containing virtual columns or expressions. The bug only occurred when the index's table had a foreign key reference to another table with an `ON DELETE CASCADE` action, and a row was deleted in the referenced table. This bug was present since virtual columns were added in v21.1.0. #77057

### Performance improvements

- Fixed a bug in the histogram estimation code that could cause the optimizer to think a scan of a multi-column index would produce 0 rows, when in fact it would produce many rows. This could cause the optimizer to choose a suboptimal plan. This bug has now been fixed, making it less likely for the optimizer to choose a suboptimal plan when multiple multi-column indexes are available. #76557
- The accuracy of histogram calculations for `BYTES` types has been improved. As a result, the optimizer should generate more efficient query plans in some cases. #76797

### Contributors

This release includes 10 merged PRs by 6 authors.

## v21.1.15

Release Date: February 14, 2022

This page lists additions and changes in v21.1.15 since v21.1.14.

### Enterprise edition changes

- Kafka sinks now support larger messages, up to 2GB in size. #76322

### SQL language changes

- Non-admin users can now use the `SHOW RANGES` statement if the `ZONECONFIG` privilege is granted. #76072
- `ST_MakePolygon` is now disallowed from making empty polygons from empty linestrings. This is not allowed in PostGIS. #76256

### Bug fixes

- Mixed dimension linestrings are now prohibited in `ST_MakePolygon`. #76256
- Fixed a bug which could cause nodes to crash when truncating abnormally large Raft logs. #75980

### Contributors

This release includes 6 merged PRs by 6 authors.

## v21.1.14

Release Date: February 9, 2022

### SQL language changes

- Collated strings may now have a locale that is a language tag, followed by a `-u-` suffix, followed by anything else. For example, any locale with a prefix of `en-US-u-` is now considered valid. #74754

### Command-line changes

- Fixed the CLI help text for `ALTER DATABASE` to show correct options for `ADD REGION` and `DROP REGION`, and include some missing options such as `CONFIGURE ZONE`. #75075

### Bug fixes

- Fixed a panic when attempting to access the hottest ranges (e.g., via the `/_status/hotranges` endpoint) before initial statistics had been gathered. #74514
- Servers no longer crash due to panics in HTTP handlers. #74533
- Previously, running `IMPORT TABLE ... PGDUMP DATA` with a `COPY FROM` statement in the dump file that had fewer target columns than the inline table definition would result in a nil pointer exception. This is now fixed. #74452
- Previously, a doubly nested `ENUM` in a DistSQL query would not be hydrated on remote nodes, resulting in panic. This is now fixed. #74527
- Error messages produced during import are now truncated. Previously, import could potentially generate large error messages that could not be persisted to the jobs table, resulting in a failed import never entering the failed state and instead retrying repeatedly. #73335
- Fixed a bug where deleting data via schema changes (e.g., when dropping an index or table) could fail with a `command too large` error. #74797
- Fixed panics that were possible in some distributed queries using `ENUM`s in join predicates. #75086
- Fixed a bug which caused errors in rare cases when trying to divide `INTERVAL` values by `INT4` or `INT2` values. #75091
- Fixed a bug that caused internal errors when altering the primary key of a table. The bug was only present if the table had a partial index with a predicate that referenced a virtual computed column. This bug was present since virtual computed columns were added in v21.1.0. #75193
- Fixed a bug that could occur when a `TIMETZ` column was indexed, and a query predicate constrained that column using a `<` or `>` operator with a `TIMETZ` constant. If the column contained values with time zones that did not match the time zone of the `TIMETZ` constant, it was possible that not all matching values could be returned by the query. Specifically, the results may not have included values within one microsecond of the predicate's absolute time. This bug was introduced when the `TIMETZ` datatype was first added in v20.1. It exists on all releases of v20.1, v20.2, v21.1, and v21.2 prior to this patch. #75173
- Fixed an internal error, `estimated row count must be non-zero`, that could occur during planning for queries over a table with a `TIMETZ` column. This error was due to a faulty assumption in the statistics estimation code about ordering of `TIMETZ` values, which has now been fixed. The error could occur when `TIMETZ` values used in the query had a different time zone offset than the `TIMETZ` values stored in the table. #75173
- Previously, during restore, CockroachDB would not insert a `system.namespace` entry for synthetic public schemas. This is now fixed. #74760
- `RESTORE ... FROM LATEST IN` now works to restore the latest backup from a collection without needing to first inspect the collection to supply its actual path. #75437
- Fixed a bug that caused internal errors in queries with set operations, like `UNION`, when corresponding columns on either side of the set operation were not the same. This error only occurred with a limited set of types. This bug is present in v20.2.6+, v21.1.0+, and v21.2.0+. #75294
- The `CancelSession` endpoint now correctly propagates gateway metadata when forwarding requests. #75886

### Contributors

This release includes 27 merged PRs by 16 authors.

## v21.1.13

Release Date: January 10, 2022

### SQL language changes

- `"visible"` is now usable as a table or column name without extra quoting. #72996
- The `create_type_statements` table now has an index on `descriptor_id`. #73672
- Added a new `stmt` column to the `crdb_internal.(cluster|node)_distsql_flows` virtual table. This column is populated on a best-effort basis. #73582
- The cluster setting that controls the default value for the session setting `reorder_joins_limit`, called `sql.defaults.reorder_joins_limit`, is now public and included in the docs. #73891
- Added escape character processing to constraint span generation. Previously, escaped-like lookups resulted in incorrect results. #74258

### Operational changes

- Added a new `bulkio.ingest.flush_delay` cluster setting to act as a last-resort option to manually slow bulk-writing processes if needed for cluster stability. This should only be used if there is no better suited back-pressure mechanism available for the contended resource. #73757

### DB Console changes

- Fixed drag-to-zoom on custom charts. #72589
- Node events will now display a permission error rather than an internal server error when a user does not have admin privileges to view events. #72464
- The absolute links on the Advanced Debug page within the DB Console have been updated to relative links. This will enable these links to work with the superuser dashboard in the Cloud Console. #73122

### Bug fixes

- Fixed a bug which allowed computed columns to also have `DEFAULT` expressions. #73192
- Fixed a bug causing `RESTORE` to sometimes map OIDs to invalid types in certain circumstances containing user-defined types. #73120
- `BACKUP WITH revision_history` would previously fail on an upgraded but un-finalized cluster. Now, it should succeed. #73110
- Fixed a bug causing CockroachDB to not set the `TableOID` and `TableAttributeNumber` attributes of `RowDescription` message of pgwire protocol in some cases (these values would be left as 0). #72449
- Fixed a bug causing CockroachDB to encounter an internal error or crash when some queries involving tuples with `ENUM` values were executed in a distributed manner. #72481
- Fixed a bug causing usernames in `ALTER TABLE ... OWNER TO` to not be normalized to lower case. #72658
- Previously, `atttypmod` in `pg_catalog.pg_attributes` for `DECIMAL` types with precision but no width was incorrectly "-1". This is now populated correctly. #72075
- Corrected how `type` displays for ZM shapes `geometry_columns` to match PostGIS output. This previously incorrectly included the Z/M lettering. #71434
- Corrected how `type` displays in `geography_columns` to better match PostGIS. This was previously in the wrong casing. #71434
- The `setval` built-in function previously did not invalidate cached sequence values. This is fixed now. #71822
- Fixed a bug preventing tuple type labels from being propagated across queries when run under DistSQL. #70391
- Fixed a bug whereby setting the `CACHE` for a sequence to 1 was ignored. Before this change `ALTER SEQUENCE ... CACHE 1` would succeed but would not modify the cache value. #71448
- When using `COPY FROM .. BINARY`, the correct format code will now be returned. #69255
- Fixed a bug causing `COPY FROM ... BINARY` to return an error if the input data was split across different messages. #69255
- Fixed a bug causing `COPY FROM ... CSV` to require each `CopyData` message to be split at the boundary of a record. This was a bug since the `COPY` protocol allows messages to be split at arbitrary points. #69255
- Fixed a bug causing `COPY FROM ... CSV` to not correctly handle octal byte escape sequences such as `\011` when using a `BYTEA` column. #69255
- Manually enqueueing ranges via the DB Console will no longer crash nodes that contain an uninitialized replica for the enqueued range. #73038
- Fixed a crash with message "attempting to propose command writing below closed timestamp" that could occur, typically on overloaded systems experiencing non-cooperative lease transfers. #73166
- The `txnwaitqueue.pusher.waiting` metric no longer over-reports the number of pushing transactions in some cases. #71743
- Fixed a rare condition that could cause a range merge to get stuck waiting on itself. The symptom of this deadlock was a goroutine stuck in `handleMergeInProgressError` for tens of minutes. #72049
- Fixed bugs causing `CREATE TABLE AS` and `CREATE MATERIALIZED VIEW` to panic if the `SELECT` query was an internal table requiring internal database state. #73620
- Fixed a rare internal error, "estimated row count must be non-zero", which could occur when planning queries using a GIN index. This error could occur if the histogram on the GIN index showed that there were no rows. #73353
- Fixed an internal error, "empty Datums being compared to other", that could occur during planning for some `SELECT` queries over tables that included a `DEFAULT` partition value in a `PARTITION BY LIST` clause. This bug had been present since 21.1.0. The bug does not exist on versions 20.2.x and earlier. #73663
- Fixed a bug in database and schema restore cleanup that resulted in a dangling descriptor entry on job failure. #73412
- Fixed a bug with the ungraceful shutdown of distributed queries in some rare cases. #73959
- Fixed a bug causing CockroachDB to return a spurious "context canceled" error for a query that actually succeeded in extremely rare cases. #73959
- Fixed a bug which caused corruption of partial indexes, which could cause incorrect query results. The bug was only present when two or more partial indexes in the same table had identical `WHERE` clauses. This bug had been present since version 21.1.0. #74475

### Performance improvements

- Improved `IMPORT INTO` performance in cases where it encounters large numbers of unresolved write intents. #72272
- The performance of transaction deadlock detection is now more stable even with significant transaction contention. #71743
- Bulk ingestion of small write batches (e.g., index backfill into a large number of ranges) is now throttled, to avoid buildup of read amplification and associated performance degradation. Concurrency is controlled by the new cluster setting `kv.bulk_io_write.concurrent_addsstable_as_writes_requests`. #74072

### Contributors

This release includes 52 merged PRs by 26 authors.

## v21.1.12

Release Date: November 15, 2021

### Security updates

- Added the `--external-io-enable-non-admin-implicit-access` flag to cluster-starting `cockroach` commands. This flag removes the admin-only restriction on interacting with arbitrary network endpoints and allows implicit authorization for operations such as `BACKUP`, `IMPORT`, or `EXPORT`. #71794

### SQL language changes

- The `pg_index` table now populates the `indpred` column for partial indexes. This column was previously `NULL` for partial indexes. #70897
- Added `crdb_internal` tables `cross_db_references` and `interleaved` for detecting cross-database references and interleaved objects. #72298
- Statements with multiple `INSERT ON CONFLICT`, `UPSERT`, `UPDATE`, or `DELETE` subqueries that modify the same table are now disallowed, as these statements can cause data corruption if they modify the same row multiple times. At the risk of data corruption, you can allow these statements by setting the `sql.multiple_modifications_of_table.enabled` cluster setting to `true`. To check for corruption, use the `EXPERIMENTAL SCRUB` command. For example: `EXPERIMENTAL SCRUB TABLE t WITH OPTIONS INDEX ALL;`. #71621

### Operational changes

- `IMPORT` now allows non-admin access to some previously-restricted network endpoints on clusters started with the `--external-io-enable-non-admin-implicit-access` flag. #72444
- A new implementation of `BACKUP` file handling is now available with the cluster setting `bulkio.backup.experimental_21_2_mode.enabled` set to `true`. #71830

### DB Console changes

- Non-admin users of the DB Console now have the ability to view the Cluster Overview page. Users without the admin role can see data about their nodes, but information such as command line arguments, environment variables, and node IP addresses and DNS names is hidden. #71719
- Replicas waiting for garbage collection were preventing the Range Report page from loading due to a JavaScript error. The page now loads and displays an empty "Replica Type" while in this state. #71745

### Bug fixes

- Fixed a bug causing CockroachDB to encounter an internal error when executing a zigzag join in some cases. #71255
- Fixed a bug causing CockroachDB to incorrectly read the data of a unique secondary index that used to be a primary index that was created via `ALTER PRIMARY KEY` in 21.1.x or prior versions. #71587
- CockroachDB now avoids dialing nodes in performance-critical code paths, which could cause substantial latency when encountering unresponsive nodes (e.g., when a VM or server is shut down). #70488
- Fixed a bug causing the TPC-C workload to improperly assign workers to the local partitions in a multi-region setup. #71753
- Fixed a bug that caused internal errors when collecting statistics on tables with virtual computed columns. #71284
- Fixed a bug causing CockroachDB to crash when network connectivity was impaired in some cases. The stack trace (in `cockroach-stderr.log`) would contain `server.(*statusServer).NodesUI`. #71719
- Fixed a panic that could occur with invalid GeoJSON input using `ST_GeomFromGeoJSON/ST_GeogFromGeoJSON`. #71308
- Fixed a bug causing cluster backups to back up opt-out system tables unexpectedly. #71922
- Fixed a bug that caused `ALTER COLUMN TYPE` statements to fail unexpectedly. #71166
- Connection timeout for `grpc` connections is now set to `20s` to match the pre-20.2 default value. #71516
- Fixed a bug that prevented the rollback of `ALTER PRIMARY KEY` when the old primary key was interleaved. #71852
- Fixed a bug that caused incorrect results for some queries that utilized a zigzag join. The bug could only reproduce on tables with at least two multi-column indexes with nullable columns. The bug was present since version 19.2.0. #71847
- Fixed a bug causing `IMPORT` statements to incorrectly reset progress upon resumption. #72086
- Fixed a bug causing schema changes running during node shutdown to fail permanently. #71558
- Fixed an incorrect "no data source matches prefix" error for queries that use a set-returning function on the right-hand side of a `JOIN` (unless `LATERAL` is explicitly specified). #71443
- Long running `ANALYZE` statements no longer result in GC TTL errors. #69599
- `IMPORT INTO` no longer crashes when encountering unresolved write intents. #71982
- Fixed a bug causing tracing to external tracers to inadvertently stop after the Enqueue Range or the Allocator debug pages were used. #72463
- Fixed a bug which prevented the Data Distribution debug page from working on clusters which were upgraded from 19.2 or earlier. #72507

### Performance improvements

- Slightly reduced `ANALYZE` and `CREATE STATISTICS` statements memory usage. #71771
- To reduce transient memory usage, CockroachDB now performs intent cleanup during garbage collection in batches as they are found instead of performing a single cleanup at the end of the garbage collection cycle. #67590

### Contributors

This release includes 51 merged PRs by 23 authors.

## v21.1.11

Release Date: October 18, 2021

### Enterprise edition changes

- Fixed a bug where `CHANGEFEED`s would fail to correctly handle a primary key change. #69926
- `CHANGEFEED`s no longer fail when started on `REGIONAL BY ROW` tables. Note that in `REGIONAL BY ROW` tables, the `crdb_region` column becomes part of the primary index. Thus, changing an existing table to `REGIONAL BY ROW` will trigger a changefeed backfill with new messages emitted using the new composite primary key. #70022
- Fixed a bug that could have led to duplicate instances of a single changefeed job running for prolonged periods of time. #70926

### SQL language changes

- Added `crdb_internal.(node|cluster)_distsql_flows` virtual tables that expose the information about the flows of the DistSQL execution scheduled on remote nodes. These tables do not include information about the non-distributed queries nor about local flows (from the perspective of the gateway node of the query). #66332
- Added new metrics to track a schema job failure (`sql.schema_changer.errors.all`, `sql.schema_changer.errors.constraint_violation`, `sql.schema_changer.errors.uncategorized`), with errors inside the `crdb_internal.feature_usage` table. #70621
- Fixed a bug where `LINESTRINGZ`, `LINESTRINGZM`, and `LINESTRINGM` could not be used as column types. #70749

### Operational changes

- Added the cluster settings `sql.defaults.transaction_rows_written_log`, `sql.defaults.transaction_rows_written_err`, `sql.defaults.transaction_rows_read_log`, and `sql.defaults.transaction_rows_read_err` (as well as the corresponding session variables). These settings determine the "size" of the transactions in written and read rows that are logged to the `SQL_PERF` logging channel. Note that the internal queries used by CockroachDB cannot error out, but can be logged instead to the `SQL_INTERNAL_PERF` logging channel. The "written" limits apply to `INSERT`, `INSERT INTO SELECT FROM`, `INSERT ON CONFLICT`, `UPSERT`, `UPDATE`, and `DELETE` whereas the "read" limits apply to `SELECT` statements, in addition to all of the others listed. These limits will not apply to `CREATE TABLE AS SELECT`, `IMPORT`, `TRUNCATE`, `DROP TABLE`, `ALTER TABLE`, `BACKUP`, `RESTORE`, or `CREATE STATISTICS` statements. Note that enabling the `transaction_rows_read_err` setting comes at the cost of disabling the usage of the auto commit optimization for the mutation statements in implicit transactions. #70175
- Adjusted the meaning of the recently introduced session variables `transaction_rows_written_err` and `transaction_rows_read_err` (as well as the corresponding `_log` variables) to indicate the largest number of rows that is still allowed. Prior to this change, reaching the limit would result in an error; now an error results only if the limit is exceeded. #70175
- Added the session variable `large_full_scan_rows`, as well as the corresponding cluster setting `sql.defaults.large_full_scan_rows`. This setting determines which tables are considered "large" for the purposes of enabling the `disallow_full_table_scans` feature to reject full table/index scans of such "large" tables. The default value for the new setting is `0`, meaning that the previous behavior of rejecting all full table/index scans is kept. Internally-issued queries aren't affected, and the new setting has no impact when the `disallow_full_table_scans` setting is not enabled. #70294
- CockroachDB now records a log event and increments a counter when removing an expired session. #68538

### Command-line changes

- Version details have been added to all JSON formatted log entries. #70451

### DB Console changes

- Renamed references to the UI console from "Admin UI" to "DB Console". #70870

### Bug fixes

- Fixed a bug where cluster revision history backups may have included dropped descriptors in the "current" snapshot of descriptors on the cluster. #69650
- Fixed a regression in statistics estimation in the optimizer for very large tables. The bug, which has been present since v20.2.14 and v21.1.7, could cause the optimizer to severely underestimate the number of rows returned by an expression. #69953
- Fixed a bug that can cause prolonged unavailability due to lease transfer of a replica that may be in need of a Raft snapshot. #69964
- Fixed a bug where, after a temporary node outage, other nodes in the cluster could fail to connect to the restarted node due to their circuit breakers not resetting. This would manifest in the logs via messages of the form `unable to dial nXX: breaker open`, where `XX` is the ID of the restarted node. Note that such errors are expected for nodes that are truly unreachable, and may still occur around the time of the restart, but for no longer than a few seconds. #70311
- `RESTORE` will now correctly ignore dropped databases that may have been included in cluster backups with revision history. #69791
- Fixed a bug where if tracing was enabled (using the `sql.trace.txn.enable_threshold` cluster setting), the statement diagnostics collection (`EXPLAIN ANALYZE (DEBUG)`) would not work. #70035
- Fixed a bug in full cluster restores where dropped descriptor revisions would cause the restore job to fail. #69654
- Fixed a bug where schema changes that included both a column addition and primary key change in the same transaction resulted in a failed changefeed. #70022
- Fixed a bug which prevented proper copying of partitions and zone configurations when de-interleaving a table with ALTER PRIMARY KEY when the columns did not appear in exactly the same order in the parent and child tables. #70695
- Fixed a bug where the exit status of the `cockroach` command did not follow the previously-documented table of exit status codes when an error occurred during command startup. Only errors occurring after startup were reported using the correct code. This bug had existed since reference exit status codes were introduced. #70675
- DNS unavailability during range 1 leaseholder loss will no longer cause significant latency increases for queries and other operations. #70134
- Fixed an issue in the Pebble storage engine where a key could be dropped from an LSM snapshot if the key was deleted by a range tombstone after the snapshot was acquired. #70967
- Fixed an internal error with joins that are both `LATERAL` and `NATURAL`/`USING`. #70800
- Fixed `Z` and `M` coordinate columns causing a panic for the `geometry_columns` and `geography_columns` tables. #70813
- Fixed a bug that could cause a CockroachDB node to deadlock upon startup in extremely rare cases. If encountered, a stack trace generated by `SIGQUIT` would have shown the function `makeStartLine()` near the top. This bug had existed since v21.1. #71408

### Performance improvements

- The conversion performance of WKT to a spatial type is slightly improved. #70181

### Miscellaneous

- Added a new `as_json` option which renders backup manifests as JSON values. #70298

### Contributors

This release includes 41 merged PRs by 24 authors.

## v21.1.10

Release Date: October 7, 2021

### Bug fixes

- Fixed a bug that caused the optimizer to erroneously discard `WHERE` filters when executing prepared statements, causing incorrect results to be returned. This bug was present since v21.1.9. #71116.

### Contributors

This release includes 1 merged PRs by 1 author.

## v21.1.9

Release Date: September 20, 2021

### SQL language changes

- Added the `bulkio.backup.proxy_file_writes.enabled` cluster setting to opt-in to writing backup files outside the KV storage layer. #69250
- You can now alter the owner of the `crdb_internal_region` type which is created by initiating a multi-region database. #69759

### Operational changes

- A new cluster setting, `sql.mutations.max_row_size.log`, was added, which controls large row logging. Whenever a row larger than this size is written (or a single column family if multiple column families are in use) a `LargeRow` event is logged to the `SQL_PERF` channel (or a `LargeRowInternal` event is logged to `SQL_INTERNAL_PERF` if the row was added by an internal query). This could occur for `INSERT`, `UPSERT`, `UPDATE`, `CREATE TABLE AS`, `CREATE INDEX`, `ALTER TABLE`, `ALTER INDEX`, `IMPORT`, or `RESTORE` statements. `SELECT`, `DELETE`, `TRUNCATE`, and `DROP` are not affected by this setting. This setting is disabled by default. #69946
- A new cluster setting, `sql.mutations.max_row_size.err`, was added, which limits the size of rows written to the database (or individual column families, if multiple column families are in use). Statements trying to write a row larger than this will fail with a code 54000 (`program_limit_exceeded`) error. Internal queries writing a row larger than this will not fail, but will log a `LargeRowInternal` event to the `SQL_INTERNAL_PERF` channel. This limit is enforced for `INSERT`, `UPSERT`, and `UPDATE` statements. `CREATE TABLE AS`, `CREATE INDEX`, `ALTER TABLE`, `ALTER INDEX`, `IMPORT`, and `RESTORE` will not fail with an error, but will log `LargeRowInternal` events to the `SQL_INTERNAL_PERF` channel. `SELECT`, `DELETE`, `TRUNCATE`, and `DROP` are not affected by this limit. Note that existing rows violating the limit cannot be updated, unless the update shrinks the size of the row below the limit, but can be selected, deleted, altered, backed-up, and restored. For this reason we recommend using the accompanying setting `sql.mutations.max_row_size.log` in conjunction with `SELECT pg_column_size()` queries to detect and fix any existing large rows before lowering `sql.mutations.max_row_size.err`. This setting is disabled by default. #69946
- New variables `sql.mutations.max_row_size.{log|err}` were renamed to `sql.guardrails.max_row_size_{log|err}` for consistency with other variables and metrics. #69946
- Added four new metrics: `sql.guardrails.max_row_size_log.count`, `sql.guardrails.max_row_size_log.count.internal`, `sql.guardrails.max_row_size_err.count`, and `sql.guardrails.max_row_size_err.count.internal`. These metrics are incremented whenever a large row violates the corresponding `sql.guardrails.max_row_size_{log|err}` limit. #69946

### DB Console changes

- A CES survey link component was added to support being able to get client feedback. #68517

### Bug fixes

- Fixed a bug where running `IMPORT PGDUMP` with a UDT would result in a null pointer exception. This change makes it fail gracefully. #69249
- Fixed a bug where the `schedules.backup.succeeded` and `schedules.backup.failed` metrics would sometimes not be updated. #69256
- The correct format code will now be returned when using `COPY FROM .. BINARY`. #69278
- Fixed a bug where `COPY FROM ... BINARY` would return an error if the input data was split across different messages. #69278
- Fixed a bug where `COPY FROM ... CSV` would require each `CopyData` message to be split at the boundary of a record. The `COPY` protocol allows messages to be split at arbitrary points. #69278
- Fixed a bug where `COPY FROM ... CSV` did not correctly handle octal byte escape sequences such as `\011` when using a `BYTES` column. #69278
- Fixed an oversight in the data generator for TPC-H which was causing a smaller number of distinct values to be generated for p_type and p_container in the part table than the spec calls for. #68710
- Fixed a bug that was introduced in v21.1.5, which prevented nodes from being decommissioned in a cluster if the cluster had multiple nodes intermittently miss their liveness heartbeat. #68552
- Fixed a bug introduced in v21.1 where CockroachDB could return an internal error when performing streaming aggregation in some edge cases. #69181
- Fixed a bug that created non-partial unique constraints when a user attempted to create a partial unique constraint in `ALTER TABLE` statements. #68745
- Fixed a bug where a `DROP VIEW ... CASCADE` could incorrectly result in "table ...is already being dropped" errors. #68618
- Fixed a bug introduced in v21.1 where the output of `SHOW CREATE TABLE` on tables with hash-sharded indexes was not round-trippable. Executing the output would not create an identical table. This has been fixed by showing `CHECK` constraints that are automatically created for these indexes in the output of `SHOW CREATE TABLE`. #69695
- Fixed internal or "invalid cast" error in some cases involving cascading updates. #69180
- Fixed a bug with cardinality estimation in the optimizer that was introduced in v21.1.0. This bug could cause inaccurate row count estimates in queries involving tables with a large number of null values. As a result, it was possible that the optimizer could choose a suboptimal plan. #69125
- Fixed a bug introduced in v20.2 that caused internal errors with set operations, like `UNION`, and columns with tuple types that contained constant `NULL` values. #69271
- Added backwards compatibility between v21.1.x cluster versions and the v21.1.8 cluster version. #69894
- Fixed a bug where table stats collection issued via `EXPLAIN ANALYZE` statements or via `CREATE STATISTICS` statements without specifying the `AS OF SYSTEM TIME` option could run into `flow: memory budget exceeded`. #69588
- Fixed a bug where an internal error or a crash could occur when some `crdb_internal` built-in functions took string-like type arguments (e.g. `name`). #69993
- Fixed all broken links to the documentation from the DB Console. #70117
- Previously, when using `ALTER PRIMARY KEY` on a `REGIONAL BY ROW` table, the copied unique index from the old primary key would not have the correct zone configurations applied. This bug is now fixed. Users who have encountered this bug should re-create the index. #69681

### Performance improvements

- Lookup joins on partial indexes with virtual computed columns are not considered by the optimizer, resulting in more efficient query plans in some cases. #69110
- Updated the optimizer cost model so that, all else being equal, the optimizer prefers plans in which `LIMIT` operators are pushed as far down the tree as possible. This can reduce the number of rows that need to be processed by higher operators in the plan tree, improving performance.#69977

### Contributors

This release includes 40 merged PRs by 19 authors.

## v21.1.8

Release Date: August 30, 2021

### Downloads

Warning:

This patch release has been withdrawn due to this technical advisory. We've removed the links to the downloads and Docker image.All the changes listed as part of this release will be in the next release. Do not upgrade to this release.

### Security updates

- The node status retrieval endpoints over HTTP (`/_status/nodes`, `/_status/nodes/<N>`, and the DB Console `/#/reports/nodes`) have been updated to require the `admin` role from the requesting user. This ensures that operational details such as network addresses and command-line flags do not leak to unprivileged users. #67068

### General changes

- A recent release removed parts of some queries from the debugging traces of those queries. This information (i.e. the execution of some low-level RPCs) has been re-included in the traces. #68923

### Enterprise edition changes

- The `kafka_sink_config` changefeed option can now include `RequiredAcks`. #69015
- Added a new per-node changefeed configuration `changefeed.node_sink_throttle_config` that can be used to throttle the rate of emission from the memory buffer thus making it possible to limit the emission rate from changefeeds. #68628

### SQL language changes

- Added a new `EXPLAIN` flag, `MEMO`, to be used with `EXPLAIN (OPT)`. When the `MEMO` flag is passed, a representation of the optimizer memo will be printed along with the best plan. The `MEMO` flag can be used in combination with other flags such as `CATALOG` and `VERBOSE`. For example, `EXPLAIN (OPT, MEMO, VERBOSE)` will print the memo along with verbose output for the best plan. #67778
- Some queries with lookup joins and/or top K sorts are now more likely to be executed in a "local" manner with the `distsql=auto` session variable when the newly introduced `sql.distsql.prefer_local_execution.enabled` cluster setting is set to `true` (`false` is the default). #68613

### Operational changes

- Introduced the `bulkio.index_backfill.checkpoint_interval` cluster setting to control the rate at which backfills checkpoint their progress. Useful for controlling the backfill rate on large tables. #68287

### Command-line changes

- `cockroach debug zip` no longer retrieves database and table details into separate files. The schema information is collected by means of `system.descriptors` and `crdb_internal.create_statements`. #68984

### DB Console changes

- Added a new column picker on the Statements and Statements Details pages to select which columns to display in the statements table. Includes a new database column option for database name information, which is hidden by default on the Statements page. #68294
- Fixed the Transactions page to show the correct value for implicit transactions. #68294
- Statements Details, Statements, and Transactions pages now display information about the node and region on which a statement was executed. #68317
- Fixed tooltips behavior on the Sessions, Statements, and Transactions pages. #68474

### Bug fixes

- Fixed missing foreign key checks in some cases when there are multiple checks and the inserted data contains a `NULL` for one of the checks. #68520
- Fixed a bug where using `ST_segmentize` on coordinates that are infinite would error with a crash. #67848
- Fixed the `COPY CSV` command so that it handles multiple records separated by newline characters. #68623
- Fixed a bug that caused incorrect query results when querying tables with multiple column families and unique secondary indexes. The bug only occurred if 1) vectorized execution was enabled for the query, 2) the query scanned a unique secondary index that contained columns from more than one column family, and 3) the rows fetched by the query contained `NULL` values for some of the indexed columns. This bug was present since version v20.1. #68238
- Fixed a crash in the `/debug/closedts-{sender,receiver}` advanced debug pages if the last message of the closed timestamp side transport buffer was removed before rendering. #68669
- Fixed an issue where terminating a CockroachDB process early in its startup routine might cause it to fail to start again, falsely reporting write-ahead log corruption. #68897
- Fixed a regression in the optimizer's cost model that could cause it to choose sub-optimal plans when choosing between two non-unique index scans with different numbers of columns per index. #68991
- Fixed a bug where CockroachDB could incorrectly evaluate `LIKE` expressions when the pattern contained the escape characters `\` if the expressions were executed via the vectorized execution engine. #68353
- File logs now respect the `max-group-size` configuration parameter again. This parameter had incorrectly become ignored in v21.1, resulting in arbitrarily large log directories. #69007
- Fixed a bug in `EXPORT` where concurrent exports could overwrite each other. #68392

### Performance improvements

- Jobs no longer hold exclusive locks during the duration of their checkpointing transactions which could result in long wait times when trying to run `SHOW JOBS`. #68244
- Lookup joins on indexes with virtual columns are now considered by the optimizer. This should result in more efficient queries in many cases. Most notably, post-query uniqueness checks for unique indexes on virtual columns in `REGIONAL BY ROW` tables can now use the unique index rather than perform a full-table scan. #68423

### Contributors

This release includes 40 merged PRs by 26 authors.

## v21.1.7

Release Date: August 9, 2021

### Security updates

- The `--cert-principal-map` flag passed to `cockroach` commands now allows the certificate principal name to contain colons. #67810

### General changes

- Added a new cluster setting (`kv.transaction.reject_over_max_intents_budget`) that controls the behavior of CockroachDB when a transaction exceeds the locks-tracking memory budget set by the `kv.transaction.max_intents_bytes` cluster setting. If `kv.transaction.reject_over_max_intents_budget` is set to `true`, CockroachDB rejects the query that would push its transaction over the memory budget with an error (error code 53400 - "configuration limit exceeded"). Transactions that don't track their locks precisely are potentially destabilizing for the cluster since cleaning up locks can take up considerable resources. Transactions that change many rows have the potential to run into this memory budget issue. #67967

### SQL language changes

- The remote DistSQL flows are now eagerly canceled if they were queued up and the query was canceled. #66331
- Added a new cluster setting (`changefeed.slow_span_log_threshold`) that allows setting a cluster-wide default for slow span logging. #68106
- Added a new session variable (`enable_copying_partitioning_when_deinterleaving_table`) which will change the behavior of `ALTER PRIMARY KEY` when performing a change which retains the same primary key but removes an `INTERLEAVE INTO` clause. When this variable is set to `true` and an `ALTER PRIMARY KEY` is run that only removes an `INTERLEAVE INTO` clause, the partitioning and zone configuration which applied to the root of the interleave will be applied to the new primary index. The default value for `enable_copying_partitioning_when_deinterleaving_table` is equal to the value set for the new cluster setting `sql.defaults.copy_partitioning_when_deinterleaving_table.enabled`. #68114

### Operational changes

- Histogram metrics now store the total number of observations over time. #68106

### DB Console changes

- Fixed a bug causing the Summary Panel on the Overview Dashboard to flicker. #67365
- Fixed a bug preventing a redirect to the originally-requested DB Console page after user login. #67858
- User can now see time series metrics for disk spilling on the Advanced Debug page. #68112
- Fixed color mismatch of node status badge on the Cluster Overview page. #68056
- Chart titles on the Replication Dashboard were previously falsely labeled as "per Store" but were in fact "per Node". This bug is now fixed. #67847

### Bug fixes

- Fixed a bug causing the `ST_GeneratePoints` built-in function to return a garbage value or an error if an empty geometry or negative nPoints input is given. #67580
- Fixed a bug where `DROP DATABASE` could return errors if the database contained temporary views in use in an another session. #67172
- Fixed a storage-level bug where Pebble would occasionally create excessively large SSTables, causing poor compaction performance and high read-amplification. This was especially likely after a manual offline compaction. #67610
- Correlated subqueries that couldn't be decorrelated and that have their own subqueries are now executed correctly when supported. Note that it is an edge case of an edge case, so it's unlikely that users have hit this bug (it was found by the randomized testing). #67570
- Fixed very rare, unexpected "index out of bounds" error from the vectorized engine when evaluating a `CASE` operator. #67779
- Catching up Raft followers on the Raft log is now more efficient in the presence of many large Raft log entries. This helps avoid situations where Raft leaders struggle to retain leadership while catching up their followers. #67127
- Fixed a bug that allowed rows to be inserted into a table with a `CHECK` constraint that always evaluated to `false` (e.g., `CHECK (false)`). This bug was present since version 21.1.0. #67341
- Fixed a bug causing changefeeds to sometimes get stuck. #67968
- Previously, CockroachDB nodes would crash whenever the cluster setting `sql.trace.txn.enable_threshold` was changed to a non-zero value. The bug was introduced in 21.1.0. #68027
- Fixed a deadlock that could occur when many replicas were rapidly queued for removal. #65859
- Fixed two bugs which affected geospatial queries with the `st_distance` function. The first bug caused errors for filters of the form `st_distance(g1, g2, use_spheroid) = 0`. The second could cause incorrect results in some cases; it incorrectly transformed filters of the form `st_distance(g1, g2) = 0` when `g1` and `g2` were geographies to `st_instersects(g1, g2)`. This is not a valid transformation because `st_distance` makes spheroid-based calculations by default while `st_intersects` only makes sphere-based calculations. #67392
- Fixed a bug causing a prepared statement to incorrectly reuse the query plan of a different prepared statement that had similar, but not identical type hints. #67688
- Fixed an issue with statistics estimation in the optimizer that could cause it to over-estimate the number of rows for some expressions and thus choose a sub-optimal plan. This issue could happen when multi-column statistics were used in combination with histograms, the query contained a predicate on two or more columns where the columns were highly correlated, and the selected values were very common according to the histograms. #67998
- Previously, CockroachDB could encounter an internal error or crash when performing a cast of `NULL` `JSON` value to Geography or Geometry types. Now this is fixed. #67902
- `INSERT` and `UPDATE` statements which operate on larger rows can now be split into batches using the `sql.mutations.mutation_batch_byte_size` setting. #67958
- A rare bug that could result in a crash while creating a debug.zip file has been fixed. The bug was only possible to hit if a debug.zip file was captured during a period of rapid lease movement. #67728
- Previously the `GRANT` and `REVOKE` commands would incorrectly handle role names. CockroachDB treats role names as case-insensitive, but these commands were incorrectly handling the names. Now, `GRANT` and `REVOKE` normalize the names and are case-insensitive. #67901

### Performance improvements

- Vectorized flows can use less memory when sending and receiving data to the network. #67609
- Range merges are no longer considered if a range has seen significant load over the previous 5 minutes, instead of being considered as long as a range had low load over the previous second. This change improves stability, as load-based splits will no longer rapidly disappear during transient throughput dips. #65362
- A new cluster setting `sql.defaults.optimizer_improve_disjunction_selectivity.enabled` enables more accurate selectivity estimation of query filters with `OR` expressions. This improves query plans in some cases. This cluster setting is disabled by default. #67730

### Contributors

This release includes 47 merged PRs by 26 authors.

## v21.1.6

Release Date: July 20, 2021

### General changes

- Switched the `release-21.1` branch to point to a fork of the Google Cloud SDK at version 0.45.1 with an upstream bug fix. #66808

### Enterprise edition changes

- Improved the internal buffering of changefeeds to be robust to bursts in traffic which exceed the throughput to the sink. #67205
- Incremental backups to a cloud storage location that already contains large existing backups now find their derived destination without listing as many remote files. #67286

### Operational changes

- Added logs for important events during the server draining/shutdown process: (1) log when the server closes an existing connection while draining, (2) log when the server rejects a new connection while draining, and (3) log when the server cancels in-flight queries after waiting for the `server.shutdown.query_wait` duration to elapse while draining. #66874

### DB Console changes

- Fixed a bug preventing the Custom Chart debug page from loading on initial. #66897
- Added a drag-to-select timescale feature to the Custom Chart debug page. #66594

### Bug fixes

- CockroachDB now allows a node with lease preferences to drain gracefully. #66712
- Fixed a panic that could occur in the optimizer when executing a prepared plan with placeholders. This could happen when one of the tables used by the query had computed columns or a partial index. #66833
- Fixed a bug that caused graceful drain to call `time.sleep` multiple times, which cut into time needed for range lease transfers. #66851
- Reduced CPU usage of idle `cockroach` processes. #66894
- Fixed an error that backup would produce in some full backups with revision history. Previously, some full backups would erroneously produce an error in the form of `batch timestamp <some_timestamp> must be after replica GC threshold <some_other_timestamp>`. #66904
- CockroachDB now avoids interacting with decommissioned nodes during DistSQL planning and consistency checking. #66950
- Changefeeds no longer interact poorly with large, abandoned transactions. Previously, this combination could result in a cascade of work during transaction cleanup that could starve out foreground traffic. #66813
- Changefeeds now properly invalidate cached range descriptors and retry when encountering decommissioned nodes. #67013
- Fixed a bug where metrics pages would lose their scroll position on chart data updates. #67089
- Fixed a bug which caused internal errors when running an `IMPORT TABLE` statement with a virtual computed column to import a CSV file. This bug has been present since virtual computed columns were introduced in v21.1.0. #67043
- Previously, the CLI would attribute some of the time spent running schema changes to network latency. This has now been fixed. Additionally, verbose timings in the CLI now also show time spent running schema changes in a separate bucket. #65719
- Fixed a statement buffer memory leak when using suspended portals. #67372
- Changefeeds on tables with array columns now support Avro. #67433
- Avro encoding now supports collated string types. #67433
- `ENUM` columns can now be encoded in Avro changefeeds #67433
- Avro changefeeds now support `BIT` and `VARBIT` columns. #67433
- `INTERVAL` columns are now supported in Avro changefeeds. #67433

### Performance improvements

- CockroachDB now continues to generate histograms when table statistics collection reaches memory limits, instead of disabling histogram generation. #67057
- The optimizer now prefers performing a reverse scan over a forward scan + sort if the reverse scan eliminates the need for a sort and the plans are otherwise equivalent. This was previously the case in most cases, but some edge cases with a small number of rows have been fixed. #67388
- When choosing between index scans that are estimated to have the same number of rows, the optimizer now prefers indexes for which it has higher certainty about the maximum number of rows over indexes for which there is more uncertainty in the estimated row count. This helps to avoid choosing suboptimal plans for small tables or if the statistics are stale. #67388

### Contributors

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

- joesankey (first-time contributor)

## v21.1.5

Release Date: July 2, 2021

Warning:

We recommend upgrading from v21.1.4 to this bug fix release as soon as possible.

### Bug fixes

- Fixed a panic that could occur in the optimizer when executing a prepared plan with placeholders. This could happen when one of the tables used by the query had computed columns or a partial index. #66883

## v21.1.4

Release Date: June 29, 2021

Warning:

We recommend upgrading from this release to the v21.1.5 bug fix release as soon as possible.

### Security updates

- Previously, all the logging output to files or network sinks was disabled temporarily while an operator was using the `/debug/logspy` HTTP API, resulting in lost entries and a breach of auditability guarantees. This behavior has been corrected. #66328
- CockroachDB now configures a maximum number of concurrent password verifications in the server process, across UI and SQL clients. This limit reduces the risk of DoS attacks or accidents due to misbehaving clients. By default, the maximum amount of concurrency is ~12% of the number of allocated CPU cores (as per `GOMAXPROCS`), with a minimum of 1 core. This default can be overridden using the environment variable `COCKROACH_MAX_BCRYPT_CONCURRENCY`. #66367

### SQL language changes

- Implemented the `ST_HasArc` built-in function. This adds better out-of-the-box support for GeoServer. #66531
- Added a new internal cluster setting, `jobs.cancel_update_limit`, for controlling how many jobs are cleaned up concurrently after query cancellation. #66488

### Command-line changes

- The SQL shell now formats times with time zones so that the minutes and seconds offsets are only shown if they are non-zero. Also, infinite floating point values are now formatted as `Infinity` rather than `Inf`. #66130
- When log entries are written to disk, the first few header lines written at the start of every new file now report the configured logging format. #66328

### API endpoint changes

- The `/debug/logspy` HTTP API has changed. The endpoint now returns JSON data by default. If the previous format is desired, the user can pass the query argument `&flatten=1` to the `logspy` URL to obtain the previous flat text format (`crdb-v1`) instead. #66328 This change is motivated as follows:
  - The previous format, `crdb-v1`, cannot be parsed reliably.
  - Using JSON entries guarantees that the text of each entry all fits on a single line of output (newline characters inside the messages are escaped). This makes filtering easier and more reliable.
  - Using JSON enables the user to apply `jq` on the output, for example via `curl -s .../debug/logspy | jq ...`.
- The `/debug/logspy` API no longer enables maximum logging verbosity automatically. To change the verbosity, use the new `/debug/vmodule` endpoint or pass the `&vmodule=` query parameter to the `/debug/logspy` endpoint. #66328 For example, suppose you wish to run a 20s logspy session:
  - Before: `curl 'https://.../debug/logspy?duration=20s&...'`.
  - Now: `curl 'https://.../debug/logspy?duration=20s&vmodule=...'` OR `curl 'https://.../debug/vmodule?duration=22s&vmodule=...' curl 'https://.../debug/logspy?duration=20s'`.
  - As for the regular `vmodule` command-line flag, the maximum verbosity across all the source code can be selected with the pattern `*=4`.
  - Note: at most one in-flight HTTP API request is allowed to modify the `vmodule` parameter. This maintains the invariant that the configuration restored at the end of each request is the same as when the request started.
- The new `/debug/vmodule` API makes it possible for an operator to configure the logging verbosity in a similar way as the SQL built-in function `crdb_internal.set_vmodule()`, or to query the current configuration as in `crdb_internal.get_vmodule()`. Additionally, any configuration change performed via this API can be automatically reverted after a configurable delay. #66328 The API forms are:
  - `/debug/vmodule`: Retrieve the current configuration
  - `/debug/vmodule?set=[vmodule config]&duration=[duration]`: Change the configuration to `[vmodule config]` . The previous configuration at the time the `/debug/vmodule` request started is restored after `[duration]`. This duration, if not specified, defaults to twice the default duration of a `logspy` request (currently, the `logspy` default duration is 5s, so the `vmodule` default duration is 10s). If the duration is zero or negative, the previous configuration is never restored.

### DB Console changes

- Fixed an issue with displaying more than 100 hours of remaining time on the Jobs page. #66596

### Bug fixes

- Minute timezone offsets are only displayed in the wire protocol if they are non-zero for `TimestampTZ` and `TimeTZ` values. Previously, they would always display. #66130
- Fixed a bug where binary `TimeTZ` values were not being decoded correctly when being sent as a parameter in the wire protocol. #66130
- CockroachDB's SQL shell now properly displays results of common array types, for example: arrays of floats, or arrays of strings. #66130
- Fixed a bug where the `--log='file-defaults: {format: crdb-v1}'` flag was not being handled properly. This bug existed since v21.1.0. #66328
- Fixed a bug where log entries could be lost while the `/debug/logspy` HTTP API was being used. This bug had existed since CockroachDB v1.1. #66328
- The binary encoding of decimals will no longer have negative `dscale` values. This was preventing Npgsql from being able to read some binary decimals from CockroachDB. #66532
- A bug has been fixed which prevented the optimizer from producing plans with partial indexes when executing some prepared statements that contained placeholders, stable functions, or casts. This bug was present since partial indexes were added in v20.2.0. #66634
- Fixed a bug which could have prevented backups from being successfully restored. #66616
- Fixed a bug where CockroachDB could crash when executing `EXPLAIN (VEC)` on some mutations. The bug is present only in the v21.1.1-v21.1.3 releases. #66573
- Fixed a bug where CockroachDB could encounter an internal error when computing window functions with `ROWS` mode of window framing if the offsets were very large for the `OFFSET FOLLOWING` boundary type. #66446
- Fixed a bug where using `ADD COLUMN UNIQUE` on `REGIONAL BY ROW` tables did not correctly add the zone configs for the newly created column index. #66696
- Fixed a bug where reading from Google Cloud Storage was not using the resuming reader, as a result of which some retryable errors were not being treated as such, and the read would fail. #66190
- Fixed a deadlock during backups and imports. #66773
- Fixed incorrect accounting for statement/transaction sampled execution statistics. #66790
- Fixed a bug causing transactions to be spuriously aborted in rare circumstances. #66567

### DB Console

- Fixed a CSS width calculation which was causing the multibar to not be visible in the DB Console. #66739

### Contributors

This release includes 28 merged PRs by 22 authors.

## v21.1.3

Release Date: June 21, 2021

### Security updates

- Syntax errors in the host-based authentication (HBA) configuration in cluster setting `server.host_based_authentication.configuration` are now logged on the OPS channel. #66128
- The `User` and `ApplicationName` fields of structured events pertaining to SQL queries are now marked as non-sensitive when they contain certain values (`root`/`node` for `User` and values starting with `$` for application names). #66443

### Enterprise edition changes

- Changefeeds with custom Kafka client configurations (using the `kafka_sink_config` object) that could lead to long delays in flushing messages will now produce an error. #66265
- The `kafka_sink_config` object now supports a `version` configuration item to specify Kafka server versions. This is likely only necessary for old (Kafka 0.11/Confluent 3.3 or earlier) Kafka servers. Additionally, settings not specified in `kafka_sink_config` now retain their default values. #66314

### SQL language changes

- `TRUNCATE` is now less disruptive on tables with a large amount of concurrent traffic. #65940
- Creating `STORED` or `VIRTUAL` computed columns with expressions that reference foreign key columns is now allowed. #66168
- The new function `crdb_internal.get_vmodule` returns the current `vmodule` configuration on the node processing the request. #63545
- The description string for the `random()` function now clarifies that there are at most 53 bits of randomness available; that is, the function is unsuitable to generate 64-bit random integer values. This behavior is similar to that of PostgreSQL. #66128
- `EXPLAIN ANALYZE` now displays information about the regions on which a statement was executed. #66368

### Operational changes

- Added a configurable limit to the number of intents collected by a `scan` before aborting, to prevent out-of-memory errors. The setting `storage.mvcc.max_intents_per_error` replaces `storage.sst_export.max_intents_per_error` and covers both `scan` and `export` commands. #65923
- `BACKUP` now puts backup data files in a `data` sub-directory of the `BACKUP` path instead of directly in the backup path. #66161

### Command-line changes

- The informational messages printed out when `cockroach demo` starts have been updated to clarify that certain information is only needed when accessing the demo cluster from another tool. #66129
- `cockroach demo` and `cockroach sql` are now able to run client-side commands via the `-e` command-line flag. This makes it possible to use commands like `\dt` or `\hf` from a shell script. #66326

### DB Console changes

- Users can now reset SQL stats from the DB Console. #65916
- Removed shading on line graphs, improving legibility when viewing more than a few series on the same plot. #66032
- Drag-to-zoom on metrics graphs now supports time ranges under 10 minutes. #66032
- In some cases, the Execution Stats page would show a very high Overhead latency for a statement. This could happen due to multiple statements being parsed together or due to statement execution being retried. To avoid this, we no longer consider the time between when parsing ends and execution begins when determining service latency. #66108
- Improved the style of the password input field for Safari. #66134
- The metrics chart under Overview was renamed from `SQL Queries` to `SQL Statements` to match the naming used under SQL Metrics. #66364

### Bug fixes

- Fixed a bug which prevented adding columns to tables which contain data and use `NOT NULL` virtual columns #65973
- Fixed a bug in the DB Console where graphs for clusters with decommissioned nodes could show an empty series and data could be incorrectly attributed to the wrong nodes. #66032
- Fixed a bug where queries on `REGIONAL BY ROW` tables could fail in the brief window in which a DROP REGION operation is in progress. #65984
- Fixed a bug where a schema's privilege descriptor could be corrupted upon executing `ALTER DATABASE ... CONVERT TO SCHEMA`, where privileges invalid on a schema were copied from the database, rendering the schema unusable. #65993
- Fixed the error classification for duplicate index names where the later index was a `UNIQUE` index. #64000
- Fixed the error classification for `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE` with the same name as an existing index. #64000
- Fixed a bug that made it less likely for range merges to succeed on clusters using multiple stores per node is now fixed. #65889
- Improved `TRUNCATE` operations to prevent contention issues. #65940
- Improved garbage collection of stale replicas by proactively checking certain replicas that have lost contact with other voting replicas. #65186
- Fixed a bug in `SHOW RANGES` which misattributed localities to nodes when using multiple stores. #66037
- Queries run through the `EXECUTE` statement can now generate statement diagnostic bundles as expected. #66098
- Previously, an `INSERT` causing a foreign key violation could result in an internal error in rare cases. The bug only affected the error response; any affected `INSERT`s (which would have been foreign-key violations) did not succeed. This bug, present since v21.1.0, has been fixed. #66300
- `BACKUP` and other operations can now reuse a previously created S3 client session when operating on the same bucket, which can avoid `NoCredentialProviders` errors on EC2 when iterating with large incremental backups. #66259
- The command exit status of `cockroach demo` and `cockroach sql` is now properly set to non-zero (error) after an error is encountered in a client-side command. Additionally, `cockroach sql` and `cockroach demo` now properly stop upon encountering an invalid configuration with `--set`, instead of starting to execute SQL statements after the invalid configuration. #66326
- Improved the availability of the jobs table for reads in large, global clusters by running background tasks at low priority. #66344
- Backups no longer risk the possibility of blocking conflicting writes while being rate limited by the `kv.bulk_io_write.concurrent_export_requests` concurrency limit. #66408
- The `soundex` and `st_difference` built-in functions for fuzzy string matching now correctly handle `NULL` values. #66302

### Performance improvements

- Fixed an issue in the optimizer that prevented spatial predicates of the form `(column && value) = true` from being index-accelerated. These queries can now use a spatial index if one is available. #65986
- The optimizer is now more efficient when planning queries on tables that have many columns and indexes. #66304
- The `COCKROACHDB_REGISTRY` file is no longer rewritten whenever a new unencrypted file is created. #66423
- After improvements, queries use up to 1MB less system memory per scan, lookup join, index join, zigzag join, or inverted join in their query plans. This will result in improved memory performance for workloads with concurrent OLAP-style queries. #66145
- Made improvements to prevent intra-query leaks during disk spilling that could cause the database to run out of memory, especially on tables with wide rows. #66145

### Contributors

This release includes 64 merged PRs by 35 authors.

## v21.1.2

Release Date: June 7, 2021

### General changes

- Added multi-region workloads for `cockroach demo movr --geo-partitioned-replicas`. Setting `--multi-region` enables for multi-region workloads, and setting `--survive` allows for surviving `AZ` or `REGION` failures. Setting `--infer-crdb-region-column` also infers the `crdb_region` for `REGIONAL BY ROW` tables. #65642
- Changefeeds now better handle slow or unavailable sinks by treating "memory exceeded" errors as retryable. #65387

### SQL language changes

- Added the `crdb_internal.lost_descriptors_with_data` function to show descriptors that have no entries but data left behind. #65462
- Added the `crdb_internal.force_delete_table_data` function which allows table data to be cleaned up only using a descriptor ID for cases of table descriptor corruption. #65462
- The statement type ("tag") is now also included alongside the full text of the SQL query in the various structured log entries produced when query execution is being logged. #65554
- CockroachDB now returns a SQL Notice if a `CREATE TABLE IF NOT EXISTS` command is used to create a `TABLE` and the `TABLE` already exists. #65636
- The `SHOW FULL TABLE SCANS` statement was added to CockroachDB. #65671
- CockroachDB now returns a SQL Notice if a `CREATE TYPE IF NOT EXISTS` command is used to create a type and the type already exists. #65635
- Added a `chunk_size` option to `EXPORT INTO CSV` to control the target CSV file size. #65388
- SQL stats can now be cleared using the `crdb_internal.reset_sql_stats()` function. #65674
- CockroachDB now supports `ALTER DATABASE ... ADD REGION IF NOT EXISTS ...` which does not cause an error when adding a region that is already in the database. #65752
- CockroachDB now outputs a clearer error message when running `ALTER DATABASE ... ADD REGION ...` if the region is an undefined region. Previously, the error message for not having a region defined on a database resulted in an error about enums. #65752
- Added the `ALTER DATABASE ... DROP REGION IF EXISTS ...` statement syntax, which does not error if dropping a region that is not defined on the database. #65752
- Fixed a bug where transitioning from locality `REGIONAL BY ROW` to `GLOBAL` or `REGIONAL BY TABLE` could mistakenly remove a zone configuration on an index which has no multi-region fields set. #65833
- CockroachDB now only blocks a zone configuration DISCARD on a multi-region table, index, or partition if the multi-region abstractions created the zone configuration. #65834

### Operational changes

- Range metrics are now gathered from the leaseholder (if live) rather than the first available range replica. This avoids scenarios where a stale replica may yield incorrect metrics, in particular over/underreplication markers. #64590

### DB Console changes

- Fixed Jobs page crash while using pagination and improved its performance. #65723
- Fixed a typo on the Network tooltip on the Statements page. #65605
- Fixed a missing node ID in the rejoin event message #65806
- Sorts on tables now pick up the correct value from the URL. #65605

### Bug fixes

- Fixed a bug where a certain percentage of cases in which a node could have served a follower read were not handled correctly, resulting in the node routing the request to another nearby node for no reason. #65471
- The `has_database_privilege` function now correctly will check privileges on databases that are not the current database being used by the session. #65534
- Fixed a bug where CockroachDB would previously crash when attempting to create a table using `CREATE TABLE ... AS` syntax where the `AS` clause selects from `crdb_internal.node_statement_statistics`, `crdb_internal.node_transaction_statistics`, or `crdb_internal.node_txn_stats` virtual tables. #65542
- Fixed a bug which allowed index definitions with redundant columns, which led to unnecessary storage usage. This bug can notably manifest itself with `ALTER TABLE` statements which alter the primary index on a partitioned table. This bug has been present for a long time in theory, but in practice would only appear in CockroachDB since version 21.1.0. #65482
- Fixed a bug where binary `TIMETZ` values were not being decoded correctly when being sent as a parameter in the wire protocol. #65341
- Fixed a race condition during transaction cleanup that could leave old transaction records behind until MVCC garbage collection. #65383
- Improved transaction cleanup for disconnected clients, to reduce intent buildup. #65383
- Added the ability to change the `COMMENT` on a column after using `ALTER TYPE` on that column. #65698
- Scheduled backup with interleaved tables can now be created with the `include_deprecated_interleaves` option. #65731
- Fixed a bug where `ST_Node` on a `LINESTRING` with the same repeated points results in an error. #65700
- Calling `get_bit` or `set_bit` on a byte array argument now goes to the correct index of the underlying bit string, in order to match the behavior of Postgres. #65786
- Fixed a bug where `ALTER DATABASE ... CONVERT TO SCHEMA` could potentially leave the schema with invalid privileges thus causing the privilege descriptor to be invalid. #65810
- CockroachDB now renders the `CACHE` clause for sequences which use a cache. #65805
- Fixed a bug that could cause a node to crash in rare cases if a `BACKUP` writing to Google Cloud Storage failed. #65802
- Fixed a bug introduced in 21.1 where cluster restores would report inaccurate progress, showing 100% progress erroneously. #65803
- Fixed a crash when performing a cluster `BACKUP` with revision history of a cluster upgraded from 20.1 to 20.2 to 21.1 which contains tables that were truncated by 20.1. #65860
- Fixed a bug that caused incorrect results for queries where `CHAR` and `VARCHAR` columns are filtered by constant string values. The bug was present since version v21.1.0. #66101

### Performance improvements

- The optimizer can now avoid full table scans for queries with a `LIMIT` and `ORDER BY` clause in some additional cases where the `ORDER BY` columns are not a prefix of an index. #65392
- The optimizer now generates query plans that scan indexes on virtual collated string columns, regardless of the casing or formatting of the collated locale in the query. #65531
- CockroachDB now reduces the number of round-trips required to call `pg_table_is_visible` in the context of `pg_catalog` queries. #65807

### Contributors

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

- Max Neverov
- Rupesh Harode

## v21.1.1

Release Date: May 24, 2021

### General changes

- Disabled read-triggered compactions to avoid instances where the storage engine would compact excessively. #65345

### SQL language changes

- Fixed Julian date parsing logic for wrong formats. #63540
- The error payload returned to the client when a `DATE`/`TIME` conversion fails now contains more details about the difference between the values provided and the values that are expected. #63540
- Introduced `ALTER TABLE ... ALTER COLUMN SET [VISIBLE|NOT VISIBLE]`, which marks columns as visible/not visible. #63881
- When using `ALTER TABLE ... LOCALITY REGIONAL BY ROW`, we would previously verify uniqueness of the new table, which was an unnecessary operation. This verification has been removed, improving the performance of updating localities to or from `REGIONAL BY ROW`. #63880
- Improved cancellation behavior for DistSQL flows. #65047
- `ST_EstimatedExtent` now always returns `NULL`. This allows GeoServer to make progress in certain cases, and is a valid default return value for the function. #65098
- Implemented `ST_Envelope` for `Box2D`. #65098
- Implemented a subset of variants for `ST_AsTWKB`, which encodes a geometry into a `TWKB` format. This allows the use of GeoServer with CRDB if the user selects "PreserveTopology" for their "Method used to simplify geometries" option on the "Store" page. #65098
- Implemented `ST_Simplify` with `preserveCollapsed` support. This unblocks the use of GeoServer with the default settings. #65098
- Lookup joins on indexes with computed columns which are also either constrained by `CHECK` constraints or use an `ENUM` data type may now choose a more optimal plan. #65361
- Floating point infinity values are now formatted as `Infinity` (or `-Infinity` if negative). This is for compatibility with PostgresSQL. #65334
- `INSERT INTO ... ON CONFLICT ... DO UPDATE SET` statements without predicates now acquire locks using the `FOR UPDATE` locking mode during their initial row scan, which improves performance for contended workloads. This behavior is configurable using the `enable_implicit_select_for_update` session variable and the `sql.defaults.implicit_select_for_update.enabled` cluster setting. #65363
- `ST_GeomFromGeoJSON(string)` is now marked as the preferred overload, meaning it will resolve correctly in more contexts. #65442

### Operational changes

- Replica garbage collection now checks replicas against the range descriptor every 12 hours (down from 10 days) to see if they should be removed. Replicas that fail to notice they have been removed from a range will therefore linger for at most 12 hours rather than 10 days. #64589

### Command-line changes

- The `--help` text for `--log` now references the fact that the flag accepts YAML syntax and also points to the `cockroach debug check-log-config` command. #64948
- The new parameter `--log-config-file` simplifies the process of loading the logging configuration from a YAML file. Instead of passing the content of the file via the `--log` flag (e.g., `--log=$(cat file.yaml)`), it is now possible to pass the path to the file using `--log-config-file=file.yaml`.
  Note: Each occurrence of `--log` and `--log-config-file` on the command line overrides the configuration set from previous occurrences. #64948
- The prefixes displayed before connection URLs when `cockroach demo` starts up have been updated to better align with the output of `cockroach start`. #63535
- The flag `--empty` for `cockroach demo` has been renamed to `--no-example-database`. `--empty` is still recognized but is marked as deprecated. Additionally, the user can now set the environment variable `COCKROACH_NO_EXAMPLE_DATABASE` to obtain this behavior automatically in every new demo session. #63535
- CockroachDB no longer supports the `\demo add` and `\demo shutdown` commands for `cockroach demo` in `--global` configurations. #63535
- Added a note when starting up a `--global` demo cluster that the `--global` configuration is experimental. #63535
- The SQL shell (`cockroach demo`, `cockroach sql`) now attempts to better format values that are akin to time/date values, as well as floating-point numbers. #63541
- `cockroach debug zip` now attempts to pull data from multiple nodes concurrently, up to 15 nodes at a time. This change is meant to accelerate the data collection when a cluster contains multiple nodes. This behavior can be changed with the new command-line flag `--concurrency`. #64705
- The format of the informational messages printed by `cockroach debug zip` , when concurrent execution is enabled. #64705
- The new command `cockroach debug list-files` show the list of files that can be retrieved via the `cockroach debug zip` command. It supports the `--nodes` and `--exclude-nodes` parameters in the same way as `cockroach debug zip`. #64705
- It is now possible to fine-tune which files get retrieved from the server nodes by the `cockroach debug zip` command, using the new flag `--include-files` and `--exclude-files`. These flags take glob patterns that are applied to the file names server-side. For example, to include only log files, use `--include-files='*.log'`. The command `cockroach debug list-files` also accepts these flags and can thus be used to experiment with the new flags before running the `cockroach debug zip` command. #64705
- The `cockroach debug zip` command now retrieves only the log files, goroutine dumps and heap profiles pertaining to the last 48 hours prior to the command invocation. This behavior is supported entirely client-side, which means that it is not necessary to upgrade the server nodes to put these newly-configurable limits in place. The other data items retrieved by `cockroach debug zip` are not affected by this time limit. This behavior can be customized by the two new flags `--files-from` and `--files-until`. Both are optional. See the command-line help text for details. The two new flags are also supported by `cockroach debug list-files`. It is advised to experiment with `list-files` prior to issuing a `debug zip` command that may retrieve a large amount of data. #64705

### DB Console changes

- A new metric for the average number of runnable goroutines per CPU is now present in the runtime graphs. #64750
- The Console now uses a new library for line graphs that renders metrics more efficiently. Customers with large clusters can now load and interact with metrics much faster than before. #64479
- Placed a legend under charts on metrics page, if more than 10 series are being displayed #64479

### Bug fixes

- Fixed a bug in the artificial latencies introduced by the `--global` flag to `cockroach demo`. #63535
- Fixed a bug where multiple concurrent invocations of `cockroach debug zip` could yield cluster instability. This bug had been present since CockroachDB v20.1. #64083
- When a `STRING` value is converted to `TIME`/`DATE`/`TIMESTAMP`, and the `STRING` value contains invalid entries, the error messages reported now more clearly report which fields are missing or undesired. #63540
- Fixed a bug where view expressions created using an `ARRAY` `ENUM` without a name for the column could cause failures when dropping unrelated `ENUM` values. #64272
- Fixed a bug causing an internal error in rare circumstances when executing queries via the vectorized engine that operate on columns of `BOOL`, `BYTES`, `INT`, and `FLOAT` types that have a mix of `NULL` and non-`NULL` values. #62915
- Fixed a bug causing CockroachDB to either return an error or crash when comparing an infinite `DATE` coming from a subquery against a `TIMESTAMP`. #64074
- CockroachDB now should crash less often due to out-of-memory conditions caused by the subqueries returning multiple rows of large size. #64727
- Previously, the session trace could contain entries that corresponded to the previous trace (i.e., `SET TRACING=ON` didn't properly reset the trace). Now this is fixed. #64945
- Previously, CockroachDB could incorrectly cast integers of larger widths to integers of smaller widths (e.g., `INT8` to `INT2`) when the former was out of range for the latter. Now this is fixed. #65035
- Fixed a race condition where read-write requests during replica removal (e.g., during range merges or rebalancing) could be evaluated on the removed replica. #64598
- `BACKUP` no longer resolves intents one-by-one. This eliminates the need to run a high-priority query to cleanup intents to unblock `BACKUP` in the case of intent buildup. #64881
- Fixed an internal error that could occur during planning when a query used the output of an `UPDATE`'s `RETURNING` clause, and one or more of the columns in the `RETURNING` clause were from a table specified in the `FROM` clause of the `UPDATE` (i.e., not from the table being updated). #62960
- Fixed an index-out-of-range error that could occur when `crdb_internal_mvcc_timestamp` was selected as part of a `view`. It is now possible to select `crdb_internal_mvcc_timestamp` as part of a view as long as it is aliased with a different name. #63632
- Fixed a bug in the application of environment variables to populate defaults for certain command-line flags, for example `COCKROACH_URL` for `--url`, has been fixed. #63539
- Fixed a stack overflow that can happen in some corner cases involving partial indexes with predicates containing `(x IS NOT NULL)`. #64738
- Providing a constant value as an `ORDER BY` value in an ordered-set aggregate, such as `percentile_dist` or `percentile_cont`, no longer returns an error. This bug has been present since order-set aggregates were added in version 20.2. #64902
- Queries that reference tables with `GEOMETRY` or `GEOGRAPHY` GIN indexes and that call geospatial functions with constant `NULL` values cast to a type, like `NULL::GEOMETRY` or `NULL::FLOAT8`, no longer error. This bug was present since 20.2. #63003
- Fixed a bug causing CockroachDB to incorrectly calculate the latency from the remote nodes when the latency info was shown on the `EXPLAIN ANALYZE (DISTSQL)` diagrams. #63089
- Fixed a bug causing the `ZONECONFIG` privilege on tables and databases to be incorrectly interpreted as `USAGE`, which could corrupt a table and/or database because `USAGE` is an invalid privilege for tables and databases. #65160
- Fixed a bug which could cause a panic when running an `EXECUTE` of a previously-prepared statement with a `REGCLASS` or `REGTYPE` parameter or a user-defined type argument after running `BEGIN AS OF SYSTEM TIME` with an invalid timestamp. #65150
- Fixed a bug which could cause a panic when issuing a query referencing a user-defined type as a placeholder. #65150
- Fixed a bug introduced in 20.2 that caused rows to be incorrectly de-duplicated from a scan with a non-unique index. #65284
- Fixed a bug where interval math on a `TIMESTAMPTZ` value on a DST boundary would incorrectly add or subtract an extra hour. #65095
- Fixed a bug where `date_trunc` on a `TIME` value on a DST boundary could switch timezones and produce the incorrect result. #65095
- Improved memory utilization under some write-heavy workloads, added better logging to storage engine to surface compaction type, and persisted previously-missing Pebble options in `OPTIONS` file. #65308
- Fixed a bug causing `revision_history` cluster backups to not include dropped databases. This means that, previously, dropped databases could not be restored from backups that were taken after the database was dropped. #65314
- Fixed a bug causing `SHOW CREATE TABLE` output to not display the zone configurations of a table or index if there were no partitions, even if there were zone configurations on the index or table. #65176
- Previously, concatenating a non-`STRING` value with a `STRING` value would not use the normal `STRING` representation of the non-`STRING` value. Now it does, so `true || 'string'` returns `truestring` instead of `tstring`. #65331
- Large `SELECT FOR UPDATE` scans will no longer prevent the memory associated with their entire result set from being reclaimed by the Go garbage collector for the lifetime of the locks that they acquire. #65359
- Fixed a rare race that could lead to a 3-second stall before a Raft leader was elected on a Range immediately after it was split off from its left-hand neighbor. #65356
- Fixed a bug where `SHOW CREATE TABLE` would show the zone configurations of a table of the same name from a different schema. #65368
- `BACKUP`, `RESTORE`, and `IMPORT` are now more resilient to node failures and will retry automatically. #65391
- Previously, replica rebalancing could sometimes rebalance to stores on dead nodes. This bug is now fixed. #65428

### Performance improvements

- The optimizer now always prefers to plan a locality-optimized scan over a regular scan when possible. This may enable the execution engine to avoid communicating with remote nodes, thus reducing query latency. #65088
- The optimizer will now try to plan anti lookup joins using "locality-optimized search". This optimization applies for anti lookup joins into `REGIONAL BY ROW` tables (i.e., the right side of the join is a `REGIONAL BY ROW` table), and if enabled, it means that the execution engine will first search locally for matching rows before searching remote nodes. If a matching row is found in a local node, remote nodes will not be searched. This optimization may improve the performance of foreign key checks when rows are inserted or updated in a table that references a foreign key in a `REGIONAL BY ROW` table. #63118
- Certain queries containing `<tuple> IN (<subquery>)` conditions now run faster. #63866
- Improved intent cleanup performance for aborted transactions. #64588
- Adjusted the estimated cost of locality-optimized anti joins in the optimizer so that they are always chosen over non-locality-optimized anti joins when possible. This makes it more likely that queries involving anti joins (such as inserts with foreign key checks) can avoid visiting remote regions. This results in lower latency. #65131
- The optimizer can now avoid full table scans for queries with a `LIMIT` and `ORDER BY` clause, where the `ORDER BY` columns form a prefix on an index in a `REGIONAL BY ROW` table (excluding the hidden `crdb_region` column). Instead of a full table scan, at most `LIMIT` rows are scanned per region. #65287

### Contributors

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

- Kumar Akshay
- Mohammad Aziz (first-time contributor)
- kurokochin (first-time contributor)

## v21.1.0

Release Date: May 18, 2021

With the release of CockroachDB v21.1, we've made a variety of flexibility, performance, and compatibility improvements. Check out a summary of the most significant user-facing changes and then upgrade to CockroachDB v21.1.

To learn more:

- Read the v21.1 blog post.
- Join us on May 19 for a livestream on why multi-region applications matter and how our Product and Engineering teams partnered to make them simple in v21.1.

### CockroachCloud

- Get a free v21.1 cluster on CockroachCloud.
- Learn about recent updates to CockroachCloud in the CockroachCloud Release Notes.

### Feature summary

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

Note:

"Core" features are freely available in the core version and do not require an enterprise license. "Enterprise" features require an enterprise license. CockroachCloud clusters include all enterprise features. You can also use `cockroach demo` to test enterprise features in a local, temporary cluster.

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

#### SQL

Version | Feature | Description
--- | --- | ---
Enterprise | Multi-Region Improvements | It is now much easier to leverage CockroachDB's low-latency and resilient multi-region capabilities. For an introduction to the high-level concepts, see the Multi-Region Overview. For further details and links to related SQL statements, see Choosing a Multi-Region Configuration, When to Use ZONE vs. REGION Survival Goals, and When to Use REGIONAL vs. GLOBAL Tables. For a demonstration of these capabilities using a local cluster, see the Multi-Region Tutorial. Finally, for details about related architectural enhancements, see Non-Voting Replicas and Non-Blocking Transactions.
Enterprise | Automatic Follower Reads for Read-Only Transactions | You can now force all read-only transactions in a session to use follower reads by setting the new `default_transaction_use_follow_reads` session variable to `on`.
Core | Query Observability Improvements | `EXPLAIN` and `EXPLAIN ANALYZE` responses have been unified and extended with additional details, including automatic statistics-backed row estimates for `EXPLAIN`, and maximum memory usage, network usage, nodes used per operator, and rows used per operator for `EXPLAIN ANALYZE`. `EXPLAIN ANALYZE` now outputs a text-based statement plan tree by default, showing statistics about the statement processors at each phase of the statement. The Transactions Page and Statements Page of the DB Console also include such details as well the mean average time statements were in contention with other transactions within a specified time interval. The SQL Dashboard has been expanded with additional graphs for latency, contention, memory, and network traffic. The SQL Tuning with `EXPLAIN` tutorial and Optimize Statement Performance guidance have been updated to leverage these improvements.
Core | Inverted Joins | CockroachDB now supports inverted joins, which force the optimizer to use a GIN index on the right side of the join. Inverted joins can only be used with `INNER` and `LEFT` joins.
Core | Partial GIN Indexes | You can now create a partial GIN index on a subset of `JSON`, `ARRAY`, or geospatial container column data.
Core | Virtual Computed Columns | You can now create virtual computed columns, which are not stored on disk and are recomputed as the column data in the expression changes.
Core | Dropping Values in User-Defined Types | It's now possible to drop values in user-defined types.
Core | Sequence Caching | You can now create a sequence with the `CACHE` keyword to have the sequence cache its values in memory.
Core | Changing Sequence & View Ownership | You can use the new `OWNER TO` parameter to change the owner of a sequence or view.
Core | Show `CREATE` Statements for the Current Database | You can now use `SHOW CREATE ALL TABLES` to return the `CREATE` statements for all of the tables, views, and sequences in the current database.
Core | Storage of Z/M Coordinates for Spatial Objects | You can now store a third dimension coordinate (`Z`), a measure coordinate (`M`), or both (`ZM`) with spatial objects. Note, however, that CockroachDB's spatial indexing is still based on the 2D coordinate system. This means that the Z/M dimension is not index accelerated when using spatial predicates, and some spatial functions ignore the Z/M dimension, with transformations discarding the Z/M value.
Core | Third-Party Tool Support | Spatial libraries for Hibernate, ActiveRecord, and Django are now fully compatible with CockroachDB's spatial features. The DataGrip IDE and Liquibase schema migration tool are also now supported.
Core | Connection Pooling Guidance | Creating the appropriate size pool of connections is critical to gaining maximum performance in an application. For guidance on sizing, validating, and using connection pools with CockroachDB, as well as examples for Java and Go applications, see Use Connection Pools.
Core | PostgreSQL 13 Compatibility | CockroachDB is now wire-compatible with PostgreSQL 13. For more information, see PostgreSQL Compatibility.

#### Recovery and I/O

Version | Feature | Description
--- | --- | ---
Enterprise | Changefeed Topic Naming Improvements | New `CHANGEFEED` options give you more control over topic naming: The `full_table_name` option lets you use a fully-qualified table name in topics, subjects, schemas, and record output instead of the default table name, and can prevent unintended behavior when the same table name is present in multiple databases. The `avro_schema_prefix` option lets you use a fully-qualified schema name for a table instead of the default table name, and makes it possible for multiple databases or clusters to share the same schema registry when the same table name is present in multiple databases.
Core | Running Jobs Asynchronously | You can use the new `DETACHED` option to run `BACKUP`, `RESTORE`, and `IMPORT` jobs asynchronously and receive a job ID immediately rather than waiting for the job to finish. This option enables you to run such jobs within transactions.
Core | Import from Local Dump File | The new `cockroach import` command imports a database or table from a local `PGDUMP` or `MYSQLDUMP` file into a running cluster. This is useful for quick imports of 15MB or smaller. For larger imports, use the `IMPORT` statement.
Core | Additional Import Options | New `IMPORT` options give you more control over the import process's behavior: The `row_limit` option limits the number of rows to import, which is useful for finding errors quickly before executing a more time- and resource-consuming import; the `ignore_unsupported_statements` option ignores SQL statements in `PGDUMP` files that are unsupported by CockroachDB; and the `log_ignored_statements` option logs unsupported statements to cloud storage or userfile storage when `ignore_unsupported_statements` is enabled.
Core | Re-validating Indexes During `RESTORE` | Incremental backups created by v20.2.2 and prior v20.2.x releases or v20.1.4 and prior v20.1.x releases may include incomplete data for indexes that were in the process of being created. Therefore, when incremental backups taken by these versions are restored by v21.1.0, any indexes created during those incremental backups will be re-validated by `RESTORE`.

#### Database Operations

Version | Feature | Description
--- | --- | ---
Core | Logging Improvements | Log events are now organized into logging channels that address different use cases. Logging channels can be freely mapped to log sinks and routed to destinations outside CockroachDB (including external log collectors). All logging aspects, including message format (e.g., JSON), are now configurable via YAML.
Core | Cluster API v2.0 | This new API for monitoring clusters and nodes builds on prior endpoints, offering a consistent REST interface that's easier to use with your choice of tooling. The API offers a streamlined authentication process and developer-friendly reference documentation.
Core | OpenShift-certified Kubernetes Operator | You can now deploy CockroachDB on the Red Hat OpenShift platform using the latest OpenShift-certified Kubernetes Operator.
Core | Auto TLS Certificate Setup | Using the new `cockroach connect` command, you can now let CockroachDB handle the creation and distribution among nodes of a cluster's CA (certificate authority) and node certificates. Note that this feature is an alpha release with core functionality that may not meet your requirements.
Core | Built-in Timezone Library | The CockroachDB binary now includes a copy of the `tzdata` library, which is required by certain features that use time zone data. If CockroachDB cannot find the `tzdata` library externally, it will now use this built-in copy.

#### Backward-incompatible changes

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

- Rows containing empty arrays in `ARRAY` columns are now contained in GIN indexes. This change is backward-incompatible because prior versions of CockroachDB will not be able to recognize and decode keys for empty arrays. Note that rows containing `NULL` values in an indexed column will still not be included in GIN indexes.
- Concatenation between a non-null argument and a null argument is now typed as string concatenation, whereas it was previously typed as array concatenation. This means that the result of `NULL || 1` will now be `NULL` instead of `{1}`. To preserve the old behavior, the null argument can be casted to an explicit type.
- The payload fields for certain event types in `system.eventlog` have been changed and/or renamed. Note that the payloads in `system.eventlog` were undocumented, so no guarantee was made about cross-version compatibility to this point. The list of changes includes (but is not limited to):
  - `TargetID` has been renamed to `NodeID` for `node_join`.
  - `TargetID` has been renamed to `TargetNodeID` for `node_decommissioning` / `node_decommissioned` / `node_recommissioned`.
  - `NewDatabaseName` has been renamed to `NewDatabaseParent` for `convert_to_schema`.
  - `grant_privilege` and `revoke_privilege` have been removed; they are replaced by `change_database_privilege`, `change_schema_privilege`, `change_type_privilege`, and `change_table_privilege`. Each event only reports a change for one user/role, so the `Grantees` field was renamed to `Grantee`.
  - Each `drop_role` event now pertains to a single user/role.
- The connection and authentication logging enabled by the cluster settings `server.auth_log.sql_connections.enabled` and `server.auth_log.sql_sessions.enabled` was previously using a text format which was hard to parse and integrate with external monitoring tools. This has been changed to use the standard notable event mechanism, with standardized payloads. The output format is now structured; see the reference documentation for details about the supported event types and payloads.
- The format for SQL audit, execution, and query logs has changed from a crude space-delimited format to JSON. To opt out of this new behavior and restore the pre-v21.1 logging format, you can set the cluster setting `sql.log.unstructured_entries.enabled` to `true`.
- The `cockroach debug ballast` command now refuses to overwrite the target ballast file if it already exists. This change is intended to prevent mistaken uses of the `ballast` command by operators. Scripts that integrate `cockroach debug ballast` can consider adding a `rm` command.
- Removed the `kv.atomic_replication_changes.enabled` cluster setting. All replication changes on a range now use joint-consensus.
- Currently, changefeeds connected to Kafka versions < v1.0 are not supported in CockroachDB v21.1.

#### Deprecations

- The CLI flags `--log-dir`, `--log-file-max-size`, `--log-file-verbosity`, and `--log-group-max-size` are deprecated. Logging configuration can now be specified via the `--log` parameter. See the Logging documentation for details.
- The client-side command `\show` for the SQL shell is deprecated in favor of the new command `\p`. This prints the contents of the query buffer entered so far.
- Currently, Google Cloud Storage (GCS) connections default to the `cloudstorage.gs.default.key` cluster setting. This default behavior will no longer be supported in v21.2. If you are relying on this default behavior, we recommend adjusting your queries and scripts to now specify the `AUTH` parameter you want to use. Similarly, if you are using the `cloudstorage.gs.default.key` cluster setting to authorize your GCS connection, we recommend switching to use `AUTH=specified` or `AUTH=implicit`. `AUTH=specified` will be the default behavior in v21.2 and beyond.

#### Known limitations

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

#### Education

Area | Topic | Description
--- | --- | ---
Cockroach University | New Intro Courses | Introduction to Distributed SQL and CockroachDB teaches you the core concepts behind distributed SQL databases and describes how CockroachDB fits into this landscape. Practical First Steps with CockroachDB is a hands-on sequel that gives you the tools to get started with CockroachDB.
Cockroach University | New Java Course | Fundamentals of CockroachDB for Java Developers guides you through building a full-stack vehicle-sharing app in Java using the popular Spring Data JPA framework with Spring Boot and a CockroachCloud Free cluster as the backend.
Cockroach University | New Query Performance Course | CockroachDB Query Performance for Developers teaches you key CockroachDB features and skills to improve application performance and functionality, such as analyzing a query execution plan, using indexes to avoid expensive full table scans, improving sorting performance, and efficiently querying fields in JSON records.
Docs | Quickstart | Documented the simplest way to get started with CockroachDB for testing and app development by using CockroachCloud Free.
Docs | Developer Guidance | Published more comprehensive, task-oriented guidance for developers building applications on CockroachDB, including connecting to a cluster, designing a database schema, reading and writing data, optimizing query performance, and debugging applications.
Docs | Connection Pooling | Added guidance on planning, configuring, and using connection pools with CockroachDB, as well as examples for Java and Go applications.
Docs | Sample Apps on CockroachCloud Free | Updated various Java, Python, Node.js, Ruby, and Go sample app tutorials to offer CockroachCloud Free as the backend.
Docs | Licensing FAQs | Updated the Licensing FAQ to explain our licensing types, how features align to licenses, how to perform basic tasks around licenses (e.g., obtain, set, verify, monitor, renew), and other common questions.
Docs | Product Limits | Added object sizing and scaling considerations, including specific hard limits imposed by CockroachDB and practical limits based on our performance testing and observations.
Docs | System Catalogs | Documented important internal system catalogs that provide non-stored data to client applications.

## v21.1.0-rc.2

Release Date: May 5, 2021

### Enterprise edition changes

- Changefeeds now reliably fail when `IMPORT INTO` is run against a targeted table, as change data capture is not supported for this action. #64372

### Bug fixes

- Fixed a correctness bug, which caused partitioned index scans to omit rows where the value of the first index column was `NULL`. This bug was present since v20.1.0. #64046
- `IMPORT` and `RESTORE` jobs that were in progress during a cluster backup will now be canceled when that cluster backup is restored. This fixes a bug where these restored jobs may have assumed to make progress that was not captured in the backup. #64352
- Fixed a race condition where read-only requests during replica removal (for example, during range merges or rebalancing) could be evaluated on the removed replica, returning an empty result. #64370
- Fixed a bug where encryption-at-rest metadata was not synced and could become corrupted during a hard reset. #64473

### Contributors

This release includes 5 merged PRs by 6 authors.

## v21.1.0-rc.1

Release Date: May 5, 2021

### SQL language changes

- CockroachDB no longer allows `ADD REGION` or `DROP REGION` statements if a `REGIONAL BY ROW` table has index changes underway, or if a table is transitioning to or from `REGIONAL BY ROW`. #64255
- CockroachDB now prevents index modification on `REGIONAL BY ROW` tables and locality to or from `REGIONAL BY ROW` changes while an `ADD REGION` or `DROP REGION` statement is being executed. #64255

### Bug fixes

- Fixed a scenario in which a rapid sequence of range splits could trigger a storm of Raft snapshots. This would be accompanied by log messages of the form "would have dropped incoming MsgApp, but allowing due to ..." and tended to occur as part of `RESTORE`/`IMPORT` operations. #64202
- Read-write contention on `GLOBAL` tables no longer has a potential to thrash without making progress. #64215
- Previously, if a `DROP INDEX` failed during a `REGIONAL BY ROW` transition, the index could have been re-inserted back into the `REGIONAL BY ROW` table but would be invalid if it was hash-sharded or partitioned. This bug is now fixed. #64255
- Fixed a rare bug present in v21.1 beta versions that could cause rapid range splits and merges on a `GLOBAL` table to lead to a stuck leaseholder replica. The situation is no longer possible. #64304
- Fixed a bug in previous v21.1 beta versions that allowed the store rebalancer to spuriously down-replicate a range during normal operation. #64303
- CockroachDB now prevents some out-of-memory conditions caused by schema change validations concurrent with other high-memory-use queries. #64307
- Fixed a bug present since v21.1.0-alpha.1 that could cause cascading `DELETE`s with subqueries to error. #64278
- Fixed a bug that caused store information to be incorrectly redacted from the CockroachDB logs, when logging was configured with redaction. #64338
- Previously, the remote flows of execution in the vectorized engine could take a long time to shut down if a node participating in the plan died. This bug is now fixed. #64219

### Contributors

This release includes 14 merged PRs by 14 authors.

## v21.1.0-beta.5

Release Date: April 29, 2021

### Docker image

    $ docker pull cockroachdb/cockroach-unstable:v21.1.0-beta.5

### Backward-incompatible changes

- The internal representation of the `voter_constraints` zone configuration attribute (new in v21.1) has been altered in a way that is partially incompatible with the representation used by previous v21.1 betas (and the alphas that include this attribute). This means that users who directly set the `voter_constraints` attribute to an empty list will lose those constraints and will have to reset them. #63674

### General changes

- Upgraded the CockroachDB binary to Go 1.15.10. #63865

### Enterprise edition changes

- Changefeeds will now fail on any regional by row table with an error, `CHANGEFEED cannot target REGIONAL BY ROW tables: <table_name>.` This is to prevent unexpected behavior in changefeeds until they offer full support for this type of table. #63542

### SQL language changes

- `RESTORE` now re-validates restored indexes if they were restored from an incremental backup that was taken while the index was being created. #63320
- The `sql.distsql.temp_storage.workmem` cluster setting is now marked as public and is included in the documentation. It determines how much RAM a single operation of a single query can use before it must spill to temporary storage. Note the operations that do not support the disk spilling will ignore this setting and are subject only to the `--max-sql-memory` startup argument. #63997
- SQL executor data validation queries spawned by a schema change or a `RESTORE` will now use vectorized query execution and DistSQL optimization if these are enabled in the cluster settings `sql.defaults.vectorize` and `sql.defaults.distsql`, respectively. This may improve the speed of these queries. #64004

### Bug fixes

- Allow the leases of offline descriptors to be cached, preventing issues with lease acquisitions during bulk operations (backup and restore operations). #63558
- Fixed bugs where `TRUNCATE` concurrent with index construction and other schema changes could result in corruption. #63143
- Fixed a panic condition which could occur in cases after a `RESTORE` of a table with user-defined types. #63549
- CockroachDB now prevents a panic condition and offers a graceful error when spatial function `ST_Segmentize` attempts to generate an extremely large number of points on a `GEOGRAPHY`. #63758
- Previously, running the `ST_Simplify` spatial function on a non-numeric value would cause the node to crash. This is now resolved. #63797
- CockroachDB now uses the existing primary key to validate indexes built for `ALTER PRIMARY KEY` changes. #63609
- Fixed occasional stalls and excessive CPU usage under macOS Big Sur when building CockroachDB with Go 1.14 or newer. #63789
- Fixed a bug where crdb_internal.validate_multi_region_zone_configs() would fail during a `REGIONAL BY ROW` locality transition. #63834
- Fixed an internal error that could occur when executing queries using a GIN index. The error was an index out of range error, and could occur in rare cases when a filter or join predicate contained at least two JSON, Array, Geometry or Geography expressions that were combined with `AND`. This has now been fixed. #63811
- Fixed a bug leading to crashes with the error message `writing below closed ts`. #63861
- Previously, if a user altered a table to `REGIONAL BY ROW` when a region was being dropped, and the drop failed and had to be rolled back, it could have resulted in the regional by row table missing a partition for this region. This is now fixed. #63793
- Prevent an internal error `use of enum metadata before hydration as an enum` when querying or showing ranges from tables with user-defined types as their `PRIMARY KEY`. #63878
- Fixed a theoretical issue in index backfills that could result in stale entries that would likely fail validation. [#64044][#64044]
- CockroachDB now correctly accounts for used memory when closing compressed files. #63917

### Performance improvements

- CockroachDB now limits a series of heap allocations when serving read-only queries. #63972
- CockroachDB now limits the amount of memory that can be used in internal buffers for Kafka and cloud sinks. #63611

### Contributors

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

- Miguel Novelo (first-time contributor)
- Rupesh Harode (first-time contributor)

## v21.1.0-beta.4

Release Date: April 19, 2021

### General changes

- Removed experimental feature `UNIQUE WITHOUT INDEX` from the documentation. #63499

### SQL language changes

- The `pg_get_partkeydef` built-in function is now implemented by always returning `NULL`. #63149
- CockroachDB now collects execution stats for all statements when seen for the first time. To disable this behavior, set the `sql.txn_stats.sample_rate` cluster setting to 0, which will disable all execution stats collection. #63325
- CockroachDB will now block the ability to set the initial `PRIMARY REGION` of a database if any multi-region fields on any zone configs in the database have been set. #63354
- CockroachDB now introduces a `pgcode` when attempting to `DROP REGION` when the region being dropped is the `PRIMARY REGION`. #63354
- Replaced the word "tuple" with its more user-friendly synonym "row" in vectorized stats outputs. #62956
- Changed `BACKUP` of interleaved tables to require the `include_deprecated_interleaves` option as interleaved table backups will not be able to be restored in future versions of CockroachDB. #63501

### Operational changes

- `RESTORE` now cannot restore `BACKUP`s made by newer versions of CockroachDB. #62398

### DB Console changes

The following statements now render correctly as events in the DB Console #63141: - `ALTER DATABASE ADD REGION` - `ALTER DATABASE SET PRIMARY REGION` - `ALTER DATABASE ... SURVIVE ... FAILURE` - `ALTER DATABASE DROP REGION` - `CREATE TYPE` - `ALTER TYPE` - `DROP TYPE`

### Bug fixes

- Fixed a bug present in earlier 21.1 versions where `BACKUP`s would produce an error when they should be able to backup the underlying data. #63095
- Dropping a foreign key that was added in the same transaction no longer triggers an internal error. This bug has been present since at least version 20.1. #62879
- Fixed a bug where an `ALTER TABLE ... ADD COLUMN ... UNIQUE` statement would cause an error if the table had a `PARTITION ALL BY` or `REGIONAL BY ROW` definition. #63189
- Fixed a bug in earlier 21.1 versions where `CREATE TABLE LIKE` would copy a `VIRTUAL` column from the source table as a `STORED` column in the destination table. #63172
- CockroachDB now returns an error when trying to perform a backup of a cluster that was taken on another tenant. #63223
- Fixed a bug where index backfill data may have been missed by `BACKUP` in incremental backups. #63221
- Fixed a bug where `REGIONAL BY ROW` zone configs were dropped before `REGIONAL BY ROW` changes are finalized. This caused a bug when the `REGIONAL BY ROW` transformation fail. #63274
- Fixed a case where implicitly partitioned columns (e.g., from `REGIONAL BY ROW` tables and hash-sharded indexes) previously showed as `implicit = false` when using `SHOW INDEXES` or querying `information_schema.pg_indexes`. #63275
- Fixed an error that could occur when performing an `UPSERT` on a `REGIONAL BY ROW` table with no secondary indexes or foreign keys. The error, 'missing "crdb_region" primary key column', has now been fixed. #63257
- Fixed a bug where tables that were created by CockroachDB 19.x or older that included foreign key constraints and were backed up with the `revision_history` option would be malformed when restored by a CockroachDB 20.x cluster if the `RESTORE` used the `AS OF SYSTEM TIME` option. #63267
- Fixed a bug in user-defined schemas where the dropping of any schema would prevent creation of schemas with the name of the database and would corrupt existing schemas of that name. #63395
- Fixed a bug in previous CockroachDB 21.1 releases where CockroachDB would sometimes return the output in an incorrect order if a query containing hash-aggregation was executed via the vectorized execution engine and spilled to temporary storage. #63408
- Fixed a bug where incremental cluster backups may have missed data written to tables while they were `OFFLINE`. In practice this could have occurred if a `RESTORE` or `IMPORT` was running across incremental backups. #63304
- CockroachDB now includes more anonymized data from SQL statements in telemetry updates and crash reports. #63482
- Fixed a rare issue that caused replica divergence. If this occurred the divergence was reported by the replica consistency checker, typically within 24 hours of occurrence, and caused the nodes to terminate. #63473

### Performance improvements

- Improved performance of reverting `IMPORT INTO` jobs that `IMPORT INTO` empty tables. #63220

### Miscellaneous improvements

- Made the Kafka library used in changefeeds configurable using the `kafka_sink_config` option to enable latency versus throughput configurations. #63361
- Connected the changefeed memory monitor to the parent SQL monitor to ensure that changefeeds do not try to use more memory than is available to the SQL server. #63409

### Contributors

This release includes 50 merged PRs by 20 authors.

## v21.1.0-beta.3

Release Date: April 12, 2021

### Enterprise edition changes

- The `WITH avro_schema_prefix` option for Avro changefeeds now sets `schema.namespace` #61734
- CockroachDB now fails fast when Change Data Capture writes are blocked. #62756

### SQL language changes

#### Multi-region SQL changes

- Users can now use a multi-region `ALTER DATABASE` command if:

  - The user is an `admin` user
  - The user is the owner of the database.
  - The user has `CREATE` privileges on the database. #62528

- Availability zones are now ordered when using the `SHOW REGIONS` set of commands. #62619

#### General SQL changes

- Added the `stub_catalog_tables` session variable, which is enabled by default. If disabled, querying an unimplemented `pg_catalog` table will result in an error, as is the case in v20.2 and earlier. Otherwise, the query will simply return no rows. #62621

### DB Console changes

- The Statements page now shows internal statements when the all filter option is selected. #62677

### Bug fixes

- Fixed a bug that in rare circumstances could cause an implicitly committed (`STAGING`) transaction to be uncommitted if any unresolved intents were removed by a range clear (e.g., when cleaning up a dropped table). This bug fix is only effective with separated intents, which are disabled by default. #62376
- Added a `DuplicateObject` error code for when a user attempts to `ADD REGION` to a database where the region already exists. #62491
- Fixed an internal error that could occur during planning for queries involving tables with many columns and at least one GIN index. The error, "estimated distinct count must be non-zero", was caused by an invalid pointer access in the cardinality estimation code. This has now been fixed. #62545
- Writing files to `userfile` would sometimes result in an error claiming that the `userfile` table already exists. This is now fixed. #62544
- When adding/dropping regions from a multi-region database, the user must now have privileges on all regional-by-row tables as these are implicitly re-partitioned under the hood. #62612
- Fixed an internal error caused by comparing collation names that had different upper/lower case characters. #62637
- Fixed a bug whereby `ENUM` types which have large numbers of values would cause unexpected errors when attempting to read from tables with columns using that `ENUM` type. #62210
- Fixed a bug introduced in earlier v21.1 alpha releases which could cause panics when dropping indexes on tables partitioned by user-defined types. #62725
- Fixed a bug from earlier v21.1 alpha releases whereby dropping an index on a table partitioned by a user-defined type and then dropping the table and then dropping the type before the GC TTL for the index has expired could result in a crash. #62725

### Performance improvements

- Improved the performance of the vectorized engine when scanning fewer than 1024 rows at a time. #62365
- Improved logic in determining the configuration for data to avoid expensive work when there are a large number of user-defined schemas. #62577
- Addressed a performance regression from a past change regarding read-triggered compactions. #62676

### Contributors

This release includes 37 merged PRs by 23 authors.

## v21.1.0-beta.2

Release Date: March 30, 2021

### SQL language changes

#### Multi-region changes

- Added validation that prevents users from updating the zone configurations of multi-region tables without first setting the `override_multi_region_zone_config` session variable. #62119
- Discarding a zone configuration from a multi-region enabled entity is blocked behind the `override_multi_region_zone_config` session variable. #62159
- Reverted the change that added the `FORCE` keyword in #61499 in favor of the `override_multi_region_zone_config` session variable. #62119
- Setting non-multi-region controlled fields on zone configs before `ALTER DATABASE ... SET PRIMARY REGION` will now be preserved and have the same value after the `SET PRIMARY REGION` command is issued. #62162
- Materialized views in multi-region databases will now have a `GLOBAL` table locality. #62194
- Materialized views which are in a database before the first `ADD REGION` will become `GLOBAL` on `ADD REGION`, in line with the behavior of `CREATE MATERIALIZED VIEW` on a multi-region database. #62194
- `ALTER DATABASE .. SET PRIMARY REGION` now requires both `CREATE` and `ZONECONFIG` privilege on all objects inside the database when adding the first region to the database. The same behavior applies for dropping the last region using `ALTER DATABASE ... DROP REGION`. #62450
- Removed the experimental multi-region locality syntaxes. #62114

#### General changes

- CockroachDB now stores information about contention on non-SQL keys. #62041
- Statement diagnostics bundles now contain output of `EXPLAIN (VEC)` and `EXPLAIN (VEC, VERBOSE)` commands for the statements. #62049
- Sampled execution stats are now available through `crdb_internal.node_{statement,transaction}_statistics`. #62089
- Increased the default value for the `sql.txn_stats.sample_rate` cluster setting from 0 to 0.1. This means that from now on every statement has 10% probability of being sampled for the purposes of execution statistics. Note that no other criteria for sampling (such as query latency) are currently being utilized to decide whether to sample a statement or not. #61815
- Added the following cluster settings: `sql.defaults.statement_timeout`, which controls the default value for the `statement_timeout` session setting; `sql.defaults.idle_in_transaction_session_timeout`, which controls the default value for the `idle_in_transaction_session_timeout` timeout setting; `sql.defaults.idle_in_session_timeout`, which already existed, but is now a public cluster setting. #62182
- `EXPLAIN` and `EXPLAIN ANALYZE` now show how long ago table statistics were collected. #61945

### Command-line changes

- Changed the formatting of namespace validation failures in `cockroach debug doctor` output. #62245

### Bug fixes

- Fixed a bug where the `target` column of `crdb_internal.zones` would show names without properly accounting for user-defined schemas. #62022
- Added validation that prevents regions being dropped on multi-region databases when there are <= 3 regions left on the database. #62162
- Fixed a bug where zone configurations were not being correctly dropped on the final `DROP REGION` of a multi-region database. #62162
- Fixed a bug where `VIEW`s and `SEQUENCE`s were not being allowed in multi-region databases. They will now default to the `REGIONAL BY TABLE` locality. #62176
- Fixed a bug where the `pg_type_is_visible` built-in function did not correctly handle user-defined types. #62225
- Fixed a bug where casting an `OID` to a `regtype` did not work for user-defined types. #62225
- A Raft leader who loses quorum will now relinquish its range lease and remove the replica if the range is recreated elsewhere, e.g., via `Node.ResetQuorum()`. #62103
- Fixed a bug where `ClearRange` could leave behind stray write intents when separated intents were enabled, which could cause subsequent storage errors. #62104
- `ALTER TABLE`, `ALTER VIEW`, and `ALTER SEQUENCE` can no longer be used to incorrectly create cross-DB references. #62341
- Disallowed adding columns of type `OIDVECTOR` or `INT2VECTOR` to a table in `ALTER TABLE ... ADD COLUMN` statements. These types are not allowed in user-created tables via `CREATE TABLE` and were previously erroneously allowed in `ALTER TABLE ... ADD COLUMN`. #62180
- CockroachDB now logs all unsupported `pgdump` statements across smaller log files that can be found in the subdirectory `import<jobID>/(unsupported_schema_stmts|unsupported_data_stmts)/<filenum>.log` #62263
- Fixed a bug where a constraint like `NOT NULL` or `CHECK` on a column made irrelevant by a `DROP CONSTRAINT` statement in a later concurrent transaction would lead to errors / incorrect behaviour. #62249
- Fixed an internal error that could occur when `REGIONAL BY ROW` tables were joined with other tables using a lookup or inverted join. The internal error was `"we expect that limited UNION ALL queries are only planned locally"`. #62383
- Fixed a bug where using `DROP REGION` on the last region of a multi-region database would not delete the global zone configurations for `GLOBAL` tables. #62220
- Fixed a bug where duplicate `IMPORT` job records may have been created, or `IMPORT` statements may have failed, when the actual job succeeded. #62396
- Fixed a bug where CockroachDB could collect execution statistics prematurely, which would result in incorrect stats (e.g., when running `EXPLAIN ANALYZE`). #62384
- Fixed a bug where setting the `kv.closed_timestamp.target_duration` to 0 did not disable routing requests to follower replicas. #62439
- Fixed a bug where a failed restore from a backup including user defined types would require manual cleanup. #62454

### Contributors

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

- Tharun

## v21.1.0-beta.1

Release Date: March 22, 2021

### Backward-incompatible changes

- The `cockroach debug ballast` command now refuses to overwrite the target ballast file if it already exists. This change is intended to prevent mistaken uses of the `ballast` command by operators. Scripts that integrate `cockroach debug ballast` can consider adding a `rm` command. #59995
- Removed the `kv.atomic_replication_changes.enabled` cluster setting. All replication changes on a range now use joint-consensus. #61170

### Security updates

- It is now possible to log SQL statements executed by admin users. This logging is enabled via the `sql.log.admin_audit.enabled` cluster setting. When set, events of type `admin_query` are logged to the `SENSITIVE_ACCESS` channel. #60708

### General changes

- Updated the AWS SDK used to interface with AWS services such as S3 (for backup, restore, and import) and KMS (for backup/restore). #59709
- `SHOW TRACE FOR SESSION` previously included CockroachDB internal traces for async threads kicked off as part of user operations. This trace data is no longer captured. #59815
- Crash reports that are sent to Cockroach Labs no longer redact the names of built-in virtual tables from the `crdb_internal`, `information_schema`, and `pg_catalog` schemas. #60799
- Raised the default limit on the maximum number of spans that can be protected by the `protectedts` subsystem. This limit can be configured using the `kv.protectedts.max_spans` cluster setting. #61018

### Enterprise edition changes

- Added the `bulkio.stream_ingestion.minimum_flush_interval` cluster setting, which allows the user to control how often the stream ingestion job will flush. Note that the job may still flush more often if the in-memory buffers are filled. #60160
- CockroachDB now supports primary key changes in `CREATE CHANGEFEED`. #58422
- Multi-region database creations are now permitted as long as the cluster has a CockroachDB subscription. #61041
- `ALTER DATABASE ... ADD REGION` now requires an enterprise license.#61169

### SQL language changes

- Added a virtual table, `crdb_internal.node_inflight_trace_spans`, which exposes span ID, parent span ID, trace ID, start time, duration, and operation of node-local inflight spans. #59492
- Added `WITH avro_schema_prefix` option to Avro changefeeds to prevent collisions in a shared schema registry #59710
- CockroachDB now allows implicitly partitioned indexes to be referenced by foreign keys using the non-implicit columns. #59692
- The `SHOW ZONE CONFIGURATION` statement has been changed to use `FROM` instead of `FOR`. #59410
- It is now possible to use the `NOT VISIBLE` qualifier for new column definitions in `CREATE TABLE`. This causes the column to become "hidden". Hidden columns are not considered when using `*` in `SELECT` clauses. Note that CockroachDB already supported hidden columns (e.g., `rowid`, which is added automatically when a table definition has no `PRIMARY KEY` constraint). This change adds the opportunity for end-users to define their own hidden columns. It is intended for use in combination with other features related to geo-partitioning introduced in v21.1, which offer more control about how geo-partitioning keys get exposed to client ORMs and other automated tools that inspect the SQL schema. #58923
- Added the `goroutine_id` column to the `crdb_internal.node_inflight_trace_spans` virtual table that represents the goroutine ID associated with a particular span. #59717
- Updated `SHOW BACKUP ... WITH PRIVILEGES` to display ownership information of objects in the backup. #59732
- The `ALTER TYPE ... DROP VALUE ...` statement has been added to drop values from an `ENUM` type. The statement drops the provided value if it isn't used as a value in any table's row. The use of this syntax is gated behind the `sql_safe_updates` session variable, as it is susceptible to the value being used in a table expression (such as a computed column). #58688
- Added support for `COPY CSV`. #59790
- CockroachDB now supports specifying `NULL` and `DELIMITER` in `COPY`. #59790
- `ALTER TABLE ... SET LOCALITY` adds the ability to change the locality of a `GLOBAL` or `REGIONAL BY TABLE` table to `REGIONAL BY ROW`, provided an existing column of the appropriate type already exists. #59624
- Overload sequence operators now accept a regclass. #59396
- `crdb_internal.invalid_objects` now includes invalid type descriptors. #59978
- Added the `finished` column to the virtual table `crdb_internal.node_inflight_trace_spans`, which represents whether each span has finished or not. #59856
- Partitioned GIN indexes are now supported. #59858
- Hidden columns (created by using `NOT VISIBLE` or implicitly created via hash sharded indexes, a lack of a primary key definition, or by using `REGIONAL BY ROW`) will now display with `NOT VISIBLE` annotations on `SHOW CREATE TABLE`. #59828
- `REGIONAL BY ROW` tables that have an implicitly created `crdb_region` table will now mark the given column as hidden so it does not display in `SELECT *` statements. #59831
- CockroachDB now recognizes the `options` URL parameter. The `options` parameter specifies session variables to set at connection start. This is treated the same as defined in the PostgreSQL docs. #59621
- Implemented the ability to `ALTER TABLE SET LOCALITY` to `REGIONAL BY ROW` without specifying `AS` for `GLOBAL` and `REGIONAL BY TABLE` tables. #59824
- Added the `sql.show_tables.estimated_row_count.enabled` cluster setting, which defaults to `true`. If `false`, `estimated_row_count` will not display on `SHOW TABLES` which improves performance. #60282
- `ALTER DATABASE ... DROP REGION` is now implemented. #59989
- When a connection is established, CockroachDB will now return a placeholder `BackendKeyData` message in the response. This is for compatibility with some tools, but using `BackendKeyData` to cancel a query will still have no effect (which is the same as before). #60281
- To match the behavior of `PRIMARY KEY` creations setting, all relevant fields to `NOT NULL`, all `PARTITION BY` / R`EGIONAL BY ROW` columns will now have their fields set to `NOT NULL` at `CREATE TABLE` time. #60379
- `ALTER DATABASE ... DROP REGION` now allows for dropping the primary region. #60437
- Added support for restore of multi-region databases. #60257
- Implemented functionality to `ALTER TABLE SET LOCALITY` from `REGIONAL BY ROW` to `REGIONAL BY TABLE` or `GLOBAL`. #60311
- Materialized views now require ownership or admin privilege to refresh. #60448
- `CONNECT` can be granted to / revoked from users at the database level (e.g., `GRANT CONNECT ON DATABASE db TO user;`). `CONNECT` allows users to view all objects in a database in `information_schema` and `pg_catalog`. Previously, only users could only see an object in `information_schema` / `pg_catalog` if they had any privilege (e.g., `SELECT`) on the object. Added a warning when using `USE DATABASE` that notifies the user that they do not have `CONNECT` privilege on the database and will need `CONNECT` privilege to use databases in a future release. #59676
- Add the built-in `crdb_internal.show_create_all_tables`, which takes in a database name (`STRING`) and returns a flat log of all the `CREATE TABLE` statements in the database followed by `ALTER` statements to add constraints. The output can be used to recreate a database. This built-in was added to replace old dump logic. #60154
- Implemented `default_to_database_primary_region`, which will return the region passed in if the region is defined on the database, falling back to the primary key if not. #59836
- The default expression for `REGIONAL BY ROW` tables is now `default_to_database_primary_region(gateway_region())`, allowing users to add to `REGIONAL BY ROW` tables from any region. This would previously error if the gateway's region was not defined on the database. #59836
- `EXPLAIN` now shows the estimated percentage of the table that is scanned. #60467
- Multi-region tables will have their zone configs regenerated during database restore. #60519
- CockroachDB now supports the `DETACHED` option when running `IMPORT`. The detached import will return `jobID` and the user can later use `SHOW JOB` to check the result of the detached import. This allows users to run `IMPORT` under explicit transactions with `DETACHED` option specified. #60442
- Casting JSON numeric scalars to numeric types now works as expected. #41367
- Most batches of data flowing through the vectorized execution engine will now be limited in size by `sql.distsql.temp_storage.workmem` (64MiB by default), which should improve the stability of CockroachDB clusters. #59851
- Implemented the ability to `ALTER TABLE LOCALITY REGIONAL BY ROW` from other `REGIONAL BY ROW` variants. #60497
- Setting of zone configs for non-physical tables is now forbidden. #60592
- Added telemetry to track usage of `pg_catalog` and `information_schema` tables #60511
- `SHOW JOBS` now displays a meaningful value in the `running_status` column for GC jobs which are actually performing garbage collection, as opposed to waiting on a timer. #59220
- Added the `SHOW CREATE ALL TABLES` statement, which allows the user to get the statements (`CREATE`/`ALTER`) to recreate a the current database. The command returns a flat log of the create statements followed by the alter statements for adding the constraints. The commands are ordered topologically such that dependencies appear before it's references. `ALTER` statements follow create statements to guarantee that the objects are all added before adding constraints. This command is added to replace old dump logic. #60539
- Added the `schema_name` and `table_i`d columns to the `crdb_internal.ranges` and `crdb_internal.ranges_no_leases` virtual tables. #59865
- Using the `CACHE` sequence option no longer results in an "unimplemented" error. The `CACHE` option is now fully implemented and will allow nodes to cache sequence numbers. A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid. #56954
- The `serial_normalization` session variable can now be set to the value `sql_sequence_cached`. If this value is set, the `sql.defaults.serial_sequences_cache_size` cluster setting can be used to control the number of values to cache in a user's session with a default of 256. When the cache is empty, the underlying sequence will only be incremented once to populate it. Using `sql_sequence_cached` will result in better performance than `sql_sequence` because the former will perform fewer distributed calls to increment sequences. However, cached sequences may result in large gaps between serial sequence numbers if a session terminates before using all the values in its cache. #56954
- CockroachDB can now encode and decode sequence regclasses. #59864
- CockroachDB now allows renaming of sequences referenced by ID and conversion of sequences referenced by name to ID. #59864
- `EXPLAIN ANALYZE` now shows more top-level query statistics. #60641
- Added `payloads_for_span` built-in that takes in a span ID and returns its payloads in `JSONB` format. If the span is not found, or if the span does not have any payloads, the built-in returns an empty JSON object. #60616
- Added a new `IMPORT PGDUMP` option, `ignore_unsupported`, to skip over all the unsupported `PGDUMP` statements. The collection of these statements will be appropriately documented. #57827
- Users will now need to specify `ignore_unsupported` to ignore all unsupported import statements during an `IMPORT PGDUMP`. #57827
- Added a new `IMPORT PGDUMP` option, `ignored_stmt_log`, which allows users to specify where they would like to log statements that have been skipped during an import, by virtue of being unsupported. #57827
- CockroachDB now supports `VIRTUAL` computed columns (as opposed to `STORED`). These are computed columns that are not stored in the primary index and are recomputed as necessary. #60748
- `EXPLAIN ANALYZE` now includes the nodes which were involved in the execution of each operator in the tree. #60550
- Added missing tables and columns at `pg_catalog`. #60758
- Added a new session setting `locality_optimized_partitioned_index_scan` and corresponding cluster default setting `sql.defaults.locality_optimized_partitioned_index_scan.enabled`. Both are currently disabled by default, and are currently unused. In the future, these settings will be used to enable or disable locality optimized search. If enabled, the optimizer will try to search locally for rows in `REGIONAL BY ROW` tables before searching remote nodes. #60771
- Added the new `parse_timestamp` function, which can be used to parse absolute timestamp strings in computed column expressions or partial index predicates. #60772
- CockroachDB now supports storing spatial type objects with `Z` and `M` dimensions (e.g., `POINTZ`, `LINESTRINGM`). #60832
- `ALTER DATABASE ... ADD REGION` now re-partitions `REGIONAL BY ROW` tables and updates the zone configs on the newly created partitions as well. #60596
- Updated the `crdb_internal.payloads_for_span` built-in to return a table instead of a `JSONB` array. Each row of the table represents one payload for the given span. It has columns for `payload_type` and `payload_jsonb`. #60784
- `ALTER DATABASE ... DROP REGION ...` now re-partitions `REGIONAL BY ROW` tables to remove the partition for the removed region and removes the zone configuration for the partition as well. #60938
- Added the experimental `experimental_enable_stream_replication` session setting and `sql.defaults.experimental_stream_replication.enabled` cluster setting to enable cluster streaming. #60826
- Introduced a new `is_multi_region` column to c`rdb_internal.create_statements`, which informs whether the database of the object is a multi-region database. #60761
- Introduced the new `crdb_internal.filter_multiregion_fields_from_zone_config_sql` built-in, which removes multi-region fields from a `CONFIGURE ZONE` statement. #60761
- Added new virtual tables `crdb_internal.cluster_contention_events` and `crdb_internal.node_contention_events`, which expose cluster-wide and gateway-only, respectively, contention information. In order to access them, the user needs to be either an admin or have `VIEWACTIVITY` grant. #60713
- The structured payloads used for SQL audit and execution logs now include a transaction counter since the beginning of the session. Statements issued inside the same SQL transaction will thus be logged with the same counter value, thus enabling per-transactions grouping during log analysis. Separate sessions use independent counters. #41929
- The geometry built-ins `ST_MakePoint` and `ST_MakePointM` have been implemented and provide a mechanism for easily creating new points. #61105
- Added the `payloads_for_trace()` built-in so that all payloads attached to all spans for a given trace ID will be displayed, utilizing the `crdb_internal.payloads_for_span()` built-in under the hood. All payloads for long-running spans are also added to debug.zip in the `crdb_internal.node_inflight_trace_spans` table dump. #60922
- `IMPORT PGDUMP` can now import dump files with non-public schemas. #57183
- Added the `sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled` cluster setting, which controls creation of uniqueness checks for `UUID` columns set to `gen_random_uuid()`. When enabled, uniqueness checks will be added for the `UUID` column if it has a unique constraint that cannot be enforced by an index. When disabled, no uniqueness checks are planned for these columns, and uniqueness is assumed due to the near-zero collision probability of `gen_random_uuid()`. #61132
- The `pg_catalog.pg_class` table now has a `relpartbound` column. This is only for compatibility, and the column value is always `NULL`. #61162
- Cluster backup now restores the zone configurations first. This means that there should be less range-relocation during and after cluster restores. #60461
- Fixed `information_schema.columns.udt_schema` for `ENUM` or user-defined types. #61139
- The geography built-in `ST_Affine` now supports 3D transformations. #61286
- The `ST_Z`, `ST_M`, and `ST_Zmflag` built-ins are now available for use. #61032
- Geometry built-ins for forcing geometries into non-2D layouts are now available. #61297
- `ST_Buffer` now requires at least 1 quadrant segment. #61315
- `ST_RotateX` function is now available. #61326
- `EXPLAIN` now shows estimated row counts for all operators even without `VERBOSE` (except when we do not have statistics for the tables). #61344
- Updated the error message returned in case of a unique constraint violation to hide the names and values for implicit partitioning columns and hash sharded index columns. #61362
- Fixed `information_schema.columns.is_identity` to display the correct value. #61348
- `ST_RotateY` and `ST_RotateZ` are now available. #61387
- CockroachDB now prevents `densifyFracs < 1e-6` for `ST_FrechetDistance` and `ST_HausdorffDistance` to protect panics and out-of-memory errors. #61427
- CockroachDB now disallows `GRANT/REVOKE` operations on system tables. #61410
- `ST_Snap` is now available. #61523
- Updates to certain fields in the zone configurations are blocked for multi-region enabled databases. This block can be overridden through the use of the `FORCE` keyword on the blocked statement. #61499
- The `ST_AddMeasure` function is now available for use. #61514
- Added a new built-in that sets the verbosity of all spans in a given trace. Syntax: `crdb_internal.set_trace_verbose($traceID,$verbosityAsBool)`. #61353
- `Pg_index.indkey` now includes attributes. #61494
- `SHOW CREATE ALL TABLES` is updated to be more memory efficient, however this should still not be run on a database with an excessive number of tables. Users should not run this on a database with more than 10000 tables (arbitrary but tested number). #61127
- Set the default cluster setting for `sql.defaults.locality_optimized_partitioned_index_scan.enabled` to `true`, which is the default for the session setting `locality_optimized_partitioned_index_scan`. When this session setting is enabled, the optimizer will plan queries to use "locality optimized search" when possible, which instructs the execution engine to search for keys in local nodes before searching remote nodes. If a key is found in a local node, the execution engine may be able to avoid visiting the remote nodes altogether. #61601
- `ALTER TYPE ... DROP VALUE` is gated behind a feature flag. The session setting is called `enable_drop_enum_value` and the corresponding cluster setting is called `sql.defaults.drop_enum_value.enabled`. #61723
- `TYPE SCHEMA CHANGE` jobs which include a `DROP VALUE` in them are now cancellable. All other jobs remain non-cancellable. #61733
- A statement contention timeseries is now displayed in the SQL metrics overview dashboard. #61844
- The `ST_SnapToGrid` function can now be used to snap `Z` and `M` dimensions. #61826
- Added `json_extract_path_text` and `jsonb_extract_path_text` built-ins. #61813
- Removed `pg_catalog` tables that were mistakenly added, notably all tables that end in `_index` that are not `pg_catalog.pg_classes` and all statistics collector tables that are not `pg_stat_activity`. #61876
- The `replicas` column of `crdb_internal.ranges{_no_leases}` now includes both voting and non-voting replicas and `crdb_internal.ranges{_no_leases}` include two new columns: `voting_replicas` and `non_voting_replicas`, which work as labeled. #61962

### Operational changes

- Added the `storage.transaction.separated_intents.enabled` cluster setting, which enables separated intents by default. #59829

### Command-line changes

- It is now possible to redirect logging to Fluentd-compatible network collectors. See the reference sink documentation for details.This is an alpha-quality feature. Note that Fluent-enabled configuration only provide minimal event buffering, and log entries are dropped if the logging server becomes unavailable or network errors are encountered. This is a known limitation and will be likely improved in a later version. #57170
- The SQL shell (`cockroach sql` / `cockroach demo`) now supports a flag `--embedded`, for use with playground-style web applications. #59750
- `cockroach userfile get` can now be used to fetch files from a cluster. #60490
- Added the `ignore-unsupported-statements`, `log-ignored-statements` and `row-limit` flags to the `cockroach import` command. #60923
- The doctor tool can now report multiple descriptor validation failures per descriptor. #60775
- The new `cockroach connect` command now recognizes `--single-node` to prepare a TLS configuration suitable for a subsequent `start-single-node` command. Additionally, the command checks that either `--single-node` is specified, or both `--init-token` and `--num-expected-peers`. #60854
- Optimized handling of multi-line SQL strings to avoid unwanted extra server roundtrips. #61207
- Added back support for `cockroach dump --dump-mode=schema`. This command calls `SHOW CREATE ALL TABLES` and returns the output. This gives users a migration path to use in v21.1 while switching over to `SHOW CREATE ALL TABLES` for v21.2. When executing this, the user is warned that the command is being removed in v21.2 and they should use `SHOW CREATE ALL TABLES` instead. #61871

### API endpoint changes

- Added the following new HTTP API endpoints:

  - `/api/v2/nodes/`: Lists all nodes in the cluster
  - `/api/v2/nodes/<node_id>/ranges`: Lists all ranges on the specified node
  - `/api/v2/ranges/hot/`: Lists hot ranges in the cluster
  - `/api/v2/ranges/<range_id>/`: Describes range in more detail
  - `/api/v2/health/`: Returns an HTTP 200 response if node is healthy. #60952

### DB Console changes

- Manually enqueue range in a replica GC queue now properly respects the `SkipShouldQueue` option. This can be useful to force a GC of a specific Range. #60619
- Added a filter for full scans to the Statements page. #60670
- The Range Report page on the DB Console will now also show each of the replica's types. #61113
- The Transaction Type column was removed from Statements page. The sort order in Statement / Transaction Table views is now by execution count. The sort order in Transaction Details view is by latency. Column titles in statements and transactions tables now say "{Statement,Transaction} Time" instead of "Latency". The Contention column added to the Statements / Transactions pages. Max Scratch Disk Usage added to Statements and Transaction Details pages. #61177
- Added a full-table scan checkbox. #61676

### Bug fixes

- Fixed a bug in the DB Console affecting status badges in Nodes list on Cluster Overview page. #59636
- Fixes a bug where backups would fail with an error when trying to read a backup that was written. #59730
- Fixes a bug where some import failures would cause tables to stay `OFFLINE`, when they should have been brought back to `PUBLIC`. #59723
- Fixed a bug that caused errors when joining two tables when one of the tables had a computed column. This bug was present since version v21.1.0-alpha.2 and not present in any production releases. #59741
- Statistics are now correctly generated for columns in multi-column GIN indexes and columns referenced in partial GIN index predicates. #59687
- Fixed a timestamp overflow bug that would overflow timestamps after the year 2038 by updating the Avro SDK used by changefeeds. #59745
- Fixed a very rare chance of inconsistent follower reads. #59502
- Previously if `RELEASE SAVEPOINT cockroach_restart` was followed by `ROLLBACK`, the `sql.txn.rollback.count` metric would be incremented. This was incorrect, since the transaction had already committed. Now that metric is not incremented in this case. #59781
- CockroachDB now explicitly verify descriptors are sequences. #60159
- The result of the `cockroach debug doctor` invocation during `cockroach debug zip` is now properly included inside the generated zip file. #60529
- Fixed a bug in the optimizer statistics code that could cause an unconstrained partial index scan to be preferred over a constrained scan of the same index. #60516
- Fixed a deficiency in the replication layer that could result in ranges becoming unavailable for prolonged periods of time (hours) when a write burst occurred under system overload. While unavailable, the range status page for the affected range would show a last index much larger than the committed index and no movement in these indexes on a quorum of the replicas. Note that this should be distinguished from the case in which enough replicas are offline to constitute a loss of quorum, where the replication layer can not make progress due to the loss of quorum itself. #60581
- Previously, retryable errors in the cleanup phase of the type schema changer wouldn't be retried automatically in the background. This is now fixed. #60495
- v20.2 introduced an ability to rebalance replicas between multiple stores on the same node. This change fixed a problem with that feature, where occasionally an intra-node rebalance would fail and a range would get stuck permanently under replicated. #60546
- Fixed a bug that would cause the value of the optional `into_db` parameter to `RESTORE` to be included in anonymized crash reports. #60624
- Fixed a bug that caused errors for some queries on tables with `GEOMETRY` or `GEOGRAPHY` GIN indexes with filters containing shapes with zero area. #60598
- CockroachDB previously didn't account for some RAM used when disk-spilling operations (like sorts and hash joins) were using the temporary storage in the vectorized execution engine. This could result in OOM crashes, especially when the rows are large in size. This has been fixed. #60593
- Fixed a bug where `ST_Node` would panic if passed in `MULTILINESTRING EMPTY`. #60691
- Fixed a bug that could result in crashes during tracing when using the `trace.debug.enable` cluster setting. #60725
- Fixed execution errors for some queries that use set operations (`UNION` / `EXCEPT` / `INTERSECT`) where a column has types of different widths on the two sides (e.g., `INT4` vs. `INT8`). #60560
- CockroachDB now avoids creating batches that exceed the raft command limit (64MB) when reverting ranges that contain very large keys. #59716
- Fixed a bug whereby high-latency global clusters could sometimes fall behind checkpointing resolved timestamps. #60807
- Added check privileges before changing table/database owner. #60800
- Fixed an internal error caused in some cases involving JSON objects and arrays in a `VALUES` clause. #60767
- Previously `DROP TYPE IF EXISTS` with one existent, and another non-existent type would cause an unhandled error. This is now fixed. #60822
- Fixed bug that could report that a protected timestamp limit was exceeded when the limit was disabled, if an error were to occur while protecting a record. #60913
- CockroachDB previously could encounter an internal error when performing `UNION` operation when the first input resulted only in NULL values and consequent inputs produce tuples, and this is now fixed. Only v21.1 alpha versions are affected. #60827
- Fixed a bug in `crdb_internal.unsafe_upsert_namespace_entry` related to tables and types in user-defined schemas. #60510
- Integers inside of tuples were not being encoded properly when using the binary format for retrieving data. This is now fixed, and the proper integer width is reported. #61013
- Blank-padded chars (e.g., `CHAR(3)`) were not being encoded correctly when returning results to the client. Now they correctly include blank-padding when appropriate. #61013
- Collated strings were not encoded with the proper type OID when sending results to the client if the OID was for the `char` type. This is now fixed. #61013
- Fixed a very rare, possible impossible in practice, bug where a range merge that applied through a Raft snapshot on the left-hand side range's leaseholder could allow that leaseholder to serve writes that invalidated reads from before the merge on the right-hand side. Release justification: bug fix #60521
- The `SHOW CREATE` output of a partitioned partial index now lists the `PARTITION BY` and `WHERE` clauses in the order accepted by the parser. #60590
- The `SHOW CREATE` output of a partial interleaved index now lists the `INTERLEAVED` and `WHERE` clauses in the order accepted by the parser. #60590
- Fix a bug where cluster restore would sometimes (very rarely) fail after retrying. #60458
- Previously, comparing a negative integer to an OID would fail to compare correctly because the integer was not converted to an unsigned representation first. this is now fixed for both comparisons and casts.#61148
- Previously, CockroachDB could not decode arrays of user-defined types when sent to the server in the binary format. Now it can. #61165
- `crdb_internal.jobs` virtual table is now populated in a paginated fashion, thus, alleviating memory related concerns when previously we could encounter OOM crash. #60693
- The `SHOW TABLES FROM database` command would always show a NULL `estimated_row_count` if inspecting a database that was not the current database. This is now fixed. #61191
- Fixed a bug where schema changes on databases and schemas could return a `relation [<id>] does not exist` if they failed or were canceled and entered the reverting state. These jobs are not actually possible to revert. With this change, the correct error causing the job to fail will be returned, and the job will enter the failed state with an error indicating that the job could not be reverted. #61159
- CockroachDB now drops the default value when its dependent sequence is dropped. #60744
- Dropping and recreating a view/table/sequence in a transaction will now correctly error out if a conflicting object exists or if the drop is incomplete. #61135
- The non-server `cockroach` commands now recognize the new `--log` flag properly. This had been broken in one of the earlier v21.1 alpha releases. #61232
- Prepared statements would sometimes get the wrong type OID for placeholder arguments used in function parameters. This is now fixed. #60949
- Fixed a case where empty zone configurations get created for certain indexes during `ALTER PRIMARY KEY`. #61235
- Schema change jobs associated with databases and schemas can no longer be canceled. Such jobs cannot actually be reverted successfully, so cancelation had no benefit and could have caused namespace corruption. #61210
- Fixed `cockroach demo` commands nodeName error argument index handling. #61246
- CockroachDB no longer displays incorrect node statuses on Metrics page, when page just loaded. #61000
- Fixed an NPE observed with a SpanFromContext call in the stack trace. #61124
- Limit scans are no longer counted as full scans. #61178
- Selecting from `crdb_internal.create_statements` will now correctly populate the `database_name` when the virtual index is not used. #61201
- Fixed an internal error when `EXPLAIN`ing an `INSERT` with an input that was determined by the optimizer to produce no rows. #61278
- Fixed a rare deadlock where a series of lease transfers concurrent with a Range merge could block each other from ever completing. #60905
- `ALTER TYPE ... ADD VALUE` changes are picked up by the array type alias correctly. #61288
- Previously, the traces of cascades and checks could be incomplete. This is now fixed. #61321
- Creating interleaved partitioned indexes is now disallowed. Previously, the database would crash when trying to create one. #61106
- Dropping and re-creating a database in a transaction will now correctly error out if an object in a dropped state is detected. #61361 #61358
- CockroachDB now uses the correct `FuncExpr` when encoding sequences. #61428
- Alter primary key was not idempotent, so logical equivalent changes to primary keys would unnecessarily create new indexes. This is now fixed. #61345
- Fixed a bug where `GRANT/REVOKE` on the `system.lease` table would result in a deadlock. #61410
- Fixed operation hangs when a node loses access to cluster RPC (e.g., after it has been decommissioned), and immediately return an error instead. #61356
- Fixed a bug from v21.1-alpha where a node decommissioning process could sometimes hang or fail when the decommission request was submitted via the node being decommissioned. #61356
- Fixed bug where zone configurations on indexes were not copied before the backfill of an `ALTER PRIMARY KEY`. They used to be copied afterwards instead. #61300
- Fixed a bug where random numbers generated as default expressions during `IMPORT` would collide a few hundred rows apart from each-other. #61214
- Fixed a bug that caused `UPSERT` and `INSERT..ON CONFLICT..DO UPDATE`statements to fail on tables with both partial indexes and foreign key references. This bug has been present since v20.2.0. #61416
- An `UPDATE..FROM` statement where the `FROM` clause contained column names that match table column names erred if the table had a partial index predicate referencing those columns. This bug, present since partial indexes were released in v20.2.0, has been fixed. #61522
- The names of custom types are no longer sent to Cockroach Labs in telemetry and crash reports. #60806
- Fixed a case where an invalid tuple comparison using ANY was causing an internal error; we now return "unsupported comparison operator". #61647
- Added better privilege checks when creating a changefeed. #61709
- Fixed privileges for system.`protected_ts_meta`. #61842
- The `indexdef` column in the `pg_indexes` table would always report that the index belonged to the public schema. Now it correctly reports user-defined schemas if necessary. #61754
- Fixed "command is too large" errors in some cases when using `EXPLAIN ANALYZE (DEBUG)` or statement diagnostics on complex queries. #61909
- Using `EXPLAIN (OPT, ENV)` on a query that references a table in a user-defined schema would fail previously. This is now fixed. #61888
- Fixed a bug that caused "column does not exist errors" in specific cases of `UPDATE..FROM` statements. The error only occurred when updating a `DECIMAL` column to a column in the `FROM` clause, and the column had a `CHECK` constraint or was referenced by a partial index predicate. #61949
- Previously the `idle_in_session_timeout` and `idle_in_transaction_session_timeout` settings would show the wrong value when using `SHOW`. They would instead show the value of the `statement_timeout` setting. This is now fixed. The functionality was already working correctly; this just fixes a display bug. #61959

### Performance improvements

- Improved the performance of the `pg_table_is_visible` built-in function. #59880
- Added support for left and anti lookup joins in cases where the equi-join condition is not on the first column in the index, but the first column(s) are constrained to a small number of constant values using a CHECK constraint or an ENUM type. Planning a lookup join for these cases can significantly improve performance if the left input to the join is much smaller than the right-hand side. #60302
- The optimizer now knows that the unique columns in an implicitly partitioned unique index form a key. This can be used to enable certain optimizations and may result in better plans. #60591
- Improved the optimizer's ability to identify constant columns in scans. This can enable different types of optimizations and result in improved plans. #60927
- Follower reads no longer wait before redirecting to the leaseholder if they could not be served by a local follower due to an insufficient closed timestamp. #60839
- Updated the query used to build the virtual table `crdb_internal.table_row_statistics` so that it is always run at `AS OF SYSTEM TIME '-10s'`. This should reduce contention on the table and improve performance for transactions that rely on `crdb_internal.table_row_statistics`, such as `SHOW TABLES`. #60953
- Improved the optimizer's cost estimation of index scans that must visit multiple partitions. When an index has multiple partitions, the optimizer is now more likely to choose a constrained scan rather than a full index scan. This can lead to better plans and improved performance. It also improves the ability of the database to serve queries if one of the partitions is unavailable. #61063
- Optimized external sort to merge partition data faster in colexec. #61056
- If the session setting `locality_optimized_partitioned_index_scan` is enabled, the optimizer will try to plan scans known to produce at most one row using "locality optimized search". This optimization applies for `REGIONAL BY ROW` tables, and if enabled, it means that the execution engine will first search locally for the row before searching remote nodes. If the row is found in a local node, remote nodes will not be searched. #60831
- The optimizer now infers additional functional dependencies based on computed columns in tables. This may enable additional optimizations and lead to better query plans. #61097
- Removed uniqueness checks on the primary key for `REGIONAL BY ROW` tables with a computed region column that is a function of the primary key columns. Uniqueness checks are not necessary in this case since uniqueness can be suitably guaranteed by the primary index. Removing these checks improves performance of `INSERT`, `UPDATE`, and `UPSERT` statements. #61097
- The optimizer no longer plans uniqueness checks for columns in implicitly partitioned unique indexes when those columns are used as the arbiters to detect conflicts in `INSERT ... ON CONFLICT` statements. Unique checks are not needed in this case to enforce uniqueness. Removing these checks results in improved performance for `INSERT ... ON CONFLICT` statements. #61184
- The columns fetched for uniqueness checks of implicitly partitioned unique indexes are now pruned to only include columns necessary for determining uniqueness. #61376
- Introspection queries that use casts into the `REGPROC` pseudo-type are made much more efficient: they're now implemented as a constant-time lookup instead of an internal query. #61211
- Fixed cases where the optimizer was doing unnecessary full table scans when the table was very small (according to the last collected statistics). #61805
- The optimizer now estimates the cost of evaluating query filters more accurately for queries with a `LIMIT`. Previously, it was assumed that the filter would be evaluated on each input row. Now, the optimizer assumes that the filter will only be evaluated on the number of rows required to produce the LIMIT's number of rows after filtering. This may lead to more efficient query plans in some cases. #61947

### Contributors

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

- Abdullah Islam (first-time contributor)
- Alan Acosta (first-time contributor)
- Kumar Akshay
- Max Neverov
- Miguel Novelo (first-time contributor)
- Ratnesh Mishra (first-time contributor)
- Tharun (first-time contributor)
- Ulf Adams (first-time contributor)
- alex-berger@gmx.ch (first-time contributor)
- leoric (first-time contributor)
- shikamaru (first-time contributor)

## v21.1.0-alpha.3

Release Date: February 8, 2021

### General changes

- Added ability to further debug connections shut down automatically by the server. #59460

### SQL language changes

- Fixed up `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE` to partition correctly under a `PARTITION ALL BY` table. #59364
- CockroachDB now applies zone configs to new unique constraints in `REGIONAL BY ROW` tables. #59364
- A new, unused field called `global_reads` was added to zone configurations. The field does not yet have any effect. #59304
- A new private cluster setting `sql.distsql.temp_storage.hash_agg.enabled` was added that allows users to disable the disk spilling capability of the hash aggregation in the vectorized execution engine. It is `true` by default which incurs some performance hit. Setting it to `false` will improve the performance, but the queries might hit an out-of-memory limit error. #59414
- The optimizer now enforces a unique constraint on the explicit index columns for implicitly partitioned unique indexes and unique indexes in `REGIONAL BY ROW` tables. An attempt to insert or update a row such that the unique constraint is violated will result in an error. #59501
- `REGIONAL BY ROW` tables now preserve zone configurations when using `ALTER PRIMARY KEY`. #59365
- Implemented `ALTER TABLE ... LOCALITY REGIONAL BY TABLE` from `LOCALITY GLOBAL`. #59407
- Zone configs now support new attributes `num_voters` and `voter_constraints`. `num_voters` will specify the number of voting replicas. When `num_voters` is explicitly specified, `num_replicas` will be the sum of voting and non-voting replicas. `voter_constraints` will specify the constraints that govern the placement of just the voting replicas, whereas the existing `constraints` attributes will govern the placement of all replicas (voting as well as non-voting). #57184
- Added SQL syntax for `RESTORE tenant x FROM REPLICATION STREAM FROM 'replication_stream'`. This allows the user to start an ingestion job to ingest KVs from the replication stream into the destination tenant's keyspace. #59112
- The `SHOW CREATE` statement now lists `ALTER PARTITION` statements sorted by the partition name and index_name. #59580
- Error messages for cross-database links now include a hint directing to the user to the deprecation docs. An example message looks like: `ERROR: the view cannot refer to other databases; (see the 'sql.cross_db_views.enabled' cluster setting) SQLSTATE: 0A000 HINT: Note that cross database references will be removed in future releases. See: https://www.cockroachlabs.com/docs/releases/v21.1.html#v21-1-0-deprecations` #59551
- The `escape_string_warning` session variable from PostgreSQL was added with compatibility-only support. It defaults to `on` and cannot be changed. #59479
- Multi-column GIN indexes can now be created. The last indexed column must be inverted types such as `JSON`, `ARRAY`, `GEOMETRY`, and `GEOGRAPHY`. All preceding columns must have types that are indexable. These indexes may be used for queries that constrain all index columns. #59565
- Added `WITH full_table_name` option to create a changefeed on `movr.public.drivers` instead of `drivers`. #59258
- `UPSERT`s on tables with an implicitly partitioned primary index now use only the explicit primary key columns as the conflict columns, excluding all implicit partitioning columns. This also applies to `REGIONAL BY ROW` tables, ensuring that the `crdb_region` column is not included in the `UPSERT` key. #59654

### Command-line changes

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

- The `channels` parameter for the log sink configurations now supports a more flexible input configuration format:
  Syntax to include specific channels:
  `channels: dev,sessions` (yaml string)
  `channels: 'dev,sessions'` (yaml string, equivalent to previous)
  `channels: [dev,sessions]` (yaml array, can use multi-line syntax with hyphens too)
  `channels: ['dev','sessions']` (same as previous)
  `channels: '[dev,sessions]'` (bracket-enclosed list inside yaml string)

  Syntax to include all channels:
  `channels: all` (yaml string)
  `channels: 'all'` (same as previous)
  `channels: [all]` (yaml array)
  `channels: ['all']` (same as previous)
  `channels: '[all]'` (bracket-enclosed "all" inside yaml string)

  Syntax to include all channels except some:
  `channels: all except dev,sessions` (yaml string)
  `channels: 'all except dev,sessions'` (same as previous, quoted string)
  `channels: 'all except [dev,sessions]'` (same as previous, list is bracket enclosed)

  For example: `sinks: stderr: channels: - DEV - SQL_SESSIONS` uses the "native" YAML syntax for lists. #59352

- The notification that `SIGHUP` was received, and that log files are flushed to disk as a result, is now sent to the OPS logging channel. #59345

- The notification that `SIGHUP` was received, and that TLS certificates are reloaded from disk as a result, is now sent to the OPS logging channel as a structured event. Refer to the reference docs for details about the event payload. #59345

### API endpoint changes

- Added a new API tree, in `/api/v2/*`, currently undocumented, that avoids the use of cookie-based authentication in favor of sessions in headers, and support for pagination. #58436

### DB Console changes

- Updated the table details page to show table-specific zone configuration values when set, show constraints and lease preferences, and display a valid statement to re-configure zone configuration for that table. #59196
- Users can now see the time series of full table or index scans in the Advanced Debug page. #59261
- The `sql.leases.active` gauge is now available to track the outstanding descriptor leases in the cluster. #57561
- Long queries are truncated in the DB Console. #59603

### Bug fixes

- Added event logs for `SET SCHEMA` statements. #58737
- Fixed an issue where a left inverted join could have incorrect results. In particular, some output rows could have non-`NULL` values for right-side columns when the right-side columns should have been `NULL`. This issue has only existed in alpha releases of 21.1 so far, and it is now fixed. #59279
- Fixed a panic where type hints mismatching placeholder names cause a crash. #59450
- Unexpected internal errors containing stack traces that reference a `countingWriter` null pointer have now been fixed. #59477
- Fixed a bug introduced in v20.1 in the DB Console where incorrect zone configuration values were shown on the table details page and constraints and lease preferences were not displayed. #59196
- Changefeeds no longer error with "1e2 is not roundtrippable at scale 2" when 100 is stored in a column with width 2. #59075
- Fixed a bug which prevented renaming a column that was referenced earlier in a transaction as part of a computed expression, index predicate, check expression, or not null constraint. #59384
- Added event logs for privilege changes in `crdb_internal.unsafe_xxx`. #59282
- Fixed a bug in which incorrect results could be returned for left and anti joins. This could happen when one of the columns on one side of the join was constrained to multiple constant values, either due to a check constraint or an `IN` clause. The bug resulted in non-matching input rows getting returned multiple times in the output, which is incorrect. This bug only affected previous alpha releases of 21.1, and has now been fixed. #59646
- Fixed `EXPLAIN ANALYZE (DEBUG)` interaction with the SQL shell. #59557
- Fixed a bug preventing foreign key constraints referencing hidden columns (e.g., `rowid`) from being added. #59659
- Fixed a bug where `DROP SCHEMA ... CASCADE` could result in types which are referenced being dropped. #59281
- Fixed a bug whereby dropping a schema with a table that used a user-defined type which was not being dropped (because it is in a different schema) would result in a descriptor corruption due to a dangling back-reference to a dropped table on the type descriptor. #59281

### Performance improvements

- The query optimizer now plans scans over GIN indexes on `JSON` columns for query filters that constrain the `JSON` column with equality and fetch value operators (`->`) inside conjunctions and disjunctions, like `j->'a' = '1' AND j->'b' = '2'`. #59266
- Fixed a bug included in 20.2.1 for the `JSON` fetch value operator, `->` which resulted in chained `->` operators in query filters not being index accelerated (e.g., `j->'a'->'b' = '1'`). Chained `->` operators are now index accelerated. #59494
- Improved the allocation performance of workloads that use the `EXTRACT` built-in. #59598

### Contributors

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

- John Seekins (first-time contributor)
- Ulf Adams (first-time contributor)

## v21.1.0-alpha.2

Release Date: February 1, 2021

### Backward-incompatible changes

- The payload fields for certain event types in `system.eventlog` have been changed and/or renamed. Note that the payloads in `system.eventlog` were an undocumented, reserved feature so no guarantee was made about cross-version compatibility to this point. The list of changes includes (but is not limited to):
  - `TargetID` has been renamed to `NodeID` for `node_join`.
  - `TargetID` has been renamed to `TargetNodeID` for `node_decommissioning` / `node_decommissioned` / `node_recommissioned`.
  - `NewDatabaseName` has been renamed to `NewDatabaseParent` for `convert_to_schema`.
  - `grant_privilege` and `revoke_privilege` have been removed; they are replaced by `change_database_privilege`, `change_schema_privilege`, `change_type_privilege`, and `change_table_privilege`. Each event only reports a change for one user/role, so the `Grantees` field was renamed to `Grantee`.
  - Each `drop_role` event now pertains to a single user/role. #57737
- The connection and authentication logging enabled by the cluster settings `server.auth_log.sql_connections.enabled` and `server.auth_log.sql_sessions.enabled` was previously using a text format which was hard to parse and integrate with external monitoring tools. This has been changed to use the standard notable event mechanism, with standardized payloads. The output format is now structured; see the generated reference documentation for details about the supported event types and payloads. #57839
- The logging format for SQL audit, execution, and query logs has changed from a crude space-delimited format to JSON. To opt out of this new behavior and restore the pre-v21.1 logging format, you can set the cluster setting `sql.log.unstructured_entries.enabled` to `true`. #59110

### Security updates

- The entropy of the auto-generated user account password for `cockroach demo` shell has been lowered, to ensure that the passwords are short and easy to copy over. This makes the password easy to brute-force and thus removes any remaining protection there may have been to the confidentiality of `demo` sessions. (The reason why a password remains, as opposed to no password whatsoever, is to prevent accidental misuse of the HTTP service by other applications running on the same machine.) Since the required shortness erases any pretense of security, the algorithm has been simplified to remove usage of a cryptographic PRNG altogether. #58305
- When using a SQL proxy, by default CockroachDB only knows about the network address of the proxy. That peer address is then used for logging, authentication rules, etc. This is undesirable, as security logging and authentication rules need to operate on the actual (final) client address instead. CockroachDB can now be configured to solve this problem (conf mechanism detailed below). When so configured, a SQL proxy can inform the CockroachDB server of the real address of the client via a server status parameter called `crdb:remote_addr`. The value must be the IP address of the client, followed by a colon, followed by the port number, using the standard Go syntax (e.g., `11.22.33.44:5566` for IPv4, `[11:22::33]:4455` for IPv6). When provided, this value overrides the SQL proxy's address for logging and authentication purposes. In any case, the original peer address is also logged alongside the client address (overridden or not), via the new logging tag `peer`. Security considerations:
  - Enabling this feature allows the peer to spoof its address with regard to authentication and thus bypass authentication rules that would otherwise apply to its address, which can introduce a serious security vulnerability if the peer is not trusted. This is why this feature is not enabled by default, and must only be enabled when using a trusted SQL proxy.
  - This feature should only be used with SQL proxies which actively scrub a `crdb:remote_addr` parameter received by a remote client, and replaces it by its own. If the proxy mistakenly forwards the parameter as provided by the client, it opens the door to the aforementioned security vulnerability.
  - Care must be taken in host-based authentication (HBA) rules:
  - TLS client cert validation, if requested by a rule, is still performed using the certificate presented by the proxy, not that presented by the client. This means that this new feature is not sufficient to forward TLS client cert authentication through a proxy. (If TLS client cert authentication is required, it must be performed by the proxy directly.)
  - The `protocol` field (first column) continues to apply to the connection type between CockroachDB and the proxy, not between the proxy and the client. Only the 4th column (the CIDR pattern) is matched against the proxy-provided remote address override. Therefore, it is not possible to apply different rules to different client address when proxying TCP connections via a unix socket, because HBA rules for unix connections do not use the address column. Also when proxying client SSL connections via a non-SSL proxy connection, or proxying client non-SSL connections via a SSL proxy connection, care must be taken to configure address-based rule matching using the proper connection type. A reliable way to bypass this complexity is to only use the `host` connection type which applies equally to SSL and non-SSL connections. As of this implementation, the feature is enabled using the non-documented environment variable `COCKROACH_TRUST_CLIENT_PROVIDED_SQL_REMOTE_ADDR`. The use of an environment variable is a stop-gap so that this feature can be used in CockroachCloud SQL pods, which do not have access to cluster settings. The environment variable will be eventually removed and replaced by another mechanism. #58381
- Added ability to set region-specific callback URLs in the OIDC config. The `server.oidc_authentication.redirect_url` cluster setting can now accept JSON as an alternative to the basic URL string setting. If a JSON value is set, it must contain a `redirect_url` key that maps to an object with key-value pairs where the key is a `region` matching an existing locality setting and the value is a callback URL. #57519

### General changes

- CockroachDB now runs fewer threads in parallel if running inside a container with a CPU limit. #57390
- Upgraded the CockroachDB binary to Go 1.15.6. #57713
- The new cluster setting `server.eventlog.enabled` controls whether notable events are also written to the table `system.eventlog`. Its default value is `true`. Changing this cluster setting can help recovering partial cluster availability when the `system.eventlog` table becomes unavailable. Note that even when `false`, notable events are still propagated to the logging system, where they can be redirected to files or other log sinks. #57879
- Moved RBAC resources in Kubernetes manifests to `rbac.authorization.k8s.io/v1`. #55065
- Cluster version upgrades, as initiated by `SET CLUSTER SETTING version = <major>-<minor>`, now perform internal maintenance duties that will increase the time that it takes for the command to complete. This delay is proportional to the amount of data currently stored in the cluster. The cluster will also experience a small amount of additional load during this period while the upgrade is being finalized. These changes expand upon our original prototype in #57445. #58088
- Upgraded to v3.9.2 of `protobuf` to consume new changes for Bazel. #58891
- Increased the default value of `sql.tablecache.lease.refresh_limit` to 500 in order to accommodate larger schemas without encountering `RETRY_COMMIT_DEADLINE_EXCEEDED` errors and generally higher latency. #59319

### Enterprise edition changes

- CockroachDB now permits partitioning by user-defined types such as enums. #57327

### SQL language changes

- Added a new built-in method to calculate Voronoi polygons. #56496
- Added a new built-in method to calculate Voronoi lines. #56496
- Previously, timezones had to be entered in the same case as they were stored on the system. Now, timezone names can be case-insensitive provided they match well-known zone names according to Go's `time/tzdata` package. #57250
- Added `LOCALITY REGIONAL BY TABLE IN PRIMARY REGION` syntax for configuring table locality. #57275
- The default value for `vectorize_row_count_threshold` setting has been decreased from 1000 to 0, meaning that, by default, CockroachDB will always use the vectorized engine for all supported queries regardless of the row estimate (unless `vectorize=off` is set). #55713
- Added the ability to `CREATE TABLE` with LOCALITY set. #57419
- `EXPLAIN` and `EXPLAIN ANALYZE` now show counts and durations in a more user-friendly way. #57420
- `EXPORT` can now be used by non-`admin` users with `SELECT` privilege on the table being exported, unless the destination URI requires `admin` privileges. #57380
- `CREATE DATABASE ... WITH [PRIMARY] REGIONS` now modifies the zone configurations with the settings as set by the multiregion configuration. #57244
- `SHOW [BACKUP]` is no longer `admin`-only. It depends on the URI construct and the credentials specified in the `SHOW [BACKUP]` query. #57412
- built-in aggregate function `regr_avgx` is now supported. #57295
- Added support for running `IMPORT` in a mixed-version cluster. #57382
- Introduced `SHOW REGIONS FROM ALL DATABASES`, which shows region metadata for each database in the table. #57500
- Added `collations` table to the `information_schema`. The `collations` table contains the collations available in the current database. #57609
- Added `collation_character_set_applicability` table to the `information_schema`. #57609
- `LOCALITY` directives on `CREATE TABLE` are now persisted onto the database. #57513
- Multi-region defined table localities now display on the `SHOW CREATE TABLE` command. #57513
- Arrays in `pg_catalog` tables now are displayed in a format that matches PostgreSQL. #56980
- The `SET TRACING` `local` option (to only trace messages issued by the local node) was removed. #57567
- Several `SHOW` commands have been adjusted to enforce a particular ordering of the output rows. #57644
- Implemented `regr_avgy` built-in aggregation function. #57583
- `crdb_internal.tables` and `SHOW TABLES` now show locality data on the tables. #57653
- `CREATE TABLE ... LOCALITY ...` statements now modify the zone configurations to be in line with the desired locality. #57654
- Added `sql.trace.stmt.enable_thresold`. Similar to `sql.trace.txn.enable_threshold`, this logs a trace of any statements that take longer than the specified duration. This new setting allows for more granularity than the transaction threshold since it applies to individual statements and does not include overhead such as communication with a SQL client. #57733
- Added a session setting `experimental_enable_unique_without_index_constraints` and cluster default `sql.defaults.experimental_enable_unique_without_index_constraints.enabled` to enable the use of `UNIQUE WITHOUT INDEX` syntax. The default value of both settings is `false` since this feature is not yet fully supported. Use of `UNIQUE WITHOUT INDEX` also depends on all nodes being upgraded to the cluster version `UniqueWithoutIndexConstraints`. #57666
- Implemented built-in function `levenshtein`. #56843
- Added table-view dependency information in `pg_depend` to improve compatibility with PostgreSQL. #57240
- The cluster event logging system has been modernized. In particular, the schema of the entries for the `info` column in `system.eventlog` has been stabilized. #57737
- The `targetID` and `reportingID` columns in `system.eventlog` are now deprecated. Their values, for relevant event types, can be found as fields inside the `info` column instead. #57737
- Added the `soundex()` and `difference()` built-in functions. #57615
- New `EXPLAIN ANALYZE (DISTSQL)` output format. `EXPLAIN ANALYZE (DISTSQL)` now only works as a top-level statement (it can no longer be part of a bigger query). #57804
- Added built-in function `ST_OrientedEnvelope` to calculate the mimimum-area rotated rectangle. #57697
- A new `default_transaction_use_follower_reads` session variable is now supported, which configures SQL transactions to perform stale reads from follower replicas. #57851
- Both `ALTER TABLE OWNER` and `REASSIGN OWNED BY` now report structured notable events about the ownership changes. Note that `REASSIGN OWNED BY` currently also reports an `alter_table_owner` for views and sequences that were implicitly reassigned, even though CockroachDB does not yet support the `ALTER VIEW OWNER` / `ALTER SEQUENCE OWNER` statements. #57969
- `EXPLAIN (DISTSQL)` has a new output schema and format. #57954
- Added an overload to `crdb_internal.pb_to_json` to suppress populating default values in fields. #58087
- `IMPORT INTO` for CSV now supports `nextval` as a default expression of a non-targeted column. #56473
- The `EXPLAIN` output for foreign key checks is now labeled `constraint-check` rather than `fk-check`. This change is in preparation for adding support for unique constraint checks, which will use the same label. #58053
- The error message for unique constraint violations now matches the error used by PostgreSQL. For example, the new error message looks like this: `ERROR: duplicate key value violates unique constraint "primary" DETAIL: Key (k)=(1) already exists.` #58053
- `EXPLAIN ANALYZE` diagrams now contain "network hops" information on streams. #58078
- Added support for the `IF NOT EXISTS` prefix to `CREATE TYPE` statements. #58173
- Added a `session_variables` table to the `information_schema`. The `session_variables` table exposes the session variables. #57837
- Indexing into scalar JSON values using an integer index is now properly supported. #58359
- The `crdb_internal.cluster_id` method now returns the ID of the underlying KV cluster in multi-tenant scenarios rather than the Nil UUID. The `ClusterID` is needed for logging and metrics for SQL tenants. #58317
- `SHOW STATEMENTS` is now an alias of `SHOW QUERIES`. The new syntax is preferred by the SQL parser. #58072
- Implemented the `gateway_region` built-in, which returns the region of the connection's current node. #58423
- `BACKUP` and `RESTORE` now use the block-level checksums embedded in their data files instead of collecting / verifying more expensive file-level SHA512 checksums. #58487
- Multi-tenant clusters will now send anonymous usage information to the central CockroachDB registration server. #58399
- Added support for `ALTER DATABASE ... ADD REGION`. #58233
- Multiple `SHOW ZONE CONFIGURATION` statements that previously used `FOR` can now also use `FROM` (e.g., `SHOW ZONE CONFIGURATION FOR RANGE`). This change standardizes the use of `FROM` for `SHOW ZONE CONFIGURATION`. #58740
- Implemented `SHOW REGIONS`, which shows a list of regions along with the databases associated with them. #57618
- Added `ALTER DATABASE ... PRIMARY REGION`. #58725
- Columns implicitly added from hash-sharded indexes will no longer display on `pg_index` and `pg_indexes`. #58749
- Added a new `implicit` column to `crdb_internal.index_columns`, which signifies whether the column is implicitly added onto the index through `PARTITION BY` with an implicit column or a hash-sharded index. #58749
- CockroachDB now omits implicit columns or hash-sharded index columns from automatically generated index names. #58898
- Implemented `PARTITION ALL BY` syntax for `CREATE TABLE`, which automatically partitions the table, and all indexes, with the same partitioning scheme. #58748
- `PARTITION ALL BY` tables now display correctly on `SHOW CREATE TABLE`. #58928
- Creating a table and changing its schema within a transaction no longer schedules a schema change job. #58888
- Implemented geo built-in function `ST_GeneratePoints`. #58288
- Hash aggregation can now spill to disk when it exhausts its memory limit when executed via the vectorized engine. #57817
- Implemented `ALTER DATABASE ... SURVIVE REGION/ZONE FAILURE`. #58937
- `CREATE INDEX` will now inherit `PARTITION BY` clauses from `PARTITION ALL BY` tables. #58988
- Implemented the geometry-based built-in functions `ST_LineSubstring({geometry,float8,float8})`. #58125
- Implemented `REGIONAL BY ROW` logic on `CREATE TABLE`. This is gated behind the experimental session variable `experimental_enable_implicit_column_partitioning`. #58987
- Added support for `ALTER VIEW` / `SEQUENCE OWNER TO` commands. #59049
- Casts to type `unknown[]` are no longer accepted in CockroachDB. Any such casts will fail to parse and return the error `ERROR: type "unknown[]" does not exist`. This is consistent with PostgreSQL's behavior. #59136
- Implemented `REGIONAL BY ROW AS ...`, which allows a column of type `crdb_internal_region` to be used as a column for `REGIONAL BY ROW` multi-region properties. #59121
- Enabled altering of table locality for tables that are `REGIONAL BY TABLE` to `REGIONAL BY TABLE` (but in a different region). #59144
- `PARTITION ALL BY` statements will now apply for tables when using `ALTER PRIMARY KEY`. #59178
- Implemented the geometry built-in function `ST_ShiftLongitude`, which is useful for plotting data on a Pacific-centred map. #59234
- Implemented `ALTER TABLE ... SET LOCALITY GLOBAL` for tables starting as `REGIONAL BY ROW`. #59256
- Improved the error message when people use set `search_path` incorrectly, or with a schema that legitimately has a comma in its name. #53974
- Creation of interleaved tables and indexes is now disabled by default. They can be re-enabled temporarily by running `SET CLUSTER SETTING sql.defaults.interleaved_tables.enabled=true`. #59248
- CockroachDB now uses a structured logging format for the SQL audit, execution, and query logs. See the generated reference documentation for details. Of note, audit and execution logs now also include information about whether a query plan contains full index scans. Previously, this information was only included in the slow query log. #59110
- `CREATE INDEX` on `REGIONAL BY ROW` tables will now correctly include the implicit partitioning and inherit the correct zone configurations. #59223
- Made a minor improvement to `EXPLAIN` output for "render" node. #59313
- `EXPLAIN ANALYZE` now defaults to the new `EXPLAIN ANALYZE (PLAN)`, which shows a text representation of the logical plan, annotated with execution statistics. #57337

### Command-line changes

- The logging configuration can now be specified via the `--log` parameter. See the documentation for details. The flags `--log-dir`, `--log-file-max-size`, `--log-file-verbosity`, `--log-group-max-size` are now deprecated. #57134
- A new command `cockroach debug check-log-config` prints out the logging configuration that results from the provided combination of `--store`, `--log`, and other logging-related flags on the command line. #57134
- The events that were previously only stored in `system.eventlog` are now also directed unconditionally to an external logging channel using a JSON format. Refer to the configuration to see how to customize how events are directed to external sinks. Note that the exact external output format (and thus how to detect/parse the events from e.g., log files) is not yet stabilized and remains subject to change. #57737
- Notable events that pertain to SQL schema, user, and privilege changes are now sent on the new `SQL_SCHEMA`, `USER_ADMIN`, and `PRIVILEGES` logging channels. These can now be redirected to different sinks from the other log entries.
  - The `SQL_SCHEMA` channel is used to report changes to the SQL logical schema, excluding privilege and ownership changes (which are reported on the separate channel `PRIVILEGES`) and zone config changes (which go to `OPS`). This includes:
    - Database, schema, table, sequence, view, and type creation.
    - Adding, removing, and changing table columns.
    - Changing sequence parameters.
    - More generally, changes to the schema that affect the functional behavior of client apps using stored objects.
  - The `USER_ADMIN` channel is typically configured in "audit" mode, with event numbering and synchronous writes. It is used to report changes in users and roles, including:
    - Users added and dropped.
    - Changes to authentication credentials, including passwords, validity, etc.
    - Role grants and revocations.
    - Role option grants and revocations.
  - The `PRIVILEGES` channel is typically configured in "audit" mode, with event numbering and synchronous writes. It is used to report data authorization changes, including:
    - Privilege grants and revocations on database, objects, etc.
    - Object ownership changes. #51987
- Logging events that are relevant to cluster operators are now categorized under the new `OPS` and `HEALTH` logging channels. These can now be redirected separately from other logging events.
  - The `OPS` channel is used to report "point" operational events, initiated by user operators or automation:
    - Operator or system actions on server processes: process starts, stops, shutdowns, and crashes (if they can be logged). Each event includes any command-line parameters and the CockroachDB version.
    - Actions that impact the topology of a cluster: node additions, removals, decommissions, etc.
    - Cluster setting changes
    - Zone configuration changes.
  - The `HEALTH` channel is the channel used to report "background" operational events, initiated by CockroachDB for reporting on automatic processes:
    - Current resource usage, including critical resource usage.
    - Node-node connection events, including connection errors and gossip details.
    - Range and table leasing events.
    - Up- and down-replication.
    - Range unavailability. #57171
- Server terminations that are triggered when a node encounters an internal fatal error are now reported on the `OPS` logging channel. The exact text of the error is not reported on the `OPS` channel, however, as it may be complex (e.g., when there is a replica inconsistency); and the `OPS` channel is typically monitored by tools that just detect irregularities. The text of the message refers instead to the channel where the additional details can be found. #57171
- The notable events `set_zone_config` and `remove_zone_config` are now sent to the `OPS` logging channel. #57171
- Added a flag to `cockroach debug decode-proto` to suppress populating default values in fields. #58087
- When a SQL notable (structured) event is logged, the payload now attempts to include the session's `application_name` as field `ApplicationName`. This is intended for use when setting up event routing and filtering in external tools. #58130
- It is now possible to set the `format` parameter of any log sink, including file sinks, to `json`, `json-compact`, `json-fluent`, or `json-fluent-compact` to write entries as structured JSON. Refer to the generated reference documentation for details. #58126
- The DB Console URL printed by `cockroach demo` now automatically logs in the user when pasted into a web browser. #56740
- The URLs generated when `cockroach demo` starts have been made shorter and easier to copy/paste by shortening the generated password. #58305
- When using the JSON output formats for log entries, the server identifiers are now reported as part of each payload once known (either cluster ID + node ID in single-tenant or KV servers, or tenant ID + SQL instance ID in multi-tenant SQL servers). #58128
- Fixed `cockroach demo --global` from crashing with `didn't get expected magic bytes header`. #58466
- Previously, for certain log files, CockroachDB would both flush individual writes (i.e., propagate them from within the `cockroach` process to the OS) and also synchronize writes (i.e., ask the OS to confirm the log data was written to disk). Per-write synchronization was found to be unnecessary and possibly detrimental to performance and operating cost, so it was removed. Now, the log data continues to be flushed, as before, and CockroachDB only requests synchronization periodically (every 30s). #58995
- The parameter `sync-writes` for file sink configurations has been removed. (This is not a backward-incompatible change because the configuration feature is new in v21.1.) #58995
- The parameter `buffered-writes` for file sink configurations has been added. It is set to `true` (writes are buffered) by default and set to `false` (i.e., avoid buffering and flush every log entry) when the `auditable` flag is requested. #58995
- The default output format for `file-group` and `stderr` sinks has been changed to `crdb-v2`. This new format is non-ambiguous and makes it possible to reliably parse log files. Refer to the format's documentation for details. Additionally, it prevents single log lines from exceeding a large size; this problem is inherent to the `crdb-v1` format and can prevent `cockroach debug zip` from retrieving `v1` log files. The new format has also been designed so that existing log file analyzers for the `crdb-v1` format can read entries written the new format. However, this conversion may be imperfect. Refer to the new format's documentation for details. In case of incompatibility, users can force the previous format by using `format: crdb-v1` in their logging configuration. #59087

### API endpoint changes

- The notable event `create_statistics` is only reported when the cluster setting `sql.stats.post_events.enabled` is enabled. This fact is now also reported in the event log reference documentation. #57877
- The `Timestamp` field of structured notable events is now numeric, and encodes a number of nanoseconds since the Unix epoch. #58070
- The Health API now checks that the SQL server is ready to accept clients when a readiness check is requested. #59350

### DB Console changes

- Minor style changes to represent new branding palette. #57130
- Changed the default per-page value on the Transactions page to 20; minor style updates. #57824
- Added a timeseries graph indicating statement denials due to feature flags on the SQL Dashboard of DB Console. #57533
- Updated labels on the Hardware Dashboard to be more accurate. #57224
- Updated the link back to Statements from Statement Details so that it will always link to the Statements list instead of invoking the "back" action on the browser. #57975
- On the Sessions page, every `age` label has been replaced with `duration` and every `txn` label has been replaced with `transaction`. The actual metrics remain unchanged. #58616
- Renamed the `CPUs` metric column to `vCPUs` in the Node List on the Cluster Overview page. #58495
- Added Open SQL Transactions to the SQL Dashboard and renamed `SQL Queries` to `SQL Statements`. Removed references to "Distributed SQL Queries" when metrics really are discussing all SQL queries. #57477

### Bug fixes

- Fixed a bug in `RESTORE` where some unusual range boundaries in interleaved tables caused an error. #58219
- Previously, CockroachDB would encounter an internal error when performing a `JSONB - String` operation via the vectorized execution engine. This has been fixed. The bug was introduced in v20.2.0. #57349
- Previously, in rare situations, an automated replication change could result in a loss of quorum. This was possible when a cluster had down nodes and a simultaneous change in replication factor. Note that a change in the replication factor can occur automatically if the cluster is comprised of fewer than five available nodes. Experimentally the likelihood of encountering this issue, even under contrived conditions, was small. #56735
- Fixed an internal error when using aggregates and window functions in an `ORDER BY` for a `UNION` or `VALUES` clause. #57498
- `DROP TYPE` and certain other statements that work over SQL scalar types now properly support type names containing special characters. #57354
- Fixed a performance regression introduced in v20.2 to reading virtual tables which introspect the schema. #57542
- Removed `system.jobs` full table scan that is expensive in the face of many completed jobs. #57587
- In v20.2.0 we mistakenly permitted users with the `admin` role to drop tables in the system database. This commit revokes that privilege. #57568
- Previously, users could not perform a cluster restore from old backup chains (incrementals on top of fulls) when using the `BACKUP INTO` syntax. #57656
- Fixed a bug that could cause `IMPORT` to incorrectly read files stored on Google Cloud if uploaded using its compression option (`gsutil -Z`). #57745
- Fixed a bug where `ST_MakeLine` and `ST_Collect` did not respect ordering when used over a window clause. #57724
- 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). #57598
- 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. #57598
- Adding a primary key constraint to a table without a primary key no longer ignores the name specified for the primary key. #57146
- Previously, when displaying replica counts during the decommissioning process, we were overcounting the replicas displayed for r1. This is no longer the case. #57812
- Fixed a bug whereby tables in schemas other than `public` would not be displayed when running `SHOW TABLES FROM <db>`. #57749
- Fixed a bug where canceled queries reading from virtual tables could cause a crashing panic. #57828
- Fixed an assertion error caused by some DDL statements used in conjunction with common table expressions (`WITH`). #57927
- Previously, `SHOW GRANTS ON DATABASE` did not include privileges that were granted on a database. Now it does. The `SHOW GRANTS ON DATABASE` output no longer includes a column for `schema_name`, as these grants are not specific to any schema. #56866
- The `information_schema.schema_privileges` table now includes the correct schema-level privileges for non-user-defined schemas. Previously, all of these schemas were omitted from the table. #56866
- The `has_schema_privilege` built-in function now works on user-defined schemas when checking for the `USAGE` privilege. #56866
- The `ST_FrechetDistance` built-in function no longer causes a `SIGFPE` panic for very small values of `densifyFrac` (e.g., 1e-20), and returns an error instead. #57966
- CockroachDB now removes a node's status entry when the node is decommissioned, to prevent it from appearing in API calls and UIs, and prevent it from affecting node constraints such as localities and attributes for various operations. #56529
- Fixed a bug which caused type information to be omitted when decoding descriptors using either `crdb_internal.pb_to_json` or `cockroach debug decode-proto`. #58087
- Fixed a bug from v21.1.0-alpha.1 where the binary could crash if a running node lost its claim to a job while updating. #58161
- Fixed a bug where multiple invocations of `AddGeometryColumn` affecting the same table would result in only the last invocation applying. #56663
- Previously, CockroachDB could return non-deterministic output when querying the `information_schema.statistics` virtual table (internally used by `SHOW INDEXES`)—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. #58191
- Fixed a `column family 0 not found` crash caused by explaining or gathering statement diagnostics on certain queries involving virtual tables. #58208
- Added a safeguard against crashes while running `SHOW STATISTICS USING JSON`, which is used internally for statement diagnostics and `EXPLAIN ANALYZE (DEBUG)`. #58221
- Fixed a bug where prior schema changes on a table that failed and could not be fully reverted could prevent the table from being dropped. #57836
- Previously, CockroachDB could crash when performing a `DELETE` operation after an alteration of the primary key when in some cases. This is now fixed. The bug was introduced in v20.1. #58153
- Fixed a bug that could cause incremental backups to a backup in a collection (i.e., `BACKUP INTO ... IN ...`) on some cloud storage providers to ignore existing incremental backups previously appended to that destination and instead backup incrementally from the base backup in that destination. #58292
- Fixed an internal panic when using the `SHOW STATISTICS USING JSON` statement on a table containing `ENUM` types. #58251
- A memory leak in the optimizer has been fixed. The leak could have caused unbounded growth of memory usage for a session when planning queries on tables with partial indexes. #58306
- Fixed a bug where primary key changes on tables being watched by changefeeds would be silently ignored. #58140
- The `pg_catalog` metadata tables were using the `CHAR` data type for single-byte character columns. Now they use the `char` data type, to match PostgreSQL. This resolves errors that would occur when using some drivers (like `tokio-postgres` for Rust) to access `pg_catalog` tables in the binary query format. #58084
- Prepared statements that include enums and use the binary format will no longer result in an error. #58043
- Fixed an internal error that could occur when executing prepared statements with placeholders that delete data from columns referenced by a foreign key with `ON DELETE CASCADE`. #58431
- Fixed a bug which caused errors when querying a table with a disjunctive filter (an `OR` expression) that is the same or similar to the predicate of one of the table's partial indexes. #58434
- Previously, in event log entries for `ALTER TYPE` and `DROP TYPE` statements, the `TypeName` field did not contain fully qualified names. #58257
- A `CREATE TABLE` statement with indexes with duplicate names will no longer result in an assertion failure. This bug was present since v20.2. #58433
- Previously, event logs were not capturing the qualified table names for `COMMENT ON INDEX` and `COMMENT ON COLUMN` commands. This PR changes the event logs to use the qualified table name. Tests were also added for these changes. #58472
- The `has_${OBJECT}_privilege` built-in methods such as `has_schema_privilege` now additionally check whether the roles of which a user is a direct or indirect member also have privileges on the object. Previously only one user was checked, which was incorrect. This bug has been present since v2.0 but became more prominent in v20.2 when role-based access control was made available in the core version of CockroachDB. #58254
- CockroachDB previously would return an internal error when attempting to execute a hash join on a JSON column via the vectorized engine. Now a more user-friendly error is returned. #57994
- Fixed a panic in protobuf decoding. #58716
- The user authentication flow no longer performs extraneous name lookups. This performance regression was present since v20.2. #58671
- CockroachDB could previously return an internal error when evaluating a binary expression between a Decimal and an Interval that required a cast to a Float when the value is out of range. A more user-friendly error is now returned instead. #58743
- Qualified table name for `alter_table_owner` event log. #58504
- Fixed a bug that caused errors when accessing a tuple column (`tuple.column` syntax) of a tuple that could be statically determined to be null. #58747
- The `indoption` column in `pg_catalog.index` is now populated correctly. #58947
- Previously, CockroachDB could encounter an internal error when executing queries with tuples containing null values and enums in a distributed setting. This is now fixed. #58894
- Fixed a nil pointer panic edge case in query setup code. #59002
- Non-ASCII characters in `NAME` results in `cockroach sql` / `demo` (e.g., in the results `SHOW TABLES`, SHOW CONSTRAINTS) are now displayed without being escaped to octal codes. #56630
- Garbage collection (GC) jobs now populate the `running_status` column for `SHOW JOBS`. This bug has been present since v20.1. #58612
- Previously, CockroachDB could encounter an internal error when executing queries with `BYTES` or `STRING` types via the vectorized engine in rare circumstances. This is now fixed. #59028
- Previously, the `substring` function on byte arrays would treat its input as unicode code points, which would cause the wrong bytes to be returned. Now it only operates on the raw bytes. #58265
- Previously, the `substring(byte[])` functions were not able to interpret bytes that had the `\` character since it was being treated as the beginning of an escape sequence. This is now fixed. #58265
- 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 has now been fixed. #59147
- Fixed an internal error that could occur when `ARRAY[NULL]` was used in a query due to incorrect typing. `ARRAY[NULL]` is now typed as `string[]` if the type cannot be otherwise inferred from the context. This is the same logic that PostgreSQL uses, thus improving compatibility in addition to fixing the internal error. #59136
- Fix a slow / hanging query that can be caused by using large `max_decimal_digits` on `ST_AsGeoJSON`. #59165
- Queries that attempt to retrieve just the key columns of a single system table row will no longer return erroneous values. #58659
- Fixed a bug in URL handling of HTTP external storage paths on Windows. #59216
- Previously, CockroachDB could crash when executing `ALTER INDEX ... SPLIT/UNSPLIT AT` when more values were provided than are explicitly specified in the index. This has been fixed. #59213
- Fixed a bug where multi-tenancy SQL pods would not successfully initialize the GEOS library. #59259
- Placeholder values are now included alongside statements in structured events. #59110
- Added qualification prefix for user-defined schema names in event logs. #58617
- Added qualification prefix for dropped views in event logs. #59058
- Parsing errors are no longer thrown when importing a `pgdump` file with array data. #58244
- Fixed a crash when creating backup schedules writing to GCS buckets. #57617
- CockroachDB now correctly exports `schedules_BACKUP_*` metrics as well as backup RPO metrics. #57488

### Performance improvements

- Previously, CockroachDB when performing an unordered `DISTINCT` operation via the vectorized execution engine would buffer up all tuples from the input, which is a suboptimal behavior when the query has a `LIMIT` clause. This has now been fixed. This behavior was introduced in v20.1. Note that the row-by-row engine doesn't have this issue. #57579
- The query optimizer can use filters that constrain columns to multiple constant values to generate lookup joins. For example, a join filter `x.a = y.a AND y.b IN (1, 2)` can be used to generate a lookup join on table `y` assuming that it has an index on `(a, b)` or `(b, a)`. #57690
- The query optimizer now explores plans with lookup joins on partitioned indexes, resulting in more efficient query plans in some cases. #57690
- Potentially improved performance for `UPDATE` statements where the table has computed columns that do not depend on updated columns. #58188
- CockroachDB now allows the storage engine to compact `sstables` based on reads, on read-heavy workloads. #58247
- Partial indexes with `IS NOT NULL` predicates can be used in cases where `JOIN` filters implicitly imply the predicate. This results in more efficient query plans for `JOIN`s and foreign checks. #58204
- Queries that use a geospatial GIN index can now take advantage of vectorized execution for some parts of the query plan, resulting in improved performance. #58241
- Previously, indexed columns of partial indexes were always fetched for `UPDATE`s and `UPSERT`s. Now they are only fetched if they are required for maintaining the state of the index. If an `UPDATE` or `UPSERT` mutates columns that are neither indexed by a partial index nor referenced in a partial index predicate, they will no longer be fetched (assuming that they are not needed to maintain the state of other indexes, including the primary index). #58358
- `UPDATE` operations on tables with partial indexes no longer evaluate partial index predicate expressions when it is guaranteed that the operation will not alter the state of the partial index. In some cases, this can eliminate fetching the existing value of columns that are referenced in partial index predicates. #58358
- The `sum_int` aggregate function is now evaluated more efficiently in a distributed setting. #58345
- `INSERT ... ON CONFLICT ... DO NOTHING` statements now use anti-joins for detecting conflicts. This simplifies the query plan for these statements, which may result in more efficient execution. #58679
- Improved the accuracy of histogram calculations for the following types: `string`/`uuid`/`inet` family. Additionally, support for `time`/`timetz` histogram calculations was also added. This improves optimizer's estimates and results in better query plans in certain instances. #55797
- The optimizer now uses collected histogram statistics to better estimate the cost of JSON and ARRAY GIN index scans, which may lead to more efficient query plans. #59326

### Build changes

- CockroachDB now builds on Ubuntu 20.10 and other distros using `gcc-10`. #58895

### Doc updates

- The types of logging sinks available through configuration are now automatically documented. #59083
- The various output formats available for logging configurations are now documented. See the generated reference documentation for details. #58075
- The cluster event logging system has been standardized. Reference documentation is now available (auto-generated from source code); changes to non-reserved payloads will now be announced at least one release version in advance. The event types are organized into broad categories: SQL Logical Schema Changes, SQL Privilege Changes, SQL User Management, CLuster-level events and SQL Miscellaneous operations. #57737
- A report of the possible logging severities and channels is now automatically generated. #57134

### Contributors

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

- Alan Acosta (first-time contributor)
- ArjunM98
- Azdim Zul Fahmi
- Cheng Jing (first-time contributor)
- Cyrus Javan
- Erik Grinaker
- Javier Fernandez-Ivern (first-time contributor)
- Kumar Akshay (first-time contributor)
- Maciej Rzasa (first-time contributor)
- Marcin Knychała
- Max Neverov
- Miguel Novelo (first-time contributor)
- Omar Bahareth (first-time contributor)
- Petr Jediný
- Ruixin Bao
- Saif Al-Harthi (first-time contributor)
- Vaibhav
- Yanxin (first-time contributor)
- b41sh (first-time contributor)
- mosquito2333 (first-time contributor)
- neeral

## v21.1.0-alpha.1

Release Date: December 8, 2020

### Backward-incompatible changes

- RocksDB can no longer be used as the storage engine. Passing in `--storage-engine=rocksdb` now returns an error. #55509
- Rows containing empty arrays in `ARRAY` columns are now contained in GIN indexes. This change is backward-incompatible because prior versions of CockroachDB will not be able to recognize and decode keys for empty arrays. Note that rows containing `NULL` values in an indexed column will still not be included in GIN indexes. #55970
- Concatenation between a non-null argument and a null argument is now typed as string concatenation, whereas it was previously typed as array concatenation. This means that the result of `NULL || 1` will now be `NULL` instead of `{1}`. To preserve the old behavior, the null argument can be casted to an explicit type. #55611

### General changes

- Added increased logging and metrics around slow disk operations. #54215
- CockroachDB now detects stalled disk operations better and crashes the process if a disk operation is taking longer than a minute. Added cluster settings to allow for tuning of this behavior. #55186
- Added some metrics surrounding schema changes. #54855
- Upgraded CockroachDB's version of Go to v1.15.4. #56363
- The timezone data is now built in to the CockroachDB binary, which is the fallback source of time if `tzdata` is not located by the default Go standard library. #56634
- Renamed instances of "Admin UI" to "DB Console" in the documentation of OIDC cluster settings. #56869
- Included `tar` in docker images. This allows users to use `kubectl cp` on 20.2.x containers. #57241

### Enterprise edition changes

- It is no longer allowed to widen an incremental-backup chain with the inclusion of new complete empty DBs. #54329
- Added cluster settings to enable/ disable the `BACKUP` and `RESTORE` commands. Attempts to use these features while they are disabled returns an error indicating that the database administrator has disabled the feature. Example usage: `SET CLUSTER SETTING feature.backup.enabled = FALSE; SET CLUSTER SETTING feature.backup.enabled = TRUE; SET CLUSTER SETTING feature.restore.enabled = FALSE; SET CLUSTER SETTING feature.restore.enabled = TRUE;`. #56533
- Added cluster settings to enable/ disable the `IMPORT`, `EXPORT`, and changefeed commands. Attempts to use these features while they are disabled returns an error indicating that the database administrator has disabled the feature. Example usage: `SET CLUSTER SETTING feature.import.enabled = FALSE; SET CLUSTER SETTING feature.import.enabled = TRUE; SET CLUSTER SETTING feature.export.enabled = FALSE; SET CLUSTER SETTING feature.export.enabled = TRUE; SET CLUSTER SETTING feature.changefeed.enabled = FALSE; SET CLUSTER SETTING feature.changefeed.enabled = TRUE;`. #56872

### SQL language changes

- Interleaved joins have been removed; merge joins are now planned in all cases when interleaved joins would have been planned previously. #54163
- It is now possible to create partial GIN indexes. The optimizer will choose to scan partial GIN indexes when the partial index predicate is implied and scanning the GIN index has the lowest estimated cost. #54376
- `EXPLAIN ANALYZE` diagrams now contain "bytes sent" information on streams. #54518
- Implemented the geometry built-in functions `ST_Rotate({geometry, float8, geometry})`. #54610
- Implemented the geometry built-in function `ST_ClosestPoint()`. #54843
- Implement the string built-in function `unaccent()`. #54628
- When enterprise features are not enabled, the `follower_read_timestamp()` function now returns `(statement_time - 4.8s)` instead of an error. #54951
- Added a new virtual table `crdb_internal.invalid_descriptors`, which runs validations on descriptors in the database context and reports any errors. #54017
- Implemented the built-in operator `add jsonb_exists_any(jsonb, text[])`. #55172
- Added the ability to optionally specify the `PRIVILEGES` keyword when issuing the `GRANT ALL` or `REVOKE ALL` statements, for Postgres compatibility. Previously, a statement like the following would fail with a syntax error: `GRANT ALL PRIVILEGES ON DATABASE a TO user1;`. #55304
- Implemented the built-in function `pg_column_size()`, which counts the amount of bytes stored by column. #55312
- Implemented the geometry built-in functions `ST_Rotate({geometry, float8, float8, float8})`. #55428
- Implemented the built-in function `ST_MemSize()`, which returns the memory space a geometry takes. #55416
- `SHOW ENUMS` now returns an array aggregation of enum values instead of having them separated by the `|` character. #55386
- Implemented the geometry built-in function `ST_PointInsideCircle()`. #55464
- Implement the geometry built-in function `ST_LineFromEncodedPolyline()`. #55429
- Implement the geometry built-in function `ST_AsEncodedPolyline()`. #55515
- Implement the geometry built-in function `ST_LineLocatePoint()`, which computes the fraction of the line segment that represents the location of the given point to the closest point on the original line. #55470
- Implemented the `REASSIGN OWNED BY ... TO ...` statement, which changes ownership of all database objects in the current database, owned by any roles in the first argument, to the new role in the second argument. #54594
- Implemented the geometry built-in function `ST_MinimumBoundingRadius()`, which returns a record containing the center point and radius of the smallest circle that can fully contain a geometry. #55532
- The vectorized engine now supports the JSONFetchValPath (`#>`) operator. #55570
- Added the ability to cast a string containing all integers into a given regtype, e.g., `'1234'::regproc`. #55607
- Potentially hazardous `DROP COLUMN` operations when `sql_safe_updates` is enabled now return a note and link to https://github.com/cockroachdb/cockroach/issues/46541. #55248
- Changed the `SPLIT AT` output to be consistent with the output from `SHOW RANGES`. #55543
- Implemented the geometry built-in function `ST_MinimumBoundingCircle()`, which returns polygon shape that approximates minimum bounding circle to contain geometry. #55567
- Added the constraint name to constraint errors, for increased wire-level Postgres compatibility. #55660
- Added support for using the syntax `... UNIQUE WITHOUT INDEX ...` in `CREATE TABLE` and `ALTER TABLE` statements, both when defining columns and unique constraints. Although this syntax can now be parsed successfully, using this syntax currently returns an error: `unique constraints without an index are not yet supported`. #55700
- Add the built-in functions `sha224()` and `sha384()`. #55720
- Implemented `SHOW REGIONS`, which returns all of regions available in a cluster. #55831
- Implemented the geography built-in function `ST_UnaryUnion()` #55894
- Implemented `ALTER TABLE ... SET LOCALITY/REGIONAL AFFINITY` statements, which configure multi-region properties of given tables. These are subject to change. #55827
- All expressions in `EXPLAIN` output that operate on indexes now show the table name the index is declared on, rather than just an alias. If the query aliases the table, the alias is also shown. For example, a scan on table `foo` that is aliased as `f` was previously displayed as `scan f`. It is now displayed as `scan foo [as=f]`. #55641
- Changed the underlying type for the version cluster setting. Previously, it was of an internal type representing "state machine", but now it's simply "version". This has no operational implications, but the `Type` column in `cockroach gen settings-list` now shows "version" instead of "custom validation". #55994,#56546
- Removed the `201auto` value for the `vectorize` session variable and the corresponding cluster setting. #55907
- Expanded the `CREATE SCHEMA`, `DROP SCHEMA`, `ALTER SCHEMA`, `GRANT ... ON SCHEMA`, `REVOKE ... ON SCHEMA`, and `SHOW GRANTS ON SCHEMA` statements to allow schema names prefixed with database names. #55647
- Added support for dollar-quoted strings with digit. #55958
- Added a new single-column output format for `EXPLAIN` and `EXPLAIN (VERBOSE)`. #55866
- Creating multi-column GIN indexes is now allowed by setting the `experimental_enable_mutlti_column_inverted_indexes` session setting to `true`. At this time, these indexes are not fully supported and their behavior is undefined. Using this feature will likely result in errors. Do not enable this setting in a production database. #55993
- Constraints that have not been validated are now marked "NOT VALID" in the output of `SHOW CREATE` and `SHOW CONSTRAINTS`. #53485
- The `NOT VALID` option can now be provided for `CHECK` and `FOREIGN KEY` constraints listed as table constraints in `CREATE TABLE` statements. This option has no affect on the constraint created. It will not skip validation. #53485
- Added a pgcode (`42704`, `undefined_object`) to the error returned when attempting to drop an index by a table and index name that doesn't exist. #55417
- Added the `WITH row_limit="{$num}"` option for importing CSVs to allow users to do a quick test run on an import of `$num` rows. Example: `IMPORT TABLE test ... CSV DATA ... WITH row_limit="3";` #56080
- Added the `WITH row_limit="{$num}"` option for importing `DELIMITED/AVRO` data to allow users to do a quick test run on an import of $num rows. Example: `IMPORT TABLE test ... DELIMITED/AVRO DATA ... WITH row_limit="3";` #56135
- Added `WITH row_limit="{$num}"` option for importing bundle formats to allow users to do a quick test run on an import of $num rows. Example: `IMPORT ... WITH row_limit="3";`. #56587
- `EXPLAIN ANALYZE` diagrams now contain "network latency" information on streams. #55705
- Implemented the `covar_pop()` and `covar_samp()` aggregation functions. #55707
- Prevented column type modification of columns that are depended on by views. #56213
- Implemented the geometry built-in functions `ST_TransScale({geometry,float8,float8,float8,float8})` #56198
- Implemented the geometry built-in function `ST_Node()`. #56183
- The concatenation operator `||` can now be used between strings and any other non-array types. #55611
- CockroachDB now returns a float instead of a decimal when at least one argument of an aggregate function is decimal. #56296
- Implemented the `regr_intercept()`, `regr_r2()`, and `regr_slope()` aggregation functions. #56296
- `EXPLAIN ANALYZE` diagrams now show "deserialization time" on streams instead of "io time". #56144
- Added a pgcode (`42804`, `DatatypeMismatch`) when adding a default value of the wrong type to a column. #56455
- Attempting to rename an undefined index now returns a `pgcode.UndefinedObject` (`42704`) error instead of an uncategorized error. #56455
- Implemented the `regr_sxx()`, `regr_sxy()`, and `regr_syy()` aggregation functions. #56585
- `SHOW REGIONS` has changed the column name for availability zones to "zones" from "availability_zones". #56344
- Introduced a `pg_collation` of "default". Strings now return the "default" collation OID in the `pg_attribute` table (this was previously `en_US`). The "default" collation is also visible on the `pg_collation` virtual table. #56598
- A table can now successfully be dropped in a transaction following other schema changes to the table in the same transaction. #56589
- Added a new variant of explain: `EXPLAIN ANALYZE (PLAN)`. #56524
- `SHOW REGIONS` functionality is now deferred to `SHOW REGIONS FROM CLUSTER`. #56627
- It is now possible to hint to the optimizer that it should plan an inverted join by using the syntax `... INNER INVERTED JOIN ...` or `... LEFT INVERTED JOIN ...`. If the hint is provided and it is possible to plan an inverted join, the optimizer will now plan an inverted join, even if it estimates that a different plan would have a lower cost. If the hint is provided but it is not possible to plan an inverted join because there is no GIN index on the right side table or the join condition is not a valid inverted join condition, the database will return an error. #55679
- Added the empty `pg_catalog.pg_opclass` table to improve compatibility with Postgres. #56653
- `SURVIVE AVAILABILITY ZONE FAILURE` is now `SURVIVE ZONE FAILURE`. #56837
- Added admin-only, `crdb_internal` functions to enable descriptor repair in dire circumstances. #55699
- Added support for an optional `=` character for `SURVIVE`, e.g., `ALTER DATABASE d SURVIVE = ZONE FAILURE`. #56881
- Introduced stubs for `ALTER DATABASE ... PRIMARY REGION` and `CREATE TABLE ... PRIMARY REGION`. #56883
- Dropping the primary index using `DROP INDEX` now returns a `FeatureNotSupported` error along with hints showing supported ways to drop primary indexes. #56858
- Renaming an index to a name that is already being used for another index will now return a `pgcode.DuplicateRelation` (`42P07`) error instead of an uncategorized error. #56681
- The `relpersistence` column in `pg_catalog.pg_class` now correctly displays `t` as the persistence status for temporary tables. #56827
- Added a deprecation notice to statements containing the `INTERLEAVE IN PARENT` clause. #56874
- `SHOW DATABASES` and `crdb_internal.databases` now display all regions as well as survival goals for a given database. #56880
- Adds a feature flag via cluster settings for the `CREATE STATISTICS` and `ANALYZE` feature. If a user attempts to use the command while disabled, an error indicating that the database administrator had disabled the feature is surfaced. Example usage: `SET CLUSTER SETTING feature.stats.enabled = FALSE; SET CLUSTER SETITNG feature.stats.enabled = TRUE;`. #57076
- `CREATE DATABASE ... PRIMARY REGION` is now stored on the database descriptor. #57038
- `SHOW DATABASES` and `crdb_internal.databases` now display the `PRIMARY REGION` set on the database descriptor as the `primary_region` column. #57038
- Added a feature flag via cluster settings for all schema change-related features. If a user attempts to use these features while they are disabled, an error indicating that the database administrator has disabled the feature is surfaced. Example usage: `SET CLUSTER SETTING feature.schema_change.enabled = FALSE; SET CLUSTER SETTING feature.schema_change.enabled = TRUE;`. #57040
- Changed `pg_constraint` column types for `confkey` and `conkey` to `smallint[]` to improve compatibility with Postgres. #56975
- The `ALTER TABLE...SPLIT/UNSPLIT` and `ALTER INDEX...SPLIT/UNSPLIT` commands are now gated by a schema change feature flag. If a user attempts to use these features while they are disabled, an error indicating that the system administrator has disabled the feature is surfaced. Example usage: `SET CLUSTER SETTING feature.schema_change.enabled = FALSE SET CLUSTER SETTING feature.schema_change.enabled = TRUE;`. #57142
- When creating a database with the regions clause specified, CockroachDB now creates a `regions` enum type automatically. #56628
- Implemented `SHOW REGION FROM DATABASE` and `SHOW REGION FROM DATABASE db`, which shows all regions for the given database, as well as whether that region is the primary region. #57106
- `CREATE TABLE AS SELECT ... FROM ... AS OF SYSTEM TIME x` is now supported. #55916
- Implemented the function `regr_count()`. #56822
- Added the `character_sets` table to the `information_schema`. #56953
- `SHOW ENUMS` is now extended to take an optional `FROM` clause. The user can specify either the schema name or both the database name and schema name separated by `.`. If a hierarchy is specified, the statement returns enums falling in that hierarchy rather than all of the enums in the current database. #57197
- The multi-region enum, created implicitly for all multi-region databases, can be introspected using the `pg_catalog.pg_enum` table. It is also displayed in `SHOW ENUMS`. #57197
- Implemented the geography built-in function `ST_Subdivide()`. #56898
- A `pgcode.UndefinedColumn` error is now returned when adding a unique constraint to one or more undefined columns. #57316
- The database name is now displayed in `SHOW REGIONS FROM DATABASE`. #57278
- Added `SHOW SURVIVAL GOAL FROM DATABASE [database]`, which shows the survival goal for a multi-region database. #57278
- Added the `uuid_generate_v4()` built-in function. It works exactly like `gen_random_uuid()` but was added for compatibility with Postgres versions older than PG13. #57212

### Command-line changes

- A `debug.zip` file now includes a script, `hot-ranges.sh`, which will summarize the hottest ranges in the cluster. #53547
- `cockroach sql` and `cockroach demo` now support the command-line parameter `--file` (shorthand `-f`) to read commands from a named file. The behavior is the same as if the file was redirected on the standard input; in particular, the processing stops at the first error encountered (which is different from interactive usage with a prompt). Note that it is not yet possible to combine `-f` with `-e`. #54741
- The large banner message "Replication has been disabled for this cluster ..." that was unconditionally emitted on the standard error stream for `cockroach start-single-node` has now become a simple log message at severity `INFO`. #54749
- `cockroach demo` now pre-creates a `demo` user account with a random password to discourage the user of `root`. The `demo` account is currently granted the `admin` role. #54749
- The CLI help text for `--max-disk-temp-storage` now properly reports the default value. #54853
- The help text displayed by `\?` in `cockroach sql` and `cockroach demo` now groups the recognized client-side commands into sections for easier reading. #54796
- The client-side command `\show` for the SQL shell is deprecated in favor of the new command `\p`. This prints the contents of the query buffer entered so far. #54796
- The new client-side command `\r` for the SQL shell erases the contents of the query buffer entered so far. This provides a convenient way to reset the input, for example, when the user gets themselves confused with string delimiters. #54796
- The SQL shell (`cockroach sql`, `cockroach demo`) now supports the client-side command `\echo`, like `psql`. This can be used, for example, to generate informational output when executing SQL scripts non-interactively. #54796
- The SQL shell (`cockroach sql`, `cockroach demo`) now support the `\i` and `\ir` client-side command which reads SQL file and evaluates its content in-place. `\ir` differs from `\i` in that the file name is resolved relative to the location of the script containing the `\ir` command. This makes `\ir` likely more desirable in the general case. Instances of `\q` inside a file included via `\i`/`\ir` stop evaluation of the file and resume evaluation of the file that included it. This feature is compatible with the identically named `psql` commands. It is meant to help compose complex initialization scripts from a library of standard components. For example, one could be defining each table and its initial contents in separate SQL files, and then use different super-files to include different tables depending on the desired final schema. #54796
- Removed the `debug sstables` command, superseded by the `debug pebble lsm` command. #54890
- Added the `cockroach debug pebble db checkpoint` debug command to easily create a checkpoint without using rocksdb. #55751
- Updated the `--storage-engine` help text to reflect RocksDB deletion. #55509
- Added support for `\connect DATABASE` and `\c DATABASE`. #55934
- Added an import CLI command that allows users to upload and import local dump files into a running cockroach cluster. `PGDUMP` and `MYSQLDUMP` formats are currently supported. #54896
- `cockroach demo` now allows for nodes to be added using the `\demo` client-side command. This works in both single node and multi-node configurations, for example, when started with `--nodes int` or `--geo-partitioned-replicas`. #56344

- Some specific situations now have dedicated exit status codes. The following codes are defined:

  Code | Description
  --- | ---
  0 | Process terminated without error.
  1 | An unspecified error was encountered. Explanation should be present in the stderr or logging output.
  2 | Go runtime error, or uncaught panic. Likely a bug in CockroachDB. Explanation may be present in logging output.
  3 | Server process interrupted gracefully with Ctrl+C / SIGINT.
  4 | Command-line flag error.
  5 | A logging operation to the process' stderr stream failed (e.g., stderr has been closed). Some details may be present in the file output, if enabled.
  6 | A logging operation to file has failed (e.g., log disk full, no inodes, permission issue, etc.). Some details may be present in the stderr stream.
  7 | Server detected an internal error and triggered an emergency shutdown.
  8 | Logging failed while processing an emergency shutdown.

  #56574

- `cockroach demo` now tries to use the same TCP port numbers for the SQL and HTTP servers on every invocation. This is meant to simplify documentation. These defaults can be overridden with the new (`demo`-specific) command line flags `--sql-port` and/or `--http-port`. #56737

- The SQL shell now accepts `yes`/`no` as boolean options for slash commands, following `psql` behavior. #56829

- A `\x [on|off]` command has been added to toggle the `records` display format, following `psql` behavior. #56829

- CockroachDB now prints a warning if the `--locality` flag does not contain a "region" tier. #57179

### API endpoint changes

- Added a new prometheus metric called `seconds_until_license_expiry` that reports the number of seconds until the enterprise license on the cluster expires, a negative number if the expiration is in the past, or `0` if there is no license. #55565

### DB Console changes

- Changed the view for tables without data on main pages. #54943
- Updated the design of the custom date range selector on the Cluster > Maps view and Metrics pages #54851
- The DB Console now shows messages provided by server instead of custom generic messages defined on the client side, for example, messages about permission restrictions to show pages for non-admin roles. #50869
- Added a new metric called `raft.scheduler.latency`, which monitors the latency for operations to be picked up and processed by the Raft scheduler. #56943
- Redesigned inline error alerts when a user has insufficient rights to see some resources. #50869
- Implemented a permission denied error for non-admin users on the Node Map and Events views. #50869
- Tables within user-defined schemas are now included in the Data Distribution page. #56388
- Creating, dropping, and altering roles or users now causes events to be logged and displayed. #55945
- If statement diagnostics are enabled for a statement, the bytes sent over the network are now shown. #55969
- `ALTER DATABASE OWNER` and `CONVERT TO SCHEMA` now cause events to be logged and displayed. #55891
- Changing schema objects now causes events to be logged and displayed. #55785
- Changing privileges (i.e., with `GRANT` or `REVOKE`) now causes events to be logged and displayed. #55612
- Renaming databases or tables now causes events to be logged and displayed. #55269
- Added descriptions for failed job on the Job Details page. #54268

### Bug fixes

- Fixed the `rpath` and `so` names of `libgeos.so` and `libgeos_c.so` such that a `dlopen` to `libgeos.so` is not needed. #55129
- Made lease transfers during rebalancing adhere to the rate limit utilized in other lease transfer cases which eliminates unexpected lease oscillations when adding a new node. #54322
- CockroachDB now handles PostgreSQL "cancel" messages on TLS connections in the same way as when they are sent without TLS: the connection is closed, but no action takes place. No error is logged. As a reminder, PostgreSQL "cancel" messages are still unsupported in CockroachDB and client should still use `CANCEL QUERY` instead. #54618
- Cleared table statistics from job description in failed and canceled backup jobs. #54446
- Fixed an error message that referred to `experimental_enable_hash_sharded_indexes` as a cluster setting when it is in fact a session variable. #54960
- Fixed a nil pointer error that could occur at planning time for some spatial queries when a GIN index existed on a geometry or geography column. #55076
- Fixed `SHOW ENUMS` column names to have `values` instead of `string_agg` for column names, and `owner` for the owner itself. #55139
- Fixed `SHOW TYPES` to show the `owner` column instead of the `value` column. #55139
- Fixed a bug where empty enums did not show up for `SHOW ENUMS` or `SHOW TYPES`. #55143
- Fixed a bug where, on failure of `CREATE TABLE AS` or `CREATE MATERIALIZED VIEW`, tables would be left in an invalid non-public state until GC instead of being marked as dropped, possibly causing spurious validation failures. The bug was introduced in earlier 20.2 pre-releases. #55272
- Fixed a rare scenario in which a node would refuse to start after updating the binary. The log message would indicate: `store [...], last used with cockroach version [...], is too old for running version [...] (which requires data from [...] or later)`. #55240
- Changefeeds defined on multiple tables will now only backfill affected tables after a schema change. #55135
- Fixed a bug where adding child tables or types to a schema being restored would cause those new child objects to become corrupted with no parent schema if the restore job had to be rolled back. #55157
- Fixed a bug where the seconds component of a zone offset of a `TIMESTAMPTZ` value was not displayed. #55071
- Fixed a bug where casts to regclass were not escaped, e.g., when the type or table name had `"` characters. #55607
- Fixed a bug where casts from string to regproc, regtype or regprocedure would not work if they contained `"` characters at the beginning or at the end. #55607
- Fixed a bug which could cause `IMPORT`, `BACKUP`, or `RESTORE` to experience an error when they occur concurrently to when the cluster sets its version to upgraded. #55524
- Fixed a rare crash during tracing when reading un-decodable data. #55783
- Prevented a crash, introduced in the 20.2 series, caused by range scans over virtual tables with virtual indexes. #56459
- In some cases CockroachDB, would attempt to transfer ranges to nodes in the process of being decommissioned or being shut down; this could cause disruption the moment the node did actually terminate. This bug has been fixed. It had been introduced some time before v2.0. #55808
- Fixed a bug causing queries sent to a freshly-restarted node to sometimes hang for a long time while the node catches up with replication. #55148
- Fixed typing of collated strings so that collation names are case-insensitive and hyphens/underscores are interchangeable. #56352
- Fixed internal errors related to very large `LIMIT` and/or `OFFSET` values. #56672
- Improved the accuracy of reported CPU usage when running in containers. #56461
- Fixed a bug which can lead to canceled schema change jobs ending in the failed rather than canceled state. #55513
- Prevented an opportunity for livelock in the jobs subsystem due to frequent updates to already finished jobs. #56855
- The `LogFile` reserved API, which was used by `cockroach debug zip`, could corrupt log entries. This has been fixed. #56901
- `DELETE` statements no longer have a chance of returning an incorrect number of deleted rows in transactions that will eventually need to restart due to contention. #56458
- Fixed a race condition in the `tpcc` workload with the `--scatter` flag where tables could be scattered multiple times or not at all. #56942
- Fixed a bug where reg* types were not parsed properly over pgwire, `COPY` or prepared statements. #56298
- Previously, casts and parsing of strings to types could allow an out-of-bounds value to be successfully used (e.g., `SELECT -232321321312::int2`) but fail with an out-of-bounds message when it is inserted into the table. This is now checked when the value is parsed or being casted to. #55095
- `cockroach debug merge-logs --redact=true --redactable-output=false` now properly removes redaction markers. #57121
- Fixed a bug related to validation of un-upgraded pre-19.2 inbound foreign keys. #57132
- Creating a materialized view that references a column with a NULL value no longer results in an error. #57139
- `ST_GeomFromGeoJSON` now sets the SRID to 4326, matching PostGIS 3.0 / RFC7946 behavior. #57152
- Fixed a bug that caused an "ambiguous column reference" error during foreign key cascading updates. This error was incorrectly produced when the child table's reference column name was equal to the concatenation of the parent's reference column name and "_new", and when the child table had a `CHECK` constraint, computed column, or partial index predicate expression that referenced the column. This bug was introduce in version 20.2. #57153
- Fixed a bug that caused errors or corrupted partial indexes of child tables in foreign key relationships with cascading `UPDATE`s and `DELETE`s. The corrupt partial indexes could result in incorrect query results. Any partial indexes on child tables of foreign key relationships with `ON DELETE CASCADE` or `ON UPDATE CASCADE` actions may be corrupt and should be dropped and re-created. This bug was introduce in version 20.2. #57100
- Second timezone offsets for `TIMETZ` now correctly display over the Postgres wire protocol; these were previously omitted. #57265
- `SELECT FOR UPDATE` now requires both `SELECT` and `UPDATE` privileges, instead of just `UPDATE` privileges. #57309
- Fixed a bug where users of an OSS build of CockroachDB would see "Page Not Found" when loading the DB Console. #56591

### Performance improvements

- The optimizer can now deduce that certain variable arguments to functions must be non-null. This improves cardinality estimation for those variables and unlocks other types of optimizations. As a result, the optimizer may choose better query plans when a function is used as a filter predicate. #54558
- Improved the selectivity estimate for array contains predicates (e.g., `arr @> ARRAY[1]`) in the optimizer. This improves the optimizer's cardinality estimation for queries containing these predicates, and may result in better query plans in some cases. #54768
- Updated the cost model in the optimizer to make index joins more expensive and better reflect the reality of their cost. As a result, the optimizer will choose index joins less frequently, generally resulting in more efficient query plans. #54768
- The optimizer simplifies join expressions to only scan a single table when the join filter is a contradiction. A limitation, now removed, was preventing this simplification from occurring in some cases, leading to more efficient query plans in some cases. #54813
- Improved the efficiency of plans for the execution of left outer spatial joins. #55216
- The optimizer now considers partial indexes when exploring zigzag joins. This may lead to more efficient query plans for queries that (1) operate on tables with partial indexes and (2) have a filter that holds two columns, indexed by two indexes, constant. #55401
- The optimizer now attempts to split a query with a disjunctive filter (OR expression) into a UNION of index scans, where one or both of the scans is an unconstrained partial index scan. As a result, more efficient query plans may be generated for queries with disjunctive filters that operate on tables with partial indexes. #55915
- CSV imports should now be slightly faster. #55845
- Previously, all `CHECK` constraints defined on a table would be tested for every `UPDATE` to the table. Now, a check constraint will not be tested for validity when the values of columns it references are not being updated. The referenced columns are no longer fetched in cases where they were only fetched to test `CHECK` constraints. #56007
- Indexes on computed columns can now be utilized when filters reference the computed expression and not the computed column directly. #55867
- The query optimizer can now generate inverted zigzag joins over partial GIN indexes. This may lead to more efficient query plans when filtering by a column that is indexed by a partial GIN index. #56101
- They query optimizer can now plan zigzag joins on two partial indexes with the same predicate, leading to more efficient query plans in some cases. #56103
- The optimizer now converts inner joins with single-row values expressions into projections. This allows decorrelation of subqueries that only reference variables from the outer query, such as `SELECT (SELECT value + 10) FROM table`. #55961
- The optimizer may now plan an inverted join if two tables are joined on `JSONB` or `ARRAY` columns using a contains predicate (e.g., `WHERE a @> b`), and the first column has a GIN index. The inverted join will be chosen if the optimizer expects it to be more efficient than any alternative plan. For queries in which the only alternative is a cartesian product followed by a filter, the inverted join will likely result in a performance improvement. #55679
- The hybrid logical clock used to coordinate distributed operations now performs significantly better under high contention with many concurrent updates from remote nodes. #56708
- 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). #56860
- 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). #56860
- 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). #56860
- The optimizer now supports using a GIN index on `JSONB` or `ARRAY` columns for a wider variety of filter predicates. Previously, GIN index usage was only supported for simple predicates (e.g., `WHERE a @> '{"foo": "bar"}'`), but now more complicated predicates are supported by combining simple contains (`@>`) expressions with `AND` and `OR` (e.g., `WHERE a @> '{"foo": "bar"}' OR a @> '{"foo": "baz"}'`). A GIN index will be used if it is available on the filtered column and the optimizer expects it to be more efficient than any alternative plan. This may result in performance improvements for queries involving `JSONB` and `ARRAU` columns. #56732

### Doc updates

- Updated several Hello World tutorials to use `cockroach demo` as the backend and an external repository for code samples, including Go with pgx and GORM, Java with JDBC and Hibernate, and Python with psycopg2, SQLAlchemy, and Django. #9025,#8991,
- Updated the Production Checklist to recommend disabling Linux memory swapping to avoid over-allocating memory. #8979

### Contributors

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

- Adrian Popescu (first-time contributor)
- Alan Acosta (first-time contributor)
- ArjunM98 (first-time contributor)
- Artem Barger
- Azdim Zul Fahmi (first-time contributor)
- David Pacheco (first-time contributor)
- Erik Grinaker
- Gabriel Jaldon (first-time contributor)
- Jake Rote (first-time contributor)
- Joshua M. Clulow (first-time contributor)
- Marcin Knychała (first-time contributor)
- Max Neverov (first-time contributor)
- Miguel Novelo (first-time contributor)
- Ruixin Bao (first-time contributor)
- TAKAHASHI Yuto (first-time contributor)
- Tayo (first-time contributor)
- Tim Graham (first-time contributor)
- Tom Milligan (first-time contributor)
- Vaibhav
- alex-berger@gmx.ch (first-time contributor)
- alex.berger@nexiot.ch (first-time contributor)
- haseth (first-time contributor)
- hewei03 (first-time contributor)
- neeral
- xinyue (first-time contributor)