- Postgresql "functional" indexes are necessarily skipped from the

autogenerate process, as the SQLAlchemy backend currently does not
support reflection of these structures.   A warning is emitted
both from the SQLAlchemy backend as well as from the Alembic
backend for Postgresql when such an index is detected.
fixes #282

3 files changed, 58 insertions, 2 deletions

diff --git a/alembic/ddl/postgresql.py b/alembic/ddl/postgresql.py
index ac3a5f4..1002687 100644
--- a/alembic/ddl/postgresql.py
+++ b/alembic/ddl/postgresql.py
@@ -1,10 +1,17 @@
 import re
 
 from .. import compat
+from .. import util
 from .base import compiles, alter_table, format_table_name, RenameTable
 from .impl import DefaultImpl
 from sqlalchemy.dialects.postgresql import INTEGER, BIGINT
-from sqlalchemy import text, Numeric
+from sqlalchemy import text, Numeric, Column
+
+if compat.sqla_08:
+    from sqlalchemy.sql.expression import UnaryExpression
+else:
+    from sqlalchemy.sql.expression import _UnaryExpression as UnaryExpression
+
 import logging
 
 log = logging.getLogger(__name__)
@@ -97,6 +104,19 @@ class PostgresqlImpl(DefaultImpl):
         for name, (uq, ix) in doubled_constraints.items():
             conn_indexes.remove(ix)
 
+        for idx in list(metadata_indexes):
+            if compat.sqla_08:
+                exprs = idx.expressions
+            else:
+                exprs = idx.columns
+            for expr in exprs:
+                if not isinstance(expr, (Column, UnaryExpression)):
+                    util.warn(
+                        "autogenerate skipping functional index %s; "
+                        "not supported by SQLAlchemy reflection" % idx.name
+                    )
+                    metadata_indexes.discard(idx)
+
 
 @compiles(RenameTable, "postgresql")
 def visit_rename_table(element, compiler, **kw):
diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst
index 1d5fb93..73a046f 100644
--- a/docs/build/changelog.rst
+++ b/docs/build/changelog.rst
@@ -7,6 +7,16 @@ Changelog
     :version: 0.7.5
 
     .. change::
+      :tags: bug, autogenerate, postgresql
+      :tickets: 282
+
+      Postgresql "functional" indexes are necessarily skipped from the
+      autogenerate process, as the SQLAlchemy backend currently does not
+      support reflection of these structures.   A warning is emitted
+      both from the SQLAlchemy backend as well as from the Alembic
+      backend for Postgresql when such an index is detected.
+
+    .. change::
       :tags: bug, autogenerate, mysql
       :tickets: 276
 
diff --git a/tests/test_autogen_indexes.py b/tests/test_autogen_indexes.py
index f798dd3..1f92649 100644
--- a/tests/test_autogen_indexes.py
+++ b/tests/test_autogen_indexes.py
@@ -1,10 +1,11 @@
 import sys
 from alembic.testing import TestBase
 from alembic.testing import config
+from alembic.testing import assertions
 
 from sqlalchemy import MetaData, Column, Table, Integer, String, \
     Numeric, UniqueConstraint, Index, ForeignKeyConstraint,\
-    ForeignKey
+    ForeignKey, func
 from alembic.testing import engines
 from alembic.testing import eq_
 from alembic.testing.env import staging_env
@@ -639,6 +640,31 @@ class PGUniqueIndexTest(AutogenerateUniqueIndexTest):
         eq_(diffs[0][1].name, "uq_name")
         eq_(len(diffs), 1)
 
+    def test_functional_ix(self):
+        m1 = MetaData()
+        m2 = MetaData()
+
+        t1 = Table(
+            'foo', m1,
+            Column('id', Integer, primary_key=True),
+            Column('email', String(50))
+        )
+        Index("email_idx", func.lower(t1.c.email), unique=True)
+
+        t2 = Table(
+            'foo', m2,
+            Column('id', Integer, primary_key=True),
+            Column('email', String(50))
+        )
+        Index("email_idx", func.lower(t2.c.email), unique=True)
+
+        with assertions.expect_warnings(
+                "Skipped unsupported reflection",
+                "autogenerate skipping functional index"
+        ):
+            diffs = self._fixture(m1, m2)
+        eq_(diffs, [])
+
 
 class MySQLUniqueIndexTest(AutogenerateUniqueIndexTest):
     reports_unnamed_constraints = True