Re: [Re] Re: PREPARE and transactions

On Fri, Jun 25, 2004 at 02:00:12AM -0000, Greg Sabino Mullane wrote:

> I was originally unhappy with the current situation, but now I think
> it is the best. Any changes will also cause a huge further headache
> for driver/application writers, as we already have a released version
> (and probably at least one more) with the current behavior. I'd be

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?

There was also the middleware argument--some intermediate software layer
may be in control of bracketing. But in such cases, can you even rely
on two independent transactions executing in the same session? You'd
need to assume that to make the current semantics work in that situation.
What if the middleware does connection pooling, or restores a broken
connection between two transactions? The latter might happen because of
a timed-out firewall, for instance, when there is a long pause between
two unrelated transactions.

Besides, just the fact that current semantics are completely "out-of-band"
relative to bracketing, I guess it really ought to be any middleware's
responsibility to manage prepared statements. If the application isn't in
control of transactionality, it seems a little iffy to have it fire off
statements that don't affect database state but can make or break future
transactions.

As for the case where statements are prepared on demand when they are
first executed, wouldn't that be better done in the backend? It would
save the application this trouble of keeping track of which statements
have been prepared.

Perhaps the real problem is in the SQL syntax... Imagine a syntax that
doesn't assign a name to a prepared statement, just defines an anonymous
pattern to plan for. The backend would match against the pattern on the
fly, so introducing prepared statements in a program would involve no
changes apart from the PREPAREs. Implementations could ignore them if
they cached plans dynamically anyway; they could implement dynamic and
more effective replacement policies for prepared statements, and share
plans between connections.

Jeroen