Improve handling of covering indexes

Improved support for covering indexes (with INCLUDE columns). Added the
ability for postgresql to render CREATE INDEX statements with an INCLUDE
clause from Core. Index reflection also report INCLUDE columns separately
for both mssql and postgresql (11+).

Fixes: #4458
Change-Id: If0b82103fbc898cdaeaf6a6d2d421c732744acd6

1 files changed, 72 insertions, 45 deletions

diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index ec9328c2f..e088cad01 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -144,7 +144,15 @@ class PartitionedReflectionTest(fixtures.TablesTest, AssertsExecutionResults):
     def test_reflect_index(self):
         idx = inspect(testing.db).get_indexes("data_values")
         eq_(
-            idx, [{"column_names": ["q"], "name": "my_index", "unique": False}]
+            idx,
+            [
+                {
+                    "name": "my_index",
+                    "unique": False,
+                    "column_names": ["q"],
+                    "include_columns": [],
+                }
+            ],
         )
 
     @testing.only_on("postgresql >= 11")
@@ -152,7 +160,17 @@ class PartitionedReflectionTest(fixtures.TablesTest, AssertsExecutionResults):
         idx = inspect(testing.db).get_indexes("data_values_4_10")
         # note the name appears to be generated by PG, currently
         # 'data_values_4_10_q_idx'
-        eq_(idx, [{"column_names": ["q"], "name": mock.ANY, "unique": False}])
+        eq_(
+            idx,
+            [
+                {
+                    "column_names": ["q"],
+                    "include_columns": [],
+                    "name": mock.ANY,
+                    "unique": False,
+                }
+            ],
+        )
 
 
 class MaterializedViewReflectionTest(
@@ -1031,7 +1049,11 @@ class ReflectionTest(fixtures.TestBase):
         conn.exec_driver_sql("ALTER TABLE t RENAME COLUMN x to y")
 
         ind = testing.db.dialect.get_indexes(conn, "t", None)
-        eq_(ind, [{"unique": False, "column_names": ["y"], "name": "idx1"}])
+        expected = [{"name": "idx1", "unique": False, "column_names": ["y"]}]
+        if testing.requires.index_reflects_included_columns.enabled:
+            expected[0]["include_columns"] = []
+
+        eq_(ind, expected)
         conn.close()
 
     @testing.fails_if("postgresql < 8.2", "reloptions not supported")
@@ -1055,19 +1077,20 @@ class ReflectionTest(fixtures.TestBase):
             )
 
             ind = testing.db.dialect.get_indexes(conn, "t", None)
-            eq_(
-                ind,
-                [
-                    {
-                        "unique": False,
-                        "column_names": ["x"],
-                        "name": "idx1",
-                        "dialect_options": {
-                            "postgresql_with": {"fillfactor": "50"}
-                        },
-                    }
-                ],
-            )
+
+            expected = [
+                {
+                    "unique": False,
+                    "column_names": ["x"],
+                    "name": "idx1",
+                    "dialect_options": {
+                        "postgresql_with": {"fillfactor": "50"}
+                    },
+                }
+            ]
+            if testing.requires.index_reflects_included_columns.enabled:
+                expected[0]["include_columns"] = []
+            eq_(ind, expected)
 
             m = MetaData()
             t1 = Table("t", m, autoload_with=conn)
@@ -1093,17 +1116,17 @@ class ReflectionTest(fixtures.TestBase):
             conn.exec_driver_sql("CREATE INDEX idx1 ON t USING gin (x)")
 
             ind = testing.db.dialect.get_indexes(conn, "t", None)
-            eq_(
-                ind,
-                [
-                    {
-                        "unique": False,
-                        "column_names": ["x"],
-                        "name": "idx1",
-                        "dialect_options": {"postgresql_using": "gin"},
-                    }
-                ],
-            )
+            expected = [
+                {
+                    "unique": False,
+                    "column_names": ["x"],
+                    "name": "idx1",
+                    "dialect_options": {"postgresql_using": "gin"},
+                }
+            ]
+            if testing.requires.index_reflects_included_columns.enabled:
+                expected[0]["include_columns"] = []
+            eq_(ind, expected)
             m = MetaData()
             t1 = Table("t", m, autoload_with=conn)
             eq_(
@@ -1133,14 +1156,17 @@ class ReflectionTest(fixtures.TestBase):
             # [{'column_names': ['x', 'name'],
             #  'name': 'idx1', 'unique': False}]
 
-            with testing.expect_warnings(
-                "INCLUDE columns for "
-                "covering index idx1 ignored during reflection"
-            ):
-                ind = testing.db.dialect.get_indexes(conn, "t", None)
+            ind = testing.db.dialect.get_indexes(conn, "t", None)
             eq_(
                 ind,
-                [{"unique": False, "column_names": ["x"], "name": "idx1"}],
+                [
+                    {
+                        "unique": False,
+                        "column_names": ["x"],
+                        "include_columns": ["name"],
+                        "name": "idx1",
+                    }
+                ],
             )
 
     @testing.provide_metadata
@@ -1499,18 +1525,19 @@ class ReflectionTest(fixtures.TestBase):
 
         # PostgreSQL will create an implicit index for an exclude constraint.
         # we don't reflect the EXCLUDE yet.
-        eq_(
-            insp.get_indexes("t"),
-            [
-                {
-                    "unique": False,
-                    "name": "quarters_period_excl",
-                    "duplicates_constraint": "quarters_period_excl",
-                    "dialect_options": {"postgresql_using": "gist"},
-                    "column_names": ["period"],
-                }
-            ],
-        )
+        expected = [
+            {
+                "unique": False,
+                "name": "quarters_period_excl",
+                "duplicates_constraint": "quarters_period_excl",
+                "dialect_options": {"postgresql_using": "gist"},
+                "column_names": ["period"],
+            }
+        ]
+        if testing.requires.index_reflects_included_columns.enabled:
+            expected[0]["include_columns"] = []
+
+        eq_(insp.get_indexes("t"), expected)
 
         # reflection corrects for the dupe
         reflected = Table("t", MetaData(testing.db), autoload=True)