On 30.09.20 08:21, Michael Paquier wrote:
On Mon, Jul 20, 2020 at 07:48:42AM +0200, Fabien COELHO wrote:
Yes, indeed. I'm planning to investigate, hopefully this week.
This reply was two months ago, and nothing has happened, so I have
marked the patch as RwF.
Given the ongoing work on returning multiple result sets from stored
procedures[0], I went to dust off this patch.
Based on the feedback, I put back the titular SHOW_ALL_RESULTS option,
but set the default to on. I fixed the test failure in
013_crash_restart.pl. I also trimmed back the test changes a bit so
that the resulting test output changes are visible better. (We could
make those stylistic changes separately in another patch.) I'll put
this back into the commitfest for another look.
[0]:
https://www.postgresql.org/message-id/flat/6e747f98-835f-2e05-cde5-86ee444a7...@2ndquadrant.com
>From 22ac191084db1b6ab155202a09bc1a6fedde794f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Sat, 27 Feb 2021 11:50:50 +0100
Subject: [PATCH v10] psql: Show all query results by default
Author: Author: Fabien COELHO <coe...@cri.ensmp.fr>
Discussion:
https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1904132231510.8961@lancre
---
.../expected/pg_stat_statements.out | 25 +
doc/src/sgml/ref/psql-ref.sgml | 29 +-
src/bin/psql/common.c | 639 ++++++++++--------
src/bin/psql/help.c | 2 +
src/bin/psql/settings.h | 1 +
src/bin/psql/startup.c | 10 +
src/bin/psql/tab-complete.c | 2 +-
src/test/regress/expected/copy2.out | 2 +-
src/test/regress/expected/copyselect.out | 14 +-
src/test/regress/expected/psql.out | 94 +++
src/test/regress/expected/transactions.out | 58 +-
src/test/regress/sql/copyselect.sql | 4 +-
src/test/regress/sql/psql.sql | 38 ++
src/test/regress/sql/transactions.sql | 2 +-
14 files changed, 609 insertions(+), 311 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out
b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 16158525ca..4ffb7e0076 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -50,8 +50,28 @@ BEGIN \;
SELECT 2.0 AS "float" \;
SELECT 'world' AS "text" \;
COMMIT;
+ float
+-------
+ 2.0
+(1 row)
+
+ text
+-------
+ world
+(1 row)
+
-- compound with empty statements and spurious leading spacing
\;\; SELECT 3 + 3 \;\;\; SELECT ' ' || ' !' \;\; SELECT 1 + 4 \;;
+ ?column?
+----------
+ 6
+(1 row)
+
+ ?column?
+----------
+ !
+(1 row)
+
?column?
----------
5
@@ -61,6 +81,11 @@ COMMIT;
SELECT 1 + 1 + 1 AS "add" \gset
SELECT :add + 1 + 1 AS "add" \;
SELECT :add + 1 + 1 AS "add" \gset
+ add
+-----
+ 5
+(1 row)
+
-- set operator
SELECT 1 AS i UNION SELECT 2 ORDER BY i;
i
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 13c1edfa4d..d14651adea 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -127,18 +127,11 @@ <title>Options</title>
commands included in the string to divide it into multiple
transactions. (See <xref linkend="protocol-flow-multi-statement"/>
for more details about how the server handles multi-query strings.)
- Also, <application>psql</application> only prints the
- result of the last <acronym>SQL</acronym> command in the string.
- This is different from the behavior when the same string is read from
- a file or fed to <application>psql</application>'s standard input,
- because then <application>psql</application> sends
- each <acronym>SQL</acronym> command separately.
</para>
<para>
- Because of this behavior, putting more than one SQL command in a
- single <option>-c</option> string often has unexpected results.
- It's better to use repeated <option>-c</option> commands or feed
- multiple commands to <application>psql</application>'s standard input,
+ If having several commands executed in one transaction is not desired,
+ use repeated <option>-c</option> commands or feed multiple commands to
+ <application>psql</application>'s standard input,
either using <application>echo</application> as illustrated above, or
via a shell here-document, for example:
<programlisting>
@@ -3523,10 +3516,6 @@ <title>Meta-Commands</title>
commands included in the string to divide it into multiple
transactions. (See <xref linkend="protocol-flow-multi-statement"/>
for more details about how the server handles multi-query strings.)
- <application>psql</application> prints only the last query result
- it receives for each request; in this example, although all
- three <command>SELECT</command>s are indeed executed,
<application>psql</application>
- only prints the <literal>3</literal>.
</para>
</listitem>
</varlistentry>
@@ -4102,6 +4091,18 @@ <title>Variables</title>
</varlistentry>
<varlistentry>
+ <term><varname>SHOW_ALL_RESULTS</varname></term>
+ <listitem>
+ <para>
+ When this variable is set to <literal>off</literal>, only the last
+ result of a combined (<literal>\;</literal>) query are shown instead
+ of all of them. Default is <literal>on</literal>. The off behavior
+ is for compatibility with older versions of psql.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>SHOW_CONTEXT</varname></term>
<listitem>
<para>
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 925fe34a3f..19eb345028 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -33,6 +33,7 @@ static bool DescribeQuery(const char *query, double
*elapsed_msec);
static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
+static int SendQueryAndProcessResults(const char *query, double
*pelapsed_msec, bool is_watch);
/*
@@ -342,6 +343,19 @@ CheckConnection(void)
}
+/*
+ * Show error message from result, if any, and check connection in passing.
+ */
+static void
+ShowErrorMessage(const PGresult *result)
+{
+ const char *error = PQerrorMessage(pset.db);
+
+ if (strlen(error))
+ pg_log_info("%s", error);
+
+ CheckConnection();
+}
/*
@@ -353,7 +367,7 @@ CheckConnection(void)
* Returns true for valid result, false for error state.
*/
static bool
-AcceptResult(const PGresult *result)
+AcceptResult(const PGresult *result, bool show_error)
{
bool OK;
@@ -384,15 +398,8 @@ AcceptResult(const PGresult *result)
break;
}
- if (!OK)
- {
- const char *error = PQerrorMessage(pset.db);
-
- if (strlen(error))
- pg_log_info("%s", error);
-
- CheckConnection();
- }
+ if (!OK && show_error)
+ ShowErrorMessage(result);
return OK;
}
@@ -472,6 +479,16 @@ ClearOrSaveResult(PGresult *result)
}
}
+/* consume all results */
+static void
+ClearOrSaveAllResults()
+{
+ PGresult *result;
+
+ while ((result = PQgetResult(pset.db)) != NULL)
+ ClearOrSaveResult(result);
+}
+
/*
* Print microtiming output. Always print raw milliseconds; if the interval
@@ -572,7 +589,7 @@ PSQLexec(const char *query)
ResetCancelConn();
- if (!AcceptResult(res))
+ if (!AcceptResult(res, true))
{
ClearOrSaveResult(res);
res = NULL;
@@ -594,10 +611,8 @@ PSQLexec(const char *query)
int
PSQLexecWatch(const char *query, const printQueryOpt *opt)
{
- PGresult *res;
double elapsed_msec = 0;
- instr_time before;
- instr_time after;
+ int res;
if (!pset.db)
{
@@ -606,75 +621,16 @@ PSQLexecWatch(const char *query, const printQueryOpt *opt)
}
SetCancelConn(pset.db);
-
- if (pset.timing)
- INSTR_TIME_SET_CURRENT(before);
-
- res = PQexec(pset.db, query);
-
+ res = SendQueryAndProcessResults(query, &elapsed_msec, true);
ResetCancelConn();
- if (!AcceptResult(res))
- {
- ClearOrSaveResult(res);
- return 0;
- }
-
- if (pset.timing)
- {
- INSTR_TIME_SET_CURRENT(after);
- INSTR_TIME_SUBTRACT(after, before);
- elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
- }
-
- /*
- * If SIGINT is sent while the query is processing, the interrupt will
be
- * consumed. The user's intention, though, is to cancel the entire
watch
- * process, so detect a sent cancellation request and exit in this case.
- */
- if (cancel_pressed)
- {
- PQclear(res);
- return 0;
- }
-
- switch (PQresultStatus(res))
- {
- case PGRES_TUPLES_OK:
- printQuery(res, opt, pset.queryFout, false,
pset.logfile);
- break;
-
- case PGRES_COMMAND_OK:
- fprintf(pset.queryFout, "%s\n%s\n\n", opt->title,
PQcmdStatus(res));
- break;
-
- case PGRES_EMPTY_QUERY:
- pg_log_error("\\watch cannot be used with an empty
query");
- PQclear(res);
- return -1;
-
- case PGRES_COPY_OUT:
- case PGRES_COPY_IN:
- case PGRES_COPY_BOTH:
- pg_log_error("\\watch cannot be used with COPY");
- PQclear(res);
- return -1;
-
- default:
- pg_log_error("unexpected result status for \\watch");
- PQclear(res);
- return -1;
- }
-
- PQclear(res);
-
fflush(pset.queryFout);
/* Possible microtiming output */
if (pset.timing)
PrintTiming(elapsed_msec);
- return 1;
+ return res;
}
@@ -885,199 +841,115 @@ ExecQueryTuples(const PGresult *result)
return success;
}
-
/*
- * ProcessResult: utility function for use by SendQuery() only
- *
- * When our command string contained a COPY FROM STDIN or COPY TO STDOUT,
- * PQexec() has stopped at the PGresult associated with the first such
- * command. In that event, we'll marshal data for the COPY and then cycle
- * through any subsequent PGresult objects.
- *
- * When the command string contained no such COPY command, this function
- * degenerates to an AcceptResult() call.
+ * Marshal the COPY data. Either subroutine will get the
+ * connection out of its COPY state, then call PQresultStatus()
+ * once and report any error. Return whether all was ok.
*
- * Changes its argument to point to the last PGresult of the command string,
- * or NULL if that result was for a COPY TO STDOUT. (Returning NULL prevents
- * the command status from being printed, which we want in that case so that
- * the status line doesn't get taken as part of the COPY data.)
+ * For COPY OUT, direct the output to pset.copyStream if it's set,
+ * otherwise to pset.gfname if it's set, otherwise to queryFout.
+ * For COPY IN, use pset.copyStream as data source if it's set,
+ * otherwise cur_cmd_source.
*
- * Returns true on complete success, false otherwise. Possible failure modes
- * include purely client-side problems; check the transaction status for the
- * server-side opinion.
+ * Update result if further processing is necessary, or NULL otherwise.
+ * Return a result when queryFout can safely output a result status:
+ * on COPY IN, or on COPY OUT if written to something other han pset.queryFout.
+ * Returning NULL prevents the command status from being printed, which
+ * we want if the status line doesn't get taken as part of the COPY data.
*/
static bool
-ProcessResult(PGresult **results)
+HandleCopyResult(PGresult **result)
{
- bool success = true;
- bool first_cycle = true;
+ bool success;
+ FILE *copystream;
+ PGresult *copy_result;
+ ExecStatusType result_status = PQresultStatus(*result);
- for (;;)
+ Assert(result_status == PGRES_COPY_OUT ||
+ result_status == PGRES_COPY_IN);
+
+ SetCancelConn(pset.db);
+
+ if (result_status == PGRES_COPY_OUT)
{
- ExecStatusType result_status;
- bool is_copy;
- PGresult *next_result;
+ bool need_close = false;
+ bool is_pipe = false;
- if (!AcceptResult(*results))
+ if (pset.copyStream)
{
- /*
- * Failure at this point is always a server-side
failure or a
- * failure to submit the command string. Either way,
we're
- * finished with this command string.
- */
- success = false;
- break;
+ /* invoked by \copy */
+ copystream = pset.copyStream;
}
-
- result_status = PQresultStatus(*results);
- switch (result_status)
+ else if (pset.gfname)
{
- case PGRES_EMPTY_QUERY:
- case PGRES_COMMAND_OK:
- case PGRES_TUPLES_OK:
- is_copy = false;
- break;
+ /* invoked by \g */
+ if (openQueryOutputFile(pset.gfname,
+
©stream, &is_pipe))
+ {
+ need_close = true;
+ if (is_pipe)
+ disable_sigpipe_trap();
+ }
+ else
+ copystream = NULL; /* discard COPY data
entirely */
+ }
+ else
+ {
+ /* fall back to the generic query output stream */
+ copystream = pset.queryFout;
+ }
- case PGRES_COPY_OUT:
- case PGRES_COPY_IN:
- is_copy = true;
- break;
+ success = handleCopyOut(pset.db,
+ copystream,
+ ©_result)
+ && (copystream != NULL);
- default:
- /* AcceptResult() should have caught anything
else. */
- is_copy = false;
- pg_log_error("unexpected PQresultStatus: %d",
result_status);
- break;
+ /*
+ * Suppress status printing if the report would go to the same
+ * place as the COPY data just went. Note this doesn't
+ * prevent error reporting, since handleCopyOut did that.
+ */
+ if (copystream == pset.queryFout)
+ {
+ PQclear(copy_result);
+ copy_result = NULL;
}
- if (is_copy)
+ if (need_close)
{
- /*
- * Marshal the COPY data. Either subroutine will get
the
- * connection out of its COPY state, then call
PQresultStatus()
- * once and report any error.
- *
- * For COPY OUT, direct the output to pset.copyStream
if it's set,
- * otherwise to pset.gfname if it's set, otherwise to
queryFout.
- * For COPY IN, use pset.copyStream as data source if
it's set,
- * otherwise cur_cmd_source.
- */
- FILE *copystream;
- PGresult *copy_result;
-
- SetCancelConn(pset.db);
- if (result_status == PGRES_COPY_OUT)
+ /* close \g argument file/pipe */
+ if (is_pipe)
{
- bool need_close = false;
- bool is_pipe = false;
-
- if (pset.copyStream)
- {
- /* invoked by \copy */
- copystream = pset.copyStream;
- }
- else if (pset.gfname)
- {
- /* invoked by \g */
- if (openQueryOutputFile(pset.gfname,
-
©stream, &is_pipe))
- {
- need_close = true;
- if (is_pipe)
- disable_sigpipe_trap();
- }
- else
- copystream = NULL; /*
discard COPY data entirely */
- }
- else
- {
- /* fall back to the generic query
output stream */
- copystream = pset.queryFout;
- }
-
- success = handleCopyOut(pset.db,
-
copystream,
-
©_result)
- && success
- && (copystream != NULL);
-
- /*
- * Suppress status printing if the report would
go to the same
- * place as the COPY data just went. Note this
doesn't
- * prevent error reporting, since handleCopyOut
did that.
- */
- if (copystream == pset.queryFout)
- {
- PQclear(copy_result);
- copy_result = NULL;
- }
-
- if (need_close)
- {
- /* close \g argument file/pipe */
- if (is_pipe)
- {
- pclose(copystream);
- restore_sigpipe_trap();
- }
- else
- {
- fclose(copystream);
- }
- }
+ pclose(copystream);
+ restore_sigpipe_trap();
}
else
{
- /* COPY IN */
- copystream = pset.copyStream ? pset.copyStream
: pset.cur_cmd_source;
- success = handleCopyIn(pset.db,
-
copystream,
-
PQbinaryTuples(*results),
-
©_result) && success;
+ fclose(copystream);
}
- ResetCancelConn();
-
- /*
- * Replace the PGRES_COPY_OUT/IN result with COPY
command's exit
- * status, or with NULL if we want to suppress printing
anything.
- */
- PQclear(*results);
- *results = copy_result;
- }
- else if (first_cycle)
- {
- /* fast path: no COPY commands; PQexec visited all
results */
- break;
}
-
- /*
- * Check PQgetResult() again. In the typical case of a
single-command
- * string, it will return NULL. Otherwise, we'll have other
results
- * to process that may include other COPYs. We keep the last
result.
- */
- next_result = PQgetResult(pset.db);
- if (!next_result)
- break;
-
- PQclear(*results);
- *results = next_result;
- first_cycle = false;
+ }
+ else
+ {
+ /* COPY IN */
+ copystream = pset.copyStream ? pset.copyStream :
pset.cur_cmd_source;
+ success = handleCopyIn(pset.db,
+ copystream,
+
PQbinaryTuples(*result),
+ ©_result);
}
- SetResultVariables(*results, success);
-
- /* may need this to recover from conn loss during COPY */
- if (!first_cycle && !CheckConnection())
- return false;
+ ResetCancelConn();
+ PQclear(*result);
+ *result = copy_result;
return success;
}
-
/*
* PrintQueryStatus: report command status as required
*
- * Note: Utility function for use by PrintQueryResults() only.
+ * Note: Utility function for use by HandleQueryResult() only.
*/
static void
PrintQueryStatus(PGresult *results)
@@ -1105,43 +977,50 @@ PrintQueryStatus(PGresult *results)
/*
- * PrintQueryResults: print out (or store or execute) query results as required
- *
- * Note: Utility function for use by SendQuery() only.
+ * HandleQueryResult: print out, store or execute one query result
+ * as required.
*
* Returns true if the query executed successfully, false otherwise.
*/
static bool
-PrintQueryResults(PGresult *results)
+HandleQueryResult(PGresult *result, bool last)
{
bool success;
const char *cmdstatus;
- if (!results)
+ if (result == NULL)
return false;
- switch (PQresultStatus(results))
+ switch (PQresultStatus(result))
{
case PGRES_TUPLES_OK:
/* store or execute or print the data ... */
- if (pset.gset_prefix)
- success = StoreQueryTuple(results);
- else if (pset.gexec_flag)
- success = ExecQueryTuples(results);
- else if (pset.crosstab_flag)
- success = PrintResultsInCrosstab(results);
+ if (last && pset.gset_prefix)
+ success = StoreQueryTuple(result);
+ else if (last && pset.gexec_flag)
+ success = ExecQueryTuples(result);
+ else if (last && pset.crosstab_flag)
+ success = PrintResultsInCrosstab(result);
+ else if (last || pset.show_all_results)
+ success = PrintQueryTuples(result);
else
- success = PrintQueryTuples(results);
+ success = true;
+
/* if it's INSERT/UPDATE/DELETE RETURNING, also print
status */
- cmdstatus = PQcmdStatus(results);
- if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
- strncmp(cmdstatus, "UPDATE", 6) == 0 ||
- strncmp(cmdstatus, "DELETE", 6) == 0)
- PrintQueryStatus(results);
+ if (last || pset.show_all_results)
+ {
+ cmdstatus = PQcmdStatus(result);
+ if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
+ strncmp(cmdstatus, "UPDATE", 6) == 0 ||
+ strncmp(cmdstatus, "DELETE", 6) == 0)
+ PrintQueryStatus(result);
+ }
+
break;
case PGRES_COMMAND_OK:
- PrintQueryStatus(results);
+ if (last || pset.show_all_results)
+ PrintQueryStatus(result);
success = true;
break;
@@ -1151,7 +1030,7 @@ PrintQueryResults(PGresult *results)
case PGRES_COPY_OUT:
case PGRES_COPY_IN:
- /* nothing to do here */
+ /* nothing to do here: already processed */
success = true;
break;
@@ -1164,7 +1043,7 @@ PrintQueryResults(PGresult *results)
default:
success = false;
pg_log_error("unexpected PQresultStatus: %d",
- PQresultStatus(results));
+ PQresultStatus(result));
break;
}
@@ -1173,6 +1052,208 @@ PrintQueryResults(PGresult *results)
return success;
}
+/*
+ * Data structure and functions to record notices while they are
+ * emitted, so that they can be shown later.
+ *
+ * We need to know which result is last, which requires to extract
+ * one result in advance, hence two buffers are needed.
+ */
+typedef struct {
+ bool in_flip;
+ PQExpBufferData flip;
+ PQExpBufferData flop;
+} t_notice_messages;
+
+/*
+ * Store notices in appropriate buffer, for later display.
+ */
+static void
+AppendNoticeMessage(void *arg, const char *msg)
+{
+ t_notice_messages *notes = (t_notice_messages*) arg;
+ appendPQExpBufferStr(notes->in_flip ? ¬es->flip : ¬es->flop, msg);
+}
+
+/*
+ * Show notices stored in buffer, which is then reset.
+ */
+static void
+ShowNoticeMessage(t_notice_messages *notes)
+{
+ PQExpBufferData *current = notes->in_flip ? ¬es->flip : ¬es->flop;
+ if (current->data != NULL && *current->data != '\0')
+ pg_log_info("%s", current->data);
+ resetPQExpBuffer(current);
+}
+
+/*
+ * SendQueryAndProcessResults: utility function for use by SendQuery()
+ * and PSQLexecWatch().
+ *
+ * Sends query and cycles through PGresult objects.
+ *
+ * When not under \watch and if our command string contained a COPY FROM STDIN
+ * or COPY TO STDOUT, the PGresult associated with these commands must be
+ * processed by providing an input or output stream. In that event, we'll
+ * marshal data for the COPY.
+ *
+ * For other commands, the results are processed normally, depending on their
+ * status.
+ *
+ * Returns 1 on complete success, 0 on interrupt and -1 or errors. Possible
+ * failure modes include purely client-side problems; check the transaction
+ * status for the server-side opinion.
+ *
+ * Note that on a combined query, failure does not mean that nothing was
+ * committed.
+ */
+static int
+SendQueryAndProcessResults(const char *query, double *pelapsed_msec, bool
is_watch)
+{
+ bool success;
+ instr_time before;
+ PGresult *result;
+ t_notice_messages notes;
+
+ if (pset.timing)
+ INSTR_TIME_SET_CURRENT(before);
+
+ success = PQsendQuery(pset.db, query);
+ ResetCancelConn();
+
+ if (!success)
+ {
+ const char *error = PQerrorMessage(pset.db);
+
+ if (strlen(error))
+ pg_log_info("%s", error);
+
+ CheckConnection();
+
+ return -1;
+ }
+
+ /*
+ * If SIGINT is sent while the query is processing, the interrupt will
be
+ * consumed. The user's intention, though, is to cancel the entire
watch
+ * process, so detect a sent cancellation request and exit in this case.
+ */
+ if (is_watch && cancel_pressed)
+ {
+ ClearOrSaveAllResults();
+ return 0;
+ }
+
+ /* intercept notices */
+ notes.in_flip = true;
+ initPQExpBuffer(¬es.flip);
+ initPQExpBuffer(¬es.flop);
+ PQsetNoticeProcessor(pset.db, AppendNoticeMessage, ¬es);
+
+ /* first result */
+ result = PQgetResult(pset.db);
+
+ while (result != NULL)
+ {
+ ExecStatusType result_status;
+ PGresult *next_result;
+ bool last;
+
+ if (!AcceptResult(result, false))
+ {
+ /*
+ * Some error occured, either a server-side failure or
+ * a failure to submit the command string. Record that.
+ */
+ ShowNoticeMessage(¬es);
+ ShowErrorMessage(result);
+ if (!is_watch)
+ SetResultVariables(result, false);
+ ClearOrSaveResult(result);
+ success = false;
+
+ /* and switch to next result */
+ result = PQgetResult(pset.db);
+ continue;
+ }
+
+ /* must handle COPY before changing the current result */
+ result_status = PQresultStatus(result);
+ Assert(result_status != PGRES_COPY_BOTH);
+ if (result_status == PGRES_COPY_IN ||
+ result_status == PGRES_COPY_OUT)
+ {
+ ShowNoticeMessage(¬es);
+
+ if (is_watch)
+ {
+ ClearOrSaveAllResults();
+ pg_log_error("\\watch cannot be used with
COPY");
+ return -1;
+ }
+
+ success &= HandleCopyResult(&result);
+ }
+
+ /*
+ * Check PQgetResult() again. In the typical case of a
single-command
+ * string, it will return NULL. Otherwise, we'll have other
results
+ * to process.
+ */
+ notes.in_flip = !notes.in_flip;
+ next_result = PQgetResult(pset.db);
+ notes.in_flip = !notes.in_flip;
+ last = (next_result == NULL);
+
+ /*
+ * Get timing measure before printing the last result.
+ *
+ * It will include the display of previous results, if any.
+ * This cannot be helped because the server goes on processing
+ * further queries anyway while the previous ones are being
displayed.
+ * The parallel execution of the client display hides the
server time
+ * when it is shorter.
+ *
+ * With combined queries, timing must be understood as an upper
bound
+ * of the time spent processing them.
+ */
+ if (last && pset.timing)
+ {
+ instr_time now;
+ INSTR_TIME_SET_CURRENT(now);
+ INSTR_TIME_SUBTRACT(now, before);
+ *pelapsed_msec = INSTR_TIME_GET_MILLISEC(now);
+ }
+
+ /* notices already shown above for copy */
+ ShowNoticeMessage(¬es);
+
+ /* this may or may not print something depending on settings */
+ if (result != NULL)
+ success &= HandleQueryResult(result, last);
+
+ /* set variables on last result if all went well */
+ if (!is_watch && last && success)
+ SetResultVariables(result, true);
+
+ ClearOrSaveResult(result);
+ notes.in_flip = !notes.in_flip;
+ result = next_result;
+ }
+
+ /* reset notice hook */
+ PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
+ termPQExpBuffer(¬es.flip);
+ termPQExpBuffer(¬es.flop);
+
+ /* may need this to recover from conn loss during COPY */
+ if (!CheckConnection())
+ return -1;
+
+ return success ? 1 : -1;
+}
+
/*
* SendQuery: send the query string to the backend
@@ -1189,9 +1270,9 @@ PrintQueryResults(PGresult *results)
bool
SendQuery(const char *query)
{
- PGresult *results;
+ PGresult *results = NULL;
PGTransactionStatusType transaction_status;
- double elapsed_msec = 0;
+ double elapsed_msec = 0.0;
bool OK = false;
int i;
bool on_error_rollback_savepoint = false;
@@ -1294,28 +1375,8 @@ SendQuery(const char *query)
pset.crosstab_flag || !is_select_command(query))
{
/* Default fetch-it-all-and-print mode */
- instr_time before,
- after;
-
- if (pset.timing)
- INSTR_TIME_SET_CURRENT(before);
-
- results = PQexec(pset.db, query);
-
- /* these operations are included in the timing result: */
- ResetCancelConn();
- OK = ProcessResult(&results);
-
- if (pset.timing)
- {
- INSTR_TIME_SET_CURRENT(after);
- INSTR_TIME_SUBTRACT(after, before);
- elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
- }
-
- /* but printing results isn't: */
- if (OK && results)
- OK = PrintQueryResults(results);
+ int res = SendQueryAndProcessResults(query, &elapsed_msec,
false);
+ OK = (res >= 0);
}
else
{
@@ -1497,7 +1558,7 @@ DescribeQuery(const char *query, double *elapsed_msec)
PQclear(results);
results = PQdescribePrepared(pset.db, "");
- OK = AcceptResult(results) &&
+ OK = AcceptResult(results, true) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
if (OK && results)
{
@@ -1545,7 +1606,7 @@ DescribeQuery(const char *query, double *elapsed_msec)
PQclear(results);
results = PQexec(pset.db, buf.data);
- OK = AcceptResult(results);
+ OK = AcceptResult(results, true);
if (pset.timing)
{
@@ -1555,7 +1616,7 @@ DescribeQuery(const char *query, double *elapsed_msec)
}
if (OK && results)
- OK = PrintQueryResults(results);
+ OK = HandleQueryResult(results, true);
termPQExpBuffer(&buf);
}
@@ -1614,7 +1675,7 @@ ExecQueryUsingCursor(const char *query, double
*elapsed_msec)
if (PQtransactionStatus(pset.db) == PQTRANS_IDLE)
{
results = PQexec(pset.db, "BEGIN");
- OK = AcceptResult(results) &&
+ OK = AcceptResult(results, true) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
ClearOrSaveResult(results);
if (!OK)
@@ -1628,7 +1689,7 @@ ExecQueryUsingCursor(const char *query, double
*elapsed_msec)
query);
results = PQexec(pset.db, buf.data);
- OK = AcceptResult(results) &&
+ OK = AcceptResult(results, true) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
if (!OK)
SetResultVariables(results, OK);
@@ -1701,7 +1762,7 @@ ExecQueryUsingCursor(const char *query, double
*elapsed_msec)
is_pager = false;
}
- OK = AcceptResult(results);
+ OK = AcceptResult(results, true);
Assert(!OK);
SetResultVariables(results, OK);
ClearOrSaveResult(results);
@@ -1810,7 +1871,7 @@ ExecQueryUsingCursor(const char *query, double
*elapsed_msec)
results = PQexec(pset.db, "CLOSE _psql_cursor");
if (OK)
{
- OK = AcceptResult(results) &&
+ OK = AcceptResult(results, true) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
ClearOrSaveResult(results);
}
@@ -1820,7 +1881,7 @@ ExecQueryUsingCursor(const char *query, double
*elapsed_msec)
if (started_txn)
{
results = PQexec(pset.db, OK ? "COMMIT" : "ROLLBACK");
- OK &= AcceptResult(results) &&
+ OK &= AcceptResult(results, true) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
ClearOrSaveResult(results);
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index e44120bf76..38e135cb45 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -411,6 +411,8 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" SERVER_VERSION_NAME\n"
" SERVER_VERSION_NUM\n"
" server's version (in short
string or numeric format)\n"));
+ fprintf(output, _(" SHOW_ALL_RESULTS\n"
+ " show all results of a combined
query (\\;) instead of only the last\n"));
fprintf(output, _(" SHOW_CONTEXT\n"
" controls display of message
context fields [never, errors, always]\n"));
fprintf(output, _(" SINGLELINE\n"
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index d65990059d..a5c6c1050c 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -147,6 +147,7 @@ typedef struct _psqlSettings
const char *prompt2;
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
+ bool show_all_results;
PGContextVisibility show_context; /* current context display
level */
} PsqlSettings;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 780479c8d7..98d269926c 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -196,6 +196,7 @@ main(int argc, char *argv[])
SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
+ SetVariableBool(pset.vars, "SHOW_ALL_RESULTS");
parse_psql_options(argc, argv, &options);
@@ -1130,6 +1131,12 @@ verbosity_hook(const char *newval)
return true;
}
+static bool
+show_all_results_hook(const char *newval)
+{
+ return ParseVariableBool(newval, "SHOW_ALL_RESULTS",
&pset.show_all_results);
+}
+
static char *
show_context_substitute_hook(char *newval)
{
@@ -1224,6 +1231,9 @@ EstablishVariableSpace(void)
SetVariableHooks(pset.vars, "VERBOSITY",
verbosity_substitute_hook,
verbosity_hook);
+ SetVariableHooks(pset.vars, "SHOW_ALL_RESULTS",
+ bool_substitute_hook,
+ show_all_results_hook);
SetVariableHooks(pset.vars, "SHOW_CONTEXT",
show_context_substitute_hook,
show_context_hook);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9f0208ac49..cdcee25821 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4120,7 +4120,7 @@ psql_completion(const char *text, int start, int end)
matches = complete_from_variables(text, "", "", false);
else if (TailMatchesCS("\\set", MatchAny))
{
- if (TailMatchesCS("AUTOCOMMIT|ON_ERROR_STOP|QUIET|"
+ if
(TailMatchesCS("AUTOCOMMIT|ON_ERROR_STOP|QUIET|SHOW_ALL_RESULTS|"
"SINGLELINE|SINGLESTEP"))
COMPLETE_WITH_CS("on", "off");
else if (TailMatchesCS("COMP_KEYWORD_CASE"))
diff --git a/src/test/regress/expected/copy2.out
b/src/test/regress/expected/copy2.out
index c64f0719e7..2703f83aa2 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -522,10 +522,10 @@ copy check_con_tbl from stdin;
NOTICE: input = {"f1":1}
NOTICE: input = {"f1":null}
copy check_con_tbl from stdin;
-NOTICE: input = {"f1":0}
ERROR: new row for relation "check_con_tbl" violates check constraint
"check_con_tbl_check"
DETAIL: Failing row contains (0).
CONTEXT: COPY check_con_tbl, line 1: "0"
+NOTICE: input = {"f1":0}
select * from check_con_tbl;
f1
----
diff --git a/src/test/regress/expected/copyselect.out
b/src/test/regress/expected/copyselect.out
index 72865fe1eb..bb9e026f91 100644
--- a/src/test/regress/expected/copyselect.out
+++ b/src/test/regress/expected/copyselect.out
@@ -126,7 +126,7 @@ copy (select 1) to stdout\; select 1/0; -- row, then
error
ERROR: division by zero
select 1/0\; copy (select 1) to stdout; -- error only
ERROR: division by zero
-copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3;
-- 1 2 3
+copy (select 1) to stdout\; copy (select 2) to stdout\; select 3\; select 4;
-- 1 2 3 4
1
2
?column?
@@ -134,8 +134,18 @@ copy (select 1) to stdout\; copy (select 2) to stdout\;
select 0\; select 3; --
3
(1 row)
+ ?column?
+----------
+ 4
+(1 row)
+
create table test3 (c int);
-select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
+select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 0 1
+ ?column?
+----------
+ 0
+(1 row)
+
?column?
----------
1
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 7204fdb0b4..0ce59753e1 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5076,3 +5076,97 @@ List of access methods
hash | uuid_ops | uuid | uuid |
2 | uuid_hash_extended
(5 rows)
+--
+-- combined queries
+--
+\echo '# combined queries tests'
+# combined queries tests
+CREATE FUNCTION warn(msg TEXT) RETURNS BOOLEAN AS $$
+ BEGIN RAISE NOTICE 'warn %', msg ; RETURN TRUE ; END
+$$ LANGUAGE plpgsql;
+-- show both
+SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two ;
+ one
+-----
+ 1
+(1 row)
+
+NOTICE: warn 1.5
+CONTEXT: PL/pgSQL function warn(text) line 2 at RAISE
+ warn
+------
+ t
+(1 row)
+
+ two
+-----
+ 2
+(1 row)
+
+-- \gset applies to last query only
+SELECT 3 AS three \; SELECT warn('3.5') \; SELECT 4 AS four \gset
+ three
+-------
+ 3
+(1 row)
+
+NOTICE: warn 3.5
+CONTEXT: PL/pgSQL function warn(text) line 2 at RAISE
+ warn
+------
+ t
+(1 row)
+
+\echo :three :four
+:three 4
+-- syntax error stops all processing
+SELECT 5 \; SELECT 6 + \; SELECT warn('6.5') \; SELECT 7 ;
+ERROR: syntax error at or near ";"
+LINE 1: SELECT 5 ; SELECT 6 + ; SELECT warn('6.5') ; SELECT 7 ;
+ ^
+-- with aborted transaction, stop on first error
+BEGIN \; SELECT 8 AS eight \; SELECT 9/0 AS nine \; ROLLBACK \; SELECT 10 AS
ten ;
+ eight
+-------
+ 8
+(1 row)
+
+ERROR: division by zero
+-- close previously aborted transaction
+ROLLBACK;
+-- misc SQL commands
+-- (non SELECT output is sent to stderr, thus is not shown in expected results)
+SELECT 'ok' AS "begin" \;
+CREATE TABLE psql_comics(s TEXT) \;
+INSERT INTO psql_comics VALUES ('Calvin'), ('hobbes') \;
+COPY psql_comics FROM STDIN \;
+UPDATE psql_comics SET s = 'Hobbes' WHERE s = 'hobbes' \;
+DELETE FROM psql_comics WHERE s = 'Moe' \;
+COPY psql_comics TO STDOUT \;
+TRUNCATE psql_comics \;
+DROP TABLE psql_comics \;
+SELECT 'ok' AS "done" ;
+ begin
+-------
+ ok
+(1 row)
+
+Calvin
+Susie
+Hobbes
+ done
+------
+ ok
+(1 row)
+
+\set SHOW_ALL_RESULTS off
+SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two ;
+NOTICE: warn 1.5
+CONTEXT: PL/pgSQL function warn(text) line 2 at RAISE
+ two
+-----
+ 2
+(1 row)
+
+\set SHOW_ALL_RESULTS on
+DROP FUNCTION warn(TEXT);
diff --git a/src/test/regress/expected/transactions.out
b/src/test/regress/expected/transactions.out
index 75dc6558d8..6221b4bf91 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -900,8 +900,18 @@ DROP TABLE abc;
-- tests rely on the fact that psql will not break SQL commands apart at a
-- backslash-quoted semicolon, but will send them as one Query.
create temp table i_table (f1 int);
--- psql will show only the last result in a multi-statement Query
+-- psql will show all results of a multi-statement Query
SELECT 1\; SELECT 2\; SELECT 3;
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
?column?
----------
3
@@ -916,6 +926,12 @@ insert into i_table values(1)\; select * from i_table;
-- 1/0 error will cause rolling back the whole implicit transaction
insert into i_table values(2)\; select * from i_table\; select 1/0;
+ f1
+----
+ 1
+ 2
+(2 rows)
+
ERROR: division by zero
select * from i_table;
f1
@@ -935,8 +951,18 @@ WARNING: there is no transaction in progress
-- begin converts implicit transaction into a regular one that
-- can extend past the end of the Query
select 1\; begin\; insert into i_table values(5);
+ ?column?
+----------
+ 1
+(1 row)
+
commit;
select 1\; begin\; insert into i_table values(6);
+ ?column?
+----------
+ 1
+(1 row)
+
rollback;
-- commit in implicit-transaction state commits but issues a warning.
insert into i_table values(7)\; commit\; insert into i_table values(8)\;
select 1/0;
@@ -963,22 +989,52 @@ rollback; -- we are not in a transaction at this point
WARNING: there is no transaction in progress
-- implicit transaction block is still a transaction block, for e.g. VACUUM
SELECT 1\; VACUUM;
+ ?column?
+----------
+ 1
+(1 row)
+
ERROR: VACUUM cannot run inside a transaction block
SELECT 1\; COMMIT\; VACUUM;
WARNING: there is no transaction in progress
+ ?column?
+----------
+ 1
+(1 row)
+
ERROR: VACUUM cannot run inside a transaction block
-- we disallow savepoint-related commands in implicit-transaction state
SELECT 1\; SAVEPOINT sp;
+ ?column?
+----------
+ 1
+(1 row)
+
ERROR: SAVEPOINT can only be used in transaction blocks
SELECT 1\; COMMIT\; SAVEPOINT sp;
WARNING: there is no transaction in progress
+ ?column?
+----------
+ 1
+(1 row)
+
ERROR: SAVEPOINT can only be used in transaction blocks
ROLLBACK TO SAVEPOINT sp\; SELECT 2;
ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
+ ?column?
+----------
+ 2
+(1 row)
+
ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
-- but this is OK, because the BEGIN converts it to a regular xact
SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
+ ?column?
+----------
+ 1
+(1 row)
+
-- Tests for AND CHAIN in implicit transaction blocks
SET TRANSACTION READ ONLY\; COMMIT AND CHAIN; -- error
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
diff --git a/src/test/regress/sql/copyselect.sql
b/src/test/regress/sql/copyselect.sql
index 1d98dad3c8..e32a4f8e38 100644
--- a/src/test/regress/sql/copyselect.sql
+++ b/src/test/regress/sql/copyselect.sql
@@ -84,10 +84,10 @@
-- psql handling of COPY in multi-command strings
copy (select 1) to stdout\; select 1/0; -- row, then error
select 1/0\; copy (select 1) to stdout; -- error only
-copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3;
-- 1 2 3
+copy (select 1) to stdout\; copy (select 2) to stdout\; select 3\; select 4;
-- 1 2 3 4
create table test3 (c int);
-select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
+select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 0 1
1
\.
2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 537d5332aa..ea76254f46 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1228,3 +1228,41 @@ CREATE MATERIALIZED VIEW mat_view_heap_psql USING
heap_psql AS SELECT f1 from tb
\dAo * pg_catalog.jsonb_path_ops
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
+
+--
+-- combined queries
+--
+\echo '# combined queries tests'
+CREATE FUNCTION warn(msg TEXT) RETURNS BOOLEAN AS $$
+ BEGIN RAISE NOTICE 'warn %', msg ; RETURN TRUE ; END
+$$ LANGUAGE plpgsql;
+-- show both
+SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two ;
+-- \gset applies to last query only
+SELECT 3 AS three \; SELECT warn('3.5') \; SELECT 4 AS four \gset
+\echo :three :four
+-- syntax error stops all processing
+SELECT 5 \; SELECT 6 + \; SELECT warn('6.5') \; SELECT 7 ;
+-- with aborted transaction, stop on first error
+BEGIN \; SELECT 8 AS eight \; SELECT 9/0 AS nine \; ROLLBACK \; SELECT 10 AS
ten ;
+-- close previously aborted transaction
+ROLLBACK;
+-- misc SQL commands
+-- (non SELECT output is sent to stderr, thus is not shown in expected results)
+SELECT 'ok' AS "begin" \;
+CREATE TABLE psql_comics(s TEXT) \;
+INSERT INTO psql_comics VALUES ('Calvin'), ('hobbes') \;
+COPY psql_comics FROM STDIN \;
+UPDATE psql_comics SET s = 'Hobbes' WHERE s = 'hobbes' \;
+DELETE FROM psql_comics WHERE s = 'Moe' \;
+COPY psql_comics TO STDOUT \;
+TRUNCATE psql_comics \;
+DROP TABLE psql_comics \;
+SELECT 'ok' AS "done" ;
+Moe
+Susie
+\.
+\set SHOW_ALL_RESULTS off
+SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two ;
+\set SHOW_ALL_RESULTS on
+DROP FUNCTION warn(TEXT);
diff --git a/src/test/regress/sql/transactions.sql
b/src/test/regress/sql/transactions.sql
index d1b6ed0280..8a1e5d8e5b 100644
--- a/src/test/regress/sql/transactions.sql
+++ b/src/test/regress/sql/transactions.sql
@@ -504,7 +504,7 @@ CREATE TABLE abc (a int);
create temp table i_table (f1 int);
--- psql will show only the last result in a multi-statement Query
+-- psql will show all results of a multi-statement Query
SELECT 1\; SELECT 2\; SELECT 3;
-- this implicitly commits:
base-commit: bb437f995d47405ecd92cf66df71f7f7e40ed460
--
2.30.1
