Thanks everyone for your feedback. I have one more question.
I'm working on enhancing Hibernate to take advantage of WHERE ... = ANY(?) for batch queries and .The enhancement is specific to postgres so it has to be added to one of the Hibernate postgres "dialect" classes.
Hibernate has separate dialect classes for postgres 8.1, 8.2, 9, 9.1, 9.2, 9.3, 9.4 and 9.5. Each class extends the previous release class - i.e., 9.5 extends 9.4 which extends 9.3, etc.
I'm trying to figure out to which class I should add this enhancement. Ideally, it should be the class associated with the earliest release of postgres that supports this functionality. I tried to find out in which release WHERE ... = ANY(?) was first implemented but have had no luck.
Does anyone have any idea?
Thanks again,
Alessandro
On Monday, May 28, 2018, 7:01:40 AM MDT, Craig Ringer <craig@2ndquadrant.com> wrote:
>However, since in my application the number of values in the array can vary, I'm wondering if using ANY has the benefit of causing the driver/DB to cache a smaller number of prepared statement.
Right you are. In case you use Array, the whole thing is passed as a single bind, and it results in a single prepared statement.
Naive use of (?, ?, ?, ?, ?) would result in multiple prepared statements that would consume memory at both client and server sides.
Right. I didn't think you'd do that, I assumed you'd interpolate properly quoted SQL literals. Not only is that more vulnerable to potential issues if your quoting is flawed, it often uses more memory and CPU for the string processing, so an array bind is definitely better.
I had my "writing TAP tests in Perl" hat on, not my "developing sane SQL" hat on. Sorry.
1) Note: sometimes you might want to use (?, ?, ?) kind of syntax (e.g. to support DBs that cannot process arrays), then you might want to use "power of two" placeholders, and fill the excessive ones with null value or with a repetition of the last value.
For instance, if you want to pass 5 values 1,2,3,4,5 you'd better pass it as
(1,2,3,4,5,5,5,5) (of course, use ?,?,?,?, ?,?,?,? )
I do not advice you to use that extensively, however it is a nice to know trick.