[Bucardo-general] Replicatiing sequences

[David Christensen]

> \On Jan 2, 2020, at 11:23 AM, Jeff Ross <rossj at cargotel.com> wrote:
> 
> On 2019-12-24 16:09, Jeff Ross wrote:
>> According to the latest docs for bucardo 5.5.0, sequences will be replicated if bucardo knows about them.
>> 
>> When I set up our master <-> master replication I added all the sequences with
>> 
>>     bucardo add all sequences db=cargotel_dev
>> 
>> Then I specifically added the sequences that are used by the tables in the load relgroup with
>> 
>> bucardo add sequence \
>>     load_id_seq load_det_id_seq loadacct_batch_id_seq client_profile_id_seq users_id_seq quote_id_seq \
>>     ref_load_status_pg_id_seq trailer_id_seq truck_id_seq lang_id_seq \
>>     ref_quote_status_id_seq ref_quote_type_id_seq driver_id_seq ref_employment_type_id_seq \
>>     ref_client_status_pg_id_seq client_flags_id_seq carrier_rating_id_seq railcar_id_seq \
>>     load_flags_id_seq load_flags2_id_seq link_load_det_id_seq load_events_id_seq \
>>     load_log_pg_id_seq load_log2_pg_id_seq load_cmt_id_seq client_events_id_seq \
>>     insp_flags_id_seq insp_gm_data_id_seq insp_gm_areaid_seq insp_gm_severity_id_seq \
>>     insp_gm_what_id_seq insp_gm_where_id_seq ref_load_flags2_id_seq ref_load_date_id_seq load_date_id_seq \
>> relgroup=load db=cargotel_dev
>> 
>> Sequences are not replicating though----the secondary database sequences are not updated when a row is inserted on the primary side, so when a row is inserted from the secondary side it fails with a duplicate key error as it attempts to insert a row with an id that has already been used.
>> 
>> Digging in a little I found the bucardo_sequences table in the bucardo schema of the primary database that looks like it should be related to replicating sequences but even after adding the sequences with the commands above it is empty.
>> 
>> If bucardo really isn't able to replicate sequences I can write a trigger to run on both sides that would reset the sequence with the max(id) of the table but I'd rather let bucardo handle replicating the sequences.
>> 
>> Thanks!
>> 
>> Jeff
>> 
> 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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20200102/ce6f1872/attachment.sig>