Subject: Re: Add Pipelining support in psql

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 10 Dec 2024 11:43:50 +0100

An improved version with simplifications and refinements.

num_queries (2nd element in the pipeline status prompt) is now used to
track queued queries that were not flushed (with a flush request or
sync) to the server. It used to count both unflushed queries and
flushed queries.

Code in ExecQueryAndProcessResults should be simpler now.
- DiscardAbortedPipelineResults function handles both discarding of
results until a synchronisation point is reached or discarding of
results until there's no more pending results.
- The logic to process the pipeline's results and getting the next
results fit more with the existing flow.
- Tests didn't cover chunk results so I've added additional tests to
cover use of pipelining + FETCH_COUNT

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Thu, 12 Dec 2024 00:53:27 +0100

> sync) to the server. It used to count both unflushed queries and

I skimmed the code a bit, but haven't looked closely at it yet. I did
try the patch out though. My thoughts below:

I think that new prompt is super useful, so useful in fact that I'd
suggest linking to it from the \startpipeline docs. I do think that
the wording in the docs could be a bit more precise:
1. The columns are not necessarily queries, they are messages or
commands. i.e. \parse and \bind_named both count as 1, even though
they form one query together.
2. messages not followed by \sync and \flushrequest, could very well
already "be sent to the server" (if the client buffer got full, or in
case of manual \flush). The main thing that \sync and \flushrequest do
is make sure that the server actually sends its own result back, even
if its buffer is not full yet.

The main feedback I have after playing around with this version is
that I'd like to have a \getresult (without the s), to only get a
single result. So that you can get results one by one, possibly
interleaved with some other queries again.

One thing I'm wondering is how useful the num_syncs count is in the
pipeline prompt, since you never really wait for a sync.

Regarding the usefulness of \flush. I agree it's not as useful as I
thought, because indeed \getresults already flushes everything. But
it's not completely useless either. The main way I was able to use it
interactively in a somewhat interesting way was to send it after a
long running query, and then while that's processing type up the next
query after it. Something like the following:

localhost jelte@postgres:5432-26274=
#> \startpipeline
Time: 0.000 ms
localhost jelte@postgres:5432-26274=
#|0,0,0|> select pg_sleep(5) \bind \g
Time: 0.000 ms
localhost jelte@postgres:5432-26274=
#|0,1,0|*> \flush
Time: 0.000 ms
localhost jelte@postgres:5432-26274=
#|0,1,0|*> select 1 \bind \g
Time: 0.000 ms
localhost jelte@postgres:5432-26274=
#|0,2,0|*> \syncpipeline
Time: 0.000 ms
localhost jelte@postgres:5432-26274=
#|1,0,2|*> \getresults
 pg_sleep
──────────

(1 row)

 ?column?
──────────
        1
(1 row)

Time: 0.348 ms

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Thu, 12 Dec 2024 11:38:48 +0100

Thanks for the review!

On Thu, Dec 12, 2024 at 12:53 AM Jelte Fennema-Nio <[email-masked]> wrote:
> suggest linking to it from the \startpipeline docs.

Good point, I've added a paragraph with the link to the %P prompt.

> I do think that

Yeah, I wasn't super happy with the num_queries and wording. I've
renamed the prompt columns to piped_syncs, piped_commands and
pending_results and added more precision on what counts as a command.

> if its buffer is not full yet.

I had the wrong assumptions on what was happening. I've changed the
definition of pending_results to: "pending_results is the number of
commands that were followed by either a \flushrequest or a
\syncpipeline, forcing the server to send the results that can be
retrieved with \getresults."

> interleaved with some other queries again.

