spock同步方式下，如何解决多主序列自增同步问题？

[kk545141]

MySQL有全局配置序列起始值和偏移量的参数，PG不具备，但使用spock方案同步时，每次手动更新的序列起始值都被自动重置成一致的，这就导致所有序列last_value都被更新为一样的，在不同机器上操作时，时常报自增序列冲突。

[danolivo]

I just added here a translation (feel free to correct if Google mistranslated it):

MySQL has global parameters for configuring sequence start values ​​and offsets, while PG does not. However, when using the spock scheme for synchronization, each manually updated sequence start value is automatically reset to be consistent. This causes all sequences to have their last_value updated to the same value, which often results in auto-increment sequence conflicts when operating on different machines.

[danolivo]

Ah, I understand your point: you mean that when using Spock for active-active sync, you get conflicts over the value of a 'serial' column when inserting tuples on different nodes, right?
Multiple manual and automatic methods may be used to avoid this issue. You may ALTER the sequence on each node with a specific start value and an interval to prevent conflicts. But I think you should use the Snowflake extension [1]. After installation of this extension, you may call the convert_sequence_to_snowflake routine, which changes the default algorithm for value generation of your sequence and lets you avoid conflicts altogether.
Is this answer ok for you?

[1] https://github.com/pgEdge/snowflake