Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1DF31634D24;
	Tue, 20 Apr 2010 11:42:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 75468-01-10; Tue, 20 Apr 2010 14:42:46 +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-gy0-f174.google.com (mail-gy0-f174.google.com
	[209.85.160.174])
	by mail.postgresql.org (Postfix) with ESMTP id 4E14E63330E;
	Tue, 20 Apr 2010 10:47:16 -0300 (ADT)
Received: by gyg13 with SMTP id 13so3209751gyg.19
	for <multiple recipients>; Tue, 20 Apr 2010 06:47:10 -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=y+p85lr4J77QB6UdyeVqpN+8UshCOxvHga47AjLFcCQ=;
	b=Ln4Ctv47bZmSCAEnGB8ZeQCwAvkT6vjVb2bEWRgmjh7vj7smVYnAP3vko5PPXpp09P
	CdCN+4gKgMEmM/neTW0C0KtUn7kC82NqrMXzFwPjKkf97LcKVN3WCezfA0V1et245Lby
	iQ/CnwI/hRUOd3t/jgr09x72J60Q8cWjB6FxQ=
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=QdYCuyCIu0pSDVKrdB59EhdQ0Tb1q7sWoqmKkbrG5aMlN4mjAc0CDIC1LE9LEBCa+/
	xez+6yK5AnJlvTeGfIfUJ7hUUgcnar9orKXxafjhFgzFb+pPb+VAQp1uLhNnZATxBtOf
	jS3u8NEVN0ZozuRL5lgNIHYIyOz0tmbM3d/1g=
MIME-Version: 1.0
Received: by 10.100.142.12 with HTTP; Tue, 20 Apr 2010 06:47:08 -0700 (PDT)
In-Reply-To: <j2r603c8f071004200352s5e175371z8bd42b9c18807fe@mail.gmail.com>
References: <3f0b79eb1003300152g5327eb47w8f9aecae6002b215@mail.gmail.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>
	<j2r603c8f071004200352s5e175371z8bd42b9c18807fe@mail.gmail.com>
Date: Tue, 20 Apr 2010 22:47:08 +0900
Received: by 10.100.74.5 with SMTP id w5mr17122435ana.206.1271771228214; Tue,
	20 Apr 2010 06:47:08 -0700 (PDT)
Message-ID: <h2w3f0b79eb1004200647nc0aa56d8ibb4be9f2f00731f5@mail.gmail.com>
Subject: Re: [HACKERS] Streaming replication document improvements
From: Fujii Masao <masao.fujii@gmail.com>
To: Robert Haas <robertmhaas@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=2.42 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, FS_REPLICA=3.599, SARE_SPEC_REPLICA=0.72
X-Spam-Level: **
X-Archive-Number: 201004/67
X-Sequence-Number: 5466

On Tue, Apr 20, 2010 at 7:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Let's just stop for a second and think about why we have
> superuser_reserved_connections in the first place. =A0As 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. =A0For 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. =A0In 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. =A0If 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. =A0When 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. =A0What 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.

Yeah, I agree with you, but the difference is only how to achieve.
ISTM that there are three choices:

1. Heikki's proposal
> ReservedBackends =3D superuser_reserved_connections + max_wal_senders
> MaxBackends =3D max_connections + autovacuum_max_workers + max_wal_sender=
s + 1

2. My proposal
    Remove superuser privilege from replication connection

3. Your proposal
    Treat superuser replication connection like non-superuser one

Since 3. is confusing for me, I like 1. or 2.

Regards,

--=20
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center