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@xxxxxxxxxxxxxx ([HACKERS] One process per session lack of sharing) https://www.postgresql.org/message-id/flat/301417900.20160712173911%40bitec.ru#301417900.20160712173911@xxxxxxxx >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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
