maybe_single throws an error when 0 rows are returned

[levity]

Bug report

Describe the bug

maybe_single is supposed to return None when the query returns no rows. But now it throws an APIError:

postgrest.exceptions.APIError: {'message': 'Missing response', 'code': '204', 'hint': 'Please check traceback of the code', 'details': "Postgrest couldn't retrieve response, please check traceback of the code. Please create an issue in `supabase-community/postgrest-py` if needed."}

To Reproduce

Run any query that returns 0 rows, e.g:

postgrestClientInstance.from_('my_table').select('*').eq('content', 'nonexistent').maybe_single().execute()

Expected behavior

should return APIResponse(data=None) as in previous versions

System information

• supabase 2.3.0
• postgrest 0.13.0
• PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

[levity]

perhaps supabase/postgrest-py#289 is a clue

[tushar-balwani]

@levity Thanks for reporting this issue. again there is change of error message in postgrest. as @J0 suggested in supabase/postgrest-py#289 we need a robust solution for repeating problem. I am happy to apply this temp fix again for now and also open for suggestions to how we can make it dynamic.

[silentworks]

This was a breaking change that was made and switching this back would be yet another breaking change. I'm not so sure if this should be reverted. It should be also noted that the current behavior differs to that of the JavaScript library as the JS library follows along the original implementation which returns an ApiResponse with data=None.

[YusufCelik]

The Docker version of Supabase that I was running did not have this issue. It wasn't until I tried to push to production that I noticed this error. Anyways, for now I fixed it with a try catch:

        db_response = supabase.table(table_name).select(
            "*").match(data_obj).maybe_single().execute()
    except postgrest.exceptions.APIError:
        db_response = None

[ex0ns]

I am facing the same issue and I think that the issue is coming from this part:

class SyncMaybeSingleRequestBuilder(SyncSingleRequestBuilder[_ReturnT]):
    def execute(self) -> Optional[SingleAPIResponse[_ReturnT]]:
        r = None
        try:
            r = SyncSingleRequestBuilder[_ReturnT].execute(self)
        except APIError as e:
            if e.details and "The result contains 0 rows" in e.details:
                return None
        if not r:
            raise APIError(
                {
                    "message": "Missing response",
                    "code": "204",
                    "hint": "Please check traceback of the code",
                    "details": "Postgrest couldn't retrieve response, please check traceback of the code. Please create an issue in `supabase-community/postgrest-py` if needed.",
                }
            )
        return r

After placing a breakpoint the e.details string is 'Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row' which does not contains The result contains 0 rows.

I am using postgrest v0.19.1 and supabase v2.11.0.

Should we update the matching string to a lower case comparison on contains 0 rows only ?

[silentworks]

There won't be any change to this anytime soon as it will be a breaking change and until we are ready to start working on version 3.0 of the supabase library this won't likely change. I've mentioned above that this was a breaking change when it was changed and it will be one again if we revert it, now the library has more users than before and it would create more issues than its current behavior. I've left the issue open as it's a reminder to fix for version 3.0 of the supabase library.

[Glinte]

This is so annoying I just forked this repo and applied the change that ex0ns suggested