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@xxxxxxxxx>:
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
