---
title: "Collecting large tables safely"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Collecting large tables safely}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>", eval = FALSE)
```

```{r setup}
library(transferegovr)
```

## The cap you cannot see

The service returns at most **1000 rows per request**, however many are asked
for, and it does so without complaint: ask for a hundred thousand and you are
given a thousand and a `206 Partial Content`. Nothing in the body says the
result was truncated. Only the `Content-Range` header does.

This package reads that header, which is why `.limit` counts rows rather than
requests, and why anything above 1000 is collected page by page:

```{r}
plans <- tg_get("ted", "plano_acao", .limit = 2500)

nrow(plans)
#> [1] 2500
tg_metadata(plans)$pages
#> [1] 3
```

`.page_size` is capped at 1000 for the same reason. A larger value would be
accepted by the package, silently truncated by the service, and the shortfall
would look like missing data.

## Ask how big it is first

```{r}
tg_count("fundoafundo", "gestao_financeira_lancamentos")
#> [1] 1115444
```

To size everything at once, `tg_tables()` takes a `counts` argument. It is the
only part of that function that touches the network — one request per table,
cached, so the second call in a session is free:

```{r}
tg_tables(counts = TRUE) |>
  dplyr::arrange(desc(rows))
#> # A tibble: 48 × 6
#>   module                  table                           columns    rows …
#>   <chr>                   <chr>                             <int>   <dbl>
#> 1 fundoafundo             gestao_financeira_lancamentos        32 1115444
#> 2 fundoafundo             gestao_financeira_subtransacoes      16  377666
#> 3 transferenciasespeciais historico_pagamento_especial          5  281163
#> 4 fundoafundo             plano_acao_historico                  5  183379
#> 5 transferenciasespeciais meta_especial                        16  156016
#> # ℹ 43 more rows
```

A million rows is over a thousand requests. The package throttles itself to
sixty requests a minute by default, so that download takes something like twenty
minutes. That is usually the wrong shape for the question being asked.

## Narrow first, then collect

Two things make a large query smaller, and both happen on the server:

**Select only the columns you need.** `gestao_financeira_lancamentos` has 32
columns; if you want four of them, the other 28 need never cross the network.

```{r}
tg_get(
  "fundoafundo", "gestao_financeira_lancamentos",
  .select = c(
    "id_lancamento_gestao_financeira",
    "id_plano_acao",
    "data_lancamento_gestao_financeira",
    "valor_lancamento_gestao_financeira"
  ),
  .limit = Inf
)
```

**Filter before you fetch.** A year's worth of a table is usually what was
wanted anyway:

```{r}
tg_count(
  "fundoafundo", "gestao_financeira_lancamentos",
  data_lancamento_gestao_financeira = list(gte("2025-01-01"), lt("2026-01-01"))
)
```

## Why the order matters

Offset pagination asks for rows 0–999, then 1000–1999, and so on. In Postgres a
query without `ORDER BY` has **no defined row order**: the planner is free to
return rows differently between two executions, and if it does, page two can
repeat rows from page one and skip others entirely. The result has the right
number of rows and the wrong contents.

So every request this package makes carries an explicit order. By default it is
the table's primary key where the API declares one, and its identifier columns
otherwise:

```{r}
tg_metadata(plans)$order
#> [1] "id_plano_acao.asc" "id_programa.asc"   "sq_instrumento.asc"
```

You can supply your own, and pagination will use it:

```{r}
tg_get("ted", "plano_acao", .order = "vl_total_plano_acao.desc", .limit = 2500)
```

Bear in mind that ordering by a column with many ties leaves the order within
each tie undefined, which brings the problem back. Prefer an identifier, or add
one as a tiebreaker:

```{r}
tg_get(
  "ted", "plano_acao",
  .order = c("vl_total_plano_acao.desc", "id_plano_acao.asc"),
  .limit = 2500
)
```

## Resuming, and taking it in pieces

`.offset` skips rows that have already been collected, so a long download can be
taken in sittings:

```{r}
first <- tg_get("ted", "plano_acao_etapa", .limit = 20000)
rest <- tg_get("ted", "plano_acao_etapa", .limit = Inf, .offset = 20000)
```

Both parts must use the same order, or they are pages of two different
sequences. The default order is deterministic for a given table, so leaving
`.order` alone is the safe choice here.

## When the count and the contents disagree

The number of rows collected is checked against the total the API reported:

```
Warning: Collected 4820 rows where the API reported 4900.
i The table may have changed while it was being read.
i Check `tg_metadata()` on the result.
```

This is a real possibility on a long download, since the underlying tables are
refreshed daily. It is a warning rather than an error because a nearly complete
result is still worth having — but it must be visible, and `tg_metadata()`
records both numbers so the gap stays in the data rather than only in the
console.

## Caching

Every request is cached for an hour, so re-running an analysis does not re-fetch
what it already has. Each page is cached separately, so an interrupted
collection resumes from the network only where it has to.

```{r}
tg_cache_dir()
#> [1] "/tmp/RtmpXXXX/transferegovr-cache"
```

The default is the session's temporary directory, so nothing outlives the
session unless you ask for it:

```{r}
tg_cache_dir(tools::R_user_dir("transferegovr", "cache"))
```

Set `TRANSFEREGOVR_CACHE_DIR` in `.Renviron` to make that permanent, and adjust
the lifetime with `options(transferegovr.cache_ttl = 86400)` — the data behind
these APIs is refreshed once a day, so an hour is conservative.

`tg_metadata(x)$cached` tells you whether a result came from the network or from
disk.

## Rate limiting and retries

Requests are throttled to sixty a minute and retried up to four times on a 429
or a 5xx, with exponential backoff and jitter. A 400 is not retried: it means
the service rejected the query itself and will reject it identically next time.

Both are adjustable, but the defaults exist to keep the package a considerate
client of a public service:

```{r}
options(
  transferegovr.requests_per_minute = 30,
  transferegovr.max_tries = 6,
  transferegovr.timeout = 120
)
```