Re: Memory usage per session

Hi

>I think the "problem" that he is having is fixable only by changing how PostgreSQL itself works.
>His problem is a PL/pgSQL function which is 11K lines in length.
>When invoked, this function is "compiled" into a large tokenized parse tree.
>This parse tree is only usable in the session which invoked the the function.
>Apparently this parse tree takes a lot of memory. And "n" concurrent users of this,
>highly used, function will therefore require "n" times as much memory because the parse tree is _not_ shareable.
>This is explained in:
>https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

There is an interesting continuation of the discussion at
pgsql-hackers(at)postgresql(dot)org ([HACKERS] One process per session lack
of sharing)

https://www.postgresql.org/message-id/flat/301417900(dot)20160712173911%40bitec(dot)ru#301417900(dot)20160712173911(at)bitec(dot)ru

>I agree that there's not really a plan to implement this, but I don't
>agree that connection pooling solves the whole problem. Most people
>can't get by with statement pooling, so in practice you are looking at
>transaction pooling or session pooling. And that means that you can't
>really keep the pool size as small as you'd like because backends can
>be idle in transaction for long enough to force the pool size to be
>pretty large. Also, pooling causes the same backends to get reused
>for different sessions which touch different relations and different
>functions so that, for example, the relcache and the PL/pgsql function
>caches grow until every one of those sessions has everything cached
>that any client needs. That can cause big problems.
>
>So, I actually think it would be a good idea to think about this. The
>problem, of course, is that as long as we allow arbitrary parts of the
>code - including extension code - to declare global variables and
>store arbitrary stuff in them without any coordination, it's
>impossible to imagine hibernating and resuming a session without a
>risk of things going severely awry. This was a major issue for
>parallel query, but we've solved it, mostly, by designating the things
>that rely on global variables as parallel-restricted, and there
>actually aren't a ton of those. So I think it's imaginable that we
>can get to a point where we can, at least in some circumstances, let a
>backend exit and reconstitute its state at a later time. It's not an
>easy project, but I think it is one we will eventually need to do.
>Insisting that the current model is working is just sticking our head
>in the sand. It's mostly working, but there are workloads where it
>fails badly - and competing database products survive a number of
>scenarios where we just fall on our face.