Thanks, Radosław, for the clarification. 2012/1/8 Radosław Smogura <rsmogura@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx>: > 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
