Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
Bruce Momjian <[EMAIL PROTECTED]> writes: > Chris Mair wrote: >> Since buffer commands all have a single char I wanted a single char one >> too. The "c" for "cursor" was taken already, so i choose the "u" (second >> char in "cursor"). If somebody has a better suggestion, let us know ;) > I think a new backslash variable isn't the way to go. I would use a > \pset variable to control what is happening. That seems like it would be very awkward to use: you'd have to type quite a bit to go from one mode to the other. Personally I think that insisting on a one-letter command name is not such a good idea if you can't pick a reasonably memorable name. I'd suggest "\gc" (\g with a Cursor) or "\gb" (\g for a Big query) or some such. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] selecting large result sets in psql using cursors
Hi,
thanks for reviewing this :)
> > attached is the new and fixed version of the patch for selecting
> > large result sets from psql using cursors.
>
> The is_select_command bit is wrong because it doesn't allow for left
> parentheses in front of the SELECT keyword (something entirely
> reasonable when considering big union/intersect/except trees).
> Also you'd need to allow for VALUES as the first keyword.
You're right, I improved is_select_command to take these into account.
(Btw, I didn't even know a command VALUES existed..)
> But isn't the whole thing unnecessary? ISTM you could just ship the
> query with the DECLARE CURSOR prepended, and see whether you get a
> syntax error or not.
I find it neat that \u gives a good error message if someone
executes a non-select query. If I leave that out there is no way to tell
a real syntax error from one cause by executing non-selects...
Anyway, if we don't want the extra check, I can skip the
is_select_command call, of course.
Patch with fix against current CVS is attached.
> At some point we ought to extend libpq enough to expose the V3-protocol
> feature that allows partial fetches from portals; that would be a
> cleaner way to implement this feature. However since nobody has yet
> proposed a good API for this in libpq, I don't object to implementing
> \u with DECLARE CURSOR for now.
>
> BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
> some other name we could use?
True :)
Since buffer commands all have a single char I wanted a single char one
too. The "c" for "cursor" was taken already, so i choose the "u" (second
char in "cursor"). If somebody has a better suggestion, let us know ;)
Bye, Chris.
PS: I'm traveling Fri 18th - Fri 25th and won't check mail often.
--
Chris Mair
http://www.1006.org
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:54:41.0 +0200
***
*** 1201,1206
--- 1201,1231
+
+
+ \u [ { filename | |command } ]
+
+
+
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exeeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+
+
+
+
+
\help (or \h) [ command ]
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 16:55:25.0 +0200
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, "u") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf && query_buf->len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, "unset") == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.0 +0200
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE,/* quit program */
PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/common.c 2006-08-17 16:59:05.0 +0200
***
*** 28,33
--- 28,34
#include "command.h"
#include "copy.h"
#include "mb/pg_wchar.h"
+ #include "mbprint.h"
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
Tom Lane wrote: > BTW, \u seems not to have any mnemonic value whatsoever ... isn't > there some other name we could use? Ever since pgsql-patches replies started going to -hackers, threading doesn't work anymore, so I for one can't tell what this refers to at all. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] selecting large result sets in psql using cursors
Chris Mair <[EMAIL PROTECTED]> writes: > attached is the new and fixed version of the patch for selecting > large result sets from psql using cursors. The is_select_command bit is wrong because it doesn't allow for left parentheses in front of the SELECT keyword (something entirely reasonable when considering big union/intersect/except trees). Also you'd need to allow for VALUES as the first keyword. But isn't the whole thing unnecessary? ISTM you could just ship the query with the DECLARE CURSOR prepended, and see whether you get a syntax error or not. At some point we ought to extend libpq enough to expose the V3-protocol feature that allows partial fetches from portals; that would be a cleaner way to implement this feature. However since nobody has yet proposed a good API for this in libpq, I don't object to implementing \u with DECLARE CURSOR for now. BTW, \u seems not to have any mnemonic value whatsoever ... isn't there some other name we could use? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] selecting large result sets in psql using cursors
Hi there,
attached is the new and fixed version of the patch for selecting
large result sets from psql using cursors.
It was previously discussed on hackers:
http://archives.postgresql.org/pgsql-hackers/2006-07/msg00231.php
Thanks again to Neil Conway for helping with this (the first
sketch of the patch was his :)
Wondering if this makes a chance to get accepted...
Bye :-)
Chris.
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml Fri Jul 28 02:24:08 2006
--- pgsql/doc/src/sgml/ref/psql-ref.sgml Fri Jul 28 02:33:20 2006
***
*** 1201,1206
--- 1201,1231
+
+
+ \u [ { filename | |command } ]
+
+
+
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exeeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+
+
+
+
+
\help (or \h) [ command ]
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c Fri Jul 28 02:24:15 2006
--- pgsql/src/bin/psql/command.c Fri Jul 28 02:32:47 2006
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, "u") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf && query_buf->len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, "unset") == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h Fri Jul 28 02:24:15 2006
--- pgsql/src/bin/psql/command.h Fri Jul 28 02:32:47 2006
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE,/* quit program */
PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c Fri Jul 28 02:24:15 2006
--- pgsql/src/bin/psql/common.c Fri Jul 28 02:32:47 2006
***
*** 28,33
--- 28,34
#include "command.h"
#include "copy.h"
#include "mb/pg_wchar.h"
+ #include "mbprint.h"
/* Workarounds for Windows */
***
*** 52,58
((T)->millitm - (U)->millitm))
#endif
!
static bool command_no_begin(const char *query);
/*
--- 53,59
((T)->millitm - (U)->millitm))
#endif
! static bool is_select_command(const char *query);
static bool command_no_begin(const char *query);
/*
***
*** 934,939
--- 935,1128
/*
+ * SendQueryUsingCursor: send the (SELECT) query string to the backend
+ * using a cursor and print out results.
+ *
+ * Unlike with SendQuery(), single step mode, ON_ERROR_ROLLBACK mode,
+ * timing and format settings (except delimiters) are NOT honoured.
+ *
+ * Returns true if the query executed successfully, false otherwise.
+ */
+ bool
+ SendQueryUsingCursor(const char *query)
+ {
+ PGresult *results;
+ bool started_txn = false;
+ PQExpBufferData buf;
+ FILE *queryFout_copy = NULL;
+ bool queryFoutPipe_copy = false;
+ intntuples, nfields = -1;
+ inti, j;
+
+ if (!pset.db)
+ {
+ psql_error("You are currently not connected to a database.\n");
+ return false;
+ }
+
+ if (!is_select_command(query))
+ {
+ psql_error("Need a SELECT command to perform cursor fetch.\n");
+ return false;
+ }
+
+ if (VariableEquals(pset.vars, "ECHO", "queries"))
+ {
+ puts(query);
+ fflush(stdout);
+ }
+
+ if (pset.logfile)
+ {
+ fprintf(pset.logfile,
+ _("* QUERY **\n"
+ "%s\n"
+ "**\n\n"), quer
