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 <bashtanov(at)imap(dot)cc> 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(dot)(dot)(dot)(dot)t(at)(dot)@.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?
>

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

>
>