psycopg2+gevent: generic connections are broken

[jamadden]

@cutz is using Celery+SQLAlchemy+psycopg2 in the same process as RelStorage+psycopg2 in a gevent-patched environment and reported this traceback:

   ...
  File "//python3.7/site-packages/sqlalchemy/engine/default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "//python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
  File "//python3.7/site-packages/relstorage/adapters/postgresql/drivers/psycopg2.py", line 208, in __call__
    conn.gevent_wait_write()
AttributeError: 'psycopg2.extensions.connection' object has no attribute 'gevent_wait_write'

Here was my response, which leaves two takeaways for RelStorage to do:

Recall that the gevent patch for psycopg2 is process-global. All connections want to yield, or none do.

The RelStorage gevent support is highly optimized just for RelStorage, and relies on connections being created through the 'gevent psycopg2' driver. That's where this attribute is supposed to be set.

There are two options without changes to RelStorage:

1. Install and use psycogreen. It installs generic gevent support. It's not as optimized, but it should work with all connections.

2. Do what we did for MySQL and sqlite, and write a tiny little engine for SQLAlchemy that creates connections using relstorage.adapters.postgresql.drivers.psycopg2:GeventPsycopg2Driver

There are two downsides to option (1). The first is that you lose BLOB support. I don't know if SQLAlchemy+celery uses blobs or not, but if it tries, it will blow up. Making BLOBs work is one of the optimizations in RelStorage's support. The second downside is that the automatic auto-patching that both RelStorage and psycogreen do are going to step on each other's toes, and you'll have to manually install the psycopgreen patch (after the gevent monkey patch) to be sure the one you want sticks.

Changes are needed in RelStorage too.

• This should be documented better for one,
• and for two, I bet we can fallback gracefully for non-RelStorage-created connections without much performance cost (though that doesn't fix blobs).

I just thought of a third action item:

• I bet we can open source the SQLAlchemy engines we write; then the documentation can reference them. That could be a project in any one of three organizations; this org might make the most sense. Heck, they could even start out as a module here...