Re: About binaryTransfer.

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<mailto: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/dbf76c2d662896c5703cf20d7362e1d061e1e43f

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<mailto: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<mailto: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<mailto: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<mailto: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<mailto:katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp>>
>>>>>>> Sent: 21 February 2014 08:40
>>>>>>> To: pgsql-jdbc(at)postgresql(dot)org<mailto: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<mailto: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<mailto:pgsql-jdbc(at)postgresql(dot)org>)
>>>>>> To make changes to your subscription:
>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>