On Wed, Jun 12, 2024 at 6:59 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Wed, Jun 12, 2024 at 10:44 AM Masahiko Sawada <sawada.m...@gmail.com> > wrote: > > > > On Tue, Jun 11, 2024 at 7:36 PM vignesh C <vignes...@gmail.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