RE: Function search_path

["Heinemann, Manfred (IMS)" <HeinemannM@xxxxxxxxxx>]

>> I have played around with the postgres memory settings and setting search_path on a function causes a lot more memory to be used than if no search_path was set.
>
>That's a pretty broad claim with a pretty small amount of evidence offered.
>
>I can certainly believe that attaching a SET clause (whether for search_path or any other GUC variable) would have an efficiency impact; one non-obvious example is that it prevents inlining if the function is a SQL function.  But I don't immediately see a reason for major memory consumption from that.  I suspect what you're seeing is specific to a particular use-case.  If you were to provide a concrete example, we could look into what's happening.
>
>regards, tom lane

Here is an example where I can show significant extra memory consumption when setting search_path on a function:

CREATE TABLE test_search_path(date_last_modified timestamp, last_name varchar);

--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
INSERT INTO test_search_path(date_last_modified, last_name) VALUES(clock_timestamp(), 'SURNAME');

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');

This only seems to be an issue when indices exist for both functions and when the inner called function does not have search_path set.

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.