Bump to v11.2.1

fix: regression that rejects URI connection strings with certain unescaped characters in the password
fix: RPCs not embedding correctly when using overloaded functions for computed relationships
2023-10-04 00:07:09 -03:00 · 2023-10-04 00:07:09 -03:00 · 2023-10-04 00:07:09 -03:00 · 2023-10-04 00:07:09 -03:00 · 2023-10-04 00:07:09 -03:00 · 2023-10-04 00:07:09 -03:00
38 changed files with 480 additions and 205 deletions
@@ -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
@@ -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:
@@ -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:
@@ -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
@@ -3,7 +3,22 @@
 All notable changes to this project will be documented in this file.
 This project adheres to [Semantic Versioning](http://semver.org/).

-## Unreleased
+## [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

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

@@ -4,16 +4,16 @@ self: super:
 {
  ## Example for including a postgresql version from a specific nixpks commit:
  ##
-  #  postgresql_14 =
-  #    let
-  #      rev = "76b1e16c6659ccef7187ca69b287525fea133244";
-  #      tarballHash = "1vsahpcx80k2bgslspb0sa6j4bmhdx77sw6la455drqcrqhdqj6a";
-  #
-  #      pinnedPkgs =
-  #        builtins.fetchTarball {
-  #          url = "https://github.com/nixos/nixpkgs/archive/${rev}.tar.gz";
-  #          sha256 = tarballHash;
-  #        };
-  #    in
-  #    (import pinnedPkgs { }).pkgs.postgresql_14;
+  postgresql_16 =
+    let
+      rev = "5148520bfab61f99fd25fb9ff7bfbb50dad3c9db";
+      tarballHash = "1dfjmz65h8z4lk845724vypzmf3dbgsdndjpj8ydlhx6c7rpcq3p";
+
+      pinnedPkgs =
+        builtins.fetchTarball {
+          url = "https://github.com/nixos/nixpkgs/archive/${rev}.tar.gz";
+          sha256 = tarballHash;
+        };
+    in
+    (import pinnedPkgs { }).pkgs.postgresql_16;
 }
@@ -1,5 +1,5 @@
 name:               postgrest
-version:            11.2.0
+version:            11.2.1
 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
@@ -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
@@ -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 headers =
+fromHeaders :: Bool -> [HTTP.Header] -> Preferences
+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
@@ -150,8 +150,7 @@ 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
+  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
 runDbHandler appState isoLvl mode authenticated prepared handler = do
@@ -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
@@ -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
@@ -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
@@ -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) =
-  fromMaybe (unknownField fn jp) $ HM.lookup qi tables >>=
-    Just . flip resolveTableField field
+resolveTypeOrUnknown ResolverContext{..} (fn, jp) =
+  case res 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, 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
@@ -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)] ->
@@ -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,8 +242,10 @@ 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) <> ")"
-    _                                               -> fromQi mainQi) <>
+    -- 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
    Just Relationship{relCardinality=M2M Junction{junTable=jt}} -> ", " <> fromQi jt
@@ -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
@@ -104,9 +104,9 @@ rangeStatusHeader topLevelRange queryTotal tableTotal =
    rangeStatus :: Integer -> Integer -> Maybe Integer -> Status
    rangeStatus _ _ Nothing = status200
    rangeStatus lower upper (Just total)
-      | lower > total               = status416 -- 416 Range Not Satisfiable
-      | (1 + upper - lower) < total = status206 -- 206 Partial Content
-      | otherwise                   = status200 -- 200 OK
+      | lower >= total && lower /= upper = status416 -- 416 Range Not Satisfiable
+      | (1 + upper - lower) < total      = status206 -- 206 Partial Content
+      | otherwise                        = status200 -- 200 OK

 contentRangeH :: (Integral a, Show a) => a -> a -> Maybe a -> Header
 contentRangeH lower upper total =
@@ -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,
-                                          toAppliedHeader)
+                                          prefAppliedHeader,
+                                          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
-              Nothing
-            else
-              toAppliedHeader <$> preferResolution
-          , toAppliedHeader <$> preferMissing
+          , prefHeader
          ]

    case preferRepresentation of
-      Just Full -> response HTTP.status201 (addPrefToHeaders headers Full ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
-      Just None -> response HTTP.status201 (addPrefToHeaders headers None) mempty
-      Just HeadersOnly -> response HTTP.status201 (addPrefToHeaders headers HeadersOnly) mempty
+      Just Full -> response HTTP.status201 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
+      Just None -> response HTTP.status201 headers 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)
-          (LBS.fromStrict rsBody)
-        Just None -> response HTTP.status204 (addPrefToHeaders headers None) mempty
+        Just Full -> response HTTP.status200 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
+        Just None -> response HTTP.status204 headers 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 None -> response HTTP.status204 [toAppliedHeader None] mempty
-      _ -> response HTTP.status204 [] mempty
+      Just Full -> response HTTP.status200 (contentTypeHeaders ctxApiRequest ++ prefHeader) (LBS.fromStrict rsBody)
+      Just None -> response HTTP.status204 prefHeader 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)
-          (LBS.fromStrict rsBody)
-        Just None -> response HTTP.status204 (addPrefToHeaders headers None) mempty
+        Just Full -> response HTTP.status200 (headers ++ contentTypeHeaders ctxApiRequest) (LBS.fromStrict rsBody)
+        Just None -> response HTTP.status204 headers 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
@@ -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

