-
24.5.1 protected1d250554 · ·
24.5.1 - 2024-09-04 SQL - SQL now infers sharding key from primary key, when the former is not specified in `create table` clause - SQL normalizes unquoted identifiers to lowercase instead of uppercase - SQL supports `LIMIT` clause - SQL supports `SUBSTR` function - SQL supports postgres [cast notation]: `expr::type` Pgproto - pgproto supports tab-completion for tables names in psql: ```sql postgres=> select * from _pico_<TAB> _pico_index _pico_plugin _pico_privilege _pico_routine _pico_table _pico_instance _pico_plugin_config _pico_property _pico_service _pico_tier _pico_peer_address _pico_plugin_migration _pico_replicaset _pico_service_route _pico_user ``` - pgproto supports explicit parameter type declarations in SQL via casting. This is helpful for drivers that do not specify parameters types, such as pq and pgx drivers for Go. In such drivers, users need to explicitly cast all query parameters. If the driver doesn't specify the type and the parameter isn't cast, the query will fail. For instance, running `SELECT * FROM t WHERE id = $1` in pgx will return "could not determine data type of parameter $1" error. To resolve this, users must specify the expected type of the parameter: `SELECT * FROM t WHERE id = $1::INT`. - Mutual TLS authentication for Pgproto. 1. Set `instance.pg.ssl` configuration parameter to `true` 1. Put PEM-encoded `ca.crt` file into instance's data directory along with `server.crt` and `server.key`. As a result pgproto server will only accept connection if client has presented a certificate which was signed by `ca.crt` or it's derivatives. If `ca.crt` is absent in instance's data directory, then client certificates are not requested and not validated. Configuration - Set up password for admin with `PICODATA_ADMIN_PASSWORD` environment variable - Multiline input is available in `picodata admin` and `picodata connect` - Set delimiter for multiline input with `\set delimiter my-shiny-delimiter` - Ability to change cluster properties via SQL `ALTER SYSTEM` command Fixes - Fix error "Read access to space '_raft_state' is denied" when executing a DML query on global tables Compatibility - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots - New index for the system table `_pico_replicaset` - `_pico_replicaset_uuid` - Changed `weight` column type to DOUBLE in `_pico_replicaset` - Option `picodata run --peer` now defaults to `--advertise` value. The previous was `localhost:3301`. This leads to the important behavior change. Running `picodata run --listen :3302` without implicit `--peer` specified now bootstraps a new cluster. The old behavior was to join `:3301` by default - DdlAbort raft log entry now contains the error information. - Add `promotion_vclock` column to `_pico_replicaset` table. - Add `current_config_version` column to `_pico_replicaset` table. - Add `target_config_version` column to `_pico_replicaset` table. - `Replicated` is no longer a valid instance state. RPC API - Removed stored procedure `.proc_replication_promote`. - New rpc entrypoint: `.proc_get_config` which returns the effective picodata configuration Lua API - Update `pico.LUA_API_VERSION`: `3.1.0` -> `4.0.0` - The following functions removed in favor of SQL commands and RPC API stored procedures: * `pico.change_password` -> [ALTER USER] * `pico.create_role` -> [CREATE ROLE] * `pico.create_user` -> [CREATE USER] * `pico.drop_role` -> [DROP ROLE] * `pico.drop_user` -> [DROP USER] * `pico.grant_privilege` -> [GRANT] * `pico.raft_get_index` -> [.proc_get_index] * `pico.revoke_privilege` -> [REVOKE] [ALTER USER]: https://docs.picodata.io/picodata/devel/reference/sql/alter_user/ [CREATE ROLE]: https://docs.picodata.io/picodata/devel/reference/sql/create_role/ [CREATE USER]: https://docs.picodata.io/picodata/devel/reference/sql/create_user/ [DROP ROLE]: https://docs.picodata.io/picodata/devel/reference/sql/drop_role/ [DROP USER]: https://docs.picodata.io/picodata/devel/reference/sql/drop_user/ [GRANT]: https://docs.picodata.io/picodata/devel/reference/sql/grant/ [REVOKE]: https://docs.picodata.io/picodata/devel/reference/sql/revoke/ [.proc_get_index]: https://docs.picodata.io/picodata/devel/architecture/rpc_api/#proc_get_index [cast notation]: https://docs.picodata.io/picodata/devel/reference/sql/cast/ -
24.4.1 protectedd3592e0d · ·
[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/
-
24.2.2 protectedc66c7dca · ·
[24.2.2] - 2024-04-03 - Fix panic after `CREATE USER alice; DROP ROLE alice;` - Fix SQL chain of joins without sub-queries `SELECT * FROM ... JOIN ... JOIN ...` - Fix SQL grammar support for `table.*` - Refine audit log [events][audit_events] list: remove 'new_database_created', add 'create_local_db', 'drop_local_db', 'connect_local_db', 'recover_local_db', 'integrity_violation' - Revise [picodata expel][cli_expel] command-line arguments and [tutorial][tutorial_expel] [audit_events]: https://docs.picodata.io/picodata/devel/reference/audit_events/ [cli_expel]: https://docs.picodata.io/picodata/24.2/reference/cli/#expel [tutorial_expel]: https://docs.picodata.io/picodata/24.2/tutorial/deploy/#expel
-
24.2.1 protected75c48e1a · ·
[24.2.1] - 2024-03-20 SQL - Introduce stored procedures: ```sql CREATE PROCEDURE my_proc(int, text) LANGUAGE SQL AS $$ INSERT INTO my_table VALUES($1, $2) $$; CALL my_proc(42, 'the answer'); SELECT * FROM my_table; ``` - The following new queries are supported: ``` CREATE PROCEDURE DROP PROCEDURE CALL PROCEDURE ALTER PROCEDURE ... RENAME TO GRANT ... ON PROCEDURE REVOKE ... ON PROCEDURE ALTER USER ... RENAME TO ``` Security - All inter-instance communications now occur under `pico_service` builtin user. The user is secured with a password in a file provided in `picodata run --service-password-file` command-line option - New requirements on password complexity — enforce uppercase, lowercase, digits, special symbols Implementation details - Make RPC API the main communication interface, see [Architecture — RPC API]. Lua API is deprecated and will be removed soon [Architecture — RPC API]: https://docs.picodata.io/picodata/devel/architecture/rpc_api/ Compatibility - System table `_pico_role` was deleted - System table `_pico_user` format changed, a new field `type` was added (_string_, `"user" | "role"`) - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots -
24.1.1 protected1bf69fa4 · ·
[24.1.1] - 2024-02-09 - Slightly change calendar versioning semantics, now it's `YY.MINOR` instead of `YY.0M`. CLI - New `picodata admin` command connects to an instance via unix socket under the admin account, see [Tutorial — Connecting — Admin console]. - New `picodata connect` implementation provides a console interface to the distributed SQL, see [Tutorial — Connecting — SQL console] - New option `picodata run --admin-sock` replaces `--console-sock` which is removed. The default value is `<data_dir>/admin.sock`. - New option `picodata run --shredding` enables secure removing of data files (snap, xlog). - New option `picodata run --log` configures the diagnostic log. - New option `picodata run --memtx-memory` controls the amount of memory allocated for the database engine. [Tutorial — Connecting — Admin console]: https://docs.picodata.io/picodata/24.1/tutorial/connecting/#admin_console [Tutorial — Connecting — SQL console]: https://docs.picodata.io/picodata/24.1/tutorial/connecting/#sql_console SQL - Global tables now can be used in the following queries: SELECT SELECT ... EXCEPT SELECT ... UNION ALL SELECT ... WHERE ... IN (SELECT ...) SELECT ... JOIN SELECT ... GROUP BY - `ALTER USER ... WITH LOGIN` can now unblock a user, who was blocked due to exceeding login attempts. Fixes - Revoke excess privileges from `guest` - Fix panic after `ALTER USER "alice" WITH NOLOGIN` - Repair `picodata connect --auth-type=ldap` - Picodata instances will no longer ignore raft entries which failed to apply. Instead now the raft loop will keep retrying the operation forever, so that admin has an opportunity to fix the error manually. Raft entries should never fail to apply, so if this happens please report a bug to us. Compatibility - System table `_pico_replicaset` now has a different format: the field `master_id` is replaced with 2 fields `current_master_id` and `target_master_id`. - All `.proc_*` stored procedures changed their return values. An extra top level array of 1 element is removed. - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots.
-
23.12.0 protectede49f56e4 · ·
[23.12.0] - 2023-12-08 Features - Clusterwide SQL is available via `\set language sql` in the interactive console with the support of global and sharded tables. - Implement request authorization based on access control lists (ACL). - Implement security audit log. - Implement automatic failover of replicaset leaders. - Introduce Web UI. It includes cluster status panel and replicaset list. Current status of Web UI is still beta. CLI - Interactive console is disabled by default. Enable it implicitly with `picodata run -i`. - Allow specifying `picodata connect [user@][host][:port]` format. It overrides the `--user` option. - Allow connecting interactive console over a unix socket `picodata run --console-sock`. Use `picodata connect --unix` to connect. Unlike connecting to a `--listen` address, console communication occurs in plain text and always operates under the admin account. - New option `picodata connect --password-file` allows supplying password in a plain-text file. - Block a user after 4 failed login attempts. - Enable the audit log with `picodata run --audit`. Lua API - Changes in terminology - all appearances of `space` changed to `table` - Update `pico.LUA_API_VERSION`: `1.0.0` -> `3.1.0` - New semantics of `pico.create_table()`. It's idempotent now. - `pico.create_table()` has new optional parameter: `engine`. Note: global spaces can only have memtx engine. - `pico.whoami()` and `pico.instance_info()` returns new field `tier` - Add `pico.sql()` - Add `pico.drop_table()` - Add `pico.create_user()`, `pico.drop_user()` - Add `pico.create_role()`, `pico.drop_role()` - Add `pico.grant_privilege()`, `pico.revoke_privilege()` - Add `pico.raft_term()` - Add `pico.change_password()` - Add `pico.wait_ddl_finalize()` - Make `pico.cas` follow access control rules - `pico.cas` now verifies dml operations before applying them - Change `pico.raft_log()` arguments - Make `opts.timeout` optional in most functions Compatibility - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots.