From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "daidewei@highgo.com" <daidewei@highgo.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: a plpgsql bug
In-reply-to: 
 <CAKFQuwZQnBBDPYq=kj++eh0X_K5eE_+K9yLWBn5vO7K1ysYcNg@mail.gmail.com>
References: <202309191058550158453@highgo.com>
 <CAKFQuwZQnBBDPYq=kj++eh0X_K5eE_+K9yLWBn5vO7K1ysYcNg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 19 Sep 2023 08:24:25 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <376950.1695139931.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 19 Sep 2023 12:12:11 -0400
Message-ID: <376951.1695139931@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/376951.1695139931%40sss.pgh.pa.us>
Precedence: bulk

"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