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 FR=
OM
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/
> > >
> > > 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.
> ****************************************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070807/=
d06aa30a/attachment-0001.htm