5 ms·

Another reason to use cursors is to avoid the problem of repeated or skipped elements caused by concurrent edits. If you use offsets and you’re on page 10, and

• contravariant 6y ago
  Though you can't avoid seeing an item just before it is deleted and skipping an item just before it is created. This isn't a huge problem but does mean that the list you end up with may not be a coherent view of the database. But if you require guarantees like that then you might be better of just using SQL directly. Or just include everything in a single response, HTTPS was designed to handle arbitrary length payloads if I recall correctly. You might want to use a more streamable format than JSON though.
  • dharmab 6y ago
    If you care about that kind of guarantee you might want to keep a connection open and stream new updates.
    • rualca 6y ago
      > If you care about that kind of guarantee you might want to keep a connection open and stream new updates. You don't need that. You just need to get the collection resource to be cacheable and subsequently track your resources' state with conditional requests. https://developer.mozilla.org/en-US/docs/Web/HTTP/Conditional_requests https://developer.mozilla.org/en-US/docs/Web/HTTP/Conditiona...
  • ajitid 6y ago
    > use a more streamable format than JSON Did you mean we should stream data over WebSocket or use HTTP/2 or do we need to do something different altogether?
    • alexeldeib 6y ago
      AFAIK chunked transfer works specifically with HTTP 1.1, think Kubernetes for example uses this with JSON in a few prominent places for things like WATCH. But that setup is hardly ideal for the use case.
    • cheschire 6y ago
      Check out MessagePack. https://msgpack.org/ https://msgpack.org/
    • contravariant 6y ago
      I don't think you need to do anything difficult you can just treat it as one big file, pretty sure online radio streams have been using this technique for ages. Though I'm not too sure about the particulars. This is annoying to do with JSON though because you need to remember to close all your brackets, something like CSV is a lot easier because you can just write the header once and then just stream the data. Edit: Looks like Python supports this using chunked transfer by simply providing the HTTP request data as an iterator.
• ajitid 6y ago
  I have question, lets say we have page 1 elements: A B C D E page 2 elements: F G H I J so 5 elements in each page. Suppose I'm on page 2. If I insert a new element Q and it gets pushed as first then page 1 will have Q A B C D. Now if I go back to page 1, I'll get A B C D E and also a token/pointer to go back one more time only to retrieve Q. So while cursor solved the issues you mentioned, it still will have this case in which pagination gets broken. I'm interested how we can tackle this.
  • e12e 6y ago
    A cursor is either valid in a transaction, or will cache its results - either way it will give a consistent view of data: https://www.postgresql.org/docs/13/sql-declare.html https://www.postgresql.org/docs/13/sql-declare.html
    • lenkite 6y ago
      These cursors are not database cursors. I too was confused by the bad terminology...
      • e12e 6y ago
        Ah, I assumed from the topic, term and discussion that this would be implemented via sql cursors. Apparently elastic search has a similar concept - but like sql cursors it cannot be stateless (because you cannot ask for Nth to Mth result of searching the collection as of state Z -if you don't want to capture/supply state Z). This means any stateless pagination api is fundamentally broken. Please don't make promises you cannot keep. For elastic: https://www.elastic.co/guide/en/elasticsearch/reference/current/paginate-search-results.html#search-after https://www.elastic.co/guide/en/elasticsearch/reference/curr...
• Too 6y ago
  One way to get around this is to drop the notion of page number. Instead do greater than last element on last page. But this has the drawback of only working as long as the sorting field doesn't have many duplicates. You have to beware if timestamps are high resolution enough for your application or if you have 100+ entries with same family name. Usually users are smart(lazy) enough to realize they got more than 100 results and will narrow down the search instead. When was the last time you looked beyond even the first 5 results on google yourself, i'd rather adjust the search term than go to page 2.
  • ralusek 6y ago
    If your ability to query can manage sorting on two fields, this can be resolved by sorting on the field first, then the primary key as a secondary field. If I'm sorting by last name, and I sort by last name, then id, I can save the cursor as last name + id, and be able to continue right where I left off.
  • rualca 6y ago
    > One way to get around this is to drop the notion of page number. Instead do greater than last element on last page. That's the whole point of a cursor, isn't it? And pagination just means querying the records to return a limited subset.
    • deleted 6y ago
      [deleted]
    • Too 6y ago
      Depends how long the cursor is alive. Url containing ?date=lt:2012-12-27 is stateless, user readable and more likely to outlive ?cursorId=xyaxjeasu&page=2. Now it might not be as accurate as having a cursor but it is a lot easier to implement, and still beats simply ?page=2 without cursor, which is the level you see in most apis today.
      • rualca 6y ago
        > Depends how long the cursor is alive. The cursor is never invalidated. You can always get a valid response from any cursor, whether the resource exists or was already deleted. That's the whole point of using a cursor. Perhaps your confusion lies in assuming that a cursor's life cycle is tied to a specific object. It isn't. A cursor means "get me whatever resources would immediately follow this resource". It matters nothing if the cursor exists or not. When you run the query, you will get exactly the resource which would follow the cursor. That's by design.
• profquail 6y ago
  Another approach to solving this issue is with a temporal database (standardized in SQL:2011); supporting a “valid time” in your query gives your user a consistent view of the database tables even if others are modifying it concurrently. It’s also handy if you want to keep audit records of the database (e.g. so users can see who/when/what changes were made). https://en.wikipedia.org/wiki/SQL:2011 https://en.wikipedia.org/wiki/SQL:2011