- Repaired some typing and test issues related to the pypy

psycopg2cffi dialect, in particular that the current 2.7.0 version
does not have native support for the JSONB type.  The version detection
for psycopg2 features has been tuned into a specific sub-version
for psycopg2cffi.  Additionally, test coverage has been enabled
for the full series of psycopg2 features under psycopg2cffi.
fixes #3439

8 files changed, 103 insertions, 47 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 68d809eaf..3a87a44a7 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -19,6 +19,17 @@
     :version: 1.0.5
 
     .. change::
+        :tags: bug, postgresql, pypy
+        :tickets: 3439
+
+        Repaired some typing and test issues related to the pypy
+        psycopg2cffi dialect, in particular that the current 2.7.0 version
+        does not have native support for the JSONB type.  The version detection
+        for psycopg2 features has been tuned into a specific sub-version
+        for psycopg2cffi.  Additionally, test coverage has been enabled
+        for the full series of psycopg2 features under psycopg2cffi.
+
+    .. change::
         :tags: feature, ext
         :pullreq: bitbucket:54
 
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index f83bab2fa..35de41fef 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -501,6 +501,14 @@ class PGDialect_psycopg2(PGDialect):
     preparer = PGIdentifierPreparer_psycopg2
     psycopg2_version = (0, 0)
 
+    FEATURE_VERSION_MAP = dict(
+        native_json=(2, 5),
+        native_jsonb=(2, 5, 4),
+        sane_multi_rowcount=(2, 0, 9),
+        array_oid=(2, 4, 3),
+        hstore_adapter=(2, 4)
+    )
+
     _has_native_hstore = False
     _has_native_json = False
     _has_native_jsonb = False
@@ -547,11 +555,15 @@ class PGDialect_psycopg2(PGDialect):
         self._has_native_hstore = self.use_native_hstore and \
             self._hstore_oids(connection.connection) \
             is not None
-        self._has_native_json = self.psycopg2_version >= (2, 5)
-        self._has_native_jsonb = self.psycopg2_version >= (2, 5, 4)
+        self._has_native_json = \
+            self.psycopg2_version >= self.FEATURE_VERSION_MAP['native_json']
+        self._has_native_jsonb = \
+            self.psycopg2_version >= self.FEATURE_VERSION_MAP['native_jsonb']
 
         # http://initd.org/psycopg/docs/news.html#what-s-new-in-psycopg-2-0-9
-        self.supports_sane_multi_rowcount = self.psycopg2_version >= (2, 0, 9)
+        self.supports_sane_multi_rowcount = \
+            self.psycopg2_version >= \
+            self.FEATURE_VERSION_MAP['sane_multi_rowcount']
 
     @classmethod
     def dbapi(cls):
@@ -625,7 +637,8 @@ class PGDialect_psycopg2(PGDialect):
                     kw = {'oid': oid}
                     if util.py2k:
                         kw['unicode'] = True
-                    if self.psycopg2_version >= (2, 4, 3):
+                    if self.psycopg2_version >= \
+                            self.FEATURE_VERSION_MAP['array_oid']:
                         kw['array_oid'] = array_oid
                     extras.register_hstore(conn, **kw)
             fns.append(on_connect)
@@ -650,7 +663,7 @@ class PGDialect_psycopg2(PGDialect):
 
     @util.memoized_instancemethod
     def _hstore_oids(self, conn):
-        if self.psycopg2_version >= (2, 4):
+        if self.psycopg2_version >= self.FEATURE_VERSION_MAP['hstore_adapter']:
             extras = self._psycopg2_extras()
             oids = extras.HstoreAdapter.get_oids(conn)
             if oids is not None and oids[0]:
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2cffi.py b/lib/sqlalchemy/dialects/postgresql/psycopg2cffi.py
index f5c475d90..f0fe23df3 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2cffi.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2cffi.py
@@ -31,6 +31,18 @@ class PGDialect_psycopg2cffi(PGDialect_psycopg2):
     driver = 'psycopg2cffi'
     supports_unicode_statements = True
 
