Re: the IN clause saga - Mailing list pgsql-jdbc

Thanks for summarizing it Oliver.

I've asked Tom Lane about the backend behavior and he informed me that:

1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
$3) (i.e., our (?, ?, ?) syntax).

2) 7.4 backends have a PostgreSQL specific extension that allows you to
fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
pass a PostgreSQL array, like integer[] to fill the list.  Note that the
parenthesis is already in place, it is not generated by the ? expansion.

The feature 2 in 7.4 backends is of limited use as the planner does not
know about the list, so the generated plan will not be as good as if you
pass the list with fixed values since the beginning.  But an improvement
for this can be attempted for 7.5.

Regards,
Fernando




Oliver Jowett wrote:
> Some of the threads on this are getting a bit bogged down, I thought I'd
> summarize the viable options I've seen so far (well, from my point of view
> anyway)
>
> setObject() currently allows the user to bypass parameter escaping via
> setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
> plugged as it's a potential security hole.
>
> However the same functionality lets you do the (nonstandard) trick of
> providing an IN clause to a PreparedStatement like "SELECT * FROM table
> WHERE pk IN ?". It'd be good to still allow this functionality somehow after
> setObject is fixed. This is going to be a postgresql-specific extension
> however we do it.
>
> Here are the permutations I can remember:
>
> Option 1: add a method to PGStatement that explicitly sets an IN clause,
>   taking either a java.sql.Array, java.util.Collection + component type,
>   array + component type, or a custom postgresql object
>
>   + there's no confusion as to what it means
>   + using a custom object allows access via setObject(..., Types.OTHER)
>     consistently, as well as via the extension method.
>   - java.sql.Array and java.util.Collection have problems as PGStatement is
>     compiled for all JDKs and JDBC versions and those types may not be present
>     (we could do a PGJDBC2Statement or something, but that's getting messy)
>   - have to downcast to a PGStatement to use it
>
> Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>
>   + no new methods or types needed
>   - setArray() behaves differently depending on query context
>   - user has to wrap the underlying array in a java.sql.Array
>
> Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>
>   + no new methods or types needed
>   - must assume that the contents of the collection use the default type mapping
>     if a type is not provided
>   - if a type is provided and we apply it to the *components* of the
>     collection, this breaks the general getObject() interface of "bind this
>     object interpreting it as this particular type".
>   - not obvious what to do with objects that are both Collections and some
>     other SQL-relevant type; solutions make setObject's behaviour complex
>     and/or query-context-dependent
>
> Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>    java.util.Collection
>
>   + as 3, but the ambiguity of "object is both Collection and SQL type X"
>     goes away.
>
> Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>   in this way.
>
>   + no issues with server-side prepare
>   - obviously, you can't set IN clauses in one go any more.
>
> 1-4 all need to disable server-side prepare when used.
>
> Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
> partial implementation of 2 written but it's easy to adapt that to whatever
> external interface.
>
> setArray() needs fixing regardless of what happens here. I hope to have a
> patch for that ready later today.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9