# What's New in v2.1

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

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

## v2.1.11

Release Date: January 29, 2020

### Admin UI changes

- CockroachDB v2.1.0 included security updates that inadvertently caused some Admin UI pages requiring table details to not display. These pages display properly once again. #44194

### Bug fixes

- Fixed panics caused by certain window functions that operate on tuples. #43118
- Prevented rare cases of infinite looping on database files written with a CockroachDB version earlier than v2.1.9. #43255
- `EXPLAIN` can now be used with statements that use `AS OF SYSTEM TIME`. #43306
- Fixed a panic when a log truncation took place concurrently with a replica being added to a Raft group. #43314
- Migrating the privileges on the `system.lease` table no longer creates a deadlock during a cluster upgrade. #43633

### Contributors

This release includes 6 merged PRs by 7 authors.

## v2.1.10

Release Date: December 16, 2019

### Security updates

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

  Note:

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

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

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

  Note:

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

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

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

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

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

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

Note:

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

### Admin UI changes

- Certain web UI pages (like the list of databases or tables) now restrict their content to match the privileges of the logged-in user. #42910
- The event log now presents all cluster settings changes, unredacted, when an admin user uses the page. #42910
- Customization of the UI by users is now only properly saved if the user has write privilege to `system.ui` (i.e., is an admin user). Also, all authenticated users share the same customizations. This is a known limitation and should be lifted in a future version. #42910
- Access to table statistics are temporarily blocked from access by non-admin users until further notice, for security reasons. #42910
- Certain debug pages have been blocked from non-admin users for security reasons. #42910

### Bug fixes

- Fixed a rare data corruption bug in RocksDB caused by newer Linux kernel's handling of i_generation on certain file systems. #41394
- Fixed a bug causing the `cluster_logical_timestamp()` function to sometimes return incorrect results. #41442
- Fixed a bug causing rapid network disconnections to lead to cluster unavailability because goroutines waited for a connection which would never be initialized to send its first heartbeat. #42166
- Fixed a case where we incorrectly determine that a query (or part of a query) which contains an `IS NULL` constraint on a unique index column returns at most one row, possibly ignoring a `LIMIT 1` clause. #42793
- `ALTER INDEX IF EXISTS` no longer fails when using an unqualified index name that does not match any existing index. Now it is a no-op. #42841
- The `CommandQueue` no longer holds on to buffers if they become too large. This prevents unbounded growth of memory that may never be reclaimed. #42961
- The `CommandQueue` now clears references to objects in its buffers to allow those objects to be reclaimed by the garbage collector. #42961
- Fixed a bug causing disk stalls to allow a node to continue heartbeating its liveness record and prevent other nodes from taking over its leases, despite being completely unresponsive. #41734

### Contributors

This release includes 8 merged PRs by 9 authors.

## v2.1.9

Release Date: September 23, 2019

### Bug fixes

- Fixed a bug that could lead to data inconsistencies and crashes with the message `consistency check failed with N inconsistent replicas`. #40353
- Fixed incorrect results, or "unordered span" errors, in some cases involving exclusive inequalities with non-numeric types. #38897
- Fixed a potential infinite loop in queries involving reverse scans. #39105
- Unary negatives in constant arithmetic expressions are no longer ignored. #39368
- Fix wrong comparator used in RocksDB compaction picker, which can lead to infinite compaction loop. #40752
- Fix bug where MVCC value at future timestamp is returned after a transaction restart. #40632
- Consider intents in a read's uncertainty interval to be uncertain just as if they were committed values. This removes the potential for stale reads when a causally dependent transaction runs into the not-yet resolved intents from a causal ancestor. #40632

### Contributors

This release includes 8 merged PRs by 4 authors.

## v2.1.8

Release Date: July 15, 2019

### Bug fixes

- Fixed crashes when trying to run certain `SHOW` commands via the pgwire prepare path. #37891
- Fixed a rare crash ("close of closed channel") that would occur when shutting down a server. #37893
- Previously, due to a bug when estimating result set sizes in the Optimizer, queries involving large `INT` ranges could result in poor plans being generated. #38039
- `NULL`s are now correctly handled by `MIN`, `SUM`, and `AVG` when used as window functions. #38357
- Prevented a possible missing row from queries that involved iterator reuse and seeking into the gap between stables bridged by a range tombstone. #37694

### Security improvements

- Only check `CommonName` on first certificate in file. #38166
- Stack memory used by CockroachDB is now marked as non-executable, improving security and compatibility with SELinux. #38134

### Contributors

This release includes 12 merged PRs by 11 authors.

## v2.1.7

Release Date: May 14, 2019

### Bug fixes

- Fixed a bug in `RESTORE` where some unusual range boundaries in interleaved tables caused an error. #36006
- CockroachDB now properly applies column width and nullability constraints on the result of conflict resolution in `UPSERT` and `INSERT ON CONFLICT`. #35373
- Subtracting `0` from a `JSONB` array now correctly removes its first element. #35619
- Fixed an on-disk inconsistency that could result from a crash during a range merge. #35752
- While a cluster is unavailable (e.g., during a network partition), memory and goroutines used for authenticating connections no longer leak when the client closes said connections. #36231
- Single column family `JSONB` columns are now decoded correctly. #36628
- Fixed a rare inconsistency that could occur on overloaded clusters. #36960
- Fixed a possible panic while recovering from a WAL on which a sync operation failed. #37214
- Reduced the risk of data unavailability during AZ/region failure. #37336

### Build changes

- CockroachDB will provisionally refuse to build with go 1.12, as this is known to produce incorrect code inside CockroachDB. #35639
- Release Docker images are now built on Debian 9.8. #35737

### Contributors

This release includes 15 merged PRs by 12 authors.

## v2.1.6

Release Date: March 11, 2019

### Bug fixes

- Fixed a panic when the subquery in `UPDATE SET (a,b) = (...subquery...)` returns no rows. #34805
- CockroachDB now only lists tables in `pg_catalog.pg_tables`, for compatibility with PostgreSQL. #34858
- Fixed a panic during some `UNION ALL` operations with projections, filters, or renders directly on top of the `UNION ALL`. #34913
- Fixed a planning bug that caused incorrect aggregation results on multi-node aggregations with implicit, partial orderings on the inputs to the aggregations. #35259

### Doc updates

- Added much more guidance on troubleshooting cluster setup and troubleshooting SQL behavior. #4223

### Contributors

This release includes 9 merged PRs by 7 authors.

## v2.1.5

Release Date: February 19, 2019

### Enterprise edition changes

- Added support for standard HTTP proxy environment variables in HTTP and S3 storage. #34535

### SQL language changes

- It is now possible to force a reverse scan of a specific index using `table@{FORCE_INDEX=index,DESC}`. #34121
- The value of `information_schema.columns.character_maximum_column` is set to `NULL` for all integer types, for compatibility with PostgreSQL. #34201

### Command-line changes

- Fixed a bug in `cockroach node status` that prevented it from displaying down nodes in the cluster in some circumstances. #34503

### Bug fixes

- Lookup joins now properly preserve their input order even if more than one row of the input corresponds to the same row of the lookup table. #33730
- Fixed a goroutine leak that would occur while a cluster was unavailable (or a subset of nodes partitioned away from the cluster) and would cause a resource spike to resolve. #34144
- Fixed panics or incorrect results in some cases when grouping on constant columns (either with `GROUP BY` or `DISTINCT ON`). #34168
- The values reported in `information_schema.columns` for integer columns created prior to CockroachDB v2.1 as `BIT` are now fixed and consistent with other integer types. #34201
- Fixed a bug that would delay Raft log truncations. #34284
- Prevented down-replicating widely replicated ranges when nodes in the cluster are temporarily down. #34199
- CockroachDB now enables re-starting a node at an address previously allocated for another node. #34198
- `CHANGEFEED`s now can be started on tables that have been backfilled by schema changes. #34362
- Fixed a back up in flow creation observed by "no inbound stream connection" caused by not releasing a lock before attempting a possibly blocking operation. #34364
- Fixed a panic when updating a job that doesn't exist. #34672
- Fixed a bug in `RESTORE` that prevented restoring some `BACKUP`s containing previously dropped or truncated interleaved tables. #34719
- The value of the `attnum` column in `pg_catalog.pg_attribute` now remains stable across column drops. #34734
- Prevented a problem that would cause the Raft log to grow very large, which in turn could prevent replication changes. #34774
- Prevented down nodes from obstructing Raft log truncation on ranges they are a member of. This problem could cause replication to fail due to an overly large Raft log. #34774
- Fixed a bug that would incorrectly cause JSON field access equality comparisons to be true when they should be false. #32214

### Performance improvements

- Index joins, lookup joins, foreign key checks, cascade scans, zig zag joins, and `UPSERT`s no longer needlessly scan over child interleaved tables when searching for keys. #33652

### Doc updates

- Updated the SQL Performance Best Practices with caveats around interleaving tables. #4273
- Added a note that when a table that was previously split is truncated, the table must be pre-split again. #4274
- Added guidance on removing `UNIQUE` constraints. #4276
- Added a warning about cross-store rebalancing not working as expected in 3-node clusters with multiple stores per node. #4320

### Contributors

This release includes 26 merged PRs by 12 authors.

## v2.1.4

Release Date: January 22, 2019

### Enterprise edition changes

- It is now possible to use AWS S3 temporary credentials for `BACKUP`/`RESTORE` and `IMPORT`/`EXPORT` using the `AWS_SESSION_TOKEN` parameter in the URL. #33046 }

### SQL language changes

- Added support for the `pg_catalog` introspection table `pg_am` for both PostgreSQL versions 9.5 and 9.6, which changed the table significantly. #33276
- Previously, CockroachDB did not consider the value of the right operand for `<<` and `>>` operators, resulting in potentially very large results and excessive RAM consumption. This has been fixed to restrict the range of these values to that supported for the left operand. #33247
- Attempts to use some PostgreSQL built-in functions that are not yet supported in CockroachDB now cause a clearer error message, and also become reported in diagnostics reporting, if diagnostics reporting is enabled, so as to gauge demand. #33427 }

### Bug fixes

- Fixed a bug where schema changes could get stuck for 5 minutes when executed immediately after a server restart. #33062
- Fixed a bug with returning dropped unique columns in `DELETE` statements with `RETURNING`. #33541
- `CHANGEFEED`s and incremental `BACKUP`s no longer indefinitely hang under an infrequent condition. #33141
- The output of `cockroach node status --ranges` previously listed the count of under-replicated ranges in the `ranges_unavailable` column and the number of unavailable ranges in the `ranges_underreplicated` column. This fixes that mix-up. #32951
- Fixed a possible goroutine leak when canceling queries. #33137
- Cancel requests (via the pgwire protocol) now close quickly with an EOF instead of hanging but still do not cancel the request. #33246
- Fixed pgwire binary decoding of decimal `NaN` and `NULL` values in arrays. #33306
- The `UPSERT` and `INSERT ON CONFLICT` statements now properly check that the user has the `SELECT` privilege on the target table. #33359
- CockroachDB does not crash upon running `SHOW SESSIONS`, `SHOW QUERIES`, and inspections of some `crdb_internal` tables when certain SQL sessions are issuing internal SQL queries. #33261
- CockroachDB no longer reports under-replicated ranges corresponding to replicas that are waiting to be deleted. #33407
- Fixed a panic that could result from not supplying a nullable column in an `INSERT ON CONFLICT ... DO UPDATE` statement. #33309
- Resolved a cluster degradation scenario that could occur during `IMPORT`/`RESTORE` operations, which manifested through a high number of pending Raft snapshots. #33587
- Fixed a panic caused by some queries involving lookup joins where an input ordering must be preserved. #33522
- Prevented a panic with certain queries that use the statement source (square bracket) syntax. #33723
- Window functions with non-empty `PARTITION BY` and `ORDER BY` clauses are now handled correctly when invoked via an external driver. #33671

### Performance improvements

- Improved the execution plans of some queries using `LIKE`. #33072

### Doc updates

- The new Life of a Distributed Transaction details the path that a query takes through CockroachDB's architecture, starting with a SQL client and progressing all the way to RocksDB (and then back out again). #4281
- Updated the Production Checklist with more current hardware recommendations and additional guidance on storage, file systems, and clock synch. #4153
- Expanded the SQLAlchemy tutorial to provide code for transaction retries and best practices for using SQLAlchemy with CockroachDB. #4142

### Contributors

This release includes 33 merged PRs by 17 authors. We would especially like to thank first-time contributor shakeelrao.

## v2.1.3

Release Date: December 17, 2018

### Enterprise edition changes

- Added timeseries metrics for debugging `CHANGEFEED` performance issues. #32872

### Bug fixes

- Resolved a cluster degradation scenario that could occur during `IMPORT`/`RESTORE` operations, which manifested through a high number of pending Raft snapshots. #33015
- Fixed a bug that could cause under-replication or unavailability in 5-node clusters and those using high replication factors. #33047
- Fixed an infinite loop in a low-level scanning routine that could be hit in unusual circumstances. #33065

### Build changes

- `ncurses` is now linked statically so that the `cockroach` binary no longer requires a particular version of the `ncurses` shared library to be available on deployment machines. #32960

### Contributors

This release includes 10 merged PRs by 8 authors.

## v2.1.2

Release Date: December 10, 2018

### Backward-incompatible changes

- The `CHANGEFEED` `experimental-avro` option has been renamed `experimental_avro`. #32235

### SQL language changes

- The `IMPORT format (file)` syntax is deprecated in favor of `IMPORT format file`. Similarly, `IMPORT TABLE ... FROM format (file)` is deprecated in favor of `IMPORT TABLE ... FROM format file`. #31301
- CockroachDB now accepts ordinary string values for placeholders of type `BPCHAR`, for compatibility with PostgreSQL clients that use them. #32661

### Command-line changes

- The `cockroach workload` command now includes the `kv` load generator. #32756

### Bug fixes

