---
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)
```

These tables are large and the page size is small. This vignette is about
getting all of a table without losing rows to pagination, and about knowing what
a download will cost before starting it.

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

## Measure first

The seventy-four tables hold about 7.5 million rows between them, spread very
unevenly — from none at all in `especiais/orgaos_analises_pendentes_especiais`
to 1,362,980 in `parcerias/extrato_bancario`.

```{r}
sizes <- tg_tables(counts = TRUE)
sizes[order(-sizes$rows), c("module", "table", "columns", "rows")]
#> # A tibble: 74 × 4
#>    module      table                                   columns    rows
#>    <chr>       <chr>                                     <int>   <dbl>
#>  1 parcerias   extrato_bancario                             13 1362980
#>  2 fundoafundo gestao_financeira_lancamentos                28 1160094
#>  3 especiais   gestao_financeira_lancamentos_especiais      34  735012
#>  4 especiais   planos_trabalho_historico                     5  461164
#>  5 parcerias   item_proposta                                14  428078
#>  …
```

That call makes seventy-four requests, and caches them. For a single table:

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

`tg_count()` takes the same filters as `tg_get()`, so you can size the thing you
actually want rather than the whole table:

```{r}
tg_count("parcerias", "proposta")
#> [1] 89415
tg_count("parcerias", "proposta", sg_uf_recebedor = "PE")
#> [1] 3258
```

## What a page costs

Each module caps a page at its own size: **200 rows** in `especiais` and
`parcerias`, **1000** in `fundoafundo` and `ted`. `tg_modules()` reports it, and
`.page_size` defaults to it. Unlike some APIs, these do not silently truncate a
larger request — one row over the cap is a `422`, and the package refuses it
before sending:

```{r}
tg_get("parcerias", "proposta", .page_size = 201)
#> Error in `tg_get()`:
#> ! `.page_size` must be a whole number between 1 and 200.
```

So the arithmetic is simple and worth doing, and the cap matters as much as the
size. The largest table, `parcerias/extrato_bancario`, is
`ceiling(1362980 / 200)` = **6,815 requests** — at the default throttle of sixty
a minute, nearly two hours. The second largest is barely smaller but lives in a
module that serves pages of 1000, so it takes 1,161 requests, about twenty
minutes.

```{r}
rows <- tg_count("parcerias", "extrato_bancario")
ceiling(rows / 200)
#> [1] 6815
```

If you genuinely need a table that size, consider whether a filter narrows it
first, and raise the throttle deliberately rather than by accident:

```{r}
options(transferegovr.requests_per_minute = 120)
```

## Limits and offsets count rows

`.limit` and `.offset` are in rows, not pages, whatever `.page_size` is set to.

```{r}
tg_get("especiais", "meta_especiais", .limit = 450)
```

That is three requests: 200, 200, 50 — the last page is trimmed to the limit.
An offset that falls inside a page is handled by fetching the page it lands in
and dropping the rows before it:

```{r}
tg_get("especiais", "meta_especiais", .limit = 100, .offset = 137,
       .page_size = 60)
```

`Inf` collects everything that matches:

```{r}
programas <- tg_get("especiais", "programas_especiais", .limit = Inf)
```

## Checking what you got

Every result carries the pagination state the API reported:

```{r}
metas <- tg_get("especiais", "meta_especiais", .limit = 450)

tg_metadata(metas)
#> $module
#> [1] "especiais"
#> $table
#> [1] "meta_especiais"
#> $total_rows
#> [1] 156193
#> $rows_returned
#> [1] 450
#> $pages
#> [1] 3
#> …
```

`total_rows` is how many rows matched, `rows_returned` how many you have. If
collection ends short of what the API said it would return, that is a warning
rather than a silent truncation:

```
Warning: Collected 448 rows where the API reported 450.
ℹ The table may have changed while it was being read.
```

## Why the row order is safe to rely on

These APIs publish no ordering parameter. Page two is simply "page two", and
whether that is a well-defined thing depends on the server keeping a stable
order between requests — which nothing in the documentation promises.

Postgres makes no such promise in general: a query without `ORDER BY` may return
rows in a different order between executions, and under offset pagination that
means page two can repeat rows from page one and skip others entirely. A row
count would not reveal it. Two pages of 200 that overlap by 40 rows still add up
to 400.

So it was tested rather than assumed. The check is to fetch the same rows at two
different page sizes and compare them as sequences:

```{r}
strip <- function(x) {
  x <- as.data.frame(x)
  attr(x, "transferegovr_metadata") <- NULL
  rownames(x) <- NULL
  x
}

big <- tg_get("especiais", "meta_especiais", .limit = 450, .page_size = 200)
small <- tg_get("especiais", "meta_especiais", .limit = 450, .page_size = 50)

identical(strip(big), strip(small))
#> [1] TRUE
```

Three requests and nine requests, cutting the same 450 rows at different
boundaries, produce the same rows in the same order. That is what rules out both
overlap and skipping.

The same comparison holds 100,000 rows deep, across repeated calls, on tables
with no natural key, and on tables with nested columns. `test-live.R` re-runs all
of it against the real services, so a change upstream shows up as a failing test
rather than as quietly wrong data.

## Caching

Responses are cached for an hour, so re-running a collection during a session
costs nothing:

```{r}
first <- tg_get("especiais", "meta_especiais", .limit = 450)
again <- tg_get("especiais", "meta_especiais", .limit = 450)

tg_metadata(again)$cached
#> [1] TRUE
```

The default cache lives in the session's temporary directory, so nothing is
written outside the session unless you ask. For a long collection you will want
it to survive:

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

The APIs send no `ETag`, `Cache-Control` or `Last-Modified`, so HTTP caching
would store nothing — this cache is the package's own. Use `tg_updated_at()` to
decide when a cached copy is stale:

```{r}
tg_updated_at("fundoafundo")
#> [1] "2026-09-28 06:02:02 UTC"
```

## A pattern for very large tables

For anything in the hundreds of thousands, collect in slices and write each one
out, so an interrupted run does not start over:

```{r}
library(purrr)

total <- tg_count("fundoafundo", "gestao_financeira_lancamentos")
slice_size <- 20000
starts <- seq(0, total - 1, by = slice_size)

walk(starts, function(start) {
  file <- sprintf("lancamentos-%08d.rds", start)
  if (file.exists(file)) return(invisible(NULL))

  rows <- tg_get(
    "fundoafundo", "gestao_financeira_lancamentos",
    .limit = slice_size, .offset = start
  )
  saveRDS(rows, file)
})
```

Because the order is stable, the slices reassemble into the whole table without
gaps or repeats.