Paging without losing rows

Three different ways a response gets cut, and only one of them tells you.

Overview

Two paging mechanisms and one silent ceiling, and they do not interchange. Two of the three failure modes look exactly like a series that simply ended.

The trap: a Range header on /rest/v1/rpc/<function> does not page. It returns the first page again, with a 200. Loop on it and you collect the same 1,000 rows forever, each batch looking like valid data.

PostgREST is configured with db-max-rows = 1000, server-wide: every response, every endpoint, every tier. Signing in does not change it. What differs is what each endpoint does when your request exceeds it.

Endpoint kind	Above 1000 rows	How you find out
GET views	pages normally	Content-Range header
RPC with p_after	refuses — SQLSTATE 22023	a 400 naming the function
RPC without p_after	refuses — SQLSTATE 22023	a 400 telling you to narrow
RPC with a tier ceiling	clamps, silently	nothing tells you

1. GET views — limit, offset, Range

curl -s "$SILO_URL/short_interest?ticker=eq.VALE3&order=trade_date.desc&limit=1000&offset=0" \
  -H "apikey: $SILO_ANON_KEY" \
  -H "Prefer: count=exact" -D -

Always send order. Without it, limit/offset walk an undefined sequence and pages can overlap or skip rows — a correctness bug, not a tidiness one.

Content-Range: 0-999/*        ← truncated; there is more
Content-Range: 0-412/413      ← complete (with Prefer: count=exact)

0-999/* is the tell. Prefer: count=exact turns the * into the real total, at the cost of a count query.

2. RPCs that page — the p_after cursor

Three functions take a cursor, because walking the whole series is the normal thing to want:

Function	Cursor value	Order
panel	'<date>|<id>|<metric>|<asset_class>' from the last row	date, id, metric, asset_class
quote_history	the last row’s trade_date as YYYY-MM-DD	trade_date
fund_nav	the last row’s period as YYYY-MM-DD	period, entity_type

1. First page

   Send p_after: "" — the empty string, not null.

2. Next page

   Copy the key out of the last row you received and send it as p_after.

3. Stop

   A page shorter than 1000 rows is the last one.

p_after: null (or omitting it) is whole-result mode: everything, unless that would exceed 1,000 rows, in which case the call refuses with 22023.

rows, cursor = [], ""
while True:
    page = post("/rpc/quote_history", {
        "p_ticker": "PETR4", "p_from": "2019-01-02", "p_after": cursor,
    })
    rows += page
    if len(page) < 1000:
        break
    cursor = page[-1]["trade_date"]     # the key, from the LAST row

Note: fund_nav paging requires p_entity_type. Its cursor is a bare period, unique only within one family, and 385 CNPJs file under both fi and fidc in the same month. Page without it and you get a 22023, not a wrong answer. Whole-result mode does not need it — every row is labelled with its family.

3. RPCs that refuse and do not page

Every other set-returning function raises 22023 above one page and offers no cursor, on purpose — a window that wide is a mistake rather than a walk. An option series lives months; a financial statement has tens of rows.

option_history · termo_history · financials · company_financials · income_statements · balance_sheets · cash_flow_statements · anbima_classes · inflation · inflation_items · fidc_cedentes · fidc_sacados · fidc_portfolio · fidc_tranches · fidc_aging · fund_holdings · fund_debentures · fund_documents · fund_restatements · the seven screen_*

The authoritative list is catalog().limits.page.functions.raise_only. Narrow p_from/p_to, the ids, or the metrics. The error says why it refused and how to fix it for that function — in the message, and again as PostgREST’s details and hint. fidc_cedentes, fidc_sacados, fidc_portfolio, fund_holdings and fund_debentures also take p_limit (1..1000) as an explicit request for the newest N rows; until catalog v34 (the FIDC three) and v41 (the holdings pair) they trimmed silently at 500 / 5,000 instead.

4. The silent one — tier ceilings

Three functions clamp: a p_limit above the ceiling is quietly lowered to it. No error, no header, no signal.

Function	Anonymous	Signed in
search_funds	25	200
option_chain	200	2 000
option_exercises	500	5 000

The only defence is arithmetic: a result of exactly 500 rows is probably truncated. If the count equals your tier’s ceiling, assume there is more and narrow the window.

panel has a ceiling on a different axis — at most 3 ids anonymously, 50 signed in — but it refuses with 22023 rather than trimming. Universe mode is signed-in only.

Deciding in one pass

Is my result complete?

GET view?
  └─ read Content-Range. "0-999/*" → page with limit/offset (and order!).

RPC that took p_after?
  └─ page shorter than 1000 → done. Exactly 1000 → send the cursor again.

RPC that returned 400 / 22023?
  └─ not an outage. Narrow the window, or page if it offers a cursor.

RPC with a tier ceiling?
  └─ count == your ceiling → assume truncated. Narrow, or sign in.

Raising the ceilings

Signing in (GitHub) returns a user JWT. Send it as Authorization: Bearer <jwt> beside apikey, never instead of it — the publishable key is not a JWT.

Signing in raises ceilings and the statement timeout (3s → 8s). It never widens the set of objects you can reach: schema api is the entire public surface for every tier.

Next

Your first screen

Four calls to something real on screen.

Conventions & limits

Headers, error codes and the full limits table.