I want to implement stable pagination using Postgres database as a backend. By stable, I mean if I re-read a page using some pagination token, the results should be identical.
Using insertion timestamps will not work, because clock synchronization errors can make pagination unstable.
I was considering using pg_export_snapshot() as a pagination token. That way, I can reuse it on every read, and the database would guarantee me the same results since I am always using the same snapshot. But the documentation says that
"The snapshot is available for import only until the end of the transaction that exported it."
(https://www.postgresql.org/docs/9.4/functions-admin.html)
Is there any workaround for this? Is there an alternate way to export the snapshot even after the transaction is closed?
You wouldn't need to export snapshots; all you need is a REPEATABLE READ READ ONLY transaction so that the same snapshot is used for the whole transaction. But, as you say, that is a bad idea, because long transactions are quite problematic.
Using insert timestamps I see no real problem for insert-only tables, but rows that get deleted or updated will certainly vanish or move unless you use “soft delete and update” and leave the old values in the table (which gives you the problem of how to get rid of the values eventually). That would be re-implementing PostgreSQL's multiversioning on the application level and doesn't look very appealing.
Perhaps you could use a scrollable WITH HOLD cursor. Then the database server will materialize the result set when the selecting transaction is committed, and you can fetch forward and backward at your leisure. Sure, that will hog server resources, but you will have to pay somewhere. Just don't forget to close the cursor when you are done.
If you prefer to conserve server resources, the obvious alternative would be to fetch the whole result set to the client and implement pagination on the client side alone.