public inbox for [email protected]
help / color / mirror / Atom feedFrom: Anthonin Bonnefoy <[email protected]>
To: Michael Paquier <[email protected]>
Cc: Daniel Verite <[email protected]>
Cc: Jelte Fennema-Nio <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add Pipelining support in psql
Date: Tue, 18 Mar 2025 09:55:21 +0100
Message-ID: <CAO6_Xqo=fNh0KCRZG7T18OZfjiabeL-t4C6mkXsz-3dAZ1YwTA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGECzQRe3pHYvEAZz5Nv9AKzS2nYFLKEfgQCgd5Gr54Um5UFHg@mail.gmail.com>
<[email protected]>
<CAO6_Xqq4JEHRaGyC=r+dpi0Ejbp2P3aUo-KfR2NuipCvE-o_ZA@mail.gmail.com>
<[email protected]>
On Tue, Mar 18, 2025 at 1:50 AM Michael Paquier <[email protected]> wrote:
> - count
> --------
> - 4
> -(1 row)
>
> This removal done in the regression tests was not intentional.
Yes, thanks for fixing that.
> 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?
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.
Attachments:
[application/octet-stream] v03-0001-psql-Allow-to-add-queries-in-an-ongoing-pipeline.patch (13.5K, ../CAO6_Xqo=fNh0KCRZG7T18OZfjiabeL-t4C6mkXsz-3dAZ1YwTA@mail.gmail.com/2-v03-0001-psql-Allow-to-add-queries-in-an-ongoing-pipeline.patch)
download | inline diff:
From ae9ce7e44c1e2502429920c5d305e1ffcd30b1a5 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Wed, 5 Mar 2025 14:55:33 +0100
Subject: psql: Allow ';' to add queries in an ongoing pipeline
Currently, the only way to pipe queries in an ongoing pipeline is to
leverage psql meta-commands to create extended queries such as \bind,
\parse or \bind_named. This prevents using psql's pipeline on existing
scripts as it would require to convert all queries to use those
meta-commands.
This patch modifies ';' behaviour within an active pipeline and send all
queries as extended queries, allowing them to be piped in a pipeline.
---
doc/src/sgml/ref/psql-ref.sgml | 21 +-
src/bin/psql/command.c | 7 +
src/bin/psql/common.c | 10 +-
src/test/regress/expected/psql_pipeline.out | 292 ++++++++++++++++++--
src/test/regress/sql/psql_pipeline.sql | 139 +++++++++-
5 files changed, 429 insertions(+), 40 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cddf6e07531..2763486e268 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3698,14 +3698,19 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput>
</para>
<para>
- Pipeline mode requires the use of the extended query protocol. All
- queries need to be sent using the meta-commands
- <literal>\bind</literal>, <literal>\bind_named</literal>,
- <literal>\close</literal> or <literal>\parse</literal>. While a
- pipeline is ongoing, <literal>\sendpipeline</literal> will append the
- current query buffer to the pipeline. Other meta-commands like
- <literal>\g</literal>, <literal>\gx</literal> or <literal>\gdesc</literal>
- are not allowed in pipeline mode.
+ Pipeline mode requires the use of the extended query protocol. Queries
+ can be sent using the meta-commands <literal>\bind</literal>,
+ <literal>\bind_named</literal>, <literal>\close</literal> or
+ <literal>\parse</literal>. While a pipeline is ongoing,
+ <literal>\sendpipeline</literal> will append the current query
+ buffer to the pipeline. Other meta-commands like <literal>\g</literal>,
+ <literal>\gx</literal> or <literal>\gdesc</literal> are not allowed
+ in pipeline mode.
+ </para>
+
+ <para>
+ Queries can also be sent using <literal>;</literal>. While a pipeline is
+ ongoing, they will automatically be sent using extended query protocol.
</para>
<para>
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..7dddf26a9fd 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -386,6 +386,262 @@ SELECT $1 \bind 3 \sendpipeline
3
(1 row)
+\endpipeline
+--
+-- Tests pipelining queries with ';'
+--
+-- Single query sent with ';'
+\startpipeline
+SELECT 1;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+-- Multiple queries sent with ';'
+\startpipeline
+SELECT 1;
+SELECT 2;
+SELECT 3;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+ ?column?
+----------
+ 3
+(1 row)
+
+-- Multiple queries on the same line can be piped with ';'
+\startpipeline
+SELECT 1; SELECT 2; SELECT 3
+;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+ ?column?
+----------
+ 3
+(1 row)
+
+-- Test \flush with queries piped with ';'
+\startpipeline
+\flush
+SELECT 1;
+\flush
+SELECT 2;
+SELECT 3;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+ ?column?
+----------
+ 3
+(1 row)
+
+-- Send multiple syncs with queries piped with ';'
+\startpipeline
+\echo :PIPELINE_COMMAND_COUNT
+0
+\echo :PIPELINE_SYNC_COUNT
+0
+\echo :PIPELINE_RESULT_COUNT
+0
+SELECT 1;
+\syncpipeline
+\syncpipeline
+SELECT 2;
+\syncpipeline
+SELECT 3;
+\echo :PIPELINE_COMMAND_COUNT
+1
+\echo :PIPELINE_SYNC_COUNT
+3
+\echo :PIPELINE_RESULT_COUNT
+2
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+ ?column?
+----------
+ 3
+(1 row)
+
+-- Mix queries piped with ';' and \sendpipeline
+\startpipeline
+SELECT 1;
+SELECT $1 \bind 'val1' \sendpipeline
+SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+SELECT 2;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ val1
+(1 row)
+
+ ?column? | ?column?
+----------+----------
+ val2 | val3
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+-- Piping a query with ';' will replace the unnamed prepared statement
+\startpipeline
+SELECT $1 \parse ''
+SELECT 1;
+\bind_named ''
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+-- An extended query can be piped by a ';' after a newline
+\startpipeline
+SELECT $1 \bind 1
+;
+SELECT 2;
+\endpipeline
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+-- COPY FROM STDIN, using ';'
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\endpipeline
+ ?column?
+----------
+ val1
+(1 row)
+
+-- COPY FROM STDIN with \flushrequest + \getresults, using ';'
+\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, using ';'
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\syncpipeline
+\getresults
+ ?column?
+----------
+ val1
+(1 row)
+
+\endpipeline
+-- COPY TO STDOUT, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\endpipeline
+ ?column?
+----------
+ val1
+(1 row)
+
+1 \N
+2 test2
+3 test3
+4 test4
+20 test2
+30 test3
+40 test4
+-- COPY TO STDOUT with \flushrequest + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\flushrequest
+\getresults
+ ?column?
+----------
+ val1
+(1 row)
+
+1 \N
+2 test2
+3 test3
+4 test4
+20 test2
+30 test3
+40 test4
+\endpipeline
+-- COPY TO STDOUT with \syncpipeline + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\syncpipeline
+\getresults
+ ?column?
+----------
+ val1
+(1 row)
+
+1 \N
+2 test2
+3 test3
+4 test4
+20 test2
+30 test3
+40 test4
\endpipeline
--
-- Pipeline errors
@@ -393,18 +649,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 +669,13 @@ SELECT \bind 'val1' \sendpipeline
SELECT $1 \bind 'val1' \sendpipeline
\endpipeline
ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0
+-- Using ';' with a parameter will trigger an incorrect parameter error and
+-- abort 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 +686,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 within 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 +780,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 +867,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..6f76adcba82 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -210,6 +210,125 @@ SELECT $1 \bind 3 \sendpipeline
\getresults 0
\endpipeline
+--
+-- Tests pipelining queries with ';'
+--
+
+-- Single query sent with ';'
+\startpipeline
+SELECT 1;
+\endpipeline
+
+-- Multiple queries sent with ';'
+\startpipeline
+SELECT 1;
+SELECT 2;
+SELECT 3;
+\endpipeline
+
+-- Multiple queries on the same line can be piped with ';'
+\startpipeline
+SELECT 1; SELECT 2; SELECT 3
+;
+\endpipeline
+
+-- Test \flush with queries piped with ';'
+\startpipeline
+\flush
+SELECT 1;
+\flush
+SELECT 2;
+SELECT 3;
+\endpipeline
+
+-- Send multiple syncs with queries piped with ';'
+\startpipeline
+\echo :PIPELINE_COMMAND_COUNT
+\echo :PIPELINE_SYNC_COUNT
+\echo :PIPELINE_RESULT_COUNT
+SELECT 1;
+\syncpipeline
+\syncpipeline
+SELECT 2;
+\syncpipeline
+SELECT 3;
+\echo :PIPELINE_COMMAND_COUNT
+\echo :PIPELINE_SYNC_COUNT
+\echo :PIPELINE_RESULT_COUNT
+\endpipeline
+
+-- Mix queries piped with ';' and \sendpipeline
+\startpipeline
+SELECT 1;
+SELECT $1 \bind 'val1' \sendpipeline
+SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline
+SELECT 2;
+\endpipeline
+
+-- Piping a query with ';' will replace the unnamed prepared statement
+\startpipeline
+SELECT $1 \parse ''
+SELECT 1;
+\bind_named ''
+\endpipeline
+
+-- An extended query can be piped by a ';' after a newline
+\startpipeline
+SELECT $1 \bind 1
+;
+SELECT 2;
+\endpipeline
+
+-- COPY FROM STDIN, using ';'
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\endpipeline
+20 test2
+\.
+
+-- COPY FROM STDIN with \flushrequest + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\flushrequest
+\getresults
+30 test3
+\.
+\endpipeline
+
+-- COPY FROM STDIN with \syncpipeline + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+COPY psql_pipeline FROM STDIN;
+\syncpipeline
+\getresults
+40 test4
+\.
+\endpipeline
+
+-- COPY TO STDOUT, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\endpipeline
+
+-- COPY TO STDOUT with \flushrequest + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\flushrequest
+\getresults
+\endpipeline
+
+-- COPY TO STDOUT with \syncpipeline + \getresults, using ';'
+\startpipeline
+SELECT 'val1';
+copy psql_pipeline TO STDOUT;
+\syncpipeline
+\getresults
+\endpipeline
+
--
-- Pipeline errors
--
@@ -217,13 +336,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 +351,13 @@ SELECT \bind 'val1' \sendpipeline
SELECT $1 \bind 'val1' \sendpipeline
\endpipeline
+-- Using ';' with a parameter will trigger an incorrect parameter error and
+-- abort the pipeline
+\startpipeline
+SELECT $1;
+SELECT 1;
+\endpipeline
+
-- An explicit transaction with an error needs to be rollbacked after
-- the pipeline.
\startpipeline
@@ -248,7 +367,7 @@ ROLLBACK \bind \sendpipeline
\endpipeline
ROLLBACK;
--- \watch sends a simple query, something not allowed within a pipeline.
+-- \watch is not allowed within a pipeline.
\startpipeline
SELECT \bind \sendpipeline
\watch 1
@@ -372,8 +491,8 @@ select 1;
-- Error messages accumulate and are repeated.
\startpipeline
SELECT 1 \bind \sendpipeline
-SELECT 1;
-SELECT 1;
+\gdesc
+\gdesc
\endpipeline
--
--
2.39.5 (Apple Git-154)
view thread (41+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add Pipelining support in psql
In-Reply-To: <CAO6_Xqo=fNh0KCRZG7T18OZfjiabeL-t4C6mkXsz-3dAZ1YwTA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox