- repair get_foreign_table_names() for PGInsp/dialect levelpr128

- repair get_view_names()
- changelog + migration note

6 files changed, 99 insertions, 23 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 9c7f207cc..b00dbb0a7 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,23 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: feature, postgresql
+        :tickets: 2891
+        :pullreq: github:128
+
+        Support has been added for reflection of materialized views
+        and foreign tables, as well as support for materialized views
+        within :meth:`.Inspector.get_view_names`, and a new method
+        :meth:`.PGInspector.get_foreign_table_names` available on the
+        Postgresql version of :class:`.Inspector`.  Pull request courtesy
+        Rodrigo Menezes.
+
+        .. seealso::
+
+            :ref:`feature_2891`
+
+
+    .. change::
         :tags: feature, orm
 
         Added new event handlers :meth:`.AttributeEvents.init_collection`
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 6a48b31fa..d967afa35 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -865,6 +865,34 @@ method that returns information on all available ``ENUM`` types::
 
 	:meth:`.PGInspector.get_enums`
 
+.. _feature_2891:
+
+Postgresql Dialect reflects Materialized Views, Foreign Tables
+--------------------------------------------------------------
+
+Changes are as follows:
+
+* the :class:`Table` construct with ``autoload=True`` will now match a name
+  that exists in the database as a materialized view or foriegn table.
+
+* :meth:`.Inspector.get_view_names` will return plain and materialized view
+  names.
+
+* :meth:`.Inspector.get_table_names` does **not** change for Postgresql, it
+  continues to return only the names of plain tables.
+
+* A new method :meth:`.PGInspector.get_foreign_table_names` is added which
+  will return the names of tables that are specifically marked as "foreign"
+  in the Postgresql schema tables.
+
+The change to reflection involves adding ``'m'`` and ``'f'`` to the list
+of qualifiers we use when querying ``pg_class.relkind``, but this change
+is new in 1.0.0 to avoid any backwards-incompatible surprises for those
+running 0.9 in production.
+
+:ticket:`2891`
+
+
 MySQL internal "no such table" exceptions not passed to event handlers
 ----------------------------------------------------------------------
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index df9797658..b9a0d461b 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -401,6 +401,7 @@ The value passed to the keyword argument will be simply passed through to the
 underlying CREATE INDEX command, so it *must* be a valid index type for your
 version of PostgreSQL.
 
+
 Special Reflection Options
 --------------------------
 
@@ -1679,22 +1680,18 @@ class PGInspector(reflection.Inspector):
         schema = schema or self.default_schema_name
         return self.dialect._load_enums(self.bind, schema)
 
-    def get_foreign_table_names(self, connection, schema=None, **kw):
-        if schema is not None:
-            current_schema = schema
-        else:
-            current_schema = self.default_schema_name
+    def get_foreign_table_names(self, schema=None):
+        """Return a list of FOREIGN TABLE names.
 
-        result = connection.execute(
-            sql.text("SELECT relname FROM pg_class c "
-                     "WHERE relkind = 'f' "
-                     "AND '%s' = (select nspname from pg_namespace n "
-                     "where n.oid = c.relnamespace) " %
-                     current_schema,
-                     typemap={'relname': sqltypes.Unicode}
-                     )
-        )
-        return [row[0] for row in result]
+        Behavior is similar to that of :meth:`.Inspector.get_table_names`,
+        except that the list is limited to those tables tha report a
+        ``relkind`` value of ``f``.
+
+        .. versionadded:: 1.0.0
+
+        """
+        schema = schema or self.default_schema_name
+        return self.dialect._get_foreign_table_names(self.bind, schema)
 
 
 class CreateEnumType(schema._CreateDropBase):
@@ -2095,6 +2092,24 @@ class PGDialect(default.DefaultDialect):
         return [row[0] for row in result]
 
     @reflection.cache
+    def _get_foreign_table_names(self, connection, schema=None, **kw):
+        if schema is not None:
+            current_schema = schema
+        else:
+            current_schema = self.default_schema_name
+
+        result = connection.execute(
+            sql.text("SELECT relname FROM pg_class c "
+                     "WHERE relkind = 'f' "
+                     "AND '%s' = (select nspname from pg_namespace n "
+                     "where n.oid = c.relnamespace) " %
+                     current_schema,
+                     typemap={'relname': sqltypes.Unicode}
+                     )
+        )
+        return [row[0] for row in result]
+
+    @reflection.cache
     def get_view_names(self, connection, schema=None, **kw):
         if schema is not None:
             current_schema = schema
@@ -2103,7 +2118,7 @@ class PGDialect(default.DefaultDialect):
         s = """
         SELECT relname
         FROM pg_class c
-        WHERE relkind IN ('m', v')
+        WHERE relkind IN ('m', 'v')
           AND '%(schema)s' = (select nspname from pg_namespace n
           where n.oid = c.relnamespace)
         """ % dict(schema=current_schema)
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index b72290588..cf1f2d3dd 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -227,9 +227,6 @@ class Inspector(object):
         :param schema: Optional, retrieve names from a non-default schema.
          For special quoting, use :class:`.quoted_name`.
 
-        .. versionchanged:: 1.0.0 now returns materialized views as well
-        as normal views.
-
         """
 
         return self.dialect.get_view_names(self.bind, schema,
diff --git a/setup.cfg b/setup.cfg
index 51a4e30bf..b70086605 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -31,7 +31,7 @@ oracle_db_link = test_link
 # CREATE EXTENSION postgres_fdw;
 # GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO public;
 # this can be localhost to create a loopback foreign table
-# postgres_test_db_link = localhost
+postgres_test_db_link = localhost
 
 
 [db]
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 4bc658694..b8b9be3de 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -59,9 +59,15 @@ class ForeignTableReflectionTest(fixtures.TablesTest, AssertsExecutionResults):
     def test_get_foreign_table_names(self):
         inspector = inspect(testing.db)
         with testing.db.connect() as conn:
-            ft_names = inspector.get_foreign_table_names(conn)
+            ft_names = inspector.get_foreign_table_names()
             eq_(ft_names, ['test_foreigntable'])
 
+    def test_get_table_names_no_foreign(self):
+        inspector = inspect(testing.db)
+        with testing.db.connect() as conn:
+            names = inspector.get_table_names()
+            eq_(names, ['testtable'])
+
 
 class MaterialiedViewReflectionTest(
         fixtures.TablesTest, AssertsExecutionResults):
@@ -85,15 +91,24 @@ class MaterialiedViewReflectionTest(
                 {"id": 89, "data": 'd1'}
             )
 
-        view = sa.DDL(
+        materialized_view = sa.DDL(
             "CREATE MATERIALIZED VIEW test_mview AS "
             "SELECT * FROM testtable")
 
-        sa.event.listen(testtable, 'after_create', view)
+        plain_view = sa.DDL(
+            "CREATE VIEW test_regview AS "
+            "SELECT * FROM testtable")
+
+        sa.event.listen(testtable, 'after_create', plain_view)
+        sa.event.listen(testtable, 'after_create', materialized_view)
         sa.event.listen(
             testtable, 'before_drop',
             sa.DDL("DROP MATERIALIZED VIEW test_mview")
         )
+        sa.event.listen(
+            testtable, 'before_drop',
+            sa.DDL("DROP VIEW test_regview")
+        )
 
     def test_mview_is_reflected(self):
         metadata = MetaData(testing.db)
@@ -109,6 +124,10 @@ class MaterialiedViewReflectionTest(
             [(89, 'd1',)]
         )
 
+    def test_get_view_names(self):
+        insp = inspect(testing.db)
+        eq_(set(insp.get_view_names()), set(['test_mview', 'test_regview']))
+
 
 class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test PostgreSQL domains"""