Re: Prepared statements considered harmful

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:

> Gregory Stark wrote:
>
> Let's verify that. JDBC and PL/pgSQL have been mentioned.
>
> 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. In fact, it doesn't say
> what "pre-compiled" means at all.

I didn't say you were violating the technical definition in the specification.
I said you're going against expectations. This is the problem with being
dogmatic about abstraction boundaries. Obviously someone who doesn't know
what's going on under the hood has no specific expectations about what
"pre-compiling" might mean.

But the reality is that you can't effectively use a database without
understanding what query plans are and users do have expectations about
behaviour below the abstraction barrier.

If you don't think "pre-compiled" and "efficiently execute multiple times"
doesn't translate into "generates a query plan so it doesn't have to go
through that process to execute the query" I think you're in a very small
minority.

> For PL/pgSQL, you simply write a query and all the preparing action
> happens implicitly. There is nothing explicit about that interface.

Well that's sort of the inherent problem with PLpgSQL and the way it mixes up
the procedural language with SQL.

I guess the natural extension of questioning PL/pgSQL would be to wonder why
subqueries in SQL queries don't get replanned every time they're executed. The
data distribution could certainly change partway though.

> So if users have certain expectations here, they're just making them up.

Well, that's what makes them "expectations" rather than promises.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com