Thread: [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
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

Hello,

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



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