psql: Allow queries terminated by semicolons while in pipeline mode

Currently, the only way to pipe queries in an ongoing pipeline (in a
\startpipeline block) is to leverage the meta-commands able to create
extended queries such as \bind, \parse or \bind_named.

While this is good enough for testing the backend with pipelines, it has
been mentioned that it can also be very useful to allow queries
terminated by semicolons to be appended to a pipeline.  For example, it
would be possible to migrate existing psql scripts to use pipelines by
just adding a set of \startpipeline and \endpipeline meta-commands,
making such scripts more efficient.

Doing such a change is proving to be simple in psql: queries terminated
by semicolons can be executed through PQsendQueryParams() without any
parameters set when the pipeline mode is active, instead of
PQsendQuery(), the default, like pgbench.  \watch is still forbidden
while in a pipeline, as it expects its results to be processed
synchronously.

The large portion of this commit consists in providing more test
coverage, with mixes of extended queries appended in a pipeline by \bind
and friends, and queries terminated by semicolons.

This improvement has been suggested by Daniel Vérité.

Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Discussion: https://postgr.es/m/d67b9c19-d009-4a50-8020-1a0ea92366a1@manitou-mail.org

4 files changed, 347 insertions, 32 deletions

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a87ff7e4597..bbe337780ff 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3282,6 +3282,13 @@ exec_command_watch(PsqlScanState scan_state, bool active_branch,
 		int			iter = 0;
 		int			min_rows = 0;
 
+		if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
+		{
+			pg_log_error("\\watch not allowed in pipeline mode");
+			clean_extended_state();
+			success = false;
+		}
+
 		/*
 		 * Parse arguments.  We allow either an unlabeled interval or
 		 * "name=value", where name is from the set ('i', 'interval', 'c',
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index ed340a466f9..5249336bcf2 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1668,7 +1668,15 @@ ExecQueryAndProcessResults(const char *query,
 			}
 			break;
 		case PSQL_SEND_QUERY:
-			success = PQsendQuery(pset.db, query);
+			if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
+			{
+				success = PQsendQueryParams(pset.db, query,
+											0, NULL, NULL, NULL, NULL, 0);
+				if (success)
+					pset.piped_commands++;
+			}
+			else
+				success = PQsendQuery(pset.db, query);
 			break;
 	}
 
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out
index 68e3c19ea05..a30dec088b9 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -11,11 +11,21 @@ SELECT $1 \bind 'val1' \sendpipeline
  val1
 (1 row)
 
+\startpipeline
+SELECT 'val1';
+\endpipeline
+ ?column? 
+----------
+ val1
+(1 row)
+
 -- Multiple queries
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+SELECT 'val4';
+SELECT 'val5', 'val6';
 \endpipeline
  ?column? 
 ----------
@@ -32,6 +42,38 @@ SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
  val2     | val3
 (1 row)
 
+ ?column? 
+----------
+ val4
+(1 row)
+
+ ?column? | ?column? 
+----------+----------
+ val5     | val6
+(1 row)
+
+-- Multiple queries in single line, separated by semicolons
+\startpipeline
+SELECT 1; SELECT 2; SELECT 3
+;
+\echo :PIPELINE_COMMAND_COUNT
+3
+\endpipeline
+ ?column? 
+----------
+        1
+(1 row)
+
+ ?column? 
+----------
+        2
+(1 row)
+
+ ?column? 
+----------
+        3
+(1 row)
+
 -- Test \flush
 \startpipeline
 \flush
@@ -39,6 +81,9 @@ SELECT $1 \bind 'val1' \sendpipeline
 \flush
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+\flush
+SELECT 'val4';
+SELECT 'val5', 'val6';
 \endpipeline
  ?column? 
 ----------
@@ -55,6 +100,16 @@ SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
  val2     | val3
 (1 row)
 
+ ?column? 
+----------
+ val4
+(1 row)
+
+ ?column? | ?column? 
+----------+----------
+ val5     | val6
+(1 row)
+
 -- Send multiple syncs
 \startpipeline
 \echo :PIPELINE_COMMAND_COUNT
@@ -75,6 +130,18 @@ SELECT $1, $2 \bind 'val4' 'val5' \sendpipeline
 3
 \echo :PIPELINE_RESULT_COUNT
 2
+SELECT 'val7';
+\syncpipeline
+\syncpipeline
+SELECT 'val8';
+\syncpipeline
+SELECT 'val9';
+\echo :PIPELINE_COMMAND_COUNT
+1
+\echo :PIPELINE_SYNC_COUNT
+6
+\echo :PIPELINE_RESULT_COUNT
+5
 \endpipeline
  ?column? 
 ----------
@@ -91,6 +158,50 @@ SELECT $1, $2 \bind 'val4' 'val5' \sendpipeline
  val4     | val5
 (1 row)
 
+ ?column? 
+----------
+ val7
+(1 row)
+
+ ?column? 
+----------
+ val8
+(1 row)
+
+ ?column? 
+----------
+ val9
+(1 row)
+
+-- Query terminated with a semicolon replaces an unnamed prepared
+-- statement.
+\startpipeline
+SELECT $1 \parse ''
+SELECT 1;
+\bind_named ''
+\endpipeline
+ ?column? 
+----------
+        1
+(1 row)
+
+-- Extended query is appended to pipeline by a semicolon after a
+-- newline.
+\startpipeline
+SELECT $1 \bind 1
+;
+SELECT 2;
+\endpipeline
+ ?column? 
+----------
+ 1
+(1 row)
+
+ ?column? 
+----------
+        2
+(1 row)
+
 -- \startpipeline should not have any effect if already in a pipeline.
 \startpipeline
 \startpipeline
@@ -118,6 +229,7 @@ INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
 COMMIT \bind \sendpipeline
 \endpipeline
 -- COPY FROM STDIN
+-- with \sendpipeline and \bind
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
@@ -127,7 +239,18 @@ COPY psql_pipeline FROM STDIN \bind \sendpipeline
  val1
 (1 row)
 
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\endpipeline
+ ?column? 
+----------
+ val1
+(1 row)
+
 -- COPY FROM STDIN with \flushrequest + \getresults
+-- with \sendpipeline and \bind
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
@@ -140,7 +263,21 @@ COPY psql_pipeline FROM STDIN \bind \sendpipeline
 
 message type 0x5a arrived from server while idle
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\flushrequest
+\getresults
+ ?column? 
+----------
+ val1
+(1 row)
+
+message type 0x5a arrived from server while idle
+\endpipeline
 -- COPY FROM STDIN with \syncpipeline + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
@@ -152,7 +289,20 @@ COPY psql_pipeline FROM STDIN \bind \sendpipeline
 (1 row)
 
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\syncpipeline
+\getresults
+ ?column? 
+----------
+ val1
+(1 row)
+
+\endpipeline
 -- COPY TO STDOUT
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
@@ -164,9 +314,30 @@ copy psql_pipeline TO STDOUT \bind \sendpipeline
 
 1	\N
 2	test2
+20	test2
+3	test3
+30	test3
+4	test4
+40	test4
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\endpipeline
+ ?column? 
+----------
+ val1
+(1 row)
+
+1	\N
+2	test2
+20	test2
 3	test3
+30	test3
 4	test4
+40	test4
 -- COPY TO STDOUT with \flushrequest + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
@@ -179,10 +350,33 @@ copy psql_pipeline TO STDOUT \bind \sendpipeline
 
 1	\N
 2	test2
+20	test2
 3	test3
+30	test3
 4	test4
+40	test4
+\endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\flushrequest
+\getresults
+ ?column? 
+----------
+ val1
+(1 row)
+
+1	\N
+2	test2
+20	test2
+3	test3
+30	test3
+4	test4
+40	test4
 \endpipeline
 -- COPY TO STDOUT with \syncpipeline + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
@@ -195,8 +389,30 @@ copy psql_pipeline TO STDOUT \bind \sendpipeline
 
 1	\N
 2	test2
+20	test2
+3	test3
+30	test3
+4	test4
+40	test4
+\endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\syncpipeline
+\getresults
+ ?column? 
+----------
+ val1
+(1 row)
+
+1	\N
+2	test2
+20	test2
 3	test3
+30	test3
 4	test4
+40	test4
 \endpipeline
 -- Use \parse and \bind_named
 \startpipeline
@@ -393,18 +609,6 @@ SELECT $1 \bind 3 \sendpipeline
 -- \endpipeline outside of pipeline should fail
 \endpipeline
 cannot send pipeline when not in pipeline mode
--- Query using simple protocol should not be sent and should leave the
--- pipeline usable.
-\startpipeline
-SELECT 1;
-PQsendQuery not allowed in pipeline mode
-SELECT $1 \bind 'val1' \sendpipeline
-\endpipeline
- ?column? 
-----------
- val1
-(1 row)
-
 -- After an aborted pipeline, commands after a \syncpipeline should be
 -- displayed.
 \startpipeline
@@ -425,6 +629,13 @@ SELECT \bind 'val1' \sendpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 \endpipeline
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 0
+-- Using a semicolon with a parameter triggers an error and aborts
+-- the pipeline.
+\startpipeline
+SELECT $1;
+SELECT 1;
+\endpipeline
+ERROR:  bind message supplies 0 parameters, but prepared statement "" requires 1
 -- An explicit transaction with an error needs to be rollbacked after
 -- the pipeline.
 \startpipeline
@@ -435,12 +646,11 @@ ROLLBACK \bind \sendpipeline
 ERROR:  duplicate key value violates unique constraint "psql_pipeline_pkey"
 DETAIL:  Key (a)=(1) already exists.
 ROLLBACK;
--- \watch sends a simple query, something not allowed within a pipeline.
+-- \watch is not allowed in a pipeline.
 \startpipeline
 SELECT \bind \sendpipeline
 \watch 1
-PQsendQuery not allowed in pipeline mode
-
+\watch not allowed in pipeline mode
 \endpipeline
 --
 (1 row)
@@ -530,7 +740,7 @@ SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline
 
  count 
 -------
-     4
+     7
 (1 row)
 
 -- After an error, pipeline is aborted and requires \syncpipeline to be
@@ -617,11 +827,11 @@ select 1;
 -- Error messages accumulate and are repeated.
 \startpipeline
 SELECT 1 \bind \sendpipeline
-SELECT 1;
-PQsendQuery not allowed in pipeline mode
-SELECT 1;
-PQsendQuery not allowed in pipeline mode
-PQsendQuery not allowed in pipeline mode
+\gdesc
+synchronous command execution functions are not allowed in pipeline mode
+\gdesc
+synchronous command execution functions are not allowed in pipeline mode
+synchronous command execution functions are not allowed in pipeline mode
 \endpipeline
  ?column? 
 ----------
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql
index e4d7e614af3..16e1e1e84cd 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -8,12 +8,24 @@ CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT);
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 \endpipeline
+\startpipeline
+SELECT 'val1';
+\endpipeline
 
 -- Multiple queries
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+SELECT 'val4';
+SELECT 'val5', 'val6';
+\endpipeline
+
+-- Multiple queries in single line, separated by semicolons
+\startpipeline
+SELECT 1; SELECT 2; SELECT 3
+;
+\echo :PIPELINE_COMMAND_COUNT
 \endpipeline
 
 -- Test \flush
@@ -23,6 +35,9 @@ SELECT $1 \bind 'val1' \sendpipeline
 \flush
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
 SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+\flush
+SELECT 'val4';
+SELECT 'val5', 'val6';
 \endpipeline
 
 -- Send multiple syncs
@@ -39,6 +54,31 @@ SELECT $1, $2 \bind 'val4' 'val5' \sendpipeline
 \echo :PIPELINE_COMMAND_COUNT
 \echo :PIPELINE_SYNC_COUNT
 \echo :PIPELINE_RESULT_COUNT
+SELECT 'val7';
+\syncpipeline
+\syncpipeline
+SELECT 'val8';
+\syncpipeline
+SELECT 'val9';
+\echo :PIPELINE_COMMAND_COUNT
+\echo :PIPELINE_SYNC_COUNT
+\echo :PIPELINE_RESULT_COUNT
+\endpipeline
+
+-- Query terminated with a semicolon replaces an unnamed prepared
+-- statement.
+\startpipeline
+SELECT $1 \parse ''
+SELECT 1;
+\bind_named ''
+\endpipeline
+
+-- Extended query is appended to pipeline by a semicolon after a
+-- newline.
+\startpipeline
+SELECT $1 \bind 1
+;
+SELECT 2;
 \endpipeline
 
 -- \startpipeline should not have any effect if already in a pipeline.
@@ -66,14 +106,23 @@ COMMIT \bind \sendpipeline
 \endpipeline
 
 -- COPY FROM STDIN
+-- with \sendpipeline and \bind
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
 \endpipeline
 2	test2
 \.
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\endpipeline
+20	test2
+\.
 
 -- COPY FROM STDIN with \flushrequest + \getresults
+-- with \sendpipeline and \bind
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
@@ -82,8 +131,18 @@ COPY psql_pipeline FROM STDIN \bind \sendpipeline
 3	test3
 \.
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\flushrequest
+\getresults
+30	test3
+\.
+\endpipeline
 
 -- COPY FROM STDIN with \syncpipeline + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 COPY psql_pipeline FROM STDIN \bind \sendpipeline
@@ -92,28 +151,59 @@ COPY psql_pipeline FROM STDIN \bind \sendpipeline
 4	test4
 \.
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\syncpipeline
+\getresults
+40	test4
+\.
+\endpipeline
 
 -- COPY TO STDOUT
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\endpipeline
 
 -- COPY TO STDOUT with \flushrequest + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
 \flushrequest
 \getresults
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\flushrequest
+\getresults
+\endpipeline
 
 -- COPY TO STDOUT with \syncpipeline + \getresults
+-- with \bind and \sendpipeline
 \startpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 copy psql_pipeline TO STDOUT \bind \sendpipeline
 \syncpipeline
 \getresults
 \endpipeline
+-- with semicolon
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\syncpipeline
+\getresults
+\endpipeline
 
 -- Use \parse and \bind_named
 \startpipeline
@@ -217,13 +307,6 @@ SELECT $1 \bind 3 \sendpipeline
 -- \endpipeline outside of pipeline should fail
 \endpipeline
 
--- Query using simple protocol should not be sent and should leave the
--- pipeline usable.
-\startpipeline
-SELECT 1;
-SELECT $1 \bind 'val1' \sendpipeline
-\endpipeline
-
 -- After an aborted pipeline, commands after a \syncpipeline should be
 -- displayed.
 \startpipeline
@@ -239,6 +322,13 @@ SELECT \bind 'val1' \sendpipeline
 SELECT $1 \bind 'val1' \sendpipeline
 \endpipeline
 
+-- Using a semicolon with a parameter triggers an error and aborts
+-- the pipeline.
+\startpipeline
+SELECT $1;
+SELECT 1;
+\endpipeline
+
 -- An explicit transaction with an error needs to be rollbacked after
 -- the pipeline.
 \startpipeline
@@ -248,7 +338,7 @@ ROLLBACK \bind \sendpipeline
 \endpipeline
 ROLLBACK;
 
--- \watch sends a simple query, something not allowed within a pipeline.
+-- \watch is not allowed in a pipeline.
 \startpipeline
 SELECT \bind \sendpipeline
 \watch 1
@@ -372,8 +462,8 @@ select 1;
 -- Error messages accumulate and are repeated.
 \startpipeline
 SELECT 1 \bind \sendpipeline
-SELECT 1;
-SELECT 1;
+\gdesc
+\gdesc
 \endpipeline
 
 --