Обсуждение: setBlob() copies the blob, even it was already a PostgreSQL blob!

Hi there,

Looking at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(int,
Blob) it seems to copy the given blob, even if the blob was already a
PostgreSQL blob.

Ideally it would use the blob if it was already a PostgreSQL blob.

Why?  If you're using Blob object with Hibernate it can be useful to
create the blob in a new entity and write some stuff to it before
saving to the DB along with the entity.  Also, I suspect (but have not
yet confirmed) that hibernate will store the blob fields any time an
object is updated to the database along with the other fields (i.e. it
doesn't necessarily UPDATE only the changed fields, it may issue an
UPDATE that includes the blob's OID and uses the setBlob() method to
set it).

The way things are now took me quite by surprise and we can have a lot
of duplicate copies of the data in the database, poor performance,
etc..  It seems like an easy fix to check whether the blob passed in
is already a PostgreSQL blob and if so, re-use the same oid already
allocated.

On Thu, 27 May 2010, Dobes wrote:

> Looking at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(int,
> Blob) it seems to copy the given blob, even if the blob was already a
> PostgreSQL blob.

Just knowing it's a postgresql blob isn't enough.  You could have fetched
that blob from another connection to another database on another server.
So at minimum you'd need to know the blob was retrieved from the same
connection.  It's also not clear that you never want to copy the blob.
It's really a pain how PG requires you to manually garbage collect your
blobs, so your strategy for doing that impacts whether you want to copy or
not.

Certainly once we get Connection.createBlob to work we don't want to have
two copies of that, so we'll need some sort of fix at some point, but it's
not clear to me that never copying is the answer.

Kris Jurka