Обсуждение: Connection Properties for FetchSize and Autocommit

Hi pgjdbc coders,

I recently had to deal with a 3rd party application (without source code) that processed large result sets, and kept running out of memory on the client.  I realise this was because the application didn't set a fetch size, nor turn off autocommit and so the criteria for the driver to use a cursor-based result set were not met.

Since I cannot modify the application, I instead altered the postgresql jdbc driver instead to allow two additional driver parameters - defaultFetchSize and defaultAutoCommit to override the built-in defaults (0 and true).

Anyway, it worked well for me, so for what its worth a diff is attached.  All test cases still pass. If you think this is worth pursuing, let me know what else needs to be done!

David Langton

David Langton wrote:
> defaultAutoCommit

That seems a pretty dangerous setting to have :/

-O

> David Langton wrote:
>>
>> defaultAutoCommit
>
> That seems a pretty dangerous setting to have :/
>
> -O
>

I'm not sure!  Without this parameter, you get a connection in
autocommit mode anyway.  If you actually set the mode yourself you get
whatever it is you want.  This parameter simply allows you to
initially set autocommit to false in the url for those (rare)
situations where it is what you want, but you can't modify the calling
application.

However, I'm certainly not any sort of JDBC expert, it just helped me
out a lot to be able to do this (along with fetchsize) to avoid
getting the whole result set sent to the driver all at once.  If I
could alter the application, I would have (and that would have been a
better place to do it!).

Regards

David Langton wrote:
>> David Langton wrote:
>>> defaultAutoCommit
>> That seems a pretty dangerous setting to have :/
>>
>> -O
>>
>
> I'm not sure!  Without this parameter, you get a connection in
> autocommit mode anyway.  If you actually set the mode yourself you get
> whatever it is you want.  This parameter simply allows you to
> initially set autocommit to false in the url for those (rare)
> situations where it is what you want, but you can't modify the calling
> application.
>
> However, I'm certainly not any sort of JDBC expert, it just helped me
> out a lot to be able to do this (along with fetchsize) to avoid
> getting the whole result set sent to the driver all at once.  If I
> could alter the application, I would have (and that would have been a
> better place to do it!).

The issue I see is that it can cause silent data loss. An application
that expects the documented API default - autocommit on - and just does
some UPDATEs is going to throw away all its changes if autocommit is
mysteriously off by default, because it's never going to call commit().

-O

>
> The issue I see is that it can cause silent data loss. An application that
> expects the documented API default - autocommit on - and just does some
> UPDATEs is going to throw away all its changes if autocommit is mysteriously
> off by default, because it's never going to call commit().
>
> -O
>

Yes, of course you are right and it would be very bad indeed.  I guess
I wasn't thinking of the impact properly (the app I am working with
isn't doing updates).

Maybe I'll revisit this again one day, but think about it a bit more first!