Em 08-01-2013 14:13, Jeremy Evans escreveu:
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 for the prompt reply, Jeremy!
We've decided to support multiple templates recently and we thought that
the fastest approach would be to just set up the search_path in a before
filter to get it working. Then we currently only allow a single template
usage at a time and we set up the current template (schema) in the users
table current_template column. This didn't require much changes to the
application and we could deploy it and get it running.
Of course a better approach is to just be explicit about the schema all
the time but that requires more time to change and review the entire
applications (Rails and Grails ones). We can do that later but for the
time being if we can get it working with less changes it would be less
error prone...
Your lazy tip (Sequel.delay) is quite interesting :) I wasn't aware of
it, thanks for sharing. But there are also a few other places where I
generate the SQL myself and I'd have to update it too among some other
parts where some joins are used... So it surely is a good change but
something that requires more time before getting it into production.
The Rack middleware trick is a good one, but there is a problem for my
situation where I extract the schema from the current user table and the
current user is retrieved by Devise (Warden) in a later middleware. I'd
probably have to add the middleware right next Warden, somehow...
Grails implements this as a lazy operation in the sense that it won't
checkout the connection from the pool until it is requested at some
point of the request. For our case it doesn't help much since we get the
schema from the database anyway, but maybe we may change that in the
future to make the schema a request param.
I'm not too much worried about this currently since almost all (if not
all) our requests involve database queries. But I understand that the
possibility of returning the connection to the pool as soon as possible
would be great when we're performing some Solr request for instance...
Maybe we can review our entire app at some point to make the schema
always explicit and avoid this shortcoming. On the other hand, it would
become harder to ensure we didn't forget to set the correct schema in
all queries all the time...
But thank you for your insight, I think middlewares are the way to go, I
just need to figure out how to get the middleware to run after Warden's
one and see if it works with Devise's authentication system.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
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.
