Thread: [JDBC] Interest in allowing caller to push binary data rather than having it pulled?
[JDBC] Interest in allowing caller to push binary data rather than having it pulled?
From
Tom Dunstan
Date:
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
Re: Interest in allowing caller to push binary data ratherthan having it pulled?
From
Dave Cramer
Date:
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