Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k6UUi-0005Lf-SE for pgsql-bugs@arkaria.postgresql.org; Fri, 14 Aug 2020 07:51:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k6UUh-0002Le-A4 for pgsql-bugs@arkaria.postgresql.org; Fri, 14 Aug 2020 07:51:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k6UUg-0002LX-Ra for pgsql-bugs@lists.postgresql.org; Fri, 14 Aug 2020 07:51:19 +0000 Received: from mail-pf1-x442.google.com ([2607:f8b0:4864:20::442]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1k6UUf-0008FX-0H for pgsql-bugs@lists.postgresql.org; Fri, 14 Aug 2020 07:51:18 +0000 Received: by mail-pf1-x442.google.com with SMTP id a79so4164667pfa.8 for ; Fri, 14 Aug 2020 00:51:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=leadboat.com; s=google; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=88M/gQ0RdG2431Sv6XyzTMJy+mJbfWE71VIh0v7bf0c=; b=MmkG1h/2H9KwcWvevZ8vEeMoVv1OgLuyDMvSyarZD+GTwrcPlT/pobcQLdqZCdmFva DOaEwdzq3vJfG6czs42MOdZLOzGC0bbC3H4SmyNccXISRzdzXtywgGPpJmLKJJzX/E3p 1NTK9xYPwJjLcgY3855AQJxK8jo0/hV9ezMew= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=88M/gQ0RdG2431Sv6XyzTMJy+mJbfWE71VIh0v7bf0c=; b=otHFtAgARLOVIxcKg05j0CWBp8Qf7vthiLYhAyhHtt+o24pimZsGLWFp+ZTMETZDHt d7QqK+dXHIPjxj4A54ZY4HLrN9l8Myl2AIuC8Uo1N4EiXfh7nAZMr53UI5Az/G7tyd+i mTmYC7fJ9E2fJLMswF8g4OCOGhv9UM4/5gJZ21dOSr60oOn5znFEdT+DN6OsZj71JBXB VUYZCwtRAIhuqxOIC8SjQzlJ5YIn+iB/bNvp5qY9ZavK7dGSPNb0D5gBuCDWseHCEbGy z+c5/mp8E4aQPNDiAZwZtkExy6EW04xMhnTs42+0uv1iozvFCzPU9FnOfwgL3r2uuHZ6 hxug== X-Gm-Message-State: AOAM531lSEWpVyLTiDs9+5PFgqp9Ia27PbOiALPpih5eLDo5WDkWQDxu dNfc+q+WIfRk0JnWHlm+N0+eLw== X-Google-Smtp-Source: ABdhPJw7xWzvA+DUmWVcy4nxiCLwqFUop9agSt44tsU3YVkbKXMCCpCnJQtHdOitaOvb1OKjyRYYfw== X-Received: by 2002:a63:d20e:: with SMTP id a14mr971079pgg.231.1597391473733; Fri, 14 Aug 2020 00:51:13 -0700 (PDT) Received: from rfd.leadboat.com (108-233-125-46.lightspeed.sntcca.sbcglobal.net. [108.233.125.46]) by smtp.gmail.com with ESMTPSA id b63sm8164111pfg.43.2020.08.14.00.51.12 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 14 Aug 2020 00:51:13 -0700 (PDT) Date: Fri, 14 Aug 2020 00:51:11 -0700 From: Noah Misch To: Tom Lane Cc: jv.cyril@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16508: using multi-host connection string when the first host is starting fails Message-ID: <20200814075111.GA1193352@rfd.leadboat.com> References: <16508-1a63222835164566@postgresql.org> <20200813075539.GD1133236@rfd.leadboat.com> <530083.1597356494@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <530083.1597356494@sss.pgh.pa.us> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, Aug 13, 2020 at 06:08:14PM -0400, Tom Lane wrote: > Noah Misch writes: > > On Wed, Jun 24, 2020 at 08:17:44AM +0000, PG Bug reporting form wrote: > >> I'm connection to pg10 using psql (tried with clients psql 10.11 & psql > >> 12.2) using a connection string such as: > >> psql 'dbname=xxxxx1,xxxxx2,xxxxx3,xxxxx4 target_session_attrs=read-write' > >> > >> the connection to first database (xxxxx1) fail with the error: > >> psql.bin: FATAL: the database system is starting up > >> > >> which is correct according to postgres state on that machine, > >> but then I would expect the psql tries the next server (xxxxx2) with is in > >> the one acceptiong the connection params (target_session_attrs=read-write) > >> instead of the error. > > > I agree. > I assume that the actual test case involved a comma-separated *host* > (or hostaddr) list, which is what drives multiple connection attempts. Like you, I assumed that. > It is true that if we manage to make a connection to a host, but it > then rejects us for some reason, we just give up rather than trying > the next host. The problem with trying to improve that is that it's > very unclear which cases it's actually appropriate to do that for. That is an obstacle, yes. Assume the connection string has N>=2 entries where, typically, one is read-write and N-1 are read-only. Here's the principle that made me agree with the bug report. It should be possible to "pg_ctl restart" any one host without interrupting clients' ability to form a read-only connection using the multi-host connection string. Currently, the outcome depends on the timing within the restart sequence: 1. fails during shutdown checkpoint 2. succeeds after port closes 3. fails after port opens, during early recovery 4. succeeds after recovery permits read-only connections That's a bad user experience. I didn't form a proposal for what to do instead, but I doubt we already have the optimum. > As an example, if you fat-finger the password to host 1, it's unlikely > that silently switching our attention to host 2 would be advisable. > At best, what you'd get is several confusing duplicate messages. I'd be fine with the duplicate messages. Yeah, if we could divine that the connection attempt failed due to a client typo, it would be nice to stop there. A client can't divine that. If the PostgreSQL servers use pam or ldap authentication, server-side trouble can cause transient authentication failures. I do seem to recall discussion that rejected retrying on all errors, but I looked and didn't locate it.