Re: About binaryTransfer.

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