Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

On Tuesday, September 21, 2021, Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> I'm surprised by the results that I describe below. Please help me
> interpret them.
>
> Briefly, why does transaction_timestamp() report a later value
> than statement_timestamp() when they're both invoked in the very first
> statement after "start transaction". (They report identical values in an
> implicitly started txn.)
>
>
>
> * transaction_timestamp() as txn_11, statement_timestamp() as stm_11, *
>
>
>
> * t_finish in timestamptz, t_start in
> timestamptz, expected_diff in double precision) *
>
> *select expected_tstz_diff_error('txn_11 - stm_11 = 0',
> :s1_txn_11, :s1_stm_11, :zero);*
>
> *txn_11 - stm_11 = 0 : error: -0.1 ms*
>

>
>
> * err constant double precision not null := (
> (extract(epoch from t_finish) - extract(epoch from t_start)) -
> expected_diff )*dp_1000; *
>

So, finish is the txn, start is the stm, and expected is 0 - thus if finish
(txn) is earlier than start (stm) your error will be negative. Which it
is, and since transaction should be earlier than statement this would seem
to be correct. In short, you seem to have mis-interpreted the error sign’s
meaning.

David J.