Re: blob without largeobject api

Edoardo Ceccarelli wrote:
> I was thinking to get this chance and drop the Resin pool to start using
> Postgres one for all database connections in my app.
> Can you give me some hints about its reliability and point me some
> example code?

I don't use a 3rd party connection pool for various reasons so I can't
really recommend one. I think the driver does provide a pool
implementation, but it didn't seem too flexible at a glance -- and it's
going to blindside any app server you're using since it just looks like
a DataSource..

What you really want is a pooling implementation that uses the
implementations of ConnectionPoolDataSource provided by the driver:
org.postgresql.jdbc2.optional.ConnectionPool or
org.postgresql.jdbc3.ConnectionPool. Despite the name, these classes
provide *support* for connection pool implementations but no actual
pooling implementation or policy.

What they do is provide a means for the actual pooling implementation to
get PooledConnection objects representing physical connections to the
DB; these are the objects that get pooled and reused. Each
PooledConnection allows you to get proxy connection handles that look
like normal JDBC connections, except that they actually delegate back to
the underlying physical connection. Closing a proxy connection invokes a
listener callback rather than closing the physical connection; the pool
implementation can use this callback to return the physical connection
to its pool.

Connection pool implementations can then build whatever pooling policy
they want on top of this, and hand out these proxy connection handles to
their clients.

The reason you want to use this sort of approach is that the proxy
connections handed out by the driver can implement any driver extensions
as necessary, without the connection pool itself having to know about it
-- i.e. in this case the proxy connections would implement PGConnection.

I'm not familiar with Resin, but is it possible that you can just tell
it to use the driver's ConnectionPoolDataSource implementation and it'll
use this sort of strategy?

> PS: the procedure to clean up not referenced LO's would be needed also
> using LargeObjectAPI?

True, but usually if you're directly using the LO API you will be
dealing with OIDs yourself and could do the cleanup yourself via the LO
API's unlink() method from your app. If you're just using setBytes() to
manage them transparently and don't have access to the LO API, there's
no way to delete the underlying old LO when you update a field
containing a LO OID..

-O