Thanks for the reply.  Sorry for my delay in responding -- I've been out on vacation.  ;-)

Please see my comments below.

Thank you, in advance, for your continued assistance.

-John Hardin


On Thu, Dec 20, 2012 at 2:19 AM, Steve Waldman <swaldman@mchange.com> wrote:
hi,

so the short answer is that what you propose to do is not a use c3p0 currently anticipates or supports.

an Exception in onCheckout, or the failure of a Connection test, are interpreted as an indication that the Connection is broken and should be retried. onCheckout is intended to set-up Connections for client use, but is not designed to control access to Connections or discriminate among clients. (how do you discriminate among clients in your initialization? and what sort of per-tenant initializations do you perform? do you ensure that whatever you do is undone on checkin, or consistently "overwrite" the initializations on checkout, to ensure that all pooled Connections are interchangeable on checkout?)

A thread local variable is set with the ID of the current Tenant very early on in request processing.  Among other things, we execute "ALTER SESSION SET CURRENT_SCHEMA ..." to "pin" this Connection to the schema of the current Tenant.  We don't "undo" this on checkin -- we simply execute it upon every checkout.

As you note, this allows our pooled Connections to be interchangeable amongst all Tenants.
 

it wouldn't be too hard to support what you want, to define an Exception that could be thrown from onCheckout to signal not a failure but a client ejection. the Exception might indicate whether the Connection should be interpreted as broken or returned to the pool for future use, or else the library could just err conservatively and purge. but...

1) i don't want to integrate this as a new feature until i get 0.9.2-final released and start the next dev series. the current release [ 0.9.2-pre8 ] is pretty much a release candidate. if there are no showstoppers, i hope to be done this series within a few weeks.

Understood.

 
2) i'm not sure that inside the pooling library is the right place to do what you want. do you think it might be better to wrap a facade around DataSource and do your tenant-specific stuff there? DataSource is a pretty easy interface to wrap.

That's what I've already done, but I'm not sure, at this level, how to tell c3p0 to "toss" a Connection.  For example, I want to test Connections to see if they need to be reestablished (e.g., they're currently connected to a node that needs to be removed from the cluster for maintenance).  c3p0 already conveniently has this "retry" logic built-in; so, implementing this behavior in a ConnectionCustomizer or ConnectionTester is trivial. However, as my original email describes, I have no means of aborting a request altogether which could happen, if, say the Connection initialization ("ALTER SESSION...") fails.

Is there any way to tell c3p0 to "toss" a Connection?


that said, i'm not opposed to adding this, if it would be helpful. but it won't be part of 0.9.2.

Personally, I think this could be VERY helpful (certainly to me  :-).  But I'm not sure I can wait until 0.9.2.  ;-)

Suggestions?

 
     smiles,
        steve



On Dec 19, 2012, at 4:07 PM, John Hardin wrote:

> I'm working on a multi-tenant application using c3p0.  I'm managing a single connection pool for all Tenants.  Upon checkout, I want to test the connection as well as initialize it for use by the current Tenant (which is established early in request processing).
>
> ConnectionCustomizer.onCheckout() or (Unified)ConnectionTester.activeCheckConnection() seem like ideal locations for my test and initialization logic. In some cases, I want to toss the current connection, and retrieve another from the pool (or create a new connection as necessary).  The "retry" built into the callers of these functions implements this very logic.
>
> HOWEVER, in other cases, I want to abort the current request altogether, and the caller's retry logic seems to prevent me from doing so.
>
> While ConnectionCustomizer.onCheckout() is declared to throw Exception, the caller catches Exception, tosses the connection, and tries again.  ConnectionTester.activeCheckConnection() returns the status of the connection, but there is no status for "abort the request".  So, in both locations, I see no way to abort the current request.  :-(
>
> Any ideas?
>
> Thank you, in advance, for your assistance.
>
> -John Hardin
>
>
> ------------------------------------------------------------------------------
> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
> Remotely access PCs and mobile devices and provide instant support
> Improve your efficiency, and focus on delivering more value-add services
> Discover what IT Professionals Know. Rescue delivers
> http://p.sf.net/sfu/logmein_12329d2d_______________________________________________
> c3p0-users mailing list
> c3p0-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/c3p0-users