Re: [HACKERS] Streaming replication document improvements

On Tue, Apr 20, 2010 at 5:47 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Tue, Apr 20, 2010 at 11:04 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Instead of doing this, could we just change the logic in InitPostgres?
>>
>> Current logic says we hit the connection limit if:
>>
>>        if (!am_superuser &&
>>                ReservedBackends > 0 &&
>>                !HaveNFreeProcs(ReservedBackends))
>>
>> Couldn't we just change this to:
>>
>>        if ((!am_superuser || am_walsender) &&
>>                ReservedBackends > 0 &&
>>                !HaveNFreeProcs(ReservedBackends))
>>
>> Seems like that'd be a whole lot simpler, if it'll do the job...
>
> It's very simple, but prevents superuser replication connection
> from being established when connection limit exceeds for
> non-superusers. It seems strange to me that superuser cannot use
> superuser_reserved_connections slots. If we'd like to forbid
> replication connection to use the slots, I think that we should
> just get rid of a superuser privilege from it instead.

Let's just stop for a second and think about why we have
superuser_reserved_connections in the first place. As I understand
it, the point is that we want to make sure that superusers don't get
locked out of the database, because superuser intervention might be
necessary to recover from whatever series of unfortunate events has
caused all of the connection slots to get used up. For example, if
there are several different applications that connect to the database,
the superuser might like to log in and see which application has
requested more than its usual allotment of connections, or the
superuser might like to log in and terminate those backends which, in
his judgement, ought to be terminated. In other words, the purpose of
superuser_reserved_connections is to allow the database to recover
from a bad state that it has gotten into: specifically, a state where
all the connection slots have been used up and regular users can't
connect.

If replication connections can use up superuser_reserved_connections
slots, then it's very possible that this safety valve will fail
completely. If the server is being flooded with connection attempts,
and one of the streaming replication connection dies, then a regular
backend will immediate grab that slot. When the streaming replication
slave automatically tries to reconnect, it will now grab one of the
superuser_reserved_connections slots, putting us one step closer to
the bad scenario where there's no way for the superuser to log in
interactively and troubleshoot.

In other words, I don't care whether or not the replication connection
is or is not technically a superuser connection. What I think is
important is trying to preserve the ability for a superuser to log in
interactively and clean up the mess even when the regular supply of
connections is maxed out.

...Robert