v19.2.0-beta.20190930.md

title	toc	summary
What's New in v19.2.0-beta.20190930	true	Additions and changes in CockroachDB version v19.2.0-beta.20190930 since version v19.2.0-alpha.20190805

September 30, 2019

In addition to bug fixes and various general, enterprise, SQL, and command-line enhancements, this release includes several major highlights:

• Locality-aware backup and restore: You can now back up or restore a subset of a table's data according to zone configurations (e.g., send your EU backup partition to an EU cloud storage location).
• Import performance improvements: Internal improvements have been made to IMPORT to enable more efficient loading of large data sets.
• Show partitions using SQL: CockroachDB now supports the SHOW PARTITIONS statement, which can be used to identify partitions. It also enhances SHOW CREATE TABLE to show constraints as described.
• Productionize Vectorization Prototype: Queries are now automatically faster for users. This work expands upon the SQL Vectorization Prototype and allows it to be used in production. Note, this does not cover the long-tail of features necessary to use vectorization on all queries. Vectorized execution is turned on by default for a subset of queries and default back to DistSQL execution for unsupported queries
• Improve Streaming Behavior on JDBC Driver: PostgreSQL wire protocol cursors have been implemented. As a result, PGJDBC is able to use streaming behavior to return pagination related results.
• Improve partitioning user experience: CockroachDB now prevents users from specifying a non-partitioned secondary index on a partitioned table by default to improve performance. Users will have the ability to override this default as it may be necessary in some rare use cases. Additionally, users can now specify the same name for the partition for tables and for all corresponding indexes.
• Improve setting of zone configs via SQL: You can now use the CLI or SQL shell to retrieve locality. ALTER TABLE now returns an error when users select conflicting or impossible zone constraints due to user error.
• Make it easy to focus queries on partitioned data: CockroachDB now automatically limits a query to only the applicable partition without having to specify the particular partition for a geo-distributed table. This prevents the query from conducting a full table scan.
• Improve decommissioning, recommissioning, and recovery of killed nodes: Improved ability to change cluster membership during expected (decommissioning, recommissioning) and unexpected (killed nodes) events without impact stability or performance.
• Better insight into constraint conformance: You can now tell if your cluster is in a healthy state based on the status of conformance to constraints like zone configurations, lease preferences, and replica diversification.

Get future release notes emailed to you:

<script> hbspt.forms.create({ css: '', cssClass: 'install-form', portalId: '1753393', formId: '39686297-81d2-45e7-a73f-55a596a8d5ff', formInstanceId: 1, target: '.install-form-1' }); </script>

Downloads

Mac Linux Source

Docker image

{% include copy-clipboard.html %}

$ docker pull cockroachdb/cockroach-unstable:v19.2.0-beta.20190930

General changes

• Replicas are now added using a Raft learner and go through the normal Raft snapshot process to catch them up, eliminating technical debt. No user-facing changes are expected. #39034 {% comment %}doc{% endcomment %}
• Atomic replication changes are now enabled by default. #40464
• RocksDB INFO logs are now stored with CockroahDB debug logs. #40681

Backward-incompatible changes

• Quotes are no longer accepted inside unquoted CSV fields. #40424
• CONFIGURE ZONE statements now fail if the user does not have sufficient privileges. If the target is a system range, the system database, or a table in the system database, the user must have an admin role. For all other databases and tables, the user must have the CREATE privilege on the target database or table. This change might be backward-incompatible for users running scripted CONFIGURE ZONE statements with restricted permissions. To add the necessary permissions, use GRANT <privileges> or GRANT <roles> as a user with an admin role. For example, to grant a user the admin role, run GRANT admin TO <user>. To grant the CREATE privilege on a database or table, run GRANT CREATE ON [DATABASE | TABLE] <name> TO <user>. #40879

Enterprise edition changes

