[RFC] Changing the default of UseDeclareFetch and Protocol (statement -> transaction rollback)

Hello,

Let me ask your opinion on whether we can change the default value of UseDeclareFetch from 0 to 1, and change the default behavior upon error from statement rollback to transaction rollback. These default settings caused the following trouble when some customer ran batch apps. The batch app prepares a SELECT statement with parameters, execute it five millions of times with different parameter values in a single transaction. They didn't experience a problem with Oracle.

* The client used too much memory and the OS crashed. This was because psqlODBC fetched all rows into the client memory at once.

* When they set UseDeclareFetch to 1, then the postgres process used too much memory and the OS crashed. This was because psqlODBC starts and ends a subtransaction for each SQL statement. PostgreSQL creates one CurTransactionContext memory context, which is 8KB, for each subtransaction and retain them until the top transaction ends. The total memory used becomes 40GB (8KB * 5 million subtransactions.) This was avoided by setting the Protocol parameter to 7.4-1, which doesn't use subtransactions.

Our other customers experienced similar problems with the JDBC driver and the psql command. They also fetch all rows of a 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 using Oracle. 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 have to use savepoints. Using savepoints increases the number of round trips and degrade performance severely.

Comments would be appreciated.

Regards
Takayuki Tsunakawa