Re: Prepared statements considered harmful

On 31-Aug-06, at 2:58 PM, Tom Lane wrote:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Gregory Stark wrote:
>>> Driver interfaces expose very clearly to the user an explicit
>>> interface to prepare and execute a query separately.
>
>> The JDBC documentation merely contains statements of the sort "A SQL
>> statement with or without IN parameters can be pre-compiled and
>> stored
>> in a PreparedStatement object. This object can then be used to
>> efficiently execute this statement multiple times." There is
>> absolutely no indication that the execution plan of the statement is
>> computed at the time of preparation.
>
> The key word there is "efficiently". I think it is a reasonable
> presumption on the user's part that a query done this way will have
> less
> overhead than just resubmitting the raw query each time.
>
> The important thing I see here is that JDBC allows use of IN
> parameters
> with or without a PreparedStatement (no?).

No, not that I am aware of. You can create a statement, and execute
it, but you need a PreparedStatement to set IN parameters

> So they've separated the
> concepts of out-of-line parameters and "preparing" a statement.
> That's
> the distinction we have unfortunately fudged in the V3 protocol.
>
> The protocol does let you use OOL parameters without retaining a
> prepared plan, thanks to the hack introduced later to not plan the
> unnamed statement at Parse time, but that's definitely a bit of a wart
> on the original protocol design. Maybe it's good enough, or maybe
> not.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Dave