Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uPCc2-002OV0-NW for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Jun 2025 03:58:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uPCc0-00Esgo-Nw for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Jun 2025 03:58:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uPCc0-00Esgg-21 for pgsql-bugs@lists.postgresql.org; Wed, 11 Jun 2025 03:58:53 +0000 Received: from mail.postgrespro.ru ([93.174.132.70]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uPCbx-001Min-10 for pgsql-bugs@lists.postgresql.org; Wed, 11 Jun 2025 03:58:51 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1749614325; bh=wT1HQtMMnB9+0Fgb59KalLC/0I0vSTJ4upSE6mK7w8k=; h=From:Message-Id:Subject:Date:In-Reply-To:Cc:To:References:From; b=5xFAkyIpYVlU5Pz5PewIvfDvYiuTP06My0l8nrLHk6m0Oe7WMEK5H00WoQDXyFmP4 O4f5+sV7g7hXfEsSkwrVcdir+w+Q1o0zVuoeZ2pC0czrZ+fcbOzrJrobQ84BjELgtU pW1pr/1BQiv346tad7GxL0OHwdznc5OfC8bvmwO9mEJQTFDZ+a8jIwtpGJpuHphGHN WaRw9MgKqv0sBcNDVliAgkDLmAev23wwDavrNpRt/sK6X0gLKHcqR+Q1UrM2qy87hx qcT1zkSHGPSQOZPfNgDBdjJWLnHD37zAvxgtv1h3qbaxFUs6psuYGAdaATwFrqGdfz 8weqhEY+beOKg== Received: from smtpclient.apple (unknown [93.174.131.138]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) (Authenticated sender: n.kalinin@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 8D63C601E3; Wed, 11 Jun 2025 06:58:44 +0300 (MSK) From: =?utf-8?B?0J3QuNC60LjRgtCwINCa0LDQu9C40L3QuNC9?= Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_2A498B81-115F-4DAC-8C16-5DE55C106A8F" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.600.51.1.1\)) Subject: Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set Date: Wed, 11 Jun 2025 10:58:32 +0700 In-Reply-To: Cc: Anthonin Bonnefoy , pgsql-bugs@lists.postgresql.org To: Michael Paquier References: <18944-8a926c30f68387dd@postgresql.org> X-Mailer: Apple Mail (2.3826.600.51.1.1) X-KSMG-AntiPhishing: NotDetected, bases: 2025/06/11 03:32:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2025/06/11 03:03:00 #27557875 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_2A498B81-115F-4DAC-8C16-5DE55C106A8F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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 <) at ./nptl/pthread_kill.c:78 #2 0x0000e89cda86cb3c in __GI_raise (sig=3Dsig@entry=3D6) at = ../sysdeps/posix/raise.c:26 #3 0x0000e89cda857e00 in __GI_abort () at ./stdlib/abort.c:79 #4 0x0000e89cda865cc0 in __assert_fail_base ( fmt=3D0xe89cda98c770 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=3Dassertion@entry=3D0xbd8dd0c47fd0 "res =3D=3D ((void *)0) = || result_status =3D=3D PGRES_PIPELINE_ABORTED", = file=3Dfile@entry=3D0xbd8dd0c47eb0 "common.c", line=3Dline@entry=3D1527, function=3Dfunction@entry=3D0xbd8dd0c76068 <__PRETTY_FUNCTION__.3> = "discardAbortedPipelineResults") at ./assert/assert.c:96 #5 0x0000e89cda865d30 in __assert_fail ( assertion=3Dassertion@entry=3D0xbd8dd0c47fd0 "res =3D=3D ((void *)0) = || result_status =3D=3D PGRES_PIPELINE_ABORTED", = file=3Dfile@entry=3D0xbd8dd0c47eb0 "common.c", line=3Dline@entry=3D1527, function=3Dfunction@entry=3D0xbd8dd0c76068 <__PRETTY_FUNCTION__.3> = "discardAbortedPipelineResults") at ./assert/assert.c:105 #6 0x0000bd8dd0bfe6a8 in discardAbortedPipelineResults () at = common.c:1527 #7 ExecQueryAndProcessResults (query=3Dquery@entry=3D0xbd8df7065330 = "COPY psql_pipeline FROM STDIN;", elapsed_msec=3Delapsed_msec@entry=3D0xffffd1a532e0, = svpt_gone_p=3Dsvpt_gone_p@entry=3D0xffffd1a532df, is_watch=3Dis_watch@entry=3Dfalse, min_rows=3Dmin_rows@entry=3D0, = opt=3Dopt@entry=3D0x0, printQueryFout=3DprintQueryFout@entry=3D0x0) at common.c:1828 #8 0x0000bd8dd0bfc6d0 in SendQuery (query=3D0xbd8df7065330 "COPY = psql_pipeline FROM STDIN;") at common.c:1212 #9 0x0000bd8dd0c10d3c in MainLoop (source=3Dsource@entry=3D0xe89cda9e0880= <_IO_2_1_stdin_>) at mainloop.c:515 #10 0x0000bd8dd0bf7070 in process_file (filename=3D0x0, use_relative_path=3Duse_relative_path@entry=3Dfalse) at = command.c:4870 #11 0x0000bd8dd0bf1b04 in main (argc=3D, argv=3D) at startup.c:420 Nikita Kalinin Postgres Professional: http://www.postgrespro.com = The Russian Postgres Company > On 4 Jun 2025, at 07:12, Michael Paquier wrote: >=20 > 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. >=20 > libpq-int.h is for internal purposes and should not be directly > used in frontends, so that's a no-go. >=20 >> 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. >=20 > 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. >=20 > For now I have applied your patch, adding one extra test with COPY TO > on top of the one with COPY FROM. > -- > Michael --Apple-Mail=_2A498B81-115F-4DAC-8C16-5DE55C106A8F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

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 =3D=3D ((void *)0) || result_status =3D=3D PGRES_PIPELINE_ABORTED' = failed.

Aborted (core = dumped). 


Coredump:

#0  __pthread_kill_implementation = (threadid=3D255760382287552, signo=3Dsigno@entry=3D6,

    no_tid=3Dno_tid@entry=3D0)= at ./nptl/pthread_kill.c:44

#1  = 0x0000e89cda8b7670 in __pthread_kill_internal (signo=3D6, = threadid=3D<optimized out>)

    at = ./nptl/pthread_kill.c:78

#2  = 0x0000e89cda86cb3c in __GI_raise (sig=3Dsig@entry=3D6) at = ../sysdeps/posix/raise.c:26

#3  = 0x0000e89cda857e00 in __GI_abort () at ./stdlib/abort.c:79

#4  0x0000e89cda865cc0 in = __assert_fail_base (

  =   fmt=3D0xe89cda98c770 "%s%s%s:%u: %s%sAssertion `%s' = failed.\n%n",

  =   assertion=3Dassertion@entry=3D0xbd8dd0c47fd0 "res =3D=3D ((void = *)0) || result_status =3D=3D PGRES_PIPELINE_ABORTED", = file=3Dfile@entry=3D0xbd8dd0c47eb0 "common.c", = line=3Dline@entry=3D1527,

  =   function=3Dfunction@entry=3D0xbd8dd0c76068 = <__PRETTY_FUNCTION__.3> "discardAbortedPipelineResults")

    at = ./assert/assert.c:96

