X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E06BC32AEF1
	for <pgsql-admin-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Oct 2004 13:37:33 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83445-06
	for <pgsql-admin-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Oct 2004 12:37:21 +0000 (GMT)
Received: from providerst.com.br (unknown [200.253.138.61])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BDAE32A17D
	for <pgsql-admin@postgresql.org>; Thu, 14 Oct 2004 13:37:16 +0100 (BST)
Brma: igor [192.168.1.104]
Received: from sistemas (igor [192.168.1.104])
	by providerst.com.br (8.11.6/8.11.6) with SMTP id i9ECU9p09415
	for <pgsql-admin@postgresql.org>; Thu, 14 Oct 2004 10:30:09 -0200
Message-ID: <001601c4b1eb$50f45fc0$6801a8c0@providerst.local>
From: "Igor Maciel Macaubas" <igor@providerst.com.br>
To: <pgsql-admin@postgresql.org>
Subject: Database schemas: search_path
Date: Thu, 14 Oct 2004 09:42:45 -0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0013_01C4B1D2.288611A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_20_30,
	HTML_FONTCOLOR_BLUE, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200410/178
X-Sequence-Number: 15171

This is a multi-part message in MIME format.

------=_NextPart_000_0013_01C4B1D2.288611A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all,

I've organized my database design in multiple schemas, and migrated my exis=
ting tables to different schemas by using a simple how-to I found in this l=
ist 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.=
=20
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 =3D 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 w=
ith 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 ascend=
ing 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,
Igor
--
igor@providerst.com.br

------=_NextPart_000_0013_01C4B1D2.288611A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#0000ff size=3D2>Hi all,</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff size=3D2>I've organized my database design in mu=
ltiple=20
schemas, and migrated my existing tables to different schemas by using a si=
mple=20
how-to I found in this list history.</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2>But what I couldn't find was a way to m=
ake this=20
schemata fully transparent to my application - which does selects directly =
to=20
tables instead to schema.table. I know that I can always set the search_pat=
h=20
environmnet variable, but I have to do it on each session. I'd like to know=
 if=20
there is a way to make my set search_path persistent on the database, no ma=
tter=20
the session. </FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2>I believe that this is supported, but c=
ouldn't=20
find out how to make it. Can someone help me?</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff size=3D2>Having to add the 'set search_path =3D=
=20
xxx,yyyy,zzzzz' everytime I connect is pain for me and my=20
application.</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff size=3D2>Another question I have is:</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2>Let's say I have a simple database with=
 100=20
tables, divided in 10 schemas with 10 tables in each. Let's say that my las=
t=20
schema is 10th_schema and my last table is z_table (last by creation - is t=
he=20
most recent created table, so I assume it's the last table on the database)=
. My=20
search_path is ascending from the 1st_schema to the 10th_schema.</FONT></DI=
V>
<DIV><FONT color=3D#0000ff size=3D2>Would my querie times/performance on ta=
ble=20
z_table be faster if this table was created on the 1st_schema ?</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2>How does postgres make this search on t=
he=20
search_path to find where a table is in?</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff size=3D2>Thanks for your help, cya!</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff size=3D2>Regards,</FONT></DIV>
<DIV><FONT color=3D#0000ff size=3D2>Igor<BR>--<BR><A=20
href=3D"mailto:igor@providerst.com.br">igor@providerst.com.br</A></FONT></D=
IV>
<DIV><FONT color=3D#0000ff size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0013_01C4B1D2.288611A0--