Thread: [JDBC] Interest in allowing caller to push binary data rather than having it pulled?

Hi all

I hit an interesting case today. It’s a bit of a limitation in the JDBC interface, so any support would have to be a
proprietaryinterface. 

Basically I have one or more byte buffers that I’d like to stream into a BYTEA at the server (using a plain INSERT
statement).In my case I’ve got Netty ByteBuf objects, but it could be anything. 

What are my current options? JDBC basically gives me PreparedStatement.setBytes() and
PreparedStatement.setBinaryStream().

PreparedStatement.setBytes() involves copying all the data, potentially multiple large buffers, into a large buffer of
exactlythe correct size. The reason to use ByteBufs in the first place was to pool our use of large buffers so that we
don’tblow out our heap - this completely kills any hope of that. 

PreparedStatement.setBinaryStream() is more flexible, but under the hood we’re just pulling stuff into an intermediary
8kbuffer and then writing it out to the socket. This is OK from a heap management perspective, but still has some
unnecessarycopying. 

What I’d really like to do would be to provide an object that the driver could interrogate for a length and then
providean OutputStream to write to. The interface would look something like: 

interface ByteStreamWriter {
    int getLength();
    void writeTo(OutputStream stream);
}

The provided output stream would be a very thin wrapper around the socket output stream just ensuring that we don’t
writetoo many bytes out. 

Usage would look thusly:

myPreparedStatement.setObject(n, new MyByteStreamWriter(myByteBuf), Types.VARBINARY);

And the user could write whatever adapter they wanted around their data.

There’s an existing StreamWrapper class in the codebase, but it just provides an InputStream when asked. It could be
adjustedto use the above interface for consistency though. 

Thoughts? I’d be happy to code up a PR if there’s interest.

Cheers

Tom




Tom,

I'd love to see this!


On 23 March 2017 at 04:24, Tom Dunstan <pgsql@tomd.cc> wrote:
Hi all

I hit an interesting case today. It’s a bit of a limitation in the JDBC interface, so any support would have to be a proprietary interface.

Basically I have one or more byte buffers that I’d like to stream into a BYTEA at the server (using a plain INSERT statement). In my case I’ve got Netty ByteBuf objects, but it could be anything.

What are my current options? JDBC basically gives me PreparedStatement.setBytes() and PreparedStatement.setBinaryStream().

PreparedStatement.setBytes() involves copying all the data, potentially multiple large buffers, into a large buffer of exactly the correct size. The reason to use ByteBufs in the first place was to pool our use of large buffers so that we don’t blow out our heap - this completely kills any hope of that.

PreparedStatement.setBinaryStream() is more flexible, but under the hood we’re just pulling stuff into an intermediary 8k buffer and then writing it out to the socket. This is OK from a heap management perspective, but still has some unnecessary copying.

What I’d really like to do would be to provide an object that the driver could interrogate for a length and then provide an OutputStream to write to. The interface would look something like:

interface ByteStreamWriter {
    int getLength();
    void writeTo(OutputStream stream);
}

The provided output stream would be a very thin wrapper around the socket output stream just ensuring that we don’t write too many bytes out.

Usage would look thusly:

myPreparedStatement.setObject(n, new MyByteStreamWriter(myByteBuf), Types.VARBINARY);

And the user could write whatever adapter they wanted around their data.

There’s an existing StreamWrapper class in the codebase, but it just provides an InputStream when asked. It could be adjusted to use the above interface for consistency though.

Thoughts? I’d be happy to code up a PR if there’s interest.

Cheers

Tom




--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Interest in allowing caller to push binary data ratherthan having it pulled?

From
Vladimir Sitnikov
Date:
PGBinaryObject might be relevant.

Vladimir

пт, 24 мар. 2017 г. в 18:45, Dave Cramer <pg@fastcrypt.com>:
Tom,

I'd love to see this!

On 23 March 2017 at 04:24, Tom Dunstan <pgsql@tomd.cc> wrote:
Hi all

I hit an interesting case today. It’s a bit of a limitation in the JDBC interface, so any support would have to be a proprietary interface.

