Thread: CREATE INDEX regression in 17 RC1 or expected behavior?
Hi all
While testing out 17 RC1 I found that a construct that previously worked has now stopped working:
CREATE OR REPLACE FUNCTION index_truncate(src TEXT) RETURNS TEXT AS $$
SELECT LOWER(LEFT(src, 100));
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION join_for_index(TEXT [])
RETURNS TEXT LANGUAGE SQL IMMUTABLE AS
$$
SELECT index_truncate(array_to_string($1, ' '))
$$;
DROP TABLE IF EXISTS test;
CREATE TABLE test (
strings TEXT[]
);
CREATE INDEX test_strings_idx ON test (join_for_index(strings));
SELECT LOWER(LEFT(src, 100));
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION join_for_index(TEXT [])
RETURNS TEXT LANGUAGE SQL IMMUTABLE AS
$$
SELECT index_truncate(array_to_string($1, ' '))
$$;
DROP TABLE IF EXISTS test;
CREATE TABLE test (
strings TEXT[]
);
CREATE INDEX test_strings_idx ON test (join_for_index(strings));
This worked fine 9.5-16 but the CREATE INDEX statement now fails with:
CREATE INDEX test_strings_idx ON test (join_for_index(strings));
psql:test.sql:21: ERROR: function index_truncate(text) does not exist
LINE 2: SELECT index_truncate(array_to_string($1, ' '))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT index_truncate(array_to_string($1, ' '))
CONTEXT: SQL function "join_for_index" during inlining
psql:test.sql:21: ERROR: function index_truncate(text) does not exist
LINE 2: SELECT index_truncate(array_to_string($1, ' '))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT index_truncate(array_to_string($1, ' '))
CONTEXT: SQL function "join_for_index" during inlining
I presume that this is related to the work in 17 around using restricted search paths in more places, but it's just a guess. CREATE INDEX isn't mentioned in the release notes.
FWIW this is from an older db migration of ours - a later one redefined join_for_index to use an explicit path to find index_truncate, and that works fine. But this breakage will then require us to go patch this older migration in many installations.
Reporting in case this is unexpected. At the very least if a function used in an index must now always find other functions using an explicit path, it seems like this should be documented and noted in the release notes.
Cheers
Tom
On Thu, 26 Sept 2024 at 12:22, Tom Dunstan <pgsql@tomd.cc> wrote:
I presume that this is related to the work in 17 around using restricted search paths in more places, but it's just a guess. CREATE INDEX isn't mentioned in the release notes.
Reading a bit closer yields:
> Functions used by expression indexes and materialized views that need to reference non-default schemas must specify a search path during function creation.
So I guess that makes this an intended breakage.
It might help to add CREATE INDEX (and maybe CREATE MATERIALIZED VIEW if that's also affected) to the list of commands affected in the release notes to make this more obvious - having a list of commands that are affected that didn't include it made me think that this wasn't intended.
Cheers
Tom
On Thu, Sep 26, 2024 at 12:22:32PM +0930, Tom Dunstan wrote:
> Reporting in case this is unexpected. At the very least if a function used
> in an index must now always find other functions using an explicit path, it
> seems like this should be documented and noted in the release notes.
The first compatibility entry in the release notes [0] has the following
sentence:
Functions used by expression indexes and materialized views that need
to reference non-default schemas must specify a search path during
function creation.
Do you think this needs to be expanded upon?
[0] https://www.postgresql.org/docs/release/17.0/
--
nathan
On Wed, 25 Sep 2024 22:16:06 -0500 Nathan Bossart <nathandbossart@gmail.com> wrote: > On Thu, Sep 26, 2024 at 12:22:32PM +0930, Tom Dunstan wrote: > > Reporting in case this is unexpected. At the very least if a function used > > in an index must now always find other functions using an explicit path, it > > seems like this should be documented and noted in the release notes. > > The first compatibility entry in the release notes [0] has the following > sentence: > > Functions used by expression indexes and materialized views that need > to reference non-default schemas must specify a search path during > function creation. Also, this is documented as followins in https://www.postgresql.org/docs/17/sql-createindex.html . While CREATE INDEX is running, the search_path is temporarily changed to pg_catalog, pg_temp. By the way, this is not mentioned in CREATE MATERIALIZED VIEW documentation, although we can find in REFRESH MATERIALIZED VIEW doc. So, I sent the doc patch in [1], and create a commitfest entry [2]. [1] https://www.postgresql.org/message-id/20240805160502.d2a4975802a832b1e04afb80%40sraoss.co.jp [2] https://commitfest.postgresql.org/49/5182/ Regards, Yugo Nagata -- Yugo Nagata <nagata@sraoss.co.jp>
On Thu, 26 Sept 2024 at 13:21, Yugo Nagata <nagata@sraoss.co.jp> wrote:
By the way, this is not mentioned in CREATE MATERIALIZED VIEW documentation, although
we can find in REFRESH MATERIALIZED VIEW doc. So, I sent the doc patch in [1],
and create a commitfest entry [2].
Thanks.
I suggest adding CREATE INDEX and CREATE MATERIALIZED VIEW to the release notes list of commands, as I looked for CREATE INDEX there and only raised this due to its absence.
Cheers
Tom
On Thu, 26 Sep 2024 13:27:54 +0930 Tom Dunstan <tom@tomd.cc> wrote: > On Thu, 26 Sept 2024 at 13:21, Yugo Nagata <nagata@sraoss.co.jp> wrote: > > > By the way, this is not mentioned in CREATE MATERIALIZED VIEW > > documentation, although > > we can find in REFRESH MATERIALIZED VIEW doc. So, I sent the doc patch in > > [1], > > and create a commitfest entry [2]. > > > > Thanks. > > I suggest adding CREATE INDEX and CREATE MATERIALIZED VIEW to the release > notes list of commands, as I looked for CREATE INDEX there and only raised > this due to its absence. I've proposed to improve the release notes to include CREATE INDEX and CREATE MATERIALIZED VIEW into the command list. [1] https://www.postgresql.org/message-id/20240926141921.57d0b430fa53ac4389344847%40sraoss.co.jp Regards, Yugo Nagata > > Cheers > > Tom -- Yugo NAGATA <nagata@sraoss.co.jp>
On Thu, 26 Sep 2024 14:21:27 +0900 Yugo NAGATA <nagata@sraoss.co.jp> wrote: > On Thu, 26 Sep 2024 13:27:54 +0930 > Tom Dunstan <tom@tomd.cc> wrote: > > > On Thu, 26 Sept 2024 at 13:21, Yugo Nagata <nagata@sraoss.co.jp> wrote: > > > > > By the way, this is not mentioned in CREATE MATERIALIZED VIEW > > > documentation, although > > > we can find in REFRESH MATERIALIZED VIEW doc. So, I sent the doc patch in > > > [1], > > > and create a commitfest entry [2]. > > > > > > > Thanks. > > > > I suggest adding CREATE INDEX and CREATE MATERIALIZED VIEW to the release > > notes list of commands, as I looked for CREATE INDEX there and only raised > > this due to its absence. > > I've proposed to improve the release notes to include CREATE INDEX and > CREATE MATERIALIZED VIEW into the command list. > > [1] https://www.postgresql.org/message-id/20240926141921.57d0b430fa53ac4389344847%40sraoss.co.jp Committed in 7e059fb6c0. https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7e059fb6c04e76b712a5a92de8c62e56f42e1bbf;hp=a3c4a91f1e283cc4b79f0b0482d2c490a599d880 It is not applied to the web (yet?), though. https://www.postgresql.org/docs/17/release-17.html Regards, Yugo Nagata -- Yugo Nagata <nagata@sraoss.co.jp>
On Fri, 11 Oct 2024 at 20:53, Yugo Nagata <nagata@sraoss.co.jp> wrote: > It is not applied to the web (yet?), though. > https://www.postgresql.org/docs/17/release-17.html Those will only be updated when 17.1 is released. David
On Fri, 11 Oct 2024 21:00:47 +1300 David Rowley <dgrowleyml@gmail.com> wrote: > On Fri, 11 Oct 2024 at 20:53, Yugo Nagata <nagata@sraoss.co.jp> wrote: > > It is not applied to the web (yet?), though. > > https://www.postgresql.org/docs/17/release-17.html > > Those will only be updated when 17.1 is released. Thank you for letting me know it. I understand. Regards, Yugo Nagata -- Yugo NAGATA <nagata@sraoss.co.jp>