[Bucardo-general] Replicatiing sequences

[Jeff Ross]

On 2020-01-02 10:48, David Christensen wrote:
>> Just in case this message got lost in the holidays, here's a follow-up.  I've proven to myself with repeated tests that the sequences really are not being replicated.  Searches turn up several places in stackexchange where it says that sequences are not replicated, but all of those are many years--and many bucardo versions--old.
>>
>> Watching the logs I've seen that before bucardo copies a new row across to the other side of the replica, it deletes any rows with matching primary keys.   I wrote the following function that I call with a BEFORE DELETE trigger:
>>
>> CREATE OR REPLACE FUNCTION cargotel_common.set_sequence () RETURNS trigger AS $$
>>      current_user = plpy.execute("select current_user")[0]["current_user"]
>>      if current_user <> "bucardo":
>>          return None
>>      if TD["event"] == "DELETE" and TD["when"] == "BEFORE":
>>          result = plpy.execute("select column_name as col,split_part(column_default,'''',2) as seq from information_schema.columns where column_default ilike '%%nextval%%' and table_schema = '%s' and table_name = '%s' order by ordinal_position limit 1" % (TD["table_schema"],TD["table_name"]))
>>          if result:
>>              ["col"],TD["table_schema"],TD["table_name"]))
>>              plpy.execute("select setval('%s',(select max(%s) + 1 from %s.%s))" % (result[0]["seq"],result[0]["col"],TD["table_schema"],TD["table_name"]))
>>      return None
>> $$ LANGUAGE plpythonu;
>>
>>
>> That works just fine--except that in practice it can be many minutes before bucardo tries to do that copy across to the replica. In that case, the trigger fires far too late to be of any practical use in a production server.
>>
>> The most often cited method to keeping sequences in sync is to only insert to one side of the pair.  I think that sort of defeats the purpose of bucardo as a multi-master setup though and still hope that I've just done some sort of mis-configuration.
>>
>> Jeff
> The canonical way to handle sequences for multi-master is to stagger the sequences on each side, so Source A gets even values and Source B gets odd values (staggered out more if you have more masters involved).  In practice, sequence synchronization is not really a good idea, particularly if there is any sort of replication delay (as you’ve seen) since you can end up with artificial conflicts, particularly if you’re inserting into multiple locations.
>
> I’m not sure if replicating sequences was intentionally deprecated with Bucardo 5, or if it just broke and no-one was using it until now, but maybe we need to be more vocal if that was an intentional choice as well as point to the preferred method of handling.
>
> Best,
>
> David
> --
> David Christensen
> Senior Software and Database Engineer
> End Point Corporation
> david at endpoint.com
> 785-727-1171
>
Thank you David!  The staggered sequences idea makes good sense and 
won't be hard to implement.

Jeff

-- 
The contents of this e-mail and any attachments are intended solely for the 
use of the named addressee(s) and may contain confidential and/or 
privileged information. Any unauthorized use, copying, disclosure, or 
distribution of the contents of this e-mail is strictly prohibited by the 
sender and may be unlawful. If you are not the intended recipient, please 
notify the sender immediately and delete this e-mail.