`getLocations()` materializes the whole `locations` table in a single query — `CursorWindowAllocationException` on large/offline databases. Please expose a paginated read (limit/offset) like `Logger.getLog(SQLQuery)`.

[Rafael-N-Moura]

Summary

BackgroundGeolocation.locations (the Dart getLocations method) reads all rows of the internal locations table in a single query. When the database grows large (e.g. a device that stays offline for a long time and accumulates thousands of records), reading it crashes natively on Android with:

android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed.

There is currently no way, from the public Dart/native API, to read locations in bounded chunks (limit/offset). The only mitigation is Config.maxRecordsToPersist, which prevents the problem by discarding the oldest records — i.e. it trades a crash for permanent data loss of buffered locations. We would like a way to read large buffers safely instead of having to drop them.

Environment

• flutter_background_geolocation: 4.18.0
• Native tslocationmanager (Android AAR): 3.7.0
• Flutter 3.29.2 / Dart 3.7.2
• Platform where the crash occurs: Android (the 2 MB CursorWindow is an Android IPC limit)
• Use case: continuous background tracking during delivery routes, with autoSync/batchSync to our own HTTP endpoint, plus a secondary "drain remaining locations into our durable outbox" step performed via BackgroundGeolocation.locations when a route ends.

Current behavior

BackgroundGeolocation.locations takes no arguments and returns the full table:

// flutter_background_geolocation/lib/models/background_geolocation.dart
static Future<List> get locations async {
  return (await _methodChannel.invokeListMethod('getLocations'))!;
}

A SQLQuery model with limit/order/start/end does exist, but it is only wired to the Logger database (Logger.getLog, Logger.emailLog, Logger.uploadLog) — not to the locations table. So we cannot pass a limit when reading locations.

Inspecting the native AAR (com.transistorsoft.locationmanager.data.sqlite.LocationOpenHelper / SQLiteLocationDAO), the locations schema is:

CREATE TABLE IF NOT EXISTS locations (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  uuid      TEXT NOT NULL DEFAULT '',
  timestamp TEXT,
  json      TEXT,
  data      BLOB,
  encrypted BOOLEAN NOT NULL DEFAULT 0,
  locked    BOOLEAN NOT NULL DEFAULT 0
);

The DAO already issues bounded/ordered SQL internally for other operations, e.g. the maxRecordsToPersist trimming:

... id <= (SELECT id FROM (SELECT id FROM locations ORDER BY id DESC LIMIT 1 OFFSET <maxRecordsToPersist>))

and counting:

SELECT count(*) FROM locations WHERE locked=0

So the native layer already has the primitives (ordering, limit, offset, count) — they are simply not exposed for reading locations into the app.

It is also worth noting that the batch HTTP sync already reads safely in pages (maxBatchSize, executing repeated LIMIT maxBatchSize requests until the queue is empty). The crash only happens on the Dart read path (getLocations), which has no equivalent paging.

Why it matters

• Devices accumulate locations while offline. The 2 MB CursorWindow is reached at roughly ~2,600 records (~800 bytes/record), after which getLocations throws and the buffered data becomes unreadable by the app.
• The only supported workaround (maxRecordsToPersist) avoids the crash by dropping the oldest records — meaning the app must choose between crashing and losing data. For fleet/telemetry use cases this is a hard trade-off.
• A paged read would let apps stream a large buffer out to their own durable storage/outbox in safe chunks, without data loss and without depending on connectivity (unlike sync()).

Steps to reproduce

1. Configure the plugin with HTTP and let it record while the device has no connectivity (so autoSync cannot drain the DB), or set autoSync: false.
2. Let the locations table grow beyond ~2,600 rows (hours of continuous tracking).
3. Call final locations = await BackgroundGeolocation.locations;.
4. Observe the native crash:

android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed.
    at android.database.CursorWindow.<init>(CursorWindow.java)
    at android.database.AbstractWindowedCursor.clearOrCreateWindow(...)
    at com.transistorsoft.locationmanager.data.sqlite.SQLiteLocationDAO ...

Proposed solution

Expose a paginated/bounded read for locations, mirroring the existing SQLQuery already used by Logger:

// Desired API (any of these shapes would solve it):
Future<List> getLocations([SQLQuery query]); // query.limit / query.order / query.start / query.end
// or
Future<List> getLocations({int? limit, int? offset, int order = SQLQuery.ORDER_ASC});

Equivalent native query against the existing schema:

SELECT id, uuid, timestamp, json, data, encrypted
FROM locations
WHERE locked = 0
ORDER BY id ASC
LIMIT ? OFFSET ?;

This would let consumers iterate the buffer in safe pages (e.g. 200–500 rows), persist each page, and delete it (destroyLocation(uuid) already exists), never exceeding the CursorWindow limit and never losing data.

A streaming variant (e.g. a method that yields pages, or accepts a page callback) would be even better, but a simple limit/offset overload is sufficient.

Workarounds we considered (and their limitations)

1. Config.maxRecordsToPersist — prevents inflation but discards the oldest records; it's data loss, not data preservation.
2. BackgroundGeolocation.sync() — drains via batched HTTP safely, but requires connectivity; the database inflates precisely because the device is offline, so sync() cannot help in the exact scenario that causes the crash.
3. Reading the SQLite file directly (opening TSLocationManager with our own SQLite library and paging with LIMIT/OFFSET) — technically possible since json is plaintext by default, but it couples us to undocumented internal schema, is Android-only, risks SQLITE_BUSY/locking against the plugin's own connection, and breaks if Config.encrypt is enabled. Not viable for production.

A first-class paged read API would remove the need for all of these.

Thanks for the great plugin — happy to test a patch.

[christocracy]

How many records do have?

flutter_background_geolocation: 4.18.0

v4 is no longer supported. If a paging api is implemented, it'll arrive only in v5.

[Rafael-N-Moura]

We don't capture the exact row count at crash time, but the crash is purely a function of the 2 MB Android CursorWindow limit. With our payload (~700–900 bytes per locations.json row) it fails somewhere around ~2,600 rows. These counts accumulate legitimately, the device tracks a delivery route in the background and, when it's offline for an extended period, autoSync can't drain the DB, so the buffer
grows until getLocations() can no longer materialize the whole table at once.

[christocracy]

It will be implemented in v5

[christocracy]

Implemented getLocations with optional query.

// One page of 500, newest first
List page = await BackgroundGeolocation.getLocations(LocationQuery(
  limit: 500,
  page: 0,
  order: LocationQuery.ORDER_DESC
));

Released to 5.4.0.