Documentation Gap: Invalid Index Skipping During ATTACH PARTITION

Core Problem

When ALTER TABLE ... ATTACH PARTITION is executed, PostgreSQL attempts to match indexes on the partition being attached to corresponding indexes on the parent partitioned table. If a matching index is found, it is "attached" to the parent's index (creating a proper partition index hierarchy). If no match is found, a new index is created on the partition.

The subtlety—introduced by commit fc55c7ff8d1 (back-patched as e90e9275f56 through v11)—is that invalid indexes are excluded from this matching process. An index can become invalid due to a failed CREATE INDEX CONCURRENTLY operation (which marks the index with indisvalid = false in pg_index rather than dropping it). When such an invalid index exists on a partition, ATTACH PARTITION silently ignores it and creates a brand new valid index, leaving the user with two indexes on the same column(s): one invalid (the old failed one) and one valid (the newly created one).

Why This Matters Architecturally

The partition index tree in PostgreSQL must maintain consistency: a partitioned index on a parent table must have valid child indexes on all partitions to function correctly for query planning and execution. Attaching an invalid index to a parent's index would create a broken index tree where some partitions have incomplete/corrupt index data. The fc55c7ff8d1 fix correctly prevents this scenario.

However, the silent creation of a duplicate index is a user-facing behavior change that was never documented. Users who encounter failed CREATE INDEX CONCURRENTLY operations (which are not uncommon in production, e.g., due to deadlocks, unique violations, or cancellations) may be confused by:

1. The presence of an unexpected new index after ATTACH PARTITION
2. The continued existence of the invalid index (which still consumes disk space and appears in pg_indexes)
3. The lack of any WARNING or NOTICE during the operation

Proposed Solutions

Original Patch (moali.pg)

Adds a new sentence to the ALTER TABLE reference page explicitly noting that invalid indexes are skipped and a new index is created when no valid equivalent is found. The exact wording is not shown but described as additive documentation.

Counter-Proposal (samimseih)

Rather than adding a new sentence, Sami Imseih proposes restructuring the existing documentation to:

1. Flip the clause order — put the positive case (valid equivalent found → attach) first, and the fallback case (no valid equivalent → create new) second
2. Insert the word "valid" — making explicit that the equivalent index must be valid to qualify for attachment

The proposed rewording:

For each index in the target table, if a valid equivalent
index already exists in the partition, it will be attached
to the target table's index, as if
ALTER INDEX ATTACH PARTITION had been executed;
otherwise, a corresponding one will be created.

This approach is more elegant because it naturally integrates the validity requirement into the existing sentence structure rather than bolting on an additional explanatory sentence. It also improves readability by presenting the "happy path" first.

Technical Context

The relevant code path is in src/backend/commands/tablecmds.c in the ATExecAttachPartition function (and related index-matching helpers). The index matching logic iterates over the partition's indexes and checks indisvalid from pg_index as a filter criterion. The key catalog field is:

• pg_index.indisvalid: Set to false when an index build fails (particularly during concurrent operations) or when an index is being dropped concurrently.

This is distinct from pg_index.indisready (which controls whether the index is updated by DML) and pg_index.indislive (which controls visibility to new transactions during DROP).

Assessment

This is a straightforward documentation-only fix for a real usability gap. The counter-proposal from Sami Imseih is superior in both conciseness and clarity. The change is appropriate for back-patching since the behavior has existed since v11. No code changes are needed—the runtime behavior is already correct.