ShotGrid find pagination stability with limit and offset under concurrent changes

Pagination Stability and Ordering

ShotGrid does not provide a native cursor-based pagination mechanism for find operations. Because it relies on limit and offset, there are no documented guarantees for result stability under concurrent modifications. If entities are created or deleted between successive page requests, the result set will "drift," leading to duplicate or missing records.

Likely Behavior Under Concurrent Changes

Since offset is a numerical skip count rather than a pointer to a specific record, the following behaviors occur during multi-page retrieval:

• Deletions: If a record on a previously fetched page is deleted, all subsequent records shift forward. The first record of the next page will be skipped.
• Insertions: If a new record is created that matches the filter and sorts before the current offset, subsequent records shift backward. The last record of the previous page will appear as the first record of the current page.

Deterministic Bulk Access Pattern

To minimize inconsistency, you must implement a deterministic sort order and a sliding window approach. While this does not eliminate drift, it makes the behavior predictable.

1. Enforce Strict Ordering: Always include a unique, immutable field in your order parameter (typically id). Without a unique sort key, the database may return records in an inconsistent order across requests.
2. Use a Comparison Filter: Instead of relying solely on offset, use the value of the last retrieved unique ID to filter the next page.

# Example logic for stable retrieval
last_id = 0
while True:
    # Filter for IDs greater than the last seen ID
    filters = [ ['id', 'gt', last_id], ... ]
    results = sg.find(entity='Asset', filters=filters, order=[['id', 'asc']], limit=100)
    if not results:
        break
    # Process results and update the pointer
    last_id = results[-1]['id']

Verification Steps

To verify the stability of your current implementation, perform these tests in a sandbox environment:

• Request page 1 (limit 10, offset 0).
• Delete one entity that matches the filter and would have appeared on page 1.
• Request page 2 (limit 10, offset 10).
• Check if the record that should have been the 11th item was skipped.

Diagnostic Detail Needed: Are you utilizing the Python API wrapper or raw REST calls? The Python API handles some request batching differently, which may impact how you implement the ID-based filtering loop.