{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Background","type":"text"}]},{"type":"paragraph","content":[{"text":"Repository queries taking collection parameters are limited to 32K collection items.","type":"text"},{"type":"hardBreak"},{"text":"For example, given a ","type":"text"},{"text":"FragmentRepository","type":"text","marks":[{"type":"em"}]},{"text":" method:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"List findAllByAnchorIdAndXpathIn(int anchorId, Collection xpaths);","type":"text"}]},{"type":"paragraph","content":[{"text":"This would throw an exception if supplied with a collection of 32,767 xpaths.","type":"text"}]},{"type":"paragraph","content":[{"text":"The reason is that JPA/Hibernate queries would translate the command to the following SQL:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT * FROM fragment WHERE anchor_id = ? AND xpath IN (?,?,?,?,?,?...,?)","type":"text"}]},{"type":"paragraph","content":[{"text":"Each collection item (xpath) is supplied as an SQL query parameter. Hibernate with Postgres has a limit of 32,767 query parameters per SQL statement, so an exception will be thrown with too many.","type":"text"}]},{"type":"paragraph","content":[{"text":"CPS-1574 is to remove those limits (and write tests to verify).","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Tests","type":"text"}]},{"type":"paragraph","content":[{"text":"As part of CPS-1574, tests have been added to confirm the presence of the limit:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":" def 'Delete datanodes from multiple anchors limit exceeded: 32,766 (~ 2^15) anchors.'() {\n given: 'more than 32,766 anchor names'\n def anchorNames = (0..32_766).collect { \"size-of-this-name-does-not-matter-for-limit-\" + it }\n when: 'single operation is executed to delete all datanodes in given anchors'\n objectUnderTest.deleteDataNodes(CPS_PERFORMANCE_TEST_DATASPACE, anchorNames, OffsetDateTime.now())\n then: 'a database exception is thrown'\n thrown(DataAccessResourceFailureException.class)\n }","type":"text"}]},{"type":"paragraph","content":[{"text":"Once the solution is implemented, these tests will be updated to show that no exception is thrown, with even more items e.g. 40,000.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Proposal #1: Batching at 32,000","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution adds a wrapper method that partitions the input collection into lists of 32,000 and calls the original repository method on each batch:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"@Query(value = \"SELECT * FROM fragment WHERE anchor_id = :anchorId AND xpath IN :xpaths\", nativeQuery = true)\nList findAllByAnchorIdAndXpathIn(int anchorId, Collection xpaths);\n\ndefault List findAllByAnchorIdAndXpathInBatch(final int anchorId, final List xpaths) {\n List results = new ArrayList<>(xpaths.size());\n for (final Collection xpathsBatch : Lists.partition(xpaths, 32000)) {\n results.addAll(findAllByAnchorIdAndXpathIn(anchorId, xpathsBatch));\n }\n return results;\n}","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution is largely self-documenting, as the 32K limit is clearly seen in the code.","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution still involves having a separate query parameter for each collection item - we are avoiding the limit by simply splitting into multiple SQL queries:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT * FROM fragment WHERE anchor_id = ? AND xpath IN (?,?,?,?,?,?...,?)\nSELECT * FROM fragment WHERE anchor_id = ? AND xpath IN (?,?,?,?,?,?...,?)\nSELECT * FROM fragment WHERE anchor_id = ? AND xpath IN (?,?,?,?)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Observations","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Performance testing is inconclusive (so far)","type":"text"}]},{"type":"paragraph","content":[{"text":"Testing with a batch size of 1000 (not 32000) showed a performance drop of 30% at 20000 items. More testing is needed to see if this applies with batch size of 32000, or if the test involved bad luck.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Advantage: Solution is easy to understand","type":"text"}]},{"type":"paragraph","content":[{"text":"This code is largely self-documenting, as the 32K limit is clearly seen in the code.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Batching on repository methods versus API methods","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution should work equally well if batching at the public API methods instead of each repository method.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Proposal #2: Postgres arrays","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution uses a third-party library to add Postgres array support to queries.","type":"text"}]},{"type":"paragraph","content":[{"text":"Adding the dependency to ","type":"text"},{"text":"cps-ri/pom.xml","type":"text","marks":[{"type":"em"}]},{"text":":","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n io.hypersistence\n hypersistence-utils-hibernate-52\n","type":"text"}]},{"type":"paragraph","content":[{"text":"This solution involves creating two versions of each repository method taking a collection parameter - one taking a Java array, and one using a Collection:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"@Query(value = \"SELECT * FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY(:xpaths)\", nativeQuery = true)\nList findAllByAnchorIdAndXpathIn(@Param(\"anchorId\") int anchorId, @Param(\"xpaths\") String[] xpaths);\n\ndefault List findAllByAnchorIdAndXpathIn(final int anchorId, final Collection xpaths) {\n return findAllByAnchorIdAndXpathIn(anchorId, xpaths.toArray(new String[0]));\n}","type":"text"}]},{"type":"paragraph","content":[{"text":"This would result in a single SQL query, given any number of xpaths:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT * FROM fragment WHERE anchor_id = ? AND xpath = ANY(?)","type":"text"}]},{"type":"paragraph","content":[{"text":"As seen, only two SQL query parameters will be ever used. This is because the array is binary-encoded and sent as a single parameter.","type":"text"}]},{"type":"paragraph","content":[{"text":"Testing so far has shown no noticeable performance difference with master branch.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Observations","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Performance is unchanged","type":"text"}]},{"type":"paragraph","content":[{"text":"Testing shows there was no increase or decrease in performance compared to master branch after arrays were used.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Disadvantage: extra dependency","type":"text","marks":[{"type":"strike"}]}]},{"type":"paragraph","content":[{"text":"Extra dependencies could add possible risks of dependency issues, security issues, etc. in future.","type":"text","marks":[{"type":"strike"}]}]},{"type":"paragraph","content":[{"text":"The new dependency is an updated version of an existing dependency (hibernate-types-52) - the developer renamed the library at some point. More info here: ","type":"text"},{"text":"https://vladmihalcea.com/hibernate-types-hypersistence-utils/","type":"text","marks":[{"type":"link","attrs":{"href":"https://vladmihalcea.com/hibernate-types-hypersistence-utils/"}}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Disadvantage: Limited array type support","type":"text"}]},{"type":"paragraph","content":[{"text":"Postgres array support is limited to primitive data types and String, that is ","type":"text"},{"text":"int[], long[], float[], double[], boolean[], String[]","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"For example, given a ","type":"text"},{"text":"FragmentRepository","type":"text","marks":[{"type":"em"}]},{"text":" method such as:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"void deleteByAnchorIn(Collection anchorEntities);","type":"text"}]},{"type":"paragraph","content":[{"text":"Changing to an array of ","type":"text"},{"text":"AnchorEntity","type":"text","marks":[{"type":"em"}]},{"text":" will not work. The following will throw an exception:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"void deleteByAnchorIn(AnchorEntity[] anchorEntities);","type":"text"}]},{"type":"paragraph","content":[{"text":"Rather, the method would have to be changed to use anchor IDs:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":"void deleteByAnchorIdIn(int[] anchorIds);","type":"text"}]},{"type":"paragraph","content":[{"text":"Side note","type":"text","marks":[{"type":"em"}]},{"text":": in the above case, if using auto-generated SQL from the method name, the SQL generated using the anchor ID will be simpler than when using AnchorEntity objects, as Hibernate will generate a JOIN between ","type":"text"},{"text":"FragmentEntity","type":"text","marks":[{"type":"em"}]},{"text":" and ","type":"text"},{"text":"AnchorEntity","type":"text","marks":[{"type":"em"}]},{"text":" in the first case, but will only use ","type":"text"},{"text":"FragmentEntity","type":"text","marks":[{"type":"em"}]},{"text":" in the last case. So while the types are more restrictive, it may result in more optimal SQL code.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Advantage: Code already using Postgres arrays can be simplified","type":"text"}]},{"type":"paragraph","content":[{"text":"The current ","type":"text"},{"text":"FragmentRepository","type":"text","marks":[{"type":"em"}]},{"text":" has code already using Postgres arrays to support \"LIKE ANY\" logic (using LIKE with a collection). However, it currently uses query parameters, and is thus limited to 32K parameters.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":" @Override\n public void deleteByAnchorIdAndXpathLikeAny(final int anchorId, final Collection xpathPatterns) {\n final String queryString = \"DELETE FROM fragment f WHERE f.anchor_id = ? AND xpath LIKE ANY (array[:parameterPlaceholders])\";\n executeUpdateWithAnchorIdAndCollection(queryString, anchorId, xpathPatterns);\n }\n\n // Accept security hotspot as placeholders in SQL query are created internally, not from user input.\n @SuppressWarnings(\"squid:S2077\")\n private void executeUpdateWithAnchorIdAndCollection(final String sqlTemplate, final int anchorId, final Collection collection) {\n if (!collection.isEmpty()) {\n final String parameterPlaceholders = String.join(\",\", Collections.nCopies(collection.size(), \"?\"));\n final String queryStringWithParameterPlaceholders =\n sqlTemplate.replaceFirst(\":parameterPlaceholders\\\\b\", parameterPlaceholders);\n\n final Query query = entityManager.createNativeQuery(queryStringWithParameterPlaceholders);\n query.setParameter(1, anchorId);\n int parameterIndex = 2;\n for (final String parameterValue : collection) {\n query.setParameter(parameterIndex++, parameterValue);\n }\n query.executeUpdate();\n }\n }","type":"text"}]},{"type":"paragraph","content":[{"text":"Using native Postgres array support, this code can be written directly in the ","type":"text"},{"text":"FragmentRepository","type":"text","marks":[{"type":"em"}]},{"text":" interface using ","type":"text"},{"text":"Query","type":"text","marks":[{"type":"em"}]},{"text":" annotation, without the 32K limitation:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"java"},"content":[{"text":" @Modifying\n @Query(value = \"DELETE FROM fragment f WHERE anchor_id = :anchorId AND xpath LIKE ANY (:xpathPatterns)\", nativeQuery = true)\n void deleteByAnchorIdAndXpathLikeAny(@Param(\"anchorId\") int anchorId, @Param(\"xpathPatterns\") String[] xpathPatterns);\n\n default void deleteByAnchorIdAndXpathLikeAny(int anchorId, Collection xpathPatterns) {\n deleteByAnchorIdAndXpathLikeAny(anchorId, xpathPatterns.toArray(new String[0]));\n }","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Comparison","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"More testing is needed to confirm if list partitioning drops performance with large datasets.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Solution #1 is explicit using 32K batches, so easy to understand what it does","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Solution #2 has shorter code, but not very obvious to the reader what it does","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"We could add a comment linking to this ticket to explain the 32K limit and why arrays are used","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Solution #2 simplifies code in the cases when we need Postgres array support.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"It's possible to use both solutions, where most appropriate.","type":"text"}]}]}]}],"version":1}

Browser not supported