How can I pool connections using psycopg and gevent?

Question

The psycopg docs state: "Psycopg connections are not green thread safe and can’t be used concurrently by different green threads. Trying to execute more than one command at time using one cursor per thread will result in an error (or a deadlock on versions before 2.4.2). Therefore, programmers are advised to either avoid sharing connections between coroutines or to use a library-friendly lock to synchronize shared connections, e.g. for pooling."

I can't find an implementation of pool that is green thread safe - are there any out there?

Answer 1

I assume you know gevent-psycopg2 module, which makes psycopg greenlet-friendly.

Looking for connection pooling solution I've tried 2 solutions:

• SQLALchemy - it seems to work properly with monkey-patched threads and gevent-psycopg2. The QueuePool class uses threading module internally for locking, monkey patching is thus necessary, even though gevent-psycopg2 makes psycopg2 green.

• there's a psycopg2 connection pooling example in gevent examples

I've tried both solutions, but not at production load - so I can't say about their robustness yet.

Answer 2

If you call gevent.monkey.patch_thread() you should be able to use psycopg2.pool.ThreadedConnectionPool.

• docs: http://initd.org/psycopg/docs/pool.html#psycopg2.pool.ThreadedConnectionPool
• source: https://github.com/dvarrazzo/psycopg/blob/2_4_5/lib/pool.py#L173