PREPARE and transactions

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