Thread: Naive schema questions

I have a number of web applications which have a large amount of their
logic written in plpgsql. For each particular application I have a
separate instance of a database. If I need to update functions I have to
connect to each database and then run \i fn_function_update.sql.

I imagined schemas might allow me to globally update functions across a
database hosting many schemas with the same structure. In this scenario
my webapp would always connect to the same database, but address
different schemas, so that

    mysolution.schema_A.people would be addressed for site 'A'
    and
    mysolution.schema_B.people would be addressed for site 'B'

(I'm assuming here that I can set the context of the schema at
connection by a plpgsql line that sets the current search path.)

However

     Schemas also contain other kinds of named objects, including data
     types, functions, and operators.
                                      (html reference: ddl-schemas.html)

seems to suggest that the functions are schema specific.

I suppose I'm trying to think of how I might implement the second point
in this list (also from dd-schemas.html):

    There are several reasons why one might want to use schemas:
    - To allow many users to use one database without interfering with
      each other.
    - To organize database objects into logical groups to make them more
      manageable.
    - Third-party applications can be put into separate schemas so they
      cannot collide with the names of other objects.

Thanks for any observations
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Am Donnerstag, 27. Mai 2004 13:15 schrieb Rory Campbell-Lange:
> I imagined schemas might allow me to globally update functions across a
> database hosting many schemas with the same structure.

Put your data tables in separate schemas, put the functions in yet another
schema, and then when you connect set the schema search path to "dataschema,
functionschema" (or maybe vice versa).

On 5/27/2004 7:15 AM, Rory Campbell-Lange wrote:

> I have a number of web applications which have a large amount of their
> logic written in plpgsql. For each particular application I have a
> separate instance of a database. If I need to update functions I have to
> connect to each database and then run \i fn_function_update.sql.
>
> I imagined schemas might allow me to globally update functions across a
> database hosting many schemas with the same structure. In this scenario
> my webapp would always connect to the same database, but address
> different schemas, so that
>
>     mysolution.schema_A.people would be addressed for site 'A'
>     and
>     mysolution.schema_B.people would be addressed for site 'B'
>
> (I'm assuming here that I can set the context of the schema at
> connection by a plpgsql line that sets the current search path.)

That is so.

>
> However
>
>      Schemas also contain other kinds of named objects, including data
>      types, functions, and operators.
>                                       (html reference: ddl-schemas.html)
>
> seems to suggest that the functions are schema specific.

It is even better. The property that set's your "schema context" is
called search_path. This contains a list of schema names. For an
unqualified (schema name not explicitly given) object, be that a table,
sequence, view, function or whatever, the system looks in all those
schemas in that particular order and uses the first found.

With that, you can have your common or shared objects in a central
schema "schema_common", and everything that's application specific in
"schema_A", "schema_B". The connection just has to set the search_path
at the beginning with

     set search_path = schema_A, schema_common;

and done.

>
> I suppose I'm trying to think of how I might implement the second point
> in this list (also from dd-schemas.html):
>
>     There are several reasons why one might want to use schemas:
>     - To allow many users to use one database without interfering with
>       each other.
>     - To organize database objects into logical groups to make them more
>       manageable.
>     - Third-party applications can be put into separate schemas so they
>       cannot collide with the names of other objects.

Yes, yes and yes. Plus the ability for you to do cross database joins
for global analyzing for example.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

----- Original Message -----
From: "Peter Eisentraut" <peter_e@gmx.net>
To: "Rory Campbell-Lange" <rory@campbell-lange.net>
Cc: "Postgresql General List" <pgsql-general@postgresql.org>
Sent: Thursday, May 27, 2004 1:10 PM
Subject: Re: [GENERAL] Naive schema questions


> Am Donnerstag, 27. Mai 2004 13:15 schrieb Rory Campbell-Lange:
> > I imagined schemas might allow me to globally update functions across a
> > database hosting many schemas with the same structure.
>
> Put your data tables in separate schemas, put the functions in yet another
> schema, and then when you connect set the schema search path to
"dataschema,
> functionschema" (or maybe vice versa).

Or when you make the calls in the web app use the following:

SELECT function_schema.function1(arg1, arg2);

instead of just:

SELECT function1(arg1, arg2);

But like Peter said have a schema per client/"instance" of your database.

Nick

> On 5/27/2004 7:15 AM, Rory Campbell-Lange wrote:
>
>> seems to suggest that the functions are schema specific.
>
> It is even better. The property that set's your "schema context" is
> called search_path. This contains a list of schema names. For an
> unqualified (schema name not explicitly given) object, be that a table,
>  sequence, view, function or whatever, the system looks in all those
> schemas in that particular order and uses the first found.

And where do tables created with "CREATE LOCAL TEMPORARY TABLE..." fit
into this, like if say a local temp table where created that has the same
name as an existing normal (i.e., not a local temp) table? And what if I
do an explicit DROP of the local temp table rather than relying on the
automatic, end-of-session clean-up? Is there any risk of losing the
normal table?

