Hi there,
here comes the latest version (version 7) of the patch to handle large
result sets with psql. As previously discussed, a cursor is used
for SELECT queries when \set FETCH_COUNT some_value > 0
(defaults to 100 if FETCH_COUNT is set with no value).
Comparing to the previous version, the patch actually got smaller and is
less invasive, because I doesn't have to deal with a new command and
can share some more code with SendQuery() in common.c.
Bye,
Chris.
--
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-28 16:01:15.000000000 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-28 16:04:46.000000000 +0200
***************
*** 2008,2013 ****
--- 2008,2030 ----
</varlistentry>
<varlistentry>
+ <term><varname>FETCH_COUNT</varname></term>
+ <listitem>
+ <para>
+ If this variable is set to an integer value > 0,
+ all <command>SELECT</command> or <command>VALUES</command>
+ queries are performed using a cursor. Therefore only a
+ limited amount of memory is used, regardless the size of
+ the result set. The integer value defines the fetch count.
+ It defaults to <literal>100</literal>. This variable can be
+ used whenever a result set needs to be retrieved that exceeds
+ the client's memory resources. Output is always unaligned
+ and uses the current field separator (see <command>\pset</command>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>HISTCONTROL</varname></term>
<listitem>
<para>
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c 2006-08-28 16:01:18.000000000 +0200
--- pgsql/src/bin/psql/common.c 2006-08-28 16:04:07.000000000 +0200
***************
*** 28,33 ****
--- 28,34 ----
#include "command.h"
#include "copy.h"
#include "mb/pg_wchar.h"
+ #include "mbprint.h"
/* Workarounds for Windows */
***************
*** 52,59 ****
((T)->millitm - (U)->millitm))
#endif
!
static bool command_no_begin(const char *query);
/*
* "Safe" wrapper around strdup()
--- 53,61 ----
((T)->millitm - (U)->millitm))
#endif
! static bool is_select_command(const char *query);
static bool command_no_begin(const char *query);
+ static bool SendQueryUsingCursor(const char *query);
/*
* "Safe" wrapper around strdup()
***************
*** 827,832 ****
--- 829,841 ----
SetCancelConn();
+ /* if FETCH_COUNT is set > 0 and this is a select query, use
+ * alternative query/output code for large result sets
+ */
+ if (GetVariableNum(pset.vars, "FETCH_COUNT", -1, DEFAULT_FETCH_COUNT, false) > 0 && is_select_command(query)) {
+ return SendQueryUsingCursor(query);
+ }
+
transaction_status = PQtransactionStatus(pset.db);
if (transaction_status == PQTRANS_IDLE &&
***************
*** 952,957 ****
--- 961,1131 ----
/*
+ * SendQueryUsingCursor:
+ * This is called by SendQuery for SELECT queries when FETCH_COUNT is set > 0.
+ * The query is performed using a cursor, so that large result sets exceeding
+ * the client's RAM size can be dealt with.
+ *
+ * Unlike with SendQuery(), timing and format settings (except delimiters)
+ * are NOT honoured.
+ *
+ * Returns true if the query executed successfully, false otherwise.
+ */
+ static bool
+ SendQueryUsingCursor(const char *query)
+ {
+ PGresult *results;
+ PQExpBufferData buf;
+ FILE *queryFout_copy = NULL;
+ bool queryFoutPipe_copy = false;
+ bool started_txn = false;
+ int ntuples, nfields = -1;
+ int i, j;
+ char fetch_str[64];
+
+ /* prepare to write output to \g 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);
+
+ snprintf(fetch_str, sizeof(fetch_str), "FETCH FORWARD %d FROM _psql_cursor",
+ GetVariableNum(pset.vars, "FETCH_COUNT", -1, DEFAULT_FETCH_COUNT, false));
+
+ for (;;)
+ {
+ /* get FETCH_COUNT tuples at a time */
+ results = PQexec(pset.db, fetch_str);
+ 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 \g 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, "COMMIT");
+ 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 ****
--- 1186,1229 ----
/*
+ * 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/settings.h pgsql/src/bin/psql/settings.h
*** pgsql.original/src/bin/psql/settings.h 2006-08-28 16:01:18.000000000 +0200
--- pgsql/src/bin/psql/settings.h 2006-08-28 16:04:07.000000000 +0200
***************
*** 16,21 ****
--- 16,23 ----
#define DEFAULT_FIELD_SEP "|"
#define DEFAULT_RECORD_SEP "\n"
+ #define DEFAULT_FETCH_COUNT 100
+
#if defined(WIN32) || defined(__CYGWIN__)
#define DEFAULT_EDITOR "notepad.exe"
#else
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
