Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> This seems like the only option that will produce correct answers,
>> so it gets my vote. How much is the performance penalty for
>> materializing the tuplestore? I'm inclined to think that whatever
>> it is, you just have to pay it if you ask for a WITH HOLD cursor.
>
> I don't mind paying it for a WITH HOLD cursor, since by definition
> you're asking for a more expensive behavior there. The thing that
> is bothering me more is whether we want to pay a price for a *non*
> WITH HOLD cursor. You can get instability for seqscan or volatile
> functions if you just try MOVE BACKWARD ALL and re-read.
I would expect to pay more for a scrollable cursor than non-
scrollable; and in fact, the fine manual says "Depending upon the
complexity of the query's execution plan, specifying SCROLL might
impose a performance penalty on the query's execution time." That
would tend to argue in favor of taking the time to produce correct
answers. It does raise a question, though, about another sentence in
the same paragraph: "The default is to allow scrolling in some cases;
this is not the same as specifying SCROLL." Either we make people pay
for this when they haven't specified SCROLL but PostgreSQL has
historically given it to them anyway, or we might break existing
applications.
-Kevin