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