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+3TX2aPXuhH1G0VQAmi7sAAEIC53AACfROAAEgoc8wAJp954AAxmRWsA==
Date: Thu, 29 Mar 2018 19:57:13 +0000
Message-ID: <917d713d16f548a3870adde2a7982a39@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>
 <da2ee034500f4639b7c03b58f18f147a@THALASSA.omni.imsweb.com>
 <11207.1521998011@sss.pgh.pa.us>
In-Reply-To: <11207.1521998011@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

>> 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 se=
eing --- the memory consumption seems stable, and about the same with or wi=
thout 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 'SURN=
AME'
>
>so I did it like this:
>
>INSERT INTO test_search_path(date_last_modified, last_name) select clock_t=
imestamp(), (random()*1000)::int::text from generate_series(1,1000000);
>
>The example doesn't seem like it'd be terribly data-dependent, but maybe t=
hat's wrong?
>
>Also, I assume you're running a reasonably up-to-date PG release, otherwis=
e 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 wou=
ld 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 w=
ith the random numeric inserts you used.

On a server with a lot of memory(100GB) and using the postgres default memo=
ry settings, the update was able to complete but when watching top there wa=
s a clear spike in %MEM for the update when the function search_path was se=
t.
The issue becomes more noticeable as you add more rows, for example at 5,00=
0,000 %MEM went over 35. Without the search_path set %MEM stays real low an=
d 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 varch=
ar);

INSERT INTO test_search_path(date_last_modified, last_name) select clock_ti=
mestamp(), (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 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');

There seems to be something about the inner function (clean_name_upper) bei=
ng used in an index of its own on the same field as the GIN functional inde=
x (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 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.