Request a Demo
Home   >   mailing lists

Re:Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up - Mailing list pgsql-bugs

From jiye
Subject Re:Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up
Date
Msg-id 33b43b9c.bf0.19cda727a59.Coremail.jiye_sw@126.com
Whole thread Raw
In response to Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-bugs
Tree view

 I am not surprise the connection timeout, i just wonder if have some solution about this, because if we do not use asynchronous foreign table scan this sql query is ok.
 I just believe that local table scan too long will enhance the possibility of connection timeout. And our application set tcp_user_timeout parameter in consideration of high availability, so it can not be changed arbitrarily.

 when async fdw scan off, sql can be executed as follow:

postgres=# set enable_async_append TO off;
SET
postgres=#
postgres=# select f1(), *, '1','1','1','1','1' from local_fs union all select 1, * from foreign_sales limit 2;
 f1 | id | sale_date | product | amount | region | ?column? | ?column? | ?column? | ?column? | ?column?
----+----+------------+---------+---------+---------+----------+----------+----------+----------+----------
  1 | 1 | 2026-02-10 | pro | 1000.00 | tianjin | 1 | 1 | 1 | 1 | 1
  1 | 2 | 2026-02-10 | pro | 1000.00 | tianjin | 1 | 1 | 1 | 1 | 1
(2 rows)

2026-03-11 09:08:50.659 CST [861] LOG: duration: 20021.652 ms plan:
        Query Text: select f1(), *, '1','1','1','1','1' from local_fs union all select 1, * from foreign_sales limit 2;
        Limit (cost=0.00..0.54 rows=2 width=189) (actual time=10011.214..20021.544 rows=2 loops=1)
          -> Append (cost=0.00..2789043.10 rows=10240148 width=189) (actual time=10011.212..20021.539 rows=2 loops=1)
                -> Seq Scan on local_fs (cost=0.00..2737726.20 rows=10240120 width=189) (actual time=10011.192..20021.516 rows=2 loops=1)
                -> Foreign Scan on foreign_sales (cost=100.00..116.16 rows=28 width=2858) (never executed)
2026-03-11 09:08:50.659 CST [1324] LOG: statement: COMMIT TRANSACTION

At 2026-03-11 01:16:04, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>On Tue, 2026-03-10 at 18:17 +0800, jiye wrote:
>> I encountered an issue with foreign table asynchronous scans.
>> 
>> I'm testing on PostgreSQL 17 in a Windows WSL environment, with TCP keepalive settings configured as follows:
>> configure:
>> > tcp_keepalives_idle = 2
>> > tcp_keepalives_interval = 2
>> > tcp_keepalives_count = 3
>> > tcp_user_timeout = 9
>> > 
>> The foreign table was created with:
>> sql:
>> > CREATE TABLE ftab (a int, data varchar);
>> I inserted test data using:
>> sql:
>> > INSERT INTO ftab 
>> > SELECT generate_series(1, 10000), 
>> >        substring(md5(random()::text) || repeat(md5(random()::text), 127), 1, 40960);
>> Then I executed an async append query that unions with a simple query:
>> sql:
>> > SELECT f1(), 'local-data' 
>> > UNION ALL 
>> > SELECT * FROM f_ftab 
>> > LIMIT 2;
>> Note: f1() is a function that sleeps for 10 seconds, created by:
>> sql
>> > CREATE OR REPLACE FUNCTION f1() 
>> > RETURNS integer 
>> > LANGUAGE plpgsql AS $$
>> > BEGIN 
>> >     PERFORM pg_sleep(10); 
>> >     RETURN 1; 
>> > END; $$;
>> The abnormal behavior was reproduced: the foreign data server connection was disconnected, with the following error logs:
>> text
>> > 2026-03-10 17:52:55.969 CST [820] LOG: statement: FETCH 100 FROM c1
>> > 2026-03-10 17:52:56.495 CST [820] LOG: could not receive data from client: Connection timed out
>> > 2026-03-10 17:52:56.495 CST [820] LOG: unexpected EOF on client connection with an open transaction
>
>I am not a network expert, but if you set tcp_user_timeout = 9, why are you surprised if the
>connection times out after 9 seconds?
>
>Yours,
>Laurenz Albe

pgsql-bugs by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up
Next
From: "David G. Johnston"
Date:
Subject: Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up