Hi All, 

 I work with Marc Fournier, for those who don't know, and have been
working extensively with the schemas feature for Hub.Org's new Account
Management package. Each client's data is stored in a seperate schema
set asside just for them (though they will never have direct access to
it, it helps us "balance" out the data. Each schema has the same set of
tables, with which different data is stored, depending on the client.

 I've run into two issues and I've been reading over the docs in
relation to schemas in the hopes that I could find a solution to my
problems. Here are the details of the issues:

 1. The first issue I'm running with, is that if I have several schemas
with the following name:

  public
  test_001
  test_002
  test_003

 the test_* schemas have the same tables listed in the their name
spaces, such that:

 Schema test_001 contains:

  test_contact;
  test_domains;
  test_accounts;
  ...
 
 Schema test_002 contains:

  test_contact;
  test_domains;
  test_accounts;
  ...

 etc.

 As you can see this is a nice, clean way to break down some datasets.
But, if I do:

 set search_path to public, test_001, test_002;

 I only get access to the tables in test_001 and public, the tables in
test_002 are not listed, and thus I do not see them on the screen while
doing a "\d".

2. Issue 2, is more a SQL issue then anything, but I'd like to be able
to do something to the affect of:

 SELECT * FROM test_*.test_domains ORDER BY domain_name;

 or better yet:

 SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td
ON (td.domain_id = ds.id);

 This would mean I can run one query to get all of the information I
need, rather then having to run a query for each client that I have to
list domains for. If I've got 10,000 clients, that's going to take a ton
of time to get the results I'm looking for...

 Does anyone know if any of these issues have been addressed in 7.4? I
see some references to schema based changes, but no details on what
those changes were. If not, are there any plans to do something like
that?
-- 
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services


Wed, 05 Nov 2003 16:54:38 -0400
Chris Bowlby <excalibur@hub.org>
Schema boggle...
Chris Bowlby wrote:

> Hi All, 
> 
>  I work with Marc Fournier, for those who don't know, and have been
> working extensively with the schemas feature for Hub.Org's new Account
> Management package. Each client's data is stored in a seperate schema
> set asside just for them (though they will never have direct access to
> it, it helps us "balance" out the data. Each schema has the same set of
> tables, with which different data is stored, depending on the client.
> 
>  I've run into two issues and I've been reading over the docs in
> relation to schemas in the hopes that I could find a solution to my
> problems. Here are the details of the issues:
> 
>  1. The first issue I'm running with, is that if I have several schemas
> with the following name:
> 
>   public
>   test_001
>   test_002
>   test_003
> 
>  the test_* schemas have the same tables listed in the their name
> spaces, such that:
> 
>  Schema test_001 contains:
> 
>   test_contact;
>   test_domains;
>   test_accounts;
>   ...
>  
>  Schema test_002 contains:
> 
>   test_contact;
>   test_domains;
>   test_accounts;
>   ...
> 
>  etc.
> 
>  As you can see this is a nice, clean way to break down some datasets.
> But, if I do:
> 
>  set search_path to public, test_001, test_002;
> 
>  I only get access to the tables in test_001 and public, the tables in
> test_002 are not listed, and thus I do not see them on the screen while
> doing a "\d".
> 
> 2. Issue 2, is more a SQL issue then anything, but I'd like to be able
> to do something to the affect of:
> 
>  SELECT * FROM test_*.test_domains ORDER BY domain_name;
> 
>  or better yet:
> 
>  SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td
> ON (td.domain_id = ds.id);
> 
>  This would mean I can run one query to get all of the information I
> need, rather then having to run a query for each client that I have to
> list domains for. If I've got 10,000 clients, that's going to take a ton
> of time to get the results I'm looking for...
> 
>  Does anyone know if any of these issues have been addressed in 7.4? I
> see some references to schema based changes, but no details on what
> those changes were. If not, are there any plans to do something like
> that?

You can't use a wildcard like that, but you can create a view like

     CREATE VIEW public.all_test_domains AS
         SELECT * FROM test_001.test_domains
         UNION ALL
         SELECT * FROM test_002.test_domains;

You have to change that view as customers (resp. schemas) come and go.


Jan

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


Wed, 05 Nov 2003 15:58:02 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Schema boggle...


On Wed, 5 Nov 2003, Jan Wieck wrote:

> Chris Bowlby wrote:
>
> > Hi All,
> >
> >  I work with Marc Fournier, for those who don't know, and have been
> > working extensively with the schemas feature for Hub.Org's new Account
> > Management package. Each client's data is stored in a seperate schema
> > set asside just for them (though they will never have direct access to
> > it, it helps us "balance" out the data. Each schema has the same set of
> > tables, with which different data is stored, depending on the client.
> >
> >  I've run into two issues and I've been reading over the docs in
> > relation to schemas in the hopes that I could find a solution to my
> > problems. Here are the details of the issues:
> >
> >  1. The first issue I'm running with, is that if I have several schemas
> > with the following name:
> >
> >   public
> >   test_001
> >   test_002
> >   test_003
> >
> >  the test_* schemas have the same tables listed in the their name
> > spaces, such that:
> >
> >  Schema test_001 contains:
> >
> >   test_contact;
> >   test_domains;
> >   test_accounts;
> >   ...
> >
> >  Schema test_002 contains:
> >
> >   test_contact;
> >   test_domains;
> >   test_accounts;
> >   ...
> >
> >  etc.
> >
> >  As you can see this is a nice, clean way to break down some datasets.
> > But, if I do:
> >
> >  set search_path to public, test_001, test_002;
> >
> >  I only get access to the tables in test_001 and public, the tables in
> > test_002 are not listed, and thus I do not see them on the screen while
> > doing a "\d".
> >
> > 2. Issue 2, is more a SQL issue then anything, but I'd like to be able
> > to do something to the affect of:
> >
> >  SELECT * FROM test_*.test_domains ORDER BY domain_name;
> >
> >  or better yet:
> >
> >  SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td
> > ON (td.domain_id = ds.id);
> >
> >  This would mean I can run one query to get all of the information I
> > need, rather then having to run a query for each client that I have to
> > list domains for. If I've got 10,000 clients, that's going to take a ton
> > of time to get the results I'm looking for...
> >
> >  Does anyone know if any of these issues have been addressed in 7.4? I
> > see some references to schema based changes, but no details on what
> > those changes were. If not, are there any plans to do something like
> > that?
>
> You can't use a wildcard like that, but you can create a view like
>
>      CREATE VIEW public.all_test_domains AS
>          SELECT * FROM test_001.test_domains
>          UNION ALL
>          SELECT * FROM test_002.test_domains;
>
> You have to change that view as customers (resp. schemas) come and go.

Oh, just thought of it ... I know there used to be limitation on this, but
I swore that they were removed ... how about a function?  could you do
something like:

for i in `SELECT nspname FROM nspname WHERE nspname LIKE 'test_*'`

... I know functins returning multi-rows was a limitation in the past, but
didn't that *just* get corrected, or am I mis-remembering?


Wed, 5 Nov 2003 17:20:06 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Schema boggle...
Chris Bowlby <excalibur@hub.org> writes:
>  As you can see this is a nice, clean way to break down some datasets.
> But, if I do:

>  set search_path to public, test_001, test_002;

>  I only get access to the tables in test_001 and public, the tables in
> test_002 are not listed, and thus I do not see them on the screen while
> doing a "\d".

Well, sure.  They are masked by the identically named tables in
test_001.  How else would you expect it to work?

> 2. Issue 2, is more a SQL issue then anything, but I'd like to be able
> to do something to the affect of:

>  SELECT * FROM test_*.test_domains ORDER BY domain_name;

I can't see trying to support this at the SQL level.  You might consider
making a plpgsql function that constructs a UNION ALL query listing all
the proper schemas and then EXECUTEs it on-the-fly.

Another possible solution is to make all the individual tables be
inheritance children of a master table.  Then selecting from the master
would work (and would be equivalent to the UNION performance-wise, I
think).

			regards, tom lane

Wed, 05 Nov 2003 16:34:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Schema boggle... 


On Wed, 5 Nov 2003, Tom Lane wrote:

> Chris Bowlby <excalibur@hub.org> writes:
> >  As you can see this is a nice, clean way to break down some datasets.
> > But, if I do:
>
> >  set search_path to public, test_001, test_002;
>
> >  I only get access to the tables in test_001 and public, the tables in
> > test_002 are not listed, and thus I do not see them on the screen while
> > doing a "\d".
>
> Well, sure.  They are masked by the identically named tables in
> test_001.  How else would you expect it to work?

                     List of relations
 Schema   |         Name          |   Type   |   Owner
----------+-----------------------+----------+-----------
 public   | categories            | table    | 186_pgsql
 public   | categories_rec_id_seq | sequence | 186_pgsql
 test_001 | table1                | table    | 186_pgsql
 test_002 | table1                | table    | 186_pgsql

the uniqueness, I would have thought, woudl have been schema.name, not
just name ...


Wed, 5 Nov 2003 18:02:58 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Schema boggle... 
"Marc G. Fournier" <scrappy@postgresql.org> writes:
>>  How else would you expect it to work?

>                      List of relations
>  Schema   |         Name          |   Type   |   Owner
> ----------+-----------------------+----------+-----------
>  public   | categories            | table    | 186_pgsql
>  public   | categories_rec_id_seq | sequence | 186_pgsql
>  test_001 | table1                | table    | 186_pgsql
>  test_002 | table1                | table    | 186_pgsql

You can get something like that if you say "\d *.*".  If you say "\d *"
or equivalently just "\d", then what you see is only the tables that you
could reference with unqualified names.  Which does not include the
tables in test_002, because they're hidden by the ones in test_001.

			regards, tom lane

Wed, 05 Nov 2003 17:08:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Schema boggle... 