Release Notes for v20.1.0-alpha20191216

Hi all,

Today we released v20.1.0-alpha20191216.

To download the release click here

What’s New in CockroachDB v20.1.0-alpha20191216

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_node <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

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

UI changes

  • 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

Contributors

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

  • Adam Pantel (first-time contributor, CockroachDB team member)
  • Ananthakrishnan (first-time contributor)
  • Andrii Vorobiov (first-time contributor)
  • George Papadrosou
  • Jaewan Park
  • Roga Pria Sembada
  • Ryan Kuo (first-time contributor)
  • Vlad
  • georgebuckerfield (first-time contributor)