RE: An I/O error occurred while sending to the backend (PG 13.4)

> -----Original Message-----
> From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
> Sent: Saturday, December 4, 2021 12:59
> To: ldh(at)laurent-hasson(dot)com
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: An I/O error occurred while sending to the backend (PG
> 13.4)
>
> On Sat, Dec 04, 2021 at 05:32:10PM +0000, ldh(at)laurent-hasson(dot)com
> wrote:
> > I have a data warehouse with a fairly complex ETL process that has
> been running for years now across PG 9.6, 11.2 and now 13.4 for the
> past couple of months. I have been getting the error "An I/O error
> occurred while sending to the backend" quite often under load in 13.4
> which I never used to get on 11.2. I have applied some tricks, particularly
> with the socketTimeout JDBC configuration.
> >
> > So my first question is whether anyone has any idea why this is
> happening? My hardware and general PG configuration have not
> changed between 11.2 and 13.4 and I NEVER experienced this on 11.2 in
> about 2y of production.
> >
> > Second, I have one stored procedure that takes a very long time to run
> (40mn more or less), so obviously, I'd need to set socketTimeout to
> something like 1h in order to call it and not timeout. That doesn't seem
> reasonable?
>
> Is the DB server local or remote (TCP/IP) to the client?
>
> Could you collect the corresponding postgres query logs when this
> happens ?
>
> It'd be nice to see a network trace for this too. Using tcpdump or
> wireshark.
> Preferably from the client side.
>
> FWIW, I suspect the JDBC socketTimeout is a bad workaround.
>
> --
> Justin

It's a remote server, but all on a local network. Network performance is I am sure not the issue. Also, the system is on Windows Server. What are you expecting to see out of a tcpdump? I'll try to get PG logs on the failing query.

Thank you,
Laurent.