Hi all. I bumped into the same problem when creating multi-tenant Rails application with PostgreSQL schemas. I tried what Pat suggested and added:
sql_query_pre = SET search_path TO 2,public Where, 2 is the ID of my tenant. However, when I ran rake ts:in, Thinking Sphinx has rewritten configuration file and indexed my default (public) schema instead. Anyway, I do not think its the correct approach to update the Rails.env.sphinx.conf, since there are many tenants and you need some kind of rotation to index each tenants data. I think the easier is to create a custom script/Rake task/whatever, which will loop through all your tenants and somehow add the schema selection to the configuration file, and then finally use indexer directly: indexer --all --rotate -c PATH_TO_CONF However, in this case one tenants data will overwrite others, unless we also set the location of the index, which needs more tweaking on the Rails side to use the correct one. Pat, what is the best approach to tackle this problem? I think ideally I would want Sphinx to index all my schemas. Or in the worst case to have per-tenant sphinx configuration files with own index store and schema selection. Is there any way Thinking Sphinx can assist? Is there anything similar on the roadmap? Or am I missing something completely? Thanks all! On Saturday, January 22, 2011 2:02:41 PM UTC+2, Pat Allan wrote: > > Hi Praveen > > Can you select a schema via a SQL statement? If so, you could add a line > in your sources something like the following: > sql_query_pre = SQL STATEMENT TO CHOOSE A SCHEMA > > You will find existing sql_query_pre lines in your sources - you don't > need to replace these, Sphinx will accept as many as you put in. > Unfortunately, there's no way to automatically set this via Thinking Sphinx > at the moment, though. > > -- > Pat > > On 21/01/2011, at 6:16 PM, praveen wrote: > > > Thanks Pat. > > > > We can create schemas in a postgresql database to group our tables in > > that database. By default postgresql has a schema "public" which is > > set by default and contains all the tables created in the database. > > > > So , for my multitenant application , i try to create different > > schamas in a database for different tenants created. So when a user > > requests for a tenant , the application set the schema to the > > particular tenant. so the data for the tenant is pulled from the > > tables in that particular schema. So when i use sphinx to indes my > > database tables, the indexer indexes only the public schema and i have > > no options to specify which schema to be indexed or the sphinx itself > > does not have the feature to select all the schemas available and > > index them , which i can filter in my application to show search > > results depending on from which tenant is the the search requested. > > > > Can you tell if there is any option to atleast specify the schema > > along with the host, usr,pass,db details in the config file?? > > > > On Jan 21, 4:53 am, Pat Allan <[email protected]> wrote: > >> Hi Praveen > >> > >> When you talk about different schemas, do you mean different databases, > each one for a different tenant? If so, there's no simple way to do this > with Thinking Sphinx. You may need to create your own script to modify the > generated configuration file, or run separate searchd instances for each > tenant and change the port and/or address depending on the tenant. > >> > >> Or do you mean something else when you say schema? I'm not sure. > >> > >> Cheers > >> > >> -- > >> Pat > >> > >> On 20/01/2011, at 7:28 PM, praveen wrote: > >> > >>> Hi Everyone, > >> > >>> I would like to know , how can i use thinking sphinx/sphinx to index > >>> all the schemas in a postgres database. > >> > >>> I am using Postgresql Database for my Multitenant application, where i > >>> differentiate each tenant by Postgresql shema , which means , I > >>> connect to the particular schema , when the user requests for the > >>> particular tenant. > >> > >>> To explain in detail, I have one rails application which will connect > >>> to different schema in my postgresql database according to the tenant > >>> id requested, to behave as a multitenant application. > >> > >>> Since i have only one application , and i have to use the same > >>> thinking sphinx plugin for indexing, i am not able configure to index > >>> tables from all the schemas i have in my postgresql database. > >> > >>> Please help. Thanks in advance. > >> > >>> -- > >>> You received this message because you are subscribed to the Google > Groups "Thinking Sphinx" group. > >>> To post to this group, send email to > >>> [email protected]<javascript:> > . > >>> To unsubscribe from this group, send email to > [email protected] <javascript:>. > >>> For more options, visit this group athttp:// > groups.google.com/group/thinking-sphinx?hl=en. > > > > -- > > You received this message because you are subscribed to the Google > Groups "Thinking Sphinx" group. > > To post to this group, send email to > > [email protected]<javascript:> > . > > To unsubscribe from this group, send email to > [email protected] <javascript:>. > > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/zIVsCnFwZNQJ. 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/thinking-sphinx?hl=en.
