From 4c6c1a6e87fe18c7800ecfa042af49da24fac114 Mon Sep 17 00:00:00 2001 From: Denis Laxalde Date: Tue, 20 May 2025 10:26:14 -0400 Subject: [PATCH] Use pg_index's indnatts when indnkeyatts is not available Using NULL when this column is not available does not work with old PostgreSQL (tested on version 9.6, as reported in #12600). Instead, use `indnatts` which should be equal to what `indnkeyatts` would be as there is no "included attributes" in the index on these old versions (but only "key columns"). From https://www.postgresql.org/docs/17/catalog-pg-index.html: * `indnatts`, "The total number of columns in the index [...]; this number includes both key and included attributes" * `indnkeyatts`, "The number of key columns in the index, not counting any included columns [...]" Fixes #12600. Closes: #12611 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12611 Pull-request-sha: 8ff48a6225ec58fdfa84aec75d487238281b1ac1 Change-Id: Idcadcd7db545bc1f73d85b29347c8ba388b1b41d (cherry picked from commit 9071811de76dea558f932215870e4a5513b30362) --- doc/build/changelog/unreleased_20/12600.rst | 7 +++++++ lib/sqlalchemy/dialects/postgresql/base.py | 14 ++++---------- 2 files changed, 11 insertions(+), 10 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/12600.rst diff --git a/doc/build/changelog/unreleased_20/12600.rst b/doc/build/changelog/unreleased_20/12600.rst new file mode 100644 index 0000000000..d544a225d3 --- /dev/null +++ b/doc/build/changelog/unreleased_20/12600.rst @@ -0,0 +1,7 @@ +.. change:: + :tags: bug, postgresql, reflection + :tickets: 12600 + + Fixed regression caused by :ticket:`10665` where the newly modified + constraint reflection query would fail on older versions of PostgreSQL + such as version 9.6. Pull request courtesy Denis Laxalde. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 9d4257cf0e..69b78bb138 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -4104,7 +4104,7 @@ class PGDialect(default.DefaultDialect): if self.server_version_info >= (11, 0): indnkeyatts = pg_catalog.pg_index.c.indnkeyatts else: - indnkeyatts = sql.null().label("indnkeyatts") + indnkeyatts = pg_catalog.pg_index.c.indnatts.label("indnkeyatts") if self.server_version_info >= (15,): indnullsnotdistinct = pg_catalog.pg_index.c.indnullsnotdistinct @@ -4224,10 +4224,7 @@ class PGDialect(default.DefaultDialect): # See note in get_multi_indexes all_cols = row["cols"] indnkeyatts = row["indnkeyatts"] - if ( - indnkeyatts is not None - and len(all_cols) > indnkeyatts - ): + if len(all_cols) > indnkeyatts: inc_cols = all_cols[indnkeyatts:] cst_cols = all_cols[:indnkeyatts] else: @@ -4579,7 +4576,7 @@ class PGDialect(default.DefaultDialect): if self.server_version_info >= (11, 0): indnkeyatts = pg_catalog.pg_index.c.indnkeyatts else: - indnkeyatts = sql.null().label("indnkeyatts") + indnkeyatts = pg_catalog.pg_index.c.indnatts.label("indnkeyatts") if self.server_version_info >= (15,): nulls_not_distinct = pg_catalog.pg_index.c.indnullsnotdistinct @@ -4689,10 +4686,7 @@ class PGDialect(default.DefaultDialect): # "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 is not None - and len(all_elements) > indnkeyatts - ): + if len(all_elements) > indnkeyatts: # this is a "covering index" which has INCLUDE columns # as well as regular index columns inc_cols = all_elements[indnkeyatts:] -- 2.47.2