Re: Lock-ups with multi-threading and psycopg 2.0.11

> > The database access is simple - I create a single connection centrally with:
...
> > Whenever a thread needs to do an SQL query, it creates a cursor, does the
> > query and destroys it. For example:
...
> Note that if you're using the same connection for each thread, you
> won't get much parallelism.  While one thread is executing a query,
> all other threads will block if they try to do the same.  If you want
> the threads to be able to issue queries in parallel, they will have to
> use separate database connections.

OK, that's interesting and not something which I'd realized. So I will
probably change the code to separate connections for each thread.

> > The section which typically locks up has the following code:
> >
> > ?logging.debug('1')
> > ?cursor = db.cursor()
> > ?logging.debug('2')
> > ?cursor.execute('select "type", "resource", "usage", "user" from "resources"')
> > ?logging.debug('3')
> > ?rs = cursor.fetchall()
> > ?logging.debug('4')
> > ?cursor.execute('select "type", "resource", "key", "value" from "resource_properties"')
> > ?logging.debug('5')
> > ?ks = cursor.fetchall()
> > ?logging.debug('6')
> > ?cursor.close()
> > ?logging.debug('7')
> >
> > In the logs, I see '1' and '2', but not '3' or anything later.
> >
> > Given that the lock-up is isolated to inside the cursor.execute(), it seems
> > that it must be in psycopg? If that's the case, can you suggest a solution
> > or how I can debug this further? For example, how (and where?) should I add
> > trace inside the psycopg code to pinpoint this further?
>
> Is it possible that some other thread is performing a cursor.execute()
> call and blocking the thread you are watching?

It is definitely possible that I'm calling cursor.execute() twice in parallel
for different cursors from different threads on the same connection.

All of the SQL queries are very simple and should return immediately - they
are selects or updates on a table with ~100 rows and ~5 columns.

Is it meant to be thread-safe to execute cursor.execute() twice in parallel
on different cursors of the same connection? Or could I have hit some
deadlock/livelock in psycopg?

> > I suspect that the problem may be having too many simultaneous open cursors.
> > Is there a way that I can log how many of these there are? Where is the
> > limit set, and can I raise it?

Is this potentially a problem? It's quite plausible that I have ~100 cursors
on the same single connection at present.

I couldn't find any postgresql configuration or documentation relating to
numbers of supported cursors - only max_connections.


Thanks!

Richard.