pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feednow() and statement_timestamp()
3+ messages / 2 participants
[nested] [flat]
* now() and statement_timestamp()
@ 2021-05-27 16:26 Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Holger Jakobs @ 2021-05-27 16:26 UTC (permalink / raw)
To: pgsql-admin
According to the documentation. now() should yield the timestamp of the
current transaction, while statement_timestamp() should yield the
timestamp of the current statement.
In case of Autocommit, both should be identical, in case of a running
transaction, they should be different.
Funny enough, psql and executing the same statement from within an
application yield different results. I tried a Tcl script and DBeaver.
Statement: |SELECT now(), statement_timestamp();|
psql: |2021-05-07 13:31:10.135851+02 | 2021-05-07 13:31:10.135851+02|
DBeaver: |2021-05-07 13:31:00.633784+02|2021-05-07 13:31:00.633913+02|
Tcl script: |{now {2021-05-07 13:24:54.659837+02} statement_timestamp
{2021-05-07 13:24:54.65985+02}}|
So in psql now() and statement_timestamp() are identical when not in a
transaction, while in the others the two values vary a little bit,
roughly 100 microseconds.
How come the same statements aren't behaving identical. I'm using a
current PostgreSQL server on a Linux machine.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: now() and statement_timestamp()
@ 2021-05-27 16:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-05-27 16:43 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin
Holger Jakobs <holger@jakobs.com> writes:
> So in psql now() and statement_timestamp() are identical when not in a
> transaction, while in the others the two values vary a little bit,
> roughly 100 microseconds.
> How come the same statements aren't behaving identical. I'm using a
> current PostgreSQL server on a Linux machine.
I'd try turning on log_statement = all to see what's actually
happening. What seems somewhat likely is that the non-psql
cases are wrapping the command you gave in BEGIN/COMMIT,
or something along that line.
[ thinks for a bit ] It could also be down to the wire protocol
used. psql is just sending a simple Query message, but the
other two might well be sending Parse/Bind/Execute. IIRC the
transaction must be started by Bind, but we may consider
the statement timestamp to be the start of processing Execute.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: now() and statement_timestamp()
@ 2021-05-28 11:15 Holger Jakobs <holger@jakobs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Holger Jakobs @ 2021-05-28 11:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 27.05.21 um 18:43 schrieb Tom Lane:
> Holger Jakobs <holger@jakobs.com> writes:
>> So in psql now() and statement_timestamp() are identical when not in a
>> transaction, while in the others the two values vary a little bit,
>> roughly 100 microseconds.
>> How come the same statements aren't behaving identical. I'm using a
>> current PostgreSQL server on a Linux machine.
> I'd try turning on log_statement = all to see what's actually
> happening. What seems somewhat likely is that the non-psql
> cases are wrapping the command you gave in BEGIN/COMMIT,
> or something along that line.
>
> [ thinks for a bit ] It could also be down to the wire protocol
> used. psql is just sending a simple Query message, but the
> other two might well be sending Parse/Bind/Execute. IIRC the
> transaction must be started by Bind, but we may consider
> the statement timestamp to be the start of processing Execute.
>
> regards, tom lane
I turned on logging of statements, and found out that psql uses a simple
query, while the others us an extended query.
According to the documentation a simple query and a series of parse,
bind, execute are approximately equivalent (Note right above "Function
Call" in
https://www.postgresql.org/docs/13/protocol-flow.html#id-1.10.5.7.6)
So actually the "approximately equivalent" is the point here. The series
of parse, bind, execute doesn't call the two funtions now() and
statement_timestamp() at the same stage, so the timestamps vary.
I wasn't aware of this effect. Now it's clear that one can only use a
simple query in order to find out whether a session is within a
transaction or in autocommit mode. Maybe the Note in the documentation
should mention this clearly.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-05-28 11:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-27 16:26 now() and statement_timestamp() Holger Jakobs <holger@jakobs.com>
2021-05-27 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-28 11:15 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox