[24.4.1] - 2024-06-21 Pgproto - Allow connecting to the cluster using PostgreSQL protocol, see [Tutorial — Connecting — Pgproto]: ``` picodata run --pg-listen localhost:5432 psql CREATE TABLE ... INSERT ... SELECT ... ``` - The feature is currently in beta. It does NOT automatically imply complete compatibility with PostgreSQL's extensive features, SQL syntax, etc [Tutorial — Connecting — Pgproto]: https://docs.picodata.io/picodata/24.4/tutorial/connecting/#pgproto SQL - New commands [CREATE INDEX] and [DROP INDEX] - Support `SELECT ... ORDER BY` - Support `SELECT ... UNION ... SELECT ... UNION` - Support common table expressions (CTE) - Support [CASE][sql_case] expression - New function [TRIM][sql_trim] - New functions `TO_CHAR`, `TO_DATE` - Allow `PRIMARY KEY` next to column declaration - Support `SET ...` and `SET TRANSACTION ...` but they are ignored - Support inferring not null constraint on primary key columns - Support `INSERT`, `UPDATE`, `DELETE` in global tables [CREATE INDEX]: https://docs.picodata.io/picodata/24.4/reference/sql/create_index/ [DROP INDEX]: https://docs.picodata.io/picodata/24.4/reference/sql/drop_index/ [sql_case]: https://docs.picodata.io/picodata/24.4/reference/sql/case/ [sql_trim]: https://docs.picodata.io/picodata/24.4/reference/sql/trim/ Configuration - Provide a new way of configuring instances via config file in a yaml format, see [Reference — Configuration file]. It extends the variety of previously available methods — environment variables and command-line arguments - New option `picodata run --config` provides a path to the config file - New option `picodata run -c` overrides single parameter using the same naming - New command `picodata config default` generates contents of the config file with default parameter values - New RPC API `.proc_get_config` returns the effective configuration [Reference — Configuration file]: https://docs.picodata.io/picodata/24.4/reference/config/ Compatibility - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots - System table `_pico_table` format changed, the field `distribution` now is a map, a new field `description` was added (_string_) - System table `_pico_tier` format changed, a new field `can_vote` was added (_boolean_) - Rename all indexes adding a prefix containing the table name, e.g. `name` -> `_pico_table_name` - System table `_pico_index` format changed, now `parts` are stored by field name instead of an index, other fields were rearranged significantly, see [Architecture — System tables] - Rename RPC APIs related to SQL: dispatch_query -> proc_sql_dispatch; execute -> proc_sql_execute [Architecture — System tables]: https://docs.picodata.io/picodata/24.4/architecture/system_tables/