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