- Fixed a panic on `UPDATE ... RETURNING *` during a schema change. #32591
- Fixed a panic on `UPSERT` in the middle of a schema change adding a non-nullable column. #32730
- Fixed a bug that prevents adding computed columns with the `NOT NULL` constraint. #32730
- Fixed a deadlock when using `ALTER TABLE ... VALIDATE CONSTRAINT` in a transaction with a schema change. #32850
- Prevented a performance degradation related to overly aggressive Raft log truncations that could occur during `RESTORE` or `IMPORT` operations.
- Prevented a stall in the processing of Raft snapshots when many snapshots are requested at the same time. #32414
- `CHANGEFEED`s now escape Kafka topic names, when necessary. #32235
- `CHANGEFEED`s now spend dramatically less time flushing Kafka writes. #32235
- `CHANGEFEED`s with the `experimental_avro` option now work with column `WIDTH`s and `PRECISION`s. #32484
- Fixed a bug where Raft proposals could get stuck if forwarded to a leader who could not itself append a new entry to its log. #32600
- Fixed a bug where calling `CREATE STATISTICS` on a large table could cause the server to crash due to running out of memory. #32635
- Fixed a bug that could cause data loss bug when a disk becomes temporarily full. #32633
- CockroachDB now reports an unimplemented error when a `WHERE` clause is used after `INSERT ... ON CONFLICT`. #32558
- CockroachDB now properly handles foreign key cascading actions `SET DEFAULT` and `SET NULL` in `SHOW CREATE` and `cockroach dump`. #32630
- Fixed a crash that could occur during or after a data import on Windows. #32666
- Lookup joins now properly preserve ordering for outer joins. Previously, under specific conditions, `LEFT JOIN` queries could produce results that did not respect the `ORDER BY` clause. #32678
- CockroachDB once again enables `admin` users, including `root`, to list all user sessions besides their own. #32709
- CockroachDB now properly rejects queries that use an invalid function (e.g., an aggregation) in the `SET` clause of an `UPDATE` statement. #32506
- Dates no longer have a time component in their text encoding over the wire. #32661
- Corrected binary decimal encoding for `NaN`. #32661
- Prevented a panic when encountering an internal error related to invalid entries in the output of `SHOW SESSIONS`. #32742
- Prevented a panic when running certain subqueries that get planned in a distributed fashion. #32670
- `CHANGEFEED`s emitting into Kafka more quickly notice new partitions. #32757
- CockroachDB now properly records statistics for sessions where the value of `application_name` is given by the client during initialization instead of `SET`. #32755
- CockroachDB now properly evaluates `CHECK` constraints after a row conflict in `INSERT ... ON CONFLICT` when the `CHECK` constraint depends on a column not assigned by `DO UPDATE SET`. #32780
- The `cockroach workload run` subcommand no longer applies to data-only generators. #32827
- Fixed a bug where metadata about contended keys was inadvertently ignored, in rare cases allowing for a failure in transaction cycle detection and transaction deadlocks. #32853

### Performance improvements

- Changed the default value for the `kv.transaction.write_pipelining_max_batch_size` cluster setting to `128`. This speeds up bulk write operations. #32621

### Doc updates

- Documented the `cockroach workload` command, which provides built-in load generators for simulating different types of client workloads, and updated various tutorials to use these workloads. #4087
- Expanded the `cockroach demo` documentation to explain the use of built-in datasets. #4087
- Added a secure version of the Performance Tuning tutorial. #4123
- Clarified that primary key columns cannot be stored with a secondary index. #4098
- Clarified when to use `DELETE` vs. `TRUNCATE`. #4094
- Added important considerations when setting up clock synchronization.
- Clarified the documentation on automatic transaction retries. #4044

### Contributors

This release includes 41 merged PRs by 15 authors.

## v2.1.1

Release Date: November 19, 2018

### SQL language changes

- Renamed the first column name returned by `SHOW STATISTICS` to `statistics_name`. #32045
- CockroachDB now de-correlates and successfully executes many queries containing correlated `EXISTS` subqueries. Previously, these queries caused a de-correlation error. #32026
- If diagnostics reporting is enabled, attempts to use `CREATE/DROP SCHEMA`, `DEFERRABLE`, `CREATE TABLE (LIKE ...)`, `CREATE TABLE ... WITH`, and the "fetch limit" parameter (e.g., via JDBC) will now be collected as telemetry to gauge demand for these currently unsupported features. Also, the name of SQL built-in functions will be collected upon evaluation errors. #31638

### Bug fixes

- Fixed a small memory leak when running distributed queries. #31759
- The `confkey` column of `pg_catalog.pg_constraint` no longer includes columns that were not involved in the foreign key reference. #31895
- The cost-based optimizer no longer chooses the wrong index for a scan because of incorrect selectivity estimation. #32011
- Fixed a bug that caused transactions to unnecessarily return a "too large" error. #31821
- Fixed rare deadlocks during `IMPORT`, `RESTORE`, and `BACKUP`. #32016
- Fixed a panic caused by incorrectly encoded Azure credentials. #32016
- Fixed a bug in the cost-based optimizer that sometimes prevented passing ordering requirements through aggregations. #32089
- Fixed a bug that sometimes caused invalid results or an "incorrectly ordered stream" error with streaming aggregations. #32097
- Fixed a bug that caused some queries with `DISTINCT ON` and `ORDER BY` with descending columns to return an error incorrectly. #32175
- Fixed a bug that caused queries with `GROUP BY` or `DISTINCT ON` to return incorrect results or an "incorrectly ordered stream" error. Also improved performance of some aggregations by utilizing streaming aggregation in more cases. #32175
- Fixed a panic caused by an incorrect assumption in the SQL optimizer code that `ROWS FROM` clauses contain only functions. #32168
- Fix an error returned by `cockroach node status` after a new node is added to the cluster at a previous node's address. #32198
- Fixed a mismatch between lookup join planning and execution, which could cause queries to fail with the error "X lookup columns specified, expecting at most Y". #31896
- Fixed a bug that caused transactions to appear partially committed. CockroachDB was sometimes claiming to have failed to commit a transaction when some (or all) of its writes were actually persisted. #32220
- Prevented long stalls that can occur in contended transactions. #32217
- Non-superusers can no longer see other users' sessions and queries via the `ListSessions` and `ListLocalSessions` status server API methods. #32284
- The graphite metrics sender now collects and sends only the latest data point instead of all data points since startup. #31888

### Performance improvements

- Improved the performance of `AS OF SYSTEM TIME` queries by letting them use the table descriptor cache. #31756
- The cost-based optimizer can now determine more keys in certain cases involving unique indexes, potentially resulting in better plans. #32044
- Within a transaction, when performing a schema change after the table descriptor has been modified, accessing the descriptor should be faster. #31756

### Doc updates

- Corrected the flow control logic of the transaction code sample in the Build a Java App with CockroachDB tutorial. #4047
- Expanded the Running in a DaemonSet instruction to cover both insecure and secure deployments. #4037
- Made it easier to find and link to specific installation methods, and updated the Homebrew instructions to note potential conflicts in cases where CockroachDB was previously installed using a different method. #4032, #4036
- Updated the `IMPORT` documentation to cover importing CockroachDB dump files. #4029

### Contributors

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

- Vijay Karthik
- neeral

## v2.1.0

Release Date: October 30, 2018

With the release of CockroachDB v2.1, we’ve made it easier than ever to migrate from MySQL and Postgres, improved our scalability on transactional workloads by 5x, enhanced our troubleshooting workflows in the Admin UI, and launched a managed offering to help teams deploy low-latency, multi-region clusters with minimal operator overhead.

- Check out a summary of the most significant user-facing changes.
- Then upgrade to CockroachDB v2.1.

### Summary

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

- Managed Offering
- Enterprise Features
- Core Features
- Known Limitations
- Documentation

### Managed Offering

The Managed CockroachDB offering is currently in Limited Availability and accepting customers on a qualified basis. The offering provides a running CockroachDB cluster suitable to your needs, fully managed by Cockroach Labs on GCP or AWS. Benefits include:

- No provisioning or deployment efforts for you
- Daily full backups and hourly incremental backups of your data
- Upgrades to the latest stable release of CockroachDB
- Monitoring to provide SLA-level support

For more details, see the Managed CockroachDB docs.

### Enterprise Features

These new features require an enterprise license. Register for a 30-day trial license here.

Feature | Description
--- | ---
Change Data Capture (Beta) | Change data capture (CDC) provides efficient, distributed, row-level change feeds into Apache Kafka for downstream processing such as reporting, caching, or full-text indexing. Use the `CREATE CHANGEFEED` statement to create a new changefeed, which provides row-level change subscriptions.
Encryption at Rest (Experimental) | Encryption at Rest provides transparent encryption of a node's data on the local disk.
`EXPORT` (Beta) | The `EXPORT` statement exports tabular data or the results of arbitrary SELECT statements to CSV files. Using the CockroachDB distributed execution engine, `EXPORT` parallelizes CSV creation across all nodes in the cluster, making it possible to quickly get large sets of data out of CockroachDB in a format that can be ingested by downstream systems.

### Core Features

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

### SQL

Feature | Description
--- | ---
`ALTER TABLE ... ALTER TYPE` | The `ALTER TABLE ... ALTER TYPE` statement changes a column's data type. Only type changes that neither require data checks nor data conversion are supported at this time.
`ALTER COLUMN ... DROP STORED` | The `ALTER TABLE ... ALTER COLUMN ... DROP STORED` statement converts a stored, computed column into a regular column.
`CANCEL JOB` | The `CANCEL JOB` statement can now be executed on long-running schema change jobs, causing them to terminate early and roll back. Also, the `CANCEL JOBS` variant of the statement lets you cancel multiple jobs at once.
`CANCEL QUERIES` | The `CANCEL QUERIES` variant of the `CANCEL QUERY` statement lets you cancel multiple queries at once.
`CANCEL SESSIONS` | The `CANCEL SESSIONS` variant of the `CANCEL SESSION` statement lets you stop multiple long-running sessions. `CANCEL SESSION` will attempt to cancel the currently active query and end the session.
Cost-Based Optimizer | The cost-based optimizer seeks the lowest cost for a query, usually related to time. In versions 2.1 and later, CockroachDB's cost-based optimizer is enabled by default.
`CREATE STATISTICS` (Experimental) | The `CREATE STATISTICS` statement generates table statistics for the cost-based optimizer to use.
`EXPLAIN (DISTSQL)` | The `DISTSQL` option generates a physical query plan for a query. Query plans provide information around SQL execution, which can be used to troubleshoot slow queries.
`EXPLAIN ANALYZE` | The `EXPLAIN ANALYZE` statement executes a SQL query and returns a physical query plan with execution statistics.
Fast Deletes for Interleaved Tables | Under certain conditions, deleting rows from interleave tables that use `ON DELETE CASCADE` will use an optimized code path and run much faster.
Lookup Joins (Experimental) | A lookup join is beneficial to use when there is a large imbalance in size between the two tables, as it only reads the smaller table and then looks up matches in the larger table. A lookup join requires that the right-hand (i.e., larger) table is indexed on the equality column.
`public` Role | All users now belong to the `public` role, to which you can grant and revoke privileges.
`SET` (session variable) `SHOW` (session variable) | Added the following options: `statement_timeout`: The amount of time a statement can run before being stopped. `optimizer`: The mode in which a query execution plan is generated. If set to `on`, the cost-based optimizer is enabled by default and the heuristic planner will only be used if the query is not supported by the cost-based optimizer; if set to `off`, all queries are run through the legacy heuristic planner.
`SHOW STATISTICS` (Experimental) | The `SHOW STATISTICS` statement lists table statistics used by the cost-based optimizer.
`SNAPSHOT` isolation level | Removed. Transactions that request to use `SNAPSHOT` are now mapped to `SERIALIZABLE`.
Subquery Support | CockroachDB's cost-based optimizer supports several common types of correlated subqueries. A subquery is said to be "correlated" when it uses table or column names defined in the surrounding query.

### CLI

Feature | Description
--- | ---
`cockroach demo` | The `cockroach demo` command starts a temporary, in-memory, single-node CockroachDB cluster and opens an interactive SQL shell to it.
`cockroach start` | The new `--advertise-addr` flag recognizes both a hostname/address and port and replaces the `--advertise-host` and `--advertise-port` flags, which are now deprecated. The new `--listen-addr` flag recognizes both a hostname/address and port and replaces the `--host` and `--port` flags, which are now deprecated for `cockroach start` but remain valid for other client commands. The new `--http-addr` flag recognizes both a hostname/address and port and replaces the `--http-host` flag, which is now deprecated.
`cockroach sql` | The `cockroach sql` command and other client commands that display SQL results now use the new table result formatter by default, replacing the previous formatter called `pretty`. This provides more compact and more reusable results.
`cockroach zone` | Deprecated. The `cockroach zone` command has been deprecated. To manage replication zones, use the `CONFIGURE ZONE` statement to add, modify, reset, and remove replication zones.

### Operations

Feature | Description
--- | ---
Controlling Leaseholder Location | Using replication zones, you can now specify preferences for where a range's leaseholders should be placed to increase performance in some scenarios.
DBeaver Support | DBeaver, a cross-platform database GUI, has been thoroughly vetted and tested with CockroachDB v2.1.
Load-based Rebalancing | In addition to the rebalancing that occurs when nodes join or leave a cluster, leases and replicas are rebalanced automatically based on the relative load across the nodes within a cluster. Note that depending on the needs of your deployment, you can exercise additional control over the location of leases and replicas by configuring replication zones.
Migration from Postgres and MySQL | The `IMPORT` command now supports importing dump files from Postgres and MySQL.
Monitoring Kubernetes Deployments | Kubernetes tutorials now feature steps on how to integrate withPrometheus, an open source tool for storing, aggregating, and querying timeseries data, and set up Alertmanager.
Multi-Cluster Kubernetes Deployments | You can now orchestrate a secure CockroachDB deployment across three Kubernetes clusters, each in a different geographic region, using the StatefulSet feature to manage the containers within each cluster and linking them together via DNS.
Pipelining of Transactional Writes | Transactional writes are pipelined when being replicated and when being written to disk, dramatically reducing the latency of transactions that perform multiple writes.
Preferring Local Networks | The new `--locality-advertise-addr` flag on `cockroach start` can be used to tell nodes in specific localities to prefer local or private interfaces. This flag is useful when running a cluster across multiple networks, where nodes in a given network have access to a private or local interface while nodes outside the network do not.
Rolling Upgrade Auto-finalization | By default, as soon as all nodes are running CockroachDB v2.1, the upgrade process will be auto-finalized. This will enable certain performance improvements and bug fixes introduced in the new version.
Viewing Node Status for an Unavailable Cluster | The `cockroach node status` command can now be run even when majority of nodes are down. Running the command now shows an additional field: `is_available.`

### Admin UI

Feature | Description
--- | ---
Advanced Debugging Page (Experimental) | The Advanced Debugging page provides links to advanced monitoring and troubleshooting reports and cluster configuration details.
Hardware Dashboard | The Hardware dashboard lets you monitor CPU usage, disk throughput, network traffic, storage capacity, and memory.
Statements page | The Statements page helps you identify frequently executed or high latency SQL statements. It also allows you to view the details of SQL statement fingerprints, which are groupings of similar SQL statements with literal values replaced by underscores.
User Authentication | As of v2.1, users must have a username and password to access the Admin UI in a secure cluster.

### Known Limitations

For information about limitations we've identified in CockroachDB v2.1, with suggested workarounds where applicable, see Known Limitations.

### Documentation

