Re: BUG #14473: Parallel query aborts with too many connections

On Thu, Dec 22, 2016 at 4:43 PM, <steven(dot)winfield(at)cantabcapital(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14473
> Logged by: Steven Winfield
> Email address: steven(dot)winfield(at)cantabcapital(dot)com
> PostgreSQL version: 9.6.1
> Operating system: RHEL 7.3
> Description:
>
> Let's say a user is using all but two or three of their allowed connections
> (set with ALTER USER name CONNECTION LIMIT n).
>
> Now if they attempt to execute a query that would be executed in parallel by
> many (say 8) workers, such as a count(*) of a large table, then the users
> connection limit can be reached and the query is aborted:
>
> mydatabase=> select count(*) from large_table;
> ERROR: too many connections for role "myname"
> CONTEXT: parallel worker
>

This happens because parallel background workers uses database
connection (BGWORKER_BACKEND_DATABASE_CONNECTION).

> ...even though the query could have been successfully executed with fewer
> workers (as I checked by locally setting max_parallel_workers_per_gather to
> 0).
>
> This is surprising, because in other circumstances the query _can_ proceed
> with fewer workers than planned - e.g. when the max_worker_processes limit
> has been hit.
>

Query can proceed with lesser workers only when we launch lesser
workers to process it. As this happens after launching the workers,
it is treated as any other normal error and will abort the query
execution. We might avoid this error by checking users 'connection
limit' before launching the workers, but not sure if it is worth.

> From the docs:
> (https://www.postgresql.org/docs/9.6/static/how-parallel-query-works.html)
>
> "Every background worker process which is successfully started for a given
> parallel query will execute the portion of the plan which is a descendent of
> the Gather node"
>
> ...implying (at least to me) that workers may not start successfully, but
> this is handled gracefully.
>

No, if there is any error while starting workers, the query will abort.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com