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

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@.@.U.....
         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?