Topic | Description
--- | ---
Experimental Features | This new page lists the experimental features that are available in CockroachDB v2.1.
Client Connection Parameters | This new page describes the parameters used to establish a client connection. The client connection parameters determine which CockroachDB cluster they connect to, and how to establish this network connection.
Deploying CockroachDB with `systemd` | The on-premises and cloud deployment tutorials now include instructions for using `systemd` to start the nodes of a cluster.
Manual and Automated Backups | This page has been updated to provide both manual and automated backup guidance.
Migration Guide | This new guide provides an overview of migrating to CockroachDB, as well as specific instructions for migrating from Postgres, migrating from MySQL, and migrating from CSV.
Networking Guidance | The Production Checklist now provides a detailed explanation of network flags and scenarios.
Online Schema Changes | This new page explains how CockroachDB updates table schema without imposing any downtown or negative consequences on applications.
Performance Benchmarking | This page walks you through TPC-C performance benchmarking on CockroachDB. It measures tpmC (new order transactions/minute) on two TPC-C datasets: 1,000 warehouses (for a total dataset size of 200GB) on 3 nodes and 10,000 warehouses (for a total dataset size of 2TB) on 30 nodes.
Performance Tuning | This new tutorial shows you essential techniques for getting fast reads and writes in CockroachDB, starting with a single-region deployment and expanding into multiple regions.
Secure "Build an App" | Most client driver and ORM tutorials now provide code samples and guidance for secure clusters.
Serializable Transactions | This new tutorial goes through a hypothetical scenario that demonstrates the importance of `SERIALIZABLE` isolation for data correctness.
Window Functions | This new page provides information about window function support in CockroachDB.

## v2.1.0-rc.2

Release Date: October 25, 2018

### Docker Image

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

### Bug Fixes

- Fixed a panic when setting some `kv.bulk_io_write` cluster settings to a value < 1. #31603
- Fixed a bug where entry application on Raft followers could fall behind entry application on the leader, causing stalls during splits. #31619
- Fixed a panic caused by an incorrect assumption in the SQL optimizer code that `ROWS FROM` clauses contain only functions. #31769
- Fixed a bug causing committed read-only transactions to be counted as aborted in metrics. #31608
- Fixed a bug where `CHANGEFEED`s may not correctly retry temporary errors when communicating with a sink. #31559

### Contributors

This release includes 18 merged PRs by 9 authors.

## v2.1.0-rc.1

Release Date: October 22, 2018

### Docker Image

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

### Enterprise edition changes

- `CHANGEFEED`s can now be configured with a minimum duration between emitted resolved timestamps. #31008
- `CHANGEFEED`s now have limited and experimental support for the `AVRO` format. #31143
- `CHANGEFEED`s now continue running when watched tables are `ALTER`ed in ways that require a backfill. #31165

### SQL language changes

- `EXPLAIN` now always shows filter and join conditions. #31186
- CockroachDB now supports CTEs inside views. #31051
- CockroachDB now hints that internal errors should be reported as bugs by users. Additionally, internal errors are now collected internally and submitted (anonymized) with other node statistics when statistic collection is enabled. #31272
- It is now possible to force a specific index for `DELETE` or `UPDATE`. #31279
- Handle binary fields dumped by `mysqldump v5.7.23` with `_binary` prefix. #31305
- `EXPLAIN ANALYZE <statement>` is now a valid equivalent of `EXPLAIN ANALYZE (DISTSQL) <statement>` #31278
- When a query references a table in `information_schema` and `pg_catalog` that is not yet implemented, this will be reported as telemetry if statistics reporting is enabled. This will help determine which features should be implemented next for compatibility. #31357

### Admin UI changes

- The Service latency: {90,99}th percentile graphs on the Overview and SQL dashboards, as well as the P50 and P99 latency numbers in the time series area sidebar, now reflect latencies of both local and distributed queries. Previously, they only included local queries. #31116
- Links to documentation pages now open in a new tab. #31132
- Improved the view of databases with no tables. #31231
- Updated Jobs dashboard to make each row expandable, allowing the user to see the error message for failed jobs. #31237

### Bug fixes

- Fixed schema change rollback caused by GC TTL threshold error. #31153
- Fixed the `_admin/v1/enqueue_range` debug endpoint to always respect its `node_id` parameter. #31087
- CockroachDB now reports an unimplemented error when a common table expression containing `INSERT`/`UPDATE`/`UPSERT`/`DELETE` is not otherwise used in the remainder of the query. #31051
- CockroachDB does not silently ignore `WITH` clauses within parentheses anymore. #31051
- Fixed a rare scenario where a backup could incorrectly include a key for an aborted transaction. #31316
- CockroachDB now avoids repeatedly trying a replica that was found to be in the process of being added. #31250
- CockroachDB will no longer fail in unexpected ways or write invalid data when the type of input values provided to `INSERT`/`UPSERT` does not match the type of the target columns. #31280
- `UPDATE` now verifies the column constraints before `CHECK` constraints, for compatibility with PostgreSQL. #31280
- It is no longer possible to use not-fully-added-yet columns in the `RETURNING` clause of `UPDATE` statements. #31280
- CockroachDB no longer (incorrectly and silently) accepts a computed column on the left side of the assignment in an `ON CONFLICT` clause. #31280
- CockroachDB no longer (incorrectly and silently) accepts a not-fully-added-yet column on the left side of the assignment in an `ON CONFLICT` clause. #31280
- CockroachDB no longer (incorrectly and silently) ignores the `HAVING` clause on `SELECT` without `FROM`. #31347
- The Range Debug page now handle cases in which there is no lease start or expiration time. #31367

### Build changes

- CockroachDB can now be built from source on macOS 10.14 (Mojave). #31308

### Doc Updates

- Updated the documentation for encryption at rest. #3848
- Updated the documentation on how to orchestrate CockroachDB across multiple Kubernetes clusters. #3845 #3847
- Updated the documentation on the cost-based optimizer. #3784
- Added documentation for fast path deletes for interleaved tables. #3834

### Contributors

This release includes 55 merged PRs by 21 authors.

## v2.1.0-beta.20181015

Release Date: October 15, 2018

### SQL language changes

- The output of `SHOW JOBS` now reports ongoing jobs first in start time order, followed by completed jobs in finished time order. #31005
- CockroachDB now supports more customizations from PostgreSQL client drivers when initially setting up the client connection. #31021
- Columns that are part of a table's `PRIMARY KEY` can no longer be specified as `STORING` columns in secondary indexes on the table. #31032
- The output of `SHOW ZONE CONFIGURATIONS` and `SHOW ZONE CONFIGURATION FOR` now only shows the zone name and the SQL representation of the config. #31089

### Command-line changes

- It is now possible to provide initial/default values for any customizable session variable in the client connection URL. #31021

### Admin UI changes

- Leveraged PopperJS positioning engine to automate the positioning of tooltips. #30476
- Added a graph of the average QPS per store to the Replication dashboard. Note that this uses an exponentially weighted moving average, not an instantaneous measurement. It is primarily of interest because it's the data that's used when making load-based rebalancing decisions. #30889
- Added a bar chart to the memory and capacity usage columns on the Node List. These columns sort by percentage used. #31070
- Added a debug page with a form that lets users manually enqueue a range in one of the various store-level replica queues on a specified store. This feature is intended for advanced users only. #31092

### Bug fixes

- Lookup joins no longer omit rows in certain circumstances during limit queries. #30836
- Fixed a panic due to malformed placeholder values. #30860
- The `cockroach start` command now prints a hint about waiting for a join or `cockroach init` only when starting nodes for a new cluster, not when adding nodes to an existing cluster. #30953
- Fixed a possible crash when using filters with `<tuple> IN <tuples>` expressions. #30968
- Prevented an edge case in load-based rebalancing where the cluster could transfer the lease for a range to a replica that isn't keeping up with the other replicas, causing brief periods where no replicas think they're leaseholder for the range and thus no requests can be processed for the range. #30972
- CockroachDB now properly ignores non-alphanumeric characters in encoding names passed to functions like `convert_from()` and `client_encoding()`, for compatibility with PostgreSQL. #31021
- CockroachDB now properly recognizes the value of `extra_float_digits` provided by clients as a connection parameter. #31021
- CockroachDB now properly recognizes two-part values for the `DateStyle` session variable and connection parameter, for compatibility with PostgreSQL. #31021
- CockroachDB now reports all server status parameters supported by PostgreSQL when setting up a session. This is expected to improve compatibility with some drivers. #31021
- CockroachDB now properly uses the client-provided default values when using the `RESET` statement (or `SET ... = DEFAULT`). #31021
- CockroachDB now properly fills the columns `boot_val` and `reset_val` in `pg_catalog.pg_settings`, for better compatibility with PostgreSQL. #31021
- CockroachDB now properly supports renaming a column that's also stored in an index. #31074
- During password login, "user does not exist" and "invalid password" cases now produce the same error message. #30935

### Performance improvements

- CockroachDB now avoids acquiring an exclusive lock when checking replica status in the write proposal path. #30920

### Doc updates

- Added a tutorial demonstrating essential performance tuning tutorial techniques for getting fast reads and writes in CockroachDB, starting with a single-region deployment and expanding into multiple regions. #3854
- Added a tutorial demonstrating the importances of serializable transactions. #3844
- Added documentation on index name resolution. #3830.
- Updated the documentation on set-returning functions (SRFs). #3810
- Update the example on how auto-incrementing is not always sequential. #3832

### Contributors

This release includes 40 merged PRs by 19 authors.

## v2.1.0-beta.20181008

Release Date: October 8, 2018

### SQL language changes

- `EXECUTE` is no longer an explainable statement. As an alternative, it is possible to `PREPARE ... AS EXPLAIN ...` and then execute the prepared statement to see the plan for a prepared query. #30725

### Admin UI changes

- Removed read and write graphs from the Hardware Dashboard. #30655

### Bug fixes

- `EXPLAIN ALTER DATABASE ... RENAME` no longer renames the target database. #30661
- `EXPLAIN ALTER TABLE ... RENAME` no longer renames the target table. #30661
- `EXPLAIN ALTER TABLE ... RENAME COLUMN` no longer renames the target column. #30661
- `EXPLAIN ALTER INDEX ... RENAME` no longer renames the target index. #30661
- It is once again possible to use `EXPLAIN` for all preparable statements, and prepare all explainable statements. #30661
- `TRUNCATE` is now properly restricted in SQL transactions like other DDL statements. #30661
- `TRUNCATE` can now be used with `EXPLAIN` and as a prepared statement. #30661
- The default unit for converting a string value when setting the `statement_timeout` session variable is now milliseconds for compatibility with PostgreSQL. #30654

### Doc updates

- Added a Migration Overview and specific guides for Migrating from Postgres, Migrating from MySQL, and Migrating from CSV. #3766
- Called out performance-optimized configuration files for Kubernetes single-cluster deployments. #3827 #3838
- Documented how replication zones affect secondary indexes. #3818
- Clarified that per-replica constraints in replication zones do not need to add up to total replicas. #3812
- Clarified a known limitation about schema changes inside transactions. #3814
- Updated the `ARRAY` documentation to cover casting from array to `STRING` values. #3813
- Documented the use of `--locality` when using `cockroach gen haproxy` to generate an HAProxy config file. #3809
- Updated the session variables documentation. #3799
- Updated the list of information included in a `debug zip`. #3796

### Contributors

This release includes 31 merged PRs by 15 authors.

## v2.1.0-beta.20181001

Release Date: October 1, 2018

### Security bug fix

- Fixed a vulnerability in which TLS certificates were not validated correctly for internal RPC interfaces. This vulnerability could allow an unauthenticated user with network access to read and write to the cluster. #30821

### SQL language changes

- The entries in the `replicas` column of the `crdb_internal.ranges` virtual table are now always sorted by store ID.
- The `EXPERIMENTAL_RELOCATE` statement no longer temporarily increases the number of replicas in a range more than one above the range's replication factor, preventing rare edge cases of unavailability.

### Command-line changes

- The `--log-dir`, `--log-dir-max-size`, `--log-file-max-size`, and `--log-file-verbosity` flags are now only available for the `cockroach start` and `cockroach demo` commands. Previously, these flags were available for other commands but rarely used or functional. #30341

### Admin UI changes

- The new SQL Query Errors graph on the SQL dashboard shows the number of queries that returned a runtime or execution error. #30371
- Hovering over a truncated entry in the Events panel now shows the full description of the event. #30391

### Bug fixes

- The `cockroach demo` command now runs with replication disabled. #30517
- The Jobs page now sorts by Creation Time by default instead of by User. #30428
- Fixed a panic in the optimizer code when generator functions such as `generate_series()` are used as the argument to an aggregate function. #30362
- Corrected the help text for `EXPORT`. #30425
- Ignored more unsupported clauses in `IMPORT ... PGDUMP`. #30425
- Fixed `IMPORT` of empty or small tables under rare conditions. #30425
- Fixed a panic when generator functions such as `unnest()` are used in the `SELECT` list with `GROUP BY`. #30462
- Fixed a panic caused by columns being reordered when using `UPSERT` with a `RETURNING` clause. #30467
- Fixed a panic when a correlated subquery in the `WHERE` clause contains an aggregate function referencing the outer query. This now causes an error since aggregates are not allowed in `WHERE`. #30522
- Corrected the list of permitted values printed when a non-permitted value is set for the `distsql` session variable. #30631

### Performance improvements

- Removed unnecessary synchronous disk writes caused by erroneous logic in the Raft implementation. #30459
- Range replicas are now automatically rebalanced throughout the cluster to even out the amount of QPS being handled by each node by default. Previously, this was available as a cluster setting but was not the default behavior. #30649

### Contributors

This release includes 56 merged PRs by 19 authors.

## v2.1.0-beta.20180924

Release Date: September 24, 2018

### SQL language changes

- `CHANGEFEED`s will retry, rather than abort, in certain cases when failing to emit to a sink. #30157
- The new `ALTER ... CONFIGURE ZONE` statement can be used to add, modify, reset, and remove replication zones, with support for placeholders (`$1`, etc.) and for multiple executions. The new `SHOW ZONE CONFIGURATION` statement can be used to view existing replication zones. Clients should use these SQL statements instead of the `cockroach zone` sub-commands, which are now deprecated and will be removed in a future version of CockroachDB. #30173
- Added the `2.0` value for both the `distsql` session setting and the `sql.defaults.distsql` cluster setting, which instructs the database to use the 2.0 `auto` behavior for determining whether queries are distributed or run through the gateway node. #30209

### Command-line changes

- The various `cockroach zone` sub-commands are now deprecated and will be removed in a future version of CockroachDB. Clients should use the SQL interface instead via `SHOW ZONE CONFIGURATION` and `ALTER ... CONFIGURE ZONE`. #30173
- Improved the output of `cockroach node status` to include separate `is_available` and `is_live` columns. #30268
- The `cockroach debug zip` command now also collects heap profiles that were generated and stored when there was high memory usage #30281

### Bug fixes

