Seems my task could be completely solved if we change Slony's schema adding:

1. a new field sl_log_*.log_timestamp (set to now() by default)
2. a new field sl_event.ev_mintimestamp (the minimum timestamp of included
modifications or, if there are no modifications, a sync creation time)

So we could check if a subscriber has already processed an event generated
strictly after the specified master time.

Of course now I could check up-to-date status relative to a session using
minxid, but the disadvanture of minxid is that it is unpredictable: I have
to perform an additional DB query to fetch the last xid from an origin at
the end of a session and cannot deduce it (in contrary, timestamps could be
deduced approximately enough based on the query start time plus the query
execution time on a client).

On 8/13/07, Dmitry Koterov <dmitry at koterov.ru> wrote:
>
> 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 sess=
ion
> > 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.coll=
aborativefusion.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/=
c4d8049e/attachment-0001.htm