Version 5.0.0

Released on 2022-07-11.

Note

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

We recommend that you upgrade to the latest 4.8 release before moving to 5.0.0.

A rolling upgrade from 4.8.x to 5.0.0 is supported. Before upgrading, you should back up your data.

Warning

Tables that were created before CrateDB 4.x will not function with 5.x and must be recreated before moving to 5.x.x.

You can recreate tables using COPY TO and COPY FROM or by inserting the data into a new table.

Table of Contents

Breaking Changes

  • Updated Lucene to the version 9.2.0.

  • Changed the type of TIMESTAMP from TIMESTAMP WITH TIME ZONE to TIMESTAMP WITHOUT TIME ZONE to be compatible with the SQL standard.

    Tables created prior to updating to CrateDB 5.0 will remain unchanged. Tables created after updating to 5.0 while using the TIMESTAMP alias will use TIMESTAMP WITHOUT TIME ZONE. Change the type definition to TIMESTAMP WITH TIME ZONE if you want to preserve the old behavior.

  • Creating tables with soft deletes disabled is no longer supported. The setting soft_deletes.enabled will always be set to true and removed in CrateDB 6.0.

  • Removed deprecated node.max_local_storage_nodes setting. Set different path.data values to run multiple CrateDB processes on the same machine.

  • Removed deprecated delimited_payload_filter built-in token filter which has been renamed to delimited_payload since CrateDB 3.2.0.

  • Removed simplefs store type as a follow-up of its removal in Lucene 9.0.0. Refer to store types for alternatives.

  • Changed the default column name for the generate_series, regexp_matches and unnest table functions. Previously they’d default to col1 as column name. The behavior changed to use the table function’s name as column name unless the function returns more than one column. Some examples:

    1. cr> SELECT * FROM generate_series(1, 2);

      This used to return a column named col1 and now returns a column named generate_series.

    2. cr> SELECT * FROM generate_series(1, 2) as numbers;

      This used to return a column named col1 and now returns a column named numbers.

    To ease migration, we added a new setting (legacy.table_function_column_naming.) to opt-out of this new behavior.

  • Fields of type pg_node_tree in PostgreSQL in the pg_class and pg_index tables now return TEXT instead of ARRAY(OBJECT) for improved compatibility with PostgreSQL.

  • The single byte byte data type was falsely exposed as the char data type. It will now be correctly exposed as the special single byte "char".

Deprecations

  • Deprecated the azure discovery functionality. It only works for classic VM and Microsoft deprecated classic VM support on Azure.

Changes

SQL Statements

  • Added support for non-recursive WITH Queries (Common Table Expressions).

  • Added support for using subscript expressions on top of aliases within the ORDER BY clause. An example: SELECT percentile(x, [0.90, 0.95]) AS percentiles FROM tbl ORDER BY percentiles[1].

  • Added support for array element access on top of a subscript on an object array. An example: object_array['subelement'][1]

  • Added support for casts from bigint to regclass for improved compatibility with PostgreSQL clients.

  • Added support for FETCH [FIRST | NEXT] <noRows> [ROW | ROWS] ONLY clause as and alternative to the LIMIT clause.

  • Allowed LIMIT and OFFSET clauses to be declared in any order, i.e.: SELECT * FROM t LIMIT 10 OFFSET 5 or SELECT * FROM t OFFSET 5 LIMIT 10.

  • Added support for LIMIT NULL, LIMIT ALL, OFFSET NULL, OFFSET 10 ROW and OFFSET 10 ROWS.

  • Added support for using NULL literals in a UNION without requiring an explicit cast.

  • Changed UNION to support implicit casts if the type of expressions in the first relation don’t match the types in the second relation.

New Types

  • Added full support, incl. storage and indexing, for the fixed-length, blank padded CHARACTER(n) data type. Previously, the single byte byte was exposed as char which has been fixed, see Breaking Changes.

  • Added decimal type as alias to numeric

SQL Standard And PostgreSQL Schema Compatibility

  • Added typsend column to pg_catalog.pgtype table for improved compatibility with PostgreSQL.

  • Added primary key and check constraint column positions into conkey field of the pg_constraint table for improved compatibility with PostgreSQL.

  • Added pg_catalog.pg_tables and pg_catalog.pg_views tables for improved PostgreSQL compatibility.

  • Added identity columns information to information_schema.columns table for improved PostgreSQL compatibility. CrateDB does not support identity columns.

  • Added an empty pg_catalog.pg_shdescription table for improved PostgreSQL compatibility.

Scalar Functions

  • Added SUBSTRING to the non-reserved SQL keywords in order to support the generic function call syntax for improved PostgreSQL compatibility. Example: SUBSTRING('crate', 1, 3)

  • Added the concat_ws scalar function which allows concatenation with a custom separator.

  • Added the object_keys scalar function which returns the set of first level keys of an object.

  • Added the pg_get_serial_sequence scalar function for improved compatibility with PostgreSQL. CrateDB does not support sequences.

  • Added has_schema_privilege scalar function which checks whether user (or current user if not specified) has specific privilege(s) for the specific schema.

  • Added support for an optional boolean argument pretty at the pg_get_expr scalar function for improved PostgreSQL compatibility.

  • Added the pg_get_partkeydef scalar function for improved compatibility with PostgreSQL. Partitioning in CrateDB is different from PostgreSQL, therefore this function always returns NULL.

  • Moved the quote_ident(text) function to pg_catalog for improved compatibility with PostgreSQL.

Performance Improvements

  • Added an optimization to push down constant join conditions to the relation in an inner join, which results in a more efficient execution plan.

  • Added an optimization for array_column = [] queries.

Administration and Operations

  • Updated the bundled JDK to 18.0.1+10

  • Users with AL privileges can now run ANALYZE

  • Updated the Admin UI. It includes a new color, new colors and no longer loads resources like web fonts from external services.