• Added support for client certificates for Kafka changefeed authentication. #39832
• Added support for locality-aware backups across multiple backup destinations. #39250 {% comment %}doc{% endcomment %}
• Added support for displaying creation statements of relations stored in a backups. #39323 {% comment %}doc{% endcomment %}
• Table statistics for the tables that are being backed up are now also backed up. #39644 {% comment %}doc{% endcomment %}
• Corrected behavior for unmatched quotes in enclosed fields. #40960
• Added auth=implicit setting, which removes access key requirement when listing S3 files for bulk I/O operations. #40836

SQL language changes

• Zone configurations now error out when they conflict with each other. #39081 {% comment %}doc{% endcomment %}
• Renamed SHOW EXPERIMENTAL_RANGES to SHOW RANGES command, as it is no longer an experimental feature. #39153 {% comment %}doc{% endcomment %}
• Added the SHOW RANGES FROM DATABASE command. #39194 {% comment %}doc{% endcomment %}
• Added partial support for row limits during portal execution in pgwire. #39085
• Added the SHOW LOCALITY command. #39058 {% comment %}doc{% endcomment %}
• Added the SHOW PARTITIONS command. #39053 {% comment %}doc{% endcomment %}
• CREATE VIEW now supports all queries supported by the optimizer, including those containing correlated subqueries. #39169 {% comment %}doc{% endcomment %}
• Mutations are no longer allowed inside views. #39169 {% comment %}doc{% endcomment %}
• Added the COLLATION FOR syntax and pg_collation_for() built-in function. #39198 {% comment %}doc{% endcomment %}
• Implemented the timezone() built-in and AT TIME ZONE syntax, which allows converting a timestamptz into a timezone at a specified timezone, and converting a timestamp into a timestamptz at a specified timezone. #39186 {% comment %}doc{% endcomment %}
• The new vectorize_row_count_threshold cluster setting controls when the vectorized execution engine is used. #39329 {% comment %}doc{% endcomment %}
• Added a VEC option to the EXPLAIN command, which shows detailed plan information for vectorized plans. #39280 {% comment %}doc{% endcomment %}
• Partition names can now be reused between different indexes on the same table. #39332 {% comment %}doc{% endcomment %}
• Columns without an explicit default value now support foreign keys with the SET DEFAULT action, in the same way as the SET NULL and SET DEFAULT NULL cases. #39136 {% comment %}doc{% endcomment %}
• Added SHOW JOB <jobid>, SHOW JOB <a_expr>, and SHOW JOBS <selectclause>. #39324 {% comment %}doc{% endcomment %}
• Added SHOW JOB WHEN COMPLETE <a_expr> and SHOW JOBS WHEN COMPLETE <selectclause>. #39457 {% comment %}doc{% endcomment %}
• IMPORT INTO now cleans up any imported rows if it fails. #39459
• Updated SHOW CREATE to display information about partition and table zone configurations. #39102 {% comment %}doc{% endcomment %}
• Explicitly closing portals over the wire after partial use is now supported. #39639
• Added progress tracking for new implementation of IMPORT. #39646 {% comment %}doc{% endcomment %}
• CockroachDB now supports an optional frame exclusion clause in the specification of window frames. #28262 {% comment %}doc{% endcomment %}
• Enabled collection of histograms by default during automatic statistics collection. #39743
• The force_retry() function, which previously required admin privileges, is now available to all users to test their transaction retry loops. #39246 {% comment %}doc{% endcomment %}
• When DROP CONSTRAINT is executed in a transaction on a validated constraint, it no longer takes effect immediately, and subsequent writes will now fail if they violate that constraint. This is to enforce consistency when dropping constraints asynchronously in the schema changer. #39648
• The ALTER PARTITION statement now supports applying a zone configuration to all the partitions of a table and its indexes that share the same partition name. The syntax for this is ALTER PARTITION <partition name> OF INDEX <table name>@*. #39750
• CockroachDB now supports the FOR UPDATE modifier on SELECT statements for compatibility with third-party tools. It has no effect because SERIALIZABLE transactions do not require explicit write locks to maintain consistency. However, this means that SELECT FOR UPDATE cannot be used as an advisory exclusive lock in CockroachDB, as used by some tools.#40206
• SHOW PARTITIONS now shows the full zone configuration statement used to configure the partition. #39814
• Added the pg_my_temp_schema() built-in for Postgres compatibility. #40194
• Added the overlaps (&&), contains (@>), and contained_by (<@) operators for arrays. #40194
• Added pg_get_function_identity_arguments() built-in. #40194
• Added the width_bucket() builtin function. #39263
• Added range size information to SHOW RANGES. #40281
• Added replica locality information to SHOW RANGES. #39813
• Reordered columns in SHOW RANGES output #40501
• SHOW CREATE TABLE now warns users if they have a partitioned table that does not contain zone configurations #40475
• Added the replication_constraint_stats, replication_stats, and replication_critical_localities system tables. These tables contain information about replication status, constraint conformance, and critical localities. #40625
• Common table expressions that are used a single time no longer create an optimization fence. #40673
• EXPLAIN now has additional output that shows if a query will run with the vectorized execution engine, and if the query will be distributed across nodes. #40652
• Column references are no longer allowed in ROWS and RANGE clauses in window functions. #40832
• Added the pg_function_is_visible and pg_get_function_result built-in functions. #40902
• Added parsing for FOR UPDATE [ OF tables ] form. #40962
• Mutations under UNION or UNION ALL are now disallowed; WITH should be used on top of the union operation instead. This restriction is temporary and will be lifted in a future release. #40975
• Support the syntax for oid wrapper arrays, like REGCLASS[]. #40949
• EXPLAIN ANALYSE can be used as an alias to EXPLAIN ANALYZE. #41090

