Re: About binaryTransfer.

Hi Dave, Mikko,

Sorry for slow response.

Mikko, thanks for cleaning things up.
My thought is all that Mikko says.

>> So the desired behaviour you want is what? You want the first query to
>> be able to use binary, or not ?
>>
I don't need using binary transfer for the first query.
But someone need and added this feature, I've thought to
remain this feature.

> I think the only problem with Tomonari's patch is some conventions
such as
> Capitalization of the first letter, and the use of a static field in the
> statement as opposed to a per statement setting
>
I've changed the behavior drastically for including
both "first query binaryTransfer" and "No extra round-trips".
As Dave says, this may cause some trouble...

Then, I create a new patch for REL9_3_STABLE.
Please see attached patch.
This patch will just reduce extra round-trips.
A extra round-trip would occur only once, but this can not be helped.

regards,
----------------
Tomonari Katsumata

(2014/04/15 22:12), Dave Cramer wrote:
> Mikko,
>
> Thanks for clearing that up.
>
> I think the only problem with Tomonari's patch is some conventions
such as
> Capitalization of the first letter, and the use of a static field in the
> statement as opposed to a per statement setting
>
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On 15 April 2014 08:53, Mikko Tiihonen
<Mikko(dot)Tiihonen(at)nitorcreations(dot)com>wrote:
>
>> I think the changes in the driver go like this:
>>
>>
>> 1) initial binary transfer implementation
>>
>> - first n queries use text and after that binary (no extra round-trips)
>>
>> - there was a debug flag to enable binary transfers for first query
(with
>> the cost of extra round-trip)
>>
>>
>> 2) someone wanted the binary transfers for one-shot operations too
>>
>> - but modified the code so that the extra driver-server round trip
cost is
>> now on _every_ execution of prepared statement
>>
>>
>> 3) tomonari created a patch the fixes the extra round-trip but still
>> allows binary transfers for first query - all configurable
>>
>>
>> Now the request is to either revert 2) change or apply 3) change. The
>> extra round-trip between each execution is really a killer for many
>> installations.
>>
>>
>> -Mikko
>> ------------------------------
>> *From:* davecramer(at)gmail(dot)com <davecramer(at)gmail(dot)com> on behalf of Dave
>> Cramer <pg(at)fastcrypt(dot)com>
>> *Sent:* 15 April 2014 15:34
>> *To:* Tomonari Katsumata
>> *Cc:* Tomonari Katsumata; Mikko Tiihonen; pgsql-jdbc(at)postgresql(dot)org
>> *Subject:* Re: [JDBC] About binaryTransfer.
>>
>> Tomonari,
>>
>> So the desired behaviour you want is what? You want the first query to
>> be able to use binary, or not ?
>>
>> Dave
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On 15 April 2014 04:20, Tomonari Katsumata <
>> katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>>
>>> Hi Dave,
>>>
>>>
>>>> I pulled this into master.
>>>>
>>> Thanks for merging my fix against master!
>>>
>>>
>>>> I'm not quite sure I want this back patched into
>>>> 9.3 though. This is new behaviour.
>>>>
>>> I think the original change(*) was done for rare case.
>>> But this change would cause a performance issue
>>> in many cases like me.
>>>
>>> So I want this fix into 9.3.
>>> If we cannot do so, we should revert
>>> the original change(*) at least.
>>>
(*)https://github.com/pgjdbc/pgjdbc/commit/dbf76c2d662896c5703cf20d7362e1
>>> d061e1e43f
>>>
>>> regards,
>>> ---------------
>>> Tomonari Katsumata
>>>
>>>
>>>>
>>>>
>>>> Dave Cramer
>>>>
>>>> dave.cramer(at)credativ(dot)ca
>>>> http://www.credativ.ca
>>>>
>>>>
>>>> On 3 April 2014 04:37, Tomonari Katsumata
>>>> <katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp>wrote:
>>>>
>>>>> Hi Dave,
>>>>>
>>>>> Did you check my pull-request?
>>>>> https://github.com/pgjdbc/pgjdbc/pull/128
>>>>>
>>>>> I don't want to use 9.2-1004 Driver, because it has some bugs
>>>>> about setQueryTimeout fixed by Heikki(*).
>>>>> (*) http://www.postgresql.org/message-id/52A0D28A.7040902@vmware.com
>>>>>
>>>>> So I need the PostgreSQL Driver 9.3-1101 have good performance
>>>>> like 9.2-1004 as soon as possible.
>>>>>
>>>>> Could you check it please?
>>>>> If I'm missing something, please tell me it!
>>>>>
>>>>> regards,
>>>>> ------------------------
>>>>> Tomonari Katsumata
>>>>>
>>>>>
>>>>> (2014/03/06 18:13), Tomonari Katsumata wrote:
>>>>>> Hi Dave,
>>>>>>
>>>>>> I've misunderstood the behavior of this problem.
>>>>>> The main problem is that the Describe message is send/receive
>>>>>> repeatedly, if user don't want to do so.
>>>>>>
>>>>>> The pull-request I've sent before(#124) didn't solve the problem.
>>>>>>
>>>>>> Now, I fixed it and sent a new pull-request.
>>>>>> https://github.com/pgjdbc/pgjdbc/pull/128
>>>>>>
>>>>>> Please check it!
>>>>>>
>>>>>> regards,
>>>>>> ----------------------
>>>>>> Tomonari Katsumata
>>>>>
>>>>>>
>>>>>> (2014/02/23 9:34), Tomonari Katsumata wrote:
>>>>>>> Hi Dave,
>>>>>>> I sent a Pull Request about this.
>>>>>>> https://github.com/pgjdbc/pgjdbc/pull/124
>>>>>>>
>>>>>>> regards,
>>>>>>> -------------------
>>>>>>> Tomonari Katsumata
>>>>>>>
>>>>>>>
>>>>>>> 2014-02-21 21:09 GMT+09:00 Dave Cramer <pg(at)fastcrypt(dot)com>:
>>>>>>>
>>>>>>>> Please submit a Pull Request
>>>>>>>>
>>>>>>>> Dave Cramer
>>>>>>>>
>>>>>>>> dave.cramer(at)credativ(dot)ca
>>>>>>>> http://www.credativ.ca
>>>>>>>>
>>>>>>>>
>>>>>>>> On Fri, Feb 21, 2014 at 3:57 AM, Tomonari Katsumata <
>>>>>>>> katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>>>>>>>>
>>>>>>>>> Hi Mikko,
>>>>>>>>>
>>>>>>>>> Thank you for the explanation.
>>>>>>>>>
>>>>>>>>> I agree with your proposal adding prepareThreshold=-1.
>>>>>>>>>
>>>>>>>>> If there are no objection, I'll do for it!
>>>>>>>>>
>>>>>>>>> regards,
>>>>>>>>> ----------------
>>>>>>>>> Tomonari Katsumata
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> (2014/02/21 16:50), Mikko Tiihonen wrote:
>>>>>>>>>> Before the patch the functionality was (if binaryTransfer=true):
>>>>>>>>>> - prepared statements after prepareThreshold were done in binary
>>>>> mode
>>>>>>>>>> - forceBinary=true could be enabled to force all statements
>>>>> (prepared +
>>>>>>>>> one-shot) to be executed in binary mode (at cost of extra
>>> round-trip)
>>>>>>>>>>
>>>>>>>>>> After the patch in question (if binaryTransfer=true):
>>>>>>>>>> - All prepared statements have extra round-trip before on first
>>> use
>>>>> and
>>>>>>>>> are immediately in binary mode
>>>>>>>>>> - forceBinary=true can be enabled to force also one-shot
>>> statements
>>>>> to
>>>>>>>>> be executed in binary mode (at cost of extra round-trip)
>>>>>>>>>>
>>>>>>>>>> Since there are users that use prepared statements in
one-shot way
>>>>>>>>> (prepare+execute+discard) the patch adds a mandatory extra
>>>>> round-trip for
>>>>>>>>> them.
>>>>>>>>>>
>>>>>>>>>> As a side note: the forceBinary is meant only as a debug flag
>>> (used
>>>>> for
>>>>>>>>> example in pgjdbc tests), not for production use.
>>>>>>>>>>
>>>>>>>>>> So the only thing the before-state could not do was to use
binary
>>>>>>>>> transfers for the first prepared statement execution. This is
>>> because
>>>>>>>>> setting prepareThreshold=0 disables the prepare instead of
>>> preparing
>>>>> before
>>>>>>>>> first use.
>>>>>>>>>>
>>>>>>>>>> I propose we revert that patch and instead add support for
>>>>>>>>> prepareThreshold=-1 which would force prepare+describe to be done
>>>>> even for
>>>>>>>>> the first execution. That would allow users to keep
controlling the
>>>>>>>>> behavior instead of forcing binary transfers immediately?
>>>>>>>>>> Alternatively we can separate the binary transfer logic from
>>>>> statement
>>>>>>>>> prepare threshold and add a separate binaryThreshold.
>>>>>>>>>>
>>>>>>>>>> -Mikko
>>>>>>>>>> ________________________________________
>>>>>>>>>> From:
pgsql-jdbc-owner(at)postgresql(dot)org<pgsql-jdbc-owner(at)postgresql(dot)
>>>>> org>
>>>>>>>>> on behalf of Tomonari Katsumata
<katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp>
>>>>>>>>>> Sent: 21 February 2014 08:40
>>>>>>>>>> To: pgsql-jdbc(at)postgresql(dot)org
>>>>>>>>>> Subject: [JDBC] About binaryTransfer.
>>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> I have a peformance trouble with 9.3-1100 driver.
>>>>>>>>>> Running same application(*) with 9.2-1004 and 9.3-1100,
>>>>>>>>>> It does another behavior.
>>>>>>>>>> (*) Retrieving 9990 rows with preparedStatement.
>>>>>>>>>>
>>>>>>>>>> 9.2-1004:
>>>>>>>>>> Always flags = 16.
>>>>>>>>>> ----
>>>>>>>>>> 14:09:55.730 (1) simple execute,
>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
>>>>>>>>> StatementResultHandler(at)8232a5d,
>>>>>>>>>> maxRows=0, fetchSize=0, flags=16
>>>>>>>>>> 14:09:55.878 (1) simple execute,
>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
>>>>>>>>> StatementResultHandler(at)34e671de,
>>>>>>>>>> maxRows=0, fetchSize=0, flags=16
>>>>>>>>>> ----
>>>>>>>>>>
>>>>>>>>>> 9.3-1100
>>>>>>>>>> Repeatedly flags = 48 and 16.
>>>>>>>>>> The count of "flags=16" is same with 9.2-1004, so
>>>>>>>>>> "flags=48" is extra executing.
>>>>>>>>>> ----
>>>>>>>>>> 14:20:34.991 (1) simple execute,
>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
>>>>>>>>> StatementResultHandler(at)19cdbc83,
>>>>>>>>>> maxRows=0, fetchSize=0, flags=48
>>>>>>>>>> 14:20:34.992 (1) simple execute,
>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
>>>>>>>>> StatementResultHandler(at)304b0cbc,
>>>>>>>>>> maxRows=0, fetchSize=0, flags=16
>>>>>>>>>> ----
>>>>>>>>>>
>>>>>>>>>> This change has caused by below commit.
>>>>>>>>>> https://github.com/pgjdbc/pgjdbc/commit/
>>>>> dbf76c2d662896c5703cf20d7362e1
>>>>>>>>> d061e1e43f
>>>>>>>>>>
>>>>>>>>>> It seems that binarytransfer mode is good at dealing with
>>>>>>>>>> big-data(many columns?many rows?), but some packets are
>>>>>>>>>> sent/received for this function, right?
>>>>>>>>>>
>>>>>>>>>> I want to make 9.3-1100 driver do old behavior like 9.2-1004.
>>>>>>>>>> What can I do ?
>>>>>>>>>>
>>>>>>>>>> regards,
>>>>>>>>>> ----------------
>>>>>>>>>> Tomonari Katsumata
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> 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
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>