public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Paquier <[email protected]>
To: Alexander Lakhin <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19494: Error on transaction commit inside pipeline triggers psql's Assert
Date: Mon, 1 Jun 2026 11:11:52 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Sun, May 31, 2026 at 12:00:01PM +0300, Alexander Lakhin wrote:
> I've found a way to trigger another assertion, but I don't think it's
> legitimate:
> --- a/src/backend/libpq/pqcomm.c
> +++ b/src/backend/libpq/pqcomm.c
> @@ -880,7 +880,7 @@ RemoveSocketFiles(void)
> static void
> socket_set_nonblocking(bool nonblocking)
> {
> - if (MyProcPort == NULL)
> + if ((MyProcPort == NULL) || (rand() % 10 == 0))
> ereport(ERROR,
> (errcode(ERRCODE_CONNECTION_DOES_NOT_EXIST),
> errmsg("there is no client connection")));
Server-side error injection. Noice.
> trigger
> psql: common.c:2055: ExecQueryAndProcessResults: Assertion
> `pset.piped_syncs > 0' failed.
This one would be in the same spirit as the others, if we cannot
really guarantee that the counters will be correct all the time we can
just more more defensive. That's an error thrown while the sync
message is processing itself, causing piped_syncs to get out of step.
> Probably there could be another way to throw an ERROR on \syncpipeline,
> but I have no good idea yet.
There is one challenge here, as far as I can see: libpq does not
really offer a way to make the difference between this thrown error
and an error that comes from a Sync, so it seems like we cannot do
much on the psql side except be more defensive? I am not sure if this
is worth the extra facility in libpq, the point would be moot in the
back branches anyway. And there is a benefit in keeping the psql code
as simple as possible, as well, so I'd tend to keep it more useful
still simpler.
> Running psql_pipeline in a loop with the above modification applied:
> for i in {1..1000}; do echo "ITERATION $i"; NO_TEMP_INSTALL=1 TESTS=psql_pipeline make -s check-tests; done
> I also observed the test hanging (at iterations 284. 543, 218) due to loss
> of synchronization between psql and postgres.
I have looked at that as well, and I don't think that this is fixable
only from the point of psql, because the error injected creates a
state where libpq's internal command queue gets out of sync regarding
what the backend has sent. The only thing that could be done is
inside libpq, as far as I can see, where we should try to detect that
the state is not synchronized anymore and fail rather than block. So
IMO, and with the error injected (which would never happen in
production in practice), the best thing I can come up with is the
attached for now.
One thing that I could see ourselves do as an extra improvement in
ExecQueryAndProcessResults() where we consume the results and check if
we're still in a busy state (some PQconsumeInput+PQisBusy). I don't
think that this should be a problem in practice, but this feels like
just hiding the real problem on the libpq side with the inconsistent
protocol state generated by the backend. I have also quickly tested
an approach based on that, unfortunately this leads to some
instability in the tests to due the async nature of the commands.
Anyway, the v3 attached passes the regression tests, handles the
pg_terminate_backend() case gracefully, handles the error case with
the error injected on backend-side a but better, and can avoid
some of the issues in the fourth case, but not all as we don't have
access to the pipe state when reaching the results do to the backend
missing up with the libpq state. Handling the 4th case more
gracefully would require some libpq changes, which may not justify the
cases we are dealing with here, at least to me. As a whole, I'd feel
that v3 is a good improvement in itself, and it addresses your
original issues and the assertions.
What do you think?
--
Michael
From 7fe4b3adf207f08d7a7bac2bc25521096959aa5e Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Mon, 1 Jun 2026 10:58:09 +0900
Subject: [PATCH v3] psql: Fix issues with deferred errors in pipelines
When an error is raised while processing a Sync message in a pipeline, e
like a deferred constraint violation, the error was not associated with
the piped command and was not counted in available_results. This caused
assertion failures in discardAbortedPipelineResults(), keeping an
incorrect state at pipeline exit, because the code assumed that
the number of available and requested results would always be positive,
expecting all the counters to be 0 at the end of a pipeline.
This commit switches discardAbortedPipelineResults() and
ExecQueryAndProcessResults() to take a softer approach when consuming
and draining the results after an error.
The reporter has shown a couple of assertion failures reachable. While
investigating more this issue I have bumped into two more. All these
cases are covered by the regression tests added in this commit, plus
some bonuses.
Reported-by: Alexander Lakhin <[email protected]>
Author: Michael Paquier <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 18
---
src/bin/psql/common.c | 76 +++++++++---
src/test/regress/expected/psql_pipeline.out | 124 ++++++++++++++++++++
src/test/regress/sql/psql_pipeline.sql | 63 ++++++++++
3 files changed, 245 insertions(+), 18 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1a4e2ea0da82..13202a974d1e 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1499,11 +1499,24 @@ 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 the connection has been lost, there will never be any more
+ * results to read, so bail out.
+ */
+ if (!ConnectionUp())
+ return NULL;
+ if (pset.available_results > 0)
+ pset.available_results--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
}
if (pset.requested_results == 0)
@@ -2044,14 +2057,16 @@ ExecQueryAndProcessResults(const char *query,
if (result_status == PGRES_PIPELINE_SYNC)
{
- Assert(pset.piped_syncs > 0);
-
/*
* Sync response, decrease the sync and requested_results
- * counters.
+ * counters. Guard against underflow: an error during Sync
+ * processing on the server can cause the client-side counter
+ * to drift.
*/
- pset.piped_syncs--;
- pset.requested_results--;
+ if (pset.piped_syncs > 0)
+ pset.piped_syncs--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
/*
* After a synchronisation point, reset success state to print
@@ -2073,8 +2088,10 @@ ExecQueryAndProcessResults(const char *query,
* In a pipeline with a non-sync response? Decrease the result
* counters.
*/
- pset.available_results--;
- pset.requested_results--;
+ if (pset.available_results > 0)
+ pset.available_results--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
}
/*
@@ -2175,14 +2192,37 @@ 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 a Sync processing itself can leave some
+ * of them behind. Consume them before exiting pipeline mode.
+ */
+ while (pset.piped_syncs > 0)
+ {
+ PGresult *remaining;
+
+ remaining = PQgetResult(pset.db);
+
+ if (remaining == NULL)
+ {
+ if (!ConnectionUp())
+ break;
+ 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..a931d63cafe7 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -764,5 +764,129 @@ 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.
+-- Deferred error combined with a regular command error after the sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind \sendpipeline
+SELECT $1 \bind 'after_error' \sendpipeline
+\endpipeline
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1
+-- Empty sync segment followed by a deferred error.
+\startpipeline
+\syncpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\endpipeline
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+-- Deferred error with \getresults reading results one at a time.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'partial' \sendpipeline
+\syncpipeline
+\getresults 1
+\getresults 1
+ ?column?
+----------
+ partial
+(1 row)
+
+\getresults
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+\endpipeline
+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..468ef1d090b6 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -438,5 +438,68 @@ 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
+
+-- Deferred error combined with a regular command error after the sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind \sendpipeline
+SELECT $1 \bind 'after_error' \sendpipeline
+\endpipeline
+
+-- Empty sync segment followed by a deferred error.
+\startpipeline
+\syncpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\endpipeline
+
+-- Deferred error with \getresults reading results one at a time.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'partial' \sendpipeline
+\syncpipeline
+\getresults 1
+\getresults 1
+\getresults
+\endpipeline
+
+DROP TABLE psql_pipeline_defer;
+
-- Clean up
DROP TABLE psql_pipeline;
--
2.54.0
Attachments:
[text/plain] v3-0001-psql-Fix-issues-with-deferred-errors-in-pipelines.patch (11.5K, 2-v3-0001-psql-Fix-issues-with-deferred-errors-in-pipelines.patch)
download | inline diff:
From 7fe4b3adf207f08d7a7bac2bc25521096959aa5e Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Mon, 1 Jun 2026 10:58:09 +0900
Subject: [PATCH v3] psql: Fix issues with deferred errors in pipelines
When an error is raised while processing a Sync message in a pipeline, e
like a deferred constraint violation, the error was not associated with
the piped command and was not counted in available_results. This caused
assertion failures in discardAbortedPipelineResults(), keeping an
incorrect state at pipeline exit, because the code assumed that
the number of available and requested results would always be positive,
expecting all the counters to be 0 at the end of a pipeline.
This commit switches discardAbortedPipelineResults() and
ExecQueryAndProcessResults() to take a softer approach when consuming
and draining the results after an error.
The reporter has shown a couple of assertion failures reachable. While
investigating more this issue I have bumped into two more. All these
cases are covered by the regression tests added in this commit, plus
some bonuses.
Reported-by: Alexander Lakhin <[email protected]>
Author: Michael Paquier <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 18
---
src/bin/psql/common.c | 76 +++++++++---
src/test/regress/expected/psql_pipeline.out | 124 ++++++++++++++++++++
src/test/regress/sql/psql_pipeline.sql | 63 ++++++++++
3 files changed, 245 insertions(+), 18 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1a4e2ea0da82..13202a974d1e 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1499,11 +1499,24 @@ 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 the connection has been lost, there will never be any more
+ * results to read, so bail out.
+ */
+ if (!ConnectionUp())
+ return NULL;
+ if (pset.available_results > 0)
+ pset.available_results--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
}
if (pset.requested_results == 0)
@@ -2044,14 +2057,16 @@ ExecQueryAndProcessResults(const char *query,
if (result_status == PGRES_PIPELINE_SYNC)
{
- Assert(pset.piped_syncs > 0);
-
/*
* Sync response, decrease the sync and requested_results
- * counters.
+ * counters. Guard against underflow: an error during Sync
+ * processing on the server can cause the client-side counter
+ * to drift.
*/
- pset.piped_syncs--;
- pset.requested_results--;
+ if (pset.piped_syncs > 0)
+ pset.piped_syncs--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
/*
* After a synchronisation point, reset success state to print
@@ -2073,8 +2088,10 @@ ExecQueryAndProcessResults(const char *query,
* In a pipeline with a non-sync response? Decrease the result
* counters.
*/
- pset.available_results--;
- pset.requested_results--;
+ if (pset.available_results > 0)
+ pset.available_results--;
+ if (pset.requested_results > 0)
+ pset.requested_results--;
}
/*
@@ -2175,14 +2192,37 @@ 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 a Sync processing itself can leave some
+ * of them behind. Consume them before exiting pipeline mode.
+ */
+ while (pset.piped_syncs > 0)
+ {
+ PGresult *remaining;
+
+ remaining = PQgetResult(pset.db);
+
+ if (remaining == NULL)
+ {
+ if (!ConnectionUp())
+ break;
+ 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..a931d63cafe7 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -764,5 +764,129 @@ 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.
+-- Deferred error combined with a regular command error after the sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind \sendpipeline
+SELECT $1 \bind 'after_error' \sendpipeline
+\endpipeline
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1
+-- Empty sync segment followed by a deferred error.
+\startpipeline
+\syncpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\endpipeline
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+-- Deferred error with \getresults reading results one at a time.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'partial' \sendpipeline
+\syncpipeline
+\getresults 1
+\getresults 1
+ ?column?
+----------
+ partial
+(1 row)
+
+\getresults
+ERROR: duplicate key value violates unique constraint "psql_pipeline_defer_pkey"
+DETAIL: Key (a)=(1) already exists.
+\endpipeline
+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..468ef1d090b6 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -438,5 +438,68 @@ 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
+
+-- Deferred error combined with a regular command error after the sync.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\syncpipeline
+SELECT $1 \bind \sendpipeline
+SELECT $1 \bind 'after_error' \sendpipeline
+\endpipeline
+
+-- Empty sync segment followed by a deferred error.
+\startpipeline
+\syncpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+\endpipeline
+
+-- Deferred error with \getresults reading results one at a time.
+\startpipeline
+INSERT INTO psql_pipeline_defer VALUES ($1), ($1) \bind 1 \sendpipeline
+SELECT $1 \bind 'partial' \sendpipeline
+\syncpipeline
+\getresults 1
+\getresults 1
+\getresults
+\endpipeline
+
+DROP TABLE psql_pipeline_defer;
+
-- Clean up
DROP TABLE psql_pipeline;
--
2.54.0
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
view thread (10+ 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]
Subject: Re: BUG #19494: Error on transaction commit inside pipeline triggers psql's Assert
In-Reply-To: <[email protected]>
* 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