- The `ON DELETE CASCADE` and `ON UPDATE CASCADE` foreign key actions no longer cascade through `NULL`s. #30122
- Fixed the evaluation of `<tuple> IS NOT NULL` and `<tuple> IS NULL` comparison operations involving a non-null constant tuple to return `true` or `false` rather than `NULL`. #30184
- Fixed the occasional improper processing of the `WITH` operand with `IMPORT`/`EXPORT`/`BACKUP`/`RESTORE` and common table expressions. #30198
- Fixed the return type of an array built from the results of a subquery to be `elementType[]` rather than `tuple{elementType}[]`. #30237
- Fixed a panic that was occurring when the cost-based optimizer was disabled and an array built from the results of a subquery was used in the `WHERE` clause of an outer query. #30237
- Fixed a panic that occurred when not all values were present in a composite foreign key. #30153
- Transaction size limit errors are no longer returned for transactions that have already committed. #30304

### Performance improvements

- Avoided unnecessary allocations when parsing prepared statement placeholders. #30299
- 1PC transactions now avoid writing transaction record and intents when pushed due to reads at a higher timestamp. #30298

### Contributors

This release includes 46 merged PRs by 20 authors.

## v2.1.0-beta.20180917

Release Date: September 17, 2018

### Backward-incompatible changes

- `CHANGEFEED`s created with previous betas and alphas will not work with this version. #29559
- The experimental, non-recommended `kv.allocator.stat_based_rebalancing.enabled` and `kv.allocator.stat_rebalance_threshold` cluster settings have been replaced by an improved approach to load-based rebalancing that can be controlled via the new `kv.allocator.load_based_rebalancing` cluster setting. By default, leases will be rebalanced within a cluster to achieve better QPS balance. #29663

### SQL language changes

- Renamed the `EXPERIMENTAL_OPT` session setting to `OPTIMIZER`. The default value is `ON`, as before. #29530
- Special characters, such as newlines, are now formatted using `octal`, instead of `hex`, for compatibility with PostgreSQL. #29593

### Command-line changes

- All `cockroach` client sub-commands (except for `cockroach workload`) now support the `--url` flag. #29621
- Removed `--log-backtrace-at` and `--verbosity` flags, which were documented as being only useful by CockroachDB developers yet never actually used by CockroachDB developers. #30092

### Admin UI changes

- Long table rows now wrap, when necessary. #29551
- Diagnostics requests are now proxied through Cockroach Labs to prevent exposing user IP addresses. #29194
- Added attributes to the login form to allow LastPass to properly recognize it. #29561
- Custom and regular charts now have the same width. #30083
- Improved the UX of the Custom Chart page, and added the ability to configure multiple independent charts. #30118
- Improved the design and accessibility of tooltips. #30115
- Various improvements to the Statements pages. #30115
  - Simplified and cleaned-up the appearance.
  - Added statement retries.
  - Right-aligned all numeric stats.
  - Added more tooltips, including for the By Gateway Node table on the Statement Details page.
  - Improved tooltips by adding a legend detailing the parts of the bar chart.
  - Highlighted summary rows.
  - Improved table headers.
  - Reordered tables to highlight the most useful data.
  - Widened bar charts.
  - Summarized `SET` statements.
  - When a statement fingerprint has sometimes failed, used the optimizer, or been distributed, the number of matching executions is now shown.

### Bug fixes

- Fixed a bug that would allow the cluster summary text in the Admin UI to overflow its space. #29548
- Corrected the behavior of `INSERT INTO t DEFAULT VALUES` when there are active schema changes. #29496
- Fixed a race condition in `IMPORT` with a column that was a collated string. #29386
- Fixed crash caused by certain kinds of `UPSERT ... RETURNING` statements on tables with active schema changes. #29543
- `CHANGEFEED`s now error when a watched table backfills (instead of undefined behavior). #29559
- Fixed a panic that occurs when verbose logging is enabled. #29534
- Fixed a panic caused by inserting values of the wrong type into columns depended on by computed columns. #29598
- Fixed an issue where, under severe load, clients were sometimes receiving retryable errors with a non-retryable error code. #29614
- The `cockroach gen haproxy` command now recognizes nodes that specify the HTTP port number using `--http-addr` instead of `--http-port`. #29536
- Fixed a panic in SQL execution. #29669
- Fixed a panic caused by malformed UTF-8 SQL strings. #29668
- Corrected the Postgres `oid` type returned for collated string columns. #29674
- Enterprise `CHANGEFEED`s now correctly skip the initial scan when started with the `cursor=` option. #29613
- Hash functions with `NULL` input now return `NULL`. #29974
- Prevented a very rare premature failure in `CHANGEFEED`s caused by a race condition with range splits. #30009
- Fixed a crash when `SELECT MIN(NULL)` was run with the SQL optimizer enabled. #30014
- Fixed a rare crash with the message `retryable error for the wrong txn`. #30046
- Fixed a bug where certain queries, like merge joins, would appear to run out of memory due to incorrect memory accounting and fail. #30087
- The `string_agg()` function can now accept a `NULL` as a delimiter. #30076

### Performance improvements

- Range replicas are now automatically rebalanced throughout the cluster to even out the amount of QPS being handled by each node. #29663
- Prevented allocation when checking RPC connection health. #30055

### Doc updates

- Updated the description of correlated subqueries. More updates coming soon. #3714
- Update the description of `cockroach` client connection parameters. #3715
- Added documentation of the `public` role, which all users belong to. #3722
- Update the Diagnostics Reporting page with a summary of details reported and how to view the details yourself. #3737

### Contributors

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

- Sankt Petersbug (first-time contributor)

## v2.1.0-beta.20180910

Release Date: September 10, 2018

### General changes

- CockroachDB no longer checks key usage attributes in security certificates. #29223

### SQL language changes

- In a mixed-version cluster, nodes running v2.0 no longer schedule distributed SQL work on nodes running v2.1. #29168
- When `TRUNCATE` or `DROP TABLE` is run while a schema change like `CREATE INDEX` is being processed, the schema change job no longer runs indefinitely. #29262
- View and table names are now recycled quickly after `DROP VIEW` and `DROP TABLE`.

### Command-line changes

- The new `cockroach workload` command provides various generators for data and query loads. #28978
- The `csv` and `tsv` formats for `cockroach` commands that output result rows now buffer data for a maximum of 5 seconds. This makes it possible to, for example, view SQL `CHANGEFEED`s interactively with `cockroach sql` and `cockroach demo`. #29445

### Bug fixes

- Fixed support for the `--http-host` flag, which was broken in previous 2.1 beta releases. #29220
- Reduced the duration of partitions in the gossip network when a node crashes to eliminate a cause of temporary data unavailability. #29317
- The `unnest` and `_pg_expandarray` functions now return an error when called with NULL as the first argument. #29385
- Fixed a crash caused by JSON values and operations that use arrays. #29432
- Fixed a rare crash with the message `no err but aborted txn proto`. #29456
- Fixed a crash caused by SQL statements containing `->(NULL::STRING)`. #29414
- Fixed table descriptor corruption when `TRUNCATE` is run while `DROP COLUMN` is being processed. #29262

### Doc updates

- Updated the Data Replication tutorial and the Production Checklist to emphasize the importance of manually increasing the replication factor for important internal data when doing so for the `.default` replication zone. #3702

### Contributors

This release includes 65 merged PRs by 20 authors.

## v2.1.0-beta.20180904

Release Date: September 4, 2018

### Enterprise edition changes

- Added additional monitoring metrics for `CHANGEFEED`s. #28923

### SQL language changes

- CockroachDB now hides more information from the statement statistics in diagnostics reporting. #28906
- CockroachDB now preserves the distinction between different column types for string values like in PostgreSQL, for compatibility with 3rd party tools and ORMs. #29006
- The `SET CLUSTER SETTING` statement can no longer be used inside a transaction. It also now attempts to wait until the change has been gossiped before allowing subsequent statements. #29082
- The `ALTER TABLE ... SPLIT AT` statement now produces an error if executed while the merge queue is enabled, as the merge queue is likely to immediately discard any splits created by the statement. #29082

### Command-line changes

- Improved the error message printed when `cockroach quit` is run on a node that has not yet been initialized. #29152
- The `cockroach start` command now emits the PID of the server process to the file specified by the `--pid-file` flag as soon as it is ready to accept network connections but possibly before it is done bootstrapping (i.e., before `cockroach init` completes). To wait for SQL readiness, use the `--listen-url-file` flag instead. #29160

### Bug fixes

- CockroachDB now populates the `data_type` column of `information_schema.columns` like PostgreSQL, for compatibility with 3rd party tools and ORMs. #29006
- The `cockroach dump` command can once again operate across multiple CockroachDB versions. #29006
- CockroachDB now distinguishes `CHAR` and `VARCHAR`, as mandated by the SQL standard and PostgreSQL compatibility. When a width is not specified (e.g., `CHAR(3)`), the maximum width of `VARCHAR` remains unconstrained whereas the maximum width of `CHAR` is 1 character. #29006
- CockroachDB now properly checks the width of strings inserted in a collated string column with a specified width. #29006
- Improve the handling of jobs run prior to a cluster upgrade. #29019
- CockroachDB once again prefers using an IPv4 listen address if a hostname with both IPv4 and IPv6 addresses is provided to `--host`/`--listen-addr`/`--advertise-addr`. #29158
- Fixed a memory leak when contended queries time out. #29099
- When the `--background` flag is specified, the `cockroach start` command now avoids printing messages to standard output after it has detached to the background. #29160

### Contributors

This release includes 41 merged PRs by 16 authors.

## v2.1.0-beta.20180827

Release Date: August 27, 2018

We have now transitioned into the CockroachDB 2.1 Beta phase and will be releasing weekly until the GA release. This week's release includes PostgreSQL compatibility enhancements, general usability improvements, performance improvements, and bug fixes. In addition, we want to highlight a few major benefits:

- Automatic performance optimizations - Range leases are now automatically rebalanced throughout the cluster to even out the amount of QPS being handled by each server.
- Better controls for geo-distributed clusters - We’ve added more sophisticated support for controlling the network interfaces to use in certain situations, so nodes can prefer local, private IPs for inter-DC communication, and only use public IPs when making hops that must go over the open internet. See the `--locality-advertise-addr` flag of the `cockroach start` command for more details.

### Backward-incompatible changes

- Support for PostgreSQL's `TIMETZ` data type has been removed due to incomplete/incorrect implementation. This feature was available only in previous 2.1 alpha releases. Before upgrading to this release, tables with the `TIMETZ` type must be dropped entirely; it is not possible to convert the data or drop a single `TIMETZ` column. #28095
- Support for the `BIT` data type has been removed due to incorrect implementation and incompatibility with some client apps. Tables with the `BIT` type will continue to work but will see their type automatically changed to `INT` in the output of `SHOW TABLES`, `information_schema`, etc. This is backward-compatible insofar that the previous `BIT` type in CockroachDB was actually a simple integer. A PostgreSQL-compatible replacement will likely be added at a later time. #28814

### General changes

- CockroachDB now supports a separate CA (`ca-ui.crt`) and certificate (`ui.crt`) for the Web UI. #27916
- The ability to set lease placement preferences in replication zones is now fully supported. Existing lease placement preferences will continue to function as in v2.0. #28261
- The new `/_admin/v1/enqueue_range` admin server endpoint runs a specified range through a specified internal queue on one or all nodes. The `skip_should_queue` parameter can also be specified to tell the system to blindly run without first checking whether it needs to be run. This endpoint is intended primarily for debugging purposes. #26554
- If enabled, anonymous diagnostics reporting now includes hardware and OS information as well as basic stats about the size of `IMPORT` jobs. #28676 #28726

### Enterprise edition changes

- This release includes several changes to the Change Data Capture feature:
  - `CHANGEFEED`s now support interleaved tables #27991
  - `CREATE CHANGEFEED` now requires an enterprise license when used with Kafka. #27962
  - `CHANGEFEED`s now produce an error when column families are added (instead of returning incorrect results) and when targeting `system` tables (instead of operating with undefined behavior). #27962
  - `CREATE CHANGEFEED` is now restricted to superusers. #27962
  - `CHANGEFEED` job descriptions now substitute values for SQL placeholders. #28220
  - `CHANGEFEED`s can now only target lists of physical tables. #27996
  - `CHANGEFEED`s now produce an error when a watched table is truncated, dropped, or renamed. #28204
  - `CHANGEFEED` Kafka tunings have been adjusted for faster flushes, improving throughput. #28586
  - `CHANGEFEED`s now checkpoint progress more granularly. #28319
  - `CHANGEFEED`s now export metrics for production monitoring. #28162
  - The `CHANGEFEED` `timestamp` option has been split into `updated` and `resolved`. #28733
  - `CHANGEFEED`s are now executed using our distributed SQL framework. #28555
- This release includes the following changes to the Encryption At Rest feature:
  - The status of encryption is now written to debug logs. #27880
  - Data keys are now rotated while nodes are running. #28148
  - The new `cockroach debug encryption-status` command displays encryption key information. #28582

### SQL language changes

