> > 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?

Because the prepared statement and the connection are handled on the
same level, and will always be.  If and when I decide to implement
multiplexing/pooling, this has to be handled a level above my driver.
Since the applications my driver is serving are written in COBOL,
prepared statement creation is highly unlikely (plus the statement names
are well mangled).  My driver wraps the SQL interface and exposes some
of it as COBOL file I/O statements and some as library commands.

> 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:

I would not necessarily be opposed to this type of optimization but it
seems more complicated.  It would be up to smarter people than me to
judge it's value vs. implementation cost :).  ISTM you are losing a
slight advantage in parsing time vs. a more general query plan
generation time speedup.  

Right now, I'm transitioning to ExexPrepared to skip the string escaping
step on the client side.  I would hate to lose that ability.  ExecParams
is a little more work to set up (doable, though).

> Am I making things too simple here?

Unfortunately, I think the answer is yes.  Being able to roll back
prepared statements would exclude them from a narrow but important class
of use.  

With the introduction of nested x into the code base, your original
problem can be guarded against (albeit somewhat clumsily) by careful use
of wrapping PREPARE/DEALLOCATE.  If you switch things up, every EXECUTE
has to be wrapped to guarantee safety in a transaction agnostic
scenario.

Now, which would you rather do: wrap PREPARE, or wrap EXECUTE?

Merlin




Fri, 2 Jul 2004 08:51:05 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: [Re] Re: PREPARE and transactions
On Fri, Jul 02, 2004 at 08:51:05AM -0400, Merlin Moncure wrote:
 
> Right now, I'm transitioning to ExexPrepared to skip the string escaping
> step on the client side.  I would hate to lose that ability.  ExecParams
> is a little more work to set up (doable, though).
 
OTOH, if you're taking client code queries and replacing them with
EXECUTEs (which I sort of gathered some people were doing), all it should
really do is move the matching activity to the server...
 

> > Am I making things too simple here?
> 
> Unfortunately, I think the answer is yes.  Being able to roll back
> prepared statements would exclude them from a narrow but important class
> of use.  

Wait, wait, wait!  I'm talking about the pattern-matching proposal there.
The proposal means that prepared statement introduction would _not_ have
to be rolled back; only the _names_ would obey different rules.  In effect
it would separate the names from the plans, and make the plans do what you
want and more.  The names would be effectively obsolete.


> With the introduction of nested x into the code base, your original
> problem can be guarded against (albeit somewhat clumsily) by careful use
> of wrapping PREPARE/DEALLOCATE.  If you switch things up, every EXECUTE
> has to be wrapped to guarantee safety in a transaction agnostic
> scenario.

But why should anyone insist on a "transaction agnostic scenario" yet 
play with prepared statements?  That seems backwards to me.  Transactions
are a correctness issue, which you think about first.  Prepared statements
are optimization, which though important is undoubtedly a lesser concern.
If you're deep enough into the SQL stream to analyze the client's demands
and prepare statements based on it, just knowing what happens to bracketing
(unlike prepared statements, you don't even need to control that) should be
relatively easy.

In any case, AFAICS my proposal does away with the need to wrap anything,
or keep track of transactions and/or prepared statements.  Would it work
for you?


Jeroen


Fri, 2 Jul 2004 16:51:57 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: [Re] Re: PREPARE and transactions