From: "Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: RE: Function search_path
Thread-Topic: Function search_path
Thread-Index: AdO7x5WpnFdOXZ+3TX2aPXuhH1G0VQAmi7sAAEIC53AACfROAAEgoc8w
Date: Thu, 22 Mar 2018 19:30:56 +0000
Message-ID: <da2ee034500f4639b7c03b58f18f147a@THALASSA.omni.imsweb.com>
References: <db194d98f61d4aa398c1399a24326e7c@THALASSA.omni.imsweb.com>
 <5f698c34-d01d-3196-1db4-75ba8f6a37d3@portavita.eu>
 <b0078bdae3e04de1ba3ddaa3527c8922@THALASSA.omni.imsweb.com>
 <2328.1521236730@sss.pgh.pa.us>
In-Reply-To: <2328.1521236730@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

>> I have played around with the postgres memory settings and setting searc=
h_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_pa=
th or any other GUC variable) would have an efficiency impact; one non-obvi=
ous 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 th=
at.  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 happenin=
g.
>
>regards, tom lane

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

CREATE TABLE test_search_path(date_last_modified timestamp, last_name varch=
ar);

--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNA=
ME'
INSERT INTO test_search_path(date_last_modified, last_name) VALUES(clock_ti=
mestamp(), '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 var=
ying)
  RETURNS text[]
  LANGUAGE plpgsql
AS $$
BEGIN
    RETURN string_to_array(clean_name_upper(original_name), '-');
END;
$$ IMMUTABLE STRICT SET search_path =3D '$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_se=
arch_path USING gin(split_name_on_hyphen(last_name));

UPDATE test_search_path SET date_last_modified =3D (date_last_modified - in=
terval '7 days');

This only seems to be an issue when indices exist for both functions and wh=
en 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 empl=
oyee or agent of the addressee(s), please note that any dissemination, dist=
ribution, or copying of this communication is strictly prohibited. If you h=
ave received this e-mail in error, please notify the sender of the error.