Postgres explain plan is not correctly optimezed if the data is a "double" but the collumn is a "numeric" about pgjdbc

[davecramer]

but why does not jdbcPG do something similar ?

I mean, when we create the PreparedStatement, all the ? types are defined in the ParameterMetaData. Moreover the data that is uses in the BigDecimal is BigDecimal#getValue() returning a double. so :

• The bytes array is the same between a double and a BigDecimal
• We have the info of the parameter type in the metadata

what are the limitation to not do so ?

Because it requires a query to do so.

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java

Line 1757 in e06b401

public ParameterMetaData getParameterMetaData() throws SQLException {

[davecramer]

If I remember correctly 06abfb7 only effects simple query mode which if you are using PreparedStatements this will not be the case.

[vlsi]

Unfortunately, that is the way PostgreSQL works.
If you have a numeric column, you need to use setBigDecimal for it. If you use setDouble + numeric column, you will have a performance issue.

There's no way driver could do about it, and if it worked previously, it was a pure luck 🤷‍♂️

[CharlesLgn]

Are you requesting SimpleQuery mode when executing this ?

In fact I am not. so as I understand, and as @vlsi said in the issue post :

There's no way driver could do about it, and if it worked previously, it was a pure luck 🤷‍♂️

Sorry to have created a PR for no reason 😓

[davecramer]

Just for your elucidation if you are not requesting SimpleQuery mode then we would be using Extended Protocol and the code you were referring to would not be used

without SimpleQuery mode this is what is executed

2024-06-17 05:55:31.341 EDT [13722] LOG:  duration: 0.210 ms parse <unnamed>: SELECT $1 * 2
2024-06-17 05:55:31.341 EDT [13722] LOG:  duration: 0.046 ms  bind <unnamed>: SELECT $1 * 2
2024-06-17 05:55:31.341 EDT [13722] DETAIL:  Parameters: $1 = '1.6'
2024-06-17 05:55:31.341 EDT [13722] LOG:  duration: 0.002 ms  execute <unnamed>: SELECT $1 * 2
2024-06-17 05:55:31.341 EDT [13722] DETAIL:  Parameters: $1 = '1.6'

Same code with SimpleQuery mode

2024-06-17 05:59:35.778 EDT [14625] LOG:  duration: 0.341 ms  statement: SELECT ('1.6'::numeric) * 2

If you are seeing the second, then somehow you are enabling SimpleQuery mode

[CharlesLgn]

2024-06-17 05:55:31.341 EDT [13722] DETAIL: Parameters: $1 = '1.6'

Is the type (numeric/double precision/...) here depending on the setDouble/setBigDecimal or is it determined by the value on the ParameterMetaData of the PreparedStatement ?

If you are seeing the second, then somehow you are enabling SimpleQuery mode

When I debuged, I didn't reach the code I was modifing (so I don't think I'm in SimpleMode). However, when we catch the request in the SGBD log, we have ('40.6'::double precision)

[davecramer]

I ran

pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/PreparedStatementTest.java

Line 45 in 195b61e

public void testSetNumber() throws SQLException {

with and without

pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/PreparedStatementTest.java

Line 25 in 195b61e

PGProperty.PREFER_QUERY_MODE.set(props, "simple");

[CharlesLgn]

I ran

pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/PreparedStatementTest.java

Line 45 in 195b61e

public void testSetNumber() throws SQLException {

with and without

pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/PreparedStatementTest.java

Line 25 in 195b61e

PGProperty.PREFER_QUERY_MODE.set(props, "simple");

ok but you said :

2024-06-17 05:55:31.341 EDT [13722] LOG:  duration: 0.002 ms  execute <unnamed>: SELECT $1 * 2
2024-06-17 05:55:31.341 EDT [13722] DETAIL:  Parameters: $1 = '1.6'

how does the SGBD choose the type of 1.6 ?
Is it define by :

• the SGBD ?
• the type given in the setDouble/setBigDecimal/setFloat ?
• the type known in the ParameterMetaData ?

[davecramer]

pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/PreparedStatementTest.java

Line 48 in 195b61e

pstmt.setBigDecimal(1, new BigDecimal("1.6"));

Sets it to be a BigDecimal

[CharlesLgn]

ok, understood.

so for my need, I did something like that in my code :

  public void setValues(PreparedStatement st, int index, double d) throws SQLException {
    int type = st.getParameterMetaData().getParameterType(index);
    if (type == Types.NUMERIC) {
      st.setBigDecimal(index, BigDecimal.valueOf(d));
    } else {
      st.setDouble(index, d);
    }
  }

but why does not jdbcPG do something similar ?

I mean, when we create the PreparedStatement, all the ? types are defined in the ParameterMetaData. Moreover the data that is uses in the BigDecimal is BigDecimal#getValue() returning a double. so :

• The bytes array is the same between a double and a BigDecimal
• We have the info of the parameter type in the metadata

what are the limitation to not do so ?