Re: PREPARE and transactions

On Wed, Jun 23, 2004 at 03:26:49PM -0400, Tom Lane wrote:

> > Even if the spec doesn't help, I think a statement prepared within a
> > transaction should definitely be deallocated at the end of the transaction.
>
> Uh, you do realize that Postgres does *everything* within a transaction?

Well, except prepared statements apparently; I'm not sure why they are an
exception.

When I say "within a transaction" as opposed to outside a transaction, I
mean of course an explicit transaction. If you want a prepared statement
to last throughout the session, I'd say it stands to reason that you
create it outside a transaction--in unfettered session context, so to
speak. I can't see how that would be either less intuitive or harder to
program in the client.

Maybe it would help to think of some precedents. Are there any actions
where it makes sense to disobey rollbacks? Counters spring to mind, but
I guess that's a technical necessity rather than an interface choice.
Session variables may be another one, but IIRC they become strictly
bracketed (when set inside a transaction, naturally) around 7.3. What
else?

> The above proposal would render prepared statements practically useless.

Could you elaborate? Wouldn't it be possible to cache the plans across
transactions like I suggested, reusing the old plan if the statement is
re-prepared with the same definition? Or are you saying it's possible,
but wouldn't be helpful?

Jeroen