v11.2.1

Maintenance Release

v11.2.1 addresses bugs introduced in v11.2.0. Also PostgreSQL 16 is now tested and confirmed to work.

What is fixed

API

• #2899, Fix application/vnd.pgrst.array not accepted as a valid mediatype - @taimoorzaeem
• #2915, Fix duplicate headers in response - @taimoorzaeem
• #2824, Fix range request with first position same as length return status 206 - @taimoorzaeem
• #2939, Fix wrong Preference-Applied with Prefer: tx=commit when transaction is rollbacked - @steve-chavez
• #2939, Fix count=exact not being included in Preference-Applied - @steve-chavez
• #2929, Fix arrow filtering on RPC returning dynamic TABLE with composite type - @steve-chavez

Schema Cache

• #2524, Fix schema cache and configuration reloading with NOTIFY not working on Windows - @diogob, @laurenceisla

Resource Embedding

• #2800, Fix not including to-one embed resources that had a NULL value in any of the selected fields when doing null filtering on them - @laurenceisla
• #2846, Fix error when requesting Prefer: count=<type> and doing null filtering on embedded resources - @laurenceisla
• #2963, Fix RPCs not embedding correctly when using overloaded functions for computed relationships - @laurenceisla

Authentication

• #2959, Fix setting default_transaction_isolation unnecessarily - @steve-chavez

Connection Pool

• #2970, Fix regression that rejects URI connection strings with certain unescaped characters in the password - @laurenceisla, @steve-chavez

Full Changelog: v11.2.0...v11.2.1

v11.2.0

Overview

PostgREST serves a RESTful API from any existing PostgreSQL database.

v11.2.0 brings you the possibility of using PostgreSQL domains and casts to change how your data is presented to web users. We call this feature "domain representations" and it holds some advantages over views.

-- having a uuid domain and a table
create domain app_uuid as uuid;

create table profiles(
  id   app_uuid
, name text
);

-- we define a function to convert the uuid domain to base64
create or replace function json(app_uuid) returns json as $$
  select to_json(encode(uuid_send($1),'base64'));
$$ language sql immutable;

-- and use the function for an implicit cast from domain to json 
create cast (app_uuid as json) with function json(app_uuid) as implicit;

-- the format changes when requesting JSON at the API level
curl "http://localhost:3000/profiles" \
  -H "Accept: application/json"

[{"id":"hGxP/ZLOTeeNEY4pkp9OxA==","name":"John Doe"}]

-- while the data is kept as is at the database level
select * from profiles;

                  id                  |   name
--------------------------------------+----------
 846c4ffd-92ce-4de7-8d11-8e29929f4ec4 | John Doe

Domain representations also allow you to change the format of the request payload and the format of the filters values. Check the Domain Representations docs for more details.

What is new

API

• Domain representations by @aljungberg in #2523
  • Allows for flexible API output formatting and input parsing on a per-column type basis using regular SQL functions configured in the database
  • Enables greater flexibility in the form and shape of your APIs, both for output and input, making PostgREST a more versatile general-purpose API server
  • Examples include base64 encode/decode your binary data (like a bytea column containing an image), choose whether to present a timestamp column as seconds since the Unix epoch or as an ISO 8601 string, or represent fixed precision decimals as strings, not doubles, to preserve precision
  • ...and accept the same in POST/PUT/PATCH by configuring the reverse transformation(s)
  • Other use-cases include custom representation of enums, arrays, nested objects, CSS hex colour strings, gzip compressed fields, metric to imperial conversions, and much more
  • Works when using the select parameter to select only a subset of columns, embedding through complex joins, renaming fields, with views and computed columns
  • Works when filtering on a formatted column without extra indexes by parsing to the canonical representation
  • Works for data RETURNING operations, such as requesting the full body in a POST/PUT/PATCH with Prefer: return=representation
  • Works for batch updates and inserts
  • Completely optional, define the functions in the database and they will be used automatically everywhere
  • Data representations preserve the ability to write to the original column and require no extra storage or complex triggers (compared to using GENERATED ALWAYS columns)
  • Note: data representations require Postgres 10 (Postgres 11 if using IN predicates); data representations are not implemented for RPC

Admin

• Query the PostgREST version in SQL by @laurenceisla in #2647
• The CLI now has the --version option which prints the version information by @laurenceisla in #2856

Resource Embedding

