From 1a08d1aade046e9516d0527ffd2ac8bb43906171 Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Tue, 1 Sep 2020 14:36:40 -0600 Subject: 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 --- test/dialect/postgresql/test_reflection.py | 117 ++++++++++++++++++----------- 1 file changed, 72 insertions(+), 45 deletions(-) (limited to 'test/dialect/postgresql/test_reflection.py') 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) -- cgit v1.2.1