What's New in v20.1.0-alpha.20200123

This version of CockroachDB is no longer supported. For more details, see the Release Support Policy.

January 30, 2020


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

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

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

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

Get future release notes emailed to you:


Docker image

$ docker pull cockroachdb/cockroach-unstable:v20.1.0-alpha.20200123

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

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., instead of, 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


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

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