--Berend Tober

Fabulous stuff! I am so delighted I chose Postgresql a couple of year
ago. Thank you for the valuable insights. A comment or two below:

On 27/05/04, Peter Eisentraut (peter_e@gmx.net) wrote:
> Am Donnerstag, 27. Mai 2004 13:15 schrieb Rory Campbell-Lange:
> > I imagined schemas might allow me to globally update functions across a
> > database hosting many schemas with the same structure.

> Put your data tables in separate schemas, put the functions in yet
> another schema, and then when you connect set the schema search path
> to "dataschema, functionschema" (or maybe vice versa).

On 27/05/04, Nick Barr (nicky@chuckie.co.uk) wrote:
> > Put your data tables in separate schemas, put the functions in yet
> > another schema, and then when you connect set the schema search path
> > to "dataschema, functionschema" (or maybe vice versa).

> Or when you make the calls in the web app use the following:
>
> SELECT function_schema.function1(arg1, arg2);
> instead of just:
> SELECT function1(arg1, arg2);
> But like Peter said have a schema per client/"instance" of your database.

Is it ok to use the public schema for the functions? It means it is that
much easier to reload the functions as one wouldn't need to specify the
search_path.

On 27/05/04, Jan Wieck (JanWieck@Yahoo.com) wrote:
...
> It is even better. The property that set's your "schema context" is
> called search_path. This contains a list of schema names. For an
> unqualified (schema name not explicitly given) object, be that a table,
> sequence, view, function or whatever, the system looks in all those
> schemas in that particular order and uses the first found.
>
> With that, you can have your common or shared objects in a central
> schema "schema_common", and everything that's application specific in
> "schema_A", "schema_B". The connection just has to set the search_path
> at the beginning with
>
>     set search_path = schema_A, schema_common;

This is brillliant. I didn't note this in the documentation.

> >I suppose I'm trying to think of how I might implement the second point
> >in this list (also from dd-schemas.html):
...
> >    - To organize database objects into logical groups to make them more
> >      manageable.
...
> Yes, yes and yes. Plus the ability for you to do cross database joins
> for global analyzing for example.

Just a question on this, Jan. Would one expect UNIONS for this sort of
work?

I just did this which is useful anyway:
schematest=> SELECT
            (select count(id) from b.messages)
            +
            (select count(id) from a.messages);
 ?column?
 ----------
         5
         (1 row)

I see the horizons expanding! Common data (I often have an 'info' table)
can be shared between schemas. I think my search_patch might go:

    this_schema, info_schema, public_schema

Thanks very much for the information.

Kind regards,
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

On 5/27/2004 6:03 PM, Rory Campbell-Lange wrote:
> Just a question on this, Jan. Would one expect UNIONS for this sort of
> work?
>
> I just did this which is useful anyway:
> schematest=> SELECT
>             (select count(id) from b.messages)
>             +
>             (select count(id) from a.messages);
>  ?column?
>  ----------
>          5
>          (1 row)
>
> I see the horizons expanding! Common data (I often have an 'info' table)
> can be shared between schemas. I think my search_patch might go:

You can mix those in queries however you want. They are just namespaces
with some additional security (even if you grant public access to an
object inside a schema, one still needs access to the schema itself).
The search path let's you hide one schemas objects behind another ones
by chosing the order. You can use qualified or unqualified names and
different search path's where one or the other makes sense in your
application- and data-design. After all, all the objects reside in the
same database and all access is covered by the same transaction.

The problem with expanded horizons is that one has more possibilities to
screw it up at the same time he get's more flexibility. Well used, this
is a powerfull feature. Poorly applied and inconsistently used it can
become a maintenance nightmare.


Jan

>
>     this_schema, info_schema, public_schema
>
> Thanks very much for the information.
>
> Kind regards,
> Rory


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

<btober@computer.org> writes:
> And where do tables created with "CREATE LOCAL TEMPORARY TABLE..." fit
> into this, like if say a local temp table where created that has the same
> name as an existing normal (i.e., not a local temp) table?

Temp tables live in a schema that is effectively inserted into your
search path ahead of whatever schema(s) are explicitly listed there.
For instance, suppose

    CREATE SCHEMA a;
    SET search_path = a, public;
    CREATE TABLE t1 (...);
    -- t1 is created in schema a
    SELECT * FROM t1;
    -- same as SELECT * FROM a.t1;

Now if I do

    CREATE TEMP TABLE t1 (...);

then SELECT * FROM t1 will reference the temp table ... but I can still
get to the permanent table by explicitly qualifying it as "a.t1".

> And what if I
> do an explicit DROP of the local temp table rather than relying on the
> automatic, end-of-session clean-up?

You drop the temp table.

> Is there any risk of losing the normal table?

Only if you do it twice --- after the initial DROP, the permanent table
would come back "into view".

            regards, tom lane