Re: [Re] Re: PREPARE and transactions

On Fri, Jul 02, 2004 at 12:30:07PM +1200, Oliver Jowett 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.

I was thinking of some "escape" keyword to say "I don't want this to be
part of the current transaction; execute it as if directly in the
surrounding session context." That would at least make it obvious what's
going on. Wouldn't take any extra semantic work, since one could just
restrict the statements that can go with this syntax--to just PREPARE and
DEALLOCATE for now. I guess it should also escape auto-abort of
transactions, which has the advantage that you can still DEALLOCATE inside
a transaction.

> >Just don't tell me that making
> >PREPARE respect rollbacks would break compatibility
>
> Why not? It's true.

So many things are true. But _not_ doing so also breaks compatibility,
so like I said, there are counterexamples.

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

Prepared queries _do_ change semantics of subsequent EXECUTE and PREPARE
statements--cause them to fail where they succeeded before; cause them
to succeed where they failed before; or make an EXECUTE do something not
necessarily related to what it did before. It just doesn't affect you
if you're totally aware of prepared statements throughout your path to
the backend.

I'm coming from the other side, where transactions are a primary semantic
concern but not everybody in the stack is aware of prepared statements,
or at least not in a coordinated way. In any case, I think the
pattern-matching approach I proposed earlier solves the problem for all
of us, and gives us more besides. Any comments on that?

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

I'm not entirely sure. What if setting a session variable triggers a
failure? I'm thinking along the lines of "oops, this data isn't valid
utf-8 after all; never mind that change in encoding." If you don't want
to have transactional behaviour, back we go to an escape syntax like I
suggested. But I think transactionality ought to be the default like it
is for everything else; ACID and correctness are a lot more important than
a modest amount of convenience in middleware code. And last but not least,
it's much easier to reason about a cleanly transactional system than about
one with holes and exceptions in it. That last point is much more
important to me than programming convenience. Remember that I too have had
to program around rollbacks to support session variables properly; I'll
gladly pay the price for a pure transactional model.

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

Haven't looked at them (I work on top of libpq), but you make it sound
like the whole PREPARE mess is just a special case of a problem with named
statements. Like I said before, the prepared-statements problem wouldn't
be here if only prepared statements were anonymous, or at least their names
weren't used in invocation. It's not the prepared plan that breaks
transactionality; it's the name.

So again, I'd like to hear any comments about my pattern-matching
proposal. Is there any problem here that it would not solve? Does anyone
see a problem in implementing them?

Jeroen