Re: About binaryTransfer.

Hi Dave,

>> Can you please test the current REL9_3_STABLE code ?
>
>OK, I'll test it.
>
I've done it.
It seems good!

All regression tests are passed.
And also the performance is improved.
I tried to simple test like below.

0.preparation
create table tbl(i int);
insert into tbl values(generate_series(1,100));

In Java program--------------------------
1. prepare statement
stmt = conn.prepareStatement("SELECT * FROM tbl WHERE i < ?")

2. bind
stmt.setInt(1,1);

3. execute 1000 times
for(int i = 0 ; i < 1000; i++ )
{
stmt.executeQuery();
}
-----------------------------------------------

The result is ...
9.3-1101: 1.619s
9.3_stable:1.049s

I'm happy to fix this.
Thank you very much!

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

2014-04-24 17:23 GMT+09:00 Tomonari Katsumata <
katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp>:

> 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(at)fastcrypt(dot)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(dot)katsumata1122(at)gmail(dot)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(dot)tomonari(at)po(dot)ntts(dot)co(dot)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(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@
> >>>>> postgresql.
> >>>>>>>>>> org>
> >>>>>>>>>>>>>> on behalf of Tomonari Katsumata <katsumata(dot)tomonari(at)po(dot)ntts(dot)
> >>>>> co.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
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>
> >>
> >>
> >
>
>
>