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!