From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp() |
Date: | 2021-09-22 00:38:12 |
Message-ID: | a9bda070-a396-4041-7369-d30dc3919e4a@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Bryn Llewellyn | 2021-09-22 00:42:55 | Thanks, David! Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp() |
Previous Message | David G. Johnston | 2021-09-22 00:10:40 | Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp() |