PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Wrong pagination in MAX() query of postgres

This is my query to fetch the latest message from each room:

SELECT MAX
    ( "Messages"."id" ) AS messageId,
    "Rooms"."id" 
FROM
    "RoomUsers"
    INNER JOIN "Rooms" ON "RoomUsers"."roomId" = "Rooms"."id"
    INNER JOIN "Conversations" ON "Conversations"."roomId" = "Rooms"."id"
    INNER JOIN "Messages" ON "Messages"."conversationId" = "Conversations"."id" 
WHERE
    "RoomUsers"."userId" = 51
GROUP BY
    "Rooms"."id"

And the output could be something like this:

+----------+-------+
|messageId |   id  |
+----------+-------+
|    1     |   4   |
|    17    |   5   |
|    7     |   6   |
|    29    |   7   |
|    18    |   8   |
|    19    |   9   |
+----------+-------+

As you can see, this is just a small result set, but if I have more than 1000 rooms with messages, it could become a large dataset, and fetching it all at once isn’t efficient or necessary for the application.

So, I want to implement pagination using OFFSET and LIMIT.

For testing, I started with small pages.

I modified my query to paginate the data with 3 records in each page:

SELECT MAX
    ( "Messages"."id" ) AS messageId,
    "Rooms"."id" 
FROM
    "RoomUsers"
    INNER JOIN "Rooms" ON "RoomUsers"."roomId" = "Rooms"."id"
    INNER JOIN "Conversations" ON "Conversations"."roomId" = "Rooms"."id"
    INNER JOIN "Messages" ON "Messages"."conversationId" = "Conversations"."id" 
WHERE
    "RoomUsers"."userId" = 51
GROUP BY
    "Rooms"."id" 
    LIMIT 3 OFFSET 0

After running this query, this is the output:

+----------+-------+
|messageId |   id  |
+----------+-------+
|    1     |   4   |
|    17    |   5   |
|    7     |   6   |
+----------+-------+

It’s okay just for this page. And if I add a page and change the offset to 1, this is the result:

+----------+-------+
|messageId |   id  |
+----------+-------+
|    17    |   5   |
|    7     |   6   |
|    29    |   7   |
+----------+-------+

And if I increase the offset’s number and change it to 2, this is the next result:

+----------+-------+
|messageId |   id  |
+----------+-------+
|    7     |   6   |
|    29    |   7   |
|    18    |   8   |
+----------+-------+

So, what is going on here?

I couldn’t understand the reason, and I need to paginate this data too!

What can I do, and how should I change it?

Tags:

2

Answers


  1. 0

    Assuming 3 rows per page the offset needs to be 0, 3 and 6.

    Also, the order of result is undeterministic without an order by clause. Add:

    ORDER BY "Rooms"."id"
    Login or Signup to reply.
  2. 0

    Postgres has non-embedded SQL cursors, and that’s pretty much exactly what they’re for. Demo:

    DECLARE messages_cursor CURSOR WITH HOLD FOR 
SELECT MAX
    ( "Messages"."id" ) AS messageId,
    "Rooms"."id" 
FROM
    "RoomUsers"
    INNER JOIN "Rooms" ON "RoomUsers"."roomId" = "Rooms"."id"
    INNER JOIN "Conversations" ON "Conversations"."roomId" = "Rooms"."id"
    INNER JOIN "Messages" ON "Messages"."conversationId" = "Conversations"."id" 
WHERE
    "RoomUsers"."userId" = 51
GROUP BY
    "Rooms"."id";

    Run it once, and fetch the results page by page:

    FETCH FORWARD 3 FROM messages_cursor;

    When you’re done, close it:

    CLOSE messages_cursor;--/*if you want to clean them all up, then*/CLOSE ALL;
    1. It saves you recomputing the whole thing each time, then sorting it each time, then throwing most of it out each time that repeated queries with progressing offset would do.
    2. If you don’t need the results to be sorted, you don’t need to slow things down with the order by.
    3. Cursor remains stable (insensitive) at all times, whereas any delete/insert/update applied to that table while you’re in the process of reading pages, whacks limit/offset out of order.
    4. WITH HOLD persists the cursor even if you have autocommit on, or need to run commits between fetches in the same session. The only way to stabilise limit/offset-based pagination is to restrict it to a single repeatable read transaction, or dump it somewhere and read the dump, emulating the cursor behaviour ineffectively. WITH HOLD resorts to caching only if you commit before closing.

    That being said, cursor is just a single notch up from limit/offset pagination – there are other ways.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search