Perpetual concurrent-rebuild diff for partial indexes with IN-list predicates (IN vs = ANY(ARRAY) normalization)

[thomas-huerlimann]

Summary

Partial indexes whose WHERE predicate uses an IN-list on a varchar column produce a perpetual spurious diff: dump renders the predicate as IN (...), but PostgreSQL stores it in the catalog as = ANY (ARRAY[...]). plan then schedules a concurrent rebuild of an index that is textually different but semantically identical - on every plan, forever, because applying the rebuild stores the predicate as = ANY (ARRAY[...]) again.

Likely a sibling of the fixed #200 (partial unique index always seen as modified) and #265; the IN-list-on-varchar normalization case is still open.

Repro (pgschema 1.11.1, PostgreSQL 17)

CREATE TABLE findings (
    id uuid PRIMARY KEY,
    org_id uuid NOT NULL,
    status varchar(20) NOT NULL,
    impact_score int
);

CREATE INDEX idx_finding_actionable
    ON findings (org_id, status, impact_score DESC)
    WHERE status IN ('new', 'acknowledged');

1. pgschema dump renders: WHERE (status)::text IN (('new'::character varying)::text, ('acknowledged'::character varying)::text)
2. pgschema plan --file <dump> against the same database reports the index as ~ idx_finding_actionable (index - concurrent rebuild) although nothing changed.
3. Executing the generated rebuild does not converge: the catalog form remains = ANY (ARRAY[...]), so the next dump/plan cycle reports the same rebuild.

In our schema this affects 6 of 97 partial indexes - exactly the ones with IN-list predicates; all other predicate shapes (IS NOT NULL, equality, boolean) are stable. A freshly built database (apply from dump, then re-plan) is also stable, which confirms this is purely a predicate-normalization comparison issue against existing catalogs.

Expected

dump/plan normalize IN (...) and = ANY (ARRAY[...]) predicate forms to the same canonical representation before comparison (e.g. compare pg_get_expr(indpred, ...) of desired vs current instead of the rendered source text).

Observed on both 1.10.0 and 1.11.1 (Docker images), server PostgreSQL 17.