Question 1370

The Row That Vanished

A feed endpoint uses keyset pagination. The cursor is a base64 blob holding the last row's updated_at and id.

def page(cursor, limit=50):
    if cursor:
        ts, last_id = decode(cursor)
        rows = db.query(
            "SELECT * FROM posts "
            "WHERE (updated_at, id) < (%s, %s) "
            "ORDER BY updated_at DESC, id DESC LIMIT %s",
            ts, last_id, limit)
    else:
        rows = db.query(
            "SELECT * FROM posts ORDER BY updated_at DESC, id DESC LIMIT %s",
            limit)
    nxt = encode(rows[-1].updated_at, rows[-1].id) if rows else None
    return rows, nxt

Users report that posts occasionally never appear, no matter how far they scroll. Which interleaving explains it?

A

The cursor is base64 but unsigned, so a tampered cursor can walk the client into rows it should not see.

B

A post that would have landed on page 4 is edited after the client fetched page 1. Its updated_at jumps to now, moving it above the cursor, so no later page will ever return it.

C

Two posts share the same updated_at, and the comparison cannot break the tie, so one of them is skipped.

D

A new post is inserted at the top between page fetches, shifting every subsequent row by one position.