Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: Forbid use of COPY and \copy while in a pipeline

  2. psql: Abort connection when using \syncpipeline after COPY TO/FROM

  1. BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    PG Bug reporting form <noreply@postgresql.org> — 2025-06-02T06:34:12Z

    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

  2. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-02T08:13:48Z

    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

  3. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> — 2025-06-02T17:00:26Z

    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".

  4. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-02T23:31:16Z

    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

  5. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> — 2025-06-03T09:56:51Z

    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.

  6. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-04T00:12:17Z

    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

  7. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Никита Калинин <n.kalinin@postgrespro.ru> — 2025-06-11T03:58:32Z

    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

  8. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> — 2025-06-11T14:11:14Z

    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.

  9. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-11T22:34:52Z

    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

  10. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> — 2025-06-12T07:38:41Z

    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.

  11. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-12T08:03:27Z

    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

  12. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> — 2025-06-12T13:04:05Z

    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.

  13. Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set

    Michael Paquier <michael@paquier.xyz> — 2025-06-13T01:19:44Z

    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