What's New in v20.1

CockroachDB v20.1 is a required Regular Release.

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

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

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

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

• Be sure to also review the Release Support Policy.

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

Get future release notes emailed to you:

Thanks!

v20.1.17

Release Date: May 17, 2021

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

Bug fixes

• Fixed a race condition where read-write requests during replica removal (for example, during range merges or rebalancing) could be evaluated on the removed replica. These cases would not result in data being written to persistent storage, but could result in errors that should not have been returned. #64604
• Fixed a bug where users of OSS builds of CockroachDB would see "Page Not Found" when loading the DB Console. #64126

v20.1.16

Release Date: May 10, 2021

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

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

Bug fixes

• Fixed a correctness bug which caused partitioned index scans to omit rows where the value of the first index column was NULL. This bug was present since v19.2.0. #64050
• Fixed a bug where multiple concurrent invocations of cockroach debug zip could lead to cluster instability. This bug was present since CockroachDB v20.1. #64086
• Previously, passwords in SQL statements in telemetry updates and crash reports were anonymized as *****. Passwords are now anonymized as '*****' so that the SQL statements do not result in parsing errors when executed. #64347
• Fixed a race condition where read-only requests during replica removal (e.g., during range merges or rebalancing) could be evaluated on the removed replica, returning an empty result. #64377
• Fixed a bug where encryption-at-rest metadata was not synced and might become corrupted during a hard reset. #64498

Performance improvements

• The Raft processing goroutine pool's size is now capped at 96. This was observed to prevent instability on large machines (32+ vCPU) in clusters with many ranges (50k+ per node). #64568
• The Raft scheduler now prioritizes the node liveness Range. This was observed to prevent instability on large machines (32+ vCPU) in clusters with many ranges (50k+ per node). #64568

Contributors

This release includes 9 merged PRs by 10 authors.

v20.1.15

Release Date: April 26, 2021

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

Bug fixes

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

Contributors

This release includes 7 merged PRs by 6 authors.

v20.1.14

Release Date: April 19, 2021

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

Bug fixes

• Fixed a bug where some import failures would cause tables to stay OFFLINE when they should have been brought back to PUBLIC. #61481
• Fixed a bug where an invalid tuple comparison using ANY was causing an internal error. CockroachDB now returns "unsupported comparison operator". #61725
• Changed the behavior of the kv.closed_timestamp.target_duration cluster setting when set to 0. This would make follower reads more aggressive instead of disabling them. Setting kv.closed_timestamp.target_duration to 0 will now disable routing requests to follower replicas. #62442
• Fixed a bug where RESTORE using AS OF SYSTEM TIME of tables that included foreign key constraints from backups created by v19.x or earlier would lead to malformed schema metadata. #62493
• Fixed an internal error that could occur during planning when a query used the output of the RETURNING clause of an UPDATE, and one or more of the columns in the RETURNING clause were from a table specified in the FROM clause of the UPDATE (i.e., not from the table being updated). #62964
• Dropping a foreign key that was added in the same transaction no longer triggers an internal error. This bug has been present since at least v20.1. #62881
• Fixed a bug where index backfill data might have been missed by BACKUP in incremental backups. #63303

Performance improvements

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

Contributors

This release includes 16 merged PRs by 13 authors.

v20.1.13

Release Date: March 15, 2021

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

Bug fixes

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

Contributors

This release includes 10 merged PRs by 10 authors.

v20.1.12

Release Date: February 16, 2021

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

Bug fixes

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

v20.1.11

Release Date: January 25, 2021

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

SQL language changes

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

Bug fixes

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

Contributors

This release includes 24 merged PRs by 17 authors.

v20.1.10

Release Date: December 21, 2020

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

Backward-incompatible changes

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

SQL language changes

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

Command-line changes

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

Bug fixes

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

Performance improvements

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

Backward-compatible change

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

Contributors

This release includes 34 merged PRs by 16 authors.

v20.1.9

Release Date: December 1, 2020

Enterprise edition changes

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

SQL language changes

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

Bug fixes

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

v20.1.8

Release Date: October 21, 2020

Bug fixes

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

v20.1.7

Release Date: October 12, 2020

Security updates

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

General changes

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

Bug fixes

• Fixed a bug where columns used in an index which contained the columns of a foreign key as a prefix could lead to all of the index columns being set to NULL or the default value on cascade. #54543
• Fixed a bug causing servers to crash with the message "committed txn with writeTooOld". Versions below 20.1.4 are susceptible to this bug. Versions 20.1.4+ will not crash, but instead print messages to the log files. #54282
• Fixed a rare bug which can lead to index backfills failing in the face of transaction restarts. #54859
• Fixed a race condition propagating post-query metadata in the vectorized execution engine. #55168
• Fixed a bug causing nodes running version 20.1 to not be able to serve follower reads in mixed-version clusters running versions 19.2 and 20.1. #55089
• The first timing column in the trace.txt file collected as part of a statement diagnostics bundle has been fixed.

Contributors

This release includes 19 merged PRs by 8 authors.

v20.1.6

Release Date: September 24, 2020

SQL language changes

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

Bug fixes

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

Contributors

This release includes 27 merged PRs by 15 authors.

v20.1.5

Release Date: August 31, 2020

SQL language changes

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

Bug fixes

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

Performance improvements

• Queries no longer block during planning if cached table statistics have become stale and the new statistics have not yet been loaded. Instead, the stale statistics are used for planning until the new statistics have been loaded. This improves performance because it prevents latency spikes that may occur if there is a delay in loading the new statistics. #52191

Contributors

This release includes 31 merged PRs by 15 authors.

v20.1.4

Release Date: August 3, 2020

General changes

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

Enterprise edition changes

• RESTORE now has a new option skip_missing_sequence_owners that must be supplied when restoring only the table/sequence that was previously a sequence owner/owned by a table. Additionally, a bug causing ownership relationships to not be remapped after a restore has been fixed. #51629

SQL language changes

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

Command-line changes

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

Bug fixes

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

Performance improvements

• Introduced a new server.consistency_check.max_rate setting, expressed in bytes/seconds, to throttle the rate at which CockroachDB scans through the disk to perform a consistency check. This control is necessary to ensure smooth performance on a cluster with large node sizes, in the 10TB+ range. #50066

Contributors

This release includes 41 merged PRs by 18 authors.

v20.1.3

Release Date: June 29, 2020

Security updates

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

Enterprise edition changes

• Full cluster restore is now more resilient to transient transaction retry errors during restore. #50004
• The default flush interval for changefeeds that do not specify a RESOLVED option is now 5s instead of 200ms to more gracefully handle higher-latency sinks. #50251

SQL language changes

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

Bug fixes

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

Performance improvements

• CockroachDB now optimizes reading of files when doing backups and storage-level compactions of files. This should deliver a performance improvement for some read-heavy operations on an IOPS-constrained device. #50105
• Limited SELECT statements now do a better job avoiding unnecessary contention with UPDATE and SELECT FOR UPDATE statements. #50119
• Improved the optimizer's estimation of the selectivity of some filters involving a disjunction (OR) of predicates over multiple columns. This results in more accurate cardinality estimation and enables the optimizer to choose better query plans in some cases. #50470

Build changes

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

Doc updates

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

Contributors

This release includes 22 merged PRs by 15 authors.

v20.1.2

Release Date: June 17, 2020

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

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

Bug fixes

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

Doc updates

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

Contributors

This release includes 30 merged PRs by 20 authors.

v20.1.1

Release Date: May 26, 2020

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

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

Backward-incompatible changes

• The copy of system and crdb_internal tables extracted by cockroach debug zip is now written using the TSV format (inside the .zip file), instead of an ASCII-art table. #48094
• Updated the textual error and warning messages displayed by cockroach quit. #47692
• cockroach quit now prints progress details on its standard error stream, even when --logtostderr is not specified. Scripts that wish to ignore this output can redirect the standard error stream. #47692
• CockroachDB v20.1 introduced an experimental new rule for the --join flag causing it to prefer SRV records, if present in DNS, to look up the peer nodes to join. However, it is also found to cause disruption in in certain deployments. To reduce this disruption and UX surprise, the feature is now gated behind a new command-line flag --experimental-dns-srv which must now be explicitly passed to cockroach start to enable it. #49129
• Added a new cluster setting, server.shutdown.lease_transfer_wait, that allows you to configure the server shutdown timeout period for transferring range leases to other nodes. Previously, the timeout period was not configurable and was set to 5 seconds, and the phase of server shutdown responsible for range lease transfers would give up after 10000 attempts of transferring replica leases away. The limit of 10000 attempts has been removed, so that now only the maximum duration server.shutdown.lease_transfer_wait applies. #47692

General changes

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

Enterprise edition changes

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

SQL language changes