- Added foreign key support to `IMPORT ... MYSQLDUMP`. #27861
- The output of `SHOW GRANTS` is now fully sorted. #27884
- Reads from Google Cloud Storage for `IMPORT` or `BACKUP` jobs are now more resilient to quota limits. #27862
- The `ORDER BY INDEX` notation now implies an ordering by the implicit primary key columns appended to an index. #27812
- Added the `server_encoding` session variable and protocol status parameter, for compatibility with PostgreSQL. It is set to `UTF8` and cannot be changed. #27943
- Extended support of the `extra_float_digits` session variable, for compatibility with PostgreSQL. #27952
- Improved the handling of `SET`, `RESET` and `SHOW`, for better compatibility with PostgreSQL. #27947
- Exposed the `integer_datetimes` session variable in `SHOW` and `pg_settings`, for compatibility with PostgreSQL. #27947
- The default values of the `client_min_messages` and `extra_float_digits` session variables now match PostgreSQL. #27947
- Corrected the `oids` and formatting of some columns in the `pg_catalog.pg_index` table. #27961
- The distribution of queries that use the `repeat()` built-in function are now permitted. #28039
- Statement statistics are now grouped separately for queries using the cost-based optimizer and heuristic planner. #27806
- CockroachDB now supports empty tuples with the syntax `()`, 1-valued tuples with the syntax `(x,)` in addition to `row(x)`, and the ability to use `IN` with an empty tuple as right operand. This is a CockroachDB extension. #28143
- CockroachDB now supports constructing array values using parentheses, for example `ARRAY(1,2,3,4)`. This is a CockroachDB extension; the standard PostgreSQL syntax `ARRAY[1,2,3,4]` remains supported. #28238
- CockroachDB now supports converting arrays and tuples to strings, for compatibility with PostgreSQL. #28183
- `ANY`/`ALL`/`SOME` comparisons are now more permissive about the types of their input expressions, and comparisons with empty tuples are now allowed. #28226
- Improved the handling of decimal 0s. Specifically, -0 is coerced to 0 and values like 0.00 retain the digits after the decimal point. #27978
- Arrays of arrays are no longer allowed, even as intermediate results. #28116
- `IMPORT ... PGDUMP` now supports CockroachDB dump files. #28359
- The decimal variants of the `ceil()` and `ceiling()` functions now return 0 where they would have returned -0 previously. #28366
- Improved support for S3-compatible endpoints in `BACKUP`, `RESTORE`, and `IMPORT`. The `AWS_REGION` parameter is no longer required. Services like Digital Ocean Spaces and Minio now work correctly. #28394
- CockroachDB now supports an optional `FILTER` clause with aggregates when used as window functions. #28357
- Normalized the case of table names imported via `IMPORT ... MYSQLDUMP`. #28397
- All queries now run through the DistSQL execution engine. #27863
- It is now an error to specify both `FORCE_INDEX` and `NO_INDEX_JOIN` hints at the same time. #28411
- Added `numeric_precision_radix` to the `information_schema.columns` table. #28467
- Added the `schemachanger.lease.duration` and `schemachanger.lease.renew_fraction` cluster settings to control the schema change lease. #28342
- Added the `string_agg()` aggregation function, which concatenates a collection of strings into a single string and separates them with a specified delimiter. #28392
- CockroachDB now fully supports the `RANGE` mode for specification of window function frames. #27022
- CockroachDB now supports the `GROUPS` mode for specification of window function frames. #28244
- CockroachDB now supports the `ARRAY()` operator and comparisons with sub-queries on the right side of the comparison, when they appear themselves in sub-queries. #28618
- CockroachDB now supports two experimental compatibility modes with how PostgreSQL handles `SERIAL` and sequences, to ease reuse of 3rd party frameworks or apps developed for PostgreSQL. These modes can be enabled with the `experimental_serial_normalization` session variable (per client) and `sql.defaults.serial_normalization` cluster setting (cluster-wide). The first mode, `virtual_sequence`, enables compatibility with many applications using `SERIAL` with maximum performance and scalability. The second mode, `sql_sequence`, enables maximum PostgreSQL compatibility but uses regular SQL sequences and is thus subject to performance constraints. #28575
- The output of `SHOW COLUMNS` now indicates which columns are hidden. #28750
- `SHOW CREATE` now reports the `FLOAT` column types as `FLOAT4` and `FLOAT8` (the default) instead of `REAL` and `FLOAT`. #28776

### Command-line changes

- This release includes the following changes to the `cockroach start` command:
  - The new `--listen-addr` flag recognizes both a hostname/address and port and replaces the `--host` and `--port` flags, which are now deprecated for `cockroach start` but remain valid for other client commands. The port portion of `--listen-addr` can be either a service name or numeric value; when specified as `0`, a port number is automatically allocated. #27800 #28373 #28502
  - The new `--advertise-addr` flag recognizes both a hostname/address and port and replaces the `--advertise-host` and `--advertise-port` flags, which are now deprecated. The port portion of `--advertise-addr` can be either a service name or numeric value; when specified as `0`, a port number is automatically allocated. #27800 #28373 #28502
  - The new `--http-addr` flag recognizes both a hostname/address and port and replaces the `--http-host` flag, which is now deprecated. The port portion of `--http-addr` can be either a service name or numeric value; when specified as `0`, a port number is automatically allocated. #28373 #28502
  - The new `--locality-advertise-addr` flag can be used advertise a hostname/address and port to other CockroachDB nodes for specific localities. This is useful in deployments with "local" or "private" interfaces that are only accessible by a subset of the nodes and "global" or "public" interfaces that are slower or more expensive but accessible by any node. In such cases, `--locality-advertise-addr` can be used to route traffic over the local interface whenever possible. #28531
  - The command now reports the URL of the web UI with the prefix "`webui:`", not `admin:`. #28038
  - The command now reports a warning if more than 75% of available RAM is reserved by `--cache` and `--max-sql-memory`. #28199
  - The command now suggests which command-line flags to use to access the newly started node in client commands (e.g., `cockroach quit`, etc.). #28198 `cockroach start`
- This release includes the following changes to `cockroach` client commands:
  - Client commands now better attempt to inform the user about why a connection is failing. #28200
  - Client commands that print out SQL results now issue a warning if more than 10000 result rows are buffered in the `table` formatter. #28490
  - Client commands that use a SQL connection (e.g., `cockroach sql`, `cockroach node`, `cockroach user`) now produce an error if a connection could not be established within 5 seconds instead of waiting forever. #28326
  - The `cockroach sql` command and other client commands that display SQL results now use the new `table` result formatter by default, replacing the previous formatter called `pretty`. This provides more compact and more reusable results. #28465
  - The `cockroach sql` command and other client commands that display SQL results containing byte arrays now print them as if they were converted by a SQL cast to the `STRING` type. #28494
  - The `--host` flag and `COCKROACH_HOST` environment variable for client commands now recognize both a hostname/address and port number. The `--port` flag is still recognized but no longer documented; `--host` is now preferred. The `COCKROACH_PORT` environment variable is now deprecated in favor of `COCKROACH_HOST`. Also, the syntax to specify IPv6 addresses has been changed to use square brackets, for example, `--host=[::1]` instead of just `--host=::1`; the previous syntax is still recognized for backward compatibility but is deprecated. #28373
- The new `timeseries.storage.10s_resolution_ttl` and `timeseries.storage.30m_resolution_ttl` cluster settings control how long time series data is retained on the cluster. They work with the recently added "roll-ups" to allow longer retention of time series data while consuming considerably less disk space. #28169
- The `cockroach demo` command now supports starting with one of various datasets loaded. #28383
- The file generated by running `cockroach debug zip` now contains the contents of the `system.rangelog` table, which is a record of range splits and rebalances in the cluster. The problem ranges report is now included as well. #28396 #28253
- The `cockroach node status` command now works on unavailable/broken clusters. #28249
- CockroachDB now reports a non-zero exit status if an attempt is made to use a non-existent command. #28492
- CockroachDB now attempts to inform the operator if the names and IP addresses listed in the configured certificates do not match the server configuration. #28502
- Added a locality filter for the `cockroach gen haproxy` command #28649

### Web UI changes

- Added disk read and write time charts to the Hardware dashboard. #27977 #28594
- The Hardware dashboard now shows system and user CPU summed instead of separately, and normalized by number of CPUs. #28596
- Added a link to the Statements page from the sidebar. #27928
- The Statements page now reveals whether a SQL query used the new cost-based optimizer. #28094
- Added the number of CPUs and percentages of memory and disk usage to the Node List. #28189
- Removed "distsql reads" time series from the SQL dashboard, since execution engines are being merged. #28350
- The Problem Ranges report now shows the number of replicas that have an excessively large log. #28034
- The Stores report now shows encryption statistics. #26890
- Login is now required by default on secure clusters. #28416
- Enlarged the clickable area on dropdown components to include entirety of the surrounding container. #28331
- The Jobs page now supports indefinitely-running job types that have a "highwater timestamp", instead of the "fraction completed" used by jobs with a finite task. #28535
- Improved the alert text that is displayed when the Web UI connection is lost. #28838

### Bug fixes

- Fixed a bug where the Statements page in the Web UI blanked out after reloading itself. #28108
- CockroachDB no longer erroneously allows generator functions, aggregates, and window functions in the `ON` clause of joins. #28839
- Fixed an `index-id does not exist` error that could happen on `ADD COLUMN` or `DROP COLUMN`. #28803
- Fixed row counts in the output of `IMPORT`. #28469
- Fixed various problems related to the rollback of schema changes.#28014 #28050
- Prevented a node from freezing after `DROP DATABASE` when the command aborts, and fixed the rare use of an older descriptor after `DROP INDEX`. #28381
- Fixed the handling of regular aggregations combined with window functions and columns "as-is". #27897
- Fixed a panic caused by key-value tracing a plan that uses an index joiner. #27942
- The `bytea_output` session variable is now properly effective for distributed queries. #27951
- Limited the size of "batch groups" when committing a batch to RocksDB to avoid rare scenarios in which multi-gigabyte batch groups are created, which can cause a server to run out of memory when replaying the RocksDB log at startup. #27895
- Fixed the round-tripping of cast expression formatting in the presence of collated strings. #27941
- Prevented spurious query errors when planning some complex correlated SRFs through the distributed execution engine. #27995
- Fixed the handling of frame boundary offsets in `WINDOW` clauses. #27933
- Fixed the formatting of time datatypes in some circumstances. #28040
- Fixed the behavior of `crdb_internal.cluster_id()` in distributed queries. #28042
- Fixed incorrect `NULL` handling in the distributed implementations of `INTERSECT` and `EXCEPT`. #28097
- Corrected erroneous failures of privileged built-ins in queries run through the distributed execution engine. #28107
- Ensured that the `TIMESTAMP` data type never retains a timezone and renders consistently across a distributed computation flow. #28112
- Corrected casts and binary operators between `TIMESTAMPTZ` and `TIMESTAMP` in some cases. #28128
- Prevented some sequence built-ins from incorrectly running in distributed flows. #28114
- Corrected the round-trip formatting of negative floats and decimals in the context of other expressions when executing in a distributed flow. #28129
- Fixed bug that could skip the row following a deleted row during `BACKUP`. #28172
- The `cockroach user set --password` command can now change the password of existing users. #28197
- CockroachDB now supports a wider range of tuple and array values in query results. #28151
- This release includes the following fixes to the `cockroach sql` and `cockroach demo` command:
  - The commands are now properly able to customize the prompt with `~/.editrc` on Linux. #28233
  - The commands once again support copy-pasting special unicode character from other documents. #28233
  - The commands once again properly handle copy-pasting a mixture of client-side commands (e.g., `set`) and SQL statements. #28235
  - The commands now properly print a warning when a `?` character is mistakenly used to receive contextual help in a non-interactive session, instead of crashing. #28324
  - The commands now work properly even when the `TERM` environment variable is not set. #28613
- Generator built-ins now correctly return no rows instead of `NULL` when given `NULL` arguments. #28252
- Fixed out-of-memory errors caused by very large raft logs. #28293 #28511
- Certain queries that use empty arrays constructed from subqueries no longer spuriously fail when executed via the distributed execution engine. #28391
- `SHOW JOBS` now uses placeholder values for `BACKUP` and `RESTORE` job descriptions. #28321
- CockroachDB now handles negative `FLOAT` zeros properly in more cases. #28569
- CockroachDB now correctly handles computation of `array_agg()` when used as a window function. #28291
- Decommissioning multiple nodes is now possible without posing a risk to cluster health. Recommissioning a node no longer requires a restart of the target node to take effect. #28707
- Fixed a rare scenario where the value written for one system key was seen when another system key was read, leading to the violation of internal invariants. #28794
- Hidden columns are now listed in `information_schema` and `pg_catalog` tables, for better compatibility with PostgreSQL. #28750
- Casting arrays now correctly preserves `NULL` values. #28860
- `IMPORT` no longer silently converts `rn` characters in CSV files into `n`. #28181
- Fixed initial poor latencies introduced in a recent release. #28599

### Performance improvements

- CockroachDB now periodically refreshes table leases to avoid initial latency on tables that have not been accessed recently. #28725
- Improved the fixed cost of running distributed sql queries. #27899
- Prevent large buffer allocation for DML statements with `RETURNING` clauses. #27944
- Improved low-level iteration performance in the presence of range tombstones. #27904
- Data ingested with `RESTORE` and `IMPORT` is now eligible for a performance optimization used in incremental `BACKUP` and `CHANGEFEED`s. #27966
- Reduced lock contention in `RemoteClockMonitor`. #28000
- Reduced lock contention in the Replica write path. #24990
- Reduced lock contention in the Gossip server. #28001 #28127
- Reduced lock contention and avoided allocations in `raftEntryCache`. #27997
- Fixed a batch commit performance regression that reduced write performance by 20%. #28163
- Greatly improved the performance of catching up followers that are behind when Raft logs are large. #28511
- Slightly improved the performance of the `nextval()` sequence function. #28576
- Reduced the cost of Raft log truncations and increased single-range throughput. #28126
- Subqueries are now run through the distributed execution engine. #28580
- Range leases are now automatically rebalanced throughout the cluster to even out the amount of QPS being handled by each node. #28340
- Greatly improved the performance of deleting from interleaved tables that have `ON DELETE CASCADE` clauses. #28330

### Doc updates

