Re: Patch for reserved connections for replication users

On Thu, Oct 17, 2013 at 8:57 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Oct 16, 2013 at 4:30 AM, Gibheer <gibheer(at)zero-knowledge(dot)org> wrote:
>> On Mon, 14 Oct 2013 11:52:57 +0530
>> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>>> On Sun, Oct 13, 2013 at 2:08 PM, Gibheer <gibheer(at)zero-knowledge(dot)org>
>>> wrote:
>>> > On Sun, 13 Oct 2013 11:38:17 +0530
>>> > Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> >
>>> >> On Thu, Oct 10, 2013 at 3:17 AM, Gibheer
>>> >> <gibheer(at)zero-knowledge(dot)org> wrote:
>>> >> > On Mon, 7 Oct 2013 11:39:55 +0530
>>> >> > Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> >> >> Robert Haas wrote:
>>> >> >> On Mon, Aug 5, 2013 at 2:04 AM, Andres Freund
>>> >> >> <andres(at)2ndquadrant(dot)com> wrote:
>> I would be glad, if you could also test the patch again, as I'm nearly
>> code blind after testing it for 4 hours.
>> I had the problem, that I could not attach as many replication
>> connections as I wanted, as they were denied as normal connections. I
>> think I got it fixed, but I'm not 100% sure at the moment.
>> After some sleep, I will read the code again and test it again, to make
>> sure, it really does what it is supposed to do.
>
> You have forgotten to attach the patch. However, now it is important
> to first get the consensus on approach to do this feature, currently
> there are 3 approaches:
> 1. Have replication_reserved_connections as a separate parameter to
> reserve connections for replication
> 2. Consider max_wal_sender to reserve connections for replication
> 3. Treat replication connections as a pool outside max_connections
>
> Apart from above approaches, we need to think how user can view the
> usage of connections, as pg_stat_activity doesn't show replication
> connections, so its difficult for user to see how the connections are
> used.
>
> I am really not sure what is best way to goahead from here, but I
> think it might be helpful if we can study some use cases or how other
> databases solve this problem.

Today I spent some time seeing how other databases (in particular
MySQL) achieve it. There seems to be no separate way to configure
replication connections, rather if user faced with
too_many_connections
(https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html)
they allow one spare connection (super user connection) to check what
all connections are doing, it seems all connections can be viewed
through one common command Show ProcessList
(https://dev.mysql.com/doc/refman/5.5/en/show-processlist.html)

By above, I don't mean that we should only do what other databases
have done, rather it is towards trying to find a way which can be
useful for users of Postgresql.

Your views/thoughts?

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