Thread: newbie patch-attempt: selecting large result sets in psql

Hi :)

powered by the great talk for newbie hackers by Neil Conway & Gavin
Sherry [1] at the anniversary summit I sneaked into the Code Sprint
and started working a bit on a Todo from Neil's Code Sprint Page:
 "Add a psql option to have it submit SELECT queries via a cursor, to avoid excessive memory consumption when
manipulatinglarge result sets"
 

Gavin gave me a sketch of a patch that added a new command line
option to psql that would have it use cursors for selects.

One of the problems with this was that a user would expect psql to
work as usual (including all format and output option stuff) and
to do this properly most of the psql output code would need to be
refactored.

Thinking about it, we had the idea to just introduce a new \ operator
that would output the results of a select (using cursors) in an
indipendent code path.  Who's selecting gigabytes of stuff into a
HTML table anyway?

So, I've introduced a new \u command that from a user perspective
is identical to \g (it executes the query buffer), just that it
uses cursors and honours only the field separator.

For example you could just do

select a, c, g
from big
\u bigout.dat

The patch is here [2], is working, but needs some performance
improvment and double checking. Which I won't do before getting
some feedback that what I'm doing does make any sense at all ;)

Bye, Chris


[1] http://neilconway.org/talks/hacking/
[2] http://www.1006.org/tmp/psql_cursor-3.patch

On Tue, 2006-07-11 at 21:19 +0200, Chris Mair wrote:
> One of the problems with this was that a user would expect psql to
> work as usual (including all format and output option stuff) and
> to do this properly most of the psql output code would need to be
> refactored.

Even if the refactoring were done, I think having a separate interface
would be a good idea, because it makes it more obvious that queries
submitted via cursors behave differently (e.g. in the case of a network
failure in the midst of executing the query).

> The patch is here [2], is working, but needs some performance
> improvment and double checking.

BTW, the patch leaks the content of "buf" (you need to
termPQExpBuffer()).

-Neil