- Added a tutorial on orchestrating CockroachDB across multiple Kubernetes clusters in different regions. #3558
- Expanded the Build an App tutorials for most languages to offer instructions and code samples for secure clusters. #3557
- Significantly expanded the documentation on Window Functions. #3426
- Added a conceptual explanation of Online Schema Changes, with examples and current limitations. #3492
- Streamlined instructions for essential enterprise and core backup and restore tasks, including a bash script for automated backups. #3489
- Expanded the TPC-C Performance Benchmarking tutorial to cover benchmarking large clusters. [#3281][#3281]
- Documented the `skip` option for `IMPORT` as well as support for decompressing input files. #3510
- Documented the `ANALYZE`, `OPT`, and `DISTSQL` option for `EXPLAIN`. #3427
- Documented how to add a computed column to an existing table and convert a computed column into a regular column. #3501 #3538
- Documented the abbreviated PostgreSQL `INTERVAL` format. #3503
- Documented the `auto_trace` session variable, which replaces the `SHOW TRACE` statement. #3508
- Various updates to the Information Schema documentation. #3531
- Documented the new default databases. #3506
- Cleaned up the output of all `SHOW` statements; combined the `SHOW CREATE TABLE`, `SHOW CREATE VIEW`, and `SHOW CREATE SEQUENCE` pages into a single `SHOW CREATE` page; and removed the experimental status from `SHOW CONSTRAINTS`. #3523
- Documented the `cockroach demo` command. #3509
- Various updates to the `cockroach sql` documentation. #3499

### Contributors

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

- Constantine Peresypkin
- Garvit Juniwal
- Joseph Lowinske (first-time contributor, CockroachDB team member)
- Song Hao
- Takuya Kuwahara
- Tim O'Brien (first-time contributor, CockroachDB team member)
- neeral

## v2.1.0-alpha.20180730

Release Date: July 30, 2018

For our July 30th alpha release, in addition to PostgreSQL compatibility enhancements, general usability improvements, and bug fixes, we want to highlight some major benefits:

- Troubleshoot performance problems with hardware metrics - The new Web UI Hardware dashboard provides more visibility into how cluster CPU, networking, disk, and memory resources are being utilized so you can quickly identify and remove performance bottlenecks.
- Easier PostgreSQL migration - We’ve made further enhancements to reduce PostgreSQL migration friction. Notable improvements include support for foreign keys, sequences, and `COPY` in `IMPORT ... PGDUMP`.
- Monitor Kubernetes-orchestrated clusters with Prometheus - We expanded our guides for running CockroachDB on Kubernetes in production to include setting up monitoring and alerting with Prometheus and Alertmanager.

Please give these features and the ones below a try. If you see something that can be improved, we’d love to hear from you on GitHub or the Forum.

### General changes

- The cost-based optimizer is now on by default. #26893
- The time series system used by CockroachDB to store internal monitoring data now utilizes pre-computed rollups to significantly increase the duration for which monitoring data is available while using less storage. Monitoring data will be available for up to a year by default; however, data older than seven days will be stored at a reduced resolution, and thus will only be able to give details about 30 minute intervals. #27121
- Building CockroachDB from source now requires yarn at version 1.7.0 or above. #27262
- Added support for signing server and client certificates by different CAs. #27636

### Enterprise edition changes

- Core dumps are now disabled when encryption is enabled. #27426
- `CHANGEFEED`s now use an asynchronous Kafka producer, increasing throughput. #27421

### SQL language changes

- CockroachDB now supports custom frame specification for window functions using `ROWS` (fully-supported) and `RANGE` modes. For `RANGE`, `<value> PRECEDING` and `<value> FOLLOWING` are not supported. #26666
- The `SNAPSHOT` isolation level has been removed. Transactions that request to use it are now mapped to `SERIALIZABLE`. #27040.
- When the cost-based optimizer is enabled, it will also affect prepared queries. #27034
- Upon failing to gather data from other nodes, the `SHOW CLUSTER QUERIES` and `SHOW CLUSTER SESSIONS` statements now report the details of the error. #26821
- Improved the description for the `age()` built-in function. #27082
- The `pg_get_indexdef()` built-in function now supports 3 arguments. #27161
- Added `COPY` support to `IMPORT .. . PGDUMP`. #27062
- The new `max_row_size` option overrides default limits on line size for `IMPORT ... PGDUMP` and `PGCOPY`. #27062
- The `SHOW TRACE FOR <stmt>` statement was incomplete and incorrect and has thus been removed. To turn on tracing, use `SET tracing = ...` and `SHOW SESSION TRACE`, or enable the new `auto_trace` client-side option for `cockroach sql`. #26729 #27805
- `SET tracing` accepts a new option `results`, which causes result rows and row counts to be copied to the session trace. This was previously implicit with option `kv` but must now be specified explicitly when desired. #26729
- The word `view` is now supported as an identifier like in PostgreSQL. #27204
- `IMPORT ... PGDUMP` no longer requires the `--no-owner` flag. #27268
- `AS OF SYSTEM TIME` can now use some more complex expressions to compute the desired timestamp. #27206
- Added support for the `convert_from()` and `convert_to()` built-in functions, for compatibility with PostgreSQL. For `convert_from()`, however, in contrast with PostgreSQL, the function in CockroachDB accepts NUL in the input, because null characters are valid in CockroachDB strings. #27328
- The `ALTER ... EXPERIMENTAL CONFIGURE ZONE` statement now accepts arbitrary scalar expressions (including possibly containing sub-queries) to compute the YAML operand. #27213
- CockroachDB now recognizes PostgreSQL's abbreviated time units when converting strings to intervals. #27393
- Sorting with a limit and/or input ordering now falls back to disk. #27083
- CockroachDB now reports a hint in the error message if it encounters a correlated query that it does not support yet. #27396
- The new `EXPERIMENTAL_RELOCATE LEASE` command for `ALTER TABLE` and `ALTER INDEX` allows manually transferring the leases for specific ranges to specific stores. #26436
- Added the `sql.distsql.flow_stream_timeout` and `sql.distsql.max_running_flows` cluster settings to fine-tune flow setup #27404
- `IMPORT` now supports a `WITH oversample = ...` option to decrease variance in data distribution during processing. #27341
- `IMPORT ... PGDUMP` now supports foreign keys. #27425
- `IMPORT ... PGDUMP` now supports sequences. #27739
- `IMPORT ... PGDUMP` now supports empty and public schemas. #27782
- `SHOW JOBS` now reports results even when a job entry is incomplete or incorrect. #27430
- The column labels in the output of `EXPLAIN` and all `SHOW` statements have been renamed for consistency. #27098
- The column labels in the output of `SHOW COLUMNS` have been renamed for consistency with `information_schema`. The new `generation_expression` column reports the expression used for computed columns. #27098
- The `SHOW CREATE` statement has been simplified and can be used equivalently on tables, views, and sequences without having to specify the type of object to inspect. #27098
- Added the `chr()` built-in function (the inverse of `ascii()`). #27278
- Added support for skipping foreign keys in `IMPORT`s that support them. #27606
- The new `sql.optimizer.count` metric has been added to track the number of queries with the experimental cost-based optimizer. #26981
- More statement types are now reported in the collected statement statistics in the web UI and diagnostics reporting. #27646
- Added support for KV traces (`SHOW KV TRACE FOR SESSION`) on DistSQL-executed queries. #27802
- The return type of single-column generator functions has been changed from `tuple{columnType}` to `columnType`. This is a compatibility change to match the behavior of PostgreSQL. #27773

### Command-line changes

- CockroachDB now computes the correct number of replicas on down nodes. Therefore, when decommissioning nodes via the `cockroach node decommission` command, the `--wait=all` option no longer hangs indefinitely when there are down nodes. As a result, the `--wait=live` option is no longer necessary and has been deprecated. The `--wait=all` option is now the default. #27027
- Added the `cockroach sqlfmt` command for formatting SQL statements. #27240
- The output labels of `cockroach user ls` and `cockroach user get` have been renamed for consistency with the SQL `SHOW USERS` statement. Also, to reduce inadvertent data leaks, the output of `cockroach user get` no longer includes hashed passwords. #27098
- The new client-side option `prompt1` can be used to customize the `cockroach sql` interactive prompt. #27803
- The new `auto_trace` client-side option can be use to turn on tracing for a `cockroach sql` session. #27805

### Web UI changes

- The new Hardware dashboard displays time series data about CPU, memory, and disk and network IO. #27626
- Time series metric metadata is now available at `<Web UI>/_admin/metricmetada`. #25359
- Encryption progress is now reported on `<Web UI>/#/reports/stores/local` debug page. #26802
- Statement statistics can now be filtered by app on the Statements page. #26949
- Improved the readability of the mean and standard deviation bar chart on the Statement Details page. #26949
- Added a visualization of the standard deviation of the latency of statements to the Statements page. #26949
- The Statements page now shows statements that executed on all nodes in the cluster, not just the gateway node. #26605
- The Statement Details page now includes a table showing statistics broken down by which node was the gateway node. #26605

### Bug fixes

- Fixed the ordering of columns in the `cockroach node status` output. #27042
- Fixed a bug that would make the Statement Details page in the Web UI break if a statement wasn't found. #27105
- Fixed some incorrectly typed columns in the `pg_index` virtual table. #27723
- Fixed permissions and audit logging issues with the optimizer. #27108
- Prevented a situation in which ranges repeatedly fail to perform a split. #26934
- Fixed a crash that could occur when distributed `LIMIT` queries were run on a cluster with at least one unhealthy node. #26950
- Failed `IMPORT`s now begin to clean up partially imported data immediately and in a faster manner. #26959
- `IMPORT` now detects node failure and will restart instead of fail. #26881
- Fixed a panic in the optimizer with `IN` filters. #27053
- Fixed a panic that could occur when renaming a scalar function used as a data source. #27039
- The server will not finalize a version upgrade automatically and erroneously if there are nodes temporarily inactive in the cluster. #26821
- The `DISTINCT ON` clause is now reported properly in statement statistics. #27221
- Fixed a panic in `IMPORT` when creating a table using a sequence operation (e.g., `nextval()`) in a column's DEFAULT expression. #27122
- `SET` now properly rejects attempts to use invalid variable names starting with `tracing.`. #27216
- Fixed `NULL` equality handling in the experimental lookup join feature. #27336
- `ALTER ... EXPERIMENTAL CONFIGURE ZONE` is now properly tracked in statement statistics. #27213
- Invalid uses of set-generating functions in `FROM` clauses are now reported with the same error code as PostgreSQL. #27390
- The number of `COPY` columns is now correctly verified during `IMPORT ... PGDUMP`. #27345
- `CHANGFEED`s now correctly emit all versions of quickly changing rows. #27612
- Alleviated a scenario in which a large number of uncommitted Raft commands could cause memory pressure at startup time. #27009
- Prevented the unbounded growth of the Raft log caused by a loss of quorum. #27774
- Foreign key references in `IMPORT ... PGDUMP` are now processed in the correct order. #27782

### Performance improvements

- Transactional writes are now pipelined when being replicated and when being written to disk, dramatically reducing the latency of transactions that perform multiple writes. This can be disabled using the new `kv.transaction.write_pipelining_enabled` cluster setting. #26599
- Reduced CPU utilization in clusters with many ranges, also during periods of lease rebalancing. #26910 #26907
- Reduced the memory size of commonly used Request and Response objects. #27112
- Improved low-level iteration performance. #27299
- Prevented a scenario when dropping a table could cause excessive compaction activity that would degrade performance significantly. #27353
- Limited the scanner from running incessantly on stores with 100s of thousands of replicas. #27441
- Prevented dead nodes in clusters with many ranges from causing unnecessarily high CPU usage. #26911
- Significantly reduce CPU usage when a large number of ranges are deleted from a node. #27520
- Min, max, sum, and avg now take linear time when used for aggregation as window functions for all supported window frame options. #26988
- `CHANGEFEED`s no longer hold all data for each poll in memory at once, increasing scalability. #27612

### Build changes

- Upgraded protobuf to 3.6.0 #26935

### Doc updates

- Added a tutorial on benchmarking CockroachDB with TPC-C. [#3281][#3281]
- Expanded the Production Checklist to cover a detailed explanation of network flags and scenarios and updated production deployment tutorials to encourage the use of `--advertise-host` on node start. #3352
- Expanded the Kubernetes tutorials to include setting up monitoring and alerting with Prometheus and Alertmanager. #3370
- Updated the rolling upgrade tutorial with explicit `systemd` commands. #3396
- Updated the OpenSSL certificate tutorial to allow multiple node certificates with the same subject. #3423
- Added an example on editing SQL statements in an external editor from within the built-in SQL shell. #3425

### Contributors

This release includes 328 merged PRs by 42 authors. We would like to thank the following contributors from the CockroachDB community, with special thanks to first-time contributors Art Nikpal, Ivan Kozik, Tarek Badr, and nexdrew.

- Art Nikpal
- Brett Snyder
- Ivan Kozik
- Nishant Gupta
- Tarek Badr
- neeral
- nexdrew

## v2.1.0-alpha.20180702

Release Date: July 2, 2018

For our July 2nd alpha release, in addition to PostgreSQL compatibility enhancements, general usability improvements, and bug fixes, we want to highlight a few major benefits:

- Get visibility into query performance with the Statements pages - The Web UI can now surface statistics about queries along with visualizations to help identify application problems quickly.
- Get up and running faster with `IMPORT MYSQLDUMP/PGDUMP` - It is now much easier to transfer existing databases to CockroachDB.
- Improved data security with Encryption at Rest (enterprise) - With this enhancement, you can now encrypt your CockroachDB files on disk, rotate keys, and monitor encryption status without having to make changes to your application code.
- Stream changes to Kafka with CDC (enterprise) - CockroachDB can now stream changes into Apache Kafka to support downstream processing such as reporting, caching, or full-text indexing.
- Secure your Web UI with User Authentication - A login page can now be enabled to control who can access the Web UI in secure clusters.

Please give these features and the ones below a try. If you see something that can be improved, we’d love to hear from you on GitHub or the Forum.

### Backward-incompatible changes

- CockroachDB now uses a different algorithm to generate column names for complex expressions in `SELECT` clauses when `AS` is not used. The results are more compatible with PostgreSQL but may appear different to client applications. This does not impact most uses of SQL, where the rendered expressions are sufficiently simple (simple function applications, reuses of existing columns) or when `AS` is used explicitly. #26550
- The output columns for the statement `SHOW CONSTRAINTS` were changed. The previous interface was experimental; the new interface will now be considered stable. #26478

### General changes

- Metrics can now be sent to a Graphite endpoint specified using the `external.graphite.endpoint` cluster setting. The `external.graphite.interval` setting controls the interval at which this happens. #25227
- Added a config file and instructions for running CockroachDB in secure mode in a Kubernetes DaemonSet. #26816

### Enterprise edition changes

- The new `SHOW BACKUP RANGES` and `SHOW BACKUP FILES` statements show details about the ranges and files, respectively, that comprise a backup. #26450

### SQL language changes

- If a computed column's expression results in an error, the name of the computed column is now added to the error returned to the user. This makes it easier for users to understand why an otherwise valid operation might fail. #26054
- Implemented the minus operation between a JSON Object and a text array. #26183
- Fixed some error messages to more closely match PostgreSQL error messages, including the corresponding PostgreSQL error codes. #26290
- Added an empty `pg_stat_activity` virtual table for compatibility with DBeaver and other SQL clients that require it. #26249
- The new `EXPLAIN (DISTSQL, ANALYZE)` statement annotates DistSQL execution plans with collected execution statistics. #25849
- `IMPORT` now supports the PostgreSQL `COPY` format. #26334
- The output of `SHOW SESSIONS` now includes the number of currently allocated bytes by the session, and the maximum number of allocated bytes that the session ever owned at once. Note that these numbers do not include the bytes allocated for the session by remote nodes. #25395
- The `bytea_output` session variable now controls how byte arrays are converted to strings and reported back to clients, for compatibility with PostgreSQL. #25835
- Added placeholder `information_schema.routines` and `information_schema.parameters` for compatibility with Navicat, PGAdmin, and other clients that require them. #26327
- CockroachDB now recognizes aggregates in `ORDER BY` clauses even when there is no `GROUP BY` clause nor aggregation performed, for compatibility with PostgreSQL. #26425
- Added the `pg_is_in_recovery()` function for compatibility with PostgreSQL tools. #26445
- CockroachDB now supports simple forms of PostgreSQL's `ROWS FROM(...)` syntax. #26223
- CockroachDB now generates a simple column name when using an SRF that produces multiple columns. #26223
- CockroachDB now properly handles some uses of multiple SRFs in the same `SELECT` clause in a way compatible with PostgreSQL. #26223
- Added the `pg_is_xlog_replay_paused()` function for compatibility with PostgreSQL tools. #26462
- Added the `pg_catalog.pg_seclabel` and `pg_catalog.pg_shseclabel` tables for compatibility with Postgres tools. Note that we do not support adding security labels. #26515
- CockroachDB now supports `INSERT ... ON CONFLICT DO NOTHING` without any specified columns; on a conflict with any `UNIQUE` column, the insert will not continue. #26465
- CockroachDB now supports the `bit_length()`, `quote_ident()`, `quote_literal()`, and `quote_nullable()` built-in functions, and the aliases `char_length()` and `character_length()` for `length()`, for compatibility with PostgreSQL. #26586
- If a function name is typed in with an invalid schema or invalid case, the error message now tries to provides a suggestion for alternate spelling. #26588
- CockroachDB now can evaluate set-generating functions with arguments that refer to the `FROM` clause. In particular, this makes it possible to use functions like `json_each()` and `json_object_keys()` over `JSONB` columns. #26503
- Added prototype support for `IMPORT ... MYSQLDUMP`, including the ability to import entire (multi-table) mysqldump files. #26164
- `CHECK` constraints are now checked when updating a conflicting row in `INSERT ... ON CONFLICT DO UPDATE` statements. #26642
- Labeled tuples can now be accessed using their labels (e.g., `SELECT (x).word FROM (SELECT pg_expand_keywords() AS x)` or a star (e.g., `SELECT (x).* FROM (SELECT pg_expand_keywords() AS x)`). #26628
- An error is now returned to the user instead of panicking when trying to add a column with a `UNIQUE` constraint when that column's type is not indexable. #26684
- Introduced the `sql.failure.count` metric, which counts the number of queries that result in an error. #26731
- Added support for de-compressing `IMPORT` files with gzip or bzip. #26796
- Added initial support for `IMPORT` with pg_dump files. #26740
- Added the `like_escape()`, `ilike_escape()`, `not_like_escape()`, `not_ilike_escape()`, `similar_escape()`, and `not_similar_escape()` built-in functions for use when an optional `ESCAPE` clause is present. #26176
- Added support for set-returning functions in distributed SQL execution. #26739
- Added a cluster setting to enable the experimental cost-based optimizer. #26299
- Added the `pg_catalog.pg_shdescription` table for compatibility with PostgreSQL tools. Note that CockroachDB does not support adding descriptions to shared database objects. #26474

### Command-line changes

- `cockroach quit` now emits warning messages on its standard error stream, not standard output. #26158
- `cockroach sql` now recognizes the values `on`, `off`, `0`, `1`, `true` and `false` to set client-side boolean parameters with `set`. #26287
- `cockroach sql` now recognizes `set option=value` as an alias to `set option value`. #26287
- `cockroach demo` now supports more options also supported by `cockroach sql`, including `--execute`, `--format`, `--echo-sql` and `--safe-updates`. #26287
- `cockroach demo` includes the welcome messages also printed by `cockroach sql`. #26287
- `cockroach demo` now uses the standard `defaultdb` database instead of creating its own `demo` database. #26287
- `cockroach sql` and `cockroach demo` now accept `--set` to run `set` commands prior to starting the shell or running commands via `-e`. #26287

### Admin UI changes

- Authentication in the Admin UI can now be enabled for secure clusters by setting the environment variable `COCKROACH_EXPERIMENTAL_REQUIRE_WEB_LOGIN=TRUE`. #25005
- System databases are now listed after all user databases on the Databases page. #25817
- Added Statements and Statement Details pages showing fingerprints of incoming statements and basic statistics about them. #24485
- Lease transfers are now shown in the Range Operations graph on the Replication dashboard. #26653
- Add a debug page showing how table data is distributed across nodes, as well as the zone configs which are affecting that distribution. #24855

### Bug fixes

- Fixed an issue where the Table details page in the Admin UI would become unresponsive after some time. #26636
- Fix a bug where `cockroach quit` would erroneously fail even though the node already successfully shut down. #26158
- `UPSERT` is now properly able to write `NULL` values to every column in tables containing more than one column family. #26169
- Fixed a bug causing index creation to fail under rare circumstances. #26265
- Corrected `NULL` handling during `IMPORT` of `MYSQLOUTFILE`. #26275
- Fixed concurrent access to the same file when using encryption. #26377
- Fixed a bug where a prepared query would not produce the right value for `current_date()` if prepared on one day and executed on the next. #26370
- Rows larger than 8192 bytes are now supported by the "copy from" protocol. #26345
- Trying to "copy from stdin" into a table that doesn't exist no longer drops the connection. #26345
- CockroachDB now produces a clearer message when special functions (e.g., `generate_series()`) are used in an invalid context (e.g., `LIMIT`). #26425
- Fixed a rare crash on node decommissioning. #26706
- Commands are now abandoned earlier once a deadline has been reached. #26643
- Using `SHOW TRACE FOR SESSION` multiple times without an intervening `SET tracing` statement now properly outputs the trace without introducing extraneous duplicate rows. #26746
- The output of debug and tracing commands is no longer corrupted when byte array values contain invalid UTF-8 sequences. #26769
- Joins across two interleaved tables no longer return incorrect results under certain circumstances when the equality columns aren't all part of the interleaved columns. #26756
- Prepared statements using `RETURNING NOTHING` that are executed using the `EXECUTE` statement are now properly parallelized. #26668
- The pretty-print code for `SHOW` now properly quotes the variable name, and the pretty-printing code for an index definition inside `CREATE TABLE` now properly indicates whether the index was inverted. #26923
- Within a transaction, DML statements are now allowed after a `TRUNCATE`. #26051

### Performance improvements

- Improved the throughput of highly contended writes with the new `contentionQueue`. #25014
- The performance impact of dropping a large table has been substantially reduced. #26449
- Using tuples in a query no longer reverts you to single node local SQL execution. #25860
- CockroachDB's internal monitoring time series are now encoded using a more efficient on-disk format to provide considerable space savings. Monitoring data written in the old format will not be converted but will still be queryable. #26614
- Improved the performance of the `sortChunks` processor. #26874

### Build Changes

- Release binaries are now built with runtime AES detection. #26649

### Doc updates

- Added `systemd` configs and instructions to deployment tutorials. #3268
- Added instructions for importing data from Postgres dump files. #3306
- Expanded the first level of the 2.1 docs sidenav by default. #3270
- Updated the Kubernetes tutorials to reflect that pods aren't "Ready" before init. #3291

### Contributors

This release includes 328 merged PRs by 35 authors. We would like to thank the following contributors from the CockroachDB community, with special thanks to first-time contributors Chris Seto and Emmanuel.

- Chris Seto
- Emmanuel
- neeral

## v2.1.0-alpha.20180604

Release Date: June 4, 2018

This release includes general usability enhancements and bug fixes as well as:

- Easier data migrations from MySQL: The `IMPORT` feature now supports a subset of MySQL export formats. We will continue to make migrations easier in the alphas leading up to 2.1.

Please give this feature and the ones below a try. If you see something that can be improved, we’d love to hear from you on GitHub or the Forum.

### General Changes

- New clusters and existing clusters upgraded to this version of CockroachDB will include two new empty databases, `defaultdb` and `postgres`. The `defaultdb` database is automatically used for clients that connect without a current database set (e.g., without a database component in the connection URL). The `postgres` database is provided for compatibility with PostgreSQL client frameworks that require it to exist when the database server has been freshly installed. Both new databases behave like any other regular database and, if deemed unnecessary, can be manually deleted. #24735
- The new `compactor.threshold_bytes` and `max_record_age` cluster settings can be used to configure the compactor. #25397
- After upgrading a cluster from v2.0 to v2.1, it is no longer necessary to manually finalize the upgrade. #24987

### SQL Language Changes

- Collated strings can now be used in `WHERE` clauses on indexed columns. #25169
- The new `CANCEL QUERIES` and `CANCEL SESSIONS` variants of the `CANCEL QUERY` and `CANCEL SESSION` statements cancel multiple queries or sessions at once. Likewise, the new `CANCEL/PAUSE/RESUME JOBS` variants of the `CANCEL JOB`, `PAUSE JOB`, and `RESUME JOB` statements operate on multiple jobs at once. #25157
- The `Level` and `Type` columns of `EXPLAIN (VERBOSE)` results are now hidden; if they are needed, they can be `SELECT`ed explicitly. #25172
- All users now automatically belong to the new `public` role. This role makes it possible to grant privileges on an object for all users at once, e.g., `GRANT SELECT ON mytable TO public;`. #25099
- The binary Postgres wire format is now supported for `INTERVAL` values. #25242
- Prevented `DROP TABLE` from using too much CPU. #24983
- Improved `SET TRACING` so that a client can more easily trace around a statement that produce errors. #25262
- Added the `generate_subscripts()` built-in function. #25295
- Improved the documentation of the `now()`, `current_time()`, `current_date()`, `current_timestamp()`, `clock_timestamp()`, `statement_timestamp()`, and `cluster_logical_timestamp()` built-in functions. #25327
- Running `TRUNCATE` without `CASCADE` on a table that has interleaved table children now returns an error instead of proceeding to delete both tables. #25265
- Tuples can now be labeled using the new grammar `((1,2,3) AS a,b,c)`. #25283
- Labeled tuples can now be accessed using their labels, but doing so requires an extra level of parentheses, e.g., `SELECT (((1,'2',true) AS a, b, c)).a`. #25810
- `SHOW TRACE FOR <stmt>` now runs `<stmt>` through the DistSQL execution engine, if supported. `SHOW KV TRACE FOR <stmt>` still runs `<stmt>` through local SQL. #24709
- Introduced two experimental scalar operators, `IFERROR()` and `ISERROR()`. They may be documented for public use in the future. #25304
- The `server.time_until_store_dead` cluster setting can no longer be set to less than `1m15s`. Setting it to lower values was previously allowed but not safe, since it can cause bad rebalancing behavior. #25598
- `CANCEL JOB` can now be executed on long-running schema change jobs, causing them to terminate early and roll back. #25571
- Added the `array_to_string()` built-in function. #25681
- `IMPORT` now supports MySQL's tabbed `OUTFILE` format. #25615
- `IMPORT` now supports `mysqldump` SQL as a data format. #25783
- The experimental lookup join feature now supports secondary indexes. #25628
- Stored, computed columns can now be converted to regular columns by running `ALTER TABLE t ALTER COLUMN c DROP STORED`. #25819
- The experimental lookup join feature now supports left outer joins. #25644
- `TRUNCATE` commands are now logged in the event log. #25868
- Improved `IMPORT` error messages. #26032

### Command-Line Changes

- Changing or removing a replication zone config now causes events to be written to the system event log. #25250
- Messages that refer to an invoked command (e.g., "Failed running start") are no longer confused by the presence of flags before the first argument (e.g., `cockroach --no-color start`). #25246
- Typos in replication zone constraints are now validated. When they are set, required attributes and localities must match at least one node in the cluster. #25421

### Admin UI Changes

- Running unit tests for the Admin UI now depends on the installation of Google Chrome. #25140
- Added RocksDB compactions/flushes to Storage graphs. #25428
- Added a Stores report page, including encryption status. #26040
- Removed time selectors and tier labels during Node Map setup. #25280

### Bug Fixes

- The `cockroach sql` command once again does not prompt for a password when a certificate is provided. #25252
- Corrected the behavior of `GREATEST` and `LEAST` built-ins when they have a leading `NULL` argument. #25882
- CockroachDB now properly reports an error when using the internal-only functions `final_variance()` and `final_stddev()` instead of causing a crash. #25158
- The `constraint_schema` column in `information_schema.constraint_column_usage` now displays the constraint's schema instead of its catalog. #25190
- `BEGIN; RELEASE SAVEPOINT;` now returns and error instead of causing a crash. #25247
- Fix a bug where the sessions endpoint on the Admin UI would return an error when there was an active transaction. #25249
- Corrected the CockroachDB-specific, currently undocumented conversion from `INTERVAL` to/from numeric types. #25257
- Fixed problems with `IMPORT` sometimes failing after node decommissioning. #25162
- Prevented queries that use placeholders for tuple types from causing a crash. #25269
- Fixed a rare `segfault` that occurred when reading from an invalid memory location returned from C++. #25347
- Fixed a bug with `IS DISTINCT FROM` not returning `NULL` values that pass the condition in some cases. #25336
- Restarting a CockroachDB server on Windows no longer fails due to file system locks in the store directory. #25267
- Prevented the consistency checker from deadlocking. This would previously manifest itself as a steady number of replicas queued for consistency checking on one or more nodes and would resolve by restarting the affected nodes. #25456
- Fixed a crash in some cases when using a `GROUP BY` with `HAVING`. #25574
- Fixed a nil pointer dereference when importing data containing date values. #25661
- Numeric literal values no longer silently lose information after a certain precision. #25597
- Prevented spurious `BudgetExceededErrors` for some queries that read a lot of `JSON` data from disk. #25679
- Fixed query errors in some cases involving a `NULL` constant that is cast to a specific type. #25735
- Fixed a crash when trying to plan certain `UNION ALL` queries. #25747
- Fixed a crash caused by inserting data into a table with computed columns that reference other columns not present in the `INSERT` statement. #25682
- `EXPLAIN (DISTSQL)` now properly reports that plans containing subqueries cannot be run through the DistSQL execution engine. #25618
- CockroachDB no longer crashes if the control statements `CANCEL`/`PAUSE`/`RESUME` are given values using special PostgreSQL types (e.g., `NAME`). #25844
- Fixed a panic when using unordered aggregations. #26042
- Fixed an error caused by `INET` constants in some rare cases. #26086
- Fixed an error caused by empty arrays in some cases. #26090
- Previously, expired compactions could stay in the queue forever. Now, they are removed when they expire. #26039
- Fixed problems using tables with foreign key or interleaved references to other tables when the tables were created in the same transaction. #25786

### Doc Updates

- Documented special syntax forms of built-in SQL functions and conditional and function-like operators, and updated the SQL operator order of precedence. #3192
- Added best practices on understanding and avoiding transaction contention and a related FAQ. #3156
- Improved the documentation of `AS OF SYSTEM TIME`. #3155
- Expanded the manual deployment guides to cover running a sample workload against a cluster. #3149
- Documented the `TIMETZ` data type. #3102
- Added FAQs on generating unique, slowly increasing sequential numbers and the differences between `UUID`, sequences, and `unique_rowid()`. #3104

### Contributors

This release includes 304 merged PRs by 38 authors. We would like to thank the following contributors from the CockroachDB community, with special thanks to first-time contributors Nishant Gupta, wabada, and yuzefovich.

- Garvit Juniwal
- Gustav Paul
- Karan Vaidya
- Nishant Gupta
- Vijay Karthik
- wabada
- Yahor Yuzefovich

## v2.1.0-alpha.20180507

Release Date: May 7, 2018

This release includes usability enhancements, PostgreSQL compatibility improvements, and general bug fixes.

Tip:
The `EXPORT CSV` feature in this release allows you to quickly get data out of CockroachDB and into a format that can be ingested by downstream systems. Unlike the existing ability to export data via a `SELECT` that outputs to a CSV file, `EXPORT` uses all nodes in the cluster to parallelize CSV creation for significantly faster processing. Note that this is a prototype feature, so we’d love for you to try it out and create issues if you’d like any enhancements or find any bugs.

### General Changes

- Added a `/_status/diagnostics/{node_id}` debug endpoint, which returns an anonymized diagnostics report. #24813 #24997
- The header of new log files generated by `cockroach start` now includes the cluster ID once it has been determined. #24926
- The cluster ID is now reported with tag `[config]` in the first log file, not only when log files are rotated. #24993
- Stopped spamming the server logs with "error closing gzip response writer" messages. #25106
- Enforced stricter validation of security certificates. #24687

### Enterprise Edition Changes

- Added alpha support for `EXPORT CSV`. #25075

### SQL Language Changes

- `ROLE` and `STORED` are no longer reserved keywords and can again be used as unrestricted names for databases, tables and columns. #24629 #24554
- The experimental SQL features `SHOW TESTING_RANGES` and `ALTER ... TESTING_RELOCATE` have been renamed `SHOW EXPERIMENTAL_RANGES` and `ALTER ... EXPERIMENTAL_RELOCATE`. #24696
- The `current_schema()` and `current_schemas()` built-in functions now only consider valid schemas, like PostgreSQL does. #24718
- Clarified the error message produced upon accessing a virtual schema with no database prefix (e.g., when `database` is not set). #24772
- Improved the error message returned on object creation when no current database is set or only invalid schemas are in the `search_path`. #24770
- Added more ways to specify an index name for statements that require one (e.g., `DROP INDEX`, `ALTER INDEX ... RENAME`, etc.), improving PostgreSQL compatibility. #24778
- Errors detected by `SHOW SYNTAX` are now tracked internally like other SQL errors. #24819
- Added support for `lpad()` and `rpad()` string functions. #24891
- Computed columns can now be added with `ALTER TABLE ... ADD COLUMN`. #24464
- Added the `TIMETZ` column type and datum. #24343
- The `EXPLAIN` output for `UPDATE` statements now also report `CHECK` expressions, for consistency with `INSERT`. #23373
- Reduced the amount of RAM used when a query performs further computations on the result of a mutation statement (`INSERT`/`DELETE`/`UPSERT`/`UPSERT`) combined with `RETURNING`. #23373
- Added the 'base64' option to the `encode()` and `decode()` built-in functions. #25002
- `IMPORT` now supports hex-encoded byte literals for `BYTES` columns. #24859
- Set Returning Functions (SRF) can now be accessed using `(SRF).x`, where `x` is the name of a column returned from the SRF or a `*`. For example, `SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).x` and `SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).*` are now both valid. Also, the naming of the resulting columns from SRFs now provide more information about the resulting tuple. #24832
- Removed the `METADATA`, `QUALIFY`, and `EXPRS` options for `EXPLAIN`. #25101
- CockroachDB now properly reports an error when a query attempts to use `ORDER BY` within a function argument list, which is an unsupported feature. #25146
- `AS OF SYSTEM TIME` queries now accept a negative interval to produce a relative time from the statement's `statement_timestamp()` time. #24768

### Command-Line Changes

- The `cockroach demo` command now shows the Admin UI URL on startup. #24738
- `cockroach dump` now supports self and cyclic foreign key references. #24716

### Admin UI Changes

- Updated the window title for each page to make browser history more useful. #24634
- Changed the label "bytes" to "used capacity" in the Nodes table. #24843
- The names of dropped schema objects on `DROP DATABASE ... CASCADE` are now displayed. #24852
- The Cluster Overview now adjusts to take the advantage of the size of the screen. #24849
- Fixed a bug where the Node List could get cut off. #24849
- Improve responsiveness of the Prometheus metrics endpoint on very overloaded nodes. #25083
- Time series charts now display data points at more consistent timestamps. #24856

### Bug Fixes

- Converted a panic related to an unsupported type to an error. #24688
- `ALTER INDEX ... RENAME` can now be used on the primary index. #24776
- Fixed a scenario in which a node could deadlock while starting up. #24808
- It is once again possible to use a simply qualified table name in qualified stars (e.g., `SELECT mydb.kv.* FROM kv`) for compatibility with CockroachDB v1.x. #24811
- Ranges in partitioned tables now properly split to respect their configured maximum size. #24896
- Fixed a bug where `SELECT * FROM [DELETE FROM ... RETURNING ...] LIMIT 1` or `WITH d AS (DELETE FROM ... RETURNING ...) SELECT * FROM d LIMIT 1` would fail to properly delete some rows. The is fixed for `SELECT * FROM [INSERT ... RETURNING ...] LIMIT 1` or `WITH d AS (INSERT ... RETURNING ...) SELECT * FROM d LIMIT 1` as well. #23373
- Removed a limitation where `UPDATE`, `INSERT`, or `UPSERT` would fail if the number of modified rows was too large. #23373
- `UPSERT` now properly reports the count of modified rows when `RETURNING` is not specified. #23373
- When adding a column, CockroachDB now verifies that the column is reference by no more than one foreign key. Existing tables with a column that is used by multiple foreign key constraints should be manually changed to have at most one foreign key per column. #25060
- Corrected the documentation for the `age()` built-in function. #25132
- Fixed a bug causing `PREPARE` to hang when run in the same transaction as a `CREATE TABLE` statement. #23816

### Performance Improvements

- Improved the performance of hash joins. #24577
- Aggregations are now streamed based on the ordering on the `GROUP BY` columns. #24113
- Some `SELECT`s with limits no longer require a second low-level scan, resulting in much faster execution. #24790

### Build Changes

- Build metadata, like the commit SHA and build time, is now properly injected into the binary when using Go 1.10 and building from a symlink. #25008

### Doc Updates

- Added a performance tuning guide for running CockroachDB in Kubernetes. #2896
- Clarified replication zone levels and added a warning about increasing the default replication factor without increasing the replication factor of system ranges. #2901
- Updated the `cockroach start` documentation to recommend decimal notation for flags that accept percentages. #3056
- Documented how to use the `server.shutdown.drain_wait` cluster setting to prevent a load balancer from sending client traffic to a node about to be shut down. #2903
- Documented the `intervalstyle` session variable. #2904
- Added documentation on the `SHOW EXPERIMENTAL_RANGES` statement. #2930
- Update the Node Map documentation to provide location coordinates for AWS, Azure, and Google Cloud. #2942
- Updated the `SPLIT AT` documentation to show how to split a table with a composite primary key. #2950
- Documented the `--temp-dir` flag for `cockroach start`. #2955
- Expanded the Production Checklist to recommend a higher replication factor when using local disks rather than a cloud providers' network-attached disks that are often replicated underneath the covers. #3001

### Contributors

This release includes 224 merged PRs by 37 authors. We would like to thank the following contributors from the CockroachDB community, with special thanks to first-time contributors Bob Potter, Karan Vaidya, dchenk, and phelanm.

- Bob Potter
- Brett Snyder
- Jingguo Yao
- Karan Vaidya
- Vijay Karthik
- dchenk
- phelanm

## v2.1.0-alpha.20180416

Release Date: April 16, 2018

### General Changes

- Prevent execution errors reporting a missing `libtinfo.so.5` on Linux systems. #24513
- A CockroachDB process will now flush its logs upon receiving `SIGHUP`.
- Added a cluster setting for HLC to be monotonic across restarts #23744
- Added a cluster setting for HLC to panic on clock jumps. #23717
- Statistics on the types of errors encountered are now included in diagnostics reporting. #22912

### Enterprise Edition Changes

- It is now possible to `RESTORE` views when using the `into_db` option. #24555
- Relaxed the limitation on using `BACKUP` in a mixed version cluster. #24493
- `BACKUP` and `RESTORE` are more resilient to non-URL-safe characters in query string authentication parameters. #24300
- The new `jobs.registry.leniency` cluster setting can be used to allow long-running import jobs to survive temporary node saturation. #23913
- Added configurable limits on the number of `BACKUP`/`RESTORE` requests each store will process. #23517

### SQL Language Changes

- Added configurable limits on the number of `IMPORT` requests each store will process. #23517
- The new `ALTER TABLE ... INJECT STATS` command injects table statistics from a JSON object (which can be obtained via `SHOW HISTOGRAM USING JSON`). #24488
- The new `SHOW STATISTICS USING JSON` variant of `SHOW STATISTICS` outputs table statistics as a JSON object; it can be used to extract statistics from clusters to reproduce issues. #24488
- `VIRTUAL` and `WORK` are no longer reserved keywords and can again be used as unrestricted names. #24491
- Added the `CANCEL SESSION` statement as well as an `IF EXISTS` variant to `CANCEL QUERY`. #23861
- Added a new `session_id` column to the result of `SHOW SESSIONS`. #23861
- Added support for the `information_schema.pg_expandarray()` function. #24422
- `DROP DATABASE` and `ALTER DATABASE ... RENAME` now prevent the removal of a database name if that database is set as the current database (`SET database` / `USE`) and the session setting `sql_safe_updates` is also set. #24246
- Added support for naming array types via the `_type` form and quoted type names. #24190
- Added the `quote_ident()` built-in function for increased PostgreSQL compatibility. #24190
- The behavior of `UPSERT` and `INSERT ... ON CONFLICT` when a `RETURNING` clause is present is now more consistent when an update touches the same row twice or more. This is a CockroachDB-specific extension. #23698
- Added the `statement_timeout` session variable. #23399
- The type determined for constant `NULL` expressions has been renamed to "unknown" for better compatibility with PostgreSQL. #23142
- Attempts to modify virtual schemas with DDL statements now fail with a clearer error message. #23044
- CockroachDB now recognizes the special syntax `SET SCHEMA <name>` as an alias for `SET search_path = <name>` for better compatibility with PostgreSQL. #22997
- Added support for `pg_sleep()` function. #22804
- Division by zero now returns the correct error code. #22912
- The GC of table data after a `DROP TABLE` now respects changes to the GC TTL interval specified in the relevant replication zone. #22774
- The full names of tables/views/sequences are now properly logged in the system event log. #22842
- `current_role` is now recognized as an alias for `current_user` for better compatibility with PostgreSQL. #22828
- The special identifier `current_catalog` is now supported as an alias for `current_database()` for better compatibility with PostgreSQL. #22828
- Added the `skip` option to the `IMPORT` command. #23466
- The service latency tracked for SQL statements now includes the wait time of the execute message in the input queue. #22880

### Command-Line Changes

- The `cockroach gen autocomplete` command can now generate zsh-compatible completion files by passing `zsh` as an argument. #24400
- Removed the `cockroach load csv` subcommand. #24319
- When `cockroach gen haproxy` is run, if an `haproxy.cfg` file already exists in the current directory, it now gets fully overwritten instead of potentially resulting in an unusable config. #24332
- The new `cockroach demo` command opens a SQL shell connected to a fully in-memory store, and an empty database named `demo`. It's useful for users or developers who wish to test out Cockroach's SQL dialect. #24259
- Replication zones now allow for specifying an ordered list of lease placement preferences. Whenever possible, CockroachDB will attempt to put the lease for a range on a store that satisfies the first set of constraints. If that's not possible, it'll attempt to put the lease on a store that satisfies the second set of constraints, and so on. If none of the preferences can be met, the lease will be placed as it is today. #23202
- Bracketed pastes are requested from the terminal emulator when possible. Pasting text into the interactive SQL shell is more reliable as a result. #23116
- The `cockroach sql` command now reminds you about `SET database = ...` and `CREATE DATABASE` if started with no current database. #23077
- Per-replica constraints in replication zones no longer have to add up to the total number of replicas in a range. If all replicas aren't specified, then the remaining replicas will be allowed on any store. #23057

### Admin UI Changes

- Removed explicit back links on Events and Nodes pages. #23904
- Added a new debug page to display all cluster settings. #24064
- While the Logs page loads, a spinner is now shown instead of a "no data" message. #23496
- The Logs page now uses a monospaced font, properly renders newlines, and packs lines together more tightly. #23496
- The Node Map now shows how long a node has been dead. #23255

### Bug Fixes

- Fixed a bug when using fractional units (e.g., `0.5GiB`) for the `--cache` and `--sql-max-memory` flags of `cockroach start`. #24381
- Fixed the handling of role membership lookups within transactions. #24284
- Fixed a panic around inverted index queries using the `->` operator. #24576
- `JSONB` values can now be cast to `STRING` values. #24518
- Fixed a panic caused by a `WHERE` condition that requires a column to equal a specific value and at the same time equal another column. #24506
- Fixed panics resulting from distributed execution of queries with OID types. #24431
- Fixed a bug where an expected transaction heartbeat failure aborted the transaction. #24134
- Fixed a bug causing index backfills to fail in a loop after exceeding the GC TTL of their source table. #24293
- Inverted index queries involving `NULL` are now properly handled. #24251
- Fixed a bug involving Npgsql and array values. #24227
- Fixed a panic caused by passing a `Name` type to `has_database_privilege()`. #24252
- On-disk checksums are now correctly generated during `IMPORT`. If there is existing data created by `IMPORT` that cannot be recreated, use `cockroach dump` to rewrite any affected tables. #24128
- Attempts to `RESTORE` to a time later than that covered by the latest `BACKUP` are now rejected. #23727
- Fixed a bug that could prevent disk space from being reclaimed. #23136
- Replication zone configs no longer accept negative numbers as input. #22870
- Fixed the occasional selection of sub-optimal rebalance targets. #23036
- The `cockroach dump` command is now able to dump sequences with non-default parameters. #23051
- `SHOW TABLES` is once again able to inspect virtual schemas. #22994
- The `CREATE TABLE .. AS` statement now properly supports placeholders in the subquery. #23006
- Fixed a bug where ranges could get stuck in an infinite "removal pending" state and would refuse to accept new writes. #22916
- Arrays now support `IS [NOT] DISTINCT FROM` operators. #23005
- Fixed incorrect index constraints on primary key columns on unique indexes. #22977
- Fixed a bug that prevented joins on interleaved tables with certain layouts from working. #22920
- The conversion from `INTERVAL` to `FLOAT` now properly returns the number of seconds in the interval. #22892
- Fix a panic cause sometimes by Flush protocol messages. #24119

### Performance Improvements

- Deleting many rows at once now consumes less memory. #22991
- Fewer disk writes are required for each database write, increasing write throughput and reducing write latency. #22317
- Reduced the amount of memory used during garbage collection of old versions. #24209
- Greatly improved the performance of the `DISTINCT` operator when its inputs are known to be sorted. #24438 #24148
- Write requests that result in no-ops are no longer proposed through Raft. #24345

### Build Changes

- Release binaries are now built with enough debug information to produce useful CPU profiles and backtraces. #24296

### Contributors

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

- Mahmoud Al-Qudsi
- Vijay Karthik (first-time contributor)