Announcing VillageSQL Server 0.0.8
VillageSQL Server 0.0.8 is now available. This release is about using extensions from applications: through prepared statements, with text in other character sets, and with the server protected when an extension function throws an exception.
Highlights of this release include the following:
- Functions that take custom types work in prepared statements.
- Extensions built with the 0.0.8 SDK can tell raw bytes from text.
- When a function in an extension built with the 0.0.8 SDK throws an exception, only that statement fails, and the server keeps running.
Custom types in prepared statements
Many applications keep values out of the SQL text. They send the query once as a prepared statement and pass the values separately, and most MySQL connectors can work this way. In 0.0.8, functions that take custom types work in prepared statements:
mysql> PREPARE re FROM 'SELECT complex_real(?) AS re';
mysql> SET @v = '(1.5,-2.5)';
mysql> EXECUTE re USING @v;
+------+
| re |
+------+
| 1.5 |
+------+
Vector search works the same way: a nearest-neighbor query with its search value passed as a parameter uses the vector index. Custom indexes are still a preview feature, behind vsql_allow_preview_extensions=ON.
Running a prepared statement many times also costs less. For many extension functions, the server no longer prepares a statement that calls them again at every EXECUTE. For a prepared nearest-neighbor query, that repeat preparation took about 10% of the server's time.
Text and raw bytes
A password hash is raw bytes, and an email address is text. MySQL keeps the two apart, and extensions built with the 0.0.8 SDK can too. A hash reaches the extension byte for byte, and text reaches it in one encoding, whatever character set your client uses.
vsql-crypto 0.0.8 uses the new BINARY type. Its hashes come back as binary data, ready to store in a VARBINARY column:
mysql> SELECT CHARSET(digest('hello', 'sha256')) AS charset;
+---------+
| charset |
+---------+
| binary |
+---------+
mysql> CREATE TABLE hashes (id INT PRIMARY KEY, h VARBINARY(32));
mysql> INSERT INTO hashes VALUES (1, digest('hello', 'sha256'));
mysql> SELECT id, HEX(h) FROM hashes;
+----+------------------------------------------------------------------+
| id | HEX(h) |
+----+------------------------------------------------------------------+
| 1 | 2CF24DBA5FB0A30E26E83B2AC5B9E29E1B161E5C1FA7425E73043362938B9824 |
+----+------------------------------------------------------------------+
Custom types read text in other character sets too. Here the COMPLEX type from an example extension reads values from a ucs2 column:
mysql> CREATE TABLE src (id INT PRIMARY KEY, s VARCHAR(40) CHARACTER SET ucs2);
mysql> INSERT INTO src VALUES (2, '(3.0,4.0)');
mysql> CREATE TABLE points (id INT PRIMARY KEY, c COMPLEX);
mysql> INSERT INTO points SELECT id, s FROM src;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, c FROM points;
+----+-------+
| id | c |
+----+-------+
| 2 | (3,4) |
+----+-------+
When an extension throws an exception
Extensions run inside the server process. When a function in an extension built with the 0.0.8 SDK throws a C++ exception, VillageSQL ends that one statement with an error, and the server keeps running. This test extension throws on purpose:
mysql> SELECT vsql_exceptions_test.exception_add(10, 20);
ERROR 3200 (HY000): VDF error in function 'exception_add': VDF threw an exception (vsql_exceptions_test::exception_add)
mysql> SELECT 'server still up' AS status;
+-----------------+
| status |
+-----------------+
| server still up |
+-----------------+
Crash-safe extension installs
If the server crashes in the middle of INSTALL EXTENSION, it does not leave a half-written extension behind. If an extension file is cut short, INSTALL EXTENSION rejects it. And if an extension update fails at restart, the server rolls back only that extension.
For extension authors
These features come from protocol 5 of the interface between the server and its extensions, which is the stable protocol in 0.0.8. If you built an extension with the development SDK of 0.0.4 through 0.0.7, rebuild it with the stable 0.0.8 SDK.
- Function arguments and results can be
BINARY, raw bytes that the server never converts, andSTRINGarguments always arrive asutf8mb4. - A function's
preruncheck can see the values of constant arguments, including a value bound to a ? in a prepared statement, so a function can accept aNULLliteral without accepting every string. - A C++ exception that escapes a function becomes a SQL error on the statement.
- A custom type can register a
from_binaryfunction besidefrom_string, to accept values in a binary form, such as a vector of packed floats. The server uses it for a _binary string, a VARBINARY column, or a hex literal. - A function like
tvector_dim(?)takes its type parameters from the value bound atEXECUTE.
Upgrading from 0.0.7
0.0.8 does not load extensions built for protocol 4, so if your 0.0.7 server has one installed, the server will not start after you upgrade. To check whether any of your installed extensions use protocol 4, run this query on your 0.0.7 server:
SELECT EXTENSION_NAME FROM INFORMATION_SCHEMA.EXTENSION_REGISTRATION WHERE NEGOTIATED_PROTOCOL = 4;
To upgrade, dump your data with mysqldump, install your extensions on a new 0.0.8 data directory, and then load the dump.
Bundled extensions
The install script, the Docker image, and the release tarballs include these extensions with VillageSQL Server 0.0.8. To use one, run INSTALL EXTENSION with its name.
- vsql-ai 0.0.8
- vsql-boolean 0.0.3
- vsql-crypto 0.0.8
- vsql-cube 0.0.8
- vsql-fuzzystrmatch 0.0.2
- vsql-http 0.0.4
- vsql-network-address 0.0.8
- vsql-oauth2 0.0.5
- vsql-rest 0.0.11
- vsql-trgm 0.0.5
- vsql-uuid 0.0.9
You can find a list of all available extensions here. To build a new extension or port an existing PostgreSQL extension, you can use an agent skill.
Special thanks
Thanks to @kash2104, who made the server print <error decoding value> for a custom value it cannot decode, and cleaned up how the server reads raw custom values.
Thanks to @jobala, whose work lets an extension function's prerun check tell a NULL literal from other values, and to @askdba, who added a MyVector profile to vector-dev-bench, our tool for measuring in-database vector indexes.
There's more in this release than fits here. The release notes carry the per-PR detail. Our roadmap is published on GitHub, and we'd like to hear what you're building and what we should work on next, either on GitHub or on Discord. If you want to support VillageSQL, please star the repo.