miguelgrinberg/microblog-api

Microsecond precision for pagination

Closed this issue · 3 comments

Christoph-ModelMe commented

Hi, I noticed problems with my pagination. After I digged into it, I noticed that for my app the pagination does not work if the posts were all created in the same second, even though they differ in the microseconds and the microseconds are correctly stored in the database.

Is this maybe a marshmallow bug or in the code or on my end?

miguelgrinberg commented

You haven't described what the problem is, you just said there is a problem. Without details I have no way to provide advice. Does my application in this repo suffer from the same issues? If not I would suggest you review your paginated query.

Christoph-ModelMe commented

oh yes sorry... So I could reproduce it with this repo. This is what I did:

  • changed the timestamp for the 100 fake posts such that they have each a distance of 10 microseconds
  • in the database the microseconds are shown correctly
  • however when I look at "explore" page and click on "more", it will not load more posts but it will load the same posts again
  • I think the reason is that the comparison between the timestamps is not working correctly in the microsecond regime, also the offset is not calculated correctly anymore in the decorator

BUT:

  • this time I noticed that this only happens for postgresql db but not when I use the default sqlite db
  • so maybe this can be fixed by adjusting the timestamp column in the models.py somehow, but I do not know how to do this in sqlalchemy 2.0
miguelgrinberg commented

This could be a floating point precision issue. I would suggest that if you need high accuracy at the microsecond level you use an integer not a datetime, which would write the seconds as a float. The integer could be a UNIX timestamp multiplied by 10^6, so that you store it in microseconds instead of seconds.

  • 👍1