From 0147e3c364de67e8f8050c55a544a7a04f47cf2c Mon Sep 17 00:00:00 2001 From: mike bayer Date: Wed, 18 Mar 2020 19:05:20 -0400 Subject: [PATCH] Don't include PG INCLUDE columns as regular index columns Fixed issue where a "covering" index, e.g. those which have an INCLUDE clause, would be reflected including all the columns in INCLUDE clause as regular columns. A warning is now emitted if these additional columns are detected indicating that they are currently ignored. Note that full support for "covering" indexes is part of :ticket:`4458`. Pull request courtesy Marat Sharafutdinov. Fixes: #5205 Closes: #5206 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5206 Pull-request-sha: 512a3817bb21991142add2d192fa7ce9b285369d Change-Id: I3196a2bf77dc5a6abd85b2fbf0ebff1b30d4fb00 (cherry picked from commit ee9bd719b7fc5f9ad34df8815ccca56d5a7a65cc) --- doc/build/changelog/unreleased_13/5205.rst | 10 +++++++ lib/sqlalchemy/dialects/postgresql/base.py | 26 +++++++++++++++--- test/dialect/postgresql/test_reflection.py | 32 ++++++++++++++++++++++ 3 files changed, 64 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/5205.rst diff --git a/doc/build/changelog/unreleased_13/5205.rst b/doc/build/changelog/unreleased_13/5205.rst new file mode 100644 index 0000000000..00129e7e21 --- /dev/null +++ b/doc/build/changelog/unreleased_13/5205.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, postgresql + :tickets: 5205 + + Fixed issue where a "covering" index, e.g. those which have an INCLUDE + clause, would be reflected including all the columns in INCLUDE clause as + regular columns. A warning is now emitted if these additional columns are + detected indicating that they are currently ignored. Note that full + support for "covering" indexes is part of :ticket:`4458`. Pull request + courtesy Marat Sharafutdinov. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 6611011b56..ac380ddd54 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -3260,7 +3260,8 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, NULL, ix.indkey%s, - %s, %s, am.amname + %s, %s, am.amname, + NULL as indnkeyatts FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -3297,7 +3298,8 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, c.conrelid, ix.indkey::varchar, - ix.indoption::varchar, i.reloptions, am.amname + ix.indoption::varchar, i.reloptions, am.amname, + %s as indnkeyatts FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -3320,7 +3322,11 @@ class PGDialect(default.DefaultDialect): ORDER BY t.relname, i.relname - """ + """ % ( + "ix.indnkeyatts" + if self.server_version_info >= (11, 0) + else "NULL", + ) t = sql.text(IDX_SQL).columns( relname=sqltypes.Unicode, attname=sqltypes.Unicode @@ -3343,6 +3349,7 @@ class PGDialect(default.DefaultDialect): idx_option, options, amname, + indnkeyatts, ) = row if expr: @@ -3366,7 +3373,18 @@ class PGDialect(default.DefaultDialect): if col is not None: index["cols"][col_num] = col if not has_idx: - index["key"] = [int(k.strip()) for k in idx_key.split()] + idx_keys = idx_key.split() + # "The number of key columns in the index, not counting any + # included columns, which are merely stored and do not + # participate in the index semantics" + if indnkeyatts and idx_keys[indnkeyatts:]: + util.warn( + "INCLUDE columns for covering index %s " + "ignored during reflection" % (idx_name,) + ) + idx_keys = idx_keys[:indnkeyatts] + + index["key"] = [int(k.strip()) for k in idx_keys] # (new in pg 8.3) # "pg_index.indoption" is list of ints, one per column/expr. diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 2ca8750011..d7f5b5381c 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -1111,6 +1111,38 @@ class ReflectionTest(fixtures.TestBase): "gin", ) + @testing.skip_if("postgresql < 11.0", "indnkeyatts not supported") + @testing.provide_metadata + def test_index_reflection_with_include(self): + """reflect indexes with include set""" + + metadata = self.metadata + + Table( + "t", + metadata, + Column("id", Integer, primary_key=True), + Column("x", ARRAY(Integer)), + Column("name", String(20)), + ) + metadata.create_all() + with testing.db.connect() as conn: + conn.execute("CREATE INDEX idx1 ON t (x) INCLUDE (name)") + + # prior to #5205, this would return: + # [{'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) + eq_( + ind, + [{"unique": False, "column_names": ["x"], "name": "idx1"}], + ) + @testing.provide_metadata def test_foreign_key_option_inspection(self): metadata = self.metadata -- 2.47.2