Re: Logical Replication of sequences

On Wed, Jun 12, 2024 at 6:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Jun 12, 2024 at 10:44 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Tue, Jun 11, 2024 at 7:36 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > 1) CREATE PUBLICATION syntax enhancement:
> > > CREATE PUBLICATION ... FOR ALL SEQUENCES;
> > > The addition of a new column titled "all sequences" in the
> > > pg_publication system table will signify whether the publication is
> > > designated as all sequences publication or not.
> > >
> >
> > The first approach sounds like we don't create entries for sequences
> > in pg_subscription_rel. In this case, how do we know all sequences
> > that we need to refresh when executing the REFRESH PUBLICATION
> > SEQUENCES command you mentioned below?
> >
>
> As per my understanding, we should be creating entries for sequences
> in pg_subscription_rel similar to tables. The difference would be that
> we won't need all the sync_states (i = initialize, d = data is being
> copied, f = finished table copy, s = synchronized, r = ready) as we
> don't need any synchronization with apply workers.

Agreed.

>
> > > 2) CREATE SUBSCRIPTION -- no syntax change.
> > > Upon creation of a subscription, the following additional steps will
> > > be managed by the subscriber:
> > > i) The subscriber will retrieve the list of sequences associated with
> > > the subscription's publications.
> > > ii) For each sequence: a) Retrieve the sequence value from the
> > > publisher by invoking the pg_sequence_state function. b) Set the
> > > sequence with the value obtained from the publisher. iv) Once the
> > > subscription creation is completed, all sequence values will become
> > > visible at the subscriber's end.
> >
> > Sequence values are always copied from the publisher? or does it
> > happen only when copy_data = true?
> >
>
> It is better to do it when "copy_data = true" to keep it compatible
> with the table's behavior.

+1

>
> > Probably we can
> > start with a single worker and extend it to have multiple workers.
>
> Yeah, starting with a single worker sounds good for now. Do you think
> we should sync all the sequences in a single transaction or have some
> threshold value above which a different transaction would be required
> or maybe a different sequence sync worker altogether? Now, having
> multiple sequence-sync workers requires some synchronization so that
> only a single worker is allocated for one sequence.
>
> The simplest thing is to use a single sequence sync worker that syncs
> all sequences in one transaction but with a large number of sequences,
> it could be inefficient. OTOH, I am not sure if it would be a problem
> in reality.

I think that we can start with using a single worker and one
transaction, and measure the performance with a large number of
sequences.

> > Or yet another idea I came up with is that a tablesync worker will
> > synchronize both the table and sequences owned by the table. That is,
> > after the tablesync worker caught up with the apply worker, the
> > tablesync worker synchronizes sequences associated with the target
> > table as well. One benefit would be that at the time of initial table
> > sync being completed, the table and its sequence data are consistent.

Correction; it's not guaranteed that the sequence data and table data
are consistent even in this case since the tablesync worker could get
on-disk sequence data that might have already been updated.

> > As soon as new changes come to the table, it would become inconsistent
> > so it might not be helpful much, though. Also, sequences that are not
> > owned by any table will still need to be synchronized by someone.
> >
>
> The other thing to consider in this idea is that we somehow need to
> distinguish the sequences owned by the table.

I think we can check pg_depend. The owned sequences reference to the table.

>
> > >
> > > 3) Refreshing the sequence can be achieved through the existing
> > > command: ALTER SUBSCRIPTION ... REFRESH PUBLICATION(no syntax change
> > > here).
> > > The subscriber identifies stale sequences, meaning sequences present
> > > in pg_subscription_rel but absent from the publication, and removes
> > > them from the pg_subscription_rel system table. The subscriber also
> > > checks for newly added sequences in the publisher and synchronizes
> > > their values from the publisher using the steps outlined in the
> > > subscription creation process. It's worth noting that previously
> > > synchronized sequences won't be synchronized again; the sequence sync
> > > will occur solely for the newly added sequences.
> > >
> > > 4) Introducing a new command for refreshing all sequences: ALTER
> > > SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES.
> > > The subscriber will remove stale sequences and add newly added
> > > sequences from the publisher. Following this, it will re-synchronize
> > > the sequence values for all sequences in the updated list from the
> > > publisher, following the steps outlined in the subscription creation
> > > process.
> >
> > The difference between 3) and 4) is whether or not to re-synchronize
> > the previously synchronized sequences. Do we really want to introduce
> > a new command for 4)? I felt that we can invent an option say
> > copy_all_sequence for the REFRESH PUBLICATION command to cover the 4)
> > case.
> >
>
> Yeah, that is also an option but it could confuse along with copy_data
> option. Say the user has selected copy_data = false but
> copy_all_sequences = true then the first option indicates to *not*
> copy the data of table and sequences and the second option indicates
> to copy the sequences data which sounds contradictory. The other idea
> is to have an option copy_existing_sequences (which indicates to copy
> existing sequence values) but that also has somewhat the same drawback
> as copy_all_sequences but to a lesser degree.

Good point. And I understood that the REFRESH PUBLICATION SEQUENCES
command would be helpful when users want to synchronize sequences
between two nodes before upgrading.

>
> > >
> > > 5) Incorporate the pg_sequence_state function to fetch the sequence
> > > value from the publisher, along with the page LSN. Incorporate
> > > SetSequence function, which will procure a new relfilenode for the
> > > sequence and set the new relfilenode with the specified value. This
> > > will facilitate rollback in case of any failures.
> >
> > Does it mean that we create a new relfilenode for every update of the value?
> >
>
> We need it for initial sync so that if there is an error both the
> sequence state in pg_subscription_rel and sequence values can be
> rolled back together.

Agreed.

> However, it is unclear whether we need to create
> a new relfilenode while copying existing sequences (say during ALTER
> SUBSCRIPTION .. REFRESH PUBLICATION SEQUENCES, or whatever command we
> decide)? Probably the answer lies in how we want to implement this
> command. If we want to copy all sequence values during the command
> itself then it is probably okay but if we want to handover this task
> to the sequence-sync worker then we need some state management and a
> new relfilenode so that on error both state and sequence values are
> rolled back.

What state transition of pg_subscription_rel entries for sequences do
we need while copying sequences values? For example, we insert an
entry with 'init' state at CREATE SUBSCRIPTION and then the
sequence-sync worker updates to 'ready' and copies the sequence data.
And at REFRESH PUBLICATION SEQUENCES, we update the state back to
'init' again so that the sequence-sync worker can process it? Given
REFRESH PUBLICATION SEQUENCES won't be executed very frequently, it
might be acceptable to transactionally update sequence values.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com