#5  = 0x0000e89cda865d30 in __assert_fail (

    = assertion=3Dassertion@entry=3D0xbd8dd0c47fd0 "res =3D=3D ((void *)0) || = result_status =3D=3D PGRES_PIPELINE_ABORTED", = file=3Dfile@entry=3D0xbd8dd0c47eb0 "common.c", = line=3Dline@entry=3D1527,

  =   function=3Dfunction@entry=3D0xbd8dd0c76068 = <__PRETTY_FUNCTION__.3> "discardAbortedPipelineResults")

    at = ./assert/assert.c:105

#6  = 0x0000bd8dd0bfe6a8 in discardAbortedPipelineResults () at = common.c:1527

#7  = ExecQueryAndProcessResults (query=3Dquery@entry=3D0xbd8df7065330 "COPY = psql_pipeline FROM STDIN;",

  =   elapsed_msec=3Delapsed_msec@entry=3D0xffffd1a532e0, = svpt_gone_p=3Dsvpt_gone_p@entry=3D0xffffd1a532df,

    = is_watch=3Dis_watch@entry=3Dfalse, min_rows=3Dmin_rows@entry=3D0, = opt=3Dopt@entry=3D0x0,

  =   printQueryFout=3DprintQueryFout@entry=3D0x0) at = common.c:1828

#8  = 0x0000bd8dd0bfc6d0 in SendQuery (query=3D0xbd8df7065330 "COPY = psql_pipeline FROM STDIN;")

  =   at common.c:1212

#9  = 0x0000bd8dd0c10d3c in MainLoop (source=3Dsource@entry=3D0xe89cda9e0880 = <_IO_2_1_stdin_>)

  =   at mainloop.c:515

#10 = 0x0000bd8dd0bf7070 in process_file (filename=3D0x0,

    = use_relative_path=3Duse_relative_path@entry=3Dfalse) at = command.c:4870

#11 = 0x0000bd8dd0bf1b04 in main (argc=3D<optimized out>, = argv=3D<optimized out>) at startup.c:420


Nikita Kalinin

Postgres Professional: = 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

= --Apple-Mail=_2A498B81-115F-4DAC-8C16-5DE55C106A8F--