pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18944: Assertion Failure in psql with idle_session_timeout Set
13+ messages / 4 participants
[nested] [flat]
* BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-02 06:34 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 13+ messages in thread
From: PG Bug reporting form @ 2025-06-02 06:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: n.kalinin@postgrespro.ru
The following bug has been logged on the website:
Bug reference: 18944
Logged by: Nikita Kalinin
Email address: n.kalinin@postgrespro.ru
PostgreSQL version: 18beta1
Operating system: ubuntu 22.04
Description:
Hello! When I execute the following query:
psql <<EOF
CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT);
\startpipeline
COPY psql_pipeline FROM STDIN;
\syncpipeline
\endpipeline
EOF
with `idle_session_timeout` set to, for example, 10000, I get the following
error:
FATAL: terminating connection due to idle-session timeout
psql: common.c:1501: discardAbortedPipelineResults: Assertion
`pset.available_results > 0' failed.
[1] 1747708 abort (core dumped) psql <<<''
Coredump:
#0 __pthread_kill_implementation (threadid=278301224788256,
signo=signo@entry=6, no_tid=no_tid@entry=0) at ./nptl/pthread_kill.c:44
#1 0x0000fd1d0d757670 in __pthread_kill_internal (signo=6,
threadid=<optimized out>) at ./nptl/pthread_kill.c:78
#2 0x0000fd1d0d70cb3c in __GI_raise (sig=sig@entry=6) at
../sysdeps/posix/raise.c:26
#3 0x0000fd1d0d6f7e00 in __GI_abort () at ./stdlib/abort.c:79
#4 0x0000fd1d0d705cc0 in __assert_fail_base (fmt=0xfd1d0d82c770 "%s%s%s:%u:
%s%sAssertion `%s' failed.\n%n",
assertion=assertion@entry=0xb9a5a2d96080 "pset.available_results > 0",
file=file@entry=0xb9a5a2d95fa0 "common.c", line=line@entry=1501,
function=function@entry=0xb9a5a2dc4138 <__PRETTY_FUNCTION__.3>
"discardAbortedPipelineResults") at ./assert/assert.c:96
#5 0x0000fd1d0d705d30 in __assert_fail
(assertion=assertion@entry=0xb9a5a2d96080 "pset.available_results > 0",
file=file@entry=0xb9a5a2d95fa0 "common.c", line=line@entry=1501,
function=function@entry=0xb9a5a2dc4138 <__PRETTY_FUNCTION__.3>
"discardAbortedPipelineResults") at ./assert/assert.c:105
#6 0x0000b9a5a2d3ec10 in discardAbortedPipelineResults () at common.c:1501
#7 ExecQueryAndProcessResults (query=query@entry=0xb9a5bfbe5690 "COPY
psql_pipeline FROM STDIN;",
elapsed_msec=elapsed_msec@entry=0xffffe844e8a0,
svpt_gone_p=svpt_gone_p@entry=0xffffe844e89f, is_watch=is_watch@entry=false,
min_rows=min_rows@entry=0, opt=opt@entry=0x0,
printQueryFout=printQueryFout@entry=0x0) at common.c:1828
#8 0x0000b9a5a2d3cc20 in SendQuery (query=0xb9a5bfbe5690 "COPY
psql_pipeline FROM STDIN;") at common.c:1212
#9 0x0000b9a5a2d52c14 in MainLoop (source=source@entry=0xfd1d0d880880
<_IO_2_1_stdin_>) at mainloop.c:515
#10 0x0000b9a5a2d375b0 in process_file (filename=0x0,
use_relative_path=use_relative_path@entry=false) at command.c:4870
#11 0x0000b9a5a2d31bc4 in main (argc=<optimized out>, argv=<optimized out>)
at startup.c:420
It appears to be a continuation of this thread:
https://www.postgresql.org/message-id/ebf6ce77-b180-4d6b-8eab-71f641499ddf@postgrespro.ru
--
Nikita Kalinin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-02 08:13 Michael Paquier <michael@paquier.xyz>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2025-06-02 08:13 UTC (permalink / raw)
To: n.kalinin@postgrespro.ru; pgsql-bugs@lists.postgresql.org; +Cc: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
On Mon, Jun 02, 2025 at 06:34:12AM +0000, PG Bug reporting form wrote:
> Hello! When I execute the following query:
> psql <<EOF
> CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT);
> \startpipeline
> COPY psql_pipeline FROM STDIN;
> \syncpipeline
> \endpipeline
> EOF
> with `idle_session_timeout` set to, for example, 10000, I get the following
> error:
> FATAL: terminating connection due to idle-session timeout
> psql: common.c:1501: discardAbortedPipelineResults: Assertion
> `pset.available_results > 0' failed.
Reproduced here, thanks for the report. In this case, we are in a
PGRES_FATAL_ERROR state due to the timeout triggered by the backend,
and the loop discarding the results in discardAbortedPipelineResults()
does not take into account this case scenario: we don't have a result
to process yet, think that there is one, and kaboom. It seems to me
that we should just add an extra case for the result = NULL case as
there is nothing pending. A FATAL means that we need to give up
anyway.
Anthonin, what do you think?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aD1dPMIZzAwMKm7G@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-02 17:00 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-02 17:00 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: n.kalinin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Mon, Jun 2, 2025 at 10:13 AM Michael Paquier <michael@paquier.xyz> wrote:
> Reproduced here, thanks for the report. In this case, we are in a
> PGRES_FATAL_ERROR state due to the timeout triggered by the backend,
> and the loop discarding the results in discardAbortedPipelineResults()
> does not take into account this case scenario: we don't have a result
> to process yet, think that there is one, and kaboom. It seems to me
> that we should just add an extra case for the result = NULL case as
> there is nothing pending. A FATAL means that we need to give up
> anyway.
>
> Anthonin, what do you think?
Also reproduced. On a cursory look (I should be able to dig into this
deeper tomorrow), it seems like we're missing a ReadyForQuery response
from the server.
A sync message is normally answered by a ReadyForQuery which is
translated to a PGRES_PIPELINE_SYNC in pqParseInput3. This is why we
add the number of piped_syncs to the requested_results as we need to
consume the generated PGRES_PIPELINE_SYNC with PQgetResult.
With the \sendsync following a COPY, we have two Sync messages but
only receive a single ReadyForQuery response. We have a similar issue
running the following:
\startpipeline
COPY psql_pipeline FROM STDIN;
\syncpipeline
\endpipeline
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> COPY 0
The psql session gets stuck in PQgetResult as we're trying to read a
sync response that doesn't exist. Looking at CopyGetData, it seems
like Sync messages are consumed and ignored:
case PqMsg_Sync:
/*
* Ignore Flush/Sync for the convenience of client
* libraries (such as libpq) that may send those
* without noticing that the command they just
* sent was COPY.
*/
goto readmessage;
So if that's expected, we may need additional logic to avoid
increasing piped_syncs when we're after a COPY? On the other hand,
ignoring the SYNC seems to break the protocol spec expectation that
"there is one and only one ReadyForQuery sent for each Sync".
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-02 23:31 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2025-06-02 23:31 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: n.kalinin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Mon, Jun 02, 2025 at 07:00:26PM +0200, Anthonin Bonnefoy wrote:
> So if that's expected, we may need additional logic to avoid
> increasing piped_syncs when we're after a COPY? On the other hand,
> ignoring the SYNC seems to break the protocol spec expectation that
> "there is one and only one ReadyForQuery sent for each Sync".
Hmm. This stuff stands as it is in the backend COPY code since
c01641f8aed0 from 2003, so there's not much we can do in the backend
or libpq. Adding some specific logic in psql to avoid piped_syncs
seems like the correct move seen from here.
Most of that should be close to ExecQueryAndProcessResults(), don't
you think? That's where we count the number of piped syncs.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aD40RKgfY4L4wnfL@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-03 09:56 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-03 09:56 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: n.kalinin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Tue, Jun 3, 2025 at 1:31 AM Michael Paquier <michael@paquier.xyz> wrote:
> Hmm. This stuff stands as it is in the backend COPY code since
> c01641f8aed0 from 2003, so there's not much we can do in the backend
> or libpq. Adding some specific logic in psql to avoid piped_syncs
> seems like the correct move seen from here.
>
> Most of that should be close to ExecQueryAndProcessResults(), don't
> you think? That's where we count the number of piped syncs.
I've tried to adjust the piped syncs counter, but that's not enough.
libpq's internal command queue still has the Syncs queued and will
stay in a busy pipeline state if the backend doesn't send the expected
ReadyForQuery. It could be possible to remove them from the queue with
pqCommandQueueAdvance, but that would require psql to include
libpq-int.h which is probably something we want to avoid.
It doesn't seem like this case can be handled gracefully. The provided
patch just aborts the connection from the frontend when excessive
piped syncs are detected to avoid staying stuck in this inconsistent
protocol state.
Attachments:
[application/octet-stream] v01-0001-Abort-connection-when-sync-is-sent-after-a-COPY.patch (3.0K, ../../CAO6_XqrQ+d3jqRg8dnW_LU+-k39ay6JSXOOsC9fe6u8hM2SQow@mail.gmail.com/2-v01-0001-Abort-connection-when-sync-is-sent-after-a-COPY.patch)
download | inline diff:
From f968080ba2d92c942f4fd44aa266d29cbf008348 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Tue, 3 Jun 2025 10:31:26 +0200
Subject: Abort connection when sync is sent after a COPY
When the backend reads COPY data, it ignores all Sync messages. With
psql pipelining, it's possible to manually send Sync with \sendpipeline
which leaves the frontend in an unrecoverable state as the backend won't
send the necessary ReadyForQuery.
It could be possible to artificially reduce the piped_syncs and
requested_results, but libpq's state will still have queued Sync in its
command queue. And the only way to consume those without directly
calling pqCommandQueueAdvance is to process ReadyForQuery messages that
won't be sent since the backend ignored the Syncs.
Thus, this patch abort the connection if we detect excessive sync after
a COPY in a pipeline to avoid staying in an inconsistent protocol state.
---
src/bin/psql/common.c | 15 +++++++++++++++
src/bin/psql/t/001_basic.pl | 14 ++++++++++++--
2 files changed, 27 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 3e4e444f3fd..ed745270b6c 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1867,6 +1867,21 @@ ExecQueryAndProcessResults(const char *query,
{
FILE *copy_stream = NULL;
+ if (pset.piped_syncs > 1)
+ {
+ /*
+ * When reading COPY data, the backend ignores SYNC messages
+ * and won't send a matching ReadyForQuery response. Even if
+ * we adjust piped_syncs and requested_results, it is not
+ * possible to salvage this as the SYNC will still be in
+ * libpq's command queue and we would be stuck in a busy
+ * pipeline state. Thus, we abort the connection to avoid this
+ * state.
+ */
+ pg_log_info("\\syncpipeline after COPY is not supported, aborting connection");
+ exit(EXIT_BADCONN);
+ }
+
/*
* For COPY OUT, direct the output to the default place (probably
* a pager pipe) for \watch, or to pset.copyStream for \copy,
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index 4050f9a5e3e..d8fcf0575e0 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -513,15 +513,25 @@ SELECT 'val1' \\bind \\sendpipeline
qr/server closed the connection unexpectedly/,
'protocol sync loss in pipeline: bind COPY, SELECT, sync and getresult');
-# This time, test without the \getresults.
+# This time, test without the \getresults and \syncpipeline.
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline FROM STDIN;
SELECT 'val1';
-\\syncpipeline
\\endpipeline},
qr/server closed the connection unexpectedly/,
'protocol sync loss in pipeline: COPY, SELECT and sync');
+# Test sending a sync after a COPY, this will abort the connection from the
+# frontend
+psql_fails_like(
+ $node,
+ qq{\\startpipeline
+COPY psql_pipeline FROM STDIN;
+\\syncpipeline
+\\endpipeline},
+ qr/\\syncpipeline after COPY is not supported, aborting connection/,
+ 'sending sync after COPY');
+
done_testing();
--
2.49.0
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-04 00:12 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2025-06-04 00:12 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: n.kalinin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Tue, Jun 03, 2025 at 11:56:51AM +0200, Anthonin Bonnefoy wrote:
> I've tried to adjust the piped syncs counter, but that's not enough.
> libpq's internal command queue still has the Syncs queued and will
> stay in a busy pipeline state if the backend doesn't send the expected
> ReadyForQuery. It could be possible to remove them from the queue with
> pqCommandQueueAdvance, but that would require psql to include
> libpq-int.h which is probably something we want to avoid.
libpq-int.h is for internal purposes and should not be directly
used in frontends, so that's a no-go.
> It doesn't seem like this case can be handled gracefully. The provided
> patch just aborts the connection from the frontend when excessive
> piped syncs are detected to avoid staying stuck in this inconsistent
> protocol state.
I have been spending some time trying to get psql to gracefully handle
this case, likely as you did by making ExecQueryAndProcessResults()
fail gracefully, but even reinitializing the counters is not enough
and I have also bumped into the libpq limitations. If somebody is
excited enough to justify exposing these APIs, perhaps they'll have a
good reason and a case to do so, but I'm not excited about doing that
for this psql tooling: we are still able to serialize commands within
a pipeline anyway.
For now I have applied your patch, adding one extra test with COPY TO
on top of the one with COPY FROM.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aD-PYXOv643xVcOf@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-11 03:58 Никита Калинин <n.kalinin@postgrespro.ru>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Никита Калинин @ 2025-06-11 03:58 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; pgsql-bugs@lists.postgresql.org
Hello!
I do not believe it is necessary to create a new bug report, as, in my opinion, this issue remains a continuation of BUG #18944. Upon executing the following request:
psql <<EOF
CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT);
\startpipeline
COPY psql_pipeline FROM STDIN;
\flushrequest
\getresults
\endpipeline
\.
COPY psql_pipeline FROM STDIN;
\syncpipeline
\getresults
EOF
we encounter yet another assertion failure:
ERROR: invalid input syntax for type integer: "endpipeline"
CONTEXT: COPY psql_pipeline, line 1, column a: "endpipeline"
message type 0x5a arrived from server while idle
Pipeline aborted, command did not run
psql: common.c:1527: discardAbortedPipelineResults: Assertion `res == ((void *)0) || result_status == PGRES_PIPELINE_ABORTED' failed.
Aborted (core dumped).
Coredump:
#0 __pthread_kill_implementation (threadid=255760382287552, signo=signo@entry=6,
no_tid=no_tid@entry=0) at ./nptl/pthread_kill.c:44
#1 0x0000e89cda8b7670 in __pthread_kill_internal (signo=6, threadid=<optimized out>)
at ./nptl/pthread_kill.c:78
#2 0x0000e89cda86cb3c in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26
#3 0x0000e89cda857e00 in __GI_abort () at ./stdlib/abort.c:79
#4 0x0000e89cda865cc0 in __assert_fail_base (
fmt=0xe89cda98c770 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n",
assertion=assertion@entry=0xbd8dd0c47fd0 "res == ((void *)0) || result_status == PGRES_PIPELINE_ABORTED", file=file@entry=0xbd8dd0c47eb0 "common.c", line=line@entry=1527,
function=function@entry=0xbd8dd0c76068 <__PRETTY_FUNCTION__.3> "discardAbortedPipelineResults")
at ./assert/assert.c:96
#5 0x0000e89cda865d30 in __assert_fail (
assertion=assertion@entry=0xbd8dd0c47fd0 "res == ((void *)0) || result_status == PGRES_PIPELINE_ABORTED", file=file@entry=0xbd8dd0c47eb0 "common.c", line=line@entry=1527,
function=function@entry=0xbd8dd0c76068 <__PRETTY_FUNCTION__.3> "discardAbortedPipelineResults")
at ./assert/assert.c:105
#6 0x0000bd8dd0bfe6a8 in discardAbortedPipelineResults () at common.c:1527
#7 ExecQueryAndProcessResults (query=query@entry=0xbd8df7065330 "COPY psql_pipeline FROM STDIN;",
elapsed_msec=elapsed_msec@entry=0xffffd1a532e0, svpt_gone_p=svpt_gone_p@entry=0xffffd1a532df,
is_watch=is_watch@entry=false, min_rows=min_rows@entry=0, opt=opt@entry=0x0,
printQueryFout=printQueryFout@entry=0x0) at common.c:1828
#8 0x0000bd8dd0bfc6d0 in SendQuery (query=0xbd8df7065330 "COPY psql_pipeline FROM STDIN;")
at common.c:1212
#9 0x0000bd8dd0c10d3c in MainLoop (source=source@entry=0xe89cda9e0880 <_IO_2_1_stdin_>)
at mainloop.c:515
#10 0x0000bd8dd0bf7070 in process_file (filename=0x0,
use_relative_path=use_relative_path@entry=false) at command.c:4870
#11 0x0000bd8dd0bf1b04 in main (argc=<optimized out>, argv=<optimized out>) at startup.c:420
Nikita Kalinin
Postgres Professional: http://www.postgrespro.com <http://www.postgrespro.com/;
The Russian Postgres Company
> On 4 Jun 2025, at 07:12, Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Jun 03, 2025 at 11:56:51AM +0200, Anthonin Bonnefoy wrote:
>> I've tried to adjust the piped syncs counter, but that's not enough.
>> libpq's internal command queue still has the Syncs queued and will
>> stay in a busy pipeline state if the backend doesn't send the expected
>> ReadyForQuery. It could be possible to remove them from the queue with
>> pqCommandQueueAdvance, but that would require psql to include
>> libpq-int.h which is probably something we want to avoid.
>
> libpq-int.h is for internal purposes and should not be directly
> used in frontends, so that's a no-go.
>
>> It doesn't seem like this case can be handled gracefully. The provided
>> patch just aborts the connection from the frontend when excessive
>> piped syncs are detected to avoid staying stuck in this inconsistent
>> protocol state.
>
> I have been spending some time trying to get psql to gracefully handle
> this case, likely as you did by making ExecQueryAndProcessResults()
> fail gracefully, but even reinitializing the counters is not enough
> and I have also bumped into the libpq limitations. If somebody is
> excited enough to justify exposing these APIs, perhaps they'll have a
> good reason and a case to do so, but I'm not excited about doing that
> for this psql tooling: we are still able to serialize commands within
> a pipeline anyway.
>
> For now I have applied your patch, adding one extra test with COPY TO
> on top of the one with COPY FROM.
> --
> Michael
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-11 14:11 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Никита Калинин <n.kalinin@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-11 14:11 UTC (permalink / raw)
To: Никита Калинин <n.kalinin@postgrespro.ru>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org
On Wed, Jun 11, 2025 at 5:58 AM Никита Калинин <n.kalinin@postgrespro.ru> wrote:
>
> I do not believe it is necessary to create a new bug report, as, in my opinion, this issue remains a continuation of BUG #18944. Upon executing the following request:
>
> psql <<EOF
> CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT);
> \startpipeline
> COPY psql_pipeline FROM STDIN;
> \flushrequest
> \getresults
> \endpipeline
> \.
> COPY psql_pipeline FROM STDIN;
> \syncpipeline
> \getresults
> EOF
>
> we encounter yet another assertion failure:
>
> ERROR: invalid input syntax for type integer: "endpipeline"
> CONTEXT: COPY psql_pipeline, line 1, column a: "endpipeline"
> message type 0x5a arrived from server while idle
> Pipeline aborted, command did not run
> psql: common.c:1527: discardAbortedPipelineResults: Assertion `res == ((void *)0) || result_status == PGRES_PIPELINE_ABORTED' failed.
I've managed to reproduce, thanks for the report.
The issue seems to come from COPY automatically sending a Sync
message. During the first COPY failure, the sync is used as a
synchronisation point, aborts the transaction and is ready to process
new commands from the client.
However, from the client's point of view, the pipeline is in an
aborted state and all commands should be ignored as ignore_till_sync
should be true.However, that's not the case due to the Sync message
automatically added by COPY.
Thus, when the second COPY is sent (something else than COPY can
trigger this), we go through discardAbortedPipelineResults while the
server answers to the commands, triggering the assertion failure.
Libpq's fe-exec doesn't seem to handle a possible pipeline state in
PQputCopyEnd. Naively, I've tried to only send a Sync message if
'conn->pipelineStatus == PQ_PIPELINE_OFF' but this will block the
client in Copy mode and the Sync message is needed to exit it.
I didn't have time to look into this further today but a possible fix
could be to send the Sync message and add it to the command queue if
we're in a pipeline. This should allow libpq's state to get out of the
aborted pipeline state and match the backend's state.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-11 22:34 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2025-06-11 22:34 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Никита Калинин <n.kalinin@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
On Wed, Jun 11, 2025 at 04:11:14PM +0200, Anthonin Bonnefoy wrote:
> Libpq's fe-exec doesn't seem to handle a possible pipeline state in
> PQputCopyEnd. Naively, I've tried to only send a Sync message if
> 'conn->pipelineStatus == PQ_PIPELINE_OFF' but this will block the
> client in Copy mode and the Sync message is needed to exit it.
> I didn't have time to look into this further today but a possible fix
> could be to send the Sync message and add it to the command queue if
> we're in a pipeline. This should allow libpq's state to get out of the
> aborted pipeline state and match the backend's state.
Hmm. The root issue is that we are trying to work around the
limitations of the existing result handling required by COPY and the
extended protocol which are both not interacting nicely as psql has
little idea that we are in a COPY state because we've sent a plain
COPY query (contrary to \copy, actually, where we could detect that
with a counter).
As this is the first release where these pipeline meta-commands exist,
we may want to consider a more restrictive measure to prevent this
state to happen to begin with, rather to try to implement workarounds.
I understand that it may not be the best thing to do, but we could
restrict the use of COPY in pipelines for the initial release of this
feature and just say that we don't support it for now. Then, in the
future, if somebody is interested, we could study how it is possible
to lift this restriction, but I suspect that we won't fly high without
some redesign of libpq in terms if pipeline queues, which has the risk
of introducing juicy bugs, but that would be a problem of the future.
So my point would be to take a drastic decision in
ExecQueryAndProcessResults() if we detect a PGRES_COPY_BOTH,
PGRES_COPY_OUT or PGRES_COPY_IN while in a pipeline, and just give up.
It does not prevent the feature to work for other queries, and we
still have test coverage for queries that cannot run in transaction
blocks like REINDEX CONCURRENTLY, etc. It just means that we cannot
enter in a COPY state while in a pipeline.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEoEjEsBFj62ywnE@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-12 07:38 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-12 07:38 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Никита Калинин <n.kalinin@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
On Thu, Jun 12, 2025 at 12:35 AM Michael Paquier <michael@paquier.xyz> wrote:
> So my point would be to take a drastic decision in
> ExecQueryAndProcessResults() if we detect a PGRES_COPY_BOTH,
> PGRES_COPY_OUT or PGRES_COPY_IN while in a pipeline, and just give up.
> It does not prevent the feature to work for other queries, and we
> still have test coverage for queries that cannot run in transaction
> blocks like REINDEX CONCURRENTLY, etc. It just means that we cannot
> enter in a COPY state while in a pipeline.
Agreed, I've also contemplated this option given the amount of edge
cases there are along with the limited usefulness of doing COPY within
a pipeline. In a sense, this worked as a way to show the missing bits
of libpq's pipeline support with COPY.
The attached patch now aborts the connection anytime we enter COPY
state while within a pipeline and removes all pipeline+COPY from the
SQL tests.
Attachments:
[application/octet-stream] v01-0001-Forbid-usage-of-COPY-in-a-pipeline.patch (11.7K, ../../CAO6_XqofwsnjeRUQj-T2YyB8q0XsYWMQO-NEsC2xKfPxTyE8_Q@mail.gmail.com/2-v01-0001-Forbid-usage-of-COPY-in-a-pipeline.patch)
download | inline diff:
From c89781841d72386c433f9ef3dcab524ad822a303 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Wed, 11 Jun 2025 15:48:10 +0200
Subject: Forbid usage of COPY in a pipeline
Running COPY within a pipeline can break protocol synchronisation in multiple
ways.
- In copy mode, the backend process ignores additional Sync messages and
won't send the matching ReadyForQuery expected by the frontend. Doing a
\syncpipeline just after COPY will leave the frontend waiting for a
ReadyForQuery message that won't be sent.
- libq automatically sends a Sync with the Copy message which isn't
tracked in the command queue, creating an unexpected synchronisation point. A
failure during copy will leave the pipeline in an aborted state while the
backend would be in a clean state, ready to process commands.
Fixing those issues would require modifications in how libpq handle
pipeline and COPY. Since this is a new feature and the use case of COPY
in a pipeline has limited usefulness, it was decided to forbid the use
of COPY within a pipeline to avoid possible break of protocol
synchronisation.
---
src/bin/psql/common.c | 27 ++-
src/bin/psql/t/001_basic.pl | 27 ++-
src/test/regress/expected/psql_pipeline.out | 188 +-------------------
src/test/regress/sql/psql_pipeline.sql | 100 -----------
4 files changed, 32 insertions(+), 310 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 47352b7faed..82ce59fb061 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1867,18 +1867,27 @@ ExecQueryAndProcessResults(const char *query,
{
FILE *copy_stream = NULL;
- if (pset.piped_syncs > 1)
+ if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
{
/*
- * When reading COPY data, the backend ignores sync messages
- * and will not send a matching ReadyForQuery response. Even
- * if we adjust piped_syncs and requested_results, it is not
- * possible to salvage this as the sync message would still be
- * in libpq's command queue and we would be stuck in a busy
- * pipeline state. Thus, we abort the connection to avoid
- * this state.
+ * Running COPY within a pipeline can break protocol
+ * synchronisation in multiple ways.
+ *
+ * While in copy mode, the backend process ignores additional
+ * Sync messages and won't send the matching ReadyForQuery
+ * expected by the frontend
+ *
+ * libq also automatically sends a Sync with the Copy message,
+ * creating an unexpected synchronisation point. A failure
+ * during copy would leave the pipeline in an aborted state
+ * while the backend would be in a clean state, ready to
+ * process commands.
+ *
+ * Fixing those issues would require modifications in how
+ * libpq handle pipeline and COPY. For the time being, we
+ * forbid the use of COPY within a pipeline.
*/
- pg_log_info("\\syncpipeline after COPY is not supported, aborting connection");
+ pg_log_info("COPY in a pipeline is not supported, aborting connection");
exit(EXIT_BADCONN);
}
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index ae5c1d66405..beab3d7e179 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -483,8 +483,8 @@ psql_like($node, "copy (values ('foo'),('bar')) to stdout \\g | $pipe_cmd",
my $c4 = slurp_file($g_file);
like($c4, qr/foo.*bar/s);
-# Tests with pipelines. These trigger FATAL failures in the backend,
-# so they cannot be tested via SQL.
+# Tests COPY within pipelines. Those abort the connection from
+# the frontend so they cannot be tested via SQL.
$node->safe_psql('postgres', 'CREATE TABLE psql_pipeline()');
my $log_location = -s $node->logfile;
psql_fails_like(
@@ -495,8 +495,8 @@ SELECT 'val1';
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: direct COPY, SELECT, sync and getresult'
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: direct COPY, SELECT, sync and getresult'
);
$node->wait_for_log(
qr/FATAL: .*terminating connection because protocol synchronization was lost/,
@@ -510,8 +510,8 @@ SELECT 'val1' \\bind \\sendpipeline
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: bind COPY, SELECT, sync and getresult');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: bind COPY, SELECT, sync and getresult');
# This time, test without the \getresults and \syncpipeline.
psql_fails_like(
@@ -520,26 +520,25 @@ psql_fails_like(
COPY psql_pipeline FROM STDIN;
SELECT 'val1';
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: COPY, SELECT and sync');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: COPY, SELECT and sync');
-# Tests sending a sync after a COPY TO/FROM. These abort the connection
-# from the frontend.
+# Tests sending a sync after a COPY TO/FROM.
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline FROM STDIN;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY FROM');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY FROM');
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline TO STDOUT;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY TO');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY TO');
done_testing();
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out
index a30dec088b9..e78e6bfa0ad 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -228,192 +228,6 @@ BEGIN \bind \sendpipeline
INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-message type 0x5a arrived from server while idle
-\endpipeline
--- with semicolon
-\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
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
@@ -740,7 +554,7 @@ SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline
count
-------
- 7
+ 1
(1 row)
-- After an error, pipeline is aborted and requires \syncpipeline to be
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql
index 16e1e1e84cd..5945eca1ef7 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -105,106 +105,6 @@ INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
-2 test2
-\.
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
-20 test2
-\.
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
-3 test3
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\flushrequest
-\getresults
-30 test3
-\.
-\endpipeline
-
--- COPY FROM STDIN with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
-4 test4
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
-40 test4
-\.
-\endpipeline
-
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
-
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
-\endpipeline
-
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
-\endpipeline
-
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
--
2.49.0
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-12 08:03 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2025-06-12 08:03 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Никита Калинин <n.kalinin@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
On Thu, Jun 12, 2025 at 09:38:41AM +0200, Anthonin Bonnefoy wrote:
> The attached patch now aborts the connection anytime we enter COPY
> state while within a pipeline and removes all pipeline+COPY from the
> SQL tests.
Thanks for the patch, that's exactly what I was testing today, as a
simple way to handle the problem reported. I'll check that in more
details tomorrow, running a bit out of time today..
Do you think that it is worth adding a sentence about this restriction
in the docs in the section dedicated to the pipeline meta-commands?
We could just add a simple sentence like "<command>COPY</command>
command is not supported while in pipeline mode."
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEqJz8wOeqOjR25b@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-12 13:04 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 13+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-12 13:04 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Никита Калинин <n.kalinin@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
On Thu, Jun 12, 2025 at 10:03 AM Michael Paquier <michael@paquier.xyz> wrote:
> Do you think that it is worth adding a sentence about this restriction
> in the docs in the section dedicated to the pipeline meta-commands?
> We could just add a simple sentence like "<command>COPY</command>
> command is not supported while in pipeline mode."
Good point, I've added the doc change to the patch.
Attachments:
[application/octet-stream] v02-0001-Forbid-usage-of-COPY-in-a-pipeline.patch (12.2K, ../../CAO6_XqoEQsMDedTME5v95e+_k6se9wtxbC75D3sOHXpr+Wpg+A@mail.gmail.com/2-v02-0001-Forbid-usage-of-COPY-in-a-pipeline.patch)
download | inline diff:
From 1906644a0f251d3d9b9423d79f5d95fc31696b18 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Wed, 11 Jun 2025 15:48:10 +0200
Subject: Forbid usage of COPY in a pipeline
Running COPY within a pipeline can break protocol synchronisation in multiple
ways.
- In copy mode, the backend process ignores additional Sync messages and
won't send the matching ReadyForQuery expected by the frontend. Doing a
\syncpipeline just after COPY will leave the frontend waiting for a
ReadyForQuery message that won't be sent.
- libq automatically sends a Sync with the Copy message which isn't
tracked in the command queue, creating an unexpected synchronisation point. A
failure during copy will leave the pipeline in an aborted state while the
backend would be in a clean state, ready to process commands.
Fixing those issues would require modifications in how libpq handle
pipeline and COPY. Since this is a new feature and the use case of COPY
in a pipeline has limited usefulness, it was decided to forbid the use
of COPY within a pipeline to avoid possible break of protocol
synchronisation.
---
doc/src/sgml/ref/psql-ref.sgml | 4 +
src/bin/psql/common.c | 27 ++-
src/bin/psql/t/001_basic.pl | 27 ++-
src/test/regress/expected/psql_pipeline.out | 188 +-------------------
src/test/regress/sql/psql_pipeline.sql | 100 -----------
5 files changed, 36 insertions(+), 310 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8f7d8758ca0..1cb67ff806d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3733,6 +3733,10 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput>
See <xref linkend="app-psql-prompting-p-uc"/> for more details
</para>
+ <para>
+ <command>COPY</command> is not supported while in pipeline mode.
+ </para>
+
<para>
Example:
<programlisting>
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 47352b7faed..82ce59fb061 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1867,18 +1867,27 @@ ExecQueryAndProcessResults(const char *query,
{
FILE *copy_stream = NULL;
- if (pset.piped_syncs > 1)
+ if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
{
/*
- * When reading COPY data, the backend ignores sync messages
- * and will not send a matching ReadyForQuery response. Even
- * if we adjust piped_syncs and requested_results, it is not
- * possible to salvage this as the sync message would still be
- * in libpq's command queue and we would be stuck in a busy
- * pipeline state. Thus, we abort the connection to avoid
- * this state.
+ * Running COPY within a pipeline can break protocol
+ * synchronisation in multiple ways.
+ *
+ * While in copy mode, the backend process ignores additional
+ * Sync messages and won't send the matching ReadyForQuery
+ * expected by the frontend
+ *
+ * libq also automatically sends a Sync with the Copy message,
+ * creating an unexpected synchronisation point. A failure
+ * during copy would leave the pipeline in an aborted state
+ * while the backend would be in a clean state, ready to
+ * process commands.
+ *
+ * Fixing those issues would require modifications in how
+ * libpq handle pipeline and COPY. For the time being, we
+ * forbid the use of COPY within a pipeline.
*/
- pg_log_info("\\syncpipeline after COPY is not supported, aborting connection");
+ pg_log_info("COPY in a pipeline is not supported, aborting connection");
exit(EXIT_BADCONN);
}
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index ae5c1d66405..beab3d7e179 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -483,8 +483,8 @@ psql_like($node, "copy (values ('foo'),('bar')) to stdout \\g | $pipe_cmd",
my $c4 = slurp_file($g_file);
like($c4, qr/foo.*bar/s);
-# Tests with pipelines. These trigger FATAL failures in the backend,
-# so they cannot be tested via SQL.
+# Tests COPY within pipelines. Those abort the connection from
+# the frontend so they cannot be tested via SQL.
$node->safe_psql('postgres', 'CREATE TABLE psql_pipeline()');
my $log_location = -s $node->logfile;
psql_fails_like(
@@ -495,8 +495,8 @@ SELECT 'val1';
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: direct COPY, SELECT, sync and getresult'
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: direct COPY, SELECT, sync and getresult'
);
$node->wait_for_log(
qr/FATAL: .*terminating connection because protocol synchronization was lost/,
@@ -510,8 +510,8 @@ SELECT 'val1' \\bind \\sendpipeline
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: bind COPY, SELECT, sync and getresult');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: bind COPY, SELECT, sync and getresult');
# This time, test without the \getresults and \syncpipeline.
psql_fails_like(
@@ -520,26 +520,25 @@ psql_fails_like(
COPY psql_pipeline FROM STDIN;
SELECT 'val1';
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: COPY, SELECT and sync');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: COPY, SELECT and sync');
-# Tests sending a sync after a COPY TO/FROM. These abort the connection
-# from the frontend.
+# Tests sending a sync after a COPY TO/FROM.
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline FROM STDIN;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY FROM');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY FROM');
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline TO STDOUT;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY TO');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY TO');
done_testing();
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out
index a30dec088b9..e78e6bfa0ad 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -228,192 +228,6 @@ BEGIN \bind \sendpipeline
INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-message type 0x5a arrived from server while idle
-\endpipeline
--- with semicolon
-\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
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
@@ -740,7 +554,7 @@ SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline
count
-------
- 7
+ 1
(1 row)
-- After an error, pipeline is aborted and requires \syncpipeline to be
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql
index 16e1e1e84cd..5945eca1ef7 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -105,106 +105,6 @@ INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
-2 test2
-\.
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
-20 test2
-\.
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
-3 test3
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\flushrequest
-\getresults
-30 test3
-\.
-\endpipeline
-
--- COPY FROM STDIN with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
-4 test4
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
-40 test4
-\.
-\endpipeline
-
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
-
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
-\endpipeline
-
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
-\endpipeline
-
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
--
2.49.0
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set
@ 2025-06-13 01:19 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 0 replies; 13+ messages in thread
From: Michael Paquier @ 2025-06-13 01:19 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Никита Калинин <n.kalinin@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
On Thu, Jun 12, 2025 at 03:04:05PM +0200, Anthonin Bonnefoy wrote:
> Good point, I've added the doc change to the patch.
Thanks. I've tweaked the comments, fixing some grammar mistakes in
them, and adjusted the tests a bit, then applied the result to close
the door to all the problems reported.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEt8sC5iInI734oC@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-06-13 01:19 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-02 06:34 BUG #18944: Assertion Failure in psql with idle_session_timeout Set PG Bug reporting form <noreply@postgresql.org>
2025-06-02 08:13 ` Michael Paquier <michael@paquier.xyz>
2025-06-02 17:00 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-02 23:31 ` Michael Paquier <michael@paquier.xyz>
2025-06-03 09:56 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-04 00:12 ` Michael Paquier <michael@paquier.xyz>
2025-06-11 03:58 ` Никита Калинин <n.kalinin@postgrespro.ru>
2025-06-11 14:11 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-11 22:34 ` Michael Paquier <michael@paquier.xyz>
2025-06-12 07:38 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-12 08:03 ` Michael Paquier <michael@paquier.xyz>
2025-06-12 13:04 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-13 01:19 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox