Thread: how to use multiple schema's
Hi, I have multiple applications, i want to create separate schema for each of the application. I dont want to change search path, since have only one user. I went through couple of post's talking about some patch related to that. It seems that patch got rejected. Any help is appreciated. Thanks and Regards, Himanshu
On Tue, Sep 1, 2009 at 2:58 PM, Himanshu Gupta<himanshu.gupta@semanticbits.com> wrote: > Hi, > > I have multiple applications, i want to create separate schema for each of > the application. I dont want to change search path, since have only one > user. I went through couple of post's talking about some patch related to > that. It seems that patch got rejected. Any help is appreciated. I'm not sure how you want this to happen. Do you want a single user to see ALL the schemas at once? Or do you want to set the search path each time you connect? Is there a reason for having multiple apps hit multiple schemas but use only one account? Multiple accounts (one for each app) would certainly make things more manageable. But mainly I'm just trying to get a grip on how you're trying to get this to work.
So, can you just set search_path to point to all the schemas at once then? Or does that not meet your needs? On Wed, Sep 2, 2009 at 7:19 AM, Himanshu Gupta<himanshu.gupta@semanticbits.com> wrote: > I want single user to see all the schema at once. Database server is on > client side and it takes time to get new user or new database instance, So i > am trying to find a workaround for that. > > Thanks, > Himanshu > > On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: > >> On Tue, Sep 1, 2009 at 2:58 PM, Himanshu >> Gupta<himanshu.gupta@semanticbits.com> wrote: >>> >>> Hi, >>> >>> I have multiple applications, i want to create separate schema for each >>> of >>> the application. I dont want to change search path, since have only one >>> user. I went through couple of post's talking about some patch related to >>> that. It seems that patch got rejected. Any help is appreciated. >> >> I'm not sure how you want this to happen. Do you want a single user >> to see ALL the schemas at once? Or do you want to set the search path >> each time you connect? Is there a reason for having multiple apps hit >> multiple schemas but use only one account? Multiple accounts (one for >> each app) would certainly make things more manageable. >> >> But mainly I'm just trying to get a grip on how you're trying to get >> this to work. > > -- When fascism comes to America, it will be intolerance sold as diversity.
Hi Scott, Thanks for response, bit if I point it to all the schema, I am just wondering how data insertion will work, I have multiple ables with same name in these applications. -HImanshu On Sep 2, 2009, at 10:48 AM, Scott Marlowe wrote: > you just set search_path to point to all the schemas at once > then? Or does that not meet your needs?
I want single user to see all the schema at once. Database server is on client side and it takes time to get new user or new database instance, So i am trying to find a workaround for that. Thanks, Himanshu On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: > On Tue, Sep 1, 2009 at 2:58 PM, Himanshu > Gupta<himanshu.gupta@semanticbits.com> wrote: >> Hi, >> >> I have multiple applications, i want to create separate schema for >> each of >> the application. I dont want to change search path, since have >> only one >> user. I went through couple of post's talking about some patch >> related to >> that. It seems that patch got rejected. Any help is appreciated. > > I'm not sure how you want this to happen. Do you want a single user > to see ALL the schemas at once? Or do you want to set the search path > each time you connect? Is there a reason for having multiple apps hit > multiple schemas but use only one account? Multiple accounts (one for > each app) would certainly make things more manageable. > > But mainly I'm just trying to get a grip on how you're trying to get > this to work.
OOPS I got caught by the reply verses reply-all and only sent this to Scott. Plus I see it has already been addressed in a later message. raa Scott Marlowe wrote: > So, can you just set search_path to point to all the schemas at once > then? Or does that not meet your needs? The only issue I see is if two or more of the "applications" have a table named the same, like "users". Which gets used? The first found in the search_path? \\||/ Rod -- > > On Wed, Sep 2, 2009 at 7:19 AM, Himanshu > Gupta<himanshu.gupta@semanticbits.com> wrote: >> I want single user to see all the schema at once. Database server is on >> client side and it takes time to get new user or new database instance, So i >> am trying to find a workaround for that. >> >> Thanks, >> Himanshu >> >> On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: >> >>> On Tue, Sep 1, 2009 at 2:58 PM, Himanshu >>> Gupta<himanshu.gupta@semanticbits.com> wrote: >>>> Hi, >>>> >>>> I have multiple applications, i want to create separate schema for each >>>> of >>>> the application. I dont want to change search path, since have only one >>>> user. I went through couple of post's talking about some patch related to >>>> that. It seems that patch got rejected. Any help is appreciated. >>> I'm not sure how you want this to happen. Do you want a single user >>> to see ALL the schemas at once? Or do you want to set the search path >>> each time you connect? Is there a reason for having multiple apps hit >>> multiple schemas but use only one account? Multiple accounts (one for >>> each app) would certainly make things more manageable. >>> >>> But mainly I'm just trying to get a grip on how you're trying to get >>> this to work. >> > > >
On Wed, Sep 02, 2009 at 10:52:07AM -0400, Himanshu Gupta wrote: > Thanks for response, bit if I point it to all the schema, I am just > wondering how data insertion will work, I have multiple ables with > same name in these applications. Do you know that you can schema qualify table names don't you? For example, if you have a "users" table in schemas "app1" and "app2", you can refer to the table in the first schema as: SELECT uid, name FROM app1.users; -- Sam http://samason.me.uk/
Sam Mason wrote: > On Wed, Sep 02, 2009 at 10:52:07AM -0400, Himanshu Gupta wrote: >> Thanks for response, bit if I point it to all the schema, I am just >> wondering how data insertion will work, I have multiple ables with >> same name in these applications. > > Do you know that you can schema qualify table names don't you? For > example, if you have a "users" table in schemas "app1" and "app2", you > can refer to the table in the first schema as: > > SELECT uid, name > FROM app1.users; Himanshu didn't mention if he has access to the code for the applications or can edit them. \\||/ Rod --
Here is my first post which missed the list. Not that it makes that much of a difference now as all my points have been addressed by others. raa Himanshu Gupta wrote: > Hi, > > I have multiple applications, i want to create separate schema for each > of the application. I dont want to change search path, since have only > one user. I went through couple of post's talking about some patch > related to that. It seems that patch got rejected. Any help is appreciated. Why not change the search_path? There is only way I'm aware of to get around changing the search_path; You must code the schema name(s) into the application. Or you could make the search_path change part of the application(s). I have put LedgerSMB, RT, and a couple of in-house applications into their own schema. Since each has it's own "user/role" for connecting to the database cluster having different (than the default) search_path per user isn't a problem. In fact I have a cluster with a database per tenant. Each tenant has (or can have) LedgerSMB, RT, and/or other applications in their database and each application is in it's own schema. But I digress/diverge. \\||/ Rod -- > > Thanks and Regards, > Himanshu > >