Command-line changes

• cockroach demo now accepts a --nodes flag, which permits customization of the size of the demo cluster. The default remains at 1. #39088 {% comment %}doc{% endcomment %}
• cockroach demo now accepts a --demo-locality flag, which makes it possible to set node localities. #39454 {% comment %}doc{% endcomment %}
• cockroach demo without an argument is now equivalent to cockroach demo movr. The previous behavior (no pre-defined dataset) is still available via cockroach demo --empty. #39298 {% comment %}doc{% endcomment %}
• cockroach start now writes the client URL to the file specified via --listen-url-file as soon as the server is ready to accept connections. This also happens before the server detaches from the terminal when --background is specified. #39300 {% comment %}doc{% endcomment %}
• Using cockroach start without --join is now deprecated and this mode of execution will be removed in a later version of CockroachDB. Consider using cockroach start-single-node instead or combine cockroach start with cockroach init. #28495 {% comment %}doc{% endcomment %}
• The advisory/informative check performed by cockroach start on the validity of addresses contained in the node certificate is now relaxed to focus on the advertised node address, and to tolerate cases when the cert contains an IP address but a hostname is specified as advertised address. #39166
• Added locality information to the output of cockroach node status. #39059 {% comment %}doc{% endcomment %}
• Removed the deprecated cockroach zone CLI commands. Please use the equivalent SHOW ZONE CONFIGURATIONS and CONFIGURE ZONE commands in a SQL client. #39177 {% comment %}doc{% endcomment %}
• Added a new cockroach start-single-node command to start single-node clusters with replication disabled. #28495 {% comment %}doc{% endcomment %}
• The cockroach user sub-commands are now deprecated. Users and roles can be managed using SQL statements instead. This functionality will be removed in the next release. #39297 {% comment %}doc{% endcomment %}
• cockroach start now accepts a --cluster-name flag. If specified, it must match when a node connects to a cluster. This feature is meant for use in combination with the cluster ID, which is still checked in any case, to protect newly created nodes (that don't have a cluster ID yet) from joining the wrong cluster. To introduce a cluster name into an existing cluster without one, the new flag can be temporarily paired with --disable-cluster-name-verification. #39270 {% comment %}doc{% endcomment %}
• cockroach start now accepts a --sql-addr flag, which makes it possible to accept connections by clients on a separate TCP address and/or port number from the one used for intra-cluster (node-node) connections. This is aimed to enable firewalling client traffic from server traffic. #39305 {% comment %}doc{% endcomment %}
• cockroach workload run and cockroach workload init now support loading initial table data using IMPORT. #35312 {% comment %}doc{% endcomment %}
• cockroach workload now supports additional command-line parameters to customize the output, to facilitate the integration with third-party testing tools:
  • For tools that wish to observe the metrics more frequently than every second, a new flag --display-every is now supported, which can be used to specify the period between metric reports. This applies to both the JSON and textual output.
  • For tools that require a different output format than the default, a new --display-format argument is supported. For now only the formats "simple" (original output format) and "incremental-json" (RFC3339 timestamps, no summary row) are supported. #37929 {% comment %}doc{% endcomment %}
• Added \d, \dt, \du, \l metacommands to the cockroach sql interactive shell. #39141 {% comment %}doc{% endcomment %}
• Added default cluster locality topologies for multi-node cockroach demo clusters. #40221
• Errors in cockroach demo are no longer logged to the command-line interface. #40196
• Added \d client-side command with no arguments as an alias for \dt, for more ease of adoption by psql users. #40309
• Added a MovR workload to run with cockroach demo. #40271
• Added location information for default localities to populate Admin UI for cockroach demo. #40229
• cockroach demo now attempts to contact a license server to obtain a temporary license for demo usage. #40273
• cockroach demo now enables telemetry for demo clusters. You can opt out of telemetry by setting the COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING environment variable. For more information, see Diagnostics Reporting. #40273
• Deprecated the --init flag on the workload run command. Use workload init instead. #40426
• Added support for a new DELIMITED data format to the IMPORT statement. #40482
• Added an option for cockroach demo to automatically apply the geo-partitioned replicas topology to the movr dataset using the --geo-partitioned-replicas flag. #40355
• cockroach sql now supports the --watch flag. When used to specified a duration, the client will repeat the statement(s) specified by -e until an error occurs. This is intended for simple monitoring scenarios during development and testing. #40594
• Added support for redirecting license acquisition to a replacement server (e.g. a suitably configured HTTP proxy), with the environment variable COCKROACH_DEMO_LICENSE_URL. This enables cockroach demo with enterprise features in firewalled network environments. #41029

Admin UI changes

• Metrics about p90 and p99 percentiles of per-application average transaction time are now exposed in the Admin UI. #39429

Bug fixes

• Fixed a potential infinite loop in queries involving reverse scans. #39084
• Upgraded the grpc library to fix connection state management bug. #39041
• Properly initialize proposal quota tracking to prevent quota leak which can hang imports or other AddSSTable operations. #39135
• crdb_internal.ranges can now be used inside views. Note that such views can become invalid in future releases if crdb_internal.ranges changes. #39195
• Prevented panic due to recording into finished tracing spans caused by acknowledging an earlier failed re-proposal when a later re-proposal exists. #39203
• CockroachDB now properly computes the default port number if an IPv6 address is provided without port number with --locality-advertise-addr. #39334
• Unary negatives in constant arithmetic expressions are no longer ignored. #39245
• pg_catalog now shows correct type lengths for INT and FLOAT. #39337
• Fixed a rare panic (message: "raft group deleted") that could occur during splits. #39571
• Fixed a panic due to incorrect type inference of some ARRAY(...) expressions. #39469
• Zone configurations now propagate to non-gossiped system tables. #39638
• cockroach dump now works properly when handling INTERLEAVED tables, printing them outside of CREATE TABLE statements. #39486
• Fixed internal errors generated during execution of some complicated cases of correlated subqueries. #39818
• Using ORDER BY inside aggregation functions is now fully supported. #40242
• Lookup joins now spill to disk if the index lookups return more rows than can be stored in memory. #40208
• Fixed an internal planning error that could occur when a DISTINCT or GROUP BY expression was contained in a subquery. #40468
• Fixed an optimizer panic when building array access expressions. #40469
• Fixed faulty error when trying to delete a table and an unrelated index in the same transaction. #40516
• ordinal_position in information_schema.columns now matches pg_attribute.attnum after a column is dropped. #40485
• Fixed fatal error with message replica descriptor of local store not found. #39796
• Intents in a read's uncertainty interval are now considered as uncertain as committed values. This removes the potential for stale reads when a causally dependent transaction runs into unresolved intents from a causal ancestor. #40600
• Fixed a cluster migration bug that could occur in clusters running a mixture of v19.1 and v19.2. This bug was signaled by messages of the form: X at key /Table/54 failed: unexpected value: ... Affected clusters should be updated to 19.2 or, if 19.1 is preferred, recreated from backup. #40464
• Fixed behavior of SERIAL columns on mixed-case tables when experimental_serial_normalization=sql_sequence. #40965
• width_bucket function no longer errors on 0-length arrays #40626
• Fixed an error that could occur when the optimizer creates a plan with a lookup semi- or anti-join nested inside another join. #40669
• Fixed a memory corruption error in the optimizer that could cause stack overflow. #40644
• The cockroach commands are now able to connect to a server with the environment variable COCKROACH_URL. #40824
• Index hints used with DELETE and UPDATE are now taken into consideration. #40715
• Fixed an issue with creating table indexes when the server is running on Windows and the store is on a file system that does not support hard links (e.g. FAT32 or network share) #40875
• Restore correct result generation for queries with index disjunctions on tables with multiple column families. #40898
• Fixed a stack overflow that could occur via use of WITH. #40937
• Detailed crash reports now always appear in the main log file. If SQL audit reporting or statement logging was activated, crash reports (i.e. "panic messages") could previously be reported in the wrong file. #40941
• Users must now have admin privileges to cancel, pause, or resume jobs. #40978
• CockroachDB now properly removes excess secondary log files (SQL audit logging, statement execution logging, and RocksDB events). #41020
• CockroachDB now avoids the internal re-use of Replica objects to fix the following crashes:
• #38772 "found rXXX:{-} [, next=0, gen=0?] in place of the RHS"
• #39796 "replica descriptor of local store not found in right hand side of split"
• #40470 "split trigger found right-hand side with tombstone"
• #40257 "snapshot widens existing replica, but no replica exists for subsumed key" #40892
• Fixed panic in cockroach workload movr run. #41072
• Resolved an issue with server hanging on cockroach quit on Windows. #40994
• The cockroach zip, cockroach node and cockroach user cockroach commands now work properly when the defaultdb database is manually dropped and the connection URL does not specify a database. cockroach user is deprecated in 19.2. #41121
• cockroach workload init ycsb now works with --data-loader=import. #41071
• Fixed bug where zone configurations on indexes could leak into configurations on partitions. #41089

Performance improvements

• Sped up file-writing during bulk-ingestion. #37528
• Snapshots sent between replicas are now applied more performantly and use less memory. #38932
• Raft entries no longer wait to be applied to the RocksDB storage engine before signaling their success to clients, they now only wait until they are committed in their Raft log. #38954
• Adjusted load-based splitting QPS threshold to avoid over-splitting. #39687
• IMPORT now writes unsorted data directly, reducing IMPORT's overhead (but potentially increasing RocksDB compaction overhead). #39487
• Improved performance for mutation statements with RETURNING that are not inside an explicit transaction. #40871

Doc updates

• Added an Oracle migration guide, which guides users through the process of converting their schema and then exporting their data so they can load it into CockroachDB via IMPORT. #5902
• Added an overview of MovR, CockroachDB's fictional vehicle-sharing dataset and application, and updated several SQL pages and examples to use the built-in MovR dataset, for example, Learn CockroachDB SQL. #5075, #5216
• Added a tutorial on how to use EXPLAIN to identify and resolve common SQL performance problems. #5178

Contributors

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

• Arseni Lapunov
• Elliot Courant
• HyeonGyu Lee (Vazrupe) (first-time contributor)
• Jaewan Park
• Kevin Barbour (first-time contributor)
• Niels Hofmans (first-time contributor)
• Shishir Jaiswal (first-time contributor)
• Taufiq Rahman (first-time contributor)
• Zeming YU (first-time contributor)
• 贾德星 (first-time contributor)