Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tlIrx-00Aqoq-Np for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Feb 2025 02:34:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tlIrv-00HYE7-9K for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Feb 2025 02:34:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tlIrt-00HYDz-US for pgsql-hackers@lists.postgresql.org; Fri, 21 Feb 2025 02:34:23 +0000 Received: from fout-b4-smtp.messagingengine.com ([202.12.124.147]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tlIrn-0024M8-0t for pgsql-hackers@postgresql.org; Fri, 21 Feb 2025 02:34:21 +0000 Received: from phl-compute-13.internal (phl-compute-13.phl.internal [10.202.2.53]) by mailfout.stl.internal (Postfix) with ESMTP id 4DF2811400D9; Thu, 20 Feb 2025 21:34:12 -0500 (EST) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-13.internal (MEProxy); Thu, 20 Feb 2025 21:34:12 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1740105252; x=1740191652; bh=cqm0yOml8M WejXx59vp1S12bIXTvwfteV1c+3SbCrl8=; b=cToMKtRM5F/BjZqqND9VDDLYIx Eox0yLxgdW/xIuBy9c6+FPby3EhGZsSzYNRtM+YppbxGMcXJoJcs1vtkzxbxkOvA ldZDiI2iscBDmYfjmmlyYFevUTlUhzOKoyV9cvkIRVjScD+gwaD3/HvLIoFazitC 0Shx/Hi/fXXv9NcdB4yPb2rqgTE6C9Ay0eIDKnrBAoy3UuT6qFP3x8G0oolvjD7R ajSJTuoogWzPQ7paQKCG3DTDd2VXGVR1W/4gFkS4oZw4f+I2YuSCGArXc0nmBChW 2l+/4tQEA3LDRmciioD70EXN/u6iXLqwkDNAErETGgNv06EguYhJfLeMIW0g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1740105252; x=1740191652; bh=cqm0yOml8MWejXx59vp1S12bIXTvwfteV1c +3SbCrl8=; b=KxcBys2evL4FRzRbY0qQ295AlR+GZ2R9Fll4yHzstMHWEFv2iBz C70MUYzzY9nGtMgKdujDymQDRf0jvl2Jij1y0aVozl2LZdleq4BClycgBm1NCLbc iZkT+nW0IRDHQOrQ4i8ONzH/4oMu/6x71HiNM4q0+ZQMJrMXRLUm5HNlmJWnYXUy s8XITNP9N8dV4WVY40M094blh18FPyfgmPQRf5SfmPiXqYRW2RuPWYORfzm77unb 4IiOAEseHsS2ev2VvFwIG96ZE55z0HN70Qa4wPkkS7YclwjHhxHNucTablpnAFl8 QQgWHVrY29a1HCUV0jm+oy6kf5W2cRpjXag== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefvddrtddtgdeikeekvdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdp uffrtefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivg hnthhsucdlqddutddtmdenfghrlhcuvffnffculdejtddmnecujfgurhepfffhvfevuffk fhggtggujgesghdtreertddtvdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrh cuoehmihgthhgrvghlsehprghquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeet leeifedufffhhfdtteelgeeggeffhfekueevteeigfduudevudetgfegiedvjeenucevlh hushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghl sehprghquhhivghrrdighiiipdhnsggprhgtphhtthhopeefpdhmohguvgepshhmthhpoh huthdprhgtphhtthhopegrnhhthhhonhhinhdrsghonhhnvghfohihsegurghtrgguohhg hhhqrdgtohhmpdhrtghpthhtohepphhoshhtghhrvghssehjvghlthgvfhdrnhhlpdhrtg hpthhtohepphhgshhqlhdqhhgrtghkvghrshesphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 20 Feb 2025 21:34:09 -0500 (EST) Date: Fri, 21 Feb 2025 11:33:41 +0900 From: Michael Paquier To: Anthonin Bonnefoy Cc: Jelte Fennema-Nio , PostgreSQL Hackers Subject: Re: Add Pipelining support in psql Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="emYTyFBg7w2MJMAu" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --emYTyFBg7w2MJMAu Content-Type: multipart/mixed; boundary="xK4qxFCFrY6+uce7" Content-Disposition: inline --xK4qxFCFrY6+uce7 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Feb 20, 2025 at 10:29:33AM +0100, Anthonin Bonnefoy wrote: > Ha yeah, I forgot about the inactive branches. I've added the new > 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. > 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. 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.=20 - Prompt support, with more edits. I'm putting these on standby for a few days, to let the buildfarm digest the main change. -- Michael --xK4qxFCFrY6+uce7 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v10-0001-Add-tests-with-implicit-transaction-blocks-and-p.patch" Content-Transfer-Encoding: quoted-printable =46rom 035f19d120b181094b00dee21acb0b4ce4cfc1e1 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier 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/exp= ected/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) =20 +-- +-- 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=3D'1h' \bind \g +SHOW statement_timeout \bind \g +\syncpipeline +SHOW statement_timeout \bind \g +SET LOCAL statement_timeout=3D'2h' \bind \g +SHOW statement_timeout \bind \g +\endpipeline +WARNING: SET LOCAL can only be used in transaction blocks + statement_timeout=20 +------------------- + 1h +(1 row) + + statement_timeout=20 +------------------- + 0 +(1 row) + + statement_timeout=20 +------------------- + 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?=20 +---------- + 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?=20 +---------- + 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?=20 +---------- + 1 +(1 row) + + ?column?=20 +---------- + 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?=20 +---------- + 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?=20 +---------- + 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 =20 +-- +-- 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=3D'1h' \bind \g +SHOW statement_timeout \bind \g +\syncpipeline +SHOW statement_timeout \bind \g +SET LOCAL statement_timeout=3D'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; --=20 2.47.2 --xK4qxFCFrY6+uce7 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v10-0002-Add-prompt-interpolation-and-variables-for-psql-.patch" Content-Transfer-Encoding: quoted-printable =46rom b183638bb31c75fab28ecf67112304b52bb12dce Mon Sep 17 00:00:00 2001 =46rom: Anthonin Bonnefoy 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) } =20 =20 +/* + * 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, =20 CheckConnection(); =20 + SetPipelineVariables(); + return -1; } =20 @@ -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; } =20 @@ -2105,6 +2129,7 @@ ExecQueryAndProcessResults(const char *query, Assert(pset.available_results =3D=3D 0); } Assert(pset.requested_results =3D=3D 0); + SetPipelineVariables(); =20 /* 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 cst= ack) snprintf(buf, sizeof(buf), "%d", pid); } break; + /* pipeline status */ + case 'P': + { + PGpipelineStatus status =3D PQpipelineStatus(pset.db); + + if (status =3D=3D PQ_PIPELINE_ON) + strlcpy(buf, "on", sizeof(buf)); + else if (status =3D=3D PQ_PIPELINE_ABORTED) + strlcpy(buf, "abort", sizeof(buf)); + else + strlcpy(buf, "off", sizeof(buf)); + break; + } =20 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"); =20 + /* 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); =20 /* 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=3D> \setenv LESS -imx4F generate one result to get. =20 + + When pipeline mode is active, a dedicated prompt variable is avail= able + to report the pipeline status. + See for more details + + Example: @@ -4501,6 +4507,39 @@ bar =20 + + PIPELINE_COMMAND_COUNT + + + The number of commands generated by \bind, + \bind_named, \close or + \parse queued in an ongoing pipeline. + + + + + + PIPELINE_RESULT_COUNT + + + The number of commands of an ongoing pipeline that were followed + by either a \flushrequest or a + \syncpipeline, forcing the server to send the + results. These results can be retrieved with + \getresults. + + + + + + PIPELINE_SYNC_COUNT + + + The number of sync messages queued in an ongoing pipeline. + + + + PORT @@ -4900,6 +4939,17 @@ testdb=3D> INSERT INTO my_table VALUES= (:'content'); =20 + + %P + + + Pipeline status: off when not in a pipeline, + on when in an ongoing pipeline or + abort when in an aborted pipeline. + + + + %R --=20 2.47.2 --xK4qxFCFrY6+uce7-- --emYTyFBg7w2MJMAu Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAme35gUACgkQnvQgOdby QH09pQ/8DtsLGMFAyAv1K39zYRitbyc7xr/k2PT1zU8enkQGT9KPejmjPkZyaNfi YPbuhBYpYCgjZVT4xW4vjdGXPYeleR+8O0haO/+fgMBhCuljImd1TlBcosKKleaN 1+1jzB0OkN02GUoN3K1st3Io1cYpXeeIYPhtZXO8qu7yBTuT3DiVSx0+EZTHeWxi tjykUiuQvYPN+GgtzOsdqKMzAq/yKz/AoHCUv4+G8uBnpjNn8ebTakKJNHXkzEWR ESliYWMNJnCYwEMvw3ClBWHEyZ4Ic8YbXZ9X07F30q2wE+kAoEqxtFqPSw6DK6qx jwlZFpj9VnneLNqrLTAp+oR6901mIXoUKKCOr/M+FjCFHHXczq2BKCIt8XnL4Ggb xLMLCBDSeJF6SHd5/AHOPsmOz8TWSHgymUJibgc3QPlC244c1wRWkEHJKnBeols6 LYZRFHlhYL5wj3W4KE0kge0dkfyfQxG0rcy71D7zI+nIx2wiMS3DJmp8ZdNwhPpE TBhphJJHeG+C7qMYdkPzfjfOSJKe/5vNJ6+EI1E0q/1iBcg9bplaVtjqyM0UvhS8 mrXjgWP21fYbgQ4iGGA9GRWJN0Os/1Gn3eflg296VHskPS2OoVkVO7SB4fd82kWw NMZsBUZL0Q1KhznuNhzNZhH715kGmAvRks0V+yMhcZmmlKuWZV8= =JQqk -----END PGP SIGNATURE----- --emYTyFBg7w2MJMAu--