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