Re: PREPARE and transactions

On Thu, Jun 24, 2004 at 08:51:32AM -0400, Merlin Moncure wrote:

> > When I say "within a transaction" as opposed to outside a transaction,
> I
> > mean of course an explicit transaction. If you want a prepared
> statement
> > to last throughout the session, I'd say it stands to reason that you
> > create it outside a transaction--in unfettered session context, so to
> > speak. I can't see how that would be either less intuitive or harder
> to
> > program in the client.
>
> I disagree. Lots of people use prepared statements for all kinds of
> different reasons. A large percentage of them do not need or make use
> of explicit transactions. Having to continually rebuild the statement
> would be a hassle. The caching mechanism also seems like extra work for

I think we're talking at cross purposes here... If the client doesn't use
explicit transactions, as you say is common, then you're obviously not
defining prepared statements inside explicit transactions either. And so
you're certainly not going to be bothered by what happens at the end of a
transaction! In that case, what I'm saying doesn't affect you at all, in
any way.

But let's look at the case where you do use explicit transactions, which
is what we're talking about. I think there should be a difference between

(1) BEGIN
PREPARE foo AS ...
...
COMMIT/ABORT

(2) PREPARE foo AS ...
BEGIN
...
COMMIT/ABORT

There currently is no difference. Even if you abort the transaction, you
will still have that prepared statement (which may require manual cleaning
up), unless you aborted because of an error which occurred inside the
transaction and before or during the PREPARE, in which case trying to
clean up the statement would be an error. You can try to clean up the
prepared statement inside the transaction, but it would not work if
there were an error or abort between the PREPARE and the DEALLOCATE.

That sounds messy to me.

What I propose is simply that these two behave as follows:

(1) PREPARE foo AS ...
BEGIN
...
COMMIT/ABORT

In this case, foo is defined for the duration of the session *just like
current behaviour.* The presence of the transaction isn't relevant here
at all; it's only there for comparison. Commit or abort of the
transaction doesn't affect foo, because foo has been defined outside
the transaction in "unfettered session context," for want of a better
term.

Presumably you're going to use foo in several transactions, and/or in
several statements that are not in any explicit transaction. Unless you
deallocate explicitly, foo will be there as long as you stay connected,
just like you're used to.

(2) BEGIN
PREPARE foo AS ...
...
COMMIT/ABORT

Here, the PREPARE is inside the transaction so at the very least, you'd
expect its effect to be undone if the transaction aborts. I would go
further and say "if you wanted foo to persist, you would have prepared
it before going into the transaction" but that's a design choice.
Deallocating at commit/abort would have the advantage that you always
know whether foo exists regardless of the transaction's outcome: if
defined inside the transaction, it lives and dies with the transaction.
If defined merely in the session (i.e. not in any transaction), it lives
and dies with the session.

So you use this second form when you don't intend to reuse this statement
after the transaction. If you do, OTOH, you use the first form. It
also means that you don't "leak" prepared statement plans if you forget
to deallocate them--remember that the prepared statement may be generated
on-the-fly based on client-side program variables.

> little result (to be fair, I like the idea of multiple backends being
> able to make use of the same plan). Generic routines can just always
> wrap the prepare statement in a subtransaction, which now allows safety
> until such time that a create or replace version becomes available,

The nested-transaction version allows you to add code to deal with the
uncertainty that I'm proposing to remove. In the current situation, it's
annoyingly hard to figure out whether the prepared statement exists so you
redefine it "just in case," going through a needless subtransaction abort
or commit. That's the nested-transaction solution you appear to favour;
but AFAICS _this_ is the approach where you have to "continually rebuild
the statement." With my version, you don't need to go through all that
because you're allowed to _know_ whether the statement exists or not.

I don't even think the nested-transaction approach helps with anything:
if you want to re-prepare foo for continued use in the rest of the session
just in case it wasn't around anymore (and ignore the likely error for
the redefinition), you might as well do so before you go into your
transaction in the first place. No nested transactions needed.

Jeroen