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
