Thread: Patch: Force Primitives

Patch: Force Primitives

From
Nicholas White
Date:
Hi,

I'd like the JDBC driver's Array implementations to return primitive arrays when I call getArray(). I can force this behaviour by passing &compatible=8.2 in my JDBCUrl, but then I can't transfer the arrays from the database via the binary protocol, as that requires a minimum compatible version of 8.3. I've attached a patch that makes the driver recognise a forcePrimitives boolean flag in the JDBCUrl, and if it sees this it will return primitive arrays, not object arrays.


Is this OK? Thanks -

Nick
Attachment

Re: Patch: Force Primitives

From
Nicholas White
Date:
I've revised the patch to make Array.getArray() return a primitive array even when using the binary transfer format. I had to add extra code to force a statement to do a describe query before executing (previously it would only do this if the org.postgresql.forcebinary system property was set), as the QueryExecutorImpl needs the Field objects to be populated when sendBind is called, or the query results will be sent back in Text format. The PrimitivesTest unit test my patch adds verifies that a column is being returned in the correct (text or binary) format. I think this fixes an outstanding bug; I believe binary transfers will only be used if the system property is set, and the binaryTransfer JDBCUrl parameter is currently useless.

Thanks -

Nick
Attachment

Re: Patch: Force Primitives

From
Mikko Tiihonen
Date:
On 03/25/2013 05:51 PM, Nicholas White wrote:
> I've revised the patch to make Array.getArray() return a primitive array even when using the binary transfer format.
Ihad to add extra code to force a 
> statement to do a describe query before executing (previously it would only do this if the org.postgresql.forcebinary
systemproperty was set), as the 
> QueryExecutorImpl needs the Field objects to be populated when sendBind is called, or the query results will be sent
backin Text format. The PrimitivesTest 
> unit test my patch adds verifies that a column is being returned in the correct (text or binary) format. I think this
fixesan outstanding bug; I believe binary 
> transfers will only be used if the system property is set, and the binaryTransfer JDBCUrl parameter is currently
useless.

By default the binary transfers for receive kick in only after the 3rd execution of a prepared statement. Do you see
thebinary transfers activating for array  
receives if you run your prepared statement select in a loop?

The forceBinary option is for unit tests that do not want to repeat every statement 3 times.

-Mikko


Re: Patch: Force Primitives

From
Nicholas White
Date:
> Do you see the binary transfers activating for array receives if you run your prepared statement select in a loop?
That's the behaviour I see, athough something's setting my m_prepareThreshold to 5 rather than 3. I'm essentially using postgres as a persistent cache for my application server; when my app server starts it loads large amounts of data from postgres using a series of select-*-from-x queries. In order to minimise network I/O I'd ideally like a way to ensure I'm using the binary protocol from the very first query. Should I submit another patch that lets you configure this behaviour (either via a new JDBCUrl parameter or whether binaryTransfer is explicitly specified)?

Separately, do you know why this behaviour is the default? Is the binary encoding more expensive (either server-side or client-side) than text encoding the data?

Thanks -

Nick

Re: Patch: Force Primitives

From
Mikko Tiihonen
Date:
On 03/25/2013 08:24 PM, Nicholas White wrote:
>  > Do you see the binary transfers activating for array receives if you run your prepared statement select in a loop?
> That's the behaviour I see, athough something's setting my m_prepareThreshold to 5 rather than 3. I'm essentially
usingpostgres as a persistent cache for my 
> application server; when my app server starts it loads large amounts of data from postgres using a series of
select-*-from-xqueries. In order to minimise 
> network I/O I'd ideally like a way to ensure I'm using the binary protocol from the very first query. Should I submit
anotherpatch that lets you configure this 
> behaviour (either via a new JDBCUrl parameter or whether binaryTransfer is explicitly specified)?

I can't remember how the prepareTheshold goes. Is it so that even setting it to 1 (0 disables them) you still get real
preparedstatements (and at the same 
time binary transfers) on the second execution. If you have an application that benefits from binary transfers on the
firstexecution then yes, we should add 
an option to enable that behaviour. So another patch for that would be nice.

> Separately, do you know why this behaviour is the default? Is the binary encoding more expensive (either server-side
orclient-side) than text encoding the data? 

It's not because of binary encoding, which is faster on both server and client side.

The current functionality tries to avoid the extra describe overhead (is it even a roundtrip?) for prepared statements
thatare only executed once (or very few  
times), which is quite common in many applications that seem to use prepared statements also for one-shot cases.

PS. if you are concerned about binary array transfer performance I did some work last year to optimize the protocol for
arraysthat do not contain nulls.  
Unfortunately there was never any resolution to the server side patches. The discussion got stuck on weather updates
withfeature flags, adding a minor version  
number to the protocol or just wait for pg 10 to break the protocol is the correct way to go forward.

You can grab the server side + jdbc patches from here:
http://www.postgresql.org/message-id/4ECC186A.30709@nitorcreations.com

-Mikko


Re: Patch: Force Primitives

From
Nicholas White
Date:
> So another patch for that would be nice.

I've attached a patch that fixes setPrepareThreshold; previously setting the threshold to n would mean the n+1th query would be the first binary one, and setting it to zero meant the binary protocol would never be used. This means the first query of a statement could never use the binary protocol. The patch makes setting the threshold to n mean the nth query is binary, so setting it to 1 would make the first query use the binary protocol. The patch includes some test cases that demonstrate this.

> The discussion got stuck on weather updates with feature flags, adding a minor version number to the protocol or just wait for pg 10 to break the protocol is the correct way to go forward.

That patch is pretty interesting - but maybe you could preserve backwards-compatibility by making a new format type (so 0 is text, 1 is binary and 2 is binary-with-implied-sizes). A new format type would give you more flexibility; you could even use variable-length or run-length encodings, or compression to represent (only large?) arrays.

Thanks -

Nick
Attachment

Re: Patch: Force Primitives

From
Nicholas White
Date:
I've submitting the setPrepareThreshold patch as a pull request here: https://github.com/pgjdbc/pgjdbc/pull/51 . Could you review it? Thanks -

Nick