Subject: Re: Add Pipelining support in psql

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,
-- 
Daniel Vérité 
https://postgresql.verite.pro/

---

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,