psycopg.OperationalError: consuming input failed: SSL SYSCALL error: EOF detected

I'm running some tasks which are actually taking more time than five minutes and I've already established connection with NeonDB(Postgres) as it's serverless when i use cursor.query it's throwing me error psycopg.OperationalError: consuming input failed: SSL SYSCALL error: EOF detected, I'm using below code

import os
from psycopg_pool import ConnectionPool
from time import sleep

# Create a connection pool
dbPool = ConnectionPool(
    conninfo="postgresurl?sslmode=require",
    min_size=2,
    max_size=10,
)


def dbConnection():
    '''
    Function to create a connection and cursor
    '''
    con = dbPool.getconn()
    cursor = con.cursor()
    return con, cursor


# Establishing DB Connection
con, cursor = dbConnection()
print("Sleep has started")
sleep(630)
print("Sleep has ended")
query = f"""UPDATE kPost SET score=0 WHERE postId='01HS94YD7K0JPN72ZMMK7WADAH';"""
cursor.execute(query)
print("Query executed")
con.commit()
print("Query committed")
con.close()
print("Connection closed")

import os
from psycopg_pool import ConnectionPool
from time import sleep

# Create a connection pool
dbPool = ConnectionPool(
    conninfo="postgresurl?sslmode=require",
    min_size=2,
    max_size=10,
)


def dbConnection():
    '''
    Function to create a connection and cursor
    '''
    con = dbPool.getconn()
    cursor = con.cursor()
    return con, cursor


# Establishing DB Connection
con, cursor = dbConnection()
print("Sleep has started")
sleep(630)
print("Sleep has ended")
query = f"""UPDATE kPost SET score=0 WHERE postId='01HS94YD7K0JPN72ZMMK7WADAH';"""
cursor.execute(query)
print("Query executed")
con.commit()
print("Query committed")
con.close()
print("Connection closed")

Output is as below:

Sleep has started
Sleep has ended
Traceback (most recent call last):
  File "/Users/dharmesh/Documents/.kBackend/kContainers/dbConnectionDisconnection/dbConnection.py", line 28, in <module>
    cursor.execute(query)
  File "/Users/dharmesh/Documents/.kBackend/kContainers/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.OperationalError: consuming input failed: SSL SYSCALL error: EOF detected

Sleep has started
Sleep has ended
Traceback (most recent call last):
  File "/Users/dharmesh/Documents/.kBackend/kContainers/dbConnectionDisconnection/dbConnection.py", line 28, in <module>
    cursor.execute(query)
  File "/Users/dharmesh/Documents/.kBackend/kContainers/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.OperationalError: consuming input failed: SSL SYSCALL error: EOF detected

It took almost 16m, Even though sleep is for just five and a half minute, As cursor.query() might be trying and trying for connection What can i do to avoid this issue ?

17 Replies

correct-apricot•2y ago

I think the problem is that you establish the connection, then idle for 10.5 minutes, during which time Neon autosuspends, if you move your connect line to after the sleep you should be fine:

print("Sleep has started")
sleep(630)
print("Sleep has ended")
# Establishing DB Connection
con, cursor = dbConnection()

print("Sleep has started")
sleep(630)
print("Sleep has ended")
# Establishing DB Connection
con, cursor = dbConnection()

rare-sapphireOP•2y ago

Can't we do something by which we can make connection alive more than ten minutes ? @andyhats

stormy-gold•2y ago

you can configure the auto-suspend behavior (or disable it completely) on our paid plans.

rare-sapphireOP•2y ago

@Mahmoud , Appreciate the response @andyhats and @Mahmoud , I want to ask you both that what deployment and production release strategies you guys use at Neon, Because, I'm seeing that you guys are unstoppable as in you are unveiling new features one by one, Every week like there's something always there, I want to know more about it as we are currently developing one application and it's like we are not getting all things are right and we are fumbling in many decisions So, If any of you can help me understand your strategies then that would be very helpful

stormy-gold•2y ago

We have multiple teams working on different components (eg. console UI, API, etc.). Each component can be deployed independently. We also leverage feature flags and use Preview Deployments

rare-sapphireOP•2y ago

@Mahmoud , You should write a blog about how you handle your backend and team management, Appreciate very fast reply

stormy-gold•2y ago

I'll let our engineering team know!

rare-sapphireOP•2y ago

We only working three people currently in our team, We have almost around seven repositories and two databases, One Deployed in NeonDB and another deployed in RDS, We are at the point of scrumblling night mare of juggling through repositories and it's taking so much time to just release one small feature, I like that you are working all very good and I see. your weekly release log and you guys are doing very well, Even here, You guys are very active, I'm assuming that it's saturday for you and you are still answering Keep it up

stormy-gold•2y ago

Yea the Neon team uses a monorepo, which makes some of this stuff easier. I'm curious about your setup. Are you using Neon for development and RDS for production? Or are they like two separate apps?

rare-sapphireOP•2y ago

@Mahmoud , We are using dev and test environment with NeonDB, Our production is in RDS We are thinking of moving production to NeonDB, Yet, We are waiting that if you guys make pay-as-you-go method available then we can move our production to NeonDB because current pricing is very hard bound and strict in compute and storage We have talked about https://discord.com/channels/1176467419317940276/1176467419938701375/1244220891022233730 If you want, I can create post on either support or feedback for this as this would really help lots of people @Mahmoud

stormy-gold•2y ago

Hmm, how big is your RDS instance?

rare-sapphireOP•2y ago

Not big, Yet, that much, may be around 1 to 2 GB

stormy-gold•2y ago

the pay-as-you-go pricing model is a bit tricky because many users prefer predictable pricing. I already shared your feedback with the team

rare-sapphireOP•2y ago

This can help lots of people - https://discord.com/channels/1176467419317940276/1176467419938701375/1244221664808407080

stormy-gold•2y ago

I understand. I'll make sure to relay your feedback to the product team 🫡 how much does that cost you per month?

rare-sapphireOP•2y ago

With storage and all considered that can cost us upto 40 to 50 USD, Although, Currently we are not fully in production yet, Meaning, We haven't published our applications yet, Once, We are going to do that it will increase rapidly We are building short-video application like YouTube Shorts and Instagram Reels Also, currently, We are not collecting much informations of users, Although, We will do that and our database size too will grow rapidly, @Mahmoud

stormy-gold•2y ago

Makes sense

Gaming

Programming

psycopg.OperationalError: consuming input failed: SSL SYSCALL error: EOF detected

Did you find this page helpful?