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

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".
>
>