I am seeing issues where some batched statements are not getting rewritten and executed as a batch.
- The queries that are not getting rewritten are UPSERT queries & INSERT INTO… ON CONFLICT …DO NOTHING queries.
If you look at the screenshot the mean row affected value is 1 when in fact the test is inserting ~1024 rows per test. (The high execution count is across multiple runs but if batching was working correctly, I would have expected that count to be much lower.)
Here’s another example:
Full Query from the diagnostics page:
INSERT INTO profiles.profile_versions(profile_id, profileoperation_id, "timestamp") VALUES ($1, $2, __more1__) ON CONFLICT (profile_id, "timestamp") DO NOTHING RETURNING NOTHING
The queries uses prepared statement:
I am using postgres driver version 42.2.9 as per the recommendation in the docs and I am setting
reWriteBatchedInserts to true in the connection string.
I can confirm that this works for simple insert queries like:
INSERT INTO profiles.profile_updates(profileoperation_id, profile_id, "timestamp", action, details) VALUES ($1, $2, __more3__), (__more100__)
I ended up just creating multi row upserts & insert for some of these cases to get around this for now. Is there some setting I am missing or is it somehow expected behavior for these queries.
Table structures for some of the tables I mentioned in the queries above:
CREATE TABLE public.profile_versions ( profile_id INT8 NOT NULL, profileoperation_id INT8 NOT NULL, "timestamp" INT8 NOT NULL, CONSTRAINT "primary" PRIMARY KEY (profile_id ASC, "timestamp" ASC), INDEX profileoperation_id (profileoperation_id ASC), INDEX "timestamp" ("timestamp" ASC), FAMILY "primary" (profile_id, profileoperation_id, "timestamp") ) CREATE TABLE public.profile_versions_latest ( profile_id INT8 NOT NULL, profileoperation_id INT8 NOT NULL, "timestamp" INT8 NOT NULL, CONSTRAINT "primary" PRIMARY KEY (profile_id ASC), FAMILY "primary" (profile_id, profileoperation_id, "timestamp") )