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 1p069m-0005oZ-3M for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Nov 2022 19:20:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p069l-00047I-0R for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Nov 2022 19:20:37 +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 1p069k-00046D-Jv for pgsql-hackers@lists.postgresql.org; Tue, 29 Nov 2022 19:20:36 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p069i-0005TJ-GK for pgsql-hackers@lists.postgresql.org; Tue, 29 Nov 2022 19:20:36 +0000 Received: by mail-ej1-x62c.google.com with SMTP id n20so36292919ejh.0 for ; Tue, 29 Nov 2022 11:20:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=rl3GzUHI2fBGA1Ih5jyPnaNT+k5fVAObEinSuXoJZn0=; b=labL82lB3x6fUl0DPuk9oJqcclZu4RG2pqFo+oKeiVcUwXY3jhKI60mIjYtUFsWCvy LFQ7ik0WqgJ3DflCYiszfwgSISJ2l4qu7LAXUV8irdZ+3zLiwfGWxvAs4dtXCAU6aTkS yxEflO0haCt9qqUv7fPWwxmxfn38c4+Mglfzm31udHITPDvwZV/9SnMMV173bWIXteYg hMz44EodVwiauYGnbXSsgh4YcC7Bz5GdaUmJqB9hxa3ayphPr58+TkfCi3K4C2r4/nwD qVjfg7gsP35tQHCketKX4XeaG7qcUNCrRpGLmsjGoDLcM+du41gEczRyE5FksGGMObGh hHyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=rl3GzUHI2fBGA1Ih5jyPnaNT+k5fVAObEinSuXoJZn0=; b=UDxT4jTxyFABbuPZjrC6yWdQkbdLF3rBRcy/Z0jllU4he9nPAHWvauUnvLPbvx6otS 2nop8d7TE57XcpdqWmxczEhQJXeLXsgNqCRdNV+XqZtW8rgaNu4dUksVdfj14Y304qqm szKmBcrRZOLLIECRlcZRJ+kzyWcGpEgCU2DEPUisG3tc0UBBlrltZL0SYCSwDcqFkXB0 Y9olRq+nIFtBWlrzUKqj4ohsN8bonEP6F+joJ2vezy/RYtfJHfCO8XJzQ3WfiQk9HQrs QXA451LHG3z+K48uF0Z2CX2qY6ANEcDCPjLDPJ1AH38EAoq5NH57VZdDjbJi8OO/XEWP FjEQ== X-Gm-Message-State: ANoB5pl4Cl1m76btIUHWT/YFxLDnr3WNczmeDidEfeWpv/U/QKzbwnQu S02cTLZOAZR5iJiN6P+0FfAVop0Eb/Wv41cZmD0= X-Google-Smtp-Source: AA0mqf5+MBHELTpcW7g2U5R/TWFRsIff08wjiDgOA8nh/8pVauCVJV8J2rL41qFxbEaDpMfCjyi/rGr2aSS9bt1HBZ8= X-Received: by 2002:a17:906:9f13:b0:7bf:661b:6b0e with SMTP id fy19-20020a1709069f1300b007bf661b6b0emr11323802ejc.191.1669749631850; Tue, 29 Nov 2022 11:20:31 -0800 (PST) MIME-Version: 1.0 References: <20220805.114916.994654810780821553.horikyota.ntt@gmail.com> <20220809.161236.1486509314201074910.horikyota.ntt@gmail.com> In-Reply-To: From: SATYANARAYANA NARLAPURAM Date: Tue, 29 Nov 2022 11:20:19 -0800 Message-ID: Subject: Re: An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication To: Andrey Borodin Cc: Bruce Momjian , Bharath Rupireddy , Kyotaro Horiguchi , Laurenz Albe , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000ab9c9105eea0e191" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ab9c9105eea0e191 Content-Type: text/plain; charset="UTF-8" On Tue, Nov 29, 2022 at 10:52 AM Andrey Borodin wrote: > On Tue, Nov 29, 2022 at 8:29 AM Bruce Momjian wrote: > > > > On Tue, Nov 29, 2022 at 08:14:10AM -0800, SATYANARAYANA NARLAPURAM wrote: > > > 2. Process proc die immediately when a backend is waiting for sync > > > replication acknowledgement, as it does today, however, upon > restart, > > > don't open up for business (don't accept ready-only connections) > > > unless the sync standbys have caught up. > > > > > > > > > Are you planning to block connections or queries to the database? It > would be > > > good to allow connections and let them query the monitoring views but > block the > > > queries until sync standby have caught up. Otherwise, this leaves a > monitoring > > > hole. In cloud, I presume superusers are allowed to connect and > monitor (end > > > customers are not the role members and can't query the data). The same > can't be > > > true for all the installations. Could you please add more details on > your > > > approach? > > > > I think ALTER SYSTEM should be allowed, particularly so you can modify > > synchronous_standby_names, no? > > We don't allow SQL access during crash recovery until it's caught up > to consistency point. And that's for a reason - the cluster may have > invalid system catalog. > So no, after crash without a quorum of standbys you can only change > auto.conf and send SIGHUP. Accessing the system catalog during crash > recovery is another unrelated problem. > In the crash recovery case, catalog is inconsistent but in this case, the cluster has remote uncommitted changes (consistent). Accepting a superuser connection is no harm. The auth checks performed are still valid after standbys fully caught up. I don't see a reason why superuser / pg_monitor connections are required to be blocked. > But I'd propose to treat these two points differently, they possess > drastically different scales of danger. Query Cancels are issued here > and there during failovers\switchovers. Crash amidst network > partitioning is not that common. > Supportability and operability are more important in corner cases to quickly troubleshoot an issue, > > Best regards, Andrey Borodin. > --000000000000ab9c9105eea0e191 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Tue, Nov 29, 2022 at 10:52 AM Andr= ey Borodin <amborodin86@gmail.c= om> wrote:
bruce@momjian.us> wrote:
>
> On Tue, Nov 29, 2022 at 08:14:10AM -0800, SATYANARAYANA NARLAPURAM wro= te:
> >=C2=A0 =C2=A0 =C2=A02. Process proc die immediately when a backend= is waiting for sync
> >=C2=A0 =C2=A0 =C2=A0replication acknowledgement, as it does today,= however, upon restart,
> >=C2=A0 =C2=A0 =C2=A0don't open up for business (don't acce= pt ready-only connections)
> >=C2=A0 =C2=A0 =C2=A0unless the sync standbys have caught up.
> >
> >
> > Are you planning to block connections or queries to the database?= It would be
> > good to allow connections and let them query the monitoring views= but block the
> > queries until sync standby have caught up. Otherwise, this leaves= a monitoring
> > hole. In cloud, I presume superusers are allowed to connect and m= onitor (end
> > customers are not the role members and can't query the data).= The same can't be
> > true for all the installations. Could you please add more details= on your
> > approach?
>
> I think ALTER SYSTEM should be allowed, particularly so you can modify=
> synchronous_standby_names, no?

We don't allow SQL access during crash recovery until it's caught u= p
to consistency point. And that's for a reason - the cluster may have invalid system catalog.
So no, after crash without a quorum of standbys you can only change
auto.conf and send SIGHUP. Accessing the system catalog during crash
recovery is another unrelated problem.

= In the crash recovery case, catalog is inconsistent but in this case, the c= luster has remote uncommitted changes (consistent). Accepting a superuser c= onnection is no harm. The auth checks performed are still valid after stand= bys fully caught up. I don't see a reason why superuser / pg_monitor co= nnections are required to be blocked.


But I'd propose to treat these two points differently, they possess
drastically different scales of danger. Query Cancels are issued here
and there during failovers\switchovers. Crash amidst network
partitioning is not that common.

Suppor= tability and operability are more important in corner cases to quickly trou= bleshoot an issue,
=C2=A0

Best regards, Andrey Borodin.
--000000000000ab9c9105eea0e191--