• The documentation now explicitly mentions Foreign Key Joins and includes more complex relationships.

Resource Representation

• nulls=stripped can remove JSON keys which have null values by @taimoorzaeem in #1601
• Improve details field of the singular error response by @taimoorzaeem in #1655

Tables and Views

• Prefer: return=* now reply with Preference-Applied headers by @taimoorzaeem in #740

What is fixed

Auth

• Fix applying superuser settings for impersonated role by @steve-chavez in #2896

API

• Fix OPTIONS not accepting all available media types by @steve-chavez in #2821
• Fix Prefer: missing=default with DOMAIN default values by @steve-chavez in #2840
• Fix HEAD unnecessarily executing aggregates by @steve-chavez in #2849
• Fix unused index on jsonb/jsonb arrow filter and order (/bets?data->>contractId=eq.1 and /bets?order=data->>contractId) by @steve-chavez in #2594
• Fix character and bit columns with fixed length not inserting/updating properly by @laurenceisla in #2861
  • Fixes the error "value too long for type character(1)" when the char length of the column was bigger than one.
• Fix null filtering on embedded resource when using a column name equal to the relation name by @steve-chavez in #2862
• Fix function parameters of type character and bit not ignoring length by @laurenceisla in #1586
  • Fixes the error "value too long for type character(1)" when the char length of the parameter was bigger than one.
• Fix error when a function returns RECORD or SET OF RECORD by @laurenceisla in #2881

Misc

• Fix compilation on Ubuntu by being compatible with GHC 9.0.2 by @steve-chavez in #2834

Deprecated

Resource Embedding

• Deprecate resource embedding target disambiguation by @steve-chavez in #2863
  • The /table?select=*,other!fk(*) must be used to disambiguate
  • The server aids in choosing the !fk by sending a hint on the error whenever an ambiguous request happens.

New Contributors

• @taimoorzaeem made their first contribution in #2873

Full Changelog: v11.1.0...v11.2.0

v11.1.0

Added

• #2786, Limit idle postgresql connection lifetime - @robx
  • New option db-pool-max-idletime (default 30s).
  • This is equivalent to the old option db-pool-timeout of PostgREST 10.0.0.
  • A config alias for db-pool-timeout is included.
• #2703, Add pre-config function - @steve-chavez
  • New config option db-pre-config(empty by default)
  • Allows using the in-database configuration without SUPERUSER
• #2781, When db-channel-enabled is false, start automatic connection recovery on a new request when pool connections are closed with pg_terminate_backend - @steve-chavez
  • Mitigates the lack of LISTEN/NOTIFY for schema cache reloading on read replicas.

Fixed

• #2791, Fix dropping schema cache reload notifications - @steve-chavez
• #2801, Stop retrying connection when "no password supplied" - @steve-chavez

v11.0.1

Fixed

• #2762, Fixes "permission denied for schema" error during schema cache load - @steve-chavez
• #2756, Fix bad error message on generated columns when using Prefer: missing=default - @steve-chavez
• #1139, Allow a 30 second skew for JWT validation - @steve-chavez
  • It used to be 1 second, which was too strict

v11.0.0

Added

• #1414, Add related orders - @steve-chavez
  • On a many-to-one or one-to-one relationship, you can order a parent by a child column /projects?select=*,clients(*)&order=clients(name).desc.nullsfirst
• #1233, #1907, #2566, Allow spreading embedded resources - @steve-chavez
  • On a many-to-one or one-to-one relationship, you can unnest a json object with /projects?select=*,...clients(client_name:name)
  • Allows including the join table columns when resource embedding
  • Allows disambiguating a recursive m2m embed
  • Allows disambiguating an embed that has a many-to-many relationship using two foreign keys on a junction
• #2340, Allow embedding without selecting any column - @steve-chavez
• #2563, Allow is.null or not.is.null on an embedded resource - @steve-chavez
  • Offers a more flexible replacement for !inner, e.g. /projects?select=*,clients(*)&clients=not.is.null
  • Allows doing an anti join, e.g. /projects?select=*,clients(*)&clients=is.null
  • Allows using or across related tables conditions
• #1100, Customizable OpenAPI title - @AnthonyFisi
• #2506, Add server-trace-header for tracing HTTP requests. - @steve-chavez
  • When the client sends the request header specified in the config it will be included in the response headers.
