From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: now() and statement_timestamp() |
Date: | 2021-05-28 11:15:54 |
Message-ID: | eb09bcc5-4676-a5fc-2d0b-c165baf16264@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Am 27.05.21 um 18:43 schrieb Tom Lane:
> Holger Jakobs <holger(at)jakobs(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikhil Shetty | 2021-05-28 13:27:43 | Upgrading password encryption from md5 to scram-sh-256 |
Previous Message | Yambu | 2021-05-28 10:46:16 | Re: LDAP autos in postgres |