Re: One process per session lack of sharing

On Tue, Jul 12, 2016 at 9:18 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> AMatveev(at)bitec(dot)ru writes:
>> Is there any plan to implement "session per thread" or "shared
>> sessions between thread"?
>
> No, not really. The amount of overhead that would add --- eg, the need
> for locking on what used to be single-use caches --- makes the benefit
> highly questionable. Also, most people who need this find that sticking
> a connection pooler in front of the database solves their problem, so
> there's not that much motivation to do a ton of work inside the database
> to solve it there.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company