Re: the IN clause saga

From: Fernando Nasser <fnasser(at)redhat(dot)com>
To: Felipe Schnack <felipes(at)ritterdosreis(dot)br>
Cc: Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org, Barry Lind <blind(at)xythos(dot)com>, Dave Cramer <Dave(at)micro-automation(dot)net>
Subject: Re: the IN clause saga
Date: 2003-07-22 15:15:06
Message-ID: 3F1D54FA.7010205@redhat.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Felipe Schnack wrote:
> Am I the only the only one who doesn't like the idea of the driver parsing SQL statements (to check if there is a IN clause)
>

Mind that this is only necessary for backward compatibility. With 7.4
and the V3 protocol you just send an array and the backend sorts it out.

> On Tue, 22 Jul 2003 10:41:22 -0400
> Fernando Nasser <fnasser(at)redhat(dot)com> wrote:
>
>
>>Oliver Jowett wrote:
>>
>>>On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>
>>>
>>>>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.
>>>
>>>
>>>I assume this is only when you're doing a PREPARE/EXECUTE?
>>>
>>
>>yes.
>>
>>
>>>>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.
>>>
>>>
>>>Hm, then it sounds like the right solution is to have setArray() expand as
>>>the guts of an IN clause when the backend is <7.4 or server prepares are
>>>off, and the parameter is in a query of the form "... IN (?)", and as a
>>>normal array otherwise.
>>>
>>
>>That is _exactly_ what I am proposing (option 2 of your summary)
>>
>>
>>
>>--
>>Fernando Nasser
>>Red Hat Canada Ltd. E-Mail: fnasser(at)redhat(dot)com
>>2323 Yonge Street, Suite #300
>>Toronto, Ontario M4P 2C9
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>

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

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Fernando Nasser 2003-07-22 15:16:06 Re: the IN clause saga
Previous Message Felipe Schnack 2003-07-22 15:12:44 Re: the IN clause saga