Hello,
Let me ask your opinion on whether we can change the default value of UseDeclareFetch from 0 to 1, and change the
defaultbehavior upon error from statement rollback to transaction rollback. These default settings caused the
followingtrouble when some customer ran batch apps. The batch app prepares a SELECT statement with parameters, execute
itfive millions of times with different parameter values in a single transaction. They didn't experience a problem
withOracle.
* The client used too much memory and the OS crashed. This was because psqlODBC fetched all rows into the client
memoryat once.
* When they set UseDeclareFetch to 1, then the postgres process used too much memory and the OS crashed. This was
becausepsqlODBC starts and ends a subtransaction for each SQL statement. PostgreSQL creates one CurTransactionContext
memorycontext, which is 8KB, for each subtransaction and retain them until the top transaction ends. The total memory
usedbecomes 40GB (8KB * 5 million subtransactions.) This was avoided by setting the Protocol parameter to 7.4-1, which
doesn'tuse subtransactions.
Our other customers experienced similar problems with the JDBC driver and the psql command. They also fetch all rows
ofa result set by default.
The users are often not aware of the fetch size, because they didn't experience the problem with the same app when
usingOracle. For example, Oracle's ODBC driver uses 64,000 bytes of buffer on the client side to fetch rows from the
server. They complained about the PostgreSQL's behavior, and someone said "PostgreSQL is difficult to use, isn't it?"
So,I'd like to change the defaults to make PostgreSQL more friendly to users, particularly newcomers from other DBMSs.
Apart from this, I think we need to implement statement-level rollback on the server so that the client driver does not
haveto use savepoints. Using savepoints increases the number of round trips and degrade performance severely.
Comments would be appreciated.
Regards
Takayuki Tsunakawa