On Tuesday, January 8, 2013 6:18:41 AM UTC-8, Rodrigo Rosenfeld Rosas wrote:
>
> Hi, I'd like to get some advice on how to properly handle connections in
> my application.
>
> We're deploying a multi-tenant(-like) strategy using PG schemas. Basically
> we do that by setting the search_path properly on a before filter. Our
> application is composed by a Grails one and a Rails one (running Sequel)
> and I use the same approach in both.
>
> But "set search_path to xxx" will only take effect for that particular
> connection. For Grails this is a non-issue as long as I don't request a new
> Hibernate session (which I don't) since it will keep the same connection
> for all queries to the database.
>
> For the Rails app this is currently not an issue since I'm deploying it
> using Unicorn (multi-process approach). But I'd like to be able to change
> it to a threaded-based approach (like Puma or JRuby) if I want to and
> currently I can't because I wouldn't have any guarantees that the
> connection with the proper search_path set in the before filter will be the
> same next time I query the database from a controller for instance.
>
> So, I'd like to ask for any advices on how I could ensure I'll always use
> the same connection throughout the entire request cycle (using a pool of
> connections, of course)... Any ideas?
>
> Thanks in advance!
>
If you must change the search_path, you should use a rack middleware with
Database#synchronize to ensure that the same connection is used for the
entire request, setting the search path inside the Database#synchronize
block, before calling the next middleware. Note that this limits
concurrency, since you can then only serve as many concurrent requests as
you have database listeners. That's the price you have to pay for
transparent connection handling in a schema-per-tenant system.
The alternative is changing your app code so that tables are explicitly
qualified instead of using the search path. This can probably be
implemented by storing the schema in a thread local variable and making the
model use it if available:
class Foo < Sequel::Model(DB.from(Sequel.delay{Thread.current[:pg_schema]
? Sequel.qualify(Thread.current[:pg_schema], :foos) : :foos}))
end
With this approach, you'll probably have to make similar changes elsewhere
in your codebase. This approach is better as it doesn't hurt concurrency,
but it probably requires more changes to your app. If it works for you,
it's the approach I would recommend.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/2lVsF2U5tgsJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
