From: daidewei@highgo.com <daidewei@highgo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Re: a plpgsql bug
Date: Wed, 20 Sep 2023 14:34:06 +0800
Message-ID: <202309201434055495623@highgo.com> (raw)
References: <202309191058550158453@highgo.com>
<CAKFQuwZQnBBDPYq=kj++eh0X_K5eE_+K9yLWBn5vO7K1ysYcNg@mail.gmail.com>
<376951.1695139931@sss.pgh.pa.us>
I find another example which doesn't change search_path,but use function 'SECURITY DEFINER'
1 create a user
./psql -dpostgres
create user test_user;
grant all on database postgres to test_user;
2 use the new user to logon and create function
./psql -dpostgres -U test_user
create schema test_user;
create or replace function test_user.test_m(id integer) returns integer as
$$
declare
var2 integer := 1;
begin
if id % 4 = 1 then
return var2 + 2;
elseif id % 4 = 2 then
return var2 + 3;
elseif id % 4 = 3 then
return var2 + 4;
else
return var2;
end if;
end; $$ language plpgsql;
create or replace function test_user.test_f(id integer) returns integer SECURITY DEFINER as
$$
declare
var1 integer := 1;
begin
var1 := test_user.test_m(23);
return var1;
end; $$ language plpgsql;
3 execute
./psql -dpostgres
do $$
declare
var1 integer;
begin
for id in 1 .. 10000000 LOOP
var1 = test_user.test_m(id);
var1 = test_user.test_f(id);
end loop;
end; $$ language plpgsql;
daidewei@highgo.com
From: Tom Lane
Date: 2023-09-20 00:12
To: David G. Johnston
CC: daidewei@highgo.com; pgsql-bugs@lists.postgresql.org
Subject: Re: a plpgsql bug
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Sep 18, 2023 at 11:46 PM daidewei@highgo.com <daidewei@highgo.com>
> wrote:
>> I found a problem in plpgsql. When there is a large loop in plpgsql,
>> it is found that the change of search_path will cause memory exhaustion and
>> thus disconnect the connection.
> It is impossible to prevent queries from exhausting memory so the fact that
> this one does isn't an indication of a bug on its own. I'm having trouble
> imagining a reasonable use case for this pattern of changing search_path
> frequently in a loop within a single execution of a function. That said,
> I'm not in a position to judge how easy or difficult an improvement in this
> area may be.
I poked into this a bit with valgrind. It seems that the problem
is that the changes to search_path thrash the "simple expression"
mechanism in plpgsql, such that it has to re-plan the various
expressions in the called function each time through. It's good about
tracking the actual cached plans and not leaking those, but what is
getting leaked into transaction-lifespan memory is the data structures
made by
expr->expr_simple_state =
ExecInitExprWithParams(expr->expr_simple_expr,
econtext->ecxt_param_list_info);
We could conceivably reclaim that data if we were willing to set up
yet another per-expression memory context to hold it. That seems
like rather a high overhead though.
The given test case is obviously a bit artificial, but I think it
may be a simplification of fairly plausible use-cases. The triggering
condition is that the same textual expression in a plpgsql function
gets executed repeatedly with different search_path settings, which
doesn't seem that unreasonable.
Perhaps another approach could be to assume that only a small number
of distinct search_path settings will be used in any one transaction,
and cache a separate plan and estate for each one. That would have
the nice side-effect of avoiding the replanning overhead, but then
we'd have to figure out how to manage the cache and keep it from
blowing out.
regards, tom lane
Attachments:Catch4E91.jpg (image/jpeg, 139.1K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: daidewei@highgo.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Re: a plpgsql bug
In-Reply-To: <202309201434055495623@highgo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox