Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues - Mailing list pgsql-general

Thanks, Radosław, for the clarification.

2012/1/8 Radosław Smogura <rsmogura@softperience.eu> wrote:
> 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

That's exactly my point:
Since JDBC manages creation of OID and pg_largeobjects it's also JDBC
which is responsible for update/delete of rows in pg_largeobjects.
As the user expects, the only thing JDBC would have to do is to issue
lo_unlink() when rows are updated or deleted.
Thus, it's currently a bug in the JDBC driver. And whatever the
solution is, it needs to be mentioned in the JDBC docs.

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

In fact, this annotation syntax usage looks like the Hibernate mapping
for PostgreSQL could be enhanced.

Yours, Stefan

2012/1/8 Radosław Smogura <rsmogura@softperience.eu>:
> 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