MIME-Version: 1.0
References: 
 <CAPhCW+-oyivUiBiP5nOMQNxQzXLnT62SvCZ1oq7rqZkHq34KGA@mail.gmail.com>
In-Reply-To: 
 <CAPhCW+-oyivUiBiP5nOMQNxQzXLnT62SvCZ1oq7rqZkHq34KGA@mail.gmail.com>
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Date: Mon, 19 May 2025 15:23:11 +0300
Message-ID: 
 <CAB=Je-Gznb3j_M--EAn9zW0cHLzgMg_YyWxWGM==g_kRfxEzrA@mail.gmail.com>
Subject: Re: Understanding DateStyle guc in startup packet
To: Manav Kumar <mkumar@yugabyte.com>
Cc: pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/alternative; boundary="000000000000b5fdb006357c3236"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB%3DJe-Gznb3j_M--EAn9zW0cHLzgMg_YyWxWGM%3D%3Dg_kRfxEzrA%40mail.gmail.com>
Precedence: bulk

--000000000000b5fdb006357c3236
Content-Type: text/plain; charset="UTF-8"

Long story short: it might be nice to decouple pgjdbc from requiring
DateStyle=ISO, however, it does not look like a walk in the park to me.

I guess here's the line that configures DateStyle ISO:
https://github.com/pgjdbc/pgjdbc/blob/d9e20874590f59543c39a99b824e09344f00a813/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java#L409

It looks like options come after DateStyle.

At the same time, for some reason related to COPY processing, the driver
asserts DateStyle must start with ISO:
https://github.com/pgjdbc/pgjdbc/issues/131

---

For historical reasons, pgjdbc often sends timestamps and dates as
text-encoded literals, so it needs the backend to recognize the value
properly.
The reason is that Java's `setTimestamp()` does not distinguish between
timestamp and timestamptz, so the driver can't use Oid for
timestamp/timestamptz,
so it falls back to text encoding with Oid "unknown".

I have not explored if the server would parse the timestamps appropriately.

---

At the same time, DateStyle might affect text representation of the
timestamps, and the driver is not prepared to parse various flavours of
timestamp representation.
A way out might be to make sure pgjdbc always requires binary encoding when
receiving timestamp/timestamptz/date.
However, it might be trickier when processing arrays or structs as
requiring all the arrays and structs to be in binary would take a bit of
time as well.

Vladimir

--000000000000b5fdb006357c3236
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Long story short: it might be nice to decouple pgjdbc=
 from requiring DateStyle=3DISO, however, it does not look like a walk in t=
he park to me.</div><div><br></div><div>I guess here&#39;s the line that co=
nfigures DateStyle ISO:=C2=A0<a href=3D"https://github.com/pgjdbc/pgjdbc/bl=
ob/d9e20874590f59543c39a99b824e09344f00a813/pgjdbc/src/main/java/org/postgr=
esql/core/v3/ConnectionFactoryImpl.java#L409">https://github.com/pgjdbc/pgj=
dbc/blob/d9e20874590f59543c39a99b824e09344f00a813/pgjdbc/src/main/java/org/=
postgresql/core/v3/ConnectionFactoryImpl.java#L409</a></div><div><br></div>=
<div>It looks like options come=C2=A0after DateStyle.</div><div><br></div><=
div>At the same time, for some reason related to COPY processing, the drive=
r asserts DateStyle must start with ISO:=C2=A0<a href=3D"https://github.com=
/pgjdbc/pgjdbc/issues/131">https://github.com/pgjdbc/pgjdbc/issues/131</a><=
/div><div><br></div><div>---</div><div><br></div><div>For historical reason=
s, pgjdbc often sends timestamps and dates as text-encoded literals, so it =
needs the backend to recognize the value properly.</div><div>The reason is =
that Java&#39;s `setTimestamp()` does not distinguish between timestamp and=
 timestamptz, so the driver can&#39;t use Oid for timestamp/timestamptz,</d=
iv><div>so it falls back to text encoding with Oid &quot;unknown&quot;.</di=
v><div><br></div><div>I have not explored if the server would parse the tim=
estamps appropriately.</div><div><br></div><div>---</div><div><br></div><di=
v>At the same time, DateStyle might affect text representation of the times=
tamps, and the driver is not prepared to parse various flavours of timestam=
p representation.</div><div>A way out might be to make sure pgjdbc always r=
equires binary encoding when receiving timestamp/timestamptz/date.</div><di=
v>However, it might be trickier when processing arrays or structs as requir=
ing all the arrays and structs to be in binary would take a bit of time as =
well.</div><div><br></div><div><div dir=3D"ltr" class=3D"gmail_signature" d=
ata-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div>Vladimir</div></div=
></div></div><br></div>

--000000000000b5fdb006357c3236--