bump version to 11.2.2

When doing Null Filtering, the to-one embed resources were not included if they had a NULL value in any of the selected fields.

.github/actions/setup-nix/action.yaml

@@ -11,7 +11,7 @@ inputs:
 runs:
   using: composite
   steps:
-    - uses: cachix/install-nix-action@v22
+    - uses: cachix/install-nix-action@v23
       with:
         install_url: https://releases.nixos.org/nix/nix-2.13.3/install
     - uses: cachix/cachix-action@v12

.github/workflows/cachix.yaml

@@ -28,7 +28,7 @@ jobs:
     name: Seed ${{ matrix.os }}
     runs-on: ${{ matrix.runs-on }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:

.github/workflows/ci.yaml

@@ -17,7 +17,7 @@ jobs:
     name: Lint & check code style
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -37,7 +37,7 @@ jobs:
         # https://github.com/actions/runner/issues/241#issuecomment-842566950
         shell: script -qec "bash --noprofile --norc -eo pipefail {0}"
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -63,7 +63,7 @@ jobs:
     strategy:
       fail-fast: false
       matrix:
-        pgVersion: [9.6, 10, 11, 12, 13, 14, 15]
+        pgVersion: [9.6, 10, 11, 12, 13, 14, 15, 16]
     name: Test PG ${{ matrix.pgVersion }} (Nix)
     runs-on: ubuntu-latest
     defaults:
@@ -72,7 +72,7 @@ jobs:
         # https://github.com/actions/runner/issues/241#issuecomment-842566950
         shell: script -qec "bash --noprofile --norc -eo pipefail {0}"
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -91,7 +91,7 @@ jobs:
     name: Test memory (Nix)
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -104,7 +104,7 @@ jobs:
     name: Build Linux static (Nix)
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -135,7 +135,7 @@ jobs:
     name: Build MacOS (Nix)
     runs-on: macos-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
 
@@ -175,7 +175,7 @@ jobs:
     name: Build ${{ matrix.name }} (Stack)
     runs-on: ${{ matrix.runs-on }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Stack working files cache
         uses: actions/cache@v3
         with:
@@ -199,7 +199,7 @@ jobs:
     name: Get FreeBSD build from CirrusCI
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Get FreeBSD executable from CirrusCI
         env:
           # GITHUB_SHA does weird things for pull request, so we roll our own:
@@ -221,7 +221,7 @@ jobs:
     name: Build Linux (Cabal, GHC ${{ matrix.ghc }})
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Workaround runner image issue
         # https://github.com/actions/runner-images/issues/7061
         run: sudo chown -R "$USER" /usr/local/.ghcup
@@ -260,7 +260,7 @@ jobs:
       GITHUB_COMMIT: ${{ github.sha }}
       GHC_VERSION: ${{ matrix.ghc }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - id: Remote-Dir
         name: Unique directory name for the remote build
         run: echo "remotepath=postgrest-build-$(uuidgen)" >> "$GITHUB_OUTPUT"
@@ -324,7 +324,7 @@ jobs:
       version: ${{ steps.Identify-Version.outputs.version }}
       isprerelease: ${{ steps.Identify-Version.outputs.isprerelease }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - id: Identify-Version
         name: Identify the version to be released
         run: |
@@ -377,7 +377,7 @@ jobs:
     env:
       VERSION: ${{ needs.Prepare-Release.outputs.version }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Download all artifacts
         uses: actions/download-artifact@v3
         with:
@@ -443,7 +443,7 @@ jobs:
       VERSION: ${{ needs.Prepare-Release.outputs.version }}
       ISPRERELEASE: ${{ needs.Prepare-Release.outputs.isprerelease }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -496,7 +496,7 @@ jobs:
       VERSION: ${{ needs.Prepare-Release.outputs.version }}
       ISPRERELEASE: ${{ needs.Prepare-Release.outputs.isprerelease }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Publish images for ARM builds on Docker Hub
         uses: appleboy/ssh-action@master
         env:
@@ -523,7 +523,7 @@ jobs:
     env:
       REMOTE_DIR: ${{ needs.Build-Cabal-Arm.outputs.remotepath }}
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
       - name: Remove uploaded files from server
         uses: appleboy/ssh-action@master
         with:

.github/workflows/loadtest.yaml

@@ -15,7 +15,7 @@ jobs:
     name: Loadtest (Nix)
     runs-on: ubuntu-latest
     steps:
-      - uses: actions/checkout@v3
+      - uses: actions/checkout@v4
         with:
           fetch-depth: 0
       - name: Setup Nix Environment

CHANGELOG.md

@@ -5,6 +5,29 @@ This project adheres to [Semantic Versioning](http://semver.org/).
 
 ## Unreleased
 
+## [11.2.2] - 2023-10-25
+
+### Fixed
+
+ - #2824, Fix regression by reverting fix that returned 206 when first position = length in a `Range` header - @laurenceisla, @strengthless
+
+## [11.2.1] - 2023-10-03
+
+### Fixed
+
+ - #2899, Fix `application/vnd.pgrst.array` not accepted as a valid mediatype - @taimoorzaeem
+ - #2524, Fix schema cache and configuration reloading with `NOTIFY` not working on Windows - @diogob, @laurenceisla
+ - #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
+ - #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
+ - #2959, Fix setting `default_transaction_isolation` unnecessarily - @steve-chavez
+ - #2929, Fix arrow filtering on RPC returning dynamic TABLE with composite type - @steve-chavez
+ - #2963, Fix RPCs not embedding correctly when using overloaded functions for computed relationships - @laurenceisla
+ - #2970, Fix regression that rejects URI connection strings with certain unescaped characters in the password - @laurenceisla, @steve-chavez
+
 ## [11.2.0] - 2023-08-10
 
 ### Added

default.nix

@@ -50,6 +50,19 @@ let
 
   postgresqlVersions =
     [
+      {
+        name = "postgresql-16";
+        postgresql = pkgs.postgresql_16.withPackages (p: [
+          p.postgis
+          (p.pg_safeupdate.overrideAttrs (old: {
+            installPhase = ''
+              mkdir -p $out/bin
+              cp safeupdate.dylib safeupdate.so || true
+              install -D safeupdate.so -t $out/lib
+            '';
+          }))
+        ]);
+      }
       { name = "postgresql-15"; postgresql = pkgs.postgresql_15.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
       { name = "postgresql-14"; postgresql = pkgs.postgresql_14.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
       { name = "postgresql-13"; postgresql = pkgs.postgresql_13.withPackages (p: [ p.postgis p.pg_safeupdate ]); }

nix/overlays/haskell-packages.nix

@@ -43,8 +43,8 @@ let
         (prev.callHackageDirect
           {
             pkg = "hasql-notifications";
-            ver = "0.2.0.5";
+            ver = "0.2.0.6";
-            sha256 = "sha256-KV36zs/RTgJh/oBBPZaNiQshusmakWnjYTcdoNa4JLA=";
+            sha256 = "sha256-7PyFlB2B70njudOjaX6tk1m77ol9vnF5fI0LF86kVAI=";
           }
           { });
 

nix/overlays/postgresql-future.nix

@@ -4,16 +4,16 @@ self: super:
 {
   ## Example for including a postgresql version from a specific nixpks commit:
   ##
-  #  postgresql_14 =
+  postgresql_16 =
-  #    let
+    let
-  #      rev = "76b1e16c6659ccef7187ca69b287525fea133244";
+      rev = "5148520bfab61f99fd25fb9ff7bfbb50dad3c9db";
-  #      tarballHash = "1vsahpcx80k2bgslspb0sa6j4bmhdx77sw6la455drqcrqhdqj6a";
+      tarballHash = "1dfjmz65h8z4lk845724vypzmf3dbgsdndjpj8ydlhx6c7rpcq3p";
-  #
+
-  #      pinnedPkgs =
+      pinnedPkgs =
-  #        builtins.fetchTarball {
+        builtins.fetchTarball {
-  #          url = "https://github.com/nixos/nixpkgs/archive/${rev}.tar.gz";
+          url = "https://github.com/nixos/nixpkgs/archive/${rev}.tar.gz";
-  #          sha256 = tarballHash;
+          sha256 = tarballHash;
-  #        };
+        };
-  #    in
+    in
-  #    (import pinnedPkgs { }).pkgs.postgresql_14;
+    (import pinnedPkgs { }).pkgs.postgresql_16;
 }

postgrest.cabal

@@ -1,5 +1,5 @@
 name:               postgrest
-version:            11.2.0
+version:            11.2.2
 synopsis:           REST API for any Postgres database
 description:        Reads the schema of a PostgreSQL database and creates RESTful routes
                     for tables, views, and functions, supporting all HTTP methods that security
@@ -92,7 +92,7 @@ library
                     , gitrev                    >= 1.2 && < 1.4
                     , hasql                     >= 1.6.1.1 && < 1.7
                     , hasql-dynamic-statements  >= 0.3.1 && < 0.4
-                    , hasql-notifications       >= 0.1 && < 0.3
+                    , hasql-notifications       >= 0.2.0.6 && < 0.3
                     , hasql-pool                >= 0.10 && < 0.11
                     , hasql-transaction         >= 1.0.1 && < 1.1
                     , heredoc                   >= 0.2 && < 0.3

src/PostgREST/ApiRequest.hs

@@ -153,7 +153,7 @@ userApiRequest conf req reqBody = do
   , iRange = ranges
   , iTopLevelRange = topLevelRange
   , iPayload = payload
-  , iPreferences = Preferences.fromHeaders hdrs
+  , iPreferences = Preferences.fromHeaders (configDbTxAllowOverride conf) hdrs
   , iQueryParams = qPrms
   , iColumns = columns
   , iHeaders = iHdrs

src/PostgREST/ApiRequest/Preferences.hs

@@ -6,6 +6,7 @@
 --
 -- [1] https://datatracker.ietf.org/doc/html/rfc7240
 --
+{-# LANGUAGE NamedFieldPuns #-}
 module PostgREST.ApiRequest.Preferences
   ( Preferences(..)
   , PreferCount(..)
@@ -15,8 +16,8 @@ module PostgREST.ApiRequest.Preferences
   , PreferResolution(..)
   , PreferTransaction(..)
   , fromHeaders
-  , ToAppliedHeader(..)
   , shouldCount
+  , prefAppliedHeader
   ) where
 
 import qualified Data.ByteString.Char8     as BS
@@ -53,7 +54,7 @@ data Preferences
 --
 -- One header with comma-separated values can be used to set multiple preferences:
 --
--- >>> pPrint $ fromHeaders [("Prefer", "resolution=ignore-duplicates, count=exact")]
+-- >>> pPrint $ fromHeaders True [("Prefer", "resolution=ignore-duplicates, count=exact")]
 -- Preferences
 --     { preferResolution = Just IgnoreDuplicates
 --     , preferRepresentation = Nothing
@@ -65,7 +66,7 @@ data Preferences
 --
 -- Multiple headers can also be used:
 --
--- >>> pPrint $ fromHeaders [("Prefer", "resolution=ignore-duplicates"), ("Prefer", "count=exact"), ("Prefer", "missing=null")]
+-- >>> pPrint $ fromHeaders True [("Prefer", "resolution=ignore-duplicates"), ("Prefer", "count=exact"), ("Prefer", "missing=null")]
 -- Preferences
 --     { preferResolution = Just IgnoreDuplicates
 --     , preferRepresentation = Nothing
@@ -77,13 +78,13 @@ data Preferences
 --
 -- If a preference is set more than once, only the first is used:
 --
--- >>> preferTransaction $ fromHeaders [("Prefer", "tx=commit, tx=rollback")]
+-- >>> preferTransaction $ fromHeaders True [("Prefer", "tx=commit, tx=rollback")]
 -- Just Commit
 --
 -- This is also the case across multiple headers:
 --
 -- >>> :{
---   preferResolution . fromHeaders $
+--   preferResolution . fromHeaders True $
 --     [ ("Prefer", "resolution=ignore-duplicates")
 --     , ("Prefer", "resolution=merge-duplicates")
 --     ]
@@ -92,12 +93,12 @@ data Preferences
 --
 -- Preferences not recognized by the application are ignored:
 --
--- >>> preferResolution $ fromHeaders [("Prefer", "resolution=foo")]
+-- >>> preferResolution $ fromHeaders True [("Prefer", "resolution=foo")]
 -- Nothing
 --
 -- Preferences can be separated by arbitrary amounts of space, lower-case header is also recognized:
 --
--- >>> pPrint $ fromHeaders [("prefer", "count=exact,    tx=commit   ,return=representation , missing=default")]
+-- >>> pPrint $ fromHeaders True [("prefer", "count=exact,    tx=commit   ,return=representation , missing=default")]
 -- Preferences
 --     { preferResolution = Nothing
 --     , preferRepresentation = Just Full
@@ -107,14 +108,14 @@ data Preferences
 --     , preferMissing = Just ApplyDefaults
 --     }
 --
-fromHeaders :: [HTTP.Header] -> Preferences
+fromHeaders :: Bool -> [HTTP.Header] -> Preferences
-fromHeaders headers =
+fromHeaders allowTxEndOverride headers =
   Preferences
     { preferResolution     = parsePrefs [MergeDuplicates, IgnoreDuplicates]
     , preferRepresentation = parsePrefs [Full, None, HeadersOnly]
     , preferParameters     = parsePrefs [SingleObject]
     , preferCount          = parsePrefs [ExactCount, PlannedCount, EstimatedCount]
-    , preferTransaction    = parsePrefs [Commit, Rollback]
+    , preferTransaction    = if allowTxEndOverride then parsePrefs [Commit, Rollback] else Nothing
     , preferMissing        = parsePrefs [ApplyDefaults, ApplyNulls]
     }
   where
@@ -128,6 +129,22 @@ fromHeaders headers =
     prefMap :: ToHeaderValue a => [a] -> Map.Map ByteString a
     prefMap = Map.fromList . fmap (\pref -> (toHeaderValue pref, pref))
 
+prefAppliedHeader :: Preferences -> Maybe HTTP.Header
+prefAppliedHeader Preferences {preferResolution, preferRepresentation, preferParameters, preferCount, preferTransaction, preferMissing } =
+  if null prefsVals
+    then Nothing
+    else Just (HTTP.hPreferenceApplied, combined)
+  where
+    combined = BS.intercalate ", " prefsVals
+    prefsVals = catMaybes [
+        toHeaderValue <$> preferResolution
+      , toHeaderValue <$> preferMissing
+      , toHeaderValue <$> preferRepresentation
+      , toHeaderValue <$> preferParameters
+      , toHeaderValue <$> preferCount
+      , toHeaderValue <$> preferTransaction
+      ]
+
 -- |
 -- Convert a preference into the value that we look for in the 'Prefer' headers.
 --
@@ -137,16 +154,6 @@ fromHeaders headers =
 class ToHeaderValue a where
   toHeaderValue :: a -> ByteString
 
--- |
--- Header to indicate that a preference has been applied.
---
--- >>> toAppliedHeader MergeDuplicates
--- ("Preference-Applied","resolution=merge-duplicates")
---
-class ToHeaderValue a => ToAppliedHeader a where
-  toAppliedHeader :: a -> HTTP.Header
-  toAppliedHeader x = (HTTP.hPreferenceApplied, toHeaderValue x)
-
 -- | How to handle duplicate values.
 data PreferResolution
   = MergeDuplicates
@@ -156,8 +163,6 @@ instance ToHeaderValue PreferResolution where
   toHeaderValue MergeDuplicates  = "resolution=merge-duplicates"
   toHeaderValue IgnoreDuplicates = "resolution=ignore-duplicates"
 
-instance ToAppliedHeader PreferResolution
-
 -- |
 -- How to return the mutated data.
 --
@@ -168,8 +173,6 @@ data PreferRepresentation
   | None        -- ^ Return nothing from the mutated data.
   deriving Eq
 
-instance ToAppliedHeader PreferRepresentation
-
 instance ToHeaderValue PreferRepresentation where
   toHeaderValue Full        = "return=representation"
   toHeaderValue None        = "return=minimal"
@@ -209,8 +212,6 @@ instance ToHeaderValue PreferTransaction where
   toHeaderValue Commit   = "tx=commit"
   toHeaderValue Rollback = "tx=rollback"
 
-instance ToAppliedHeader PreferTransaction
-
 -- |
 -- How to handle the insertion/update when the keys specified in ?columns are not present
 -- in the json body.
@@ -222,5 +223,3 @@ data PreferMissing
 instance ToHeaderValue PreferMissing where
   toHeaderValue ApplyDefaults = "missing=default"
   toHeaderValue ApplyNulls    = "missing=null"
-
-instance ToAppliedHeader PreferMissing

src/PostgREST/App.hs

@@ -150,7 +150,6 @@ postgrestResponse appState conf@AppConfig{..} maybeSchemaCache pgVer authResult@
     liftEither . mapLeft Error.ApiRequestError $
       ApiRequest.userApiRequest conf req body
 
-  Response.optionalRollback conf apiRequest $
   handleRequest authResult conf appState (Just authRole /= configDbAnonRole) configDbPreparedStatements pgVer apiRequest sCache
 
 runDbHandler :: AppState.AppState -> SQL.IsolationLevel -> SQL.Mode -> Bool -> Bool -> DbHandler b -> Handler IO b

src/PostgREST/Config.hs

@@ -479,26 +479,26 @@ readPGRSTEnvironment =
 -- >>> addFallbackAppName ver "postgres:///postgres?host=server&port=5432"
 -- "postgres:///postgres?host=server&port=5432&fallback_application_name=PostgREST%2011.1.0%20%285a04ec7%29"
 --
--- >>> addFallbackAppName ver "host=localhost port=5432 dbname=postgres"
--- "host=localhost port=5432 dbname=postgres fallback_application_name='PostgREST 11.1.0 (5a04ec7)'"
---
 -- >>> addFallbackAppName ver "postgresql://"
 -- "postgresql://?fallback_application_name=PostgREST%2011.1.0%20%285a04ec7%29"
 --
--- >>> addFallbackAppName strangeVer "host=localhost port=5432 dbname=postgres"
--- "host=localhost port=5432 dbname=postgres fallback_application_name='PostgREST 11\\'1&0@#$%,.:\"[]{}?+^()=asdfqwer'"
---
 -- >>> addFallbackAppName strangeVer "postgres:///postgres?host=server&port=5432"
 -- "postgres:///postgres?host=server&port=5432&fallback_application_name=PostgREST%2011%271%260%40%23%24%25%2C.%3A%22%5B%5D%7B%7D%3F%2B%5E%28%29%3Dasdfqwer"
+--
+-- >>> addFallbackAppName ver "postgres://user:invalid_chars[]#@host:5432/postgres"
+-- "postgres://user:invalid_chars[]#@host:5432/postgres"
+--
+-- >>> addFallbackAppName ver "invalid_uri1=val1 invalid_uri2=val2"
+-- "invalid_uri1=val1 invalid_uri2=val2"
 addFallbackAppName :: ByteString -> Text -> Text
 addFallbackAppName version dbUri = dbUri <>
   case uriQuery <$> parseURI (toS dbUri) of
-    Nothing  -> " " <> keyValFmt -- Assume key/value connection string if the uri is not valid
+    -- Does not add the application name to key=val connection strings or invalid URIs
+    Nothing  -> mempty
     Just ""  -> "?" <> uriFmt
     Just "?" -> uriFmt
     _        -> "&" <> uriFmt
   where
     uriFmt = pKeyWord <> toS (escapeURIString isUnescapedInURIComponent $ toS pgrstVer)
-    keyValFmt = pKeyWord <> "'" <> T.replace "'" "\\'" pgrstVer <> "'"
     pKeyWord = "fallback_application_name="
     pgrstVer = "PostgREST " <> T.decodeUtf8 version

src/PostgREST/Config/Database.hs

@@ -154,7 +154,7 @@ queryRoleSettings prepared =
       select
         kv.rolname,
         i.value as iso_lvl,
-        array_agg(row(kv.key, kv.value)) filter (where key <> 'default_transation_isolation') as role_settings
+        coalesce(array_agg(row(kv.key, kv.value)) filter (where key <> 'default_transaction_isolation'), '{}') as role_settings
       from kv_settings kv
       join pg_settings ps on ps.name = kv.key and ps.context = 'user'
       left join iso_setting i on i.rolname = kv.rolname

src/PostgREST/MediaType.hs

@@ -140,6 +140,7 @@ decodeMediaType mt =
     "application/vnd.pgrst.object+json":rest -> checkSingularNullStrip rest
     "application/vnd.pgrst.object":rest      -> checkSingularNullStrip rest
     "application/vnd.pgrst.array+json":rest  -> checkArrayNullStrip rest
+    "application/vnd.pgrst.array":rest       -> checkArrayNullStrip rest
     "*/*":_                                  -> MTAny
     other:_                                  -> MTOther other
     _                                        -> MTAny

src/PostgREST/Plan.hs

@@ -226,22 +226,19 @@ resolveTableFieldName table fieldName =
   fromMaybe (unknownField fieldName []) $ HMI.lookup fieldName (tableColumns table) >>=
     Just . resolveColumnField
 
-resolveTableField :: Table -> Field -> CoercibleField
-resolveTableField table (fieldName, []) = resolveTableFieldName table fieldName
-resolveTableField table (fieldName, jp) =
-  case resolveTableFieldName table fieldName of
-    -- types that are already json/jsonb don't need to be converted with `to_jsonb` for using arrow operators `data->attr`
-    -- this prevents indexes not applying https://github.com/PostgREST/postgrest/issues/2594
-    cf@CoercibleField{cfIRType="json"}  -> cf{cfJsonPath=jp}
-    cf@CoercibleField{cfIRType="jsonb"} -> cf{cfJsonPath=jp}
-    -- other types will get converted `to_jsonb(col)->attr`
-    cf                                  -> cf{cfJsonPath=jp, cfToJson=True}
-
 -- | Resolve a type within the context based on the given field name and JSON path. Although there are situations where failure to resolve a field is considered an error (see `resolveOrError`), there are also situations where we allow it (RPC calls). If it should be an error and `resolveOrError` doesn't fit, ensure to check the `cfIRType` isn't empty.
 resolveTypeOrUnknown :: ResolverContext -> Field -> CoercibleField
-resolveTypeOrUnknown ResolverContext{..} field@(fn, jp) =
+resolveTypeOrUnknown ResolverContext{..} (fn, jp) =
-  fromMaybe (unknownField fn jp) $ HM.lookup qi tables >>=
+  case res of
-    Just . flip resolveTableField field
+    -- types that are already json/jsonb don't need to be converted with `to_jsonb` for using arrow operators `data->attr`
+    -- this prevents indexes not applying https://github.com/PostgREST/postgrest/issues/2594
+    cf@CoercibleField{cfIRType="json"}  -> cf{cfJsonPath=jp, cfToJson=False}
+    cf@CoercibleField{cfIRType="jsonb"} -> cf{cfJsonPath=jp, cfToJson=False}
+    -- other types will get converted `to_jsonb(col)->attr`, even unknown types
+    cf                                  -> cf{cfJsonPath=jp, cfToJson=True}
+  where
+    res = fromMaybe (unknownField fn jp) $ HM.lookup qi tables >>=
+          Just . flip resolveTableFieldName fn
 
 -- | Install any pre-defined data representation from source to target to coerce this reference.
 --
@@ -383,7 +380,7 @@ addRels schema action allRels parentNode (Node rPlan@ReadPlan{relName,relHint,re
             Relationship{relCardinality=M2M _} -> -- m2m does internal implicit joins that don't need aliasing
               rPlan{from=relForeignTable r, relToParent=Just r, relAggAlias=aggAlias, relJoinConds=getJoinConditions Nothing parentAlias r}
             ComputedRelationship{} ->
-              rPlan{from=relForeignTable r, relToParent=Just r{relTable=maybe (relTable r) (QualifiedIdentifier mempty) parentAlias}, relAggAlias=aggAlias, fromAlias=newAlias}
+              rPlan{from=relForeignTable r, relToParent=Just r{relTableAlias=maybe (relTable r) (QualifiedIdentifier mempty) parentAlias}, relAggAlias=aggAlias, fromAlias=newAlias}
             _ ->
               rPlan{from=relForeignTable r, relToParent=Just r, relAggAlias=aggAlias, fromAlias=newAlias, relJoinConds=getJoinConditions newAlias parentAlias r}
           ) <$> rel

src/PostgREST/Query.hs

@@ -226,9 +226,9 @@ optionalRollback AppConfig{..} ApiRequest{iPreferences=Preferences{..}} = do
     SQL.condemn
   where
     shouldCommit =
-      configDbTxAllowOverride && preferTransaction == Just Commit
+      preferTransaction == Just Commit
     shouldRollback =
-      configDbTxAllowOverride && preferTransaction == Just Rollback
+      preferTransaction == Just Rollback
 
 -- | Runs local (transaction scoped) GUCs for every request.
 setPgLocals :: AppConfig  -> KM.KeyMap JSON.Value -> BS.ByteString -> [(ByteString, ByteString)] ->

src/PostgREST/Query/QueryBuilder.hs

@@ -203,7 +203,7 @@ readPlanToCountQuery (Node ReadPlan{from=mainQi, fromAlias=tblAlias, where_=logi
     then mempty
     else " WHERE " ) <>
   intercalateSnippet " AND " (
-    map (pgFmtLogicTree qi) logicForest ++
+    map (pgFmtLogicTreeCount qi) logicForest ++
     map pgFmtJoinCondition relJoinConds ++
     subQueries
   )
@@ -216,6 +216,18 @@ readPlanToCountQuery (Node ReadPlan{from=mainQi, fromAlias=tblAlias, where_=logi
       if joinType == Just JTInner
         then ("EXISTS (" <> readPlanToCountQuery readReq <> " )"):rest
         else rest
+    findNullEmbedRel fld = find (\(Node ReadPlan{relAggAlias} _) -> fld == relAggAlias) forest
+
+    -- https://github.com/PostgREST/postgrest/pull/2930#discussion_r1325293698
+    pgFmtLogicTreeCount :: QualifiedIdentifier -> CoercibleLogicTree -> SQL.Snippet
+    pgFmtLogicTreeCount qiCount (CoercibleExpr hasNot op frst) = SQL.sql notOp <> " (" <> intercalateSnippet (opSql op) (pgFmtLogicTreeCount qiCount <$> frst) <> ")"
+      where
+        notOp =  if hasNot then "NOT" else mempty
+        opSql And = " AND "
+        opSql Or  = " OR "
+    pgFmtLogicTreeCount _ (CoercibleStmnt (CoercibleFilterNullEmbed hasNot fld)) =
+      maybe mempty (\x -> (if not hasNot then "NOT " else mempty) <> "EXISTS (" <> readPlanToCountQuery x <> ")") (findNullEmbedRel fld)
+    pgFmtLogicTreeCount qiCount (CoercibleStmnt flt) = pgFmtFilter qiCount flt
 
 limitedQuery :: SQL.Snippet -> Maybe Integer -> SQL.Snippet
 limitedQuery query maxRows = query <> SQL.sql (maybe mempty (\x -> " LIMIT " <> BS.pack (show x)) maxRows)
@@ -230,7 +242,9 @@ getQualifiedIdentifier rel mainQi tblAlias = case rel of
 fromF :: Maybe Relationship -> QualifiedIdentifier -> Maybe Alias -> SQL.Snippet
 fromF rel mainQi tblAlias = "FROM " <>
   (case rel of
-    Just ComputedRelationship{relFunction,relTable} -> fromQi relFunction <> "(" <> pgFmtIdent (qiName relTable) <> ")"
+    -- Due to the use of CTEs on RPC, we need to cast the parameter to the table name in case of function overloading.
+    -- See https://github.com/PostgREST/postgrest/issues/2963#issuecomment-1736557386
+    Just ComputedRelationship{relFunction,relTableAlias,relTable} -> fromQi relFunction <> "(" <> pgFmtIdent (qiName relTableAlias) <> "::" <> fromQi relTable <> ")"
     _                                                             -> fromQi mainQi) <>
   maybe mempty (\a -> " AS " <> pgFmtIdent a) tblAlias <>
   (case rel of

src/PostgREST/Query/SqlFragment.hs

@@ -15,6 +15,7 @@ module PostgREST.Query.SqlFragment
   , mutRangeF
   , orderF
   , pgFmtColumn
+  , pgFmtFilter
   , pgFmtIdent
   , pgFmtJoinCondition
   , pgFmtLogicTree
@@ -336,7 +337,7 @@ pgFmtArrayLiteralForField values _ = unknownLiteral (pgBuildArrayLiteral values)
 
 
 pgFmtFilter :: QualifiedIdentifier -> CoercibleFilter -> SQL.Snippet
-pgFmtFilter _ (CoercibleFilterNullEmbed hasNot fld) = pgFmtIdent fld <> " IS " <> (if hasNot then "NOT" else mempty) <> " NULL"
+pgFmtFilter _ (CoercibleFilterNullEmbed hasNot fld) = pgFmtIdent fld <> " IS " <> (if not hasNot then "NOT " else mempty) <> "DISTINCT FROM NULL"
 pgFmtFilter _ (CoercibleFilter _ (NoOpExpr _)) = mempty -- TODO unreachable because NoOpExpr is filtered on QueryParams
 pgFmtFilter table (CoercibleFilter fld (OpExpr hasNot oper)) = notOp <> " " <> pgFmtField table fld <> case oper of
    Op op val  -> " " <> simpleOperator op <> " " <> pgFmtUnknownLiteralForField (unknownLiteral val) fld

src/PostgREST/Response.hs

@@ -17,9 +17,6 @@ module PostgREST.Response
   , updateResponse
   , addRetryHint
   , isServiceUnavailable
-  , optionalRollback
-  , concatPrefAppsHeaders
-  , addPrefToHeaders
   , traceHeaderMiddleware
   ) where
 
@@ -42,10 +39,9 @@ import qualified PostgREST.Response.OpenAPI as OpenAPI
 import PostgREST.ApiRequest              (ApiRequest (..),
                                           InvokeMethod (..))
 import PostgREST.ApiRequest.Preferences  (PreferRepresentation (..),
-                                          PreferTransaction (..),
                                           Preferences (..),
-                                          shouldCount,
+                                          prefAppliedHeader,
-                                          toAppliedHeader)
+                                          shouldCount)
 import PostgREST.ApiRequest.QueryParams  (QueryParams (..))
 import PostgREST.Config                  (AppConfig (..))
 import PostgREST.MediaType               (MediaType (..))
@@ -68,11 +64,12 @@ import Protolude.Conv (toS)
 
 
 readResponse :: Bool -> QualifiedIdentifier -> ApiRequest -> ResultSet -> Wai.Response
-readResponse headersOnly identifier ctxApiRequest@ApiRequest{..} resultSet = case resultSet of
+readResponse headersOnly identifier ctxApiRequest@ApiRequest{iPreferences=Preferences{..},..} resultSet = case resultSet of
   RSStandard{..} -> do
     let
       (status, contentRange) = RangeQuery.rangeStatusHeader iTopLevelRange rsQueryTotal rsTableTotal
       response = gucResponse rsGucStatus rsGucHeaders
+      prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing Nothing Nothing preferCount preferTransaction Nothing
       headers =
         [ contentRange
         , ( "Content-Location"
@@ -82,6 +79,7 @@ readResponse headersOnly identifier ctxApiRequest@ApiRequest{..} resultSet = cas
           )
         ]
         ++ contentTypeHeaders ctxApiRequest
+        ++ prefHeader
       rsOrErrBody = if status == HTTP.status416
         then Error.errorPayload $ Error.ApiRequestError $ ApiRequestTypes.InvalidRange
           $ ApiRequestTypes.OutOfBounds (show $ RangeQuery.rangeOffset iTopLevelRange) (maybe "0" show rsTableTotal)
@@ -98,6 +96,9 @@ createResponse QualifiedIdentifier{..} MutateReadPlan{mrMutatePlan} ctxApiReques
     let
       pkCols = case mrMutatePlan of { Insert{insPkCols} -> insPkCols; _ -> mempty;}
       response = gucResponse rsGucStatus rsGucHeaders
+      prefHeader = prefAppliedHeader $
+        Preferences (if null pkCols && isNothing (qsOnConflict iQueryParams) then Nothing else preferResolution)
+        preferRepresentation Nothing preferCount preferTransaction preferMissing
       headers =
         catMaybes
           [ if null rsLocation then
@@ -111,20 +112,15 @@ createResponse QualifiedIdentifier{..} MutateReadPlan{mrMutatePlan} ctxApiReques
                 )
           , Just . RangeQuery.contentRangeH 1 0 $
               if shouldCount preferCount then Just rsQueryTotal else Nothing
-          , if null pkCols && isNothing (qsOnConflict iQueryParams) then
+          , prefHeader
-              Nothing
-            else
-              toAppliedHeader <$> preferResolution
-          , toAppliedHeader <$> preferMissing
           ]
 
     case preferRepresentation of
-      Just Full -> response HTTP.status201 (addPrefToHeaders headers Full ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
+      Just Full -> response HTTP.status201 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
-      Just None -> response HTTP.status201 (addPrefToHeaders headers None) mempty
+      Just None -> response HTTP.status201 headers mempty
-      Just HeadersOnly -> response HTTP.status201 (addPrefToHeaders headers HeadersOnly) mempty
+      Just HeadersOnly -> response HTTP.status201 headers mempty
       Nothing -> response HTTP.status201 headers mempty
 
-
   RSPlan plan ->
     Wai.responseLBS HTTP.status200 (contentTypeHeaders ctxApiRequest) $ LBS.fromStrict plan
 
@@ -137,12 +133,12 @@ updateResponse ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} resultSet
       contentRangeHeader =
         Just . RangeQuery.contentRangeH 0 (rsQueryTotal - 1) $
           if shouldCount preferCount then Just rsQueryTotal else Nothing
-      headers = catMaybes [contentRangeHeader, toAppliedHeader <$> preferMissing]
+      prefHeader = prefAppliedHeader $ Preferences Nothing preferRepresentation Nothing preferCount preferTransaction preferMissing
+      headers = catMaybes [contentRangeHeader, prefHeader]
 
     case preferRepresentation of
-        Just Full -> response HTTP.status200 (addPrefToHeaders headers Full ++ contentTypeHeaders ctxApiRequest)
+        Just Full -> response HTTP.status200 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
-          (LBS.fromStrict rsBody)
+        Just None -> response HTTP.status204 headers mempty
-        Just None -> response HTTP.status204 (addPrefToHeaders headers None) mempty
         _ -> response HTTP.status204 headers mempty
 
   RSPlan plan ->
@@ -153,11 +149,12 @@ singleUpsertResponse ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} resu
   RSStandard {..} -> do
     let
       response = gucResponse rsGucStatus rsGucHeaders
+      prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing preferRepresentation Nothing preferCount preferTransaction Nothing
 
     case preferRepresentation of
-      Just Full -> response HTTP.status200 (contentTypeHeaders ctxApiRequest ++ [toAppliedHeader Full]) (LBS.fromStrict rsBody)
+      Just Full -> response HTTP.status200 (contentTypeHeaders ctxApiRequest ++ prefHeader) (LBS.fromStrict rsBody)
-      Just None -> response HTTP.status204 [toAppliedHeader None] mempty
+      Just None -> response HTTP.status204 prefHeader mempty
-      _ -> response HTTP.status204 [] mempty
+      _ -> response HTTP.status204 prefHeader mempty
 
   RSPlan plan ->
     Wai.responseLBS HTTP.status200 (contentTypeHeaders ctxApiRequest) $ LBS.fromStrict plan
@@ -170,12 +167,12 @@ deleteResponse ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} resultSet
       contentRangeHeader =
         RangeQuery.contentRangeH 1 0 $
           if shouldCount preferCount then Just rsQueryTotal else Nothing
-      headers = [contentRangeHeader]
+      prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing preferRepresentation Nothing preferCount preferTransaction Nothing
+      headers = contentRangeHeader : prefHeader
 
     case preferRepresentation of
-        Just Full -> response HTTP.status200 (addPrefToHeaders headers Full ++ contentTypeHeaders ctxApiRequest)
+        Just Full -> response HTTP.status200 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
-          (LBS.fromStrict rsBody)
+        Just None -> response HTTP.status204 headers mempty
-        Just None -> response HTTP.status204 (addPrefToHeaders headers None) mempty
         _ -> response HTTP.status204 headers mempty
 
   RSPlan plan ->
@@ -209,7 +206,7 @@ respondInfo allowHeader =
   Wai.responseLBS HTTP.status200 [allOrigins, (HTTP.hAllow, allowHeader)] mempty
 
 invokeResponse :: InvokeMethod -> Routine -> ApiRequest -> ResultSet -> Wai.Response
-invokeResponse invMethod proc ctxApiRequest@ApiRequest{..} resultSet = case resultSet of
+invokeResponse invMethod proc ctxApiRequest@ApiRequest{iPreferences=Preferences{..}, ..} resultSet = case resultSet of
   RSStandard {..} -> do
     let
       response = gucResponse rsGucStatus rsGucHeaders
@@ -219,7 +216,8 @@ invokeResponse invMethod proc ctxApiRequest@ApiRequest{..} resultSet = case resu
         then Error.errorPayload $ Error.ApiRequestError $ ApiRequestTypes.InvalidRange
           $ ApiRequestTypes.OutOfBounds (show $ RangeQuery.rangeOffset iTopLevelRange) (maybe "0" show rsTableTotal)
         else LBS.fromStrict rsBody
-      headers = [contentRange]
+      prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing Nothing preferParameters preferCount preferTransaction Nothing
+      headers = contentRange : prefHeader
 
     if Routine.funcReturnsVoid proc then
         response HTTP.status204 headers mempty
@@ -278,62 +276,12 @@ addRetryHint delay response = do
 isServiceUnavailable :: Wai.Response -> Bool
 isServiceUnavailable response = Wai.responseStatus response == HTTP.status503
 
-optionalRollback :: AppConfig -> ApiRequest -> ExceptT Error.Error IO Wai.Response -> ExceptT Error.Error IO Wai.Response
-optionalRollback AppConfig{..} ApiRequest{iPreferences=Preferences{..}} resp = do
-  newRes <- catchError resp $ return . Error.errorResponseFor
-  return $ Wai.mapResponseHeaders preferenceApplied newRes
-  where
-    shouldCommit =
-      configDbTxAllowOverride && preferTransaction == Just Commit
-    shouldRollback =
-      configDbTxAllowOverride && preferTransaction == Just Rollback
-    preferenceApplied
-      | shouldCommit =
-          addHeadersIfNotIncluded
-            [toAppliedHeader Commit]
-      | shouldRollback =
-          addHeadersIfNotIncluded
-            [toAppliedHeader Rollback]
-      | otherwise =
-          identity
-
 -- | Add headers not already included to allow the user to override them instead of duplicating them
 addHeadersIfNotIncluded :: [HTTP.Header] -> [HTTP.Header] -> [HTTP.Header]
 addHeadersIfNotIncluded newHeaders initialHeaders =
-  filter (\(nk, nv) -> isNothing $ find (\(ik, iv) -> ik == nk && nv == iv) initialHeaders) newHeaders ++
+  filter (\(nk, _) -> isNothing $ find (\(ik, _) -> ik == nk) initialHeaders) newHeaders ++
   initialHeaders
 
--- |  Filters out multiple Preference-Applied Headers from the list and concatenate them into a single Preference-Applied header:
---
--- >>> :{
---  concatPrefAppsHeaders
---     [("Content-Type","application/json")
---     , ("Preference-Applied","tx=commit")
---     , ("Preference-Applied","return=minimal")]
--- :}
--- [("Content-Type","application/json"),("Preference-Applied","tx=commit, return=minimal")]
-
-concatPrefAppsHeaders :: [HTTP.Header] -> [HTTP.Header]
-concatPrefAppsHeaders headers = otherHeaders ++ [(HTTP.hPreferenceApplied, combinedPrefApps)]
-  where
-  (prefApps, otherHeaders) = L.partition (\(k, _) -> k == HTTP.hPreferenceApplied) headers
-  prefAppsValues = [ v | (_,v) <- prefApps]
-  combinedPrefApps = BS.intercalate ", " prefAppsValues
-
--- | Given response headers and a preferRepresentation value, add
---   preferRepresentation to Preference-Applied
---
--- >>> :{
---  addPrefToHeaders
---      [("Content-Type", "application/json")
---      , ("Preference-Applied", "tx=commit")]
---      None
--- :}
--- [("Content-Type","application/json"),("Preference-Applied","tx=commit, return=minimal")]
-
-addPrefToHeaders :: [HTTP.Header] -> PreferRepresentation -> [HTTP.Header]
-addPrefToHeaders headers pref = concatPrefAppsHeaders (headers ++ [toAppliedHeader pref])
-
 traceHeaderMiddleware :: AppConfig -> Wai.Middleware
 traceHeaderMiddleware AppConfig{configServerTraceHeader} app req respond =
   case configServerTraceHeader of

src/PostgREST/SchemaCache.hs

@@ -882,6 +882,7 @@ allComputedRels =
     (QualifiedIdentifier <$> column HD.text <*> column HD.text) <*>
     (QualifiedIdentifier <$> column HD.text <*> column HD.text) <*>
     (QualifiedIdentifier <$> column HD.text <*> column HD.text) <*>
+    pure (QualifiedIdentifier mempty mempty) <*>
     column HD.bool <*>
     column HD.bool
 

src/PostgREST/SchemaCache/Relationship.hs

@@ -31,6 +31,7 @@ data Relationship = Relationship
   { relFunction     :: QualifiedIdentifier
   , relTable        :: QualifiedIdentifier
   , relForeignTable :: QualifiedIdentifier
+  , relTableAlias   :: QualifiedIdentifier
   , relToOne        :: Bool
   , relIsSelf       :: Bool
   }

stack.yaml

@@ -12,5 +12,5 @@ nix:
 extra-deps:
   - git: https://github.com/PostgREST/postgresql-libpq.git
     commit: 890a0a16cf57dd401420fdc6c7d576fb696003bc
-  - hasql-notifications-0.2.0.5
+  - hasql-notifications-0.2.0.6
   - hasql-pool-0.10

stack.yaml.lock

@@ -16,12 +16,12 @@ packages:
     commit: 890a0a16cf57dd401420fdc6c7d576fb696003bc
     git: https://github.com/PostgREST/postgresql-libpq.git
 - completed:
-    hackage: hasql-notifications-0.2.0.5@sha256:6c67d2ee42e948162e89176a2f52a2c6bb562678b33688e96e048be9b3d74122,2028
+    hackage: hasql-notifications-0.2.0.6@sha256:16d783f5cd1660fad924fd3769380889de5804e057f09b304dcdc3a3ff11eb3c,2028
     pantry-tree:
-      sha256: 8493755b0817a36b910a37d568769ca49ab715ef5dd935a0d09f0716aa39da51
+      sha256: 2319743501bb3c0bef801014ce61308b8666cef86ae5a97a0a283c0c1ec12d4f
       size: 452
   original:
-    hackage: hasql-notifications-0.2.0.5
+    hackage: hasql-notifications-0.2.0.6
 - completed:
     hackage: hasql-pool-0.10@sha256:912197a328acb85505f98bb9700d61f366b87659ca45126c5c2d636687b801c3,2112
     pantry-tree:

test/memory/memory-tests.sh

@@ -102,7 +102,7 @@ postJsonArrayTest(){
 
 echo "Running memory usage tests.."
 
-jsonKeyTest "1M" "POST" "/rpc/leak?columns=blob" "16M"
+jsonKeyTest "1M" "POST" "/rpc/leak?columns=blob" "23M"
 jsonKeyTest "1M" "POST" "/leak?columns=blob" "16M"
 jsonKeyTest "1M" "PATCH" "/leak?id=eq.1&columns=blob" "16M"
 

test/spec/Feature/Query/ComputedRelsSpec.hs

@@ -192,3 +192,29 @@ spec = describe "computed relationships" $ do
           {"name":"Windows 10","computed_clients":{"name":"Microsoft"}}
         ]}
       ]|] { matchHeaders = [matchContentTypeJson] }
+
+  -- https://github.com/PostgREST/postgrest/issues/2963
+  context "can be defined using overloaded functions" $ do
+    it "tables" $ do
+      get "/items?select=*,computed_rel_overload(*)&limit=1"
+        `shouldRespondWith`
+        [json|
+          [{"id":1,"computed_rel_overload":[{"id":1}]}]
+        |] { matchHeaders = [matchContentTypeJson] }
+      get "/items2?select=*,computed_rel_overload(*)&limit=1"
+        `shouldRespondWith`
+        [json|
+          [{"id":1,"computed_rel_overload":[{"id":1},{"id":2}]}]
+        |] { matchHeaders = [matchContentTypeJson] }
+
+    it "rpc" $ do
+      get "/rpc/search?id=1&select=*,computed_rel_overload(*)"
+        `shouldRespondWith`
+        [json|
+          [{"id":1,"computed_rel_overload":[{"id":1}]}]
+        |] { matchHeaders = [matchContentTypeJson] }
+      get "/rpc/search2?id=1&select=*,computed_rel_overload(*)"
+        `shouldRespondWith`
+        [json|
+          [{"id":1,"computed_rel_overload":[{"id":1},{"id":2}]}]
+        |] { matchHeaders = [matchContentTypeJson] }

test/spec/Feature/Query/DeleteSpec.hs

@@ -38,7 +38,7 @@ spec =
           `shouldRespondWith` [json|[{"id":2}]|]
           { matchStatus  = 200
           , matchHeaders = ["Content-Range" <:> "*/1"
-                           , "Preference-Applied" <:> "return=representation"]
+                           , "Preference-Applied" <:> "return=representation, count=exact"]
           }
 
       it "ignores ?select= when return not set or return=minimal" $ do

test/spec/Feature/Query/InsertSpec.hs

@@ -102,7 +102,7 @@ spec actualPgVersion = do
           , matchHeaders = [ matchContentTypeJson
                            , matchHeaderAbsent hLocation
                            , "Content-Range" <:> "*/1"
-                           , "Preference-Applied" <:> "return=representation"]
+                           , "Preference-Applied" <:> "return=representation, count=exact"]
           }
 
       it "can rename and cast the selected columns" $

test/spec/Feature/Query/JsonOperatorSpec.hs

@@ -300,3 +300,11 @@ spec actualPgVersion = describe "json and jsonb operators" $ do
          "code": "PGRST100",
          "hint": null} |]
       { matchStatus = 400, matchHeaders = [matchContentTypeJson] }
+
+  it "works when an RPC returns a dynamic TABLE with a composite type" $
+    get "/rpc/returns_complex?select=val->r&val->i=gt.0.5&order=val->>i.desc" `shouldRespondWith`
+      [json|[
+        {"r":0.3},
+        {"r":0.2}
+      ]|]
+      { matchStatus = 200, matchHeaders = [matchContentTypeJson] }

test/spec/Feature/Query/NullsStrip.hs

@@ -45,6 +45,16 @@ spec =
           , matchHeaders = [matchCTArrayStrip]
           }
 
+      it "strips nulls when Accept: application/vnd.pgrst.array;nulls=stripped" $
+        request methodGet  "/organizations?select=*"
+          [("Accept","application/vnd.pgrst.array;nulls=stripped")]
+          ""
+          `shouldRespondWith`
+          [json|[{"id":1,"name":"Referee Org","manager_id":1},{"id":2,"name":"Auditor Org","manager_id":2},{"id":3,"name":"Acme","referee":1,"auditor":2,"manager_id":3},{"id":4,"name":"Umbrella","referee":1,"auditor":2,"manager_id":4},{"id":5,"name":"Cyberdyne","referee":3,"auditor":4,"manager_id":5},{"id":6,"name":"Oscorp","referee":3,"auditor":4,"manager_id":6}]|]
+          { matchStatus  = 200
+          , matchHeaders = [matchCTArrayStrip]
+          }
+
       it "strips nulls when Accept: application/vnd.pgrst.object+json;nulls=stripped" $
         request methodGet  "/organizations?limit=1"
           [singularStrip]

test/spec/Feature/Query/RangeSpec.hs

@@ -145,6 +145,14 @@ spec = do
         it "returns whole range with status 200" $
           get "/items" `shouldRespondWith` 200
 
+      context "count with an empty body" $ do
+        it "returns empty body with Content-Range */0" $
+          request methodGet "/items?id=eq.0"
+            [("Prefer", "count=exact")] ""
+            `shouldRespondWith`
+              [json|[]|]
+              { matchHeaders = ["Content-Range" <:> "*/0"] }
+
       context "when I don't want the count" $ do
         it "returns range Content-Range with /*" $
           request methodGet "/menagerie"
@@ -211,7 +219,20 @@ spec = do
                              , "Content-Range" <:> "2-4/*" ]
             }
 
-      it "succeeds if offset equals 0 as a no-op" $
+      context "succeeds if offset equals 0 as a no-op" $ do
+        it  "no items" $ do
+          get "/items?offset=0&id=eq.0"
+            `shouldRespondWith`
+              [json|[]|]
+              { matchHeaders = ["Content-Range" <:> "*/*"] }
+
+          request methodGet "/items?offset=0&id=eq.0"
+            [("Prefer", "count=exact")] ""
+            `shouldRespondWith`
+              [json|[]|]
+              { matchHeaders = ["Content-Range" <:> "*/0"] }
+
+        it  "one or more items" $
           get "/items?select=id&offset=0&order=id"
             `shouldRespondWith`
               [json|[{"id":1},{"id":2},{"id":3},{"id":4},{"id":5},{"id":6},{"id":7},{"id":8},{"id":9},{"id":10},{"id":11},{"id":12},{"id":13},{"id":14},{"id":15}]|]

test/spec/Feature/Query/RelatedQueriesSpec.hs

@@ -2,6 +2,7 @@ module Feature.Query.RelatedQueriesSpec where
 
 import Network.Wai (Application)
 
+import Network.HTTP.Types
 import Test.Hspec
 import Test.Hspec.Wai
 import Test.Hspec.Wai.JSON
@@ -256,3 +257,124 @@ spec = describe "related queries" $ do
         { matchStatus  = 200
         , matchHeaders = [matchContentTypeJson]
         }
+
+    -- "?table=not.is.null" does a "table IS DISTINCT FROM NULL" instead of a "table IS NOT NULL"
+    -- https://github.com/PostgREST/postgrest/issues/2800#issuecomment-1720315818
+    it "embeds verifying that the entire target table row is not null" $ do
+      get "/table_b?select=name,table_a(name)&table_a=not.is.null" `shouldRespondWith`
+        [json|[
+          {"name":"Test 1","table_a":{"name":"Not null 1"}},
+          {"name":"Test 2","table_a":{"name":null}}
+        ]|]
+        { matchStatus  = 200
+        , matchHeaders = [matchContentTypeJson]
+        }
+      get "/table_b?select=name,table_a()&table_a=is.null" `shouldRespondWith`
+        [json|[
+          {"name":"Test 3"}
+        ]|]
+        { matchStatus  = 200
+        , matchHeaders = [matchContentTypeJson]
+        }
+
+    it "works with count=exact" $ do
+      request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
+        [("Prefer", "count=exact")] ""
+       `shouldRespondWith`
+        [json|[
+          {"name":"Windows 7", "clients":{"name":"Microsoft"}},
+          {"name":"Windows 10", "clients":{"name":"Microsoft"}},
+          {"name":"IOS", "clients":{"name":"Apple"}},
+          {"name":"OSX", "clients":{"name":"Apple"}}
+        ]|]
+        { matchStatus  = 200
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-3/4" ]
+        }
+      request methodGet "/projects?select=name,clients()&clients=is.null"
+        [("Prefer", "count=exact")] ""
+       `shouldRespondWith`
+        [json|[{"name":"Orphan"}]|]
+        { matchStatus  = 200
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-0/1" ]
+        }
+      request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
+        [("Prefer", "count=exact")] ""
+       `shouldRespondWith`
+        [json|[
+          {"id":1,"name":"Walmart"},
+          {"id":2,"name":"Target"}
+        ]|]
+        { matchStatus  = 200
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-1/2" ]
+        }
+
+    it "works with count=planned" $ do
+      request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
+        [("Prefer", "count=planned")] ""
+       `shouldRespondWith`
+        [json|[
+          {"name":"Windows 7", "clients":{"name":"Microsoft"}},
+          {"name":"Windows 10", "clients":{"name":"Microsoft"}},
+          {"name":"IOS", "clients":{"name":"Apple"}},
+          {"name":"OSX", "clients":{"name":"Apple"}}
+        ]|]
+        { matchStatus  = 206
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-3/1200" ]
+        }
+      request methodGet "/projects?select=name,clients()&clients=is.null"
+        [("Prefer", "count=planned")] ""
+       `shouldRespondWith`
+        [json|[{"name":"Orphan"}]|]
+        { matchStatus  = 200
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-0/1" ]
+        }
+      request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
+        [("Prefer", "count=planned")] ""
+       `shouldRespondWith`
+        [json|[
+          {"id":1,"name":"Walmart"},
+          {"id":2,"name":"Target"}
+        ]|]
+        { matchStatus  = 206
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-1/952" ]
+        }
+
+    it "works with count=estimated" $ do
+      request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
+        [("Prefer", "count=estimated")] ""
+       `shouldRespondWith`
+        [json|[
+          {"name":"Windows 7", "clients":{"name":"Microsoft"}},
+          {"name":"Windows 10", "clients":{"name":"Microsoft"}},
+          {"name":"IOS", "clients":{"name":"Apple"}},
+          {"name":"OSX", "clients":{"name":"Apple"}}
+        ]|]
+        { matchStatus  = 206
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-3/1200" ]
+        }
+      request methodGet "/projects?select=name,clients()&clients=is.null"
+        [("Prefer", "count=estimated")] ""
+       `shouldRespondWith`
+        [json|[{"name":"Orphan"}]|]
+        { matchStatus  = 200
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-0/1" ]
+        }
+      request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
+        [("Prefer", "count=estimated")] ""
+       `shouldRespondWith`
+        [json|[
+          {"id":1,"name":"Walmart"},
+          {"id":2,"name":"Target"}
+        ]|]
+        { matchStatus  = 206
+        , matchHeaders = [ matchContentTypeJson
+                         , "Content-Range" <:> "0-1/952" ]
+        }

test/spec/Feature/Query/SingularSpec.hs

@@ -72,8 +72,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 4 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should not be updated, either
@@ -88,8 +87,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 4 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should not be updated, either
@@ -145,8 +143,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 2 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should not exist, either
@@ -161,8 +158,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 2 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should not exist, either
@@ -177,8 +173,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 2 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should not exist, either
@@ -226,8 +221,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 5 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should still exist
@@ -244,8 +238,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 5 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular ]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- the rows should still exist
@@ -318,8 +311,7 @@ spec =
           `shouldRespondWith`
             [json|{"details":"The result contains 2 rows","message":"JSON object requested, multiple (or no) rows returned","code":"PGRST116","hint":null}|]
             { matchStatus  = 406
-            , matchHeaders = [ matchContentTypeSingular
+            , matchHeaders = [ matchContentTypeSingular]
-                             , "Preference-Applied" <:> "tx=commit" ]
             }
 
         -- should rollback function

test/spec/Feature/RollbackSpec.hs

@@ -37,8 +37,8 @@ preferCommit   = [("Prefer", "return=representation"), ("Prefer", "tx=commit")]
 preferRollback = [("Prefer", "return=representation"), ("Prefer", "tx=rollback")]
 
 withoutPreferenceApplied      = []
-withPreferenceCommitApplied   = [ "Preference-Applied" <:> "tx=commit" ]
+withPreferenceCommitApplied   = [ matchHeaderValuePresent "Preference-Applied" "tx=commit" ]
-withPreferenceRollbackApplied = [ "Preference-Applied" <:> "tx=rollback" ]
+withPreferenceRollbackApplied = [ matchHeaderValuePresent "Preference-Applied" "tx=rollback" ]
 
 shouldRespondToReads reqHeaders respHeaders = do
   it "responds to GET" $ do

test/spec/SpecHelper.hs

@@ -46,6 +46,12 @@ matchCTArrayStrip = "Content-Type" <:> "application/vnd.pgrst.array+json;nulls=s
 matchCTSingularStrip :: MatchHeader
 matchCTSingularStrip = "Content-Type" <:> "application/vnd.pgrst.object+json;nulls=stripped; charset=utf-8"
 
+matchHeaderValuePresent :: HeaderName -> BS.ByteString -> MatchHeader
+matchHeaderValuePresent name val = MatchHeader $ \headers _ ->
+  case lookup name headers of
+    Just hdr -> if val `BS.isInfixOf` hdr then Nothing else Just $ "missing header value: " <> toS val <> "\n"
+    Nothing  -> Just $ "missing header: " <> toS (original name) <> "\n"
+
 matchHeaderAbsent :: HeaderName -> MatchHeader
 matchHeaderAbsent name = MatchHeader $ \headers _body ->
   case lookup name headers of

test/spec/fixtures/data.sql

@@ -853,3 +853,8 @@ INSERT INTO datarep_next_two_todos VALUES (2, 1, 3, 'do these first');
 TRUNCATE TABLE bitchar_with_length CASCADE;
 INSERT INTO bitchar_with_length(bit, char) VALUES ('00000', 'aaaaa');
 INSERT INTO bitchar_with_length(bit, char) VALUES ('11111', 'bbbbb');
+
+TRUNCATE TABLE table_a CASCADE;
+INSERT INTO table_a(id, name) VALUES (1, 'Not null 1'), (2, null), (3, 'Not null 2');
+TRUNCATE TABLE table_b CASCADE;
+INSERT INTO table_b(table_a_id, name) VALUES (1, 'Test 1'), (2, 'Test 2'), (null, 'Test 3');

test/spec/fixtures/schema.sql

@@ -3368,3 +3368,106 @@ $$ language sql;
 create function returns_setof_record_params(id int, name text) returns setof record as $$
 select * from projects p where p.id >= $1 and p.name like $2;
 $$ language sql;
+
+create function raise_sqlstate_test1() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC","details":"DEF","hint":"XYZ"}',
+      detail = '{"status":332,"status_text":"My Custom Status","headers":{"X-Header":"str"}}';
+end
+$$;
+
+create function raise_sqlstate_test2() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC"}',
+      detail = '{"status":332,"headers":{"X-Header":"str"}}';
+end
+$$;
+
+create function raise_sqlstate_test3() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC"}',
+      detail = '{"status":404,"headers":{"X-Header":"str"}}';
+end
+$$;
+
+create function raise_sqlstate_test4() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC"}',
+      detail = '{"status":404,"status_text":"My Not Found","headers":{"X-Header":"str"}}';
+end
+$$;
+
+create function raise_sqlstate_invalid_json_message() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = 'INVALID',
+      detail = '{"status":332,"headers":{"X-Header":"str"}}';
+end
+$$;
+
+create function raise_sqlstate_invalid_json_details() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC","details":"DEF"}',
+      detail = 'INVALID';
+end
+$$;
+
+create function raise_sqlstate_missing_details() returns void
+  language plpgsql
+  as $$
+begin
+    raise sqlstate 'PGRST' USING
+      message = '{"code":"123","message":"ABC","details":"DEF"}';
+end
+$$;
+
+create table table_a (
+  id int primary key,
+  name text
+);
+
+create table table_b (
+  table_a_id int references table_a(id),
+  name text
+);
+
+create or replace function test.returns_complex()
+returns table(id int, val complex) as $$
+  select 1, row(0.1, 0.5)::complex as val
+  union
+  select 2, row(0.2, 0.6)::complex as val
+  union
+  select 3, row(0.3, 0.7)::complex as val;
+$$ language sql;
+
+create function computed_rel_overload(items) returns setof items2 as $$
+  select * from items2 limit 1
+$$ language sql;
+
+create function computed_rel_overload(items2) returns setof items2 as $$
+  select * from items2 limit 2
+$$ language sql;
+
+create function search2(id bigint) returns setof items2
+language plpgsql
+stable
+as $$ begin
+  return query select items2.id from items2 where items2.id=search2.id;
+end$$;