Re: logical decoding and replication of sequences, take 2

Patch set needs a rebase, PFA rebased patch-set.

The conflict was in commit "Add decoding of sequences to built-in
replication", in files tablesync.c and 002_pg_dump.pl.

On Thu, May 18, 2023 at 7:53 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> Hi,
> Sorry for jumping late in this thread.
>
> I started experimenting with the functionality. Maybe something that
> was already discussed earlier. Given that the thread is being
> discussed for so long and has gone several changes, revalidating the
> functionality is useful.
>
> I considered following aspects:
> Changes to the sequence on subscriber
> -----------------------------------------------------
> 1. Since this is logical decoding, logical replica is writable. So the
> logically replicated sequence can be manipulated on the subscriber as
> well. This implementation consolidates the changes on subscriber and
> publisher rather than replicating the publisher state as is. That's
> good. See example command sequence below
> a. publisher calls nextval() - this sets the sequence state on
> publisher as (1, 32, t) which is replicated to the subscriber.
> b. subscriber calls nextval() once - this sets the sequence state on
> subscriber as (34, 32, t)
> c. subscriber calls nextval() 32 times - on-disk state of sequence
> doesn't change on subscriber
> d. subscriber calls nextval() 33 times - this sets the sequence state
> on subscriber as (99, 0, t)
> e. publisher calls nextval() 32 times - this sets the sequence state
> on publisher as (33, 0, t)
>
> The on-disk state on publisher at the end of e. is replicated to the
> subscriber but subscriber doesn't apply it. The state there is still
> (99, 0, t). I think this is closer to how logical replication of
> sequence should look like. This is aso good enough as long as we
> expect the replication of sequences to be used for failover and
> switchover.
>
> But it might not help if we want to consolidate the INSERTs that use
> nextvals(). If we were to treat sequences as accumulating the
> increments, we might be able to resolve the conflicts by adjusting the
> columns values considering the increments made on subscriber. IIUC,
> conflict resolution is not part of built-in logical replication. So we
> may not want to go this route. But worth considering.
>
> Implementation agnostic decoded change
> --------------------------------------------------------
> Current method of decoding and replicating the sequences is tied to
> the implementation - it replicates the sequence row as is. If the
> implementation changes in future, we might need to revise the decoded
> presentation of sequence. I think only nextval() matters for sequence.
> So as long as we are replicating information enough to calculate the
> nextval we should be good. Current implementation does that by
> replicating the log_value and is_called. is_called can be consolidated
> into log_value itself. The implemented protocol, thus requires two
> extra values to be replicated. Those can be ignored right now. But
> they might pose a problem in future, if some downstream starts using
> them. We will be forced to provide fake but sane values even if a
> future upstream implementation does not produce those values. Of
> course we can't predict the future implementation enough to decide
> what would be an implementation independent format. E.g. if a
> pluggable storage were to be used to implement sequences or if we come
> around implementing distributed sequences, their shape can't be
> predicted right now. So a change in protocol seems to be unavoidable
> whatever we do. But starting with bare minimum might save us from
> larger troubles. I think, it's better to just replicate the nextval()
> and craft the representation on subscriber so that it produces that
> nextval().
>
> 3. Primary key sequences
> -----------------------------------
> I am not experimented with this. But I think we will need to add the
> sequences associated with the primary keys to the publications
> publishing the owner tables. Otherwise, we will have problems with the
> failover. And it needs to be done automatically since a. the names of
> these sequences are generated automatically b. publications with FOR
> ALL TABLES will add tables automatically and start replicating the
> changes. Users may not be able to intercept the replication activity
> to add the associated sequences are also addedto the publication.
>
> --
> Best Wishes,
> Ashutosh Bapat



--
Best Wishes,
Ashutosh Bapat