Обсуждение: Database schemas: search_path

Поиск
Список
Период
Сортировка

Database schemas: search_path

От
"Igor Maciel Macaubas"
Дата:
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,
 

Re: Database schemas: search_path

От
Bruno Wolff III
Дата:
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.

Re: Database schemas: search_path

От
Jaime Casanova
Дата:
 --- 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

Re: Database schemas: search_path

От
"Igor Maciel Macaubas"
Дата:
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