Python Postgres Connections with Green Threads

Question

I am using psycopg2 and I have more than one green thread in my application. Each thread gets connections using psycopg2.connect. Sometimes I get the following error:

error: Second simultaneous read on fileno 14 detected.  Unless you really know what you're doing,
make sure that only one greenthread can read any particular socket.  Consider using a pools.Pool.
If you do know what you're doing and want to disable this error, call
eventlet.debug.hub_prevent_multiple_readers(False) - MY THREAD=<built-in method switch of
GreenThread object at 0x7fbf6aafc048>; THAT THREAD=FdListener('read', 14,
<built-in method switch of greenlet.greenlet object at 0x7fbf6aafc470>,
<built-in method throw of greenlet.greenlet object at 0x7fbf6aafc470>)

I don't have connection pooling configured in this project as far as I know. (grep -ri pool .; returns nothing.)

Does psycopg2.connect reuse connections in some sort of implicit connection pool?

How do I get a new connection without reusing old connections (or sockets)?

Answer 1

Psycopg2 by default resuses connection to the DB when you call psycopg2.connect(). many times, so if multiple green threads use the same connection at the same time, you can get this error.

To avoid this, use a connection pool, such as SQLAlchemy orbuilt-in psycopg2.pool.

Alternatively, you can create a new connection to the DB every time you need to connect to DB.

import psycopg2
conn = psycopg2.connect(database="mydatabase", user="myusername", password="pass", host="myhost", port="myport", connection_factory=None)

Note, that this can be slow and very inefficient.

Personal advice: use a connection pool.