Re: [RFC] How about changing the default value of defaultRowFetchSize?

Hello,

Let me ask your opinion on whether the default value of defaultRowFetchSize can or should be changed. The current default value is 0, which means the driver fetches all rows at once when the result set is opened. I'd like to propose changing it to some small positive value because:

* It sometimes causes out-of-memory trouble when users' batch apps process many rows on the client side. They are often not aware of the fetch size, because they didn't experience the problem with the same app when using Oracle. Oracle's default fetch size is 10. They complained about the PostgreSQL's behavior. (OTOH, MySQL's JDBC driver fetches all rows by default like PostgreSQL.)

* If the performance is better when the fetch size is 0, then those who are keen to improve performance can find the method for tuning fetch size. I think it would be better to make the default setting more friendly for people who aren't so conscious of tuning.

Regards
Takayuki Tsunakawa

Hi,

It is important to note that fetch size only works when autocommit is off.
The implication here is:
1) fetch size has no bearing whatsoever for most people.
2) You have to know that fetch size only works in a transaction so you
should be sophisticated enough to set it.
OTOH, I have no objection to setting it to say 100

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 20 October 2016 at 03:55, Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:

> Hello,
>
> Let me ask your opinion on whether the default value of
> defaultRowFetchSize can or should be changed. The current default value is
> 0, which means the driver fetches all rows at once when the result set is
> opened. I'd like to propose changing it to some small positive value
> because:
>
> * It sometimes causes out-of-memory trouble when users' batch apps process
> many rows on the client side. They are often not aware of the fetch size,
> because they didn't experience the problem with the same app when using
> Oracle. Oracle's default fetch size is 10. They complained about the
> PostgreSQL's behavior. (OTOH, MySQL's JDBC driver fetches all rows by
> default like PostgreSQL.)
>
> * If the performance is better when the fetch size is 0, then those who
> are keen to improve performance can find the method for tuning fetch size.
> I think it would be better to make the default setting more friendly for
> people who aren't so conscious of tuning.
>
>
> Regards
> Takayuki Tsunakawa
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

On 2016-10-20 09:55, Tsunakawa, Takayuki wrote:
> Hello,
>
> Let me ask your opinion on whether the default value of
> defaultRowFetchSize can or should be changed. The current default
> value is 0, which means the driver fetches all rows at once when the
> result set is opened. I'd like to propose changing it to some small
> positive value because:
>
> * It sometimes causes out-of-memory trouble when users' batch apps
> process many rows on the client side. They are often not aware of the
> fetch size, because they didn't experience the problem with the same
> app when using Oracle. Oracle's default fetch size is 10. They
> complained about the PostgreSQL's behavior. (OTOH, MySQL's JDBC driver
> fetches all rows by default like PostgreSQL.)
>
> * If the performance is better when the fetch size is 0, then those
> who are keen to improve performance can find the method for tuning
> fetch size. I think it would be better to make the default setting
> more friendly for people who aren't so conscious of tuning.

Note that - with some between the lines reading of the JDBC API +
specification, fetchSize 0 means: use driver default.

I infer this from setFetchSize:

> If the value specified is zero, then the hint is ignored. The default
> value is zero.

and getFetchSize:

> If this Statement object has not set a fetch size by calling the
> method setFetchSize, the return value is implementation-specific.

In other words, the default fetch size is zero (setFetchSize), and if
setFetchSize has never been called, then the default fetch size is
implementation-specific, which to my interpretation mean 0 ->
implementation-specific default

In other words, PostgreSQL JDBC could leave the default at 0, but then
use a default fetch size.

Mark

On Thu, Oct 20, 2016 at 6:53 AM, Mark Rotteveel <mark(at)lawinegevaar(dot)nl>
wrote:

>
> In other words, PostgreSQL JDBC could
> ​​
> leave the default at 0, but then use a default fetch size.
>
>
​
That's seems wrong, the Fetch Size should be correctly reported, the
defaultRowFetchSize property can be changed to 100 but a call to
getFetchSize() should return 100 not 0.

Based on the driver docs:
"defaultRowFetchSize: Default parameter for
java.sql.Statement#getFetchSize(). A value of 0 means that need fetch all
rows at once"

The value of 0 should be used to fetch all rows, if we ​leave the default
at 0 and handle internally that as 100 (and reporting it as 0) it is a
wrong behavior.

