Fix indexes reflection in PostgreSQLpr/13

Reflection of indexes must preserve the order of columns.

Fixes issue 2767.

2 files changed, 24 insertions, 15 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 9d89fe160..d69110ebf 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1952,7 +1952,7 @@ class PGDialect(default.DefaultDialect):
           SELECT
               i.relname as relname,
               ix.indisunique, ix.indexprs, ix.indpred,
-              a.attname
+              a.attname, a.attnum, ix.indkey
           FROM
               pg_class t
                     join pg_index ix on t.oid = ix.indrelid
@@ -1972,11 +1972,12 @@ class PGDialect(default.DefaultDialect):
         t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode})
         c = connection.execute(t, table_oid=table_oid)
 
-        index_names = {}
-        indexes = []
+        indexes = defaultdict(lambda: defaultdict(dict))
+
         sv_idx_name = None
         for row in c.fetchall():
-            idx_name, unique, expr, prd, col = row
+            idx_name, unique, expr, prd, col, col_num, idx_key = row
+
             if expr:
                 if idx_name != sv_idx_name:
                     util.warn(
@@ -1985,22 +1986,25 @@ class PGDialect(default.DefaultDialect):
                       % idx_name)
                 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
-            if idx_name in index_names:
-                index_d = index_names[idx_name]
-            else:
-                index_d = {'column_names': []}
-                indexes.append(index_d)
-                index_names[idx_name] = index_d
-            index_d['name'] = idx_name
+
+            index = indexes[idx_name]
             if col is not None:
-                index_d['column_names'].append(col)
-            index_d['unique'] = unique
-        return indexes
+                index['cols'][col_num] = col
+            index['key'] = [int(k.strip()) for k in idx_key.split()]
+            index['unique'] = unique
+
+        return [
+            {'name': name,
+             'unique': idx['unique'],
+             'column_names': [idx['cols'][i] for i in idx['key']]}
+            for name, idx in indexes.items()
+        ]
 
     @reflection.cache
     def get_unique_constraints(self, connection, table_name,
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 255fd5595..5a8a54c46 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -95,6 +95,7 @@ class ComponentReflectionTest(fixtures.TablesTest):
     @classmethod
     def define_index(cls, metadata, users):
         Index("users_t_idx", users.c.test1, users.c.test2)
+        Index("users_all_idx", users.c.user_id, users.c.test2, users.c.test1)
 
     @classmethod
     def define_views(cls, metadata, schema):
@@ -339,7 +340,11 @@ class ComponentReflectionTest(fixtures.TablesTest):
         expected_indexes = [
             {'unique': False,
              'column_names': ['test1', 'test2'],
-             'name': 'users_t_idx'}]
+             'name': 'users_t_idx'},
+            {'unique': False,
+             'column_names': ['user_id', 'test2', 'test1'],
+             'name': 'users_all_idx'}
+        ]
         index_names = [d['name'] for d in indexes]
         for e_index in expected_indexes:
             assert e_index['name'] in index_names