Re:Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up
| От | jiye |
|---|---|
| Тема | Re:Re: FDW connection drops with "Connection timed out" during async append query due to TCP receive buffer filling up |
| Дата | |
| Msg-id | 33b43b9c.bf0.19cda727a59.Coremail.jiye_sw@126.com обсуждение исходный текст |
| Ответ на | 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>) |
| Список | pgsql-bugs |
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 по дате отправления: