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

2012/1/8 Thomas Kellerer <spam_eater@gmx.net> wrote:
> I think you are better off using bytea unless you need to access only parts
> of the blob regularly.

That's a valid tip. But it's to the current JDBC implementation to
take action because it currently leads to disk space leakage when
using JDBC and JPA/Hibernate.

After a tedious time of bug searching I found a possible solution and
a testimonial here: "One of the problems with the JDBC driver (and
this affects the ODBC driver also), is that the specification assumes
that references to BLOBs (Binary Large OBjects) are stored within a
table, and if that entry is changed, the associated BLOB is deleted
from the database. As PostgreSQL stands, this doesn't occur." (taken
from the docs http://www.postgresql.org/docs/9.1/static/lo.html )

In addition, unfortunately the (lo_unlink) problem of LargeObjects and
BLOBs is not mentioned in the JDBC docs:
http://jdbc.postgresql.org/documentation/head/binary-data.html

Stefan

2012/1/8 Thomas Kellerer <spam_eater@gmx.net>:
> Stefan Keller wrote on 06.01.2012 19:04:
>
>> 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."
>
>
> I think you are better off using bytea unless you need to access only parts
> of the blob regularly.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general