---
title: Bound the unbounded whereIn bind lists
type: task
status: planned
description: >-
  Postgres caps a statement at 65,535 bind parameters and knex whereIn binds one per element; 20+
  league call sites build that list from a query result, and the 16-bit count WRAPS rather than
  erroring so the crash names neither the limit nor the query.
base_uri: user:task/league/bound-unbounded-wherein-bind-lists.md
created_at: '2026-09-04T19:04:48.165Z'
entity_id: e33ba7e4-b349-43ce-b9e3-213473666d8b
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-10-01T04:49:44.055Z'
visibility_analyzed_at: '2026-11-05T23:48:25.819Z'
---

# Bound the Unbounded whereIn Bind Lists

## Overview

- Postgres accepts at most 65,535 bind parameters in a statement, and a knex `.whereIn(col, list)` binds one per element. League has 20+ call sites whose list is built from a query result rather than from a bounded constant, so any of them crashes once its corpus grows past the ceiling.
- Impact: the failure mode is the problem, not the crash. The 16-bit parameter count WRAPS rather than erroring cleanly, so the run dies with `bind message has N parameter formats but 0 parameters` — a protocol error naming neither the limit nor the query's intent. It reads as database corruption or a driver bug.
- Found on 2026-09-04 in `scripts/calculate-historical-hit-rates.mjs`, where the list carried one esbid per gamelog ROW rather than per distinct game: 74,368 parameters for ~6,900 distinct games. Fixed there in league `f46aef4a1`.

## Background

The fixed site had two independent faults, and both recur across the other call sites:

- **Not deduplicated.** `player_gamelogs.map(g => g.esbid)` yields one esbid per player per game. A season is ~285 distinct games behind ~36,000 gamelog rows, so dedup alone removed 99% of the parameters.
- **Not chunked.** Even deduplicated, a list that scales with the corpus eventually crosses the ceiling.

The repo already carries the tool: `chunk_array` in `libs-shared/chunk.mjs`, and `build_esbid_chunks` in `calculate-historical-hit-rates.mjs` is the worked pattern.

Note the cron on the fixed site never hit this in a year of running, because `--current_week_only` kept its list to one week. A site that is safe under its scheduled invocation is not safe under a backfill invocation, and the backfill is exactly when someone is watching least.

## The work

Enumerate the call sites from the code that defines the class, not by grepping for `esbid`:

```bash
grep -rn "whereIn(" scripts/ libs-server/ jobs/ --include=*.mjs
```

For each, decide which of three it is, and say which in the commit:

- **Bounded by construction** — the list is a constant, an enum, or a fixed small set (`['REG','POST']`, `stat_countable_play_types`). Leave it.
- **Bounded in practice but not by construction** — a week's games, one team's players. Dedup if it repeats, and note the bound that holds it.
- **Scales with the corpus** — every season, every player, every market. Dedup AND chunk.

Several sites already read `unique_esbids`, so the dedup half is partly done and partly not; do not assume a variable named `unique_*` was actually deduplicated at its source.

## Assumptions

- **The ceiling is 65,535 and it wraps rather than erroring.** Verified 2026-09-04: 74,368 esbids produced `8832 parameter formats` (74368 - 65536 = 8832).
- **UNCHECKED — whether any site is currently over the ceiling in production.** The fixed one was reachable only via a backfill invocation nobody had run. Others may be equally latent, or already broken on a path nobody exercises. Measure the live list sizes before assuming this is theoretical.

## Plan

### Core Implementation

- [ ] **Enumerate and classify every whereIn call site**
  - Purpose: a defect in shared machinery reaches everything routed through it; the sites noticed first share a naming convention rather than a cause.
  - Verify: the classification names every site the grep returns, with a stated reason per site. A site left unclassified is a site nobody checked.

- [ ] **Fix the sites that scale with the corpus**
  - Changes: dedup via `new Set`, chunk via `chunk_array`, following `build_esbid_chunks`.
  - Verify: a spec that feeds a list past 65,535 and asserts every chunk is under the ceiling, shown red against the unchunked shape. The spec in `test/scripts.hit-rate-esbid-chunking.spec.mjs` is the worked example, including the control that a completeness-only assertion cannot catch.

### Testing and Validation

- [ ] **Confirm no fixed site changed its result set**
  - Purpose: chunking splits one query into several, and a site that relied on a single query's ordering or a join's row multiplicity can change behaviour silently.
  - Verify: for each fixed site, run it before and after over the same input and require identical output as a set.