Re: Weird prepared stmt behavior

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Before jumping into doing that, though, I'd want to have some
> discussions about the implications for the V3 protocol's notion of
> prepared statements. The protocol spec does not say anything that
> would suggest that prepared statements are lost on transaction rollback,
> and offhand it seems like they shouldn't be because the protocol is
> lower-level than transactions.

Woah, that would totally defeat the purpose of prepared statements.

The idea is that an OLTP system can prepare all the statements it will ever
need at startup time. Then simply execute them with various parameters as
needed.

For instance, on even a large web site there are often only a few dozen pages
with a few hundred SQL queries total. It's entirely feasible to prepare them
all on startup then simply execute them as needed. This means the optimizer
only ever needs to look at a query once, not every execution which could be
hundreds of times per second.

Actually, using a web server architected like Apache this turns into "prepare
the first time it's seen in a given process or thread". Configure Apache
processes to last a few thousand page requests and the query is reparsed and
optimized often enough to take advantage of new statistics but infrequently
enough to be a negligible hit on performance.

Using a good driver like Perl's DBI this just means using something like
prepare_cached() instead of prepare().

--
greg