The following bug has been logged online:
Bug reference: 2963
Logged by: DUVAL Jean-Pierre
Email address: jp.duval@up-comp.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.duva@up-comp.com - www.up-comp.com
