Re: [Re] Re: PREPARE and transactions

On Sat, Jul 03, 2004 at 11:17:18AM +1200, Oliver Jowett wrote:

> >So many things are true. But _not_ doing so also breaks compatibility,
> >so like I said, there are counterexamples.
>
> This is nonsense. Not changing the current behaviour cannot break
> compatibility, almost by definition.

Almost. But prepared statements can break compatibility with code not
aware of their existence yet--and in some cases, this does not happen if
they behave transactionally. It may not be a big deal, but I'm not
convinced that the effort of supporting rollbacks in middleware is such
a big waste of time either.

> Please do take a look. The V3 protocol treats the set of named
> statements as part of the connection state, not as anything at the SQL
> statement level. There are also named portals to deal with if your issue
> is that things shouldn't be named.

But neither of these pose as SQL statements. It's the SQL session that
I'm really worried about.

> The client has query-lifetime and query-reuse information that the
> server does not have and can't obtain via simple query matching. Also,
> clients need per-query control over use of PREPARE: prepared queries can
> run slower as they must use a more general query plan. I don't see how
> you overcome either of these if the server hides the mechanics of which
> query plans are preserved.

The converse is also true: a dynamic scheme may do better than a static
one. This often happens. We may even want to detect reusability on the
fly; that could be based on the same mechanism. And there's that idea of
sharing plans between backends that also comes into play.

> You could implement the pattern-matching logic as a passthrough layer in
> front of the server -- perhaps in something like pgpool? -- and
> translate to PREPARE based on patterns. Then your application can remain
> unaware of the translation to PREPARE for the most part, the only issue
> being name collision which in practice is simple to work around. But I
> don't see why you want this in the main server at all -- it's really a
> bandaid for applications that don't want to precisely control the
> prepared-statement behaviour themselves.

Don't want to, or perhaps can't. It may be hard for the application to
deallocate a statement, for instance, because the transaction failed
before it got to the DEALLOCATE and the middleware doesn't make it easy to
go back and fix that.

Jeroen