MIME-Version: 1.0
References: <TYAPR01MB5866A9A39100EDA85E612B3EF5A39@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <20230216.142101.1198410163394123325.horikyota.ntt@gmail.com>
 <TYAPR01MB5866A2D600A86F743DDF8FE5F5A09@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <20230216.175546.98020490115903046.horikyota.ntt@gmail.com>
 <TYAPR01MB5866F00191375D0193320A4DF5A19@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <CAA4eK1K4uPbudrNdH+=_vN-Hpe9wYh=3vBS5Ww9dHn-LOWMV0g@mail.gmail.com>
 <TYAPR01MB5866504C73ACCF98E0E09B11F5A49@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <CAHut+Pu6Y+BkYKg6MYGi2zGnx6imeK4QzxBVhpQoPMeDr1npnQ@mail.gmail.com>
 <TYAPR01MB5866C6BCA4D9386D9C486033F5A59@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <CAA4eK1++pGobxhw6WiAGpuiQMr_cQ2NiHvMq1inn-YV54Cx6fg@mail.gmail.com>
 <TYAPR01MB5866982AD09BD79D2536675EF5AA9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <OSZPR01MB631003E2729310B412199EB3FDAB9@OSZPR01MB6310.jpnprd01.prod.outlook.com>
 <TYAPR01MB5866F85511EE35513A899AC3F5AB9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <CAD21AoCpf184dPD_n7F6FCn99siCNsiK3Viiu2K6gUzig+wu7Q@mail.gmail.com>
 <CAA4eK1+-iBwU-TC7SFQ=uJGsVBT1CZLheB0ZYEs+8ZnkWkA5Tw@mail.gmail.com> <CAD21AoA0mPq_m6USfAC8DAkvFfwjqGvGq++Uv=avryYotvq98A@mail.gmail.com>
In-Reply-To: <CAD21AoA0mPq_m6USfAC8DAkvFfwjqGvGq++Uv=avryYotvq98A@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Mon, 27 Feb 2023 14:56:19 +0530
Message-ID: <CAA4eK1LhTTADwJFh3-RoxxUQe3EvbvV3OFpoRNLQ3q_Tvo2caw@mail.gmail.com>
Subject: Re: Time delayed LR (WAS Re: logical replication restrictions)
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, 
	"shiy.fnst@fujitsu.com" <shiy.fnst@fujitsu.com>, Peter Smith <smithpb2250@gmail.com>, 
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>, "andres@anarazel.de" <andres@anarazel.de>, 
	"vignesh21@gmail.com" <vignesh21@gmail.com>, "shveta.malik@gmail.com" <shveta.malik@gmail.com>, 
	"Takamichi Osumi (Fujitsu)" <osumi.takamichi@fujitsu.com>, 
	"dilipbalaut@gmail.com" <dilipbalaut@gmail.com>, "euler@eulerto.com" <euler@eulerto.com>, 
	"m.melihmutlu@gmail.com" <m.melihmutlu@gmail.com>, "marcos@f10.com.br" <marcos@f10.com.br>, 
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1LhTTADwJFh3-RoxxUQe3EvbvV3OFpoRNLQ3q_Tvo2caw%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 27, 2023 at 1:50=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail.=
com> wrote:
>
> On Mon, Feb 27, 2023 at 3:34 PM Amit Kapila <amit.kapila16@gmail.com> wro=
te:
> >
> > > ---
> > > Why do we not to delay sending COMMIT PREPARED messages?
> > >
> >
> > I think we need to either add delay for prepare or commit prepared as
> > otherwise, it will lead to delaying the xact more than required.
>
> Agreed.
>
> > The
> > patch seems to add a delay before sending a PREPARE as that is the
> > time when the subscriber will apply the changes.
>
> Considering the purpose of this feature mentioned in the commit
> message "particularly to fix errors that might cause data loss",
> delaying sending PREPARE would really help that situation? For
> example, even after (mistakenly) executing PREPARE for a transaction
> executing DELETE without WHERE clause on the publisher the user still
> can rollback the transaction. They don't lose data on both nodes yet.
> After executing (and replicating) COMMIT PREPARED for that
> transaction, they lose the data on both nodes. IIUC the time-delayed
> logical replication should help this situation by delaying sending
> COMMIT PREPARED so that, for example, the user can stop logical
> replication before COMMIT PREPARED message arrives to the subscriber.
> So I think we should delay sending COMMIT PREPARED (and COMMIT)
> instead of PREPARE. This would help users to correct data loss errors,
> and would be more consistent with what recovery_min_apply_delay does.
>

The one difference w.r.t recovery_min_apply_delay is that here we will
hold locks for the duration of the delay which didn't seem to be a
good idea. This will also probably lead to more bloat as we will keep
transactions open for a long time. Doing it before DecodePrepare won't
have such problems. This is the reason that we decide to perform a
delay at the start of the transaction instead at commit/prepare in the
subscriber-side approach.

--=20
With Regards,
Amit Kapila.