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

On 10 January 2012 00:06, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> 2012/1/9 Oliver Jowett <oliver(at)opencloud(dot)com>:
>> Otherwise, what should JDBC do differently here? Be specific. It would
>
> First, I pretty sure that Hibernate nor the Tomcat/Java GC are
> misconfigured - since it works now after having installed the trigger
> by hand.

You misunderstand - by GC I mean the process that collects garbage LOs
that are no longer referenced. I don't mean the JVM's heap GC.
You need a GC process like this if you are using LOs and not managing
their lifetimes explicitly from the application. Consider it part of
the necessary DB setup. You've already discovered the usual mechanisms
for it ('lo' or 'vacuumlo' depending on exactly what your data model
looks like).

> To become more specific read the first two sections as a first hint
> here in this official doc:
> http://www.postgresql.org/docs/current/interactive/lo.html

FWIW, that documentation is pretty old (the JDBC docs now live
separately on jdbc.postgresql.org; the JDBC references in that
appendix are mostly historical)
But I'm not sure quite what you're referring to - those docs are
fairly clear about what you need to do? Specifically:

> Now this is fine for PostgreSQL-specific applications, but standard code using JDBC or ODBC won't delete the objects, resulting in orphan objects — objects that are not referenced by anything, and simply occupy disk space.

Which is exactly my point - if you are going to use generic JDBC code
that does not explicitly delete LOs when they become detached, then
you need a separate mechanism to clean them up - that's just the way
the model works. If you want to avoid that, don't use LOs, use bytea.

So I'm still confused about what you'd like to see changed in the JDBC
driver. Can you explain?

Oliver