Getting 'Unexpected EOF on client connection with an open transaction' for large queries

[rishabh-agarwal792]

• OS: Container running Python3.7 Slim image
• Psycopg version: 2.9.4
• Python version: 3.7
• PostgreSQL version: 14

Describe the bug I am running a Python based service that interacts with PostgreSQL database using the pyscopg2 library. Everything works fine until I run a large insert query when I see the following error message in the database logs - Unexpected EOF on client connection with an open transaction.

Posts across the internet suggest that this is an issue with client walking away while the connection was still open. But interestingly, I don't see any error or exceptions in my Python code.

This is how I am executing the large (around 40MB in size) insert query inside my python service ~

try:
    with conn.cursor() as c:
        c.execute(query)
except Exception:
    logger.warning("Query failed %s" % query)
    raise

Even though the query failed to execute, I don't get any exception thrown.

Maybe it is some issue with the network that made Postgres think that the client walked away? Even if that is the case, I would expect some exception being thrown.

[dvarrazzo]

It seems a network problem to me, so "the client walks away" not in Python but at networking level. Psycopg closes the connection "politely" when it has a chance, but here it seems that the connection gets interrupted in the middle of the conversation between client and server (in the middle of the execute()).

I don't think we can help. But, out of curiosity, does the connection get interrupted while the client is sending data or there is a pause in the network (while the server is processing the result, and before giving the client a response)?

Are you sending the server a 40Mb query? If you have to do such a massive insert, can you use something else instead? (copy, execute_batch, psycopg 3 with row-by-row copy or executemany)?

[rishabh-agarwal792]

Thanks for the response @dvarrazzo.

This network problem seems to be occurring every time I am running my service. I don't see any signs of network interruption except the logs of Postgres database.

I know that executing such a gigantic query at once is not suggested, but does psycopg2 applies some limit on the size of query that can be executed? And, is there anything we can do to catch such errors in psycopg2?

[dvarrazzo]

No, psycopg doesn't apply any limit.

And, you are catching the error in psycopg2 already. Maybe what you would like to happen is to avoid the error... but the error is not in psycopg, it's in the network stack, psycopg can only report it.

I don't know if it's a problem with the timing, as in the query takes too long to be processed, and some tcp timeout kicks in, that's I was asking if there is network activity during processing. If that's the case, setting tcp keepalives on the connection socket (conn.fileno())can help. I don't have a one-liner for you but it's googlable information.