Subject: Understanding DateStyle guc in startup packet

From: Manav Kumar <[email-masked]>
Date: Mon, 19 May 2025 17:00:54 +0530

Manav Kumar <[email-masked]>
Sat, May 17, 6:05 PM (2 days ago)
to pgsql-bugs
Hi Team,
I'm writing to clarify a syntax to pass the guc options in the startup
packt of the connection via JDBC.

Wrote below small java program:

Properties props = new Properties();
props.setProperty("options", "-c DateStyle=Postgres,DMY");
props.setProperty("user", "postgres");
props.setProperty("password", "postgres");
connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", props);

stmt1 = connection.createStatement();
ResultSet rs = stmt1.executeQuery("show DateStyle");
while (rs.next()) {
System.out.println(rs.getString(1));
}
stmt1.execute("reset DateStyle");
rs = stmt1.executeQuery("show DateStyle");
while (rs.next()) {
System.out.println(rs.getString(1));
}

The output I'm getting is:
ISO, DMY
ISO, DMY.

As explained by @Laurenz Albe <[email-masked]>  the driver forces
the value of DateStyle to remain ISO even though the client tries to set a
different value in the startup packet.

Can you please point me in the code where it happens or share briefly how
it has been implemented. I was testing a connection pool with pg and saw
the Postgres,DMY (client provided value) values coming in the startup
packet.

Best,
Manav

---

From: Vladimir Sitnikov <[email-masked]>
Date: Mon, 19 May 2025 15:23:11 +0300

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

---

From: Dave Cramer <[email-masked]>
Date: Mon, 19 May 2025 11:12:10 -0400

> https://github.com/pgjdbc/pgjdbc/issues/131

Why would you want to change it? We keep it a specific way to simplify
parsing it internally. If you want to display it differently than you can
change the output format

> Dave Cramer
> www.postgres.rocks

---

From: Vladimir Sitnikov <[email-masked]>
Date: Mon, 19 May 2025 18:37:24 +0300

>Why would you want to change it?

Applications might have their own view on the way DateStyle connection
property should be set.
It could make a difference if the app uses things like select
'03-05-2025'::date.

Of course, it would be great if the app code could resist from casting text
to dates, however,
I think the driver should stand in the way there by enforcing a very
specific DateStyle value.

To me, DateStyle looks like timezone, and client_encoding which we
currently enforce, yet we could do better and skip enforcing the values.

Vladimir

---

From: Dave Cramer <[email-masked]>
Date: Mon, 19 May 2025 13:25:54 -0400

On Mon, 19 May 2025 at 11:43, Vladimir Sitnikov <[email-masked]>
wrote:

> Of course, it would be great if the app code could resist from casting
> text to dates, however,

If the JDK supports all the variants then yes we could easily accomplish
this.

Dave

---

From: Manav Kumar <[email-masked]>
Date: Fri, 23 May 2025 14:08:07 +0530

cc: Laurenz, pgsql-jdbc.
Hi,
Can you also share how does DRIVER comes to know the value been set for
DateStyle is other than "ISO". and throw an error:
 The server's DateStyle parameter was changed to Postgres, DMY. The JDBC
driver requires DateStyle to begin with ISO for correct operation.

I thought it used to read the PARAMETER STATUS packet that it receives from
the server. But I tried changing it connection pooler i doens't see the
same error. Can you point me to code where this assert check is present.

On Thu, May 22, 2025 at 6:23 PM Manav Kumar <[email-masked]> wrote:

> Hi,
> Can you also share how does DRIVER comes to know the value been set for
> DateStyle is other than "ISO". and throw an error:
>  The server's DateStyle parameter was changed to Postgres, DMY. The JDBC
> driver requires DateStyle to begin with ISO for correct operation.
>
> I thought it used to read the PARAMETER STATUS packet that it receives
> from the server. But I tried changing it connection pooler i doens't see
> the same error. Can you point me to code where this assert check is
> present.
>
> On Mon, May 19, 2025 at 11:31 PM Manav Kumar <[email-masked]> wrote:
>
>> Got it.
>> Let me know if you think my understanding is correct.
>> The way these special GUC's are being set is:
>> "DateStyle" : "ISO"
>> "Client_encoding": "UTF8".
>>
>> And when we pass
>> "options": "DateStyle=Postgres,DMY"
>> I think it takes less precedence in the server than if the direct key as
>> a guc variable is passed as key.
>>
>> This can be verified from below codes i found on server side:
>>
>> https://github.com/postgres/postgres/blob/29f7ce6fe78e3f8d520764b5870453d791a3ca65/src/backend/utils/init/postinit.c#L1262
>> - First cmd line options are processed i.e -options.
>>
>> Followed by
>>
>> https://github.com/postgres/postgres/blob/29f7ce6fe78e3f8d520764b5870453d791a3ca65/src/backend/utils/init/postinit.c#L1294
>> - Followed by GUC options are set/overriden.
>>
>> "DateStyle", "Client_encoding" etc all comes into guc_options as general
>> GUC options.
>> https://github.com/postgres/postgres/blob/29f7ce6fe78e3f8d520764b5870453d791a3ca65/src/backend/tcop/backend_startup.c#L787
>>
>>
>>
>>
>> My next question is: Suppose i comment the code to set the value of
>> "DateStyle" to "ISO" from startup where key is my "DateStyle".
>> The error I get is:
>> org.postgresql.util.PSQLException: The server's DateStyle parameter was
>> changed to Postgres, DMY. The JDBC driver requires DateStyle to begin with
>> ISO for correct operation.
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveParameterStatus(QueryExecutorImpl.java:2887)
>> *Questions *is how does the driver comes to know which value has been
>> set on server, is it due to PARAMETER STATUS which is been returned for the
>> "DateStyle" on getting set and it contains the value as "Postgres,DMY" and
>> driver reads this value to throw an error as it doesn't matches "ISO". Is
>> it correct ?
>>
>>
>> Thanks a lot for giving me all hints and help!
>> Best
>> Manav
>>
>>
>>
>>
>>
>> On Mon, May 19, 2025 at 9:07 PM Vladimir Sitnikov <
>> [email-masked]> wrote:
>>

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 23 May 2025 11:58:51 +0200

On Fri, 2025-05-23 at 14:08 +0530, Manav Kumar wrote:
>  The server's DateStyle parameter was changed to Postgres, DMY. The JDBC driver requires DateStyle to begin with ISO for correct operation. 
> 
> from the server. But I tried changing it connection pooler i doens't
> see the same error. Can you point me to code where this assert check
> is present.  

See the method
org.postgresql.core.v3.QueryExecutorImpl.receiveParameterStatus()
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java#L2886

See the PostgreSQL documentation for context:
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-ASYNC

  ParameterStatus messages will be generated whenever the active value
  changes for any of the parameters the backend believes the frontend
  should know about. Most commonly this occurs in response to a SET SQL
  command executed by the frontend, and this case is effectively
  synchronous — but it is also possible for parameter status changes
  to occur because the administrator changed a configuration file and
  then sent the SIGHUP signal to the server. Also, if a SET command is
  rolled back, an appropriate ParameterStatus message will be generated
  to report the current effective value.

Yours,
Laurenz Albe