Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?

boraldomaster wrote
> 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 ?

Your original examples only create the cursor and do not actually use it.
You should be comparing how long it takes both examples to fetch the first
10 pages of records to get a meaningful comparison.  It won't matter if the
DECLARE only takes 3ms in the non-hold case if retrieving the first page of
records take 2s.

Very few things are impossible, and this likely is not one of them, but
unless you don't want to go live with your solution for another couple of
years it likely does not matter whether the held cursor performance can be
improved by re-engineering the code.  The current implementation saves the
hold cursor's results to a temporary area as a snapshot so that it can be
used consistently outside of transactions without the hassle of creating an
actual persistent temporary table.  Basically you save having to send the
entire result-set to the caller but instead cache the results and feed only
a fraction of them at each request.  It does not seem intended to solve the
problem of dynamic fetching in that if it hasn't executed the query then how
is it supposed to know what the "next 10" records are?  This also ignores
the fact that held cursor wants to return the same data that existed at the
time of declaration - which is why a non-held cursor can only work in a
transaction where that state information is saved.  Since that state is
discarded the held version has to compromise by creating a snapshot of all
the data and persist it.

In theory the held cursor could save the meaningful state information and
during fetch the system could pretend it is operating some time in the past.
You are welcome to try and convince someone to explore this and other
theories - I am not that person nor do I have the time/need to perform the
convincing.

I do not have any meaningful experience with your scenario but if a page is
a very small fraction of the total table size then using indexes, limits,
and offsets should give you solid performance probably 98%+ of the time.
Yes, large offsets can be problematic but reverse ordering can help AND in
most use cases the frequency of high page numbers compared to lower ones is
significantly less.

David J.








--
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-tp5762401p5762567.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.