---
title: Make the data-view sort a total order so pagination is stable
type: task
status: planned
description: >-
  The 500-row page is not reproducible across plans: the generated ORDER BY is not a total order, so
  ties break arbitrarily by plan and offset pagination can repeat or skip rows.
base_uri: user:task/league/data-views/make-data-view-pagination-order-total.md
created_at: '2026-09-23T18:54:28.818Z'
entity_id: 03869d61-e003-400e-b97d-0e0e0907a841
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[█████████████████████-██████████-███-███████-██████████-███-████-████-███████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-23T19:23:39.508Z'
visibility_analyzed_at: '2026-11-05T23:48:54.170Z'
---

## Overview

The generated `ORDER BY` is not a total order, so which rows land on a page is a property of the PLAN rather than of the data. Found 2026-09-23 while measuring [[█████████████████████-██████████-███-███████-██████████-███-████-████-███████]]: view `bc6cd0c8` returned a different 500 rows under the materialised-only arm than under the other two, with no change to the query or the data.

The sort it emitted was `order by 5 DESC NULLS LAST, player.pid asc`. That looks total because of the trailing `player.pid`, and is not — the statement is not at player grain, so `player.pid` does not uniquely identify a row, and rows sharing a pid tie. Ties then break by whatever order the executor happened to produce, which changes with the plan.

Two consequences, and the second is the one users see:

- **A page is not reproducible.** The same request can return different rows on two executions, which also makes any result-set comparison across plans unreliable as a correctness oracle.
- **Offset pagination can repeat or skip rows.** Page 2 is `OFFSET 500` over a re-sorted result, so a row that tied its way onto page 1 can appear again on page 2, and another can be skipped entirely. No row is lost from the table, but a user paging through an export sees duplicates and gaps.

This is pre-existing and independent of the CTE-materialisation work — that work only made it visible, by producing two plans for the same statement.

## What is not yet known

- Which saved views are affected, and how many. The tie-breaking column is emitted per row grain, so the answer is a property of the grain rather than of the individual view.
- Whether the right fix is to append the full row-grain key tuple to every `ORDER BY`, or to move pagination off `OFFSET` onto a keyset cursor. The first is much smaller and fixes reproducibility; only the second fixes pagination under concurrent writes.

## Plan

- [ ] Enumerate the row grains from the row-grain registry and, for each, identify the column tuple that uniquely identifies a row.
- [ ] Measure how many of the saved views currently emit a non-total `ORDER BY`.
- [ ] Append the grain's identifying tuple to the emitted sort, and add a negative control proving a view with ties now returns the same page twice under forced-different plans.
- [ ] Decide separately whether offset pagination should move to a keyset cursor, which is a larger change and may not be warranted.