• #2694, Make db-root-spec stable. - @steve-chavez
  • This can be used to override the OpenAPI spec with a custom database function
• #1567, On bulk inserts, missing values can get the column DEFAULT by using the Prefer: missing=default header - @steve-chavez
• #2501, Allow filtering byIS DISTINCT FROM using the isdistinct operator, e.g. /people?alias=isdistinct.foo
• #1569, Allow any/all modifiers on the eq,like,ilike,gt,gte,lt,lte,match,imatch operators, e.g. /tbl?id=eq(any).{1,2,3} - @steve-chavez
  • This converts the input into an array type
• #2561, Configurable role settings - @steve-chavez
  • Database roles that are members of the connection role get their settings applied, e.g. doing
    ALTER ROLE anon SET statement_timeout TO '5s' will result in that statement_timeout getting applied for that role.
  • Works when switching roles when a JWT is sent
  • Settings can be reloaded with NOTIFY pgrst, 'reload config'.
• #2468, Configurable transaction isolation level with default_transaction_isolation - @steve-chavez
  • Can be set per function create function .. set default_transaction_isolation = 'repeatable read'
  • Or per role alter role .. set default_transaction_isolation = 'serializable'

Fixed

• #2651, Add the missing get path item for RPCs to the OpenAPI output - @laurenceisla
• #2648, Fix inaccurate error codes with new ones - @laurenceisla
  • PGRST204: Column is not found
  • PGRST003: Timed out when acquiring connection to db
• #1652, Fix function call with arguments not inlining - @steve-chavez
• #2705, Fix bug when using the Range header on PATCH/DELETE - @laurenceisla
  • Fix the"message": "syntax error at or near \"RETURNING\"" error
  • Fix doing a limited update/delete when an order query parameter was present
• #2742, Fix db settings and pg version queries not getting prepared - @steve-chavez
• #2618, Fix PATCH requests not recognizing embedded filters and using the top-level resource instead - @steve-chavez

Changed

• #2705, The Range header is now only considered on GET requests and is ignored for any other method - @laurenceisla
  • Other methods should use the limit/offset query parameters for sub-ranges
  • PUT requests no longer return an error when this header is present (using limit/offset still triggers the error)
• #2733, Remove bulk RPC call with the Prefer: params=multiple-objects header. A function with a JSON array or object parameter should be used instead.

v10.2.0

Added

