We were discussing prepared statement support for libpqxx just now (Bruce,
Peter Eisentraut & myself are manning the postgres booth at LinuxTag 2004
in Karlsruhe, Germany), when we ran into a problem that came up two months
ago.  That discussion follows:

Post by Alvaro Herrera:
> Hackers,
> 
> Is this expected?  If so, why?  I'd expect the prepared stmt to be
> deallocated.
> 
> alvherre=# begin;
> BEGIN
> alvherre=# prepare tres as select 3;
> PREPARE
> alvherre=# rollback;
> ROLLBACK
> alvherre=# execute tres;
> ?column? 
> ----------
> 3
> (1 fila)

Followup by Tom Lane:
> prepare.c probably should have provisions for rolling back its state to
> the start of a failed transaction ... but it doesn't.
> 
> Before jumping into doing that, though, I'd want to have some
> discussions about the implications for the V3 protocol's notion of
> prepared statements.  The protocol spec does not say anything that
> would suggest that prepared statements are lost on transaction rollback,
> and offhand it seems like they shouldn't be because the protocol is
> lower-level than transactions.

Now, here's a scenario that has us worried:

BEGIN
  PREPARE foo AS ...
  ...			[error]
  DEALLOCATE foo 	[fails: already aborted by previous error]
ABORT
BEGIN
  PREPARE foo AS ...  	[fails: foo is already defined!]
  EXECUTE foo		[fails: already aborted by previous error]
COMMIT			[fails: already aborted by previous error]
 
You could say that the DEALLOCATE in the first transaction should have
been outside the transaction, i.e. after the ABORT.  But that would mean
that the client is expected to roll back, manually, individual changes
made in an aborted transaction.  If that's what we expect from the client,
what's the point in having transactions in the first place?

Lots of variations of the scenario spring to mind.  Imagine the second
transaction were not a transaction at all: the second PREPARE would fail,
and the EXECUTE may go execute the wrong statement.

A partial fix would be to allow identical redefinitions of a prepared
statement, optionally with reference counting to determine when it should
be deallocated.  But instances of the same transaction may want to include
a pseudo-constant in the fixed part of the query text that changes between
instances of the transaction.

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.
If it turns out that this wastes a lot of opportunities for reuse, the
prepared plans can always be cached across definitions.


Jeroen


Wed, 23 Jun 2004 18:16:36 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
PREPARE and transactions
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> 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?
The above proposal would render prepared statements practically useless.

			regards, tom lane

Wed, 23 Jun 2004 15:26:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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


Thu, 24 Jun 2004 11:20:39 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: PREPARE and transactions