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

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