Error: SSL connection closed unexpectedly. Error while fetching large data using postgres FDW

Question

I am using postgres 13 and created a foreign server with use_remote_estimate: on and fetch_size: 10000. tableA here is a partition table by created_date. The query is running fine if the number of records is around 3M but throws an error for more than 3M records.

    select
      date_trunc('month', created_date)::date as month,
  count(distinct category_id) as categorys,
  count(distinct user_id) as workers
    from
      test.tableA A
    where
      is_expired = false
      and pool in (1, 2, 4, 6, 10)
      and created_date >= (select now() - interval '180 days')

    group by 1

Error :

ERROR:  SSL connection has been closed unexpectedly
CONTEXT:  remote SQL command: CLOSE c48
WARNING:  no connection to the server
Query failed
PostgreSQL said: SSL connection has been closed unexpectedly

Is there any configuration that needs to be added for handling large data?

On which side is that error getting reported? What does the other side report? — jjanes
– jjanes, Commented Dec 6, 2022 at 15:37

Laurenz Albe · Accepted Answer · 2022-12-06 13:32:32Z

1

Very likely there is a network component that forcibly closes TCP connections that are idle for a certain amount of time. If you cannot fix that network component, you can try to send TCP keepalive packets more often to keep the connection busy:

on the client side, you can set the option keepalives_idle = 120 on the foreign server
on the client side, you can set the parameter tcp_keepalives_idle = 120

That would send a keepalive packet every two minutes. Usually it is good enough to use one of the above options.

answered Dec 6, 2022 at 13:32

Laurenz Albe

62.7k4 gold badges58 silver badges94 bronze badges

I tried using both options but it is not working in this case.

Sushma Yadav
– Sushma Yadav

2022-12-09 05:07:10 +00:00
Commented Dec 9, 2022 at 5:07
A pity. But it is a network problem, and that's where you have to search.

Laurenz Albe
– Laurenz Albe

2022-12-09 08:46:03 +00:00
Commented Dec 9, 2022 at 8:46

Add a comment |

Stack Exchange Network

Error: SSL connection closed unexpectedly. Error while fetching large data using postgres FDW

1 Answer 1

Your Answer

Hot Network Questions

Error: SSL connection closed unexpectedly. Error while fetching large data using postgres FDW

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions