Re: [Re] Re: PREPARE and transactions - Mailing list pgsql-hackers

On Thu, Jul 01, 2004 at 04:06:06PM -0400, Merlin Moncure wrote:
> The big picture here is that with the current behavior, it is possible
> to keep track of existence of prepared statements without wrapping or
> even being aware of transaction activity.  This is tremendously useful
> for handling them in a generic way because transactions and prepared
> statements are handled on different levels of my (and others')
> technology stack.  If you change that behavior, that is no longer
> possible, period, and I will be forced to stop using them.
But then how can you be sure that other layers in your stack won't try to
re-establish a broken connection, multiplex sessions, pool connections,
parallellize transactions or prepare their own statements?


> On the conceptual side of things, I submit that the creation and
> deletion of prepared statements are much closer to session variables
> than to normal imperative SQL (that normally has a transactional
> lifetime).  They are an optimization hack (albeit a very handy one) much
> the same as turning off sequential scans.  I tend to think of them more
> as macros that I can create and destroy (and in implementation, that is
> really close to what they are).

Here lies the real problem: if only they were _designed_ as an optimization
hack, things would be fine.  Just make them anonymous and let the server
pattern-match them; no need to change your code to keep it working.

In fact...

What if we allowed the backend to match queries to the patterns of
prepared statements without actually naming them?  You'd then have two
ways of executing a prepared query:

1. EXECUTE, as we do now, which involves naming the statement and that's
where the trouble begins.  So probably best to avoid this approach, unless
you PREPARE and EXECUTE in the same transaction and so know exactly what's
going on.

2. Issue your query as normal; the backend will find that it matches a
prepared query (regardless of what it's called) and use the plan stored
there.  Optimizing a program to use prepared queries is a mere matter of
adding the PREPARE, and no other program statements need to be changed. 
The worst that can happen is that you accidentally lose an opportunity
to apply the prepared plan somewhere.

The great benefit of 2 would be that the server can cache & reuse prepared
statements across transactions, _without transferring any semantic state_
in these strange and exotic ways.  In other words, no matter what happens to
the prepared statement, your code will still run.  And probably still
benefit from the optimization; you get the freedom to tweak that further as
you like, but it needn't affect existing code.  Middleware writers won't
even need to parse SQL statements to replace them with EXECUTEs anymore.

Other benefits we could eventually get out of this would be dynamic
garbage collection; reuse across sessions; dynamic, server-side choice
between multiple overlapping ("specialized") prepared statements; quietly
accepted redefinitions that make syntactic sense.

Of course this would take some work, both in developer time and execution
time, but it would allow us to have sensible transaction semantics on the
one hand, and optimization (and simplified at that!) on the other.

Am I making things too simple here?


Jeroen