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

Hello,

The attached patch does the following proposal. Please review and apply it.

> -----Original Message-----
> From: pgsql-odbc-owner(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Tsunakawa, Takayuki
> Sent: Friday, October 21, 2016 3:25 PM
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: [ODBC] [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