Chapter 2: postjevsql, the functions you call from SQL

This crate is the extension itself: the part Postgres loads when you say CREATE EXTENSION postjevsql. It declares every SQL function, every jev.* setting, the jev_cache table and the result types, and it holds the judge, the code that turns a call into a request, a cache row and an answer.

Here is the surprise: the SQL functions do nothing. Look at jev_prob in src/lib.rs and its body is one line, which raises an error. The work is done by a plan node (chapter 7) that finds every call to these functions in a query and answers them itself, many rows at once. The function body runs only if a call escaped that node, and then it refuses rather than quietly sending one request per row. The functions are, in effect, a vocabulary the planner recognises.

The functions

Every function takes the row first: a table alias for the whole row (jev(t, …), which sends every column), or a column list (jev((t.title, t.body), …), which sends only those, costs fewer tokens and is usually what you want). Every one is STRICT, so a NULL argument gives NULL and sends nothing.

Calls that ask the same question share one judgment: jev and jev_prob on the same question, jev_score and jev_score_norm, a scalar function and its _full twin, jev_confidence and the Choice or Score it is about. One request, one cache row. fit is the probability, asked as a separate yes/no question in the first request, that any of the labels fits at all, since a Choice always picks one even when none is right.

Aside. Why must options keep your order? Because order changes the answer. One measurement moved a correct label from 0.34 to 0.83 just by listing it last instead of first. So the extension never shuffles: your order is sent, your order is part of the cache key, and reordering a list is, correctly, a different question.

The settings

What it keeps, and what it refuses

Every answer is a row in jev_cache, keyed by the hash of exactly what was sent, and each row is a receipt: the request bytes, the requested and the answering model, TypeSafe's request id, the tokens billed, the full answer, the namespace and the time. The table is revoked from PUBLIC; the scan reaches it as its owner, because a role that could write it could forge answers.

Every function is revoked from PUBLIC at install, so spending the API key takes an explicit GRANT EXECUTE. And every failure carries a SQLSTATE you can branch on, with the request id and attempt count in its DETAIL:

Try it. EXPLAIN SELECT * FROM people p WHERE jev(p, 'Could this person work from home?'); sends nothing and needs no API key. The plan shows a Custom Scan (JevScan) with Candidate Rows, Estimated Requests, Estimated Input Tokens and Worst-Case Cost: the price, before it is paid. EXPLAIN ANALYZE runs it and adds Cache Hits, Cache Misses, Shared Judgments, Requests, Input Tokens and Cost.

For the people who maintain it

← Previous: Chapter 1, crates/ · Up: crates · Next: Chapter 3, postjevsql/src/ →