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));
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
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]https://www.postgresql.org/docs/release/17.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]/messages/by-id/20240805160502.d2a4975802a832b1e04afb80@sraoss.co.jp,
and create a commitfest entry [2]https://commitfest.postgresql.org/49/5182/.
[1]: /messages/by-id/20240805160502.d2a4975802a832b1e04afb80@sraoss.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]: /messages/by-id/20240926141921.57d0b430fa53ac4389344847@sraoss.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] /messages/by-id/20240926141921.57d0b430fa53ac4389344847@sraoss.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.htmlThose 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>