[pgpool-general: 2663] Re: Blocking of excessive connections

[Tatsuo Ishii]

> Is it possible to change the behaviour of handling of client connections
> when more than num_init_children try to connect?

No.

> We have a number of
> applications using a drop wizard connection pool to connect to the pgpool-II
> cluster. In an ideal situation these applications would be sized to the
> maximum amount of available connection, but as said, this would be an ideal
> situation. In real life, some of these application try to allocate more
> connections then available, but instead of a connection error the
> application pool just waits for the connection to become active. As such,
> one misconfigured application can hold all the connections of the pool,
> blocking all other applications.
>
> It would be great if we could mimic the standard postgresql rejection

This behavior can be emulated by setting:

fail_over_on_backend_error = off
backend_flag0 = 'DISALLOW_TO_FAILOVER'
health_check_period = 0

and set num_init_children > max_connections.

Unfortunately you cannot use the failover feature of pgpool-II though.

> or
> even better, put some timeout value for the queued connections.

This is not possible because the queue is inside the kernel. There's
no API to control the timeout in the queue.

> For
> instance, queue a new connection if no available pool processes for x time,
> reject after x time if no processes are freed to handle the queued
> connection. As such, the application pool would know that the extra
> connection didn't succeed and would pool the request to one of the already
> open connections.
> 
>  
> 
> An even better way of coping with this would be to be able to limit the
> amount of connections per user/database/ip.

This is doable (with moderate amount of codes).

> I still need to test what
> happens if you put a user limit in postgresql, for example, if you put a
> connection limit of 4 for user test on postgresql level, what happens if you
> try to open 5 connections to the pgpool with some processes left to handle
> connections. Does it reject the connection or does it queue it?
> 
>  
> 
> Regards,
> 
> Sam
> 
>  
>