Re: IN or ANY for batch queries

On 28 May 2018 at 18:34, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
wrote:

> Hi,
>
> >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.
>

... ugh. Cool, but gross.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services