​Regards,​

Jorge>The value of 0 should be used to fetch all rows, if we ​leave the
default at 0 and handle internally that as 100 (and reporting it as 0) it
is a wrong behavior.

Note that "unset" and "set to 0" might have different meanings for a good
(or bad?) reason.

There's non-zero overhead when using low fetch sizes for narrow tables:
https://github.com/pgjdbc/pgjdbc/issues/292#issuecomment-107249028
E.g. 1.7 times difference for fetchsize 1000 and 100 for fetching 2000 rows
of 4 int4 over localhost connection.

Ideally, I would like the backend to support "fetch at most 10000 rows and
at most 1MiB" kind of requests.

Unfortunately, PG does not yet support "byte size limited" fetches, so we
might implement some "machine learning" trick: "fetch 100 rows, calculate
average row size, then adjust fetch size so subsequent fetch would be close
to the desired "fetch byte length"

Anyway, I think it makes sense to switch to some non-zero value for
defaultRowFetchSize, then implement "fetch size autoscaling".

Vladimir

On Thu, Oct 20, 2016 at 10:33 AM, Vladimir Sitnikov <
sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

> Jorge>The value of 0 should be used to fetch all rows, if we ​leave the
> default at 0 and handle internally that as 100 (and reporting it as 0) it
> is a wrong behavior.
>
> Note that "unset" and "set to 0" might have different meanings for a good
> (or bad?) reason.
>
>

​Yes, but "by default"​ is set to 0, not "unset (null)":

