CREATE INDEX regression in 17 RC1 or expected behavior?

From: Tom Dunstan <pgsql(at)tomd(dot)cc>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: CREATE INDEX regression in 17 RC1 or expected behavior?
Date: 2024-09-26 02:52:32
Message-ID: CAPPfruwX8w7Aa80-9csd+W+XMt=iknD0vHGsGbSvs5hXqkLepg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Dunstan 2024-09-26 03:12:55 Re: CREATE INDEX regression in 17 RC1 or expected behavior?
Previous Message Michael Paquier 2024-09-26 01:08:21 Re: query_id, pg_stat_activity, extended query protocol