Re: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified

On Sat, 28 Sept 2024 at 02:59, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> After debugging, I find that `DefineIndex` would call `RestrictSearchPath`
> in `indexcmds.c:1234` and `indexcmds.c:1334` before entering recursion, so
> that the nested `DefineIndex` would be only able to look up operator classes
> in `pg_catalog, pg_temp`.
>
> Since the behavior between `CREATE INDEX` and `CREATE INDEX PARTITION BY` is
> different, it should be a bug.

There's an item in the release notes [1] which should be expended to
mention this as an incompatibility, namely:

"Change functions to use a safe search_path during maintenance
operations (Jeff Davis) §

This prevents maintenance operations (ANALYZE, CLUSTER, REFRESH
MATERIALIZED VIEW, REINDEX, or VACUUM) from performing unsafe access.
Functions used by expression indexes and materialized views that need
to reference non-default schemas must specify a search path during
function creation."

That doesn't quite mention CREATE INDEX. There's a discussion about
fixing that by adding CREATE INDEX to the list of commands. See [2].

I think you can now safely assume that you'll need to provide the
schema name for the opclass you've specified.

Also please see the CREATE INDEX documentation [3] where it says:

"While CREATE INDEX is running, the search_path is temporarily changed
to pg_catalog, pg_temp."

David

[1] https://www.postgresql.org/docs/release/17.0/
[2] https://www.postgresql.org/message-id/20240926141921.57d0b430fa53ac4389344847%40sraoss.co.jp
[3] https://www.postgresql.org/docs/17/sql-createindex.html