How do I recycle cursors?

[swasheck]

I have a max_conn of 1000 (just for argument's sake) and I run out of cursors. This is the big batch that I run:

cursors = yield gen.Task(self.db.batch, queries)
            for key,cursor in cursors.items():
                worddata = {}
                if verse[0] not in results.keys():
                    results[verse[0]] = []  
                datapoints = cursor.fetchone()
                results[verse[0]].append( {
                        'wordid': datapoints[0],
                        'word': datapoints[1],
                        'inflectedmeaning': datapoints[2],
                        'root': datapoints[3],
                        'rootmeaning': datapoints[4],
                        'case': datapoints[5],
                        'person': datapoints[6],
                        'gender': datapoints[7],
                        'number': datapoints[8],
                        'tense': datapoints[9],
                        'voice': datapoints[10],
                        'mood': datapoints[11],
                } )     

I use this to render a page and each word has a link. At this point, I'm getting regular errors to the effect of PoolError: connection pool exausted or InterfaceError: cursor already closed. I'm pretty sure that I'm doing something wrong but am unsure as to what it is.

def get(self, word):            
        queries = {}
        queries['worddetails'] = ['select * from worddetailsbyword(%s)', (word,)]       
        queries['wordlocations'] = ['select v.id, reference, value from verse v join version vv on v.version_id = vv.id where to_tsvector(analysis_text) @@ to_tsquery(%s)', (word,)]       

        cursors = yield gen.Task(self.db.batch, queries)

        for key, cursor in cursors.items():
            self.write('Query results: %s = %s<br>' % (key, cursor.fetchall()))
        self.finish()

[FSX]

The PoolError says that you're out of connections, which means you've gone over the limit of 1000. db.batch requires a connection for each query (more or less).

I don't really know where the InterfaceError: cursor already closed comes from. Cursors shouldn't be closed. And there is not really a way to reuse cursors, because it would involve manually setting the connection state and callbacks. So that's why the cursor is hidden away in the connection class ans is inly used for fetching data after a query has been done.

[swasheck]

Right, but once the big batch is done my connection pool should drop, right? Postgres shows that these connections are gone so when I start a new batch I should be starting from scratch, right? Like i said, this is probably me doing something wrong.

On Sep 29, 2012, at 2:43 AM, Frank Smit notifications@github.com wrot

The PoolError says that you're out of connections, which means you've gone over the limit of 1000. db.batch requires a connection for each query (more or less).

I don't really know where the InterfaceError: cursor already closed comes from. Cursors shouldn't be closed. And there is not really a way to reuse cursors, because it would involve manually setting the connection state and callbacks. So that's why the cursor is hidden away in the connection class ans is inly used for fetching data after a query has been done.

— Reply to this email directly or view it on GitHub.

[FSX]

It should. Your code looks correct to me, so the only thing I can think of is that too much connections are needed or something goes wrong inside of Momoko. Can you give me more information about he amount of queries and data? I can make a test case and see if I can reproduce it.

[tesh11]

I came across an issue with cursors on the rewrite branch. In the busy() getter on Connection, the parentheses are missing on the call to isexecuting. That keeps the connections from being cleaned up.

[FSX]

I saw your pull request and merged it.

[tesh11]

Thanks!

On Wed, Oct 10, 2012 at 3:57 AM, Frank Smit notifications@github.comwrote:

I saw your pull request and merged it.

— Reply to this email directly or view it on GitHubhttps://github.com/FSX/momoko/issues/34#issuecomment-9294375.

[arielakeman]

I'm getting the same issue on 1.0.0b2 when the database is hit hard, particularly with a yield to an array of moko.Op's. The queries are heavy, it's a local (internal) application and can take between half a second and five seconds to execute.

The relevant parts of the stack trace are

File "D:...\momoko-1.0.0b2-py3.2.egg\momoko\connection.py", line 287, in io_callback self.callback(None)

results[i].fetchall() psycopg2.InterfaceError: cursor already closed

[FSX]

Ok, I think I might have an idea of what is going wrong. The connection might be closed before the results are fetched from the cursor. I'm not completely sure, but after running a small test I get the same error.

I'm at work/internship at the moment so I can't dive into right now. I have time later tonight.

[arielakeman]

Thanks, much appreciated.

[FSX]

I'm stuck. What I think is happing is that the connection is closed by the connection pool cleaner while the cursor is still being used.It seemed easy to fix by keeping a set of weak references to the cursors in the Connection class. When the set is empty (it becomes empty when the cursors are deleted by the garbage collector) the connection can be closed again. But I can't get it to work. I'm trying to figure out why. I also wasn't able to find anything in Psycopg2's connection class that idicates that there are still opened cursors. That would have made it a lot easier. Maybe I'm also overthinking it.

You could try disabling the pool cleaner by setting cleanup_timeout to 0 and setting the minimum amount of connections to a appropriate amount for now.

I'm going to FOSDEM tomorrow so I don't much time, but I'll try to come up with something while I'm on the train!

[FSX]

@arielakeman, @swasheck. I've written about a possible solution in #41.