<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
On 2019-12-24 16:09, Jeff Ross wrote:<br>
<blockquote type="cite"
cite="mid:3e7faf45-4c3c-b4f9-128f-3251f9b94c81@cargotel.com">According
to the latest docs for bucardo 5.5.0, sequences will be replicated
if bucardo knows about them.
<br>
<br>
When I set up our master <-> master replication I added all
the sequences with
<br>
<br>
bucardo add all sequences db=cargotel_dev
<br>
<br>
Then I specifically added the sequences that are used by the
tables in the load relgroup with
<br>
<br>
bucardo add sequence \
<br>
load_id_seq load_det_id_seq loadacct_batch_id_seq
client_profile_id_seq users_id_seq quote_id_seq \
<br>
ref_load_status_pg_id_seq trailer_id_seq truck_id_seq
lang_id_seq \
<br>
ref_quote_status_id_seq ref_quote_type_id_seq driver_id_seq
ref_employment_type_id_seq \
<br>
ref_client_status_pg_id_seq client_flags_id_seq
carrier_rating_id_seq railcar_id_seq \
<br>
load_flags_id_seq load_flags2_id_seq link_load_det_id_seq
load_events_id_seq \
<br>
load_log_pg_id_seq load_log2_pg_id_seq load_cmt_id_seq
client_events_id_seq \
<br>
insp_flags_id_seq insp_gm_data_id_seq insp_gm_areaid_seq
insp_gm_severity_id_seq \
<br>
insp_gm_what_id_seq insp_gm_where_id_seq
ref_load_flags2_id_seq ref_load_date_id_seq load_date_id_seq \
<br>
relgroup=load db=cargotel_dev
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
Thanks!
<br>
<br>
Jeff
<br>
<br>
</blockquote>
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.<br>
<br>
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:<br>
<br>
<font face="Courier New, Courier, monospace">CREATE OR REPLACE
FUNCTION cargotel_common.set_sequence () RETURNS trigger AS $$<br>
current_user = plpy.execute("select
current_user")[0]["current_user"]<br>
if current_user <> "bucardo":<br>
return None <br>
if TD["event"] == "DELETE" and TD["when"] == "BEFORE": <br>
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"]))<br>
if result:<br>
["col"],TD["table_schema"],TD["table_name"]))<br>
plpy.execute("select setval('%s',(select max(%s) + 1
from %s.%s))" %
(result[0]["seq"],result[0]["col"],TD["table_schema"],TD["table_name"]))<br>
return None<br>
$$ LANGUAGE plpythonu;<br>
<br>
</font><br>
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.<br>
<br>
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.<br>
<br>
Jeff<br>
<br>
<br>
</body>
</html>
<br>
<pre style="white-space:pre-wrap;color:rgb(34,34,34);background-color:rgb(255,255,255)">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.</pre>