Hmm.
Seems sl_confirm is not usable in this case, because:
1. its timestamp field holds a SUBSCRIBER time, not the master time
2. slony source code shows that insertions in sl_event and sl_confirm are
done in a single transaction, so, if something appeared in sl_confirm on a
subscriber, it is also appeared in sl_event.


On 8/7/07, Dmitry Koterov <dmitry at koterov.ru> wrote:
>
> Thanks for your answers!
>
> No, it is not for monitoring purpose, it is for optimal checking if all of
> the session's transactions are processed by a subscriber or not yet.
> (Insignificant false negatives are alowed here.) I do not need an
> information about the whole node status, but - only about the status of a
> single session executed some time ago. If all transactions of this session
> were processed by a subscriber or not yet - here is the main question.
>
> 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.
> >
>
> Oh, sorry! Of course, I meant the following question:
>
> -------------
> BEGIN;
> UPDATE tbl SET c=3D10 WHERE d=3D10;
> COMMIT;
> --
> -- some delay (e.g. 0.1s)
> --
> SELECT max(sl_event.ev_seqno);  -- =3D> save a result to $seqno variable
> FROM SL_EVENT!
> The question is: if subscriber's max(sl_confirm.con_seqno) (check
> SL_CONFIRM here!) is greater than $seqno, could I be 100% sure that the
> transaction #1 is already processed and committed by this subscriber?
> -------------
>
> So, we fetch & save the seqno from the origin sl_event, and compare it -
> with confirmed items in sl_confirm on a subscriber.
>
>
>
> > 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 =3D <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=3D10 WHERE b=3D10;
> > > > > COMMIT;
> > > > > --
> > > > > -- some little delay (e.g. 0.1s)
> > > > > --
> > > > > BEGIN;
> > > > > UPDATE tbl SET a=3D20 WHERE b=3D20;
> > > > > 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=3D10 WHERE d=3D10;
> > > > > COMMIT;
> > > > > --
> > > > > -- some delay (e.g. 0.1s)
> > > > > --
> > > > > SELECT nextval('some_seq');  -- =3D> 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=3D10 WHERE d=3D10;
> > > > > COMMIT;
> > > > > --
> > > > > -- some delay (e.g. 0.1s)
> > > > > --
> > > > > SELECT max(sl_event.ev_seqno);  -- =3D> 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/<http://people.collab=
orativefusion.com/%7Ewmoran/>
> > > >
> > > > 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/
> > <http://people.collaborativefusion.com/%7Ewmoran/>
> >
> > 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.
> > ****************************************************************
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070813/=
abd369c8/attachment.htm