Re: [Re] Re: PREPARE and transactions

Jeroen T. Vermeulen wrote:

> If it's that important, come up with a generic "session-not-transaction"
> syntax to temporarily escape bracketing.

Do you have a proposal for this? It seems to me that if your argument is
that "if you want the old behaviour, you could add this extension" means
that you need to provide the extension before changing the behaviour.
Otherwise you're just exchanging one set of disgruntled users for another.

> Now, two more issues for middleware that does prepare statements:

> (*) What if preparing a statement fails? Could be that you've just broken
> the transaction at a point where the application didn't expect it, or in a
> way it didn't expect.

The JDBC driver, at least, is careful to only issue a PREPARE at the
same point the underlying query would have been run anyway, so there's
no issue there.

> (*) What if your prepared statement interferes with one prepared by the
> application?

Name your autogenerated statement names better. Document the names that
applications shouldn't use as part of your middleware documentation, or
don't support applications using PREPARE when the middleware is too.

> Just don't tell me that making
> PREPARE respect rollbacks would break compatibility

Why not? It's true.

> I should add here that session variables used to escape transaction
> bracketing as well--but that was fixed some time ago. Why are session
> variables so different from prepared statements?

Prepared queries do not change the semantics of queries. Some session
variables do (e.g. DateStyle).

It's actually quite annoying -- speaking from the client side -- for the
state of some of the session variables to be affected by transaction
boundaries, namely those that are really to do with the protocol-level
stream's state, e.g. client_encoding. You really don't want those
changing back on you, and you want the ability to change them at
arbitrary points in the connection -- even if you're in a failed
transaction. Ideally I'd like to see a way where all of these
connection-specific settings that are to do with the client's
preferences, not the server's query execution semantics, can be changed
immediately regardless of transaction state -- because they really have
nothing to do with the global state of the database, they are already
isolated from the settings on other connections by their very nature,
and they do not affect the semantics of the queries being executed. The
only thing that making them transactional gives you is atomicity, and
since they affect the protocol stream at the point where they change,
you've actually bought extra work by allowing them to be rolled back.

But I think the tide is against me on this one :)

> At the very least, it
> would be nice for middleware to deal with one form of session state, not
> "the kind you manipulate with regular statements" and "the kind that
> ignores transaction bracketing except that it happens to be atomic also, and
> that manipulations are still rejected inside transactions that are already
> in failure mode."

This is only true for middleware that is not dealing with PREPARE
itself, but wants to be PREPARE-aware. The exact opposite is true for
middleware that uses PREPARE itself, as illustrated by my JDBC example
earlier.

Also: what about the V3 protocol's support for named statements?

V3-protocol named statements are very much nontransactional
protocol-level connection state at the moment, and are effectively a
more flexible form of PREPARE (they even map to the same namespace, I
believe). If you want to make PREPARE transactional, do those messages
also change? If you want to keep the messages nontransactional (as I
think they should be), why don't the arguments for doing that also apply
to PREPARE?

-O