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?

Sohn

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.

Dichotomy

Ended up using the connection pooling example in the gevent docs – Sohn

Updated link to psycopg2 gevent example: github.com/gevent/gevent/blob/master/examples/psycopg2_pool.py – Cochleate

Update: this lib has been declared "obselete" in favor of: bitbucket.org/dvarrazzo/psycogreen – Cantatrice

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

Arturoartus

Is this correct? Isn't this for multi threading as opposed to for gevent "green" threads? – Haroun

Answer 3

Here's a good pool (not just example, but used in production): psycopg2_pool.py

Cofield

is it possible to use it with sqlalchemy? – Claussen