Re: CONNECTION LIMIT and Parallel Query don't play well together

On Tue, Jan 10, 2017 at 4:14 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> It has come to my attention that when a user has a CONNECTION LIMIT
> set, and they make use of parallel query, that their queries can fail
> due to the connection limit being exceeded.

That's bad.

> Now, as I understand it, during the design of parallel query, it was
> designed in such a way that nodeGather could perform all of the work
> in the main process in the event that no workers were available, and
> that the only user visible evidence of this would be the query would
> be slower than it would otherwise be.

That was the intent.

> After a little bit of looking around I see that CountUserBackends()
> does not ignore the parallel workers, and counts these as
> "CONNECTIONS". It's probably debatable to weather these are
> connections or not, ...

Yeah. I think that I looked at the connection limit stuff in the 9.4
time frame and said, well, we shouldn't let people use background
workers as a way of evading the connection limit, so let's continue to
count them against that limit. Then, later on, I did the work to try
to make it transparent when sufficient parallel workers cannot be
obtained, but forgot about this case or somehow convinced myself that
it didn't matter.

One option is certainly to decide categorically that background
workers are not connections, and therefore CountUserBackends() should
ignore them and InitializeSessionUserId() shouldn't call it when the
session being started is a background worker. That means that
background workers don't count against the user connection limit, full
stop. Another option, probably slightly less easy to implement, is to
decide that background workers in general count against the limit but
parallel workers do not. The third option is to count both background
workers and parallel workers against the limit but somehow recover
gracefully when this error trips. But I have no idea how we could
implement that third option in a reasonable way.

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