Re-Request mapping of internal variables for stored procedures

rdlrt commented 7 months ago

Environment

PostgreSQL version: 16.2
PostgREST version: 12.0.2
Operating system: N.A.

Description of issue

This is a follow-up from #1124 , to request a better routing for limit/offset for stored procedures. Noting that limit/offset works perfectly fine for SQL Functions with inlining capability, this request is more to extend it's benefits to more complicated scenarios where we need to rely on PLPGSQL.

The recommended workaround in the linked issue was using a reverse proxy to convert limit/offset params into headers, and use them in the underlying query.

However, I am not able to see a fool-proof way to use this across use cases. Specifically, when using the above:

PostgREST will have it's instance limit (derived from config) applied on the RPC call (which may be inlined), eg: 1000.
If we want to make use of this URL parameter in the query itself, we limit the internal queries to 1000 records.
Now, if the URL parameter specifies offset=5000, since the records on the internal query is restricted to 1000, the returned response would be {"code":"PGRST103","details":"An offset of 5000 was requested, but there are only 1000 rows.","hint":null,"message":"Requested range not satisfiable"}.

Alternatives considered

Bumping postgrest config for max limit of records, but this could go in millions and can easily DDoS instances.
Keyset pagination - but is not available in a consumer (of endpoints) friendly way

Feature Request / Ideas Request

It would be nice if we can build on the workaround suggested to be able to somehow indicate postgrest to consider a specific header as inline-limit/offset - to indicate to PostgREST to bypass it's native offset count (or adapt based on provided limit/offset) preventing returning error as above. I suppose I may not be thinking this through (or you may have already discussed this), if so - any ideas/workarounds around this would be appreciated.

Example

Adding LIMIT/OFFSET to function below that can already inline may not make sense, but it is the simplest case that helps demonstrate the issue

A sample (simplified for the issue description using language SQL, the actual case involves a free service with ~61 endpoints of which ~50 are pgplsql-based while others are LANGUAGE SQL based, allowing native postgrest filtering) SQL - a simple table with more than 1000 records:

CREATE OR REPLACE FUNCTION grest.es()
RETURNS TABLE(
  no word31type
)
LANGUAGE sql STABLE
AS $$
  SELECT
    no
  FROM epoch
  ORDER BY no DESC
  LIMIT COALESCE(current_setting('request.header.limit', true)::int, 1000)
  OFFSET COALESCE(current_setting('request.header.offset', true)::int, 0);
$$;

It is quite possible that maybe I misunderstood the solution on linked issue, but based on above, there is an indirect subquery which is limiting the results - that will prevent/impact counts for offset.

Successful case (limit + offset fits within the postgrest instance limit):

curl "http://127.0.0.1:8053/rpc/es?limit=10&offset=500" -H "Prefer: count=exact" -I
HTTP/1.1 206 Partial Content
...
content-range: 500-509/1000
preference-applied: count=exact
...

Problematic case (offset goes beyond postgrest instance limit):

curl "http://127.0.0.1:8053/rpc/es?limit=10&offset=2000" -H "Prefer: count=exact" -I
HTTP/1.1 416 Requested Range Not Satisfiable
...
content-range: */1000
...

wolfgangwalther commented 7 months ago

I don't think the proposed solution will work for OFFSET, because offset will be applied twice:

Once inside your function
Once outside the function by PostgREST directly.

This is not a problem for LIMIT, but with OFFSET it is.

The simplest solution I can think of is to just add my_limit and my_offset arguments to your RPC and then change your query respectively. Those will then only be applied inside your function.

rdlrt commented 7 months ago

Thanks for your reply, but since we need to kinda have the limit/offset (compatible with native postgrest filters as they're exposed as is), the name of URL params themselves need to align.

But thinking for solution on similar lines, I am thinking maybe an extension of that workaround could be to strip off those parameters from URL itself (at reverse proxy before passing to postgrest) and convert limit/offset params to be handled internally - as this can be combed with ACLs to apply on specific endpoints where we do need to optimize the default pagination, accordingly - closing this.

PostgREST / postgrest