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_compiler.py | 28 +++++++ test/dialect/postgresql/test_reflection.py | 117 ++++++++++++++++++----------- 2 files changed, 100 insertions(+), 45 deletions(-) (limited to 'test/dialect/postgresql') diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 556601fc6..c20498b2d 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -1721,6 +1721,34 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "(INCREMENT BY 7 START WITH 4))", ) + def test_index_extra_include_1(self): + metadata = MetaData() + tbl = Table( + "test", + metadata, + Column("x", Integer), + Column("y", Integer), + Column("z", Integer), + ) + idx = Index("foo", tbl.c.x, postgresql_include=["y"]) + self.assert_compile( + schema.CreateIndex(idx), "CREATE INDEX foo ON test (x) INCLUDE (y)" + ) + + def test_index_extra_include_2(self): + metadata = MetaData() + tbl = Table( + "test", + metadata, + Column("x", Integer), + Column("y", Integer), + Column("z", Integer), + ) + idx = Index("foo", tbl.c.x, postgresql_include=[tbl.c.y]) + self.assert_compile( + schema.CreateIndex(idx), "CREATE INDEX foo ON test (x) INCLUDE (y)" + ) + class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = postgresql.dialect() 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