From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: 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: <231375.1727925543@sss.pgh.pa.us>
References: <18637-f51e314546e3ba2a@postgresql.org>
 <CAApHDvrmBSTA8-Sv=HEQvrPO8QDPLB7y0d0gJweBQe2c8PMGYw@mail.gmail.com>
 <231375.1727925543@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 02 Oct 2024 23:19:03 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <364850.1727974294.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 03 Oct 2024 12:51:34 -0400
Message-ID: <364851.1727974294@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/364851.1727974294%40sss.pgh.pa.us>
Precedence: bulk

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=3D# create extension cube;
CREATE EXTENSION
regression=3D# create table items(category_id int, val cube) partition by =
list(category_id);
CREATE TABLE
regression=3D# CREATE INDEX ON items (val cube_ops);
CREATE INDEX
regression=3D# CREATE TABLE id_123 PARTITION OF items FOR VALUES IN (1, 2)=
;
CREATE TABLE
regression=3D# 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