Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 28 May 2026 14:26:06 +0900
From: Michael Paquier <michael@paquier.xyz>
To: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19494: Error on transaction commit inside pipeline triggers
 psql's Assert
Message-ID: <ahfR7s1ZXC6MSC0i@paquier.xyz>
References: <19494-97a86d84fee71c47@postgresql.org>
 <ahe7ylLcESTmnZfk@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="niSFj3EAmbtQuAvl"
Content-Disposition: inline
In-Reply-To: <ahe7ylLcESTmnZfk@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/ahfR7s1ZXC6MSC0i%40paquier.xyz>
Precedence: bulk


--niSFj3EAmbtQuAvl
Content-Type: multipart/mixed; boundary="GmiEme0sUhGVvoa7"
Content-Disposition: inline


--GmiEme0sUhGVvoa7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, May 28, 2026 at 12:51:38PM +0900, Michael Paquier wrote:
> I am completely sure yet, but it looks like we will need to be smarter
> with the handling of the number of piped commands by tracking them
> across the syncs in the shape of a queue, or something like that?  So
> it feels like we need to think harder about the tracking of this
> activity depending on the state of the pipeline we're in.  Or we could
> lift some of these assertions, but that would not be right to me.

Hmm.  Taking a step back this would be overcomplicating things.  As
long as we are careful to consume the synced results still in a
pipeline, it looks like we should be fine.  While digging into it, I
have found a third assertion that was triggerable with
available_results at the end of the pipeline, once I began mixing
\getresults with a deferred error.

This stuff is tricky enough that I may not have overseen all the
patterns possible, of course, at least this is progress.

Alexander, what do you think?
--
Michael

--GmiEme0sUhGVvoa7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=0001-psql-Fix-failures-with-deferred-errors-in-pipelines.patch
Content-Transfer-Encoding: quoted-printable

=46rom f9ce93fc1e9bb47f4a50605a2b836bb9cc278989 Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
Date: Thu, 28 May 2026 14:20:47 +0900
Subject: [PATCH] psql: Fix failures with deferred errors in pipelines

---
 src/bin/psql/common.c                       | 48 ++++++++---
 src/test/regress/expected/psql_pipeline.out | 91 +++++++++++++++++++++
 src/test/regress/sql/psql_pipeline.sql      | 39 +++++++++
 3 files changed, 166 insertions(+), 12 deletions(-)

diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1a4e2ea0da82..3698f37c742c 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1499,11 +1499,19 @@ discardAbortedPipelineResults(void)
 		}
 		else if (res =3D=3D NULL)
 		{
-			/* A query was processed, decrement the counters */
-			Assert(pset.available_results > 0);
-			Assert(pset.requested_results > 0);
-			pset.available_results--;
-			pset.requested_results--;
+			/*
+			 * A query was processed, decrement the counters.
+			 *
+			 * It is possible to get here with available_results =3D=3D 0 when an
+			 * error is generated by the Sync message processing itself.
+			 * Such errors are not counted in available_results because they
+			 * are not associated with a piped command.  In that case, skip
+			 * the counter decrements and continue to find the sync result.
+			 */
+			if (pset.available_results > 0)
+				pset.available_results--;
+			if (pset.requested_results > 0)
+				pset.requested_results--;
 		}
=20
 		if (pset.requested_results =3D=3D 0)
@@ -2175,14 +2183,30 @@ ExecQueryAndProcessResults(const char *query,
=20
 	if (end_pipeline)
 	{
-		/* after a pipeline is processed, pipeline piped_syncs should be 0 */
-		Assert(pset.piped_syncs =3D=3D 0);
-		/* all commands have been processed */
-		Assert(pset.piped_commands =3D=3D 0);
-		/* all results were read */
-		Assert(pset.available_results =3D=3D 0);
+		/*
+		 * Reset available/requested results.  Normally these are already 0,
+		 * but an error generated by Sync processing itself can leave some of
+		 * them behind.  Consume them before exiting pipeline mode.
+		 */
+		while (pset.piped_syncs > 0)
+		{
+			PGresult   *remaining =3D PQgetResult(pset.db);
+
+			if (remaining =3D=3D NULL)
+				continue;
+			if (PQresultStatus(remaining) =3D=3D PGRES_PIPELINE_SYNC)
+				pset.piped_syncs--;
+			PQclear(remaining);
+		}
+		pset.piped_syncs =3D 0;
+		pset.piped_commands =3D 0;
+		pset.available_results =3D 0;
+		pset.requested_results =3D 0;
+
+		if (PQpipelineStatus(pset.db) !=3D PQ_PIPELINE_OFF)
+			PQexitPipelineMode(pset.db);
 	}
-	Assert(pset.requested_results =3D=3D 0);
+
 	SetPipelineVariables();
=20
 	/* may need this to recover from conn loss during COPY */
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress=
/expected/psql_pipeline.out
index a0816fb10b68..66e1e4f2ef5e 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -764,5 +764,96 @@ VACUUM psql_pipeline \bind \sendpipeline
         1
 (1 row)
=20
+-- Deferred constraint violation at commit time in a pipeline.
+CREATE TABLE psql_pipeline_defer (a INTEGER PRIMARY KEY DEFERRABLE INITIAL=
LY DEFERRED);
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) RETURNING * \bind 1 \sen=
dpipeline
+\endpipeline
+ a=20
+---
+ 1
+ 1
+(2 rows)
+
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defe=
r_pkey"
+DETAIL:  Key (a)=3D(1) already exists.
+-- Same with \syncpipeline and commands after the failing sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\endpipeline
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defe=
r_pkey"
+DETAIL:  Key (a)=3D(1) already exists.
+   ?column?  =20
+--------------
+ after_sync_1
+(1 row)
+
+-- More patterns with more \syncpipeline, more commands and \getresults
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\getresults
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defe=
r_pkey"
+DETAIL:  Key (a)=3D(1) already exists.
+SELECT $1 \bind 'after_sync_2' \sendpipeline
+\endpipeline
+   ?column?  =20
+--------------
+ after_sync_1
+(1 row)
+
+   ?column?  =20
+--------------
+ after_sync_2
+(1 row)
+
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+\getresults
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defe=
r_pkey"
+DETAIL:  Key (a)=3D(1) already exists.
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\getresults
+SELECT $1 \bind 'after_sync_2' \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'after_sync_3' \sendpipeline
+SELECT $1 \bind 'after_sync_4' \sendpipeline
+SELECT $1 \bind 'after_sync_5' \sendpipeline
+\endpipeline
+   ?column?  =20
+--------------
+ after_sync_1
+(1 row)
+
+   ?column?  =20
+--------------
+ after_sync_2
+(1 row)
+
+   ?column?  =20
+--------------
+ after_sync_3
+(1 row)
+
+   ?column?  =20
+--------------
+ after_sync_4
+(1 row)
+
+   ?column?  =20
+--------------
+ after_sync_5
+(1 row)
+
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defe=
r_pkey"
+DETAIL:  Key (a)=3D(1) already exists.
+DROP TABLE psql_pipeline_defer;
 -- Clean up
 DROP TABLE psql_pipeline;
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/=
psql_pipeline.sql
index 6788dceee2e9..ec68e48e25aa 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -438,5 +438,44 @@ SELECT 1 \bind \sendpipeline
 VACUUM psql_pipeline \bind \sendpipeline
 \endpipeline
=20
+-- Deferred constraint violation at commit time in a pipeline.
+CREATE TABLE psql_pipeline_defer (a INTEGER PRIMARY KEY DEFERRABLE INITIAL=
LY DEFERRED);
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) RETURNING * \bind 1 \sen=
dpipeline
+\endpipeline
+
+-- Same with \syncpipeline and commands after the failing sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\endpipeline
+
+-- More patterns with more \syncpipeline, more commands and \getresults
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\getresults
+SELECT $1 \bind 'after_sync_2' \sendpipeline
+\endpipeline
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+\getresults
+SELECT $1 \bind 'after_sync_1' \sendpipeline
+\getresults
+SELECT $1 \bind 'after_sync_2' \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'after_sync_3' \sendpipeline
+SELECT $1 \bind 'after_sync_4' \sendpipeline
+SELECT $1 \bind 'after_sync_5' \sendpipeline
+\endpipeline
+
+DROP TABLE psql_pipeline_defer;
+
 -- Clean up
 DROP TABLE psql_pipeline;
--=20
2.54.0


--GmiEme0sUhGVvoa7--

--niSFj3EAmbtQuAvl
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmoX0e4ACgkQnvQgOdby
QH3luQ/9EP79NiPQTrZJJ5nACZn3Oe6ikSJXA7LSdNM34c4YPi2QMi+I9LbggUIv
LTVDlrYijBJErfw5Za04a3bJPDVpzvtoWMjnNLet0m5KQNgVRIFrMhvdkLyyvl+J
lMKPPaDqrMswUObjLTnsyxdZ/OJtw+rCmUhsPJOF+khl+9oFbnreZJgo4XFd06TF
ZPe5megTb0wWBl2Z8eTQNDJV8Q9vqQ0zWVzdXpbIPTFAGS2DOTWDdgIu/VLh5M0z
Hw0UB8GlAkFdIlH4heFRKJBt9aqQFH9XDaoIh5OgBeYr1hlEwo0pWZGKhT8Cj3bo
B9fsD7B/kan+NDmUog8Vz9CRGwQirNh9wdgj+XPjmcaD2leb1vs1l78lhYjk0ysG
BU2Dek9S1s5Hq8AzZMSfTmHwbVf1IO19/pg682mpwX6rlYOUoh6Ma3M/lRg0b6l3
6QgHu8O2WJmCPY3ztg2qn0oSq0vRf1uQqX7q2kx27lgu0l+A5YjR4pnOlMaCToH+
XgMq1kqcgEpGtyFAqUjF5bNyW6I9iGb9IYL7HpgBl9mUrYRTnrOWzmFa1J0jKiy+
xSu5xcEuE7VQsPRjq7yEuBBloKfcKiYZZoOWPUSYgLcl6KKsEE+SCKyLJaaZdsiq
sxoc7p+Zs/fP65GsAHaTnzV4NtTMOT/54URp2IxQzTbbPdou8yQ=
=9ei+
-----END PGP SIGNATURE-----

--niSFj3EAmbtQuAvl--