Version 5.5.0

Released on 2023-10-27

Note

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

We recommend that you upgrade to the latest 5.4 release before moving to 5.5.0.

A rolling upgrade from 5.4.x to 5.5.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

• Added CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP to non-reserved keywords.

• Validation for COPY FROM is now enforced. The validation parameter is deprecated and using it results in a deprecation message in the logs.

• UPDATE statements will continue on row failures instead of always showing an error. The affected rows are displayed by the resulting row count.

• Restricted Logical Replication of tables created on a cluster with higher major/minor version to a cluster with major/minor lower version.

Changes

SQL Statements

• Changed the CREATE TABLE analysis to be more strict. Before it was possible to use unqualified names in place of string literals to define values within WITH clauses. Now string literals are required.

  For example, instead of:

  my_column text index using fulltext with (analyzer = myanalyzer)
  

  It is necessary to use:

  my_column text index using fulltext with (analyzer = 'myanalyzer')
  

• Changed CREATE TABLE to allow defining CHECK constraints inline on sub-columns of object columns. Before it was necessary to use table check constraints.

• Added support for ALTER TABLE DROP COLUMN statement.

SQL Standard and PostgreSQL Compatibility

• Allowed statements that set standard_conforming_strings session setting to the default value (on).

• Added a new read-only session setting max_identifier_length.

• Added support for INTERVAL multiplication by integers.

• Added an empty pg_catalog.pg_event_trigger table.

Data Types

• Added support for explicit casts from strings in JSON list format to object[].

• Added a FLOAT_VECTOR type to store dense vectors of float values which can be searched using a k-nearest neighbour algorithm via a new KNN_MATCH scalar.

Scalar and Aggregation Functions

• Added a KNN_MATCH scalar.

• Added a (string FROM string) overload to substr('string', from, [ count ]) to extract a substring from a string that matches a POSIX regular expression pattern.

Performance and Resilience Improvements

• Limited the amount of errors returned by RETURN SUMMARY of COPY FROM to prevent running into circuit breaker errors due to memory constraints.

Administration and Operations

• Added an optimizer rule for cross-join elimination which will reorder the joined relations of a query to eliminate cross-joins e.g.:

  SELECT * FROM t1 CROSS JOIN t2 INNER JOIN t3 ON t3.z = t1.x AND t3.z = t2.y
  

  This query can be reordered to t1, t3, t2 to eliminate the cross-join between t1 and t2. This will result in the following logical plan:

   Eval[x, y, z]
     └ Join[INNER | (z = y)]
       ├ Join[INNER | (x = z)]
       │  ├ Collect[doc.t1 | [x] | true]
       │  └ Collect[doc.t3 | [z] | true]
       └ Collect[doc.t2 | [y] | true]
  
  This optimizer rule can be disabled with the session settings::
  
   SET optimizer_eliminate_cross_join = false
  

  Note that this setting is experimental, and may change in the future.

• Added support for renaming views via ALTER TABLE <view> RENAME TO <newName>.

• Changed permissions on sys.jobs and sys.jobs_log to allow users with the AL privileges to see entries from other users.

• Added a new memory.operation_limit cluster and session setting.

• Added support for endpoint and secondary endpoint to CREATE REPOSITORY for Azure storage.