+    # psycopg2cffi's first release is 2.5.0, but reports
+    # __version__ as 2.4.4.  Subsequent releases seem to have
+    # fixed this.
+
+    FEATURE_VERSION_MAP = dict(
+        native_json=(2, 4, 4),
+        native_jsonb=(99, 99, 99),
+        sane_multi_rowcount=(2, 4, 4),
+        array_oid=(2, 4, 4),
+        hstore_adapter=(2, 4, 4)
+    )
+
     @classmethod
     def dbapi(cls):
         return __import__('psycopg2cffi')
diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py
index 5d74d54ad..52620bb78 100644
--- a/test/dialect/postgresql/test_dialect.py
+++ b/test/dialect/postgresql/test_dialect.py
@@ -60,16 +60,19 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
             eq_(testing.db.dialect._get_server_version_info(mock_conn(string)),
                 version)
 
-    @testing.only_on('postgresql+psycopg2', 'psycopg2-specific feature')
+    @testing.requires.psycopg2_compatibility
     def test_psycopg2_version(self):
         v = testing.db.dialect.psycopg2_version
         assert testing.db.dialect.dbapi.__version__.\
             startswith(".".join(str(x) for x in v))
 
-    @testing.only_on('postgresql+psycopg2', 'psycopg2-specific feature')
+    @testing.requires.psycopg2_compatibility
     def test_psycopg2_non_standard_err(self):
-        from psycopg2.extensions import TransactionRollbackError
-        import psycopg2
+        # under pypy the name here is psycopg2cffi
+        psycopg2 = testing.db.dialect.dbapi
+        TransactionRollbackError = __import__(
+            "%s.extensions" % psycopg2.__name__
+        ).extensions.TransactionRollbackError
 
         exception = exc.DBAPIError.instance(
             "some statement", {}, TransactionRollbackError("foo"),
@@ -79,7 +82,7 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
     # currently not passing with pg 9.3 that does not seem to generate
     # any notices here, would rather find a way to mock this
     @testing.requires.no_coverage
-    @testing.only_on('postgresql+psycopg2', 'psycopg2-specific feature')
+    @testing.requires.psycopg2_compatibility
     def _test_notice_logging(self):
         log = logging.getLogger('sqlalchemy.dialects.postgresql')
         buf = logging.handlers.BufferingHandler(100)
@@ -100,9 +103,7 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         assert 'will create implicit sequence' in msgs
         assert 'will create implicit index' in msgs
 
-    @testing.only_on(
-        ['postgresql+psycopg2', 'postgresql+pg8000'],
-        'psycopg2/pg8000-specific feature')
+    @testing.requires.psycopg2_or_pg8000_compatibility
     @engines.close_open_connections
     def test_client_encoding(self):
         c = testing.db.connect()
@@ -121,26 +122,23 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         new_encoding = c.execute("show client_encoding").fetchone()[0]
         eq_(new_encoding, test_encoding)
 
+    @testing.requires.psycopg2_compatibility
     def test_pg_dialect_use_native_unicode_from_config(self):
         config = {
-            'sqlalchemy.url': 'postgresql://scott:tiger@somehost/test',
+            'sqlalchemy.url': testing.db.url,
             'sqlalchemy.use_native_unicode': "false"}
 
         e = engine_from_config(config, _initialize=False)
         eq_(e.dialect.use_native_unicode, False)
 
         config = {
-            'sqlalchemy.url': 'postgresql://scott:tiger@somehost/test',
+            'sqlalchemy.url': testing.db.url,
             'sqlalchemy.use_native_unicode': "true"}
 
         e = engine_from_config(config, _initialize=False)
         eq_(e.dialect.use_native_unicode, True)
 
-
-    @testing.only_on(
-        ['postgresql+psycopg2', 'postgresql+pg8000',
-         'postgresql+psycopg2cffi'],
-        'psycopg2 / pg8000 - specific feature')
+    @testing.requires.psycopg2_or_pg8000_compatibility
     @engines.close_open_connections
     def test_autocommit_isolation_level(self):
         c = testing.db.connect().execution_options(
@@ -234,8 +232,7 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
             testing.db.execute('drop table speedy_users')
 
     @testing.fails_on('+zxjdbc', 'psycopg2/pg8000 specific assertion')
-    @testing.fails_on('pypostgresql',
-                      'psycopg2/pg8000 specific assertion')
+    @testing.requires.psycopg2_or_pg8000_compatibility
     def test_numeric_raise(self):
         stmt = text(
             "select cast('hi' as char) as hi", typemap={'hi': Numeric})
diff --git a/test/dialect/postgresql/test_query.py b/test/dialect/postgresql/test_query.py
index 27cb958fd..4a33644e0 100644
--- a/test/dialect/postgresql/test_query.py
+++ b/test/dialect/postgresql/test_query.py
@@ -549,7 +549,7 @@ class InsertTest(fixtures.TestBase, AssertsExecutionResults):
 
 class ServerSideCursorsTest(fixtures.TestBase, AssertsExecutionResults):
 
-    __only_on__ = 'postgresql+psycopg2'
+    __requires__ = 'psycopg2_compatibility',
 
     def _fixture(self, server_side_cursors):
         self.engine = engines.testing_engine(
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 0ebe68cba..32e0259aa 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -817,7 +817,7 @@ class ReflectionTest(fixtures.TestBase):
             }])
 
     @testing.provide_metadata
