MIME-Version: 1.0
References: <CALj2ACXQmGx-L073=8CUSOk3hhe=HL_e6dveYeR9WJcEh+7jvg@mail.gmail.com>
 <CAA4eK1L_UP=ZjCj=+cGEauYXHC1icUEfx16p+B7qrAHZuCx5Ew@mail.gmail.com>
 <CALj2ACU6tV0bh_MiFBYU7-VK2cj8Gas73htcQjoOv0uvSEG_HA@mail.gmail.com>
 <CAJpy0uA-gJp=aoH+=1jYvU4mZYt4vJCe=cmNn50ZLGFUdjB34g@mail.gmail.com>
 <CAJpy0uAqBi+GbNn2ngJ-A_Z905CD3ss896bqY2ACUjGiF1Gkng@mail.gmail.com>
 <CAJpy0uCLu+mqAwAMum=pXE9YYsy0BE7hOSw_Wno5vjwpFY=63g@mail.gmail.com>
 <CAJpy0uB-yE+Riw7JQ4hW0+igJxvPc+rq+9c7WyTa1Jz7+2gAiA@mail.gmail.com>
 <CALj2ACXRFx9g7A9RFJZF7eBe=zxk7=apMRFuCgJJKYB7O=vgwg@mail.gmail.com>
 <ZgJ9D0p28ijwsjsl@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA4eK1KrPGwfZV9LYGidjxHeW+rxJ=E2ThjXvwRGLO=iLNuo=Q@mail.gmail.com> <ZgKGIDC5lttWTdJH@ip-10-97-1-34.eu-west-3.compute.internal>
In-Reply-To: <ZgKGIDC5lttWTdJH@ip-10-97-1-34.eu-west-3.compute.internal>
From: shveta malik <shveta.malik@gmail.com>
Date: Tue, 26 Mar 2024 15:17:36 +0530
Message-ID: <CAJpy0uDkTW+t1k3oPkaipFBzZePfFNB5DmiA==pxRGcAdpF=Pg@mail.gmail.com>
Subject: Re: Introduce XID age and inactive timeout based replication slot invalidation
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, 
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, 
	Nathan Bossart <nathandbossart@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, shveta malik <shveta.malik@gmail.com>
Content-Type: multipart/mixed; boundary="000000000000cfebc606148d2ead"
Archived-At: <https://www.postgresql.org/message-id/CAJpy0uDkTW%2Bt1k3oPkaipFBzZePfFNB5DmiA%3D%3DpxRGcAdpF%3DPg%40mail.gmail.com>
Precedence: bulk

--000000000000cfebc606148d2ead
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 26, 2024 at 1:54=E2=80=AFPM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi,
>
> On Tue, Mar 26, 2024 at 01:37:21PM +0530, Amit Kapila wrote:
> > On Tue, Mar 26, 2024 at 1:15=E2=80=AFPM Bertrand Drouvot
> > <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > 2 =3D=3D=3D
> > >
> > > It looks like inactive_since is set to the current timestamp on the s=
tandby
> > > each time the sync worker does a cycle:
> > >
> > > primary:
> > >
> > > postgres=3D# select slot_name,inactive_since from pg_replication_slot=
s where failover =3D 't';
> > >   slot_name  |        inactive_since
> > > -------------+-------------------------------
> > >  lsub27_slot | 2024-03-26 07:39:19.745517+00
> > >  lsub28_slot | 2024-03-26 07:40:24.953826+00
> > >
> > > standby:
> > >
> > > postgres=3D# select slot_name,inactive_since from pg_replication_slot=
s where failover =3D 't';
> > >   slot_name  |        inactive_since
> > > -------------+-------------------------------
> > >  lsub27_slot | 2024-03-26 07:43:56.387324+00
> > >  lsub28_slot | 2024-03-26 07:43:56.387338+00
> > >
> > > I don't think that should be the case.
> > >
> >
> > But why? This is exactly what we discussed in another thread where we
> > agreed to update inactive_since even for sync slots.
>
> Hum, I thought we agreed to "sync" it and to "update it to current time"
> only at promotion time.

I think there may have been some misunderstanding here. But now if I
rethink this, I am fine with 'inactive_since' getting synced from
primary to standby. But if we do that, we need to add docs stating
"inactive_since" represents primary's inactivity and not standby's
slots inactivity for synced slots. The reason for this clarification
is that the synced slot might be generated much later, yet
'inactive_since' is synced from the primary, potentially indicating a
time considerably earlier than when the synced slot was actually
created.

Another approach could be that "inactive_since" for synced slot
actually gives its own inactivity data rather than giving primary's
slot data. We update inactive_since on standby only at 3 occasions:
1) at the time of creation of the synced slot.
2) during standby restart.
3) during promotion of standby.

I have attached a sample patch for this idea as.txt file.

I am fine with any of these approaches.  One gives data synced from
primary for synced slots, while another gives actual inactivity data
of synced slots.

thanks
Shveta

--000000000000cfebc606148d2ead
Content-Type: text/plain; charset="US-ASCII"; 
	name="v1-0001-inactive_since-for-synced-slots.patch.txt"
Content-Disposition: attachment; 
	filename="v1-0001-inactive_since-for-synced-slots.patch.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_lu86xh8q0>
X-Attachment-Id: f_lu86xh8q0

