My use-case is just creating paginated list for a large table. The first obvious option is offset limit but it works too slow for great offset. A lot of topics propose using cursors for that, so I am learning this possibility. You will say that there are other possibilities. Yes - but I am trying to compare all them, therefore learning cursors at the moment. If cursors don't fit - I will learn other options.
So - for now I see the following. Unheld cursors are not usable at all for this purpose, as they require a connection per client + table. Held cursor - is a bit better. But bad things. 1. It is created as long as creating temp table. 3s is not an acceptable reponse time. Even for the 1st query. 2. Held cursor is visible only to connection. So I need to create it for every connection. This means 3s per connection. 3. To ensure effective cursor usage I need to ensure that any web-client session wroks with the same DB connection while listing table pages. Besides - cursor is obviously shows not-up-time results as this is in fact some old snapshot of data. All this moves me away from using cursors. But if held cursor was created as fast as unheld - I could change my opinion. I don't understand why is this really impossible. When I create unheld cursor - it takes 1 ms. Why cannot held cursor do the same (but store in session - or even better in whole db - anything it stores in transaction when being unheld). Even algorythmically - this should be possible. If I make *select * from z* - it actually shouldn't fetch anything - just save this query. When I do *fetch 10 from mycursor* - it should fetch first 10 records but not more. And so on. So - why is this really impossible ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-a-cursor-that-is-independent-of-transactions-and-doesn-t-calculated-when-created-tp5762401p5762543.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
