RE: Function search_path

>> Here is an example where I can show significant extra memory consumption when setting search_path on a function:
>
>I got around to testing this example today, and I don't see what you're seeing --- the memory consumption seems stable, and about the same with or without the "SET search_path" clause.
>
>You didn't specify exactly how to do this bit:
>
>--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
>
>so I did it like this:
>
>INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);
>
>The example doesn't seem like it'd be terribly data-dependent, but maybe that's wrong?
>
>Also, I assume you're running a reasonably up-to-date PG release, otherwise I'd be wondering about leaks in the GIN index AM; we've fixed some issues of that sort in GIN bulk updates in the past.
>But again, it's not clear what the connection to a function SET clause would be. So I lack a plausible theory at the moment.

Tom,
Thanks for your looking at this.

I tested this well on 9.6.7 but I saw the same issue on 10.3.
You are correct the issue is not data dependant and I saw the same issues with the random numeric inserts you used.

On a server with a lot of memory(100GB) and using the postgres default memory settings, the update was able to complete but when watching top there was a clear spike in %MEM for the update when the function search_path was set.
The issue becomes more noticeable as you add more rows, for example at 5,000,000 %MEM went over 35. Without the search_path set %MEM stays real low and the update was a lot quicker.

Here is the more complete test case:

CREATE SCHEMA test_search_path AUTHORIZATION test_search_path;

--then logged in as the test_search_path user

CREATE TABLE test_search_path(date_last_modified timestamp, last_name varchar);

INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);

CREATE OR REPLACE FUNCTION clean_name_upper(original_name varchar)
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(upper(original_name));
END;
$$ IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION split_name_on_hyphen(original_name character varying)
RETURNS text[]
LANGUAGE plpgsql
AS $$
BEGIN
RETURN string_to_array(clean_name_upper(original_name), '-');
END;
$$ IMMUTABLE STRICT SET search_path = '$user';

CREATE INDEX idx_test_search_path_clean_name_upper_last_name ON test_search_path (clean_name_upper(last_name));
CREATE INDEX idx_test_search_path_split_name_on_hyphen_last_name ON test_search_path USING gin(split_name_on_hyphen(last_name));

UPDATE test_search_path SET date_last_modified = (date_last_modified - interval '7 days');

There seems to be something about the inner function (clean_name_upper) being used in an index of its own on the same field as the GIN functional index (split_name_on_hyphen) that is causing this.

Thanks,
Manfred

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.