Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8A662632B9E;
	Tue, 20 Apr 2010 07:52:24 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024)
	with ESMTP id 78773-03; Tue, 20 Apr 2010 10:52:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f202.google.com (mail-iw0-f202.google.com
	[209.85.223.202])
	by mail.postgresql.org (Postfix) with ESMTP id AF963632AC6;
	Tue, 20 Apr 2010 07:52:12 -0300 (ADT)
Received: by iwn40 with SMTP id 40so4149428iwn.1
	for <multiple recipients>; Tue, 20 Apr 2010 03:52:11 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:received:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=eNLRru3hXztF2aQhEYAEXZXX1/zoju6REoP+GJwxAx4=;
	b=OGIgjdDd+NhNoVeMV2aKtij3uz1YQgdzSnCzVlLPpKe686E63lG3U8pdX31yj9VRhv
	rm2+7/Bm/pBfozUB5jHxmk4qkVp2YJ5I46V/VtTDOgMs5kIpkLyGDh1KTOkyWHv2jc1E
	B3nZqWpWl83A56aef+w5h1NYmXH8qz6ZJ+Ua0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jAJBqeCyNpfVOOesQMiV5KMHTx/tFDiFm0pOhUGOwByU0bYDBwVA3DAcmntuKO8YGe
	zYsW1xcLfhZbuYN7v6az+nbnBJd0ZGcikvpfleD2/KL0I70vhsau7HDZ9p3AnYK7i2Vk
	bsBPGmyilW/3Vb0gX9KQYnm5ymo3Zrmzi/i/U=
MIME-Version: 1.0
Received: by 10.231.38.194 with HTTP; Tue, 20 Apr 2010 03:52:11 -0700 (PDT)
In-Reply-To: <s2u3f0b79eb1004200247q2f1c35f0pc7bf4af306f3514c@mail.gmail.com>
References: <3f0b79eb1003300152g5327eb47w8f9aecae6002b215@mail.gmail.com>
	<4BB49B0C.1050901@enterprisedb.com>
	<j2t603c8f071004011018tbdcc5d17y1fe17b29ab08703c@mail.gmail.com>
	<19262.1270142946@sss.pgh.pa.us>
	<4BB4952D0200002500030333@gw.wicourts.gov>
	<4BB4DBEF.3010301@agliodbs.com>
	<o2m603c8f071004011058neb6e0ea1r1f5caccbb7c50402@mail.gmail.com>
	<k2i3f0b79eb1004012306xdfa88c1cudd77b61368c997ea@mail.gmail.com>
	<w2z603c8f071004191904yefcaba2fy1abc2020174a35c5@mail.gmail.com>
	<s2u3f0b79eb1004200247q2f1c35f0pc7bf4af306f3514c@mail.gmail.com>
Date: Tue, 20 Apr 2010 06:52:11 -0400
Received: by 10.231.146.2 with SMTP id f2mr2347315ibv.23.1271760731792; Tue,
	20 Apr 2010 03:52:11 -0700 (PDT)
Message-ID: <j2r603c8f071004200352s5e175371z8bd42b9c18807fe@mail.gmail.com>
Subject: Re: [HACKERS] Streaming replication document improvements
From: Robert Haas <robertmhaas@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Tom Lane <tgl@sss.pgh.pa.us>,
	Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	pgsql-docs@postgresql.org,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.068 tagged_above=-10 required=5 tests=AWL=-2.351,
	BAYES_00=-1.9, FS_REPLICA=3.599, SARE_SPEC_REPLICA=0.72
X-Spam-Level: 
X-Archive-Number: 201004/64
X-Sequence-Number: 5463

On Tue, Apr 20, 2010 at 5:47 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Tue, Apr 20, 2010 at 11:04 AM, Robert Haas <robertmhaas@gmail.com> wro=
te:
>> Instead of doing this, could we just change the logic in InitPostgres?
>>
>> Current logic says we hit the connection limit if:
>>
>> =A0 =A0 =A0 =A0if (!am_superuser &&
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ReservedBackends > 0 &&
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0!HaveNFreeProcs(ReservedBackends))
>>
>> Couldn't we just change this to:
>>
>> =A0 =A0 =A0 =A0if ((!am_superuser || am_walsender) &&
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ReservedBackends > 0 &&
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0!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