Thread: Will Connection.createBlob be implemented any time soon?

Andreas Joseph Krogh wrote on 19.01.2014 14:14:
> $subject
> It'd be nice to be able to do:
>                  val b = con.createBlob()
>                  val os = b.setBinaryStream(1)
>                  IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
> Is there a work-around for it?

Using PreparedStatement.setBinaryStream() works without problems.
You don't even need the intermediate Blob instance:

PreparedStatement pstmt = con.createStatement("....");

pstmt.setBinaryStream(1, is);
   or
pstmt.setBinaryStream(1, is, length);  // if the length is known

Andreas Joseph Krogh wrote on 19.01.2014 14:14:
> $subject
> It'd be nice to be able to do:
>                  val b = con.createBlob()
>                  val os = b.setBinaryStream(1)
>                  IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
> Is there a work-around for it?

Using PreparedStatement.setBinaryStream() works without problems.
You don't even need the intermediate Blob instance:

PreparedStatement pstmt = con.createStatement("....");

pstmt.setBinaryStream(1, is);
   or
pstmt.setBinaryStream(1, is, length);  // if the length is known

Andreas Joseph Krogh wrote on 19.01.2014 14:41:
>     Using PreparedStatement.setBinaryStream() works without problems.
>     You don't even need the intermediate Blob instance:
>
>     PreparedStatement pstmt = con.createStatement("....");
>
>     pstmt.setBinaryStream(1, is);
>         or
>     pstmt.setBinaryStream(1, is, length);  // if the length is known
>
> I'm using JPA and need to be able to set a (new) Blob as a field, like this:

Ah! The joys of database obfuscation layers.
Sorry, I can't help you with that.

> val b = con.createBlob()
> val os = b.setBinaryStream(1)
> IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
> myEntity.setData(b)

The above code (val b = ...) does not look like Java. What exactly is that?

Andreas Joseph Krogh wrote on 19.01.2014 14:41:
>     Using PreparedStatement.setBinaryStream() works without problems.
>     You don't even need the intermediate Blob instance:
>
>     PreparedStatement pstmt = con.createStatement("....");
>
>     pstmt.setBinaryStream(1, is);
>         or
>     pstmt.setBinaryStream(1, is, length);  // if the length is known
>
> I'm using JPA and need to be able to set a (new) Blob as a field, like this:

Ah! The joys of database obfuscation layers.
Sorry, I can't help you with that.

> val b = con.createBlob()
> val os = b.setBinaryStream(1)
> IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
> myEntity.setData(b)

The above code (val b = ...) does not look like Java. What exactly is that?

Hi Andreas

On 20 January 2014 00:11, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> I'm using JPA and need to be able to set a (new) Blob as a field, like this:
>
>                 val b = con.createBlob()
>                 val os = b.setBinaryStream(1)
>                 IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
>                 myEntity.setData(b)
>
> Where MyEntity has a field "data" which is of type "Blob"

I was under the impression that most JPA implementations will allow
you to stick a byte[] as your property type and will handle the rest
under the good (usually using the blob support in PreparedStatement).
Is that an option for you, or do you need the streaming functionality?

Cheers

Tom

Hi Andreas

On 20 January 2014 00:11, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> I'm using JPA and need to be able to set a (new) Blob as a field, like this:
>
>                 val b = con.createBlob()
>                 val os = b.setBinaryStream(1)
>                 IOUtils.copyLarge(is, os, new Array[Byte](1024 * 1024))
>                 myEntity.setData(b)
>
> Where MyEntity has a field "data" which is of type "Blob"

I was under the impression that most JPA implementations will allow
you to stick a byte[] as your property type and will handle the rest
under the good (usually using the blob support in PreparedStatement).
Is that an option for you, or do you need the streaming functionality?

On 01/28/2014 02:40 AM, Tom Dunstan wrote:

> I was under the impression that most JPA implementations will allow
> you to stick a byte[] as your property type and will handle the rest
> under the good (usually using the blob support in PreparedStatement).

If I recall correctly, you need to special-case byte[], otherwise it
will be turned into a PostgreSQL array type (probably "int array").

--
Florian Weimer / Red Hat Product Security Team

On 01/28/2014 09:09 AM, Andreas Joseph Krogh wrote:

> I need streaming yes, that's why I use Blobs.

The PostgreSQL wire protocol does not support streaming BYTEA parameters
and result data.  Your initial example does not actually use streaming
in an essential way, so I'm not sure what you're trying to achieve.

--
Florian Weimer / Red Hat Product Security Team

On 01/28/2014 09:09 AM, Andreas Joseph Krogh wrote:

> I need streaming yes, that's why I use Blobs.

The PostgreSQL wire protocol does not support streaming BYTEA parameters
and result data.

 

  Your initial example does not actually use streaming
in an essential way, so I'm not sure what you're trying to achieve.

 

On Tue, Jan 28, 2014 at 4:29 AM, Andreas Joseph Krogh <andreak@officenet.no> wrote:

På tirsdag 28. januar 2014 kl. 10:21:34, skrev Florian Weimer <fweimer@redhat.com>:

On 01/28/2014 09:09 AM, Andreas Joseph Krogh wrote:

> I need streaming yes, that's why I use Blobs.

