Date: Sat, 4 Dec 2021 11:59:26 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: ldh@laurent-hasson.com
Cc: pgsql-performance@postgresql.org
Subject: Re: An I/O error occurred while sending to the backend (PG 13.4)
Message-ID: <20211204175926.GC17618@telsasoft.com>
References: 
 <MN2PR15MB2560DC71C217FF03AC9082F5856B9@MN2PR15MB2560.namprd15.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <MN2PR15MB2560DC71C217FF03AC9082F5856B9@MN2PR15MB2560.namprd15.prod.outlook.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211204175926.GC17618%40telsasoft.com>
Precedence: bulk

On Sat, Dec 04, 2021 at 05:32:10PM +0000, ldh@laurent-hasson.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