In response to "Dmitry Koterov" <dmitry at koterov.ru>:

> Thanks.
> Please answer one more question - more detalized...
> 
> ("Non-overlapped" - I meant that the first transaction finished strictly
> before the second is started. Because they are performed in a single
> session.)
> 
> > if you are sure that the transactions are committed
> > on the master in a particular order, you can then be sure that those are
> > committed on each of the slaves in the same order
> Great! That means that if I sure that the last transaction of a session is
> successfully processed by a subscriber, I have to be sure that ALL previous
> transactions of this session are also processed already.
> 
> > No.  That's not what that table does.  It simply replicates events to other
> > servers in the cluster, it doesn't guarantee that they've been processed.
> > Have a look at sl_confirm.
> OK, so, let's transform the question with sl_confirm usage:
> 
> BEGIN;
> UPDATE tbl SET c=10 WHERE d=10;
> COMMIT;
> --
> -- some delay (e.g. 0.1s)
> --
> SELECT max(sl_confirm.con_seqno);  -- => save a result to $seqno variable
> 
> The question is: if subscriber's max(sl_confirm.con_seqno) is greater than
> $seqno, could I be 100% sure that the transaction #1 is already processed
> and committed by this subscriber?

I'm not sure I understand your question:

sl_cofirm records times that Slony events are confirmed on each subscriber
node.  As a result, a simple max(sl_confirm.con_seqno) doesn't really tell
you anything rational.

I recommend watching that table for a while to see how things occur, you'll
get a better idea of what's going on:

select * from sl_confirm order by con_seqno desc, con_timestamp desc limit 10;

I assume, from this question, that you're trying to come up with a way to monitor
Slony.  A query like the following would help:

SELECT (now() - max(con_timestamp)) < '15 sec'::interval AS nodes_synced
 FROM sl_confirm
 WHERE con_received = <node you want to monitor>;

Which will return true if the node is within 15 seconds of being synced.  That
would be good for general monitoring.

If you need to be certain that individual transactions have made it, then you're
probably using the wrong replication system.  Might I recommend 2-phase commit.
Otherwise, you best bet is to connect to the slave and query the data to see if
it looks the way you want it.

The problem is that if other transactions are running, I don't know how you're
going to reliably retrieve the Slony event ID that corresponds to your particular
transaction.

> On 8/6/07, Bill Moran <wmoran at collaborativefusion.com> wrote:
> >
> > In response to "Dmitry Koterov" <dmitry at koterov.ru>:
> >
> > > Hello.
> > >
> > > Could you please answer three questions about Slony's transaction
> > > serialization? (I suppose that two first answers will be "yes", but I'd
> > like
> > > to hear the opinions of gurus.) Unfortunately I cannot find direct
> > answers
> > > in the Slony documentation.
> > >
> > > 1. I have the following non-overlapped sequence of transactions in a
> > SINGLE
> > > (!!!) session (connection) on an origin:
> > >
> > > BEGIN;
> > > UPDATE tbl SET a=10 WHERE b=10;
> > > COMMIT;
> > > --
> > > -- some little delay (e.g. 0.1s)
> > > --
> > > BEGIN;
> > > UPDATE tbl SET a=20 WHERE b=20;
> > > COMMIT;
> > >
> > > The question is: if a subscriber received and processed the result of
> > the
> > > transaction #2, could I be sure that it had also received and committed
> > a
> > > result of the transaction #1? Transactions are not overlapped.
> >
> > I'm unsure what you mean by "non-overlapped".  The whole point to a
> > transaction
> > is that it is an atomic operation, so, by design, transactions can't
> > overlap,
> > since they happen within a single atom of time.
> >
> > To answer your question, if you are sure that the transactions are
> > committed
> > on the master in a particular order, you can then be sure that those are
> > committed on each of the slaves in the same order.  Otherwise, Slony
> > wouldn't
> > even work.
> >
> > > 2. I have the following sequence in a SINGLE session (also not
> > overlapped):
> > >
> > > BEGIN;
> > > UPDATE tbl SET c=10 WHERE d=10;
> > > COMMIT;
> > > --
> > > -- some delay (e.g. 0.1s)
> > > --
> > > SELECT nextval('some_seq');  -- => save a result to $some_seq variable
> > >
> > > The question is: if subscriber's currval('some_seq') is greater than
> > > $some_seq, could I be sure that the transaction #1 is also processed and
> > > committed by this subscriber?
> >
> > How is this question different than #1?
> >
> > > 3. I have the following sequence in a SINGLE session (not overlapped):
> > >
> > > BEGIN;
> > > UPDATE tbl SET c=10 WHERE d=10;
> > > COMMIT;
> > > --
> > > -- some delay (e.g. 0.1s)
> > > --
> > > SELECT max(sl_event.ev_seqno);  -- => save a result to $seqno variable
> > >
> > > The question is: if subscriber's max(sl_event.ev_seqno) is greater than
> > > $seqno, could I be 100% sure that the transaction #1 is already
> > processed
> > > and committed by this subscriber?
> >
> > No.  That's not what that table does.  It simply replicates events to
> > other
> > servers in the cluster, it doesn't guarantee that they've been processed.
> > Have a look at sl_confirm.
> >
> > --
> > Bill Moran
> > Collaborative Fusion Inc.
> > http://people.collaborativefusion.com/~wmoran/
> >
> > wmoran at collaborativefusion.com
> > Phone: 412-422-3463x4023
> > _______________________________________________
> > Slony1-general mailing list
> > Slony1-general at lists.slony.info
> > http://lists.slony.info/mailman/listinfo/slony1-general
> >
> 
> 
> 
> 
> 
> 
> 


-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran at collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************