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

On 9/21/21 4:23 PM, Bryn Llewellyn 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.)

I'm not seeing it:

test(5432)=# begin;

select
   transaction_timestamp() as txn_11,
   statement_timestamp()   as stm_11,
   clock_timestamp()       as clk_11,
   pg_sleep(0.5)        as dummy,
   transaction_timestamp() as txn_12,
   statement_timestamp()   as stm_12,
   clock_timestamp()       as clk_12;

select pg_sleep(1);

select
   transaction_timestamp() as txn_2,
   statement_timestamp()   as stm_2,
   clock_timestamp()       as clk_2;

commit;

BEGIN
-[ RECORD 1 ]-------------------------
txn_11 | 2021-09-21 17:29:18.272042-07
stm_11 | 2021-09-21 17:29:18.2722-07
clk_11 | 2021-09-21 17:29:18.272297-07
dummy  |
txn_12 | 2021-09-21 17:29:18.272042-07
stm_12 | 2021-09-21 17:29:18.2722-07
clk_12 | 2021-09-21 17:29:18.772863-07

-[ RECORD 1 ]
pg_sleep |

-[ RECORD 1 ]------------------------
txn_2 | 2021-09-21 17:29:18.272042-07
stm_2 | 2021-09-21 17:29:19.774436-07
clk_2 | 2021-09-21 17:29:19.77456-07


No matter how many times I run this the transaction_timestamp() is less 
then statement_timestamp().  This is as it should be, as 
transaction_timestamp() fires with the begin; and statement_timestamp() 
fires at select ... and then updates with the subsequent select .... and 
clock_timestamp() just keeps marching on through the whole process.

-- 
Adrian Klaver
adrian.klaver@aklaver.com