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?

score 1 · Answer 1 · answered Dec 06 '22 at 13:32

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.

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

1 Answers1