Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

On Sun, 8 Jan 2012 18:08:09 +0100, Stefan Keller wrote:
> I'd like to backup my statement below regarding in JDBC driver from
> PostgreSQL:
>
> When storing fields of type BLOB it inserts the binary string in
> system table pg_largeobject (via user table). But when rows in user
> table get updated or deleted it does not update nor delete
> corresponding rows in table pg_largeobject.
>
> That's really a bug!
>
> Fortunately there's a solution indicated in the official docs
> (http://www.postgresql.org/docs/9.1/static/lo.html)
> To me, something like this should be implemented before hand in JDBC
> driver.
> And in any case there should be a bold note about this in the JDBC
> docs (http://jdbc.postgresql.org/documentation/head/binary-data.html
> )
>
> Yours, Stefan
>
>
>
> 2012/1/6 Stefan Keller <sfkeller@gmail.com>:
>> Hi,
>>
>> I run into a nasty behavior of current PostgreSQL JDBC.
>>
>> I maintain images (from Webcams). In the Java and Hibernate (JPA)
>> code
>> I specified a @Lob annotation on class MyData and a attribte/data
>> type
>> "byte[] mydata;". Hibernate then generates two tables in PostgreSQL,
>> one called MyData with a column mydata of type oid and an internal
>> one
>> called pg_largobjects (which contain foreign keys to the oid).
>> That's
>> also explained in the JDBC docs [1], saying "PostgreSQL provides two
>> distinct ways to store binary data. Binary data can be stored in a
>> table using the data type bytea or by using the Large Object feature
>> which stores the binary data in a separate table in a special format
>> and refers to that table by storing a value of type oid in your
>> table."
>>
>> Now, when replacing the images (few hundred) with new ones using
>> Java,
>> pg_largeobjects grew constantly until the file system run out of
>> space. So old image data did'nt get released! This is to me a bug
>> because the user/programmer must (and should) assume that there is a
>> strict 1:1 relationship between generated table MyData and its LOB
>> column data (stored in pg_largeobjects).
>> => I finally found the supplied module 'lo' [2] which releases
>> detached records. Is this the recommended way to resolve this
>> problem?
>>
>> Searching for explanations I found a ticket HHH2244 [3] which was
>> closed by the Hibernate team without action referring to the JDBC
>> Spec. which says: "An SQL BLOB is a built-in type that stores a
>> Binary
>> Large Object as a column value in a row of a database table".
>> => In other words: The PostgreSQL JDBC team should take action on
>> this
>> but didn't until now, right?
>>
>> There is another issue about "PostgreSQL and BLOBs" [4]. First it
>> cites PostgreSQL JDBC docs [1]. The thread [4] ends somehow too in a
>> 'deadlock' concluding  "...the PostgreSQLDialect (as of 3.5.5) needs
>> to change not to use MaterializedBlobType until the Postgres (JDBC)
>> team changes their driver (which does not seem to have happened in
>> the
>> last 6 years)."
>> => Any solutions or comments form insiders on this?
>>
>> Yours, Stefan
>>
>> [1] http://jdbc.postgresql.org/documentation/head/binary-data.html
>> [2] http://www.postgresql.org/docs/current/interactive/lo.html
>> [3] https://hibernate.onjira.com/browse/HHH-2244
>> [4] http://relation.to/Bloggers/PostgreSQLAndBLOBs
This is common approach for PostgreSQL and some proxy of real life
Blobs. One time I submitted bug about this to Hibernate. But step by
step.

1. BLOBs are... Large Objects, they are stored as reference because
those objects are large, if you will store this objects as bytea then
select * will return all large data. It may not be comfortable not only
to download few GB of data, but to keep this on stack too. From your
perspective it doesn't matters because you put it in byte[]. But if You
will keep e.g. CD-ROM images then it's much more better to use streaming
approach then bytea[]. More over due to some security JDBC driver will
at least double memory consumed by bytea.

2. Specifying hibernate data type as bytea do not resolve problems
because it will still use LOB approach.

3. pg_largeobjects is system table, hibernate do not creates it.

4. Trigger based approach is good for this, but You need to do this
mannualy

5. If you want to use bytea use
@Type(type="org.hibernate.type.PrimitiveByteArrayBlobType") (I think you
should remove @Lob too) on your field.

Regards,
Radosław Smogura