Basically I have one or more byte buffers that I’d like to stream into a BYTEA at the server (using a plain INSERT statement). In my case I’ve got Netty ByteBuf objects, but it could be anything.

What are my current options? JDBC basically gives me PreparedStatement.setBytes() and PreparedStatement.setBinaryStream().

PreparedStatement.setBytes() involves copying all the data, potentially multiple large buffers, into a large buffer of exactly the correct size. The reason to use ByteBufs in the first place was to pool our use of large buffers so that we don’t blow out our heap - this completely kills any hope of that.

PreparedStatement.setBinaryStream() is more flexible, but under the hood we’re just pulling stuff into an intermediary 8k buffer and then writing it out to the socket. This is OK from a heap management perspective, but still has some unnecessary copying.

What I’d really like to do would be to provide an object that the driver could interrogate for a length and then provide an OutputStream to write to. The interface would look something like:

interface ByteStreamWriter {
    int getLength();
    void writeTo(OutputStream stream);
}

The provided output stream would be a very thin wrapper around the socket output stream just ensuring that we don’t write too many bytes out.

Usage would look thusly:

myPreparedStatement.setObject(n, new MyByteStreamWriter(myByteBuf), Types.VARBINARY);

And the user could write whatever adapter they wanted around their data.

There’s an existing StreamWrapper class in the codebase, but it just provides an InputStream when asked. It could be adjusted to use the above interface for consistency though.

Thoughts? I’d be happy to code up a PR if there’s interest.

Cheers

Tom




--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Interest in allowing caller to push binary data ratherthan having it pulled?

From
Álvaro Hernández Tortosa
Date:

On 23/03/17 04:24, Tom Dunstan wrote:
> Hi all
>
> I hit an interesting case today. It’s a bit of a limitation in the JDBC interface, so any support would have to be a
proprietaryinterface. 
>
> Basically I have one or more byte buffers that I’d like to stream into a BYTEA at the server (using a plain INSERT
statement).In my case I’ve got Netty ByteBuf objects, but it could be anything. 
>
> What are my current options? JDBC basically gives me PreparedStatement.setBytes() and
PreparedStatement.setBinaryStream().
>
> PreparedStatement.setBytes() involves copying all the data, potentially multiple large buffers, into a large buffer
ofexactly the correct size. The reason to use ByteBufs in the first place was to pool our use of large buffers so that
wedon’t blow out our heap - this completely kills any hope of that. 
>
> PreparedStatement.setBinaryStream() is more flexible, but under the hood we’re just pulling stuff into an
intermediary8k buffer and then writing it out to the socket. This is OK from a heap management perspective, but still
hassome unnecessary copying. 
>
> What I’d really like to do would be to provide an object that the driver could interrogate for a length and then
providean OutputStream to write to. The interface would look something like: 
>
> interface ByteStreamWriter {
>      int getLength();
>      void writeTo(OutputStream stream);
> }
>
> The provided output stream would be a very thin wrapper around the socket output stream just ensuring that we don’t
writetoo many bytes out. 
>
> Usage would look thusly:
>
> myPreparedStatement.setObject(n, new MyByteStreamWriter(myByteBuf), Types.VARBINARY);
>
> And the user could write whatever adapter they wanted around their data.
>
> There’s an existing StreamWrapper class in the codebase, but it just provides an InputStream when asked. It could be
adjustedto use the above interface for consistency though. 
>
> Thoughts? I’d be happy to code up a PR if there’s interest.
>
> Cheers
>
> Tom
>
>
>
>

     Hi Tom.

     I think this is quite a good approach. I've seen significant
overheads in heap object creation in the process of
serialization/deserialization. Some of them were documented as part of
the slides of this talk:
https://www.slideshare.net/8kdata/java-and-postgresql-performance-features-and-the-future
(starting slide #19).

     So having an adapter to write the data to the socket without
rewriting the bytes is a very desirable goal, in my opinion.

     Cheers,

     Álvaro


--

Álvaro Hernández Tortosa


-----------
<8K>data