Re: JDBC sometimes sends a parse message with only 2 of 21 parameter types specified

Hi Alexey,



On Fri, 25 Jan 2019 at 12:06, Alexey Bashtanov <[email protected]> wrote:

> Hello,
>
> I have a problem that I cannot easily reproduce (happens on average once
> a day on live).
> My SQL is the following:
>
>          final String sql = "insert into brand_versions ( "
>                  + "brand_id,"
>                  + "name, "
>                  + "description, "
>                  + "creation_date, "
>                  + "enabled,"
>                  + "normalised_keyphrase,"
>                  + "tags, "
>                  + "creator_id, "
>                  + "type, "
>                  + "subtype, "
>                  + "version_creation_date, "
>                  + "modifier_id, "
>                  + "daily_twitter_limit, "
>                  + "daily_limit, "
>                  + "twitter_tracking_type,"
>                  + "use_industry_classifier,"
>                  + "inclusion_terms,"
>                  + "languages, "
>                  + "language_agnostic, "
>                  + "options, "
>                  + "usecase, "
>                  + "wizard_configuration) "
>                  + "values (:brand_id, :name, :description,
> :creation_date, :enabled, ' ',:tags, " +
>                  ":creator_id, :type, :subtype, :version_creation_date,
> :modifier_id, " +
>                  ":daily_twitter_limit, :daily_limit,
> :twitter_tracking_type, :use_industry_classifier, array[ :included_term
> ], " +
>                  ":languages::text[], :language_agnostic,
> hstore(:options), :usecase, :wizardConfiguration) " +
>                  "returning *, "
>                         + " inclusion_terms[1] as included_term ";
>
> And I use NamedParameterJdbcTemplate to execute it like this:
>
> njt.query(sql, parameters, queryVersionRowMapper);
>
> parameters is of type MapSqlParameterSource
>
> "options" parameter in it is of type Map<String, String>, others are of
> types int, String, Date, boolean, Set<String>
>
> In the vast majority of cases it works fine and Parse message sent by
> client includes type specifications for all parameters.
> Bind immediately follows it, with parameter values.
>
> However, occasionally I can see the parse message having non-0 type oids
> for "brand_id" and "name" only:
>
>          0x0000:  4500 02a4 b474 4000 4006 55b3 0a00 0d16 E....t@[email protected].....
>          0x0010:  0a00 0d17 af5a 1538 7854 05c2 f125 f8f8 .....Z.8xT...%..
>          0x0020:  8018 05a4 a00d 0000 0101 080a bd9d 053c ...............<
>          0x0030:  4229 ebff 5000 0002 6300 696e 7365 7274 B)..P...c.insert
>          0x0040:  2069 6e74 6f20 6272 616e 645f 7665 7273 .into.brand_vers
>          0x0050:  696f 6e73 2028 2062 7261 6e64 5f69 642c ions.(.brand_id,
>          0x0060:  6e61 6d65 2c20 6465 7363 7269 7074 696f name,.descriptio
>          0x0070:  6e2c 2063 7265 6174 696f 6e5f 6461 7465 n,.creation_date
>          0x0080:  2c20 656e 6162 6c65 642c 6e6f 726d 616c ,.enabled,normal
>          0x0090:  6973 6564 5f6b 6579 7068 7261 7365 2c74 ised_keyphrase,t
>          0x00a0:  6167 732c 2063 7265 6174 6f72 5f69 642c ags,.creator_id,
>          0x00b0:  2074 7970 652c 2073 7562 7479 7065 2c20 .type,.subtype,.
>          0x00c0:  7665 7273 696f 6e5f 6372 6561 7469 6f6e version_creation
>          0x00d0:  5f64 6174 652c 206d 6f64 6966 6965 725f _date,.modifier_
>          0x00e0:  6964 2c20 6461 696c 795f 7477 6974 7465 id,.daily_twitte
>          0x00f0:  725f 6c69 6d69 742c 2064 6169 6c79 5f6c r_limit,.daily_l
>          0x0100:  696d 6974 2c20 7477 6974 7465 725f 7472 imit,.twitter_tr
>          0x0110:  6163 6b69 6e67 5f74 7970 652c 7573 655f acking_type,use_
>          0x0120:  696e 6475 7374 7279 5f63 6c61 7373 6966 industry_classif
>          0x0130:  6965 722c 696e 636c 7573 696f 6e5f 7465 ier,inclusion_te
>          0x0140:  726d 732c 6c61 6e67 7561 6765 732c 206c rms,languages,.l
>          0x0150:  616e 6775 6167 655f 6167 6e6f 7374 6963 anguage_agnostic
>          0x0160:  2c20 6f70 7469 6f6e 732c 2075 7365 6361 ,.options,.useca
>          0x0170:  7365 2c20 7769 7a61 7264 5f63 6f6e 6669 se,.wizard_confi
>          0x0180:  6775 7261 7469 6f6e 2920 7661 6c75 6573 guration).values
>          0x0190:  2028 2431 2c20 2432 2c20 2433 2c20 2434 .($1,.$2,.$3,.$4
>          0x01a0:  2c20 2435 2c20 2720 272c 2436 2c20 2437 ,.$5,.'.',$6,.$7
>          0x01b0:  2c20 2438 2c20 2439 2c20 2431 302c 2024 ,.$8,.$9,.$10,.$
>          0x01c0:  3131 2c20 2431 322c 2024 3133 2c20 2431 11,.$12,.$13,.$1
>          0x01d0:  342c 2024 3135 2c20 6172 7261 795b 2024 4,.$15,.array[.$
>          0x01e0:  3136 205d 2c20 2431 373a 3a74 6578 745b 16.],.$17::text[
>          0x01f0:  5d2c 2024 3138 2c20 6873 746f 7265 2824 ],.$18,.hstore($
>          0x0200:  3139 292c 2024 3230 2c20 2432 3129 2072 19),.$20,.$21).r
>          0x0210:  6574 7572 6e69 6e67 202a 2c20 2069 6e63 eturning.*,..inc
>          0x0220:  6c75 7369 6f6e 5f74 6572 6d73 5b31 5d20 lusion_terms[1].
>          0x0230:  6173 2069 6e63 6c75 6465 645f 7465 726d as.included_term
>          0x0240:  2000 0015 0000 0017 0000 0413 0000 0000 ................
>          0x0250:  0000 0000 0000 0000 0000 0000 0000 0000 ................
>          0x0260:  0000 0000 0000 0000 0000 0000 0000 0000 ................
>          0x0270:  0000 0000 0000 0000 0000 0000 0000 0000 ................
>          0x0280:  0000 0000 0000 0000 0000 0000 0000 0000 ................
>          0x0290:  0000 0000 0000 0000 4400 0000 0653 0053 ........D....S.S
>          0x02a0:  0000 0004                                ....
>
> on line 0x0030 we can see that it's the unnamed prepared statement
> on line 0x0240 0x0015 is the number of parameters (21),
>    0x00000017 is int, and 0x00000413 is varchar
> on line 0x0290 we can see the Sync message
>
> This fails because of `hstore()` function polymorphism.
>
> I maybe could fix that by putting explicit type casts in my SQL, but I'm
> curious why it's happening.
>
> Unfortunately I could not reproduce it reliably.
> What could be the circumstances for JDBC to specify parameter values for
> only 2 of them?
>

When in doubt JDBC uses the UNSPECIFIED OID.
https://github.com/pgjdbc/pgjdbc/blob/b7fd9f3cef734b4c219e2f6bc6c19acf68b2990b/pgjdbc/src/main/java/org/postgresql/core/Oid.java#L20
As we can't see inside the  NamedParameterJdbcTemplate I have no idea what
it is doing but I assume it is responsible somehow for using UNSPECIFIED.

Dave

>
>