Thread: Re: [Re] Re: PREPARE and transactions

Jeroen wrote:
> Granted, that's probably going to force the issue.  I do wonder
though:
> one of the arguments in favour of the current semantics is that the
> problems can be worked around using nested transactions.  Then what
were
> people doing before nested transactions, in Tom's scenario where the
> programmer doesn't know where transactions begin?

The trick is that with the current semantics, you don't have to watch
transaction activity, just the prepare statements.  You know if and when
(from the client/driver's point of view) a prepared statement exists
because you created it and don't have to be concerned about the
lifetime.

If you guys change the lifetime, it becomes difficult or impossible to
set a flag on the client which guarantees prepared statement existence.
This means I have to wrap the statement execution with a subtransaction
or run the risk of bouncing a current transaction.  Currently in the
applications I write 70% of all I/O goes through prepared
statements...the reason to do this was to reduce statement turnaround
latency, which is the main driving performance factor in COBOL
applications.

I would be fine with changing the lifetime if an EXECUTE failure did not
abort the current transaction.  Then I could simply watch the return
code of the statement execution and prepare the statement on
demand...from my point of view, this would actually be the most elegant
scenario.

Merlin

On Fri, Jun 25, 2004 at 08:54:57AM -0400, Merlin Moncure wrote:

> I would be fine with changing the lifetime if an EXECUTE failure did not
> abort the current transaction.  Then I could simply watch the return
> code of the statement execution and prepare the statement on
> demand...from my point of view, this would actually be the most elegant
> scenario.

BEGIN;... do something ... ;SUBBEGIN;    EXECUTE ...;    -- if it fails:    -- SUBABORT;    -- PREPARE ...;    --
SUBBEGIN;   -- EXECUTE ...;    -- can continue as if nothing happenedSUBCOMMIT;
 
COMMIT;

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Acepta los honores y aplausos y perderás tu libertad"

Jeroen wrote:
> I see how making PREPARE obey rollbacks would be inconvenient for some
> existing code, but frankly I'm getting a bit worried about the "why
should
> I care whether what I do is committed or not?" argument.  I guess one
> could
> say that about lots of statements: "I don't really want this to be
subject
> to the transaction but it happens convenient for me to write it inside
the
> transaction, and then I have this problem that it's affected by
> rollbacks."
> If it's that important, come up with a generic
"session-not-transaction"
> syntax to temporarily escape bracketing.

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.

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).

Merlin

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

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

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

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

There is one subtle difference, and that the type resolution gets moved
from the server to the client.  ExecParams() requires a list of type
OIDs to be sent along with the statement whereas ExecPrepared() does
not.  To me, this is an inconsistent interface is set up with the
parameter name, not the oid of the parameter.  To me, it makes much more
sense to resolve by type name in all cases, but this may have some
performance implications.

> But why should anyone insist on a "transaction agnostic scenario" yet
> play with prepared statements?  That seems backwards to me.
Transactions

Because it improved statement turnaround by about 33%, and that's all I
really care about.  The conceptual case seems outweighed by practical
matters (if you move to a transactional lifetime first, that is).  This
performance difference is like having an extra processor.

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

Yes, it would.  It would actually make my life a lot easier
(notwithstanding my minor gripe with ExecParams) because I would no
longer have to deal with all the complexities surrounding prepared
statements.  This is A Good Thing, because the optimization is generic
and thus will benefit a lot more people.

It is also more a more complex optimization model; and I think you would
have to justifiably prove that it is in the same performance league as
the current prepared statement model.  Also the current prepared
statement behavior should be retained for 7.5 and perhaps deprecated if
your viewpoint wins out (and it should).

Merlin

On Fri, Jul 02, 2004 at 11:18:44AM -0400, Merlin Moncure wrote:
> Yes, it would.  It would actually make my life a lot easier
> (notwithstanding my minor gripe with ExecParams) because I would no
> longer have to deal with all the complexities surrounding prepared
> statements.  This is A Good Thing, because the optimization is generic
> and thus will benefit a lot more people.  
Okay, off I go into the source code then.  :-)

(Which is not to say "I'll have it ready next week" but I can get an idea
of how hard it would be)


> It is also more a more complex optimization model; and I think you would
> have to justifiably prove that it is in the same performance league as
> the current prepared statement model.  Also the current prepared

True, there is a cost.  OTOH I think the added advantages could pay off
as well, e.g. sharing plans between backends (if there's not too much
locking.


> statement behavior should be retained for 7.5 and perhaps deprecated if
> your viewpoint wins out (and it should).

I guess so.  My timing isn't exactly impeccable...


Jeroen