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

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(at)gmail(dot)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