RnJvbSA3ZGNkMGU5NTI5OTI2MzE4N2ViMWYwMzgxMmY4MzIxYjI2MTJlZTVjIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTaHZldGEgTWFsaWsgPHNodmV0YS5tYWxpa0BnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMjYgTWFyIDIwMjQgMTQ6NDI6MjUgKzA1MzAKU3ViamVjdDogW1BBVENIIHYx
XSBpbmFjdGl2ZV9zaW5jZSBmb3Igc3luY2VkIHNsb3RzLgoKaW5hY3RpdmVfc2luY2UgaXMgdXBk
YXRlZCBmb3Igc3luY2VkIHNsb3RzOgoxKSBhdCB0aGUgdGltZSBvZiBjcmVhdGlvbiBvZiBzbG90
LgoyKSBkdXJpbmcgc2VydmVyIHJlc3RhcnQuCjMpIGR1cmluZyBwcm9tb3Rpb24uCi0tLQogc3Jj
L2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC9zbG90c3luYy5jIHwgIDEgKwogc3JjL2JhY2tl
bmQvcmVwbGljYXRpb24vc2xvdC5jICAgICAgICAgICAgIHwgMTUgKysrKysrKysrKysrLS0tCiAy
IGZpbGVzIGNoYW5nZWQsIDEzIGluc2VydGlvbnMoKyksIDMgZGVsZXRpb25zKC0pCgpkaWZmIC0t
Z2l0IGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC9zbG90c3luYy5jIGIvc3JjL2Jh
Y2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC9zbG90c3luYy5jCmluZGV4IGJiZjlhMmM0ODUuLjYx
MTQ4OTVkY2EgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvc2xv
dHN5bmMuYworKysgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9sb2dpY2FsL3Nsb3RzeW5jLmMK
QEAgLTYyOCw2ICs2MjgsNyBAQCBzeW5jaHJvbml6ZV9vbmVfc2xvdChSZW1vdGVTbG90ICpyZW1v
dGVfc2xvdCwgT2lkIHJlbW90ZV9kYmlkKQogCQlTcGluTG9ja0FjcXVpcmUoJnNsb3QtPm11dGV4
KTsKIAkJc2xvdC0+ZWZmZWN0aXZlX2NhdGFsb2dfeG1pbiA9IHhtaW5faG9yaXpvbjsKIAkJc2xv
dC0+ZGF0YS5jYXRhbG9nX3htaW4gPSB4bWluX2hvcml6b247CisJCXNsb3QtPmluYWN0aXZlX3Np
bmNlID0gR2V0Q3VycmVudFRpbWVzdGFtcCgpOwogCQlTcGluTG9ja1JlbGVhc2UoJnNsb3QtPm11
dGV4KTsKIAkJUmVwbGljYXRpb25TbG90c0NvbXB1dGVSZXF1aXJlZFhtaW4odHJ1ZSk7CiAJCUxX
TG9ja1JlbGVhc2UoUHJvY0FycmF5TG9jayk7CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9yZXBs
aWNhdGlvbi9zbG90LmMgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9zbG90LmMKaW5kZXggZDBh
MmY0NDBlZi4uZjJhNTdhMTRlYyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24v
c2xvdC5jCisrKyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL3Nsb3QuYwpAQCAtNjI4LDcgKzYy
OCwxMCBAQCByZXRyeToKIAkgKiBub3cuCiAJICovCiAJU3BpbkxvY2tBY3F1aXJlKCZzLT5tdXRl
eCk7Ci0Jcy0+aW5hY3RpdmVfc2luY2UgPSAwOworCisJaWYgKCEoUmVjb3ZlcnlJblByb2dyZXNz
KCkgJiYgcy0+ZGF0YS5zeW5jZWQpKQorCQlzLT5pbmFjdGl2ZV9zaW5jZSA9IDA7CisKIAlTcGlu
TG9ja1JlbGVhc2UoJnMtPm11dGV4KTsKIAogCWlmIChhbV93YWxzZW5kZXIpCkBAIC03MDQsMTQg
KzcwNywyMCBAQCBSZXBsaWNhdGlvblNsb3RSZWxlYXNlKHZvaWQpCiAJCSAqLwogCQlTcGluTG9j
a0FjcXVpcmUoJnNsb3QtPm11dGV4KTsKIAkJc2xvdC0+YWN0aXZlX3BpZCA9IDA7Ci0JCXNsb3Qt
PmluYWN0aXZlX3NpbmNlID0gbm93OworCisJCWlmICghKFJlY292ZXJ5SW5Qcm9ncmVzcygpICYm
IHNsb3QtPmRhdGEuc3luY2VkKSkKKwkJCXNsb3QtPmluYWN0aXZlX3NpbmNlID0gbm93OworCiAJ
CVNwaW5Mb2NrUmVsZWFzZSgmc2xvdC0+bXV0ZXgpOwogCQlDb25kaXRpb25WYXJpYWJsZUJyb2Fk
Y2FzdCgmc2xvdC0+YWN0aXZlX2N2KTsKIAl9CiAJZWxzZQogCXsKIAkJU3BpbkxvY2tBY3F1aXJl
KCZzbG90LT5tdXRleCk7Ci0JCXNsb3QtPmluYWN0aXZlX3NpbmNlID0gbm93OworCisJCWlmICgh
KFJlY292ZXJ5SW5Qcm9ncmVzcygpICYmIHNsb3QtPmRhdGEuc3luY2VkKSkKKwkJCXNsb3QtPmlu
YWN0aXZlX3NpbmNlID0gbm93OworCiAJCVNwaW5Mb2NrUmVsZWFzZSgmc2xvdC0+bXV0ZXgpOwog
CX0KIAotLSAKMi4zNC4xCgo=
--000000000000cfebc606148d2ead--