Re: [Re] Re: PREPARE and transactions

On Fri, Jul 02, 2004 at 09:51:38PM -0000, Greg Sabino Mullane wrote:

> Specifically, your proposal (if I read it correctly) would require me
> to send in the full SQL statement every time, thus negating a major

Well, like I said, it wouldn't actually _require_ this; it would just
allow transactional semantics for the current, explicit syntax without
the actual optimization being rolled back.

A logical consequence of the implementation, I think, would be that
you could re-PREPARE a statement when in doubt, without paying the
parsing and planning cost again.

> benefit of prepared statements in that I do not have to quote the
> values myself, but can let the server handle it. It would also be a lot
> of work for the server to parse the statement and find a "match".
> Maybe enough work to make the "solution" worse than the cure.

The algorithm as I've got it worked out so far doesn't look very costly,
unless you have lots and lots of highly similar prepared statements, or
queries so long that their sheer text size becomes the problem. There's
no actual extra parsing involved, as far as I can see, just pattern
matching and the extraction of the variables.

As you say, however, quoting of parameters remains...

> Finally, I am still not convinced there is a problem at all. While the
> current behavior is a bit quirky, I think it does work out overall.
> A prepared statement will throw an error on two conditions:
>
> 1) The name is already in use.

Presumably you're taking about a prepare statement here, not a prepared
statement.

> This is trivial to solve by the client, so it should never arise.
> (with the caveat that middleware should clearly document its naming
> scheme, and request and/or enforce that the client not creating
> conflicting names). So we really should remove this from the debate.

In other words, it's only trivial to solve by the client if there is no
possible conflict over where and how the statement got prepared. You
say it's trivial, but you're also assuming that the work to ensure this
has already been done. Most problems become trivial that way!

> 2) The statement is invalid.
> This one is very common, and it /should/ throw an error. With the fact
> that statements cannot be shared across connections, and the handy
> PQtransactionStatus function, the client should easily be able to
> handle any situation that arises smoothly.

Of course you shouldn't forget this one:

3) The PREPARE is executed inside a transaction that has already run
into an error.

> Having the statement exist even on rollback is a little harder to
> accept, but I have yet to see a better alternative. Prepare statements
> already "break the rules" anyway, by the fact that a commit() after a
> prepare does not make them available to any other people viewing
> the database.

But that's no different with session variables. They're transactional,
and they affect session state only. There is a place reserved for session
state as well as transaction state and database state. No news there. What
bugs me is that PREPARE introduces another kind of session state into the
mix, one that doesn't allow you to get out of a fix by aborting the
transaction and continuing the session.

Jeroen