sergiogarciasilva opened a new issue, #2576: URL: https://github.com/apache/age/issues/2576
`sql/agtype_string.sql` declares three `agtype` string-matching functions: - `agtype_string_match_starts_with` — `STABLE` - `agtype_string_match_ends_with` — `STABLE` - `agtype_string_match_contains` — `IMMUTABLE` All three are pure functions over `agtype` values — same inputs, same output, every time, no catalog lookups, no session state. There's no functional reason for `contains` to be `IMMUTABLE` while `starts_with`/`ends_with` are `STABLE`; this looks like an oversight rather than a deliberate distinction. **Concrete cost of the current state:** PostgreSQL requires a function to be `IMMUTABLE` to be used as an expression-index key (`STABLE`/`VOLATILE` functions are rejected at `CREATE INDEX` time with "functions in index expression must be marked IMMUTABLE"). So today there is no way — not even by hand-building an index — to accelerate a `STARTS WITH` or `ENDS WITH` predicate via an expression index. `CONTAINS` doesn't have this problem only because it happens to already be marked correctly. **What this fix does and doesn't do**, to be upfront about the limit of the ask: marking these two functions `IMMUTABLE` does **not**, by itself, make `WHERE n.prop STARTS WITH 'x'` or `ENDS WITH` start using an index automatically. Read in source (`src/backend/parser/cypher_expr.c:1604-1628`, AGE 1.7.0), Cypher's `STARTS WITH`/`ENDS WITH` compile to a `FuncExpr` call of these functions, not to an operator — and AGE's automatic property-index pushdown (`match_clause_to_indexcol()` and friends) matches on operators via opclass, not on arbitrary function calls. No operator, no automatic pushdown, regardless of volatility. What the `IMMUTABLE` marking *does* unlock: a user can build their own expression index directly on the function call — `CREATE INDEX ON <graph>."<Label>" (agtype_string_match_starts_with(properties, ...))` — and use it via a manual rewrite of the predicate, or as groundwork for a future operator + opclass that would let AGE's own pushdown logic reach it. Today that manual path is not just unoptimized, it's flatly unavailable because Postgres refuses the `CREATE INDEX` outright on a `STABLE` function. So: two honest, separate claims — (1) this is a one-line correctness fix (volatility category should match reality), and (2) it is a prerequisite for indexable prefix/suffix matching, not a fix for it by itself. #2417 shows the maintainers have recently merged planner-metadata-only changes to `agtype` operators for exactly this kind of correctness reason; happy to open this as a PR if a maintainer confirms there isn't a reason these were left `STABLE` that we're missing. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
