From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: usamoi@outlook.com
Subject: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified
Date: Fri, 27 Sep 2024 14:31:54 +0000
Message-ID: <18637-f51e314546e3ba2a@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18637
Logged by: CREATE INDEX won't look up operator classes in search_path if PARTITION BY
is specified
Email address: usamoi@outlook.com
PostgreSQL version: 17.0
Operating system: Linux 6.10
Description:
This SQL works in PostgreSQL 16.4 but not in PostgreSQL 17.0. Here
`vector_l2_ops` is an operator class in schema `vectors`.
```
SET search_path TO public, vectors;
CREATE TABLE items (val vector(3), category_id int) PARTITION BY
LIST(category_id);
CREATE TABLE id_123 PARTITION OF items FOR VALUES IN (1, 2, 3);
CREATE TABLE id_456 PARTITION OF items FOR VALUES IN (4, 5, 6);
CREATE TABLE id_789 PARTITION OF items FOR VALUES IN (7, 8, 9);
CREATE INDEX ON items USING vectors (val vector_l2_ops);
```
The error message is: `ERROR: operator class "vector_l2_ops" does not exist
for access method "vectors"`
If I do not use PARTITION BY and CREATE TABLE OF, it works and the message
is `CREATE INDEX`.
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, usamoi@outlook.com
Subject: Re: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified
In-Reply-To: <18637-f51e314546e3ba2a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox