Em 08-01-2013 15:26, Rodrigo Rosenfeld Rosas escreveu:
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.
I was also thinking about another approach. Using an "after_connect"
proc to set the search_path to the one set by a thread local variable if
one is set. That way I don't need to always use the same connection. It
would also be much simpler to implement and wouldn't have much
drawbacks, right?
--
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.
