BUG #2963: PQprepare and transactions.

The following bug has been logged online:

Bug reference: 2963
Logged by: DUVAL Jean-Pierre
Email address: jp(dot)duval(at)up-comp(dot)com
PostgreSQL version: 8.1.3
Operating system: Windows XP
Description: PQprepare and transactions.
Details:

To speed up programs, I call:
1) PQprepare("MyStatement") one time.
2) PQexecPrepared("MyStatement") many times.
3) PQexec("DEALLOCATE MyStatement") to deallocate the statement prepared.

This works with no transaction.

Case A :
--------
If I put all these three steps in a loop finished by a commit or rollback, I
cann't neither do again:
A) PQprepare("MyStatement") one time.
B) PQexecPrepared("MyStatement") many times.

Case B :
--------
Moreover, just after connecting, if I just do a rollback and then a begin
transaction, I cann't neither do at second iteration:
A) PQprepare("MyStatement") one time.
B) PQexecPrepared("MyStatement") many times.

It seems that PQprepare() is using something like DECLARE CURSOR WITHOUT
HOLD, that is impacted by transactions:
X) Either destroyed at the end of the transaction.
Y) Or not possible to do when a transaction is open.

It should be better to use WITH HOLD inside PQprepare() or to add a
parameter to do so. In this case the behavior of PostgreSQL to manage
prepared statements and transactions should be like the one of other
database engines - Db2, Informix, Oracle, Max DB, My Sql, Sql Server for
which I am sure of the behavior.

At this time, due to this bug, it is not possible to write a large batch
program handling millions of datas.

Jean-Pierre DUVAL - jp(dot)duva(at)up-comp(dot)com - www.up-comp.com