@@ -31,6 +31,7 @@ data Relationship = Relationship
  { relFunction     :: QualifiedIdentifier
  , relTable        :: QualifiedIdentifier
  , relForeignTable :: QualifiedIdentifier
+  , relTableAlias   :: QualifiedIdentifier
  , relToOne        :: Bool
  , relIsSelf       :: Bool
  }
@@ -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
@@ -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:
@@ -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"

@@ -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] }
@@ -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
@@ -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" $
@@ -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] }
@@ -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]
@@ -453,3 +453,17 @@ spec = do
            { matchStatus  = 416
            , matchHeaders = ["Content-Range" <:> "*/15"]
            }
+
+        it "refuses a range with first position the same as number of items" $
+          request methodGet "/rpc/getitemrange?min=1&max=2"
+                  (rangeHdrsWithCount $ ByteRangeFromTo 1 2) mempty
+            `shouldRespondWith`
+              [json| {
+                "message":"Requested range not satisfiable",
+                "code":"PGRST103",
+                "details":"An offset of 1 was requested, but there are only 1 rows.",
+                "hint":null
+              }|]
+            { matchStatus  = 416
+            , matchHeaders = ["Content-Range" <:> "*/1"]
+            }
@@ -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" ]
+        }
@@ -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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular ]
            }

        -- 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
-                             , "Preference-Applied" <:> "tx=commit" ]
+            , matchHeaders = [ matchContentTypeSingular]
            }

        -- should rollback function
@@ -37,8 +37,8 @@ preferCommit   = [("Prefer", "return=representation"), ("Prefer", "tx=commit")]
 preferRollback = [("Prefer", "return=representation"), ("Prefer", "tx=rollback")]

 withoutPreferenceApplied      = []
-withPreferenceCommitApplied   = [ "Preference-Applied" <:> "tx=commit" ]
-withPreferenceRollbackApplied = [ "Preference-Applied" <:> "tx=rollback" ]
+withPreferenceCommitApplied   = [ matchHeaderValuePresent "Preference-Applied" "tx=commit" ]
+withPreferenceRollbackApplied = [ matchHeaderValuePresent "Preference-Applied" "tx=rollback" ]

 shouldRespondToReads reqHeaders respHeaders = do
  it "responds to GET" $ do
@@ -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
@@ -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');
@@ -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$$;
Author	SHA1	Message	Date
Laurence IslaandSteve Chavez	d07b5acf15	Bump to v11.2.1	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	64d02d7375	fix: regression that rejects URI connection strings with certain unescaped characters in the password	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	5c822b7ec4	fix: RPCs not embedding correctly when using overloaded functions for computed relationships	2023-10-04 00:07:09 -03:00
steve-chavez	de97f646a4	ci: fix MacOS CI	2023-10-04 00:07:09 -03:00
steve-chavez	a45c639a97	fix: arrow filter on RPC returning TABLE+composite	2023-10-04 00:07:09 -03:00
steve-chavez	1e711051da	fix: unnecessary set default_transaction_isolation	2023-10-04 00:07:09 -03:00
steve-chavez	79077c873f	test: adjust memory test	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	2ed163945b	Add test option for PostgreSQL 16	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	cf3cd4b8d0	Add missing changelog entries	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	38ea4378da	fix: error when requesting "Prefer: count=<type>" with null filters on embedded resources	2023-10-04 00:07:09 -03:00
Laurence IslaandSteve Chavez	226f1caa24	fix: bug when Null Filtering on embedded resources When doing Null Filtering, the to-one embed resources were not included if they had a NULL value in any of the selected fields.	2023-10-04 00:07:09 -03:00
steve-chavez	6475f254f7	fix: inconsistent Preference-Applied * Don't apply `tx=commit` if the transaction doesn't commit * Apply `count=exact` * Also simplifies the Preference-Applied logic, removing the need for some functions.	2023-10-04 00:07:09 -03:00
dependabot[bot]andSteve Chavez	d6cd5d0fb4	build(deps): bump cachix/install-nix-action (#2935 )	2023-10-04 00:07:09 -03:00
dependabot[bot]andSteve Chavez	c73282c3f0	build(deps): bump actions/checkout from 3 to 4 (#2936 )	2023-10-04 00:07:09 -03:00
Taimoor ZaeemandSteve Chavez	d7dfdaa03f	fix: range request with first position same as length return status 206	2023-10-04 00:07:09 -03:00
Taimoor ZaeemandSteve Chavez	6c8ce3929c	fix: duplicate headers in response	2023-10-04 00:07:09 -03:00
Diogo BiazusandSteve Chavez	f84bc6a0ff	fix: schema cache and configuration reloading with NOTIFY not working on Windows	2023-10-04 00:07:09 -03:00
Taimoor ZaeemandSteve Chavez	82ecf836c4	fix: application/vnd.pgrst.array not accepted as a valid mediatype	2023-10-04 00:07:09 -03:00