Re: Prepared statements considered harmful

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> With time, it becomes ever clearer to me that prepared SQL statements
> are just a really bad idea.

That's an overstatement, but I'll agree that they have strong
limitations.

> I'd wish that we reconsider when and how prepared statements are used. The
> JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the
> problem is really all over the place.

AFAIK those are the only two places where preparation is the default
... what else were you thinking of?

> - Transparently invalidate and regenerate prepared plans more often. This
> could be tied to the transaction count, update activity obtained from the
> statistics collector, etc.

FWIW, I've assumed right along that once we have a plan-invalidation
mechanism, any ANALYZE stats update would invalidate affected plans.

> - Redefine "prepared" to mean "parsed" rather than "parsed and planned".

For plan-inval to work in all cases, we'll have to store either the source
query string or the raw grammar's output tree, before even parse analysis.
Is that what you are thinking of? It's hardly "prepared" at all if you
do that.

As noted downthread, we've confused out-of-line parameter value shipping
with prepared statements. It might be worth rejiggering the FE/BE
protocol to separate those things better.

regards, tom lane