• The pg_collation, pg_proc, pg_database, and pg_type tables in the pg_catalog database no longer require privileges on any database in order for the data to be visible. #48080, #48765
• Histogram collection with CREATE STATISTICS is no longer supported on columns with type array. Only row count, distinct count, and null count are collected for array-type columns. #48343
• ROLLBACK TO SAVEPOINT is no longer permitted after miscellaneous internal errors. #48305
• Fixed an issue with optimizing subqueries involving set operations that can prevent queries from executing. #48680
• CockroachDB now correctly reports the type length for the char type. #48642
• The RowDescription message of the wire-level protocol now contains the table ID and column ID for each column in the result set. These values correspond to pg_attribute.attrelid and pg_attribute.attnum. If a result column does not refer to a simple table or view, these values will be zero, as they were before. The message also contains the type modifier for each column in the result set. This corresponds to pg_attribute.atttypmod. If it is not available, the value is -1, as it was before. #48748, #49087

Command-line changes

• cockroach debug zip now tries multiple times to retrieve data using SQL if it encounters retry errors and skips over fully decommissioned nodes. It also supports two command-line parameters --nodes and --exclude-nodes. When specified, they control which nodes are inspected when gathering the data. This makes it possible to focus on a group of nodes of interest in a large cluster, or to exclude nodes that cockroach debug zip would have trouble reaching otherwise. Both flags accept a list of individual node IDs or ranges of node IDs, e.g., --nodes=1,10,13-15. #48094
• Client commands such as cockroach init and cockroach quit now support the --cluster-name and --disable-cluster-name-verification flags in order to support running them on clusters that have been configured to use a cluster name. Previously it was impossible to run such commands against nodes configured with the --cluster-name flag. #48016
• It is now possible to drain a node without shutting down the process, using the cockroach node drain command. This makes it easier to integrate with service managers and orchestration: it now becomes safe to issue cockroach node drain and then separately stop the service via a process manager or orchestrator. Without this new mode, there is a risk to misconfigure the service manager to auto-restart the node after it shuts down via quit, in a way that's surprising or unwanted. The new command node drain also recognizes the new --drain-wait flag. #47692
• The time that cockroach quit waits client-side for the node to drain (remove existing clients and push range leases away) is now configurable via the command-line flag --drain-wait. Note that separate server-side timeouts also apply separately. Check the server.shutdown.* cluster settings for details. #47692
• The commands cockroach quit and cockroach node drain now report a "work remaining" metric on their standard error stream. The value reduces until it reaches 0 to indicate that the graceful shutdown has completed server-side. An operator can now rely on cockroach node drain to obtain confidence of a graceful shutdown prior to terminating the server process. #47692
• The default value of the parameter --drain-wait for cockroach quit has been increased from 1 minute to 10 minutes, to give more time for nodes with thousands of ranges to migrate their leases away. #47692
• Added support for list cert with certificates which require --cert-principal-map to pass validation. #48177
• Added support for the --cert-principal-map flag in the cockroach cert, cockroach sql, cockroach init, and cockroach quit commands. #48177
• Made --storage-engine sticky (i.e., resolve to the last used engine type when unspecified) even when specified stores are encrypted at rest. #49073

Admin UI changes

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

Bug fixes

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

Performance improvements

• Disabled the Go runtime block profile by default which results in a small but measurable reduction in CPU usage. The block profile has diminished in utility with the advent of mutex contention profiles and is almost never used during performance investigations. #48153
• The cleanup job which runs after a primary key change to remove old indexes, which blocks other schema changes from running, now starts immediately after the primary key swap is complete. This reduces the amount of waiting time before subsequent schema changes can run. #47818
• Histograms used by the optimizer for query planning now have more accurate row counts per histogram bucket, particularly for columns that have many null values. The histograms also have improved cardinality estimates. This results in better plans in some cases. #48626, #48646
• Fixed a bug that caused a simple schema change to take more than 30s. #48621
• Queries run via the vectorized execution engine are now processed faster, with most noticeable gains on the queries that output many rows. #48732
• Reduced time needed to run a backup command when it is built on a lot of previous incremental backups. #48772

Doc updates

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

v20.1.0

Release Date: May 12, 2020

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

Summary

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

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

CockroachCloud

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

• CockroachCloud pricing is now available on our website.

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

Core features

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

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

Enterprise features

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

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

Backward-incompatible changes

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

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

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

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

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

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

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

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

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

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

Known limitations

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

Education

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

v20.1.0-rc.2

Release Date: April 21, 2020

SQL language changes

• The new ability to specify TIME/TIMETZ and INTERVAL precision is available only after finalizing an upgrade to v20.1. Previously, it was allowed to specify precision for these data types in clusters with mixed v19.2 and v20.1 nodes, but nodes running v19.2 would not respect the precision. #47438

Command-line changes

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

Bug fixes

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

Contributors

This release includes 15 merged PRs by 10 authors.

v20.1.0-rc.1

Release Date: April 14, 2020

Security updates

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

General changes

• Transactions reading a lot of data behave better when exceeding the memory limit set by the kv.transaction.max_refresh_spans_bytes cluster setting. Such transactions now attempt to resolve the conflicts they run into instead of being forced to always retry. Increasing kv.transaction.max_refresh_spans_bytes should no longer be necessary for most workloads. #46803
• Before upgrading from v19.2 to v20.1, it is best practice to make sure there are no schema changes in progress. However, if any are still running when the upgrade is started, note that they will stop making progress during the upgrade and will run to completion once the upgrade has been finalized. #47073

Enterprise edition changes

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

SQL language changes

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

Command-line changes

• The cockroach commands that internally use SQL, including cockroach sql and cockroach demo, now can connect to a server using a unix datagram socket. The syntax for this is --url 'postgres://user@?host=/path/to/directory?port=NNNN'. #47007
• The cockroach workload command now sets its application_name based on the chosen workload. #46546
• The cockroach debug zip command now creates valid zip files even if some of its requests encounter an error. #46634
• The cockroach demo command now displays a TCP-based connection URL and unix datagram socket for each node of a demo cluster. #46935
• It is now possible to pre-configure the secure mode of cockroach demo using the COCKROACH_INSECURE environment variable like other client commands. #46959
• When running cockroach demo in secure mode, the generated SQL URL now embeds the password so that commands using this URL can run without a request for password. #47007
• The SQL URL generated by cockroach demo no longer requires TLS client certificates in particular directory locations. #47007
• The new experimental client-side command demo ls for cockroach demo displays the connection parameters for every node in a demo cluster. #47007
• The client-side commands specific to cockroach demo, starting with demo, are now advertised in the output of ?. Note that this feature is currently experimental. #47007

Admin UI changes

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

Bug fixes

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

Performance improvements

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

Build changes

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

Doc updates

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

v20.1.0-beta.4

Release Date: March 30, 2020

Security updates

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

Enterprise edition changes

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

SQL language changes

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

Command-line changes

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

Bug fixes

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

Doc updates

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

v20.1.0-beta.3

Release Date: March 25, 2020

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

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

Backward-incompatible changes

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

Security updates

Identity management changes

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

Authentication changes

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

Authorization changes

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

Security bug fixes

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

Enterprise edition changes

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

Storage changes

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

SQL changes

SQL language additions

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

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

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

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

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

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

• Added the every aggregate function. #46059

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

Query planning changes

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

Execution changes

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

Updates to schema change / DDL statements

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

Updates to Bulk I/O statements

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

Changes to background job management

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

Updates to APIs and introspection

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

Command-line changes

Changes to operational workflows

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

Configuration changes

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

Usability improvements

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

Change to troubleshooting facilities

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

Admin UI changes

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

Performance improvements

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

Doc updates

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

v20.1.0-beta.2

Release Date: March 2, 2020

In addition to various updates, enhancements, and bug fixes, this v20.1 beta release includes the ability to log slow SQL queries. By setting the sql.log.slow_query.latency_threshold cluster setting, each node of your cluster will log queries that exceed the specified service latency to a new file called cockroach-sql-slow.log.

Security updates

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

General changes

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

Enterprise edition changes

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

SQL language changes

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

Command-line changes

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

Admin UI changes

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

Bug fixes

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

Performance improvements

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

Doc updates

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

v20.1.0-beta.1

Release Date: February 17, 2020

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

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

Backward-incompatible changes

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

Security updates

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

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

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

  #43848

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

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

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

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

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

General changes

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

Enterprise edition changes

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

SQL language changes

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

Command-line changes

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

Admin UI changes

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

Bug fixes

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

Performance improvements

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

Build changes

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

Doc updates

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

v20.1.0-alpha.20200123

Release Date: January 30, 2020

In addition to security updates and various enhancements and bug fixes, this v20.1 alpha release includes some major highlights:

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

Security updates

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

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

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

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

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

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

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

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

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

• Users GRANTing and REVOKEing admin roles must be members of the admin role with ADMIN OPTION. This check was previously bypassed. #41218

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

General changes

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

Enterprise edition changes

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

SQL language changes

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

Command-line changes

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

Admin UI changes

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

Bug fixes

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

Performance improvements

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

Build changes

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

Doc Updates

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

v20.1.0-alpha20191216

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

Backward-incompatible changes

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

General changes

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

Enterprise edition changes

• RESTORE now supports the restoration of empty databases. #42005

SQL language changes

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

Command-line changes

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

Admin UI changes

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

Bug fixes

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

Performance improvements

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

Build changes

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

v20.1.0-alpha.20191118

Release Date: November 18, 2019

General changes

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

Backward-incompatible changes

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

Enterprise edition changes

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

SQL language changes

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

Command-line changes

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

Bug fixes

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

Performance improvements

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