MIME-Version: 1.0
References: <10df46d9.7dc2.19cd740a640.Coremail.jiye_sw@126.com>
 <CAKFQuwYcEvfna4HUkG9VkX0cR_xbRRhT_0qDaAHhrJ4UX0EW-g@mail.gmail.com>
 <5557f6f5.45bd.19cdb7a1764.Coremail.jiye_sw@126.com> <CAPmGK157wTfup6Jqt7K1p1=S0kGoBRSeQ1M50fLt93tcF1NQ5w@mail.gmail.com>
 <705358bc.2ead.19d5154b82f.Coremail.jiye_sw@126.com>
In-Reply-To: <705358bc.2ead.19d5154b82f.Coremail.jiye_sw@126.com>
From: Etsuro Fujita <etsuro.fujita@gmail.com>
Date: Sun, 12 Apr 2026 16:05:25 +0900
Message-ID: <CAPmGK17zmpCP6NAWWuQ9tPQpvLBRs6FDCMKxd37Chw_81ZnueQ@mail.gmail.com>
Subject: Re: Re: Re: FDW connection drops with "Connection timed out" during
 async append query due to TCP receive buffer filling up
To: jiye <jiye_sw@126.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAPmGK17zmpCP6NAWWuQ9tPQpvLBRs6FDCMKxd37Chw_81ZnueQ%40mail.gmail.com>
Precedence: bulk

On Fri, Apr 3, 2026 at 12:13=E2=80=AFPM jiye <jiye_sw@126.com> wrote:
> We have successfully reproduced this issue and gained a clearer understan=
ding of its root cause. The application uses a cursor to fetch partial resu=
lts in batches, with a delay between consecutive fetch operations. When the=
 interval between two batches exceeds the tcp_user_timeout threshold, the c=
onnection is terminated unexpectedly.

I think that that is *expected* behavior.

> In my analysis, during cursor-based queries, applications typically retri=
eve results in partial batches. If the number of rows fetched in a single b=
atch is smaller than the number of rows scanned from the local table, the e=
xecutor is unable to proceed with fetching rows from the foreign table.

IIRC, I don't think that Append in async mode has such a limitation;
it chooses the next partition to scan independently of the number of
rows returned from it.  No?

> To achieve a fundamental resolution, I propose two potential solutions:
>
> =E2=80=8CAlternate Row Fetching=E2=80=8C: Modify the executor to alternat=
ely retrieve rows from the local table and the foreign table, ensuring bala=
nced data flow between the two data sources.
> =E2=80=8CAsynchronous Tuple Storage=E2=80=8C: Implement a tuple storage m=
echanism to asynchronously cache results from the foreign table. This would=
 allow the executor to fetch foreign table results into the storage buffer =
independently, preventing TCP window exhaustion and decoupling the dependen=
cy between local and foreign data retrieval.

I suppose that these are improvements, but I'm not sure these are
really worth complicating the code, as what you are trying to solve by
these is not a normal case; in particular, it's far from normal to set
a tcp_user_timeout that the query cannot finish.

Anyway, thanks for sharing the analysis and ideas!  Sorry for the delay.

Best regards,
Etsuro Fujita