-
25.1.1 protected38230552 · ·
[25.1.1] - 2025-02-21 Configuration - New alter system parameters - `sql_vdbe_opcode_max` and `sql_motion_row_max`. - Default config name is `picodata.yaml`. - The default names for replicaset and instance are now generated using the following patterns: - ReplicaSet Name: `{tier_name}_{replicaset_number_in_this_tier}` - Instance Name: `{tier_name}_{replicaset_number_in_tier}_{instance_number_in_replicaset}` - New parameters for Vinyl configuration: `bloom_fpr`, `max_tuple_size`, `page_size`, `range_size`, `run_count_per_size`, `run_size_ratio`, `read_threads`, `write_threads` and `timeout`. - New parameter for Memtx configuration: `max_tuple_size`. - `plugin_dir` parameter is renamed to `share_dir` (--plugin-dir -> --share-dir, PICODATA_PLUGIN_DIR -> PICODATA_SHARE_DIR, config: instance.plugin_dir -> instance.share_dir) - The following parameters has been moved from configuration file to `_pico_db_config` system table: - checkpoint_interval - checkpoint_count - max_concurrent_messages - `max_heartbeat_period` removed from `_pico_db_config` - CLI parameter `service_password_file` removed from arguments. To set a password for connecting to the cluster, create a file named `.picodata-cookie` in the instance's directory and store the password there. - Major renaming of parameters from _pico_db_config: - `max_concurrent_messages` renamed to `iproto_net_msg_max` - `password_min_length` renamed to `auth_password_length_min` - `password_enforce_uppercase` renamed to `auth_password_enforce_uppercase` - `password_enforce_lowercase` renamed to `auth_password_enforce_lowercase` - `password_enforce_digits` renamed to `auth_password_enforce_digits` - `password_enforce_specialchars` renamed to `auth_password_enforce_specialchars` - `max_login_attempts` renamed to `auth_login_attempt_max` - `auto_offline_timeout` renamed to `governor_auto_offline_timeout` - `max_pg_statements` renamed to `pg_statement_max` - `max_pg_portals` renamed to `pg_portal_max` - `snapshot_chunk_max_size` renamed to `raft_snapshot_chunk_size_max` - `snapshot_read_view_close_timeout` renamed to `raft_snapshot_read_view_close_timeout` - `cluster_wal_max_size` renamed to `raft_wal_size_max` - `cluster_wal_max_count` renamed to `raft_wal_count_max` - `listen`, `advertise` parameters are renamed to `iproto_listen`, `iproto_advertise` - Added scopes to all parameters from `_pico_db_config`. There are two scopesright now - `tier` and `global`. Parameters with scope `tier` can be different on different tiers. For example `ALTER SYSTEM SET parameter_with_scope_tier FOR ALL TIERS` or `ALTER SYSTEM SET parameter_with_scope_tier FOR TIER default`. Parameters with scope `global` are the same on each instance. - `instance.shredding` moved to `cluster` section and is now defined at bootstrap only. CLI - `picodata expel` takes instance uuid instead of instance name. - `picodata expel` now doesn't allow expelling Online instances and replicaset masters by default. New `--force` flag can be used to forcefully expel an Online instance which will shutdown once it finds out it got Expelled. - String cells are now output without double quotes during SELECT. - `picodata connect` and `picodata admin` return a non-zero exit code for file inputs with errors. - `picodata --version` now provides verbose output, including the build type (static or dynamic) and the build configuration (release or debug) - New command `picodata status` which prints all current members of the cluster and their status. Pgproto - Support LDAP authentication method Compatibility - Added unique index on column `uuid` for `_pico_instance` table. IDs of `_pico_instance_raft_id` and `_pico_instance_replicaset_name` now equals to 2 and 3. - New special command `\set delimiter enter` to change the default delimiter to EOL (End Of Line). Introduced a new inner prompt prefix to indicate when input is waiting for a delimiter. EOF is now treated as a delimiter when reading files. - New field `_pico_property.system_catalog_version` representing version of a system catalog. It may not be changed at every release, so this is not autoincrementing value. - From now on, when joining a cluster, an instance's version must be the same as the cluster's version or one minor version higher. For example, if the cluster's version is 25.1, only instances with versions 25.1 or 25.2 can join. - In the `_pico_property` table, there is a new field called `cluster_version`, which shows the global version of the cluster. - In the `_pico_instance` table, there is a new field called `picodata_version` that displays the version of the executable running on the instance. - The global `cluster_version` is updated by the governor only when every instance in the cluster has been upgraded to the new minor version. - System table `_pico_peer_address` has a new column `conncetion_type` that indicates the connection type of the peer. It can be `iproto` or `pgproto`. - Global rename - Config File Changes: - `data_dir` renamed to `instance_dir` - Source Code Changes: - `data_dir` renamed to `instance_dir` - Environment Variable Changes: - `PICODATA_DATA_DIR` renamed to `PICODATA_INSTANCE_DIR` - PgProto is now enabled by default and listens at `127.0.0.1:4327`. - Prevented non-admin users with the DROP TABLE privilege from dropping system tables. - SQL query parameters renamed: - `vdbe_max_steps` to `sql_vdbe_opcode_max` - `vtable_max_rows` to `sql_motion_row_max` - `SCALAR` and `NUMBER` data types are not supported anymore. RPC API - `.proc_expel` and `.proc_expel_redirect` takes instance uuid instead of instance name. Fixes - It's no longer possible to execute DML queries for tables that are not operable - Fixed panic on user/role creation when max user number was exceeded - `picodata expel` used to finish before the instance got finally expelled. Now it will block until the instance is completely expelled, or the timeout is exceeded. - Fixed a bug, where we would allow to create more than 2 versions of the same plugin (and panic when compiled in debug mode). - `DROP PLUGIN` now leaves the plugin's data in the database if `WITH DATA` wasn't specified. Previously we would return an error instead. SQL - SQL support `SUBSTRING` function - SQL support window functions -
24.6.1 protectede1d07a14 · ·
24.6.1 - 2024-10-25 Configuration - New feature `tier` - a group of instances with own replication factor. Tiers can span multiple failure domains and a single cluster can have multiple tiers. Going forward it will be possible to specify which tier a table belongs to. - Default authentication method changed from `CHAP-SHA1` to `MD5` both for user creation and in connect CLI. This change affects new user creation and all system users (except the `pico_service` user), as a command-line interface of `picodata connect` and `picodata expel`. Also, default schema version at cluster boot is now `1`, not `0` as it was previously. Connection via `Pgproto` no longer requires additional manual step to change the authentication method. However if you use `iproto` the admin will have to manually change the authentication type. - Support human numbers to configure memtx.memory, vinyl.memory and vinyl.cache parameters. Supported suffixes: K, M, G, T, 1K = 1024 (e.g picodata run --memtx-memory 10G) Plugins - New ability to write custom plugins for picodata. Plugins are supposed to be written in Rust using our official SDK crate: `picodata-plugin`. Plugins are compiled to shared objects which are loaded directly into picodata process. Plugins have in-process access to various picodata API's. Plugins do not use special sandboxing mechanisms for maximum performance. Thus require special care during coding. Make sure you do not install plugins from untrusted sources. - Plugins are able to use dedicated RPC subsystem for communication inside the cluster. - Plugins are able to provide migrations written in SQL to define objects they need for operation. - Plugins are cluster aware, they're deployed on entire cluster. It is possible to specify particular `tier` for plugins to run on. - Plugins are managed with SQL API, i e `CREATE PLUGIN` and such. For details consult with SQL reference. CLI - New `picodata connect` and `picodata expel` argument `--timeout` for specifying the timeout for address resolving operation. - Replace the use of `localhost` with `127.0.0.1` in `picodata run --listen` default value and everywhere across documentation and examples to reduce ambiguity. RPC API - New rpc entrypoint: `.proc_get_vshard_config` which returns the vshard configuration of tier. - Unused types of IPROTO requests have been forbidden for the sake of integrity. Here is the list (all start with "IPROTO_" prefix): INSERT, REPLACE, UPDATE, DELETE, CALL_16, UPSERT, NOP, PREPARE, BEGIN, COMMIT, ROLLBACK. In future verisons, SELECT will also be forbidden. Compatibility - The current version is NOT compatible with prior releases. It cannot be started with the old snapshots - Order of columns in `_pico_service_route` table has changed. - Global rename - Config File Changes: - `cluster_id` renamed to `name` - `instance_id` renamed to `name` - `replicaset_id` renamed to `replicaset_name` - Source Code Changes: - `cluster_id` renamed to `cluster_name` - `instance_id` renamed to `instance_name` - `instance.instance_uuid` renamed to `instance.uuid` - `replicaset.replicaset_uuid` renamed to `replicaset.uuid` - `replicaset.replicaset_name` renamed to `replicaset.name` - `replicaset_id` renamed to `replicaset_name` - corresponding tables' columns and indexes changed accordingly - Environment Variable Changes: - `PICODATA_CLUSTER_ID` renamed to `PICODATA_CLUSTER_NAME` - `PICODATA_INSTANCE_ID` renamed to `PICODATA_INSTANCE_NAME` - `PICODATA_REPLICASET_ID` renamed to `PICODATA_REPLICASET_NAME` - Default delimiter in `picodata connect` and `picodata admin` cli sessions is now `;` Lua API - Update `pico.LUA_API_VERSION`: `4.0.0` -> `5.0.0` - The following functions removed in favor of SQL commands and RPC API stored procedures: * `pico.create_table` -> [CREATE TABLE] * `pico.drop_table` -> [DROP TABLE] * `pico.raft_read_index` -> [.proc_read_index] * `pico.raft_wait_index` -> [.proc_wait_index] [CREATE TABLE]: https://docs.picodata.io/picodata/devel/reference/sql/create_table/ [DROP TABLE]: https://docs.picodata.io/picodata/devel/reference/sql/drop_table/ [.proc_read_index]: https://docs.picodata.io/picodata/devel/architecture/rpc_api/#proc_read_index [.proc_wait_index]: https://docs.picodata.io/picodata/devel/architecture/rpc_api/#proc_wait_index SQL - SQL supports `LIKE` operator - SQL supports `ILIKE` operator - SQL supports `lower` and `upper` string functions - Execute option `sql_vdbe_max_steps` was renamed to `vdbe_max_steps` - SQL supports `SELECT` statements without scans: `select 1` - `CREATE TABLE`, `CREATE INDEX`, `CREATE PROCEDURE`, `CREATE USER` and `CREATE ROLE` support `IF NOT EXISTS` option - `DROP TABLE`, `DROP INDEX`, `DROP PROCEDURE`, `DROP USER` and `DROP ROLE` support `IF EXISTS` option - `CREATE TABLE`, `CREATE INDEX`, `CREATE PROCEDURE`, `DROP TABLE`, `DROP INDEX` and `DROP PROCEDURE` support WAIT APPLIED (GLOBALLY | LOCALLY) options, allowing users to wait for operations to be committed across all replicasets or only on the current one - EXPLAIN estimates query buckets Fixes - Fixed bucket rebalancing for sharded tables - Fixed panic when applying snapshot with the same index -
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