PostgREST 12.2 is out! It comes with Observability and API improvements. In this post, we'll see what's new.
Prometheus Metrics
Version 12.2 ships with Prometheus-compatible metrics for PostgREST's schema cache and connection pool. These are useful for troubleshooting, for example, when PostgREST's pool is starved for connections.
_10curl localhost:3001/metrics_10_10# HELP pgrst_db_pool_timeouts_total The total number of pool connection timeouts_10# TYPE pgrst_db_pool_timeouts_total counter_10pgrst_db_pool_timeouts_total 7.0_10_10# ....
A full list of supported metrics is available in the PostgREST documentation.
Hoisted Function Settings
Sometimes it's handy to set a custom timeout per function. You can now do this on 12.2 projects with:
_10create or replace function special_function()_10returns void as $$_10 select pg_sleep(3); -- simulating some long-running process_10$$_10language sql_10set statement_timeout to '4s';
And calling the function with the RPC interface.
When doing set statement_timeouton the function, the statement_timeout will be “hoisted” and applied per transaction.
By default this also works for other settings, namely plan_filter.statement_cost_limit and default_transaction_isolation. The list of hoisted settings can be extended by modifying the db-hoisted-tx-settings configuration.
Before 12.2, this could be done by setting a statement_timeout on the API roles, but this affected all the SQL statements executed by those roles.
Max Affected
In prior versions of PostgREST, users could limit the number of records impacted by mutations (insert/update/delete) to 1 row using vendor media type application/vnd.pgrst.object+json. That supports a common use case but is not flexible enough to support user defined values.
12.2 introduces the max-affected preference to limit the affected rows up to a custom value.
For example:
_10curl -i "http://localhost:3000/items?id=lt.15" -X DELETE \_10 -H "Content-Type: application/json" \_10 -H "Prefer: handling=strict, max-affected=10"
If the number of affected records exceeds max-affected , an error is returned:
_10HTTP/1.1 400 Bad Request_10{_10 "code": "PGRST124",_10 "message": "Query result exceeds max-affected preference constraint",_10 "details": "The query affects 14 rows",_10 "hint": null_10}
Try it out
PostgREST v12.2 is already available on the Supabase platform on its latest patch version (v12.2.3) for new projects. Spin up a new project or upgrade your existing project to try it out!