-    @testing.only_on("postgresql>=8.5")
+    @testing.only_on("postgresql >= 8.5")
     def test_reflection_with_unique_constraint(self):
         insp = inspect(testing.db)
 
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index e26526ef3..fac0f2df8 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -1567,7 +1567,7 @@ class HStoreRoundTripTest(fixtures.TablesTest):
         self._assert_data([{"k1": "r1v1", "k2": "r1v2"}])
 
     def _non_native_engine(self):
-        if testing.against("postgresql+psycopg2"):
+        if testing.requires.psycopg2_native_hstore.enabled:
             engine = engines.testing_engine(
                 options=dict(
                     use_native_hstore=False))
@@ -1581,7 +1581,7 @@ class HStoreRoundTripTest(fixtures.TablesTest):
         cols = insp.get_columns('data_table')
         assert isinstance(cols[2]['type'], HSTORE)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_insert_native(self):
         engine = testing.db
         self._test_insert(engine)
@@ -1590,7 +1590,7 @@ class HStoreRoundTripTest(fixtures.TablesTest):
         engine = self._non_native_engine()
         self._test_insert(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_criterion_native(self):
         engine = testing.db
         self._fixture_data(engine)
@@ -1624,7 +1624,7 @@ class HStoreRoundTripTest(fixtures.TablesTest):
         engine = self._non_native_engine()
         self._test_fixed_round_trip(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_fixed_round_trip_native(self):
         engine = testing.db
         self._test_fixed_round_trip(engine)
@@ -1645,12 +1645,12 @@ class HStoreRoundTripTest(fixtures.TablesTest):
             }
         )
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_unicode_round_trip_python(self):
         engine = self._non_native_engine()
         self._test_unicode_round_trip(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_unicode_round_trip_native(self):
         engine = testing.db
         self._test_unicode_round_trip(engine)
@@ -1659,7 +1659,7 @@ class HStoreRoundTripTest(fixtures.TablesTest):
         engine = self._non_native_engine()
         self._test_escaped_quotes_round_trip(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_hstore
     def test_escaped_quotes_round_trip_native(self):
         engine = testing.db
         self._test_escaped_quotes_round_trip(engine)
@@ -1691,14 +1691,16 @@ class HStoreRoundTripTest(fixtures.TablesTest):
 
 
 class _RangeTypeMixin(object):
-    __requires__ = 'range_types',
-    __dialect__ = 'postgresql+psycopg2'
+    __requires__ = 'range_types', 'psycopg2_compatibility'
     __backend__ = True
 
     def extras(self):
         # done this way so we don't get ImportErrors with
         # older psycopg2 versions.
-        from psycopg2 import extras
+        if testing.against("postgresql+psycopg2cffi"):
+            from psycopg2cffi import extras
+        else:
+            from psycopg2 import extras
         return extras
 
     @classmethod
@@ -1966,7 +1968,7 @@ class DateTimeTZRangeTests(_RangeTypeMixin, fixtures.TablesTest):
 
     def tstzs(self):
         if self._tstzs is None:
-            lower = testing.db.connect().scalar(
+            lower = testing.db.scalar(
                 func.current_timestamp().select()
             )
             upper = lower + datetime.timedelta(1)
@@ -2216,17 +2218,17 @@ class JSONRoundTripTest(fixtures.TablesTest):
         cols = insp.get_columns('data_table')
         assert isinstance(cols[2]['type'], self.test_type)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_insert_native(self):
         engine = testing.db
         self._test_insert(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_insert_native_nulls(self):
         engine = testing.db
         self._test_insert_nulls(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_insert_native_none_as_null(self):
         engine = testing.db
         self._test_insert_none_as_null(engine)
@@ -2284,15 +2286,15 @@ class JSONRoundTripTest(fixtures.TablesTest):
             },
         )
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_custom_native(self):
         self._test_custom_serialize_deserialize(True)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_custom_python(self):
         self._test_custom_serialize_deserialize(False)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_criterion_native(self):
         engine = testing.db
         self._fixture_data(engine)
@@ -2364,7 +2366,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
         engine = self._non_native_engine()
         self._test_fixed_round_trip(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_fixed_round_trip_native(self):
         engine = testing.db
         self._test_fixed_round_trip(engine)
@@ -2391,7 +2393,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
         engine = self._non_native_engine()
         self._test_unicode_round_trip(engine)
 
-    @testing.only_on("postgresql+psycopg2")
+    @testing.requires.psycopg2_native_json
     def test_unicode_round_trip_native(self):
         engine = testing.db
         self._test_unicode_round_trip(engine)
diff --git a/test/requirements.py b/test/requirements.py
index db5e65f4c..db4daca20 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -727,12 +727,12 @@ class DefaultRequirements(SuiteRequirements):
     @property
     def range_types(self):
         def check_range_types(config):
-            if not against(config, "postgresql+psycopg2"):
+            if not against(
+                    config,
+                    ["postgresql+psycopg2", "postgresql+psycopg2cffi"]):
                 return False
             try:
-                config.db.execute("select '[1,2)'::int4range;")
-                # only supported in psycopg 2.5+
-                from psycopg2.extras import NumericRange
+                config.db.scalar("select '[1,2)'::int4range;")
                 return True
             except:
                 return False
@@ -765,6 +765,27 @@ class DefaultRequirements(SuiteRequirements):
         )
 
     @property
+    def psycopg2_native_json(self):
+        return self.psycopg2_compatibility
+
+    @property
+    def psycopg2_native_hstore(self):
+        return self.psycopg2_compatibility
+
+    @property
+    def psycopg2_compatibility(self):
+        return only_on(
+            ["postgresql+psycopg2", "postgresql+psycopg2cffi"]
+        )
+
+    @property
+    def psycopg2_or_pg8000_compatibility(self):
+        return only_on(
+            ["postgresql+psycopg2", "postgresql+psycopg2cffi",
+             "postgresql+pg8000"]
+        )
+
+    @property
     def percent_schema_names(self):
         return skip_if(
             [