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

From Tsunakawa, Takayuki
Subject [RFC] Changing the default of UseDeclareFetch and Protocol (statement -> transaction rollback)
Date
Msg-id 0A3221C70F24FB45833433255569204D1F634C1A@G01JPEXMBYT05
Whole thread Raw
Responses Re: [RFC] Changing the default of UseDeclareFetch and Protocol (statement -> transaction rollback)
List pgsql-odbc
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



pgsql-odbc by date:

Previous
From: "Tsunakawa, Takayuki"
Date:
Subject: Re: DIAG [HY000] server closed the connection unexpectedly
Next
From: "Inoue, Hiroshi"
Date:
Subject: Re: xmin when linked tables are updated?