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
andPRIVILEGES
keywords, used to restore user management metadata has been replaced byUSERMANAGEMENT
, which dictates that all users and roles of the database together with their privileges are restored. RestoringUSERS
orPRIVILEGES
separately is not possible anymore.
Deprecations¶
USERS
andPRIVILEGES
keywords, used when restoring a snapshot, in order to restore users and privileges metadata respectively, have been deprecated. They have been replaced byUSERMANAGEMENT
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 thepg_catalog.pg_type
table. This should resolve compatibility issues withnpgsql
>= 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 returning0
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 thepercentile
aggregation function to behave differently in some cases. For example, the following query used to return4.5
but will now return5.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
andtable_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
.