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

boraldomaster wrote
> If there any possiblity to have a cursor that as fast as cursor without
> hold and as transaction-independent as cursor with hold ?

Depends...

From the documentation - the first source of potential understanding:

http://www.postgresql.org/docs/9.0/interactive/sql-declare.html

"In the current implementation, the rows represented by a held cursor are
copied into a temporary file or memory area so that they remain available
for subsequent transactions."

Can you make that copy take considerably less time OR come up with a better
implementation?

I guess, in theory, you could reduce the startup cost by only creating the
temporary file at commit instead of at the declare.  In the case where you
scroll through the whole cursor once during the transaction as each row is
emitted it could also be cached at that point so only un-emitted rows would
have to be spooled before the commit could finish.

If you provide more of the how/why of what you are doing people may be able
to suggest alternative solutions - though 3 seconds for a 10-million row
cursor/temporary-table does not seem that hideous.

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