CODEWITHSUNDEEP
pythonpostgresqlsslsqlalchemysystem-calls
Sheena
Sheena

Reputation: 16212

SSL syscall error bad file descriptor using sqlalchemy and postgres

So I have a daemon process that talks to Postgres via sqlalchemy. The daemon does something like this:

while True:
    oEngine = setup_new_engine()
    with oEngine.connect() as conn:
        Logger.debug("connection established")
        DBSession = sessionmaker(bind=conn)()
        Logger.debug('DBSession created. id={0}'.format(id(DBSession)))

        #do a bunch of stuff with DBSession

        DBSession.commit()
        Logger.debug('DBSession committed. id={0}'.format(id(DBSession)))

On the first iteration of the forever loop everything works great. For a while. The DBSession successfully makes a few queries to the database. But then one query fails with the error:

OperationalError: (OperationalError) SSL SYSCALL error: Bad file descriptor

This speaks to me of a closed connection or file descriptor being used. But the connections are created and maintained by the daemon so I don't know what this means.

In other words what happens is:

 create engine
 open connection
 setup dbsession
 query dbsession => works great
 query dbsession => ERROR

The query in question looks like:

 DBSession.query(Login)
                        .filter(Login.LFTime == oLineTime)
                        .filter(Login.success == self.success)
                        .count()

which seems perfectly reasonable to me.

My question is: What kinds of reasons could there be for this kind of behaviour and how can I fix it or isolate the problem?

Let me know if you need more code. There is a heck of a lot of it so I went for the minimalist approach here...

Upvotes: 3

Views: 2871

Answers (2)

Robert N
Robert N

Reputation: 1174

You are creating the session inside your while loop, which is very ill-advised. With the code the way you had it the first time, you would spawn off a new connection at every iteration and leave it open. Before too long, you would be bound to hit some kind of limit and be unable to open yet another new session. (What kind of limit? Hard to say, but it could be a memory condition since DB connections are pretty weighty; it could be a DB-server limit where it will only accept a certain number of simultaneous user connections for performance reasons; hard to know and it doesn't really matter, because whatever the limit was, it has prevented you from using a very wasteful approach and hence has worked as intended!)

The solution you have hit upon fixes the problem because, as you open a new connection with each loop, so you also close it with each loop, freeing up the resources and allowing additional loops to create sessions of their own and succeed. However, this is still a lot of unnecessary busyness and a waste of processing resources on both the server and the client. I suspect it would work just as well-- and potentially be a lot faster-- if you move the sessionmaker outside the while loop. 

def main():
    oEngine = setup_new_engine()
    with oEngine.connect() as conn:
        Logger.debug("connection established")
        DBSession = sessionmaker(bind=conn)()

        apparently_infinite_loop(DBSession)

        # close only after we are done and have somehow exited the infinite loop
        DBSession.close()


def apparently_infinite_loop(DBSession):
    while True:

        #do a bunch of stuff with DBSession

        DBSession.commit()

I don't currently have a working sqlalchemy setup, so you likely have some syntax errors in there, but anyway I hope it makes the point about the fundamental underlying issue.

More detail is available here: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#session-faq-whentocreate

Some points from the docs to note:

  1. "The Session will begin a new transaction if it is used again". So this is why you don't need to be constantly opening new sessions in order to get transaction scope; a commit is all it takes.
  2. "As a general rule, the application should manage the lifecycle of the session externally to functions that deal with specific data." So your fundamental problem originally (and still) is all of that session management going on right down there inside the while loop right alongside your data processing code.

Upvotes: 1

Sheena
Sheena

Reputation: 16212

I fixed this by thinking about the session scope instead of the transaction scope.

while True:
    do_stuff()

def do_stuff():
    oEngine = setup_new_engine()
    with oEngine.connect() as conn:
        Logger.debug("connection established")
        DBSession = sessionmaker(bind=conn)()

        #do a bunch of stuff with DBSession

        DBSession.commit()
        DBSession.close()

I would still like to know why this fixed things though...

Upvotes: 2

Related Questions