\getresults was easier to implement since it was more or less the
current behaviour :). I didn't want to add a new meta-command just for
that (I feel like I'm already adding a lot of them) so I've added a
num_results parameter to \getresults. You should be able to use
\getresults 1 to get a single result. A sync response count as a
result.

> pipeline prompt, since you never really wait for a sync.

With the addition of the num_results parameter for \getresults,
knowing the number of syncs becomes more useful when results are
consumed one by one.

> query after it.

I feel there's a large overlap between \flush and \flushrequest. On
the other hand, if people want to test the behaviour of pushing data
with and without a flush request message, then \flush can be useful.

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Fri, 10 Jan 2025 11:59:44 +0100

> with and without a flush request message, then \flush can be useful.

I've been looking into some issues related to a backend process stuck
in ClientWrite state blocking the WAL replay and it turns out that
pipelining + \flush was extremely useful to reproduce the issue. It
makes it possible to saturate the server->client socket buffer since
psql doesn't consume the results, easily triggering the state where
the process is stuck in ClientWrite. So I'm amending my position on
the usefulness of \flush and definitely see interesting usages.

I've also found out that I didn't correctly manage connection reset.
I've fixed this in v6 by resetting the pipeline counters on connection
reset and only calling discardAbortedPipelineResults if we're inside a
pipeline.

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 14 Jan 2025 09:49:02 +0100

During my tests, I've noticed I didn't handle query cancellation, this
is now fixed. I've also added additional comments related to
available_results to make it clearer that it depends on what the
server has flushed to the client.

---

From: Michael Paquier <[email-masked]>
Date: Tue, 18 Feb 2025 16:22:53 +0900

> server has flushed to the client.

This is a pretty cool patch.  I like the structure you have used for
the integration with the tracking of the number of commands, the
number of syncs (like pgbench) put in a pipeline, the number of
results requested and the number of results available.  That makes the
whole easier to look at with a state in pset.

+	PSQL_SEND_PIPELINE_SYNC,
+	PSQL_START_PIPELINE_MODE,
+	PSQL_END_PIPELINE_MODE,
+	PSQL_FLUSH,
+	PSQL_SEND_FLUSH_REQUEST,
+	PSQL_GET_RESULTS,

These new values are inconsistent, let's use some more PSQL_SEND_*
here.  That makes the whole set of values more greppable.

The tests in psql.sql are becoming really long.  Perhaps it would be
better to split that into its own file, say psql_pipeline.sql?  The
input file is already 2k lines, you are adding 15% more lines to that.

+ * otherwise, calling PQgetResults will block. 

Likely PQgetResults => PQgetResult().

Wondering if the cancellation in ExecQueryAndProcessResults() is
sound, I've not been able to break it, still..

I can also get behind the additions of \flush and \flushrequest to
query different parts of libpq.

+	if (pset.requested_results == 0)
+		/* We've read all requested results, exit */
+		return res;

Set of nits with the style of the code, but I'd suggest to use
braces {} here, to outline that the comment is in a block.  There's a
second, larger one in discardAbortedPipelineResults().

+	if (strcmp(cmd, "gx") == 0 && PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
+	{
+		pg_log_error("\\gx not allowed in pipeline mode");
+		clean_extended_state();
+		return PSQL_CMD_ERROR;
+	}

What is the reasoning here behind this restriction?  \gx is a wrapper
of \g with expanded mode on, but it is also possible to call \g with
expanded=on, bypassing this restriction.

Let's split the prompt patch with the support of %P into its own
patch.

-#define DEFAULT_PROMPT1 "%/%R%x%# "
-#define DEFAULT_PROMPT2 "%/%R%x%# "
+#define DEFAULT_PROMPT1 "%/%R%P%x%# "
+#define DEFAULT_PROMPT2 "%/%R%P%x%# "
 #define DEFAULT_PROMPT3 ">> "

I don't think that changing this default is a good idea.  Everybody
can do that in their own .psqlrc (spoiler: I do).

The idea to use three fields with a hardcoded format does not look
like a good idea to me.  I think that this should be done in a
different and more extensible way:
- Use %P to track if we are in pipeline mode on, off or abort.
- Define three new variables that behave like ROW_COUNT, but for the
fields you want to track here.  These could then be passed down to a
PROMPT with %:name:, grammar already supported.

That would make the whole much more flexible.  At it seems to me that
we could also add requested_results to this set?  These could be named
with the same prefix, like PIPELINE_SYNC_COUNT, etc.
--
Michael

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 18 Feb 2025 18:34:20 +0100

> whole easier to look at with a state in pset.

Thanks!

> here.  That makes the whole set of values more greppable.

Changed.

> input file is already 2k lines, you are adding 15% more lines to that.

Agreed, I wasn't sure if this was enough to warrant a dedicated test
file. This is now separated in psql_pipeline.sql.

> Likely PQgetResults => PQgetResult().

Indeed, this is fixed.

> +               clean_extended_state();

Changed.

> expanded=on, bypassing this restriction.

The issue is that \gx enables expanded mode for the duration of the
query and immediately reset it in sendquery_cleanup. With pipelining,
the command is piped and displaying is done by either \endpipeline or
\getresults, so the flag change has no impact. Forbidding it was a way
to make it clearer that it won't have the expected effect. If we
wanted a similar feature, this would need to be done with something
like \endpipelinex or \getresultsx.

> with the same prefix, like PIPELINE_SYNC_COUNT, etc.

I've split the patch and created the 3 special variables:
PIPELINE_SYNC_COUNT, PIPELINE_COMMAND_COUNT, PIPELINE_RESULT_COUNT.

For requested_results, I don't think there's value in exposing it
since it is used as an exit condition and thus will always be 0
outside of ExecQueryAndProcessResults.

---

From: Michael Paquier <[email-masked]>
Date: Thu, 20 Feb 2025 17:02:42 +0900

> file. This is now separated in psql_pipeline.sql.

You have forgotten the expected output.  Not a big issue as the input
was sent.

> like \endpipelinex or \getresultsx.

Hmm, okay.  If one wants one mode or the other it is always possible
to force one with \pset expanded when getting the results.  Not sure
if there is any need for new specific commands for these two printing
the results.  Another option would be to authorize the command to run,
but perhaps your option is just better as per the enforced behavior in
the output.  So fine by me.  There is coverage so we'll know if there
are arguments in favor of authorizing the command, if need be.

> PIPELINE_SYNC_COUNT, PIPELINE_COMMAND_COUNT, PIPELINE_RESULT_COUNT.

Thanks.  Looks sensible now.

> outside of ExecQueryAndProcessResults.

I've been playing with this patch and this configuration:
\set PROMPT1 '=(pipeline=%P,sync=%:PIPELINE_SYNC_COUNT:,cmd=%:PIPELINE_COMMAND_COUNT:,res=%:PIPELINE_RESULT_COUNT:)%#' 

That's long, but seeing the evolution of the pipeline status is pretty
cool depending on the meta-commands used.

While testing, I have been able to run into an assertion failure by
adding some tests in psql.sql to check for the case of inactive
branches for \if.  For example:
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1047,11 +1047,15 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
    \echo arg1 arg2 arg3 arg4 arg5
    \echo arg1
    \encoding arg1
+   \endpipeline
    \errverbose

And the report:
+psql: mainloop.c:513: MainLoop: Assertion `conditional_active(cond_stack)' failed.

We should have tests for all new six meta-commands in psql.sql.
MainLoop() is wrong when in pipeline mode for inactive branches.
--
Michael

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Thu, 20 Feb 2025 10:29:33 +0100

> You have forgotten the expected output.  Not a big issue as the input

I was writing the mail with the missing file when you sent this mail.
This is fixed.

> MainLoop() is wrong when in pipeline mode for inactive branches.

Ha yeah, I forgot about the inactive branches. I've added the new
commands and fixed the behaviour.

A small issue I've noticed while testing: When a pipeline has at least
one queue command, pqClearConnErrorState isn't called in
PQsendQueryStart and errors are appended. For example:

\startpipeline
select 1 \bind \g
select 1;
PQsendQuery not allowed in pipeline mode
select 1;
PQsendQuery not allowed in pipeline mode
PQsendQuery not allowed in pipeline mode

This looks more like an issue on libpq's side as there's no way to
reset or advance the errorReported from ExecQueryAndProcessResults
(plus PQerrorMessage seems to ignore errorReported). I've added an
additional test to track this behaviour for now as this would probably
be better discussed in a dedicated thread.

---

From: Michael Paquier <[email-masked]>
Date: Fri, 21 Feb 2025 11:33:41 +0900

> commands and fixed the behaviour.

And I did not notice that it was as simple as forcing the status in
the routines for the new meta-commands, as we do for the existing
ones.  Noted.

> be better discussed in a dedicated thread.

I am not sure if we should change that, actually, as it does not feel
completely wrong to stack these errors.  That's a bit confusing,
sure.  Perhaps a new libpq API to retrieve stacked errors when we are
in pipeline mode would be more adapted?  The design would be
different.

Anyway, I've stared at the result processing code for a couple of
hours, and the branches we're taking for the pipeline modes seem to be
rather right the way you have implemented them.  The docs, comments
and tests needed quite a few tweaks and edits to be more consistent.
There were some grammar mistakes, some frenchisms.

I'm hoping that there won't be any issues, but let's be honest, I am
definitely sure there will be some more tuning required.  It comes
down to if we want this set of features, and I do to be able to expand
tests in core with the extended query protocol and pipelines, knowing
that there is an ask for out-of-core projects.  This one being
reachable with a COPY gave me a huge smile:
+message type 0x5a arrived from server while idle

So let's take one step here, I have applied the main patch.  I am
really excited by the possibilities all this stuff offers.

Attached are the remaining pieces, split here because they are
different bullet points:
- Tests for implicit transactions with various commands, with some
edits. 
- Prompt support, with more edits.

I'm putting these on standby for a few days, to let the buildfarm
digest the main change.
--
Michael
From 035f19d120b181094b00dee21acb0b4ce4cfc1e1 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 21 Feb 2025 11:27:36 +0900
Subject: [PATCH v10 1/2] Add tests with implicit transaction blocks and
 pipelines

This checks interactions with $subject for the following commands that
have dedicated behaviors in transaction blocks:
- SET LOCAL
- REINDEX CONCURRENTLY
- VACUUM
- Subtransactions
---
 src/test/regress/expected/psql_pipeline.out | 112 ++++++++++++++++++++
 src/test/regress/sql/psql_pipeline.sql      |  72 +++++++++++++
 2 files changed, 184 insertions(+)

diff --git src/test/regress/expected/psql_pipeline.out src/test/regress/expected/psql_pipeline.out
index bdf5a99d09b0..f4603d2b66ae 100644
--- src/test/regress/expected/psql_pipeline.out
+++ src/test/regress/expected/psql_pipeline.out
@@ -585,5 +585,117 @@ PQsendQuery not allowed in pipeline mode
         1
 (1 row)
 
+--
+-- Pipelines and transaction blocks
+--
+-- SET LOCAL will issue a warning when modifying a GUC outside of a
+-- transaction block.  The change will still be valid as a pipeline
+-- runs within an implicit transaction block.  Sending a sync will
+-- commit the implicit transaction block. The first command after a
+-- sync will not be seen as belonging to a pipeline.
+\startpipeline
+SET LOCAL statement_timeout='1h' \bind \g
+SHOW statement_timeout \bind \g
+\syncpipeline
+SHOW statement_timeout \bind \g
+SET LOCAL statement_timeout='2h' \bind \g
+SHOW statement_timeout \bind \g
+\endpipeline
+WARNING:  SET LOCAL can only be used in transaction blocks
+ statement_timeout 
+-------------------
+ 1h
+(1 row)
+
+ statement_timeout 
+-------------------
+ 0
+(1 row)
+
+ statement_timeout 
+-------------------
+ 2h
+(1 row)
+
+-- REINDEX CONCURRENTLY fails if not the first command in a pipeline.
+\startpipeline
+SELECT $1 \bind 1 \g
+REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+ ?column? 
+----------
+ 1
+(1 row)
+
+ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block
+-- REINDEX CONCURRENTLY works if it is the first command in a pipeline.
+\startpipeline
+REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+ ?column? 
+----------
+ 2
+(1 row)
+
+-- Subtransactions are not allowed in a pipeline.
+\startpipeline
+SAVEPOINT a \bind \g
+SELECT $1 \bind 1 \g
+ROLLBACK TO SAVEPOINT a \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+ERROR:  SAVEPOINT can only be used in transaction blocks
+-- LOCK fails as the first command in a pipeline, as not seen in an
+-- implicit transaction block.
+\startpipeline
+LOCK psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+ERROR:  LOCK TABLE can only be used in transaction blocks
+-- LOCK succeeds as it is not the first command in a pipeline,
+-- seen in an implicit transaction block.
+\startpipeline
+SELECT $1 \bind 1 \g
+LOCK psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+ ?column? 
+----------
+ 1
+(1 row)
+
+ ?column? 
+----------
+ 2
+(1 row)
+
+-- VACUUM works as the first command in a pipeline.
+\startpipeline
+VACUUM psql_pipeline \bind \g
+\endpipeline
+-- VACUUM fails when not the first command in a pipeline.
+\startpipeline
+SELECT 1 \bind \g
+VACUUM psql_pipeline \bind \g
+\endpipeline
+ ?column? 
+----------
+        1
+(1 row)
+
+ERROR:  VACUUM cannot run inside a transaction block
+-- VACUUM works after a \syncpipeline.
+\startpipeline
+SELECT 1 \bind \g
+\syncpipeline
+VACUUM psql_pipeline \bind \g
+\endpipeline
+ ?column? 
+----------
+        1
+(1 row)
+
 -- Clean up
 DROP TABLE psql_pipeline;
diff --git src/test/regress/sql/psql_pipeline.sql src/test/regress/sql/psql_pipeline.sql
index 38e48054eeb9..ec62e6c5f247 100644
--- src/test/regress/sql/psql_pipeline.sql
+++ src/test/regress/sql/psql_pipeline.sql
@@ -350,5 +350,77 @@ SELECT 1;
 SELECT 1;
 \endpipeline
 
+--
+-- Pipelines and transaction blocks
+--
+
+-- SET LOCAL will issue a warning when modifying a GUC outside of a
+-- transaction block.  The change will still be valid as a pipeline
+-- runs within an implicit transaction block.  Sending a sync will
+-- commit the implicit transaction block. The first command after a
+-- sync will not be seen as belonging to a pipeline.
+\startpipeline
+SET LOCAL statement_timeout='1h' \bind \g
+SHOW statement_timeout \bind \g
+\syncpipeline
+SHOW statement_timeout \bind \g
+SET LOCAL statement_timeout='2h' \bind \g
+SHOW statement_timeout \bind \g
+\endpipeline
+
+-- REINDEX CONCURRENTLY fails if not the first command in a pipeline.
+\startpipeline
+SELECT $1 \bind 1 \g
+REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+
+-- REINDEX CONCURRENTLY works if it is the first command in a pipeline.
+\startpipeline
+REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+
+-- Subtransactions are not allowed in a pipeline.
+\startpipeline
+SAVEPOINT a \bind \g
+SELECT $1 \bind 1 \g
+ROLLBACK TO SAVEPOINT a \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+
+-- LOCK fails as the first command in a pipeline, as not seen in an
+-- implicit transaction block.
+\startpipeline
+LOCK psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+
+-- LOCK succeeds as it is not the first command in a pipeline,
+-- seen in an implicit transaction block.
+\startpipeline
+SELECT $1 \bind 1 \g
+LOCK psql_pipeline \bind \g
+SELECT $1 \bind 2 \g
+\endpipeline
+
+-- VACUUM works as the first command in a pipeline.
+\startpipeline
+VACUUM psql_pipeline \bind \g
+\endpipeline
+
+-- VACUUM fails when not the first command in a pipeline.
+\startpipeline
+SELECT 1 \bind \g
+VACUUM psql_pipeline \bind \g
+\endpipeline
+
+-- VACUUM works after a \syncpipeline.
+\startpipeline
+SELECT 1 \bind \g
+\syncpipeline
+VACUUM psql_pipeline \bind \g
+\endpipeline
+
 -- Clean up
 DROP TABLE psql_pipeline;
-- 
2.47.2

From b183638bb31c75fab28ecf67112304b52bb12dce Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 18 Feb 2025 15:32:31 +0100
Subject: [PATCH v10 2/2] Add prompt interpolation and variables for psql
 pipeline

Add %P prompt interpolation that reports the status pipeline: on, off or
abort. Additionally, 3 new special variables are added to report the
status of an ongoing pipeline:
- PIPELINE_SYNC_COUNT: reports the number of piped sync
- PIPELINE_COMMAND_COUNT: reports the number of piped commands, a
  command being either \bind, \bind_named, \close or \parse
- PIPELINE_RESULT_COUNT: reports the results available to read with
  \getresults
---
 src/bin/psql/common.c          | 27 +++++++++++++++++-
 src/bin/psql/prompt.c          | 14 ++++++++++
 src/bin/psql/startup.c         |  5 ++++
 doc/src/sgml/ref/psql-ref.sgml | 50 ++++++++++++++++++++++++++++++++++
 4 files changed, 95 insertions(+), 1 deletion(-)

diff --git src/bin/psql/common.c src/bin/psql/common.c
index bc8c40898f71..ed340a466f99 100644
--- src/bin/psql/common.c
+++ src/bin/psql/common.c
@@ -524,6 +524,26 @@ SetShellResultVariables(int wait_result)
 }

+/*
+ * Set special pipeline variables
+ * - PIPELINE_SYNC_COUNT: The number of piped syncs
+ * - PIPELINE_COMMAND_COUNT: The number of piped commands
+ * - PIPELINE_RESULT_COUNT: The number of results available to read
+ */
+static void
+SetPipelineVariables(void)
+{
+	char		buf[32];
+
+	snprintf(buf, sizeof(buf), "%d", pset.piped_syncs);
+	SetVariable(pset.vars, "PIPELINE_SYNC_COUNT", buf);
+	snprintf(buf, sizeof(buf), "%d", pset.piped_commands);
+	SetVariable(pset.vars, "PIPELINE_COMMAND_COUNT", buf);
+	snprintf(buf, sizeof(buf), "%d", pset.available_results);
+	SetVariable(pset.vars, "PIPELINE_RESULT_COUNT", buf);
+}
+
+
 /*
  * ClearOrSaveResult
  *
@@ -1661,6 +1681,8 @@ ExecQueryAndProcessResults(const char *query,
 
 		CheckConnection();
 
+		SetPipelineVariables();
+
 		return -1;
 	}
 
@@ -1669,8 +1691,10 @@ ExecQueryAndProcessResults(const char *query,
 	{
 		/*
 		 * We are in a pipeline and have not reached the pipeline end, or
-		 * there was no request to read pipeline results, exit.
+		 * there was no request to read pipeline results.  Update the psql
+		 * variables tracking the pipeline activity and exit.
 		 */
+		SetPipelineVariables();
 		return 1;
 	}
 
@@ -2105,6 +2129,7 @@ ExecQueryAndProcessResults(const char *query,
 		Assert(pset.available_results == 0);
 	}
 	Assert(pset.requested_results == 0);
+	SetPipelineVariables();
 
 	/* may need this to recover from conn loss during COPY */
 	if (!CheckConnection())
diff --git src/bin/psql/prompt.c src/bin/psql/prompt.c
index 08a14feb3c3f..3aa7d2d06c80 100644
--- src/bin/psql/prompt.c
+++ src/bin/psql/prompt.c
@@ -31,6 +31,7 @@
  *		sockets, "[local:/dir/name]" if not default
  * %m - like %M, but hostname only (before first dot), or always "[local]"
  * %p - backend pid
+ * %P - pipeline status: on, off or abort
  * %> - database server port number
  * %n - database user name
  * %s - service
@@ -181,6 +182,19 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
 							snprintf(buf, sizeof(buf), "%d", pid);
 					}
 					break;
+					/* pipeline status */
+				case 'P':
+					{
+						PGpipelineStatus status = PQpipelineStatus(pset.db);
+
+						if (status == PQ_PIPELINE_ON)
+							strlcpy(buf, "on", sizeof(buf));
+						else if (status == PQ_PIPELINE_ABORTED)
+							strlcpy(buf, "abort", sizeof(buf));
+						else
+							strlcpy(buf, "off", sizeof(buf));
+						break;
+					}
 
 				case '0':
 				case '1':
diff --git src/bin/psql/startup.c src/bin/psql/startup.c
index 703f3f582c17..5018eedf1e57 100644
--- src/bin/psql/startup.c
+++ src/bin/psql/startup.c
@@ -205,6 +205,11 @@ main(int argc, char *argv[])
 	SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
 	SetVariableBool(pset.vars, "SHOW_ALL_RESULTS");
 
+	/* Initialize pipeline variables */
+	SetVariable(pset.vars, "PIPELINE_SYNC_COUNT", "0");
+	SetVariable(pset.vars, "PIPELINE_COMMAND_COUNT", "0");
+	SetVariable(pset.vars, "PIPELINE_RESULT_COUNT", "0");
+
 	parse_psql_options(argc, argv, &options);
 
 	/*
diff --git doc/src/sgml/ref/psql-ref.sgml doc/src/sgml/ref/psql-ref.sgml
index a8a0b694bbf0..32130242e9c8 100644
--- doc/src/sgml/ref/psql-ref.sgml
+++ doc/src/sgml/ref/psql-ref.sgml
@@ -3727,6 +3727,12 @@ testdb=&gt; <userinput>\setenv LESS -imx4F</userinput>
         generate one result to get.
        </para>
 
+       <para>
+        When pipeline mode is active, a dedicated prompt variable is available
+        to report the pipeline status.
+        See <xref linkend="app-psql-prompting-p-uc"/> for more details
+       </para>
+
        <para>
         Example:
 <programlisting>
@@ -4501,6 +4507,39 @@ bar
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-variables-pipeline-command-count">
+        <term><varname>PIPELINE_COMMAND_COUNT</varname></term>
+        <listitem>
+        <para>
+        The number of commands generated by <literal>\bind</literal>,
+        <literal>\bind_named</literal>, <literal>\close</literal> or
+        <literal>\parse</literal> queued in an ongoing pipeline.
+        </para>
+        </listitem>
+      </varlistentry>
+
+      <varlistentry id="app-psql-variables-pipeline-result-count">
+        <term><varname>PIPELINE_RESULT_COUNT</varname></term>
+        <listitem>
+        <para>
+        The number of commands of an ongoing pipeline that were followed
+        by either a <command>\flushrequest</command> or a
+        <command>\syncpipeline</command>, forcing the server to send the
+        results. These results can be retrieved with
+        <command>\getresults</command>.
+        </para>
+        </listitem>
+      </varlistentry>
+
+      <varlistentry id="app-psql-variables-pipeline-sync-count">
+        <term><varname>PIPELINE_SYNC_COUNT</varname></term>
+        <listitem>
+        <para>
+        The number of sync messages queued in an ongoing pipeline.
+        </para>
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-variables-port">
         <term><varname>PORT</varname></term>
         <listitem>
@@ -4900,6 +4939,17 @@ testdb=&gt; <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-prompting-p-uc">
+        <term><literal>%P</literal></term>
+        <listitem>
+        <para>
+        Pipeline status: <literal>off</literal> when not in a pipeline,
+        <literal>on</literal> when in an ongoing pipeline or
+        <literal>abort</literal> when in an aborted pipeline.
+        </para>
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-prompting-r">
         <term><literal>%R</literal></term>
         <listitem>
-- 
2.47.2

---

From: Michael Paquier <[email-masked]>
Date: Tue, 25 Feb 2025 10:11:13 +0900

> I'm putting these on standby for a few days, to let the buildfarm

Initial digestion has gone well.  The remaining pieces have been done
as 3ce357584e79 and a4e986ef5a46.  For the prompt part, I have added a
couple of tests with \echo and the variables.  The patch felt
incomplete without these.  Perhaps we could extend them more, at least
we have a start point.
--
Michael

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 25 Feb 2025 08:30:16 +0100

On Tue, Feb 25, 2025 at 2:11 AM Michael Paquier <[email-masked]> wrote:
> For the prompt part, I have added a

Good catch. I guess that's also another benefit of having special
variables, as it makes it easier to check the pipeline state.

---

From: "Daniel Verite" <[email-masked]>
Date: Fri, 28 Feb 2025 17:31:00 +0100

Anthonin Bonnefoy wrote:

> to make it clearer that it won't have the expected effect

But it's not just \gx

The following invocations don't respect the desired output destination
and formats (ignoring them), when a pipeline is active:

select ... \bind \g filename
select ... \bind \g |program
select ... \bind \g (format=unaligned tuples_only=on)

Just like for \gx the problem is that in a pipeline, sending the query
is not followed by getting the results, and the output properties
of a query are lost in between.

Best regards,
-- 
Daniel Vérité 
https://postgresql.verite.pro/

---

From: Michael Paquier <[email-masked]>
Date: Tue, 4 Mar 2025 09:56:55 +0900

(My previous message did not reach the lists, so re-sending with some
edits.)

> of a query are lost in between.

Right.  I completely forgot that these options could be applied with a
simple \g.  With the results being decoupled from the execution, one
can argue that the options defined at the moment when the query is
sent and executed should be the moment commanding how the result are
shaped when retrieving a batch with \getresult during a pipeline.
However, this means that we would need to save the set of options from
printQueryOpt when running the query depending on the number of
results we expect, and reapply them in order of the results
expected. We have the APIs to do that, with savePsetInfo() and
restorePsetInfo().

Anyway, can we really say that the set of printQueryOpt saved at
execution is the correct set to use?  It is possible to have the
opposite argument and say that we should just apply the printQueryOpt
at the moment where \getresult is run.  A benefit of that it to keep
the loop retrieving results simpler in ExecQueryAndProcessResults(),
because we pass down to this call *one* printQueryOpt as "opt". 

There's of course the simplicity argument that I do like a lot here,
but applying the printing options at the time of \getresult feels also
more natural to me.

FWIW, I agree that HEAD is unbalanced with its handling of \gx, so we
could do one of the following two things:
1) Ignore any formatting options given to \g, but also allow \gx to
run, documenting that during a pipeline the formatting options are
ignored, and that the set of options defined when doing a \getresult
is the only thing that matters.
2) Invent a new meta-command (suggested by Daniel Verite off-list, and
not published to the lists because I don't know how to do a
reply-all), like a \send, a \push, forbiding entirely \gx and \g when
in a pipeline.  If we were to integrate options into this new
meta-command, a split with \g would make an integration easier to
think about.  One name suggestion I can come up is \sendpipeline.

I'd consider option 2, based on Daniel's concerns.  One line I'm going
to draw here is that we should not go down to manipulations of
printQueryOpt while retrieving batch of results depending on the style
of output that was defined when sending a query in a pipeline.

Anthonin, as the primary author, any thoughts?
--
Michael

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 4 Mar 2025 10:31:46 +0100

> but applying the printing options at the time of \getresult feels also

Saving the printQueryOpt when a command is pushed was an option I had
in mind if that was straightforward to implement. However, even with
savePsetInfo, you will need to save values like gfname and gset_prefix
since it impacts the output (it may make sense to move those in
printQueryOpt). This would also need to be saved for all commands,
like \close or \parse since we don't distinguish if a piped command
generates an output or not. So that definitely looks like it would add
a lot of complexity for limited benefit.

> of output that was defined when sending a query in a pipeline.

Another possible option would be to directly send the command without
requiring an additional meta-command, like "SELECT 1 \bind". However,
this would make it more painful to introduce new parameters, plus it
makes the \bind and \bind_named inconsistent as it is normally
required to send the result with a separate meta-command.

I like the \sendpipeline option. It makes it clearer that formatting
options are not applicable within a pipeline (at least, in the current
implementation) and I think it would make more sense to have those
formatting options in \getresults or \endpipeline.

I took a stab at creating the \sendpipeline meta-command. I've also
realised there's a small leak where fname is currently not freed on
queries like 'select ... \bind \gx filename' when within a pipeline,
which is fixed in patch 0001.

---

From: Michael Paquier <[email-masked]>
Date: Wed, 5 Mar 2025 08:11:36 +0900

> a lot of complexity for limited benefit.

Yeah, same opinion here.  I don't want this level of complexity with
extra manipulations of printQueryOpt when fetching the results,
either.  I'm all for making these meta-commands to what we think is
more natural, but not at the cost of a more complex logic in the
result printing depending on what's been given by a meta-command when
a query is pushed to a pipeline.

> which is fixed in patch 0001.

Indeed.  Fixed this one for now.
--
Michael

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 7 Mar 2025 01:05:13 +0100

On Tue, 25 Feb 2025 at 02:11, Michael Paquier <[email-masked]> wrote:
> Initial digestion has gone well.

One thing I've noticed is that \startpipeline throws warnings when
copy pasting multiple lines. It seems to still execute everything as
expected though. As an example you can copy paste this tiny script:

\startpipeline
select pg_sleep(5) \bind \g
\endpipeline

And then it will show these "extra argument ... ignored" warnings

\startpipeline: extra argument "select" ignored
\startpipeline: extra argument "pg_sleep(5)" ignored

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Fri, 7 Mar 2025 09:08:51 +0100

On Thu, Mar 6, 2025 at 5:20 AM Michael Paquier <[email-masked]> wrote:
> That was not a test case we had in mind originally here, but if it is
> possible to keep the implementation simple while supporting your
> demand, well, let's do it.  If it's not that straight-forward, let's
> use the new meta-command, forbidding \g and \gx based on your
> arguments from upthread.

I think the new meta-command is a separate issue from allowing ';' to
push in a pipeline. Any time there's a change or an additional format
option added to \g, it will need to be forbidden for pipelining. The
\sendpipeline meta-command will help keep those exceptions low since
the whole \g will be forbidden.

Another possible option would be to allow both \g and \gx, but send a
warning like "printing options within a pipeline will be ignored" if
those options are used, similar to "SET LOCAL" warning when done
outside of a transaction block. That would have the benefit of making
existing scripts using \g and \gx compatible.

For using ';' to push commands in a pipeline, I think it should be
fairly straightforward. I can try to work on that next week (I'm
currently chasing a weird memory context bug that I need to finish
first).

> \startpipeline: extra argument "pg_sleep(5)" ignored

It looks like an issue with libreadline. At least, I've been able to
reproduce the warnings and 'readline(prompt);' returns everything as a
single line, with the \n inside the string. This explains why what is
after \startpipeline is processed as arguments. This can also be done
with:
select 1 \bind \g
select 2 \bind \g
And somehow, I couldn't reproduce the issue anymore once I've compiled
and installed libreadline with debug symbols.

---

From: "Daniel Verite" <[email-masked]>
Date: Fri, 07 Mar 2025 23:31:12 +0100

Jelte Fennema-Nio wrote:

> As an example you can copy paste this tiny script:
> 
> \startpipeline

It happens with other metacommands as well, and appears
to depend on a readline option that is "on" by default since
readline-8.1 [1]

 enable-bracketed-paste

    When set to ‘On’, Readline configures the terminal to insert each
    paste into the editing buffer as a single string of characters,
    instead of treating each character as if it had been read from the
    keyboard. This is called putting the terminal into bracketed paste
    mode; it prevents Readline from executing any editing commands
    bound to key sequences appearing in the pasted text. The default
    is ‘On’.

This behavior of the metacommand complaining about arguments 
on the next line also happens if using \e and typing this sequence
of commands  in the editor. In that case readline is not involved.

There might be something to improve here, because
a metacommand cannot take its argument from the next line,
and yet that's what the error messages somewhat imply.
But that issue is not related to the new pipeline metacommands.

[1]
https://tiswww.case.edu/php/chet/readline/readline.html#index-enable_002dbracketed_002dpaste

Best regards,

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Mon, 17 Mar 2025 10:50:50 +0100

Here is a new patch set:

0001: This introduces the \sendpipeline meta-command and forbid \g in
a pipeline. This is to fix the formatting options of \g that are not
supported in a pipeline.

0002: Allows ';' to send a query using extended protocol when within a
pipeline by using PQsendQueryParams with 0 parameters. It is not
possible to send parameters with extended protocol this way and
everything will be propagated through the query string, similar to a
simple query.

---

From: Michael Paquier <[email-masked]>
Date: Mon, 17 Mar 2025 19:19:30 +0900

> everything will be propagated through the query string, similar to a

Thanks for sending a new patch set.  I was planning to look at the
situation tomorrow, and you have beaten me to it.

The split makes sense, and I'm OK with 0001.  0002 is going to require
a much closer lookup.
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Tue, 18 Mar 2025 09:50:27 +0900

> a pipeline. This is to fix the formatting options of \g that are not

- count 
--------
-     4
-(1 row)

This removal done in the regression tests was not intentional.

I have done some reordering of the code around the new meta-command so
as things are ordered alphabetically, and applied the result.

> everything will be propagated through the query string, similar to a

I like the simplicity of what you are doing here, relying on
PSQL_SEND_QUERY being the default so as we use PQsendQueryParams()
with no parameters rather than PQsendQuery() when the pipeline mode is
not off.

How about adding a check on PIPELINE_COMMAND_COUNT when sending a
query through this path?  Should we check for more scenarios with
syncs and flushes as well when sending these queries?
--
Michael

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Tue, 18 Mar 2025 09:55:21 +0100

> This removal done in the regression tests was not intentional.

Yes, thanks for fixing that.

> syncs and flushes as well when sending these queries?

I've added additional tests when piping queries with ';':
- I've reused the same scenario with \sendpipeline: single query,
multiple queries, flushes, syncs, using COPY...
- Using ';' will replace the unnamed prepared statement. It's a bit
different from expected as a simple query will delete the unnamed
prepared statement.
- Sending an extended query prepared with \bind using a ';' on a
newline, though this is not specific to pipelining. The scanned
semicolon triggers the call to SendQuery, processing the buffered
extended query. It's a bit unusual but that's the current behaviour.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 18 Mar 2025 10:27:38 +0100

> extended query. It's a bit unusual but that's the current behaviour.

One thing that comes to mind that I think would be quite useful and
pretty easy to implement if we have this functionality within a
pipeline: An \extended command. That puts psql in "extended protocol
mode" (without enabling pipelining). In "extended protocol mode" all
queries would automatically be sent using PQsendQueryParams. That
would remove the need to use \bind anymore outside of a pipeline
either.

---

From: "Daniel Verite" <[email-masked]>
Date: Tue, 18 Mar 2025 10:36:28 +0100

Anthonin Bonnefoy wrote:

> pipeline by using PQsendQueryParams

It's a nice improvement!

> with 0 parameters. It is not

It's actually possible to use parameters

\startpipeline 
\bind 'foo'
select $1;
\endpipeline

 ?column? 
----------
 foo
(1 row)

I suspect there's a misunderstanding that \bind can only be placed
after the query, because it's always written like that in the regression
tests, and in the documentation.
But that's just a notational preference.

Best regards,

---

From: Michael Paquier <[email-masked]>
Date: Wed, 19 Mar 2025 08:56:54 +0900

> It's actually possible to use parameters

Nice trick, unrelated to pipelines.  I don't think that we have
anything testing this specific pattern for \bind.  At quick glance all
our test use \bind at the end of a query string.  Perhaps we should?
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Wed, 19 Mar 2025 11:28:54 +0900

> would remove the need to use \bind anymore outside of a pipeline

How does that help when passing parameter values?  \bind is here to be
able to pass down parameter values to queries that are prepared, so we
cannot bypass it as the parameter values need to be passed to the
\bind meta-command itself.

Perhaps an \extended command that behaves outside a pipeline makes
sense to force the use of queries without parameters to use the
extended mode, but I cannot get much excited about the concept knowing
all the meta-commands we have now (not talking about the pipeline
part, which is different, as we can treat queries in batches).
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Wed, 19 Mar 2025 13:49:23 +0900

> extended query. It's a bit unusual but that's the current behaviour.

The tests could be much more organized, particularly for the "sinple"
and "multiple" and COPY cases, rather than being treated as two
different groups at different locations of psql_pipeline.sql.  I've
spent some time reorganizing all that.

A second thing that was a bit itchy is the use of ";" for what's a
semicolon, and we use this term in the psql docs to refer to queries
terminated by that.  The whole paragraph could be simplified a bit
more, mentioning that everything in a pipeline uses the extended
protocol, while \bind & co are more like options.  The description of
PIPELINE_COMMAND_COUNT could be simpler, and the part about the
pending results can be more general now so I've removed it.

With all that set, I've applied the patch.  If you have more
suggestions, please feel free to mention them.
--
Michael

---

From: "Daniel Verite" <[email-masked]>
Date: Wed, 19 Mar 2025 14:05:34 +0100

> part, which is different, as we can treat queries in batches).

When psql started supporting the extended query protocol, the question
of enabling it more globally rather than query-by-query was discussed
a bit [1]. The idea was to switch to it with a setting or a variable
rather than a metacommand.
Some pros and cons were mentioned in the thread, but on the whole
it was not convincing enough to get implemented.

[1]
https://www.postgresql.org/message-id/e8dd1cd5-0e04-3598-0518-a605159fe314%40enterprisedb.com

Best regards,