When paginating without cursors, it's possible to see phantom reads. E.g. paginating over a large set of data like so:
// assume isolation level = READ COMMITTED
SELECT * FROM users ORDER_BY age ASC LIMIT :pageSize OFFSET :page * :pageSize
can result in duplicates or missing items if concurrent transactions make changes to the data in between page calls - i.e. triggering phantom reads or nonrepeatable reads.
Do cursors in PostgreSQL exhibit the same behaviour? E.g. if I use a "read committed" isolation level, can fetching from the cursor result in phanton/nonrepeatable reads, causing items to be missed or returned more than once?
There cannot be any phantom reads if you paginate a result set using a cursor, because a cursor uses a fixed snapshot of the data, that is, it always sees the same state of the database, regardless of the life time of the cursor and any concurrent data modifications.
You find that mentioned in the documentation of DECLARE:
In PostgreSQL, all cursors are insensitive; so these key words have no effect and are only accepted for compatibility with the SQL standard.
Note, however, that a regular cursor only lives withing the context of a database transaction. So if you paginate a query result, and the user browses through the result set interactively, you'd need to keep a database transaction open for a very long time. That is something you cannot do, if you value the health of your database. A workaround that you can choose is a WITH HOLD cursor. Such a cursor lives longer than a database transaction, so you don't need to keep a transaction open. The result set of a WITH HOLD cursor is materialized on the server when the database transaction ends, so that commit can take a long time. You also have to remember to close the cursor, else the resources on the server are bound until the end of the database session.
See my article for details and further considerations.