Re: [HACKERS] Slony-I goes BETA (possible bug) - Mailing list pgsql-advocacy

On 6/6/2004 5:21 AM, Jeff Davis wrote:
> I have two nodes, node 1 and node 2.
>
> Both are working with node 1 as the master, and data from subscribed
> tables is being properly replicated to node 2.
>
> However, it looks like there's a possible bug with sequences. First let
> me explain that I don't entirely understand how a replicated sequence is
> expected to behave, but as far as this report is concerned, I assume
> that if you do a nextval() on node 1, than "SELECT last_value FROM
> test_seq" on node 2 will return the updated value.
>
> It looks like the sequence value is not updated on node 2, until some
> other event happens, like doing an UPDATE on a replicated table on node
> 1.

You are right. The "local" slon node checks every "-s" milliseconds
(commandline switch) if the sequence sl_action_seq has changed, and if
so generate a SYNC event. Bumping a sequence alone does not cause this,
only operations that invoke the log trigger on replicated tables do.

Speaking of this, this would also mean that there is a gap between the
last sl_action_seq bumping operation and the commit of that transaction.
If the local slon will generate the sync right in that gap, the changes
done in that transaction will not be replicated until the next
transaction triggers another sync.

I am not sure how to effectively avoid this problem without blindly
creating SYNC events in a maybe less frequent interval. Suggestions?


Jan

>
> I already have a table "t2" which is properly replicating.
>
> So, here's what I give to slonik to add the sequence to set 1:
> slonik <<_EOF_
> cluster name = $CLUSTER;
>
> node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
> node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';
>
> create set (id=34, origin=1, comment='set 34');
> set add sequence (set id = 34, origin = 1, id = 35, full qualified
> name='public.test_seq', comment = 'sequence test');
>
> subscribe set (id=34,provider=1,receiver=2,forward=no);
>
> merge set (id=1,add id = 34, origin=1);
>
> subscribe set (id=1,provider=1,receiver=2,forward=no);
> _EOF_
>
> Note: results of the query are put after the "--" following the query
> for easier readability.
>
> node1=> SELECT last_value FROM test_seq; -- 1
> node2=> SELECT last_value FROM test_seq; -- 1
> node1=> SELECT nextval('test_seq'); -- 1
> node1=> SELECT nextval('test_seq'); -- 2
> node1=> SELECT nextval('test_seq'); -- 3
> node1=> SELECT last_value FROM test_seq; -- 3
> node2=> SELECT last_value FROM test_seq; -- 1
> node2=> -- wait for a long time, still doesn't update
> node2=> SELECT last_value FROM test_seq; -- 1
> node1=> INSERT INTO t2(a) VALUES('string');
> node2=> SELECT last_value FROM test_seq; -- 3
> node2=> -- now it's updated!
>
> So, that looks like a possible bug where a nextval() call doesn't
> trigger the replication. But it does appear to replicate after an
> unrelated event triggers the replication (in this case an update to t2,
> an unrelated table).
>
> If not, what is the expected behavior of replicated sequences anyway? It
> seems you couldn't call nextval() from a slave node, and because of that
> you also can't make use of currval(). It looks like the slaves can
> really only get the "SELECT last_value FROM test_seq". So is there a
> particular use case someone had in mind when implementing the "SET ADD
> SEQUENCE" for slonik?
>
> Regards,
>     Jeff Davis


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #