Understanding Sequence replication process

[footcow]

Hi,

I'm using :

• pglogical | 1.2.1
• PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
• OS Debian Jessy

On Master I create a sample table with serial (sequence) : create table herve_wseq (id serial primary key, val text);

Do the same on the Slave with same command : create table herve_wseq (id serial primary key, val text);

Then add it to the replication from the Master:

SELECT pglogical.replication_set_add_table(
  set_name := 'default',
    relation := 'herve_wseq',
      synchronize_data := TRUE
      );

Then add data like this on Master : insert into herve_wseq (val) values ('test'); insert into herve_wseq (val) values ('test2'); etc.

On Master I have now :

master=# select * from herve_wseq;
 id |  val
----+-------
  1 | test
  2 | test2
  3 | test3
  4 | test4
  5 | test5
(5 rows)

On Slave I have :

pglogical=# select * from herve_wseq;
 id |  val
----+-------
  1 | test
  2 | test2
  3 | test3
  4 | test4
  5 | test5
(5 rows)

Until this all is perfect, replication is working, but if I looking at the sequence itself of this table.

Master :

master=# select * from herve_wseq_id_seq;
   sequence_name   | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 herve_wseq_id_seq |          5 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)

Slave :

pglogical=# select * from herve_wseq_id_seq;
   sequence_name   | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 herve_wseq_id_seq |       1004 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t
(1 row) 

So why the Slave version of the sequence last value is 1004 and not 5 ? Did I missed something in the way I have processed ? How Can I solve this situation ... I can't have the sequence on the Slave with a different value than my Master.

Thanks per advance for your answer. Hervé

[PJMODOS]

This is not bug, it's by design, the subscriber is in-front of the provider in terms of sequences, how much in-front is dynamic depending on how fast the sequence is consumed, it just tries to keep "safe" buffer.

[ringerc]

On 24 October 2016 at 18:13, Piedvache Hervé notifications@github.com wrote:

So why the Slave version of the sequence last value is 1004 and not 5 ?

Because we cannot decode sequence advances, we have to send sequence updates in advance to leave a buffer to make sure the replica is always ahead of the master.

If we can get sequence value decoding into PostgreSQL 10 that might change, but it's quite complex and neither Petr nor I currently have the time budget for it so it's likely we'll need to keep using the current strategy on PostgreSQL 10 too.

Did I missed something in the way I have processed ?

Nope, it's working as designed.

How Can I solve this situation ... I can't have the sequence on the Slave with a different value than my Master.

It's working as designed. There's nothing to solve.

You can have your replica sequences ahead of the master. If you can't then you application is buggy and needs to be fixed, because it's normal for sequences to have gaps and jumps in ordinary single-master standalone PostgreSQL operation too. For example, if PostgreSQL does a crash restart it'll jump the sequence ahead then.

Your app shouldn't even assume that sequence value follows in time order. That's not really guaranteed either, because two inserts might get sequences in one order then commit in another order.

Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services