Hi Julian, It's hard to say where your issue is (I'm unfamiliar with PGbouncer) but yes, setting the search_path on every connection ensures the schema name spacing remains coherent. You can get the gist of this at https://github.com/holodigm/landlady/blob/master/lib/landlady.rb#L11
Regards, Kym On Friday, March 17, 2017 at 11:24:41 AM UTC+11, Julian Connor wrote: > > Hey Kym, > > Please allow me to unearth this ancient thread. :) > > Last week we attempted to switch to a multi-tenanted approach using the > Apartment gem and I think we ran into a similar issue. We did a bit of > testing in our staging environment and everything seemed fine, but when we > came out of maintenance mode in production, we started seeing a number of > errors that made it look like the search_path was being lost or incorrectly > set during the request. > > The current theory is that, while under load, our Unicorn workers > modifying a cached (?) schema_search_path. > > Some info on our setup- > > Rails 4.2.7.1 > Ruby 2.1.5 > Unicorn > PGBouncer to pool PG connections amongst heroku dynos > > We're only using two schemas: grailed and public. The public schema > contains shared models; users in our case. All other models exist in the > grailed schema. We were seeing a lot of errors where ActiveRecord > successfully retrieved a resource out of the grailed schema but was unable > to follow an association. > > E.g., Listing.find(100) would run successfully but > Listing.find(100).designer would fail, only under load. Both models exist > in the grailed schema. This behavior leads us to believe that "grailed" was > being removed from the schema search path before the request could complete. > > Does anyone have any insight into why this would happen? Is it as simple > as reseting the ActiveRecord connection on every request? > > We're doing some load testing and trying to reproduce the error on another > staging machine. We haven't had any success thus far. > > Any help is appreciated! > > Best, > Julian > > On Friday, November 4, 2011 at 8:19:41 AM UTC-4, Kym McInerney wrote: >> >> Hi Robert, >> >> I agree with Eric, that Unicorn is losing state on the Postgres >> session. I'd suggest this is a combination of AREL request caching and >> new sessions failing to check the subdomain space correctly. In >> Postgres, set_search_path is also cached. I had a similar issue >> recently, which was rectified by clearing the ActiveModel connection >> session on each new session request. With moderate traffic this is OK, >> for higher traffic not so depending on your underlying architecture. >> >> I'd look at how the AREL sessions are handling the Postgres >> 'set_search_path' as this is why you are getting data from one scheme >> via another schema sub-domain. In particular look at how apartment is >> doing this, as apartment only takes the grunt work out of coding the >> controllers but IMHO doesn't really remove the inherent complexities >> in multi-controller session handling across schemas. >> >> The simplest (and stablest) approach I've found for the sub-domain/ >> schema 'pattern' in a multitenant app is to create a before_filter >> subdomain check method in AppController that sets the Postgres search >> path based on your tenancy namespaces as the request comes in, Rails >> does the rest and sessions are inherently assigned to a specific >> namespace through the stack. Benefits are numerous, in particular code >> maintenance, but this is reusable across asset collections, >> templating , authentication etc. >> >> Also, I found the Postgres adapter for 3.1 is missing a couple of >> pieces required for stable multi-tenancy, which I believe have been >> merged into 3.1.1, relating to session schema checking. >> >> Cheers, >> K >> >> On Nov 4, 12:31 pm, Robert Hameeteman >> <robert.hameete...@pageuppeople.com> wrote: >> > Hi, >> > >> > -- The issue -- >> > We have a multi-tenant system, which identifies the user by an URL- >> > prefix and database table (This database is excluded from the >> > apartment model), from the database look-up we select which apartment >> > (Ruby gemhttp://rubygems.org/gems/apartment) in the Postgres server >> > to select. >> > >> > This system has lately been developed and we encountered issues when >> > moving onto our pre-production server (Used for user testing, similar >> > configuration to production). >> > >> > Appears under moderate load (Only handful of users accessing both >> > systems at the same time), the system progressively worsens and >> > appears after a while to select the wrong apartment. i.e. We are under >> > Client A URL, but on next page load we get Client B data. >> > >> > We have two calls to the database table look up on the page load, one >> > for the database switching and another to define client styles (This >> > needs to be fixed later but useful for debugging at the moment.). >> > >> > So in addition to the above data mix-up, we have styles mix-up. More >> > importantly we can have styles from one client and data from another. >> > >> > -- Investigation so far -- >> > We have done some basis analysis so far and found that on the points >> > where it degrades to the point of mixing up client data (i.e. >> > selecting wrong apartment) majority of the request is relying on the >> > table in the seperate database to the apartments which defines which >> > client is selected.(Approximately 95% or so) >> > >> > We have been able to do a hot-fix that has these client values >> > hardcoded into ruby rather than doing database calls to the shared >> > database and this has proven to resolve the issue. >> > >> > Of course we are looking for a longer term fix, server load times >> > under these conditions are 50% webserver 50% database. >> > >> > -- Server Configuration -- >> > >> > We are running our Ruby environment on Unicorn in Rails 3.1 in our UAT >> > environment, (Step before production with same configuration). >> > >> > We have two clients running on this environment, using the apartment >> > model (http://rubygems.org/gems/apartment) in Postgres server. >> > >> > The way we identify which client the user belongs to (They both have >> > Unique URL's per client), is that we haved a database and table within >> > that, that sits apart from the apartment model that contains >> > information regarding all the clients and which URL belongs to either. >> > >> > On page load, we check the shared database for which client the URL >> > belongs to and then do a database switch dependant on the results. >> > >> > Any help or tips would be welcome, or if more clarification is >> > required. >> > >> > Cheers ! > > -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To unsubscribe from this group and stop receiving emails from it, send an email to rails-oceania+unsubscr...@googlegroups.com. To post to this group, send email to rails-oceania@googlegroups.com. Visit this group at https://groups.google.com/group/rails-oceania. For more options, visit https://groups.google.com/d/optout.
