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

On 12 January 2017 at 09:36, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

I think the root question here which we need to ask ourselves is, what
is "CONNECTION LIMIT" for. I seem to have come around to assuming it's
meant to be to protect the server to give everyone a fairer chance of
getting a connection to the database. Now, since background workers
don't consume anything from max_connections, then I don't really feel
that a background worker should count towards "CONNECTION LIMIT". I'd
assume any CONNECTION LIMITs that are set for a user would be
calculated based on what max_connections is set to. If we want to
limit background workers in the same manner, then perhaps we'd want to
invent something like "WORKER LIMIT N" in CREATE USER.

> 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.

I agree with your view on the third option. I looked at this too and
it seems pretty horrible to try and do anything in that direction. It
seems that even if we suppressed the ERROR message, and had the worker
exit, that we'd still briefly consume a background worker slot which
would reduce the chances of some entitle user connection obtaining
them, in fact, this is the case as it stands today, even if that
moment is brief.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services