10.0.0 ====== Features -------- XML/SOAP support for RPC ~~~~~~~~~~~~~~~~~~~~~~~~ RPC now understands the ``text/xml`` media type, allowing SQL functions to send XML output(``Accept: text/xml``) and receive XML input(``Content-Type: text/xml``). This makes SOAP endpoints possible, check the :ref:`create_soap_endpoint` how-to and the :ref:`scalar_return_formats` reference for more details. GeoJSON support ~~~~~~~~~~~~~~~ GeoJSON is supported across the board(reads, writes, RPC) with the ``Accept: application/geo+json`` header, this depends on PostGIS from the versions 3.0.0 and up. The :ref:`working with PostGIS section ` has an example to get you started. Execution Plan ~~~~~~~~~~~~~~ The :ref:`execution plan ` of a request is now obtainable with the ``Accept: application/vnd.pgrst.plan`` header. The result can be in ``text`` or ``json`` formats and is compatible with EXPLAIN vizualizers like `explain.depesz.com `_ or `explain.dalibo.com `_. Resource Embedding ~~~~~~~~~~~~~~~~~~ - A :ref:`one-to-one relationship ` is now detected when a foreign key is unique. - Using :ref:`computed_relationships`, you can add custom relationships or override automatically detected ones. This makes :ref:`resource_embedding` possible on Foreign Data Wrappers and complex SQL views. Horizontal/Vertical Filtering ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - :ref:`Accessing fields of a Composite type or elements of an Array type ` is now possible with the arrow operators(``->``, ``->>``) in the same way you would access a JSON type fields. - :ref:`pattern_matching` operators for `POSIX regular expressions `_ are now available: ``match`` and ``imatch``, equivalent in PostgreSQL to ``~`` and ``~*`` respectively. Insertions/Updates ~~~~~~~~~~~~~~~~~~ - ``limit`` can now affect the number of updated/deleted rows. See :ref:`limited_update_delete`. OpenAPI ~~~~~~~ You can now activate the "Authorize" button in SwaggerUI by enabling the :ref:`openapi-security-active` configuration. Add your JWT token prepending :code:`Bearer` to it and you'll be able to request protected resources. Administration ~~~~~~~~~~~~~~ - Two :ref:`health check endpoints ` are now exposed in a secondary port. - :ref:`pgrst_logging` now shows the database user. - It is now possible to execute PostgREST without specifying any configuration variable. The three that were mandatory on the previous versions, are no longer so. - If :ref:`db-uri` is not set, PostgREST will use the `libpq environment variables `_ for the database connection. - If :ref:`db-schemas` is not set, it will use the database ``public`` schema. - If :ref:`db-anon-role` is not set, it will not allow anonymous requests. Error messages ~~~~~~~~~~~~~~ - To increase consistency, all the errors messages are now normalized. The ``hint``, ``details``, ``code`` and ``message`` fields will always be present in the body, each one defaulting to a ``null`` value. In the same way, the :ref:`errors that were raised ` with ``SQLSTATE`` now include the ``message`` and ``code`` in the body. - To further clarify the source of an error, we now add a ``PGRST`` prefix to the error code of all the errors that are PostgREST-specific and don't come from the database. These errors have unique codes that identify them and are documented in the :ref:`pgrst_errors` section. Documentation improvements ~~~~~~~~~~~~~~~~~~~~~~~~~~ * Added a :doc:`/how-tos/working-with-postgresql-data-types` how-to, which contains explanations and examples on how to work with different PostgreSQL data types such as timestamps, ranges or PostGIS types, among others. * Added in-database and environment variable settings for each :ref:`configuration variable `. * Added the :ref:`file_descriptors` subsection. * Added a reference page for :ref:`error_source`. * Moved the :ref:`error_source` and the :ref:`status_codes` sections to the :ref:`errors reference page `. * Moved the *Casting type to custom JSON* how-to to the :ref:`casting_range_to_json` subsection. * Removed direct links for PostgREST versions older than 8.0 from the versions menu. * Removed the *Embedding table from another schema* how-to. * Restructured the :ref:`resource_embedding` section: - Added a :ref:`one-to-many` and :ref:`many-to-one` subsections. - Renamed the *Embedding through join tables* subsection to :ref:`many-to-many`. * Split up the *Insertions/Updates* section into :ref:`insert` and :ref:`update`. Breaking changes ---------------- * Many-to-many relationships now require that foreign key columns be part of the join table composite key - This was needed to reduce :ref:`embed_disamb` errors in complex schemas(`#2070 `_). - For migrating to this version, the less invasive method is to use :ref:`computed_relationships` to replace the previous many-to-many relationships. - Otherwise you can change your join table primary key. For example with ``alter table permission_user drop constraint permission_user_pkey, add primary key (id, user_id, permission_id);`` * Views now are not detected when embedding using :ref:`target_disamb`. - This embedding form was easily made ambiguous whenever a new view was added(`#2277 `_). - For migrating to this version, you can use :ref:`computed_relationships` to replace the previous view relationships. - :ref:`hint_disamb` works as usual on views. * ``limit/offset`` now limits the affected rows on ``UPDATE``/``DELETE`` - Previously, ``limit``/``offset`` only limited the returned rows but not the actual updated rows(`#2156 `_) * ``max-rows`` is no longer applied on ``POST``, ``PATCH``, ``PUT`` and ``DELETE`` returned rows - This was misleading because the affected rows were not really affected by ``max-rows``, only the returned rows were limited(`#2155 `_) * Return ``204 No Content`` without ``Content-Type`` for RPCs returning ``VOID`` - Previously, those RPCs would return ``null`` as a body with ``Content-Type: application/json`` (`#2001 `_). * Using ``Prefer: return=representation`` no longer returns a ``Location`` header - This reduces unnecessary computing for all insertions (`#2312 `_) Bug fixes --------- * Return ``204 No Content`` without ``Content-Type`` for ``PUT`` (`#2058 `_) * Clarify error for failed schema cache load. (`#2107 `_) - From ``Database connection lost. Retrying the connection`` to ``Could not query the database for the schema cache. Retrying.`` * Fix silently ignoring filter on a non-existent embedded resource (`#1771 `_) * Remove functions, which are not callable due to unnamed arguments, from schema cache and OpenAPI output. (`#2152 `_) * Fix accessing JSON array fields with ``->`` and ``->>`` in ``?select=`` and ``?order=``. (`#2145 `_) * Ignore ``max-rows`` on ``POST``, ``PATCH``, ``PUT`` and ``DELETE`` (`#2155 `_) * Fix inferring a foreign key column as a primary key column on views (`#2254 `_) * Restrict generated many-to-many relationships (`#2070 `_) - Only adds many-to-many relationships when a table has foreign keys to two other tables and these foreign key columns are part of the table's primary key columns. * Allow casting to types with underscores and numbers (e.g. ``select=oid_array::_int4``) (`#2278 `_) * Prevent views from breaking one-to-many/many-to-one embeds when using column or foreign key as target (`#2277 `_, `#2238 `_, `#1643 `_) - When using a column or foreign key 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 foreign key on a view to embed a table (``/view?select=*,col-or-fk(*)``) * Increase the ``db-pool-timeout`` to 1 hour to prevent frequent high connection latency (`#2317 `_) * The search path now correctly identifies schemas with uppercase and special characters in their names (regression) (`#2341 `_) * "404 Not Found" on nested routes and "405 Method Not Allowed" errors no longer start an empty database transaction (`#2364 `_) * Fix inaccurate result count when an inner embed was selected after a normal embed in the query string (`#2342 `_) * ``OPTIONS`` requests no longer start an empty database transaction (`#2376 `_) * Allow using columns with dollar sign ($) without double quoting in filters and ``select`` (`#2395 `_) * Fix loop crash error on startup in PostgreSQL 15 beta 3. ``Log: "UNION types \"char\" and text cannot be matched."`` (`#2410 `_) * Fix race conditions managing database connection helper (`#2397 `_) * Allow ``limit=0`` in the request query to return an empty array (`#2269 `_) Thanks ------ Big thanks from the `PostgREST team `_ to our sponsors! .. container:: image-container .. image:: ../_static/cybertec-new.png :target: https://www.cybertec-postgresql.com/en/?utm_source=postgrest.org&utm_medium=referral&utm_campaign=postgrest :width: 13em .. image:: ../_static/2ndquadrant.png :target: https://www.2ndquadrant.com/en/?utm_campaign=External%20Websites&utm_source=PostgREST&utm_medium=Logo :width: 13em .. image:: ../_static/retool.png :target: https://retool.com/?utm_source=sponsor&utm_campaign=postgrest :width: 13em .. image:: ../_static/gnuhost.png :target: https://gnuhost.eu/?utm_source=sponsor&utm_campaign=postgrest :width: 13em .. image:: ../_static/supabase.png :target: https://supabase.com/?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage :width: 13em .. image:: ../_static/oblivious.jpg :target: https://oblivious.ai/?utm_source=sponsor&utm_campaign=postgrest :width: 13em * Evans Fernandes * Jan Sommer * `Franz Gusenbauer `_ * `Daniel Babiak `_ * Tsingson Qin * Michel Pelletier * Jay Hannah * Robert Stolarz * Nicholas DiBiase * Christopher Reid * Nathan Bouscal * Daniel Rafaj * David Fenko * Remo Rechkemmer * Severin Ibarluzea * Tom Saleeba * Pawel Tyll If you like to join them please consider `supporting PostgREST development `_.