From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: usamoi@outlook.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified
Date: Thu, 03 Oct 2024 12:51:34 -0400
Message-ID: <364851.1727974294@sss.pgh.pa.us> (raw)
In-Reply-To: <231375.1727925543@sss.pgh.pa.us>
References: <18637-f51e314546e3ba2a@postgresql.org>
<CAApHDvrmBSTA8-Sv=HEQvrPO8QDPLB7y0d0gJweBQe2c8PMGYw@mail.gmail.com>
<231375.1727925543@sss.pgh.pa.us>
I wrote:
> I have not dug into the code, but I think this may actually be a
> bug; not because of the change of search path, but because it looks
> like the opclass is probably being looked up more than once during
> the command, with different search paths. That has security
> implications, and not good ones. We should fix this so that the
> opclass is looked up exactly once, and passed down to the partitions
> by OID not name.
Here's an example that doesn't rely on any outside extension:
regression=# create extension cube;
CREATE EXTENSION
regression=# create table items(category_id int, val cube) partition by list(category_id);
CREATE TABLE
regression=# CREATE INDEX ON items (val cube_ops);
CREATE INDEX
regression=# CREATE TABLE id_123 PARTITION OF items FOR VALUES IN (1, 2);
CREATE TABLE
regression=# CREATE INDEX items_idx_2 ON items (val cube_ops);
ERROR: operator class "cube_ops" does not exist for access method "btree"
So that's pretty awful: creating the partitioned index by itself is
willing to look up the opclass in the current search path, and then
adding a new partition will play nice with that, but creating a
partitioned index when there's already a partition will not.
It's got to be considered a bug that the two paths for making a
child index behave differently.
As far as I understand, the change of search path is only supposed to
affect user-defined code within the expressions of an expression index.
Operands of the command itself should consistently be interpreted in
the current search path.
regards, tom lane
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: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, usamoi@outlook.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified
In-Reply-To: <364851.1727974294@sss.pgh.pa.us>
* 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