PostgREST 12.2 is out! It comes with Observability and API improvements. In this post, we'll see what's new.
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.
_10 curl 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 _10 pgrst_db_pool_timeouts_total 7.0 _10 _10 # ....
A full list of supported metrics is available in the PostgREST documentation.
Sometimes it's handy to set a custom timeout per function. You can now do this on 12.2 projects with:
_10 create or replace function special_function() _10 returns void as $$ _10 select pg_sleep(3); -- simulating some long-running process _10 $$ _10 language sql _10 set 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.
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:
_10 curl -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:
_10 HTTP/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 }
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!