Re: Introduce XID age and inactive timeout based replication slot invalidation

Hi,

On Thu, Mar 21, 2024 at 10:53:54AM +0530, Bharath Rupireddy wrote:
> On Thu, Mar 21, 2024 at 9:07 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > But the issue is that it would make it inconsistent with the new inactivetimeout
> > > > in the subscription that is added in "v12-0005".
> > >
> > > Can you please elaborate what the inconsistency it causes with inactivetimeout?
> > >
> > I think the inconsistency can arise from the fact that on publisher
> > one can change the inactive_timeout for the slot corresponding to a
> > subscription but the subscriber won't know, so it will still show the
> > old value.

Yeah, that was what I had in mind.

> > If we want we can document this as a limitation and let
> > users be aware of it. However, I feel at this stage, let's not even
> > expose this from the subscription or maybe we can discuss it once/if
> > we are done with other patches.

I agree, it's important to expose it for things like "failover" but I think we
can get rid of it for the timeout one.

>> Anyway, if one wants to use this
> > feature with a subscription, she can create a slot first on the
> > publisher with inactive_timeout value and then associate such a slot
> > with a required subscription.

Right.

>
> If we are not exposing it via subscription (meaning, we don't consider
> v13-0004 and v13-0005 patches), I feel we can have a new SQL API
> pg_alter_replication_slot(int inactive_timeout) for now just altering
> the inactive_timeout of a given slot.

Agree, that seems more "natural" that going through a replication connection.

> With this approach, one can do either of the following:
> 1) Create a slot with SQL API with inactive_timeout set, and use it
> for subscriptions or for streaming standbys.

Yes.

> 2) Create a slot with SQL API without inactive_timeout set, use it for
> subscriptions or for streaming standbys, and set inactive_timeout
> later via pg_alter_replication_slot() depending on how the slot is
> consumed

Yes.

> 3) Create a subscription with create_slot=true, and set
> inactive_timeout via pg_alter_replication_slot() depending on how the
> slot is consumed.

Yes.

We could also do the above 3 and altering the timeout with a replication
connection but the SQL API seems more natural to me.

>
> This approach seems consistent and minimal to start with.
>
> If we agree on this, I'll drop both 0004 and 0005 that are allowing
> inactive_timeout to be set via replication commands and via
> create/alter subscription respectively, and implement
> pg_alter_replication_slot().

+1 on this.

> FWIW, adding the new SQL API pg_alter_replication_slot() isn't that hard.

Also I think we should ensure that one could "only" alter the timeout property
for the time being (if not that could lead to the subscription inconsistency
mentioned above).

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com