To: pgsql-admin@lists.postgresql.org
References: <536812b3-8040-371d-506d-266bcf10ec2f@jakobs.com>
 <1785725.1622133836@sss.pgh.pa.us>
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: now() and statement_timestamp()
Message-ID: <eb09bcc5-4676-a5fc-2d0b-c165baf16264@jakobs.com>
Date: Fri, 28 May 2021 13:15:54 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: <1785725.1622133836@sss.pgh.pa.us>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="KmGyokB7Lb6vpyz4jUgU12YlrIzGx3Lnw"
Archived-At: 
 <https://www.postgresql.org/message-id/eb09bcc5-4676-a5fc-2d0b-c165baf16264%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--KmGyokB7Lb6vpyz4jUgU12YlrIzGx3Lnw
Content-Type: multipart/mixed; boundary="RRahnLEMWXXhkhMS5bObepnvHuzo6gRGT";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <eb09bcc5-4676-a5fc-2d0b-c165baf16264@jakobs.com>
Subject: Re: now() and statement_timestamp()
References: <536812b3-8040-371d-506d-266bcf10ec2f@jakobs.com>
 <1785725.1622133836@sss.pgh.pa.us>
In-Reply-To: <1785725.1622133836@sss.pgh.pa.us>

--RRahnLEMWXXhkhMS5bObepnvHuzo6gRGT
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: de-DE

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 =3D 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,=20
bind, execute are approximately equivalent (Note right above "Function=20
Call" in=20
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=20
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=20
simple query in order to find out whether a session is within a=20
transaction or in autocommit mode. Maybe the Note in the documentation=20
should mention this clearly.


--=20

Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--RRahnLEMWXXhkhMS5bObepnvHuzo6gRGT--

--KmGyokB7Lb6vpyz4jUgU12YlrIzGx3Lnw
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYLDQ6gUDAAAAAAAKCRCKV5yTsxlhumds
AJ96V5RUsYZW9gVH2yl/3dknmlBjwgCgwYHS74JwW93YE48kTOjVCeXSeWQ=
=987m
-----END PGP SIGNATURE-----

--KmGyokB7Lb6vpyz4jUgU12YlrIzGx3Lnw--