PostgreSQL get_indexes reports an invalid index (failed CREATE INDEX CONCURRENTLY) as a normal index #13577

Describe the bug

A CREATE INDEX CONCURRENTLY that fails (unique violation, cancel, crash) leaves the index in pg_index with indisvalid = false. Postgres ignores it for planning, but reflection returns it exactly like a healthy index.

The PG index query selects indisunique, indoption, indpred, indnkeyatts, indnullsnotdistinct and filters on indisprimary only; indisvalid and indisready are never read (lib/sqlalchemy/dialects/postgresql/base.py, _index_query: 2.0.52 lines 4885-4917, 2.1.0rc2 lines 5269-5301). The columns are already declared on pg_catalog.pg_index (pg_catalog.py lines 183 and 185).

Consequence: Inspector callers and Alembic autogenerate compare the reflected dict to metadata, find it equal, and emit nothing, so the broken index is never rebuilt.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

https://docs.sqlalchemy.org/en/21/dialects/postgresql.html#postgresql-index-reflection

SQLAlchemy Version in Use

2.0.52 and 2.1.0rc2

DBAPI (i.e. the database driver)

psycopg 3.3

Database Vendor and Major Version

PostgreSQL 15

Python Version

3.12

Operating system

macOS

To Reproduce

from sqlalchemy import create_engine, inspect, text

e = create_engine("postgresql+psycopg://...")
with e.connect().execution_options(isolation_level="AUTOCOMMIT") as c:
    c.execute(text("CREATE TABLE t (id int primary key, x int)"))
    c.execute(text("INSERT INTO t VALUES (1, 1), (2, 1)"))
    try:
        c.execute(text("CREATE UNIQUE INDEX CONCURRENTLY ix_t_x ON t (x)"))
    except Exception as err:
        print("build failed as intended:", type(err).__name__)
    print(c.execute(text("SELECT indisvalid FROM pg_index WHERE indexrelid = 'ix_t_x'::regclass")).scalar())
    print(inspect(c).get_indexes("t"))

Error

build failed as intended: IntegrityError
False
[{'name': 'ix_t_x', 'unique': True, 'column_names': ['x']}]

Same result when the index is marked invalid with UPDATE pg_index SET indisvalid = false.

Additional context

Proposed shape, mirroring the existing NOT VALID handling for check constraints (dialect_options["not_valid"] = True, base.py 5303): add indisvalid to the select and set dialect_options["postgresql_invalid"] = True only when it is false, so healthy indexes reflect unchanged. postgresql_invalid accepted by Index so a reflected Table still re-creates, with no DDL effect. I have this change with a test ready on a branch (https://github.com/HardMax71/sqlalchemy/tree/pg-reflect-invalid-index) and will open the PR once the issue is labelled open for pull requests.