• #2663, Limit maximal postgresql connection lifetime - @robx
  • New option db-pool-max-lifetime (default 30m)
  • db-pool-acquisition-timeout is no longer optional and defaults to 10s
  • Fixes postgresql resource leak with long-lived connections (#2638)

Fixed

• #2667, Fix db-pool-acquisition-timeout not logging to stderr when the timeout is reached - @steve-chavez

v10.1.2

Fixed

• #2565, Fix bad M2M embedding on RPC - @steve-chavez
• #2575, Replace misleading error message when no function is found with a hint containing functions/parameters names suggestions - @laurenceisla
• #2582, Move explanation about "single parameters" from the message to the details in the error output - @laurenceisla
• #2569, Replace misleading error message when no relationship is found with a hint containing parent/child names suggestions - @laurenceisla
• #1405, Add the required OpenAPI items object when the parameter is an array - @laurenceisla
• #2592, Add upsert headers for POST requests to the OpenAPI output - @laurenceisla
• #2623, Fix FK pointing to VIEW instead of TABLE in OpenAPI output - @laurenceisla
• #2622, Consider any PostgreSQL authentication failure as fatal and exit immediately - @michivi
• #2620, Fix NOTIFY pgrst not reloading the db connections catalog cache - @steve-chavez

v10.1.1

Fixed

• #2548, Fix regression when embedding views with partial references to multi column FKs - @wolfgangwalther
• #2558, Fix regression when requesting limit=0 and db-max-row is set - @laurenceisla

v10.1.0

Added

• #2348, Add db-pool-acquisition-timeout configuration option, time in seconds to wait to acquire a connection. - @robx

Fixed

• #2261, #2349, #2467, Reduce allocations communication with PostgreSQL, particularly for request bodies. - @robx
• #2401, #2444, Fix SIGUSR1 to fully flush connections pool. - @robx
• #2428, Fix opening an empty transaction on failed resource embedding - @steve-chavez
• #2455, Fix embedding the same table multiple times - @steve-chavez
• #2518, Fix a regression when embedding views where base tables have a different column order for FK columns - @wolfgangwalther
• #2458, Fix a regression with the location header when inserting into views with PKs from multiple tables - @wolfgangwalther
• #2356, Fix a regression in openapi output with mode follow-privileges - @wolfgangwalther
• #2283, Fix infinite recursion when loading schema cache with self-referencing view - @wolfgangwalther
• #2343, Return status code 200 for PATCH requests which don't affect any rows - @wolfgangwalther
• #2481, Treat computed relationships not marked SETOF as M2O/O2O relationship - @wolfgangwalther
• #2534, Fix embedding a computed relationship with a normal relationship - @steve-chavez
• #2362, Fix error message when [] is used inside select - @wolfgangwalther
• #2475, Disallow !inner on computed columns - @wolfgangwalther
• #2285, Ignore leading and trailing spaces in column names when parsing the query string - @wolfgangwalther
• #2545, Fix UPSERT with PostgreSQL 15 - @wolfgangwalther
• #2459, Fix embedding views with multiple references to the same base column - @wolfgangwalther

Changed

• #2444, Removed db-pool-timeout option, because this was removed upstream in hasql-pool. - @robx
• #2343, PATCH requests that don't affect any rows no longer return 404 - @wolfgangwalther
• #2537, Stricter parsing of query string. Instead of silently ignoring, the parser now throws on invalid syntax like json paths for embeddings, hints for regular columns, empty casts or fts languages, etc. - @wolfgangwalther

Deprecated

• #1385, Deprecate bulk-calls when including the Prefer: params=multiple-objects in the request. A function with a JSON array or object parameter should be used instead for a better performance.

v10.0.0

Added

• #1933, #2109, Add a minimal health check endpoint - @steve-chavez
  • For enabling this, the admin-server-port config must be set explictly
  • A <host>:<admin_server_port>/live endpoint is available for checking if postgrest is running on its port/socket. 200 OK = alive, 503 = dead.
  • A <host>:<admin_server_port>/ready endpoint is available for checking a correct internal state(the database connection plus the schema cache). 200 OK = ready, 503 = not ready.
• #1988, Add the current user to the request log on stdout - @DavidLindbom, @wolfgangwalther
• #1823, Add the ability to run postgrest without any configuration. - @wolfgangwalther
  • #1991, Add the ability to run without db-uri using libpq's PG environment variables to connect. - @wolfgangwalther
  • #1769, Add the ability to run without db-schemas, defaulting to db-schemas=public. - @wolfgangwalther
  • #1689, Add the ability to run without db-anon-role disabling anonymous access. - @wolfgangwalther
• #1543, Allow access to fields of composite types in select=, order= and filters through JSON operators -> and ->>. - @wolfgangwalther
• #2075, Allow access to array items in ?select=, ?order= and filters through JSON operators -> and ->>. - @wolfgangwalther
• #2156, #2211, Allow applying limit/offset to UPDATE/DELETE to only affect a subset of rows - @steve-chavez
  • It requires an explicit order on a unique column(s)
• #1917, Add error codes with the "PGRST" prefix to the error response body to differentiate PostgREST errors from PostgreSQL errors - @laurenceisla
• #1917, Normalize the error response body by always having the detail and hint error fields with a null value if they are empty - @laurenceisla
• #2176, Errors raised with SQLSTATE now include the message and the code in the response body - @laurenceisla
• #2236, Support POSIX regular expression operators for row filtering - @enote-kane
• #2202, Allow returning XML from RPCs - @fjf2002
• #2268, Allow returning XML from single-column queries - @fjf2002
• #2300, RPC POST for function w/single unnamed XML param #2300 - @fjf2002
• #1564, Allow geojson output by specifying the Accept: application/geo+json media type - @steve-chavez
  • Requires postgis >= 3.0
  • Works for GET, RPC, POST/PATCH/DELETE with Prefer: return=representation.
  • Resource embedding works and the embedded rows will go into the properties key
  • In case of multiple geometries in the same table, you can choose which one will go into the geometry key with the usual ?select query parameter.
• #1082, Add security definitions to the OpenAPI output - @laurenceisla
• #2378, Support http OPTIONS method on RPC and root path - @steve-chavez
• #2354, Allow getting the EXPLAIN plan of a request by using the Accept: application/vnd.pgrst.plan header - @steve-chavez
  • Only allowed if the db-plan-enabled config is set to true
  • Can generate the plan for different media types using the for parameter: Accept: application/vnd.pgrst.plan; for="application/vnd.pgrst.object"
  • Different options for the plan can be used with the options parameter: Accept: application/vnd.pgrst.plan; options=analyze|verbose|settings|buffers|wal
  • The plan can be obtained in text or json by using different media type suffixes: Accept: application/vnd.pgrst.plan+text and Accept: application/vnd.pgrst.plan+json.
• #2144, Support computed relationships which allow extending and overriding relationships for resource embedding - @steve-chavez, @wolfgangwalther
• #1984, Detect one-to-one relationships for resource embedding - @steve-chavez
  • Detected when there's a foreign key with a unique constraint or when a foreign key is also a primary key

Fixed

• #2058, Return 204 No Content without Content-Type for PUT - @wolfgangwalther
• #2107, Clarify error for failed schema cache load. - @steve-chavez
  • From Database connection lost. Retrying the connection to Could not query the database for the schema cache. Retrying.
• #1771, Fix silently ignoring filter on a non-existent embedded resource - @steve-chavez
• #2152, Remove functions, which are uncallable because of unnamend arguments from schema cache and OpenAPI output. - @wolfgangwalther
• #2145, Fix accessing json array fields with -> and ->> in ?select= and ?order=. - @wolfgangwalther
• #2155, Ignore max-rows on POST, PATCH, PUT and DELETE - @steve-chavez
• #2254, Fix inferring a foreign key column as a primary key column on views - @steve-chavez
• #2070, Restrict generated many-to-many relationships - @steve-chavez
  • Only adds many-to-many relationships when: a table has FKs to two other tables and these FK columns are part of the table's PK columns.
• #2278, Allow casting to types with underscores and numbers(e.g. select=oid_array::_int4) - @steve-chavez
• #2277, #2238, #1643, Prevent views from breaking one-to-many/many-to-one embeds when using column or FK as target - @steve-chavez
  • When using a column or FK as target for embedding(/tbl?select=*,col-or-fk(*)), only tables are now detected and views are not.
  • You can still use a column or an inferred FK on a view to embed a table(/view?select=*,col-or-fk(*))
• #2317, Increase the db-pool-timeout to 1 hour to prevent frequent high connection latency - @steve-chavez
• #2341, The search path now correctly identifies schemas with uppercase and special characters in their names (regression) - @laurenceisla
• #2364, "404 Not Found" on nested routes and "405 Method Not Allowed" errors no longer start an empty database transaction - @steve-chavez
• #2342, Fix inaccurate result count when an inner embed was selected after a normal embed in the query string - @laurenceisla
• #2376, OPTIONS requests no longer start an empty database transaction - @steve-chavez
• #2395, Allow using columns with dollar sign($) without double quoting in filters and select - @steve-chavez
• #2410, Fix loop crash error on startup in Postgres 15 beta 3. Log: "UNION types "char" and text cannot be matched". - @yevon
• #2397, Fix race conditions managing database connection helper - @robx
• #2269, Allow limit=0 in the request query to return an empty array - @gautam1168, @laurenceisla

Changed

• #2001, Return 204 No Content without Content-Type for RPCs returning VOID - @wolfgangwalther
  • Previously, those RPCs would return "null" as a body with Content-Type: application/json.
• #2156, limit/offset now limits the affected rows on UPDATE/DELETE - @steve-chavez
  • Previously, limit/offset only limited the returned rows but not the actual updated rows
• #2155, max-rows is no longer applied on POST/PATCH/PUT/DELETE returned rows - @steve-chavez
  • This was misleading because the affected rows were not really affected by max-rows, only the returned rows were limited
• #2070, Restrict generated many-to-many relationships - @steve-chavez
  • A primary key that contains the foreign key columns is now needed for generating many-to-many relationships.
• #2277, Views now are not detected when embedding using the column or FK as target (/view?select=*,column(*)) - @steve-chavez
  • This embedding form was easily made ambiguous whenever a new view was added.
  • You can use computed relationships to keep this embedding form working
• #2312, Using Prefer: return=representation no longer returns a Location header - @laurenceisla
• #1984, For the cases where one to one relationships are detected, json objects will be returned instead of json arrays of length 1
  • If you wish to override this behavior, you can use computed relationships to return arrays again