What's New in v2.2.0-alpha.20190114

January 14, 2019

Get future release notes emailed to you:

Thanks!

Downloads

Docker image

$ docker pull cockroachdb/cockroach-unstable:v2.2.0-alpha.20190114

Backward-incompatible changes

• Composite foreign key matching
• Mutation statements

Composite foreign key matching

We are changing the way composite foreign key matches are evaluated to match the default Postgres behavior. If your schema currently uses composite keys, it may require updates, since this change may affect your foreign key constraints and cascading behavior.

Prior to this change, we were matching composite keys with an incorrect implementation of the MATCH FULL method, and we are resolving this by moving all existing composite foreign key matches to a correct implementation of the MATCH SIMPLE method. Note that prior to this, there was no option for MATCH FULL or MATCH SIMPLE and all foreign key matching used the incorrect implementation of MATCH FULL.

For a more detailed explanation of the changes, see below.

For matching purposes, composite foreign keys can be in one of 3 states:

• Valid: Keys that can be used for matching foreign key relationships.
• Invalid: Keys that will not be used for matching.
• Unacceptable: Keys that cannot be inserted at all.

The MATCH FULL implementation we were using prior to this change allowed composite keys with a combination of NULL and non-null values. This meant that we matched on NULLs if a NULL existed in the referencing column, essentially treating NULLs as a valid value. This was incorrect, since MATCH FULL requires that if any column of a composite key is NULL, then all columns of the key must be NULL. In other words, either all must be NULL, or none may be.

To resolve this issue, all matches going forward will use the MATCH SIMPLE method (this matches the Postgres default). MATCH SIMPLE stipulates that:

• Valid composite keys may contain no NULL values, and will be used for matching.
• Invalid keys are keys with one or more NULL values, and will not be used for matching, including cascading operations.

For more information, see #32693.

Mutation statements

Mutation statements like UPDATE and INSERT no longer attempt to guarantee mutation or output ordering when an ORDER BY clause is present. It is now an error to use ORDER BY without LIMIT with the UPDATE statement. #33087

SQL language changes

• Added support for configuring authentication via an hba.conf cluster setting. #32892
• Added support for collecting table statistics on a default set of columns by calling CREATE STATISTICS with no columns specified. #32981
• Added the default_int_size session variable and sql.defaults.default_int_size cluster setting to control how the INT and SERIAL types are interpreted. The default value, 8, causes these types to be interpreted as aliases for INT8 and SERIAL8, which have been the historical defaults for CockroachDB. PostgreSQL clients that expect INT and SERIAL to be 32-bit values can set default_int_size to 4, which will cause INT and SERIAL to be aliases for INT4 and SERIAL4. Please note that due to issue #32846, SET default_int_size does not take effect until the next statement batch is executed. #32848
• When creating a replication zone, if a field is set to COPY FROM PARENT, the field now inherits its value from its parent zone, but any change to the field in the parent zone no longer affects the child zone. #32861
• Cockroach now supports specifying the matching method for composite foreign keys (a foreign key that includes more than one column) as either MATCH SIMPLE or MATCH FULL. MATCH SIMPLE remains the default. MATCH FULL differs from MATCH SIMPLE by not allowing the mixing of NULL and non-NULL values. Only all NULL values will not be used to validate a foreign key constraint check or cascading action. MATCH PARTIAL is still not supported. For more details see issue #20305 or https://www.postgresql.org/docs/11/sql-createtable.html. #32998
• The string_agg() aggregate function is now supported by the cost-based optimizer. #33172
• Added support for the pg_catalog introspection table pg_am for both PostgreSQL versions 9.5 and 9.6, which changed the table significantly. #33252
• Added foreign key checks to kv traces. #33328
• CockroachDB now defines columns domain_catalog, domain_schema and domain_name in information_schema.columns (using NULL values, since domain types are not yet supported) for compatibility with PostgreSQL clients. #33267
• Attempts to use some PostgreSQL built-in functions that are not yet supported in CockroachDB now cause a clearer error message, and also become reported in diagnostics reporting, if diagnostics reporting is enabled, so as to gauge demand. #33390
• CockroachDB now reports the name (not the value) of unsupported client parameters passed when setting up new SQL sessions in diagnostics reporting, if diagnostics reporting is enabled, to guage demand for additional support. #33264
• CockroachDB now collects statistics for statements executed "internally" (for system purposes). This is meant to facilitate performance troubleshooting. #32215
• CockroachDB now supports associating comments to SQL databases using PostgreSQL's COMMENT ON DATABASE syntax. This also provides proper support for pg's pg_catalog.pg_description and the obj_description() built-in function. #33057
• CockroachDB now supports associating comments to SQL table columns using PostgreSQL's COMMENT ON COLUMN syntax. This also provides proper support for pg's pg_catalog.pg_description and the col_description() built-in function. #33355
• Logical plans are now sampled and stored in statement statistics. #33020
• SHOW EXPERIMENTAL_RANGES is faster if no columns are requested from it, like in SELECT COUNT(*) FROM [SHOW EXPERIMENTAL_RANGES...]. #33463
• The new experimental_optimizer_updates cluster setting controls whether UPDATE and UPSERT statements are planned by the cost-based optimizer rather than the heuristic planner. Also note that when the setting is set, check constraints are not checked for rows skipped by the INSERT ... DO NOTHING clause. #33339

