ALTER INDEX …​ UNUSABLE Implementation Notes

PG_KEYWORD("until", UNTIL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unusable", UNUSABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("update", UPDATE, UNRESERVED_KEYWORD, BARE_LABEL)

| ALTER INDEX qualified_name REBUILD rebuild_index_opt_list
        {
            OraAlterIndexRebuildStmt *n = makeNode(OraAlterIndexRebuildStmt);
            n->relation = $3;
            n->options = $5;
            $$ = (Node *) n;
        }
| ALTER INDEX qualified_name UNUSABLE
        {
            OraAlterIndexUnusableStmt *n = makeNode(OraAlterIndexUnusableStmt);
            n->relation = $3;
            $$ = (Node *) n;
        }

bool        indislive;         /* is this index alive at all? */
bool        indisreplident;    /* is this index the identity for replication? */
bool        indisunusable;     /* manually disabled via ALTER INDEX ...
                                 * UNUSABLE (Oracle compat); skipped by the
                                 * planner and unmaintained by DML until
                                 * REBUILD */

/*
 * index_set_unusable - set or clear pg_index.indisunusable
 *
 * Oracle-compatible companion to index_set_state_flags(): flips the
 * "manually disabled via ALTER INDEX ... UNUSABLE" bit.  Unlike the
 * indisvalid/indisready/indislive trio, this flag is not part of the
 * CREATE/DROP INDEX CONCURRENTLY state machine, so no transition
 * invariants need to be asserted here.
 */
void
index_set_unusable(Oid indexId, bool unusable)
{
    Relation    pg_index;
    HeapTuple   indexTuple;
    Form_pg_index indexForm;

    pg_index = table_open(IndexRelationId, RowExclusiveLock);

    indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexId));
    if (!HeapTupleIsValid(indexTuple))
        elog(ERROR, "cache lookup failed for index %u", indexId);
    indexForm = (Form_pg_index) GETSTRUCT(indexTuple);

    if (indexForm->indisunusable != unusable)
    {
        indexForm->indisunusable = unusable;
        CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
    }

    table_close(pg_index, RowExclusiveLock);
}

void
ExecOraAlterIndexUnusable(ParseState *pstate, const OraAlterIndexUnusableStmt *stmt)
{
    ReindexParams params = {0};
    struct ReindexIndexCallbackState cbstate;
    Oid         indexOid;
    char        relkind;

    if (ORA_PARSER != compatible_db)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("ALTER INDEX ... UNUSABLE is only available when compatible_db is oracle")));

    /*
     * Reuse the same parsing/locking approach as the non-CONCURRENTLY
     * path of ExecOraAlterIndexRebuild(): AccessExclusiveLock on the
     * index itself, ShareLock on the heap table (decided by the shared
     * callback), with the permission check also handled by that
     * callback.
     */
    cbstate.params = params;
    cbstate.locked_table_oid = InvalidOid;

    indexOid = RangeVarGetRelidExtended(stmt->relation,
                                        AccessExclusiveLock,
                                        0,
                                        RangeVarCallbackForReindexIndex,
                                        &cbstate);

    relkind = get_rel_relkind(indexOid);
    if (relkind == RELKIND_PARTITIONED_INDEX)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("ALTER INDEX ... UNUSABLE is not supported for partitioned indexes"),
                 errhint("Mark each partition's leaf index unusable individually.")));

    index_set_unusable(indexOid, true);
}

if (!index->indisvalid)
{
    index_close(indexRelation, NoLock);
    continue;
}

/*
 * Ignore indexes manually disabled via the Oracle-compatible
 * ALTER INDEX ... UNUSABLE.  This check is unconditional (not
 * gated on the current session's compatible_db): indisunusable
 * is a catalog fact about the index, not a per-session parser
 * choice, and every session must honor it consistently to avoid
 * planning against a stale/unmaintained index.
 */
if (index->indisunusable)
{
    index_close(indexRelation, NoLock);
    continue;
}

ii = makeIndexInfo(indexStruct->indnatts,
                   indexStruct->indnkeyatts,
                   index->rd_rel->relam,
                   RelationGetIndexExpressions(index),
                   RelationGetIndexPredicate(index),
                   indexStruct->indisunique,
                   indexStruct->indnullsnotdistinct,
                   /*
                    * A manually UNUSABLE index (Oracle compat) is treated
                    * as not ready for inserts, so DML stops maintaining
                    * it, same as an in-progress CREATE INDEX CONCURRENTLY.
                    */
                   indexStruct->indisready && !indexStruct->indisunusable,
                   false,
                   index->rd_indam->amsummarizing,
                   indexStruct->indisexclusion && indexStruct->indisunique);

index_bad = (!indexForm->indisvalid ||
             !indexForm->indisready ||
             !indexForm->indislive ||
             indexForm->indisunusable);
if (index_bad ||
    (indexForm->indcheckxmin && !indexInfo->ii_BrokenHotChain))
{
    ...
    indexForm->indisvalid = true;
    indexForm->indisready = true;
    indexForm->indislive = true;
    /* a completed non-concurrent rebuild always clears UNUSABLE */
    indexForm->indisunusable = false;
    CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
}

ALTER INDEX idx_sales_id UNUSABLE;  -- idx_sales_id is a partitioned parent index
-- ERROR: ALTER INDEX ... UNUSABLE is not supported for partitioned indexes
-- HINT: Mark each partition's leaf index unusable individually.

SET compatible_db = PG_PARSER;
ALTER INDEX idx_emp_email UNUSABLE;
-- ERROR: syntax error at or near "UNUSABLE"

ALTER INDEX idx_emp_email UNUSABLE;
INSERT INTO emp VALUES (999, 'a@example.com');  -- duplicates an existing row, succeeds
ALTER INDEX idx_emp_email REBUILD;
-- ERROR: could not create unique index "idx_emp_email"
-- DETAIL: Key (email)=(a@example.com) is duplicated.