Re: About binaryTransfer. - Mailing list pgsql-jdbc

Hi Dave,

Thank you for a lot!

 > Can you please test the current REL9_3_STABLE code ?

OK, I'll test it.

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

(2014/04/23 23:56), Dave Cramer wrote:
 > Tomonari,
 >
 > I committed the code to REL9_3_STABLE. As the "new" functionality is
 > invoked by setting prepareThreshold to -1 it should not be intrusive.
 >
 > Can you please test the current REL9_3_STABLE code ?
 >
 > Dave Cramer
 >
 > dave.cramer(at)credativ(dot)ca
 > http://www.credativ.ca
 >
 >
 > On 21 April 2014 06:31, Dave Cramer <pg@fastcrypt.com> wrote:
 >
 >> Tomonari,
 >>
 >> I'm hesitant to apply the first patch, as the static variable means
every
 >> statement will have the same behaviour. Think about this. What if
another
 >> statement sets it to false after you have set it to true ?
 >>
 >> You have to keep in mind there is only copy of the driver inside a JVM.
 >>
 >>
 >>
 >>
 >>
 >> Dave Cramer
 >>
 >> dave.cramer(at)credativ(dot)ca
 >> http://www.credativ.ca
 >>
 >>
 >> On 17 April 2014 11:29, Tomonari Katsumata
<t.katsumata1122@gmail.com>wrote:
 >>
 >>> Hi Dave,
 >>>
 >>> Thank you for check.
 >>>
 >>>> We still have to deal with the static variable ForceBinaryTransfer. It
 >>> has
 >>>> to be per statement. The static variable will change the behaviour of
 >>> all
 >>>> statements.
 >>>>
 >>>> It should also be named forceBinaryTransfer.
 >>>>
 >>> I've misunderstood what you say.
 >>> Now I could understand it.
 >>>
 >>> I changed public variable "forceBinaryTransfers" to
 >>> static variable "ForceBinaryTransfers" .
 >>> Please check fix_ForceBinaryTranfers-part2.patch.
 >>> This patch is for current master(*1).
 >>> (*1)0eadcb873c68715cc3d0e9478c13ce3e38b798ae
 >>>
 >>> Because this patch is for new feature, I think
 >>> it is not necessary to be applyed to REL9_3_STABLE.
 >>> But I want to apply a patch which I sent in last mail(*2)
 >>> to REL9_3_STABLE for resolving performance problem.
 >>> (*2) sending again. see attached
 >>> fix_ForceBinaryTranfers-for-93stable.patch
 >>>
 >>> Still am I missing something?
 >>>
 >>> regards,
 >>> -----------------
 >>> Tomonari Katsumata
 >>>
 >>>>
 >>>> Dave Cramer
 >>>>
 >>>> dave.cramer(at)credativ(dot)ca
 >>>> http://www.credativ.ca
 >>>>
 >>>>
 >>>> On 17 April 2014 05:15, Tomonari Katsumata
 >>>> <katsumata.tomonari@po.ntts.co.jp
 >>>>> wrote:
 >>>>
 >>>>> 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.Tiihonen@
 >>>>> nitorcreations.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@gmail.com <davecramer@gmail.com> on behalf of
 >>> Dave
 >>>>>>> Cramer <pg@fastcrypt.com>
 >>>>>>> *Sent:* 15 April 2014 15:34
 >>>>>>> *To:* Tomonari Katsumata
 >>>>>>> *Cc:* Tomonari Katsumata; Mikko Tiihonen; pgsql-jdbc@postgresql.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.tomonari@po.ntts.co.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.tomonari@po.ntts.co.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@fastcrypt.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.tomonari@po.ntts.co.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@postgresql.org<pgsql-jdbc-owner@
 >>>>> postgresql.
 >>>>>>>>>> org>
 >>>>>>>>>>>>>> on behalf of Tomonari Katsumata <katsumata.tomonari@po.ntts.
 >>>>> co.jp>
 >>>>>>>>>>>>>>> Sent: 21 February 2014 08:40
 >>>>>>>>>>>>>>> To: pgsql-jdbc@postgresql.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@8232a5d,
 >>>>>>>>>>>>>>> maxRows=0, fetchSize=0, flags=16
 >>>>>>>>>>>>>>> 14:09:55.878 (1) simple execute,
 >>>>>>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
 >>>>>>>>>>>>>> StatementResultHandler@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@19cdbc83,
 >>>>>>>>>>>>>>> maxRows=0, fetchSize=0, flags=48
 >>>>>>>>>>>>>>> 14:20:34.992 (1) simple execute,
 >>>>>>>>>>>>>>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$
 >>>>>>>>>>>>>> StatementResultHandler@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@postgresql.org
 >>> )
 >>>>>>>>>>>>>>> To make changes to your subscription:
 >>>>>>>>>>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
 >>>>>>>>>>>>>>>
 >>>>>>>>>>>>>>>
 >>>>>>>>>>>>>>
 >>>>>>>>>>>>>>
 >>>>>>>>>>>>>>
 >>>>>>>>>>>>>>
 >>>>>>>>>>>>>> --
 >>>>>>>>>>>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
 >>>>>>>>>>>>>> To make changes to your subscription:
 >>>>>>>>>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
 >>>>>>>>>>>>>>
 >>>>>>>>>>>>>
 >>>>>>>>>>>>>
 >>>>>>>>>>>>
 >>>>>>>>>>>
 >>>>>>>>>>>
 >>>>>>>>>>>
 >>>>>>>>>>
 >>>>>>>>>>
 >>>>>>>>>>
 >>>>>>>>>
 >>>>>>>>
 >>>>>>>>
 >>>>>>>>
 >>>>>>>>
 >>>>>>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>
 >>>
 >>
 >>
 >