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

On Wed, Jan 11, 2017 at 2:44 AM, 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.
>
> Simple test case:
>
> postgres=# CREATE USER user1 LOGIN CONNECTION LIMIT 2;
> CREATE ROLE
> postgres=# \c postgres user1
> You are now connected TO DATABASE "postgres" AS USER "user1".
> postgres=> CREATE TABLE t1 AS (SELECT i FROM GENERATE_SERIES(1,6000000) s(i));
> SELECT 6000000
> postgres=> SET max_parallel_workers_per_gather = 2;
> SET
> postgres=> SELECT COUNT(*) FROM t1;
> ERROR: too many connections FOR ROLE "user1"
> CONTEXT: parallel worker
>
> 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.
>

This has been reported previously [1] and I have explained the reason
why such a behaviour is possible and why this can't be handled in
Gather node.

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

I think this is not only for parallel workers, rather any background
worker that uses database connection
(BGWORKER_BACKEND_DATABASE_CONNECTION) will be counted in a similar
way. I am not sure if it is worth inventing something to consider
such background worker connections different from backend connections.
However, I think we should document it either in parallel query or in
background worker or in Create User .. Connection section.

[1] - https://www.postgresql.org/message-id/20161222111345.25620.8603%40wrigleys.postgresql.org

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