Admin UI changes

• The Statement Details page now shows sample logical plans for each unique fingerprint. #33483
• SQL queries issued internally by CockroachDB are now visible on the Statements page. They can be filtered using the application name. #32215

Bug fixes

• Fixed a bug where schema changes could get stuck for 5 minutes when executed immediately after a server restart. #32988
• Fixed a bug with returning dropped unique columns in DELETE statements with RETURNING. #33438
• Fixed a bug that could cause under-replication or unavailability in 5-node clusters and those using high replication factors. #32949
• Fixed an infinite loop in a low-level scanning routine that could be hit in unusual circumstances. #33063
• CockroachDB no longer reports under-replicated ranges corresponding to replicas that are waiting to be deleted. #32845
• Fixed a possible goroutine leak when canceling queries. #33130
• CHANGEFEEDs and incremental BACKUPs no longer indefinitely hang under an infrequent condition. #32909
• cockroach node status --ranges previously listed the count of under-replicated ranges in the ranges_unavailable column and the number of unavailable ranges in the ranges_underreplicated column. This fixes that mix-up. #32950
• Fixed a panic in the cost-based optimizer during the planning of some queries. #33183
• Cancel requests (via the pgwire protocol) now close quickly with an EOF instead of hanging but still do not cancel the request. #33202
• CockroachDB does not crash upon running SHOW SESSIONS, SHOW QUERIES, and inspections of some crdb_internal tables when certain SQL sessions are issuing internal SQL queries. #33138
• Updated the Zipkin library to avoid deadlock when stopping Zipkin tracing. #33287
• Fixed a panic that could result from not supplying a nullable column in an INSERT ON CONFLICT ... DO UPDATE statement. #33245
• Fixed pgwire binary decoding of decimal NaN and NULL in arrays. #33295
• The UPSERT and INSERT ON CONFLICT statements now properly check that the user has the SELECT privilege on the target table. #33358
• CockroachDB now errors with a fatal exit when data or logging partitions become unresponsive. Previously, the process would remain running, though in an unresponsive state. #32978
• Updated the contextual help for \h EXPORT in cockroach sql to reflect the actual syntax of the statement. #33460
• INSERT ON CONFLICT ... DO NOTHING no longer ignores rows that appear to be duplicate in the INSERT operands but are not yet present in the table. These are now properly inserted. #33320
• Prevented a panic with certain queries that use the statement source (square bracket) syntax. #33537
• Previously, CockroachDB did not consider the value of the right operand for << and >> operators, resulting in potentially very large results and excessive RAM consumption. This has been fixed to restrict the range of these values to that supported for the left operand. #33221

Performance improvements

• Cross-range disjunctive scans where the result size can be deduced are now automatically parallelized. #31616
• Limited the concurrency of BACKUP on nodes with fewer cores to reduce performance impact. #33277
• Index joins, lookup joins, foreign key checks, cascade scans, zig zag joins, and UPSERTs no longer needlessly scan over child interleaved tables when searching for keys. #33350

Doc updates

• Updated the Production Checklist with more current hardware recommendations and additional guidance on storage, file systems, and clock synch. #4153
• Expanded the SQLAlchemy tutorial to provide code for transaction retries and best practices for using SQLAlchemy with CockroachDB. #4142

Contributors

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

• Jaewan Park
• Jingguo Yao

Was this helpful?