The PostgreSQL wire protocol does not support streaming BYTEA parameters
and result data.

 

Why do you assume I use BYTEA? I use OID for myu blobs.

 

 

  Your initial example does not actually use streaming
in an essential way, so I'm not sure what you're trying to achieve.

 

Read it again, it does use streaming. It copies the "is" stream out to the "os" stream, backed by the Blob.

Using EclipseLink with the pgjdbc-ng driver it works perfectly using Connection.createBlob, streaming etc. I've tried streaming > 200GB stuff with a Blob field in a JPA-object and it works.

 

What pussles me is why the "official" driver doesn't implement createBlob as I thought it should be a quit common use-case to want to stream large data-chunks in the db (for tx-purposes).

 

 

Apparently it isn't a common use-case as the driver has never had it. 

On 01/19/2014 09:14 PM, Andreas Joseph Krogh wrote:
> $subject
>
> It'd be nice to be able to do:
>
>                 val b = con.createBlob()
>                 val os = b.setBinaryStream(1)

The usual answer to "when will it be implemented" questions is "when
someone steps forward, does it, and submits a patch".

I haven't seen any prototypes or early attempts at this code. I suggest
taking a look and seeing what's involved in adding it.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 01/28/2014 05:29 PM, Andreas Joseph Krogh wrote:
> Read it again, it does use streaming. It copies the "is" stream out to
> the "os" stream, backed by the Blob.

Sure, but if it's backed by a 'bytea' column the scenes PgJDBC still
reads the whole thing into memory before sending it off (for output), or
reads the whole blob from the PostgreSQL database connection before
exposing it to the client (for input).

PostgreSQL supports streaming for pg_largeobject, i.e. the "lob" type,
but not for bytea. That doesn't mean you can't present a streaming
interface to it, just that you don't get any benefit from doing so.

> Using EclipseLink with the pgjdbc-ng driver it works perfectly using
> Connection.createBlob, streaming etc. I've tried streaming > 200GB stuff
> with a Blob field in a JPA-object and it works.

Is the backing column "lob", or a pg_largeobject directly? This won't
work with bytea, so it must be.

> What pussles me is why the "official" driver doesn't implement
> createBlob as I thought it should be a quit common use-case to want to
> stream large data-chunks in the db (for tx-purposes).

Because nobody's wanted it enough to implement it yet.

There are lots of areas where PgJDBC could be better, but nobody who's
had the time has also had the inclination to do the work required to
make it happen.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 01/19/2014 09:14 PM, Andreas Joseph Krogh wrote:
> $subject
> 
> It'd be nice to be able to do:
> 
>                 val b = con.createBlob()
>                 val os = b.setBinaryStream(1)

The usual answer to "when will it be implemented" questions is "when
someone steps forward, does it, and submits a patch".

I haven't seen any prototypes or early attempts at this code. I suggest
taking a look and seeing what's involved in adding it.

On 01/28/2014 05:29 PM, Andreas Joseph Krogh wrote:
> Read it again, it does use streaming. It copies the "is" stream out to
> the "os" stream, backed by the Blob.

Sure, but if it's backed by a 'bytea' column the scenes PgJDBC still
reads the whole thing into memory before sending it off (for output), or
reads the whole blob from the PostgreSQL database connection before
exposing it to the client (for input).

PostgreSQL supports streaming for pg_largeobject, i.e. the "lob" type,
but not for bytea. That doesn't mean you can't present a streaming
interface to it, just that you don't get any benefit from doing so.

> Using EclipseLink with the pgjdbc-ng driver it works perfectly using
> Connection.createBlob, streaming etc. I've tried streaming > 200GB stuff
> with a Blob field in a JPA-object and it works.

Is the backing column "lob", or a pg_largeobject directly? This won't
work with bytea, so it must be.

> What pussles me is why the "official" driver doesn't implement
> createBlob as I thought it should be a quit common use-case to want to
> stream large data-chunks in the db (for tx-purposes).

Because nobody's wanted it enough to implement it yet.

There are lots of areas where PgJDBC could be better, but nobody who's
had the time has also had the inclination to do the work required to
make it happen.

 

On Wed, Jan 29, 2014 at 4:12 AM, Andreas Joseph Krogh <andreak@officenet.no> wrote:

På onsdag 29. januar 2014 kl. 06:57:25, skrev Craig Ringer <craig@2ndquadrant.com>:

On 01/19/2014 09:14 PM, Andreas Joseph Krogh wrote:
> $subject
> 
> It'd be nice to be able to do:
> 
>                 val b = con.createBlob()
>                 val os = b.setBinaryStream(1)

The usual answer to "when will it be implemented" questions is "when
someone steps forward, does it, and submits a patch".

I haven't seen any prototypes or early attempts at this code. I suggest
taking a look and seeing what's involved in adding it.

 

I kind of  hoped the answer would be ending with "... or someone willing to fund it" but I recognize that the driver is practically in maintenance-mode and nobody has time for any real development on it.

 

Maybe it's time to re-suggest adding pgjdbc-ng to http://jdbc.postgresql.org so people are aware of the alternative, active developed, driver?

 

 

Possibly, however given the man hours that are being spent on it they would be far better spent on this driver.