5.6

Version 5.2.0

Released on 2023-01-12.

Note

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

We recommend that you upgrade to the latest 5.1 release before moving to 5.2.0.

A rolling upgrade from 5.1.x to 5.2.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

  • Removed support for the CRATE_INCLUDE environment variable from the bin/crate start script. Configuration of CrateDB should happen via the crate.yml, the CRATE_HEAP_SIZE environment variable and optionally CRATE_JAVA_OPTS.

  • Removed support for the -d and -p options from the bin/crate start script. It’s recommended to run CrateDB either via a container runtime like Docker, or via a service manager like systemd where these options are not required.

  • Subtraction of timestamps was returning their difference in milliseconds, but with result type TIMESTAMP which was wrong and led to issues with several PostgreSQL compliant clients. Instead of just fixing the result type, and change it to LONG, the subtraction of timestamps was changed to return an INTERVAL and be 100% compliant with PostgreSQL behaviour.

    Before:

    SELECT '2022-12-05T11:22:33.123456789'::timestamp - '2022-11-21T10:11:22.0012334'::timestamp;
+-----------------------+
| 1213871122::timestamp |
+-----------------------+
|            1213871122 |
+-----------------------+

    After:

    SELECT '2022-12-05T11:22:33.123456789'::timestamp - '2022-11-21T10:11:22.0012334'::timestamp;
+------------------------------+
| 'PT337H11M11.122S'::interval |
+------------------------------+
| 337:11:11.122                |
+------------------------------+

    To use the previous behaviour, timestamps can simply be cast to longs before subtracting them:

    SELECT (ts_end::long - ts_start::long) FROM test

    Alternatively, epoch can be extracted from the result of the subtraction:

    SELECT EXTRACT(epoch FROM ts_end - ts_start) FROM test

Changes

SQL Statements

  • Added support for adding multiple columns in a single ALTER TABLE ADD COLUMN statement.

  • Extended the syntax for CREATE VIEW to allow parenthesis surrounding the query.

SQL Standard And PostgreSQL Schema Compatibility

  • Bumped the version of PostgreSQL wire protocol to 11 since 10 has been deprecated.

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

  • Added a datestyle session setting that shows the display format for date and time values. Only the ISO style is supported. Optionally provided pattern conventions for the order of date parts (Day, Month, Year) are ignored.

  • Added support for SCROLL and backward movement to cursors. See DECLARE and FETCH.

  • Added the MAX_BY and MIN_BY aggregation functions

  • Added support for bit operators on integral and BIT types.

  • Added support for dollar quoted strings, see String Literal for further details.

  • cancel messages sent from a client via the PostgreSQL wire protocol are now internally forwarded to other nodes to support setups with load-balancers.

  • Added support for SUM() aggregations on INTERVAL type. e.g.:

    SELECT SUM(tsEnd - tsStart) FROM test

Scalar Functions

  • Added the concat(object, object) scalar function which combines two objects into a new object containing the union of their first level properties, taking the second object’s values for duplicate properties.

  • Added the parse_uri(text) scalar function which parses a valid URI string into an object containing the URI components, making it easier to query them.

  • Added the parse_url(text) scalar function which parses a valid URL string into an object containing the URL components, including parsed query parameters, making it easier to query them.

  • Added support for EXTRACT(field FROM interval). e.g.:

    SELECT EXTRACT(MINUTE FROM INTERVAL '49 hours 127 minutes')

Administration and Operations

  • Added attributes column to sys.nodes table to expose custom node settings.

  • Exposed the require, include and exclude routing.allocation settings per partition within information_schema.table_partitions.

  • Updated to Admin UI 1.24.1, which added Italian translations, updated some dependency packages across the board, and its tool chain.