public inbox for [email protected]
help / color / mirror / Atom feedUnderstanding DateStyle guc in startup packet
7+ messages / 4 participants
[nested] [flat]
* Understanding DateStyle guc in startup packet
@ 2025-05-19 11:30 Manav Kumar <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Manav Kumar @ 2025-05-19 11:30 UTC (permalink / raw)
To: [email protected]; Laurenz Albe <[email protected]>
Manav Kumar <[email protected]>
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 protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-19 12:23 Vladimir Sitnikov <[email protected]>
parent: Manav Kumar <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Vladimir Sitnikov @ 2025-05-19 12:23 UTC (permalink / raw)
To: Manav Kumar <[email protected]>; +Cc: [email protected]; Laurenz Albe <[email protected]>
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/...
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-19 15:12 Dave Cramer <[email protected]>
parent: Vladimir Sitnikov <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2025-05-19 15:12 UTC (permalink / raw)
To: Vladimir Sitnikov <[email protected]>; +Cc: Manav Kumar <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
On Mon, 19 May 2025 at 08:23, Vladimir Sitnikov <[email protected]>
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.
>
> I guess here's the line that configures DateStyle ISO:
> https://github.com/pgjdbc/pgjdbc/blob/d9e20874590f59543c39a99b824e09344f00a813/pgjdbc/src/main/java/...
>
> 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-19 15:37 Vladimir Sitnikov <[email protected]>
parent: Dave Cramer <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Vladimir Sitnikov @ 2025-05-19 15:37 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Manav Kumar <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
>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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-19 17:25 Dave Cramer <[email protected]>
parent: Vladimir Sitnikov <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Dave Cramer @ 2025-05-19 17:25 UTC (permalink / raw)
To: Vladimir Sitnikov <[email protected]>; +Cc: Manav Kumar <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
On Mon, 19 May 2025 at 11:43, Vladimir Sitnikov <[email protected]>
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.
>
If the JDK supports all the variants then yes we could easily accomplish
this.
Dave
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-23 08:38 Manav Kumar <[email protected]>
parent: Vladimir Sitnikov <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Manav Kumar @ 2025-05-23 08:38 UTC (permalink / raw)
To: Vladimir Sitnikov <[email protected]>; +Cc: [email protected]; Laurenz Albe <[email protected]>
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 protected]> 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 protected]> wrote:
>
>> Got it.
>> Let me know if you think my understanding is correct.
>> The way these special GUC's are being set is:
>> https://github.com/pgjdbc/pgjdbc/blob/d9e20874590f59543c39a99b824e09344f00a813/pgjdbc/src/main/java/...
>>
>> "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...
>> - First cmd line options are processed i.e -options.
>>
>> Followed by
>>
>> https://github.com/postgres/postgres/blob/29f7ce6fe78e3f8d520764b5870453d791a3ca65/src/backend/utils...
>> - 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/...
>>
>>
>>
>>
>> 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 protected]> 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
>>>
>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Understanding DateStyle guc in startup packet
@ 2025-05-23 09:58 Laurenz Albe <[email protected]>
parent: Manav Kumar <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2025-05-23 09:58 UTC (permalink / raw)
To: Manav Kumar <[email protected]>; Vladimir Sitnikov <[email protected]>; +Cc: [email protected]
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 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.
See the method
org.postgresql.core.v3.QueryExecutorImpl.receiveParameterStatus()
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecut...
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-05-23 09:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-19 11:30 Understanding DateStyle guc in startup packet Manav Kumar <[email protected]>
2025-05-19 12:23 ` Vladimir Sitnikov <[email protected]>
2025-05-19 15:12 ` Dave Cramer <[email protected]>
2025-05-19 15:37 ` Vladimir Sitnikov <[email protected]>
2025-05-19 17:25 ` Dave Cramer <[email protected]>
2025-05-23 08:38 ` Manav Kumar <[email protected]>
2025-05-23 09:58 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox