[Bucardo-general] Replicatiing sequences

[Jeff Ross]

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 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20200102/7873beb0/attachment.htm>