Picodata 24.5 is out!

The Picodata development team is pleased to announce the release of Picodata 24.5, our core software product. The list of new and improved features this time gathers around three main areas: SQL development, Pgproto module and configuration improvement.

The new Picodata release offers much-improved SQL console capabilities, with new commands added and existing commands and keywords improved. These include:

• SQL now infers sharding key from primary key, when the former is not specified in CREATE TABLE clause. This means you can omit DITRIBUTED (which was previously mandatory to use) and get your table sharded by PRIMARY KEY.
• SELECT now supports LIMIT clause. For instance, the SELECT * FROM t LIMIT 2 query will limit the resulting table to as few as 2 rows from t.
• Also, SELECT can be used with the SUBSTR function to get a substring from a string
• SQL normalizes unquoted identifiers to lowercase instead of uppercase

We carry on with pushing the Pgproto module to new heights and make it more convenient to connect and work with Picodata clusters via PostgreSQL-compatible clients. In particular, Pgproto supports tab-completion for tables names in psql (version 16 or newer):

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.

Finally in the Pgproto part, we implemented mutual TLS authentication for Pgproto. Use it this way: enable SSL on the server side and put the certificate files in the instance’s working directory. As a result, the Pgproto server will only accept a connection if a client has presented a signed certificate.

Picodata configuration has become better in several ways:

• a select number of clusterwide parameters can be controlled via the ALTER SYSTEM command in SQL
• you can set the administrator password (amongst the oher ways) by exporting the PICODATA_ADMIN_PASSWORD shell variable
• both the user’s (picodata connect) and the administrator’s (picodata admin) consoles have full suport for multiline input and respect your custom-set delimiter (\set delimiter my-shiny-delimiter)

See the full list of changes in the CHANGELOG for this release.

Get a hand on Picodata 24.5 by downloading the pre-built packages for supported Linux distributions in the download section at our site.

You can find installation advisory as well as documentation for installing software, creating instances, cluster configuration and deployment at our dedicated documentation site.

Send your feedback and get in touch with Picodata developers in our Telegram chat at @picodataen or @picodataru.