What's New in v20.1.0-beta.1

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.

Get future release notes emailed to you:

Thanks!

Downloads

Docker image

$ docker pull cockroachdb/cockroach-unstable:v20.1.0-beta.1

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

Contributors

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

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

Was this helpful?