Inconsistent keyset pagination when using (timestamp, uuid) fields
5
votes
1
answer
1788
views
I am using the keyset pagination method for uuids on my Postgres database described in this post:
- https://dba.stackexchange.com/questions/267794/how-to-do-pagination-with-uuid-v4-and-created-time-on-concurrent-inserted-data
However, I have noticed when I have two records where the date is the same, rows are being skipped from the result.
For example when I run the query:
SELECT id, created_at FROM collection
ORDER BY created_at DESC, id DESC
I get the records back as I expect them, with created_at
being the primary order, then id
acting as a tiebreaker:
|id|created_at|
|--|----------|
|e327847a-7058-49cf-bd91-f562412aedd9|2022-05-23 23:07:22.592|
|d35c6bb8-06dd-4b86-b5c6-d123340520e2|2022-05-23 23:07:22.592|
|5167cf95-953f-4f7b-9881-03ef07adcf3c|2022-05-23 23:07:22.592|
|d14f48dc-df22-4e98-871a-a14a91e8e3c1|2022-05-23 23:07:21.592|
However when I run a query to paginate through like:
SELECT id, created_at
FROM collection
WHERE (created_at, id) < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC
LIMIT 3
I would expect to get back the last two records, but my result set is instead
|id|created_at|
|--|----------|
|d14f48dc-df22-4e98-871a-a14a91e8e3c1|2022-05-23 23:07:21.592|
I've also tried some variations on the query to try to fix it, such as:
SELECT id, created_at
FROM collection
WHERE created_at < '2022-05-23 23:07:22.592' OR
(created_at = '2022-05-23 23:07:22.592' AND id < 'd35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC
But I still get back the same result set.
What's going on with my query?
Asked by Daniel
(51 rep)
May 24, 2022, 05:53 PM
Last activity: May 24, 2022, 07:53 PM
Last activity: May 24, 2022, 07:53 PM