Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Understanding DateStyle guc in startup packet

Re: Understanding DateStyle guc in startup packet

From
Dave Cramer
Date:


On Mon, 19 May 2025 at 08:23, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
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.


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

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

Re: Understanding DateStyle guc in startup packet

From
Vladimir Sitnikov
Date:
>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

Re: Understanding DateStyle guc in startup packet

From
Manav Kumar
Date:
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 <mkumar@yugabyte.com> 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 <mkumar@yugabyte.com> wrote:
Got it.
Let me know if you think my understanding is correct. 
"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:

Followed by

"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 <sitnikov.vladimir@gmail.com> wrote:
>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

Re: Understanding DateStyle guc in startup packet

From
Laurenz Albe
Date:
On Fri, 2025-05-23 at 14:08 +0530, Manav Kumar wrote:
> 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
forcorrect 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.  

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