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.