Hacker News Reader

Comment by londons_explore | Hacker News Reader

What if the last element on a page is deleted when you click "next"?

You use the sort key as the page selector. So if it's cities sorted by name, the query is "give me the first 20 cities with names after 'Constantinople'". If Constantinople gets the works, it doesn't matter, because there are still 20 cities whose names come after that lexicographically.

If it is updated, though (in this case, renamed to Istanbul), it can appear later once again, and newly inserted rows behind "Constantinople" will never appear.

I mean, these problems happen if you use offsets too, and my point is that they don't matter.

The most hardcore solution to those would be pre-computing pages but I doubt it makes sense to bring even more state to the search results.

Yes, pagination over a consistent view is a whole other problem! Either you store search results server side, in which case now you need to think about expiration and running out of space, or you are able to regenerate them on the fly somehow. A database which supports as-of queries might make that quite easy, but not many databases do.

> but not many databases do.

Really? The underlying storage engine of most databases stores all data necessary for an "as of" query. Simply ignore all data pages newer than the timestamp cutoff, and prevent the garbage collection of any page that could be part of such a query.

In fact thats the way [eg. postgres] can do a long-running query on a table that someone else is modifying. You in effect are looking at a snapshot of the table at the moment you started the query, even if it takes an hour to produce all the results.

> If Constantinople gets the works

Relevant song: https://youtube.com/watch?v=Z0JhC3LO0-8

> What if the last element on a page is deleted when you click "next"?

That makes absolutely no difference. You're still querying the database for elements that come after a value from a data type for which there is an order. You don't need that element to exist to run a comparison. For example, consider a timestamp-based query: you don't need an element with that specific date to exist to search for any element whose timestamp was taken after an arbitrary moment.

A B C D

2 items per page

first page is A&B. Next query is "WHERE ID>'B'".

What's the problem?

Aa and Ab are inserted after loading the first page.

Sure, and then B comes by again. Which just means there are different use cases with different definitions of correct, and you need to specify yours.

That's an orthogonal problem that you need to define correctness for your application.

Because LIMIT...OFFSET will also arguably give you the wrong result. E.g. Aa is inserted. The user will see "B" both as the last item on the first page, and the first item on the second page.

Or with your example:

Aa Ab B C

Now "A" is inserted between page loads. The user will never see "A". What's right for your application? Maybe a notification saying "previous pages have gotten new items". Maybe not. It all depends.

Reddit can be annoying if you go page after page. As stories are bumped down you see them again. That, in my opinion, is a bug. But solving it requires something completely different from merely defining page boundaries.

Reply on news.ycombinator.com