> /**
> * Default parameter for {(at)link java.sql.Statement#getFetchSize()}. A
> value of {(at)code 0} means
> * that need fetch all rows at once
> */
> DEFAULT_ROW_FETCH_SIZE("defaultRowFetchSize", "0",
> "Positive number of rows that should be fetched from the database
> when more rows are needed for ResultSet by each fetch iteration"),
>

​So if the driver internally handle fetch size 0 == fetch size 100, that's
what i call an hidden inconsistent behavior.

Don't get me wrong, it make sense to switch ​defaultRowFetchSize to "100",
but a call to getFetchSize() should be == 100, not 0.

> There's non-zero overhead when using low fetch sizes for narrow tables:
> https://github.com/pgjdbc/pgjdbc/issues/292#issuecomment-107249028
> E.g. 1.7 times difference for fetchsize 1000 and 100 for fetching 2000
> rows of 4 int4 over localhost connection.
>
> Ideally, I would like the backend to support "fetch at most 10000 rows and
> at most 1MiB" kind of requests.
>
> Unfortunately, PG does not yet support "byte size limited" fetches, so we
> might implement some "machine learning" trick: "fetch 100 rows, calculate
> average row size, then adjust fetch size so subsequent fetch would be close
> to the desired "fetch byte length"
>
>
​This is called Adaptive Buffering in SQL Server JDBC Driver, perhaps it
helps inspire:
https://msdn.microsoft.com/en-us/library/bb879937(v=sql.110).aspx​

>
> Anyway, I think it makes sense to switch to some non-zero value for
> ​​
> defaultRowFetchSize, then implement "fetch size autoscaling".
>
>

On 20 October 2016 at 13:30, Jorge Solórzano <jorsol(at)gmail(dot)com> wrote:

> On Thu, Oct 20, 2016 at 10:33 AM, Vladimir Sitnikov <
> sitnikov(dot)vladimir(at)gmail(dot)com> wrote:
>
>> Jorge>The value of 0 should be used to fetch all rows, if we ​leave the
>> default at 0 and handle internally that as 100 (and reporting it as 0) it
>> is a wrong behavior.
>>
>> Note that "unset" and "set to 0" might have different meanings for a good
>> (or bad?) reason.
>>
>>
>
> ​Yes, but "by default"​ is set to 0, not "unset (null)":
>
>> /**
>> * Default parameter for {(at)link java.sql.Statement#getFetchSize()}. A
>> value of {(at)code 0} means
>> * that need fetch all rows at once
>> */
>> DEFAULT_ROW_FETCH_SIZE("defaultRowFetchSize", "0",
>> "Positive number of rows that should be fetched from the database
>> when more rows are needed for ResultSet by each fetch iteration"),
>>
>
> ​So if the driver internally handle fetch size 0 == fetch size 100, that's
> what i call an hidden inconsistent behavior.
>
> Don't get me wrong, it make sense to switch ​defaultRowFetchSize to "100",
> but a call to getFetchSize() should be == 100, not 0.
>

As I said 99% of the code out there will never use this as fetch size is
totally useless in autocommit mode. Also an inconsistent behaviour. There a
lots of them.. Setting it internally is fine IMO. I also think it should be
reported as 0 unless the user explicitly sets it. What we do inside the
driver is our business.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 20-10-2016 17:59, Jorge Solórzano wrote:
> On Thu, Oct 20, 2016 at 6:53 AM, Mark Rotteveel <mark(at)lawinegevaar(dot)nl
> <mailto:mark(at)lawinegevaar(dot)nl>> wrote:
>
>
> In other words, PostgreSQL JDBC could
> ​​
> leave the default at 0, but then use a default fetch size.
>
>
> ​
> That's seems wrong, the Fetch Size should be correctly reported, the
> defaultRowFetchSize property can be changed to 100 but a call to
> getFetchSize() should return 100 not 0.
>
> Based on the driver docs:
> "defaultRowFetchSize: Default parameter for
> java.sql.Statement#getFetchSize(). A value of 0 means that need fetch
> all rows at once"
>
> The value of 0 should be used to fetch all rows, if we ​leave the
> default at 0 and handle internally that as 100 (and reporting it as 0)
> it is a wrong behavior.

My reply was purely from the perspective of the JDBC specification, not
how the PostgreSQL JDBC driver currently implements it.

According to the JDBC specification:

"The methods getFetchDirection and getFetchSize return the current value
of the hints. If either of these methods is called before the
corresponding setter method has been called, the value returned is
implementation-defined."

In other words, the default fetch size is implementation defined,
furthermore the javadoc of Statement.setFetchSize implies that setting
it to value 0 will revert to the default (whatever that is).

Reading my previous reply back, I could have formulated a bit better,
but what I meant to say is that the fact that PostgreSQL current uses
"fetch all" as the default is not at all mandated by the standard, and
using a different default is perfectly fine from the perspective of JDBC.

However doing that would - for the PostgreSQL JDBC driver - change the
meaning of calling Statement.setFetchSize(0) from "fetch all" to "fetch
with the default fetch size" (although those interpretations are the
same for current versions of the driver).

Mark

--
Mark Rotteveel

On 20 October 2016 at 13:51, Mark Rotteveel <mark(at)lawinegevaar(dot)nl> wrote:

> On 20-10-2016 17:59, Jorge Solórzano wrote:
>
>> On Thu, Oct 20, 2016 at 6:53 AM, Mark Rotteveel <mark(at)lawinegevaar(dot)nl
>> <mailto:mark(at)lawinegevaar(dot)nl>> wrote:
>>
>>
>> In other words, PostgreSQL JDBC could
>> ​​
>> leave the default at 0, but then use a default fetch size.
>>
>>
>> ​
>> That's seems wrong, the Fetch Size should be correctly reported, the
>> defaultRowFetchSize property can be changed to 100 but a call to
>> getFetchSize() should return 100 not 0.
>>
>> Based on the driver docs:
>> "defaultRowFetchSize: Default parameter for
>> java.sql.Statement#getFetchSize(). A value of 0 means that need fetch
>> all rows at once"
>>
>> The value of 0 should be used to fetch all rows, if we ​leave the
>> default at 0 and handle internally that as 100 (and reporting it as 0)
>> it is a wrong behavior.
>>
>
> My reply was purely from the perspective of the JDBC specification, not
> how the PostgreSQL JDBC driver currently implements it.
>
> According to the JDBC specification:
>
> "The methods getFetchDirection and getFetchSize return the current value
> of the hints. If either of these methods is called before the corresponding
> setter method has been called, the value returned is
> implementation-defined."
>
> In other words, the default fetch size is implementation defined,
> furthermore the javadoc of Statement.setFetchSize implies that setting it
> to value 0 will revert to the default (whatever that is).
>

Even more of an argument to leave it returning 0 unless it is explicitly set

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 20-10-2016 19:30, Jorge Solórzano wrote:
> ​So if the driver internally handle fetch size 0 == fetch size 100,
> that's what i call an hidden inconsistent behavior.
>
> Don't get me wrong, it make sense to switch ​defaultRowFetchSize to
> "100", but a call to getFetchSize() should be == 100, not 0.

And that is how it should work, however according to the JDBC
specification (or at least: that is how I read it),
Statement.setFetchSize(0) means "use driver default fetch size".

On the other hand, drivers are also free to ignore fetch size hints.

Mark
--
Mark Rotteveel

On Thu, Oct 20, 2016 at 11:55 AM, Mark Rotteveel <mark(at)lawinegevaar(dot)nl>
wrote:

> On 20-10-2016 19:30, Jorge Solórzano wrote:
>
>> ​So if the driver internally handle fetch size 0 == fetch size 100,
>> that's what i call an hidden inconsistent behavior.
>>
>> Don't get me wrong, it make sense to switch ​defaultRowFetchSize to
>> "100", but a call to getFetchSize() should be == 100, not 0.
>>
>
> And that is how it should work, however according to the JDBC
> specification (or at least: that is how I read it),
> Statement.setFetchSize(0) means "use driver default fetch size".
>

To how I read the api (http://docs.oracle.com/javase/7/docs/api/java/sql/
Statement.html#setFetchSize(int)):

"​If the value specified is zero, then the hint is ignored. The default
value is zero."

It don't really said "use default fetch size", it said the hint is ignored.
IMO the api is a little ambiguous but the api and specification in "*13.5
Performance Hints*" reads:

>
>
> *The methods getFetchDirection and getFetchSize return the current value
> of thehints. If either of these methods is called before the corresponding
> setter method hasbeen called, the value returned is implementation-defined.*
>

​So is up to drivers choice what to return as "default"​

​if a setFetchSize​ is not called, so again IMO it should return 100 not 0.

Anyway, as Dave mentioned, this is just a "hint", and most apps need a
certain scenario to actually use it (autocommit=off, type_forward_only,
single statement). "What we do inside the driver is our business" is fine
as long as you stick with the standard.

> On the other hand, drivers are also free to ignore fetch size hints.
>
> Mark
> --
> Mark Rotteveel
>

Hi, all,

Thanks for many comments and information. First, let me make consensus on the interpretation of JDBC spec.

The value given to setFetchSize() is a hint, not the actual number of rows. The default hint is 0. e.g. The Java SE 7 API doc says:

"Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero."

On the other hand, the JDBC spec and the API doc say different thing regarding the return value of getFetchSize(). The JDBC doc says that it returns the hint value, while the API doc says that it returns the number of rows (not the hint).

[JDBC spec]
"The methods getFetchDirection and getFetchSize return the current value of the
hints. If either of these methods is called before the corresponding setter method has
been called, the value returned is implementation-defined."

[Java SE 7 API doc]
"Retrieves the number of result set rows that is the default fetch size for ResultSet objects generated from this Statement object. If this Statement object has not set a fetch size by calling the method setFetchSize, the return value is implementation-specific."

However, the API doc should be correct. If the return value were the hint value, it should be 0 before calling setFetchSize(). But both docs say the return value is "implementation-defined." The implementation-defined value is the number of rows when the current hint is 0.

Then, I'd like to propose the following behavior. Is this OK?

* The default value of defaultRowFetchSize is 100.

* When setFetchSize() is not called, or setFetchSize(0) was called
getFetchSize() returns 100.
The actual number of rows fetched is 100.

* When setFetchSize() was called with a non-zero argument value
getFetchSize() returns that value.
The actual number of rows fetched is that value.

It's unfortunate that the fetch size works only when autocommit is off, but the above change will mitigate the risk of bad impression that newcomers from other DBMSs may have on PostgreSQL.

Should I submit a patch, or is there someone who wants to do it?

Regards
Takayuki Tsunakawa

From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Tsunakawa, Takayuki
> * When setFetchSize() is not called, or setFetchSize(0) was called
> getFetchSize() returns 100.
> The actual number of rows fetched is 100.

Sorry, not 100, but the value of defaultRowFetchSize.

Regards
Takayuki Tsunakawa

How in this case one would enable previous behavior (fetching all)?

Also as for me, 100 is too low. I'd make it 1000 and make configurable from
the uri (defaultFetchSize).

Best regards, Vitalii Tymchyshyn

Пт, 21 жовт. 2016 00:34 користувач Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> пише:

> From: pgsql-jdbc-owner(at)postgresql(dot)org
> > [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Tsunakawa,
> Takayuki
> > * When setFetchSize() is not called, or setFetchSize(0) was called
> > getFetchSize() returns 100.
> > The actual number of rows fetched is 100.
>
> Sorry, not 100, but the value of defaultRowFetchSize.
>
> Regards
> Takayuki Tsunakawa
>

The only thing I disagree with is returning the internal number. 0 means it
has not been set.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 October 2016 at 08:12, Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:

> How in this case one would enable previous behavior (fetching all)?
>
> Also as for me, 100 is too low. I'd make it 1000 and make configurable
> from the uri (defaultFetchSize).
>
> Best regards, Vitalii Tymchyshyn
>
> Пт, 21 жовт. 2016 00:34 користувач Tsunakawa, Takayuki <
> tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> пише:
>
>> From: pgsql-jdbc-owner(at)postgresql(dot)org
>> > [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Tsunakawa,
>> Takayuki
>> > * When setFetchSize() is not called, or setFetchSize(0) was called
>> > getFetchSize() returns 100.
>> > The actual number of rows fetched is 100.
>>
>> Sorry, not 100, but the value of defaultRowFetchSize.
>>
>> Regards
>> Takayuki Tsunakawa
>>
>

On Sun, Oct 23, 2016 at 5:39 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> The only thing I disagree with is returning the internal number. 0 means
> it has not been set.
>

​defaultRowFetchSize is not an internal number.​ If you set the property
you get the value of the property.

>
> Dave Cramer
>

On 23 October 2016 at 18:03, Jorge Solórzano <jorsol(at)gmail(dot)com> wrote:

>
> On Sun, Oct 23, 2016 at 5:39 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> The only thing I disagree with is returning the internal number. 0 means
>> it has not been set.
>>
>
> ​defaultRowFetchSize is not an internal number.​ If you set the property
> you get the value of the property.
>
>
That I agree with. If you don't set it you get 0

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>
>
>>
>> Dave Cramer
>>
>

From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
> ​defaultRowFetchSize is not an internal number.​ If you set the
> property you get the value of the property.
>
>
>
>
> That I agree with. If you don't set it you get 0

You will get the value of defaultRowFetchSize if you don't call setFetchSize(), and the default value of defaultRowFetchSize will be, say, 100. As I mentioned in the previous mail, I think getFetchSize() returns the actual number of rows, not the hint (0).

Any comments on the default value of defaultRowFetchSize? There was a request for 1,000. I think 100 would be sufficient, considering the typical web pagination, for example. Those who want to speed up batch apps by reducing round trips can find the parameter and/or setFetchSize() and use them.

Regards
Takayuki Tsunakawa

On 23 October 2016 at 23:12, Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:

> From: pgsql-jdbc-owner(at)postgresql(dot)org
> > [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
> > ​defaultRowFetchSize is not an internal number.​ If you set the
> > property you get the value of the property.
> >
> >
> >
> >
> > That I agree with. If you don't set it you get 0
>
> You will get the value of defaultRowFetchSize if you don't call
> setFetchSize(), and the default value of defaultRowFetchSize will be, say,
> 100. As I mentioned in the previous mail, I think getFetchSize() returns
> the actual number of rows, not the hint (0).
>

This is what I take issue with. It should return 0 which means that it has
not been set. What use is it to the user ?

>
> Any comments on the default value of defaultRowFetchSize? There was a
> request for 1,000. I think 100 would be sufficient, considering the
> typical web pagination, for example. Those who want to speed up batch apps
> by reducing round trips can find the parameter and/or setFetchSize() and
> use them.
>

1000 has been determined to be optimal. Both Vladimir and I have done
performance testing on this. Also it won't matter to the user, since if
they only have 100 rows they will only get 100 rows back anyway. 1000 rows
should be able to fit into any reasonable client machines memory

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>
>

From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of
> Dave Cramer
> This is what I take issue with. It should return 0 which means that it has
> not been set. What use is it to the user ?

I'm sorry to repeat myself. According to the API doc, the use of getFetchSize() is to retrieve the number of result set rows, not the hint value. So, I thought 0 was not appropriate because 0 is only a valid hint value passed to setFetchSize(). Which sentence in what document does your returning 0 follow?

http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html

Retrieves the number of result set rows that is the default fetch size for ResultSet objects generated from this Statement object. If this Statement object has not set a fetch size by calling the method setFetchSize, the return value is implementation-specific.

Returns:the default fetch size for result sets generated from this Statement object

> 1000 has been determined to be optimal. Both Vladimir and I have done
> performance testing on this. Also it won't matter to the user, since if
> they only have 100 rows they will only get 100 rows back anyway. 1000 rows
> should be able to fit into any reasonable client machines memory

OK, agreed.

Regards
Takayuki Tsunakawa

On 24 October 2016 at 23:15, Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:

> From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of
> > Dave Cramer
> > This is what I take issue with. It should return 0 which means that it
> has
> > not been set. What use is it to the user ?
>
> I'm sorry to repeat myself. According to the API doc, the use of
> getFetchSize() is to retrieve the number of result set rows, not the hint
> value. So, I thought 0 was not appropriate because 0 is only a valid hint
> value passed to setFetchSize(). Which sentence in what document does your
> returning 0 follow?
>
> http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html
>
> Retrieves the number of result set rows that is the default fetch size for
> ResultSet objects generated from this Statement object.

>
> *If this Statement object has not set a fetch size by calling the method
> setFetchSize, the return value is implementation-specific.*

This states that if setFetchSize has not been called then we return what we
want. Given that if the statement is in auto-commit then the fetch size is
irrelevant. The correct logic would be if autocommit=false then return the
default value, otherwise 0, but I'm not advocating this either.

So my question to you is how would you use this information anyway? It's
not like you can allocate more memory or something to accommodate the rows.
It makes more sense to me that if I get 0 back then I know I have to set
it. If I get the value back that I set it to then I know what's going on. I
would assert that anyone that is knowledgable enough to use this is going
to call setFetchSize.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>

From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of
> Dave Cramer
> This states that if setFetchSize has not been called then we return what
> we want. Given that if the statement is in auto-commit then the fetch size
> is irrelevant. The correct logic would be if autocommit=false then return
> the default value, otherwise 0, but I'm not advocating this either.
>
> So my question to you is how would you use this information anyway? It's
> not like you can allocate more memory or something to accommodate the rows.
> It makes more sense to me that if I get 0 back then I know I have to set
> it. If I get the value back that I set it to then I know what's going on.
> I would assert that anyone that is knowledgable enough to use this is going
> to call setFetchSize.

I got your point, thanks. I agree that getFetchSize() returns 0 when setFetchSize() hasn't been called yet or setFetchSize(0) was called. Users can interpret the return value of 0 as (1) defaultRowFetchSize if autocommit is off, or (2) all rows if autocommit is on.

I think I'll submit a patch. Of course, I don't mind if anyone will do it.

Regards
Takayuki Tsunakawa

Hi,

Look to this PR https://github.com/pgjdbc/pgjdbc/pull/675 it should solve
problem "which defaultRowFetchSize value we must choose".

2016-10-26 5:02 GMT+03:00 Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>:

> From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of
> > Dave Cramer
> > This states that if setFetchSize has not been called then we return what
> > we want. Given that if the statement is in auto-commit then the fetch
> size
> > is irrelevant. The correct logic would be if autocommit=false then return
> > the default value, otherwise 0, but I'm not advocating this either.
> >
> > So my question to you is how would you use this information anyway? It's
> > not like you can allocate more memory or something to accommodate the
> rows.
> > It makes more sense to me that if I get 0 back then I know I have to set
> > it. If I get the value back that I set it to then I know what's going on.
> > I would assert that anyone that is knowledgable enough to use this is
> going
> > to call setFetchSize.
>
> I got your point, thanks. I agree that getFetchSize() returns 0 when
> setFetchSize() hasn't been called yet or setFetchSize(0) was called. Users
> can interpret the return value of 0 as (1) defaultRowFetchSize if
> autocommit is off, or (2) all rows if autocommit is on.
>
> I think I'll submit a patch. Of course, I don't mind if anyone will do it.
>
> Regards
> Takayuki Tsunakawa
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

From: Vladimir Gordiychuk [mailto:folyga(at)gmail(dot)com]
> Look to this PR https://github.com/pgjdbc/pgjdbc/pull/675 it should solve
> problem "which defaultRowFetchSize value we must choose".

Thanks for the info. So a cleverer solution is being discussed, isn't it? I'd like to wait and see how this will go.

Regards
Takayuki Tsunakawa