Re: Understanding JDBC Behaviour

On Sat, 2025-05-17 at 18:05 +0530, Manav Kumar wrote:
> 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.
>
> Rather shouldn't the expected output should be 
> Postgres, DMY
> Postgres, DMY //Please correct me - Even after the reset the default value should be Postgres, DMY ?
>  I checked the official docs, and found the syntax above. 
> Looking forward to seeing your response! Please let me know if I should post elsewhere.

The correct list would have been pgsql-jdbc, but I'll answer here.

There is no bug involved.  The behavior can be explained as follows:

1. The PostgreSQL JDBC driver forces "DateStyle" to "ISO", because it needs that setting
   to handle dates and timestamps correctly.  If you manage to change the setting, the
   driver will fail with the error message:

      The server''s DateStyle parameter was changed to ....
      The JDBC driver requires DateStyle to beegin with ISO for correct operation.

2. RESET will reset the parameter to what it was when the session started.

Yours,
Laurenz Albe