Version 5.6.0¶

Released on 2024-01-22.

Note

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

We recommend that you upgrade to the latest 5.5 release before moving to 5.6.0.

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

When restoring a snapshot, USERS and PRIVILEGES keywords, used to restore user management metadata has been replaced by USERMANAGEMENT, which dictates that all users and roles of the database together with their privileges are restored. Restoring USERS or PRIVILEGES separately is not possible anymore.

Deprecations ¶

USERS and PRIVILEGES keywords, used when restoring a snapshot, in order to restore users and privileges metadata respectively, have been deprecated. They have been replaced by USERMANAGEMENT and their behavior has been modified, please see Breaking Changes for details.

Changes ¶

SQL Statements ¶

Added support for explicit NULL column constraint definitions in CREATE TABLE statements.
Added support for named PRIMARY KEY constraint declaration.
Extended the EXPLAIN statement to support the VERBOSE option.
Added support for ALTER TABLE RENAME COLUMN statement.
Added support for CREATE ROLE statement. For details see Administration and Operations.
Added support for ALTER ROLE statement, which is identical to ALTER USER statement.
Added support for DROP ROLE statement, which is identical to DROP USER statement.

SQL Standard and PostgreSQL Compatibility ¶

Added a unknown type for serialization via the PostgreSQL wire protocol and to the pg_catalog.pg_type table. This should resolve compatibility issues with npgsql >= 8.0.
Added an empty pg_catalog.pg_depend table.
Changed pg_catalog.pg_roles table to be properly populated, as previously it was always returning 0 rows.
Added support of optional ESCAPE parameter to LIKE and ILIKE operators.

Data Types ¶

Introduced the BKD-tree-based indexing strategy for geo_shape.

Scalar and Aggregation Functions ¶

Updated the tdigest library which results in the percentile aggregation function to behave differently in some cases. For example, the following query used to return 4.5 but will now return 5.0:
```
SELECT percentile(x, 0.5) FROM generate_series(0, 9, 1) AS t (x) ;
```

Performance and Resilience Improvements ¶

Reduced the amount of disk reads necessary for ANALYZE operations.

Improved filter push-down for left/right outer joins when the joins are nested e.g.:

SELECT * FROM (SELECT * FROM a LEFT JOIN b ON a.a = b.b LEFT JOIN c ON b.b = c.c) t WHERE b > 1;

Now, the above query will result in the following logical plan

NestedLoopJoin[LEFT | (b = c)] (rows=unknown)
  ├ HashJoin[(a = b)] (rows=unknown)
  │  ├ Collect[doc.a | [a] | true] (rows=unknown)
  │  └ Collect[doc.b | [b] | (b > 1)] (rows=unknown)
  └ Collect[doc.c | [c] | true] (rows=unknown)

Administration and Operations ¶

Added database roles, which can be used to group privileges, and can be granted to users or other roles, thus enabling privileges inheritance.
Allowed un-indexed columns or columns without doc-values to be queryable.
Added the new options schema_rename_pattern, schema_rename_replacement, table_rename_pattern and table_rename_replacement to RESTORE SNAPSHOT to allow renaming tables during restore.
Added sys.roles table which contains all database roles defined in the cluster.
Added granted_roles column to sys.users table which lists the roles granted to a user, together with the user that granted each role.

User Interface ¶

Updated to Admin UI 1.24.7, which fixed a minor grammar issue on the Spanish version of the Help page, and optimized images with oxipng.