Обсуждение: Database schemas: search_path
Hi all,
I've organized my database design in multiple schemas, and migrated my existing tables to different schemas by using a simple how-to I found in this list history.
But what I couldn't find was a way to make this schemata fully transparent to my application - which does selects directly to tables instead to schema.table. I know that I can always set the search_path environmnet variable, but I have to do it on each session. I'd like to know if there is a way to make my set search_path persistent on the database, no matter the session.
I believe that this is supported, but couldn't find out how to make it. Can someone help me?
Having to add the 'set search_path = xxx,yyyy,zzzzz' everytime I connect is pain for me and my application.
Another question I have is:
Let's say I have a simple database with 100 tables, divided in 10 schemas with 10 tables in each. Let's say that my last schema is 10th_schema and my last table is z_table (last by creation - is the most recent created table, so I assume it's the last table on the database). My search_path is ascending from the 1st_schema to the 10th_schema.
Would my querie times/performance on table z_table be faster if this table was created on the 1st_schema ?
How does postgres make this search on the search_path to find where a table is in?
Thanks for your help, cya!
Regards,
On Thu, Oct 14, 2004 at 09:42:45 -0300, Igor Maciel Macaubas <igor@providerst.com.br> wrote: > Hi all, > > I've organized my database design in multiple schemas, and migrated my existing tables to different schemas by using asimple how-to I found in this list history. > But what I couldn't find was a way to make this schemata fully transparent to my application - which does selects directlyto tables instead to schema.table. I know that I can always set the search_path environmnet variable, but I haveto do it on each session. I'd like to know if there is a way to make my set search_path persistent on the database, nomatter the session. > I believe that this is supported, but couldn't find out how to make it. Can someone help me? Use ALTER DATABASE to set per database defaults.
--- Bruno Wolff III <bruno@wolff.to> escribió: > On Thu, Oct 14, 2004 at 09:42:45 -0300, > Igor Maciel Macaubas <igor@providerst.com.br> > wrote: > > Hi all, > > > > I've organized my database design in multiple > schemas, and migrated my existing tables to > different schemas by using a simple how-to I found > in this list history. > > But what I couldn't find was a way to make this > schemata fully transparent to my application - which > does selects directly to tables instead to > schema.table. I know that I can always set the > search_path environmnet variable, but I have to do > it on each session. I'd like to know if there is a > way to make my set search_path persistent on the > database, no matter the session. > > I believe that this is supported, but couldn't > find out how to make it. Can someone help me? > > Use ALTER DATABASE to set per database defaults. > Hi, set the parameter in the postgresql.conf Regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
Hi, Thanks, I did it using alter database and worked fine. Thanks for all! Regards, Igor -- igor@providerst.com.br ----- Original Message ----- From: "Jaime Casanova" <systemguards@yahoo.com> To: <pgsql-admin@postgresql.org> Sent: Thursday, October 14, 2004 1:19 PM Subject: Re: [ADMIN] Database schemas: search_path > --- Bruno Wolff III <bruno@wolff.to> escribió: >> On Thu, Oct 14, 2004 at 09:42:45 -0300, >> Igor Maciel Macaubas <igor@providerst.com.br> >> wrote: >> > Hi all, >> > >> > I've organized my database design in multiple >> schemas, and migrated my existing tables to >> different schemas by using a simple how-to I found >> in this list history. >> > But what I couldn't find was a way to make this >> schemata fully transparent to my application - which >> does selects directly to tables instead to >> schema.table. I know that I can always set the >> search_path environmnet variable, but I have to do >> it on each session. I'd like to know if there is a >> way to make my set search_path persistent on the >> database, no matter the session. >> > I believe that this is supported, but couldn't >> find out how to make it. Can someone help me? >> >> Use ALTER DATABASE to set per database defaults. >> > > Hi, > set the parameter in the postgresql.conf > > Regards, > Jaime Casanova > > _________________________________________________________ > Do You Yahoo!? > Información de Estados Unidos y América Latina, en Yahoo! Noticias. > Visítanos en http://noticias.espanol.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org