Version 4.2.0

Released on 2020-07-07.

Note

If you are upgrading a cluster, you must be running CrateDB 4.0.2 or higher before you upgrade to 4.2.0.

We recommend that you upgrade to the latest 4.1 release before moving to 4.2.0.

A rolling upgrade from 4.1.7+ to 4.2.0 is supported.

Before upgrading, you should back up your data.

Breaking Changes

  • Changed the logic how the array_unique scalar function infers the argument types. Previously if the arguments had a different type it used the type of the first argument. For example:

    1. cr> select array_unique(['1'], [1.0, 2.0]);
    2. +---------------------------------+
    3. | array_unique(['1'], [1.0, 2.0]) |
    4. +---------------------------------+
    5. | ["1", "1.0", "2.0"]             |
    6. +---------------------------------+

    This logic has been changed to instead use a type precedence logic to be consistent with how other functions behave:

    1. cr> select array_unique(['1'], [1.0, 2.0]);
    2. +------------+
    3. | [1.0, 2.0] |
    4. +------------+
    5. | [1.0, 2.0] |
    6. +------------+

  • Bulk INSERT INTO ... VALUES (...) statements do not throw an exception any longer when one of the bulk operations fails. The result of the execution is only available via the results array represented by a row count for each bulk operation.

  • Numeric literals fitting into the integer range are now treated as integer literals instead of bigint literals. Thus a statement like select 1 will return an integer column type. This shouldn’t be an issue for most clients as the HTTP endpoint uses JSON for serialization and PostgreSQL clients usually use a typed getLong.

    A consequence of this change is that arithmetic with literals used in statements will also happen within the integer range, and it can lead to an integer overflow if the result does not fit into the integer range. For example, a calculation like 365 * 24 * 60 * 60 * 1000 needs to be explicitly cast into the bigint range to avoid an integer overflow. This can be done like this: 365::bigint * 24 * 60 * 60 * 1000. If working with timestamps it is recommended to instead use the more readable interval type instead: '1 year'::interval.

  • In the PostgreSQL Wire Protocol, ReadyForQuery messages now contain the IN TRANSACTION or FAILED TRANSACTION indicator based on previous BEGIN and COMMIT SQL statements. Before this change, the status always defaulted to IDLE. This change may have the side-effect that an explicit rollback call in a client library will result in an unsupported ROLLBACK statement.

Deprecations

  • The index.warmer.enabled setting has been deprecated and doesn’t have any effect anymore.

Changes

Administration

  • The JavaScript user-defined function language is now enabled by default in the CrateDB enterprise edition.

  • Added the optimizer session setting to configure query optimizer rules.

  • Include the bundled version of OpenJDK (14.0.1+7) into the CrateDB built. It means that CrateDB doesn’t rely on JAVA_HOME of the host system any longer.

  • Increased the default interval to detect keystore or truststore changes to five minutes.

  • Added a tables column to the sys.snapshots table which lists the fully qualified name of all tables contained within the snapshot.

  • Limit the output of COPY FROM RETURN SUMMARY in the presence of errors to display up to 50 line_numbers to avoid buffer pressure at clients and to improve readability.

SQL Standard and PostgreSQL compatibility improvements

  • Added scalar function CURRENT_TIME, that returns the system’s time as microseconds since midnight UTC, at the time the SQL statement is handled. The actual return type is the new data type timetz.

  • Added new type time with time zone, a.k.a timetz, which is to be used as return type for time related functions such as the future current_time.

  • Added the oidvector data type which is used in some pg_catalog tables.

  • Added the Regproc alias data type that is used to reference functions in the pg_catalog tables.

  • Added the varchar(n) and character varying(n) types, where n is an optional length limit.

  • Added the server_version_num and server_version read-only session settings.

  • Added the pg_catalog.pg_proc table.

  • Added the pg_catalog.pg_range table.

  • Added the pg_catalog.pg_enum table.

  • Added the information_schema.character_sets table.

  • Added pg_type columns: typbyval, typcategory, typowner, typisdefined, typrelid, typndims, typcollation, typinput, typoutput, and typndefault for improved PostgreSQL compatibility.

  • Added support for JOIN USING, e.g. SELECT * FROM t1 JOIN t2 USING (col), an alternative to JOIN ON, when the column name(s) are the same in both relations.

  • Added entries for primary keys to pg_class and pg_index table.

  • Added support for record subscript syntax as alternative to the existing object subscript syntax.

  • Added support for using columns of type long inside subscript expressions (e.g., array_expr[column]).

  • Made generate_series addressable by specifying the pg_catalog schema explicitly. So, for example, both generate_series(1, 2) and pg_catalog.generate_series(1, 2) are valid.

  • Added support for the PostgreSQL notation to refer to array types. For example, it is now possible to use text[] instead of array(test).

  • Added support for GROUP BY operations on analysed columns of type text.

Functions and operators

New statements and clauses

  • Extended the supported syntax for SET TRANSACTION.

  • Added the DISCARD statement.

  • Added the CHECK constraint syntax, which specifies that the values of certain columns must satisfy a boolean expression on insert and update.

  • Introduced new optional RETURNING clause for INSERT and UPDATE to return specified values from each row written.

Performance improvements

  • Optimized <column> IS NOT NULL queries.