[erikpukinskis]

Yah, another downside of cursor-based pagination is: what happens when the record the cursor refers to is deleted?

Do you just crash and ask the user to start over?

Do you have to nudge open cursors on every delete?

[simonw]

My implementation of cursors works by encoding the primary ID of the last row on the page, along with additional information corresponding to the sort order if that's needed.

That way it doesn't matter if the record is deleted - I can still return the next page by showing records that come after that provided cursor value.

There's an example on this page: https://latest.datasette.io/fixtures/sortable?_sort=sortable

Since the table is sorted by the "sortable" column, the next page link includes this:

    ?_next=15%2Cg%2Cz

15 is the last value for "sortable" on the page, then g,z are the compound primary key for that last row.

[erikpukinskis]

Interesting… what happens if new records are added with that 15 value? Do you need an implied secondary sort with the record created time?

Also what if there are more than $page_size records with that 15 value?

[simonw]

Yes, if you want to support new records being added it's up to you to include something like a created date as part of your specified sort order.

More than page_size records with that value works fine - that's why the primary key is included as a tie-breaker.

[erikpukinskis]

Doesn’t that take you back to the original problem though: if you delete the primary key the query fails?

Would the created_at be a better option than the primary key? That can’t be deleted or changed, and that would give you a stable secondary sort?

[hamandcheese]

Instead of the cursor being an ID, it could directly encode whatever column(s) you are sorting by. Then you don’t have to locate any record in particular, you can always return records that sort after the cursor.