Обсуждение: Understanding DateStyle guc in startup packet

Поиск
Список
Период
Сортировка

Understanding DateStyle guc in startup packet

От
Manav Kumar
Дата:

Manav Kumar mkumar@yugabyte.com

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  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

Re: Understanding DateStyle guc in startup packet

От
Vladimir Sitnikov
Дата:
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

---

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