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.000000000 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:54:41.000000000 +0200
***************
*** 1201,1206 ****
--- 1201,1231 ----
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\u</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in <replaceable
+ class="parameter">filename</replaceable> or pipes the output
+ into a separate Unix shell executing <replaceable
+ class="parameter">command</replaceable>.
+ Unlike <literal>\g</literal>, <literal>\u</literal> works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore <literal>\u</literal> 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.
+ <literal>\u</literal> always gives unaligned output. It does, however
+ use the current field separator (see <command>\pset</command>).
+ <literal>\u</literal> gives an error when trying to execute something
+ that is not a SELECT statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
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.000000000 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 16:55:25.000000000 +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.000000000 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.000000000 +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.000000000 +0200
--- pgsql/src/bin/psql/common.c 2006-08-17 16:59:05.000000000 +0200
***************
*** 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);
/*
***************
*** 952,957 ****
--- 953,1146 ----
/*
+ * 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;
+ int ntuples, nfields = -1;
+ int i, 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"), query);
+ fflush(pset.logfile);
+ }
+
+ SetCancelConn();
+
+ /* prepare to write output to \u argument, if any */
+ if (pset.gfname)
+ {
+ queryFout_copy = pset.queryFout;
+ queryFoutPipe_copy = pset.queryFoutPipe;
+
+ pset.queryFout = stdout; /* so it doesn't get closed */
+
+ /* open file/pipe */
+ if (!setQFout(pset.gfname))
+ {
+ pset.queryFout = queryFout_copy;
+ pset.queryFoutPipe = queryFoutPipe_copy;
+ ResetCancelConn();
+ return false;
+ }
+ }
+
+ /* If we're not in a transaction, start one */
+ if (PQtransactionStatus(pset.db) == PQTRANS_IDLE)
+ {
+ results = PQexec(pset.db, "BEGIN");
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ goto error;
+
+ PQclear(results);
+ started_txn = true;
+ }
+
+ initPQExpBuffer(&buf);
+ appendPQExpBuffer(&buf,
+ "DECLARE _psql_cursor NO SCROLL CURSOR FOR %s",
+ query);
+
+ results = PQexec(pset.db, buf.data);
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ goto error;
+
+ PQclear(results);
+ termPQExpBuffer(&buf);
+
+ for (;;)
+ {
+ /* space-time trade-off: get 100 tuples at a time */
+ results = PQexec(pset.db, "FETCH FORWARD 100 FROM _psql_cursor");
+ if (PQresultStatus(results) != PGRES_TUPLES_OK)
+ goto error;
+
+ ntuples = PQntuples(results);
+ if (ntuples == 0)
+ {
+ PQclear(results);
+ break;
+ }
+
+ if (nfields == -1)
+ nfields = PQnfields(results);
+
+ for (j = 0; j < ntuples; j++)
+ {
+ for (i = 0; i < nfields; i++)
+ {
+ fprintf(
+ pset.queryFout, "%s",
+ (char*) mbvalidate((unsigned char*) PQgetvalue(results, j, i),
+ pset.popt.topt.encoding)
+ );
+ if (i != nfields - 1)
+ {
+ fprintf(pset.queryFout, "%s", pset.popt.topt.fieldSep);
+ }
+ }
+ fprintf(pset.queryFout, "\n");
+ }
+
+ PQclear(results);
+
+ if (cancel_pressed)
+ break;
+ }
+
+ /* close \u argument file/pipe, restore old setting */
+ if (pset.gfname)
+ {
+ setQFout(NULL);
+
+ pset.queryFout = queryFout_copy;
+ pset.queryFoutPipe = queryFoutPipe_copy;
+
+ free(pset.gfname);
+ pset.gfname = NULL;
+ }
+
+ results = PQexec(pset.db, "CLOSE _psql_cursor");
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ goto error;
+ PQclear(results);
+
+ if (started_txn)
+ {
+ results = PQexec(pset.db, "ROLLBACK");
+ started_txn = false;
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ goto error;
+ PQclear(results);
+ }
+
+ /* check for events that may occur during query execution */
+
+ if (pset.encoding != PQclientEncoding(pset.db) &&
+ PQclientEncoding(pset.db) >= 0)
+ {
+ /* track effects of SET CLIENT_ENCODING */
+ pset.encoding = PQclientEncoding(pset.db);
+ pset.popt.topt.encoding = pset.encoding;
+ SetVariable(pset.vars, "ENCODING",
+ pg_encoding_to_char(pset.encoding));
+ }
+
+ PrintNotifications();
+
+ return true;
+
+ error:
+ psql_error("%s", PQerrorMessage(pset.db));
+ if (results)
+ PQclear(results);
+ if (started_txn)
+ {
+ results = PQexec(pset.db, "ROLLBACK");
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ goto error;
+ PQclear(results);
+ }
+ ResetCancelConn();
+ return false;
+ }
+
+
+ /*
* Advance the given char pointer over white space and SQL comments.
*/
static const char *
***************
*** 1012,1017 ****
--- 1201,1244 ----
/*
+ * Check whether the specified command is a SELECT (or VALUES).
+ */
+ static bool
+ is_select_command(const char *query)
+ {
+ int wordlen;
+ const char *pos = 0;
+
+ /*
+ * First we must advance over any whitespace, comments and left parentheses.
+ */
+ while (pos != query)
+ {
+ pos = query;
+ query = skip_white_space(query);
+ if (query[0] == '(') {
+ query++;
+ }
+ }
+
+ /*
+ * Check word length (since "selectx" is not "select").
+ */
+ wordlen = 0;
+ while (isalpha((unsigned char) query[wordlen]))
+ wordlen += PQmblen(&query[wordlen], pset.encoding);
+
+ if (wordlen == 6 && pg_strncasecmp(query, "select", 6) == 0)
+ return true;
+
+ if (wordlen == 6 && pg_strncasecmp(query, "values", 6) == 0)
+ return true;
+
+ return false;
+ }
+
+
+ /*
* Check whether a command is one of those for which we should NOT start
* a new transaction block (ie, send a preceding BEGIN).
*
diff -rc pgsql.original/src/bin/psql/common.h pgsql/src/bin/psql/common.h
*** pgsql.original/src/bin/psql/common.h 2006-08-17 16:51:04.000000000 +0200
--- pgsql/src/bin/psql/common.h 2006-08-17 16:55:25.000000000 +0200
***************
*** 55,60 ****
--- 55,61 ----
extern PGresult *PSQLexec(const char *query, bool start_xact);
extern bool SendQuery(const char *query);
+ extern bool SendQueryUsingCursor(const char *query);
extern bool is_superuser(void);
extern bool standard_strings(void);
diff -rc pgsql.original/src/bin/psql/help.c pgsql/src/bin/psql/help.c
*** pgsql.original/src/bin/psql/help.c 2006-08-17 16:51:04.000000000 +0200
--- pgsql/src/bin/psql/help.c 2006-08-17 16:55:25.000000000 +0200
***************
*** 190,195 ****
--- 190,196 ----
fprintf(output, _("Query Buffer\n"));
fprintf(output, _(" \\e [FILE] edit the query buffer (or file) with external editor\n"));
fprintf(output, _(" \\g [FILE] send query buffer to server (and results to file or |pipe)\n"));
+ fprintf(output, _(" \\u [FILE] like \\g, but use cursor for SELECT (for large result sets)\n"));
fprintf(output, _(" \\p show the contents of the query buffer\n"));
fprintf(output, _(" \\r reset (clear) the query buffer\n"));
#ifdef USE_READLINE
diff -rc pgsql.original/src/bin/psql/mainloop.c pgsql/src/bin/psql/mainloop.c
*** pgsql.original/src/bin/psql/mainloop.c 2006-08-17 16:51:04.000000000 +0200
--- pgsql/src/bin/psql/mainloop.c 2006-08-17 16:55:25.000000000 +0200
***************
*** 264,270 ****
success = slashCmdStatus != PSQL_CMD_ERROR;
! if ((slashCmdStatus == PSQL_CMD_SEND || slashCmdStatus == PSQL_CMD_NEWEDIT) &&
query_buf->len == 0)
{
/* copy previous buffer to current for handling */
--- 264,270 ----
success = slashCmdStatus != PSQL_CMD_ERROR;
! if ((slashCmdStatus == PSQL_CMD_SEND || slashCmdStatus == PSQL_CMD_NEWEDIT || slashCmdStatus == PSQL_CMD_SEND_USING_CURSOR) &&
query_buf->len == 0)
{
/* copy previous buffer to current for handling */
***************
*** 282,287 ****
--- 282,298 ----
/* flush any paren nesting info after forced send */
psql_scan_reset(scan_state);
}
+ else if (slashCmdStatus == PSQL_CMD_SEND_USING_CURSOR)
+ {
+ success = SendQueryUsingCursor(query_buf->data);
+
+ resetPQExpBuffer(previous_buf);
+ appendPQExpBufferStr(previous_buf, query_buf->data);
+ resetPQExpBuffer(query_buf);
+
+ /* flush any paren nesting info after forced send */
+ psql_scan_reset(scan_state);
+ }
else if (slashCmdStatus == PSQL_CMD_NEWEDIT)
{
/* rescan query_buf as new input */
diff -rc pgsql.original/src/bin/psql/settings.h pgsql/src/bin/psql/settings.h
*** pgsql.original/src/bin/psql/settings.h 2006-08-17 16:51:04.000000000 +0200
--- pgsql/src/bin/psql/settings.h 2006-08-17 16:55:25.000000000 +0200
***************
*** 37,43 ****
printQueryOpt popt;
VariableSpace vars; /* "shell variable" repository */
! char *gfname; /* one-shot file output argument for \g */
bool notty; /* stdin or stdout is not a tty (as determined
* on startup) */
--- 37,43 ----
printQueryOpt popt;
VariableSpace vars; /* "shell variable" repository */
! char *gfname; /* one-shot file output argument for \g and \u */
bool notty; /* stdin or stdout is not a tty (as determined
* on startup) */
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
