Reflect mssql/postgresql filtered/partial indexes

Added support for inspection / reflection of partial indexes / filtered
indexes, i.e. those which use the ``mssql_where`` or ``postgresql_where``
parameters, with :class:`_schema.Index`.   The entry is both part of the
dictionary returned by :meth:`.Inspector.get_indexes` as well as part of a
reflected :class:`_schema.Index` construct that was reflected.  Pull
request courtesy Ramon Williams.

**Have a nice day!**
Fixes: #4966

Closes: #5504
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5504
Pull-request-sha: b3018bac987081193b2e65cfdb6aeb7d5d270fcd

Change-Id: Icbb2f93d1545700718ccb5222097185b815f5dbc

2 files changed, 28 insertions, 10 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index ab6e19cf4..e3d16f3f3 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -728,11 +728,13 @@ from ... import util
 from ...engine import cursor as _cursor
 from ...engine import default
 from ...engine import reflection
+from ...sql import coercions
 from ...sql import compiler
 from ...sql import elements
 from ...sql import expression
 from ...sql import func
 from ...sql import quoted_name
+from ...sql import roles
 from ...sql import util as sql_util
 from ...sql.type_api import to_instance
 from ...types import BIGINT
@@ -2205,6 +2207,10 @@ class MSDDLCompiler(compiler.DDLCompiler):
         whereclause = index.dialect_options["mssql"]["where"]
 
         if whereclause is not None:
+            whereclause = coercions.expect(
+                roles.DDLExpressionRole, whereclause
+            )
+
             where_compiled = self.sql_compiler.process(
                 whereclause, include_table=False, literal_binds=True
             )
@@ -2785,7 +2791,8 @@ class MSDialect(default.DefaultDialect):
 
         rp = connection.execution_options(future_result=True).execute(
             sql.text(
-                "select ind.index_id, ind.is_unique, ind.name "
+                "select ind.index_id, ind.is_unique, ind.name, "
+                "ind.filter_definition "
                 "from sys.indexes as ind join sys.tables as tab on "
                 "ind.object_id=tab.object_id "
                 "join sys.schemas as sch on sch.schema_id=tab.schema_id "
@@ -2806,6 +2813,12 @@ class MSDialect(default.DefaultDialect):
                 "unique": row["is_unique"] == 1,
                 "column_names": [],
             }
+
+            if row["filter_definition"] is not None:
+                indexes[row["index_id"]].setdefault("dialect_options", {})[
+                    "mssql_where"
+                ] = row["filter_definition"]
+
         rp = connection.execution_options(future_result=True).execute(
             sql.text(
                 "select ind_col.index_id, ind_col.object_id, col.name "
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c56cccd8d..409511416 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2131,6 +2131,10 @@ class PGDDLCompiler(compiler.DDLCompiler):
         whereclause = index.dialect_options["postgresql"]["where"]
 
         if whereclause is not None:
+            whereclause = coercions.expect(
+                roles.DDLExpressionRole, whereclause
+            )
+
             where_compiled = self.sql_compiler.process(
                 whereclause, include_table=False, literal_binds=True
             )
@@ -3459,9 +3463,10 @@ class PGDialect(default.DefaultDialect):
             IDX_SQL = """
               SELECT
                   i.relname as relname,
-                  ix.indisunique, ix.indexprs, ix.indpred,
+                  ix.indisunique, ix.indexprs,
                   a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
                   ix.indoption::varchar, i.reloptions, am.amname,
+                  pg_get_expr(ix.indpred, ix.indrelid),
                   %s as indnkeyatts
               FROM
                   pg_class t
@@ -3504,7 +3509,6 @@ class PGDialect(default.DefaultDialect):
                 idx_name,
                 unique,
                 expr,
-                prd,
                 col,
                 col_num,
                 conrelid,
@@ -3512,6 +3516,7 @@ class PGDialect(default.DefaultDialect):
                 idx_option,
                 options,
                 amname,
+                filter_definition,
                 indnkeyatts,
             ) = row
 
@@ -3524,13 +3529,6 @@ class PGDialect(default.DefaultDialect):
                 sv_idx_name = idx_name
                 continue
 
-            if prd and not idx_name == sv_idx_name:
-                util.warn(
-                    "Predicate of partial index %s ignored during reflection"
-                    % idx_name
-                )
-                sv_idx_name = idx_name
-
             has_idx = idx_name in indexes
             index = indexes[idx_name]
             if col is not None:
@@ -3586,6 +3584,9 @@ class PGDialect(default.DefaultDialect):
                 if amname and amname != "btree":
                     index["amname"] = amname
 
+                if filter_definition:
+                    index["postgresql_where"] = filter_definition
+
         result = []
         for name, idx in indexes.items():
             entry = {
@@ -3608,6 +3609,10 @@ class PGDialect(default.DefaultDialect):
                 entry.setdefault("dialect_options", {})[
                     "postgresql_using"
                 ] = idx["amname"]
+            if "postgresql_where" in idx:
+                entry.setdefault("dialect_options", {})[
+                    "postgresql_where"
+                ] = idx["postgresql_where"]
             result.append(entry)
         return result