0001-psql-Fix-failures-with-deferred-errors-in-pipelines.patch

From f9ce93fc1e9bb47f4a50605a2b836bb9cc278989 Mon Sep 17 00:00:00 2001
From: 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 == 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 == 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--;
 		}
 
 		if (pset.requested_results == 0)
@@ -2175,14 +2183,30 @@ ExecQueryAndProcessResults(const char *query,
 
 	if (end_pipeline)
 	{
-		/* after a pipeline is processed, pipeline piped_syncs should be 0 */
-		Assert(pset.piped_syncs == 0);
-		/* all commands have been processed */
-		Assert(pset.piped_commands == 0);
-		/* all results were read */
-		Assert(pset.available_results == 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 = PQgetResult(pset.db);
+
+			if (remaining == NULL)
+				continue;
+			if (PQresultStatus(remaining) == PGRES_PIPELINE_SYNC)
+				pset.piped_syncs--;
+			PQclear(remaining);
+		}
+		pset.piped_syncs = 0;
+		pset.piped_commands = 0;
+		pset.available_results = 0;
+		pset.requested_results = 0;
+
+		if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
+			PQexitPipelineMode(pset.db);
 	}
-	Assert(pset.requested_results == 0);
+
 	SetPipelineVariables();
 
 	/* 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)
 
+-- Deferred constraint violation at commit time in a pipeline.
+CREATE TABLE psql_pipeline_defer (a INTEGER PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) RETURNING * \bind 1 \sendpipeline
+\endpipeline
+ a 
+---
+ 1
+ 1
+(2 rows)
+
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL:  Key (a)=(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_defer_pkey"
+DETAIL:  Key (a)=(1) already exists.
+   ?column?   
+--------------
+ 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_defer_pkey"
+DETAIL:  Key (a)=(1) already exists.
+SELECT $1 \bind 'after_sync_2' \sendpipeline
+\endpipeline
+   ?column?   
+--------------
+ after_sync_1
+(1 row)
+
+   ?column?   
+--------------
+ 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_defer_pkey"
+DETAIL:  Key (a)=(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?   
+--------------
+ after_sync_1
+(1 row)
+
+   ?column?   
+--------------
+ after_sync_2
+(1 row)
+
+   ?column?   
+--------------
+ after_sync_3
+(1 row)
+
+   ?column?   
+--------------
+ after_sync_4
+(1 row)
+
+   ?column?   
+--------------
+ after_sync_5
+(1 row)
+
+ERROR:  duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL:  Key (a)=(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
 
+-- Deferred constraint violation at commit time in a pipeline.
+CREATE TABLE psql_pipeline_defer (a INTEGER PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) RETURNING * \bind 1 \sendpipeline
+\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;
-- 
2.54.0