Stripe wrapper only allows fetching first 100 objects

[thomtrp]

Describe the bug

When performing pg_graphql queries on a foreign table imported by the stripe_fdw, I cannot fetch more than the first 100 items. Checking the foreign table, I can see I have more items to fetch.

Using raw sql query works well. Trying with the postgres_fdw works also well.

Only stripe_fdw combinated with pg_graphql seems to have this limit.

To Reproduce

Steps to reproduce the behavior:

1. Using stripe_fdw, create a foreign table based on stripe API. For my example, I will use the object customers.
2. Perform a query and get the last cursor. I got 60 items with that first query.

query {
  customerCollection {
    edges {
      node {
        __typename
        id: id
      }
      cursor
      __typename
    }
    pageInfo {
      startCursor
      endCursor
      __typename
    }
    __typename
  }
}

3. Perform a second query adding after: <last_cursor> in customerCollection query params. I got only 40 items this time.
4. Perform a third query using the new last_cursor. I got an empty answer while I have 165 items in my table.
   {"data": {"customerCollection": {"edges": [], "pageInfo": {"endCursor": null, "__typename": "PageInfo", "startCursor": null}, "__typename": "customerConnection"}}}

Expected behavior

I should be able to fetch my 165 customers and not only the first 100.

Additional context

I already opened an issue related with stripe_fdw and pg_graphql limitations for stripe foreign table relations.

[olirice]

pg_graphql doesn't do any unique handling of foreign data wrappers. It treats them identically to tables so its unlikely that we'll be able to resolve your issue in this repo.

Its possibly you're hitting something like this supabase/wrappers#113
from the stripe docs it looks like you might be getting exactly 1 page of data back with the maximum 100 elements

please also note that putting a foreign data wrapper directly on your API's search path is not recommended

[olirice]

In case it helps create a reproducible example, the underlying SQL that your query gets compiled to would be roughly

(
                with __records as (
                    select
                       *
                    from
                       <your fdw>
                    where
                        true
                    order by
                        <value>
                    limit
                        <value>
                    offset
                        <value>
                ),
                __total_count(___total_count) as (
                    select
                        count(*)
                    from
                        <your fdw>
                    where
                        true
                ),
                select
                    jsonb_build_object('id', id)
                from
                    __records foo,
                    __total_count
            )

[olirice]

closing as not relevant to this repo
see supabase/wrappers#113 for this issue