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

On Thu, Feb 24, 2022 at 12:47:42AM +0000, ldh(at)laurent-hasson(dot)com wrote:
> 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.

> It'd be nice to see a network trace for this too. Using tcpdump or
> wireshark. Preferably from the client side.
>
> Hello Justin,
>
> It has been ages! The issue has been happening a bit more often recently, as much as once every 10 days or so. As a reminder, the set up is Postgres 13.4 on Windows Server with 16cores and 64GB memory. The scenario where this occurs is an ETL tool called Pentaho Kettle (V7) connecting to the DB for DataWarehouse workloads. The tool is Java-based and connects via JDBC using postgresql-42.2.5.jar. There are no particular settings besides the socketTimeout setting mentioned above.
>
> The workload has some steps being lots of quick transactions for dimension tables for example, but some fact table calculations, especially large pivots, can make queries run for 40mn up to over an hour (a few of those).
>
> I caught these in the logs at the time of a failure but unsure what to make of that:
>
> 2022-02-21 02:10:43.605 EST [1368] LOG: unexpected EOF on client connection with an open transaction
> 2022-02-21 02:10:43.605 EST [3304] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.
>
> 2022-02-21 02:10:43.605 EST [3304] LOG: unexpected EOF on client connection with an open transaction
> 2022-02-21 02:31:38.808 EST [1704] LOG: setsockopt(TCP_USER_TIMEOUT) not supported

I suggest to enable CSV logging, which has many more columns of data.
Some of them might provide an insight - I'm not sure.
log_destination=csvlog (in addition to whatever else you have set).

And the aforementioned network trace. You could set a capture filter on TCP
SYN|RST so it's not absurdly large. From my notes, it might look like this:
(tcp[tcpflags]&(tcp-rst|tcp-syn|tcp-fin)!=0)

--
Justin