Re: namespaces and schemas

Dear PG-users,

while praparing a database teaching class with PG 7.1, I have some trouble
setting up a working multi user environment.

My problem is that when some user does a "CREATE TABLE", this table is
created in the *global* namespace, not in the private user schema as I
would have expected.

This means that only the first user can create a table with a given name;
all other users are blocked from using this name in the future.

Question:

Is it possible to assign different users different namespaces?

My current impressions:

- SCHEMA seems to be on the TODO list for PG 7.3.
Thus this problem might be solved next (?) year.

- With PG 7.1 or 7.2 the only way I see is to use a separate database
for each user. As we have about 50 groups in the course, this might
cause some trouble elsewhere.
Is there another workaround?

Christoph Dalitz

On Tue, 2 Jul 2002, Christoph Dalitz wrote:

> Dear PG-users,
>
> while praparing a database teaching class with PG 7.1, I have some trouble
> setting up a working multi user environment.
>
> My problem is that when some user does a "CREATE TABLE", this table is
> created in the *global* namespace, not in the private user schema as I
> would have expected.
>
> This means that only the first user can create a table with a given name;
> all other users are blocked from using this name in the future.
>
> Question:
>
> Is it possible to assign different users different namespaces?
>
> My current impressions:
>
> - SCHEMA seems to be on the TODO list for PG 7.3.
> Thus this problem might be solved next (?) year.

The development version (7.3dev) already has schema support. It's not
necessarily to be recommended but unless you want near certainty there aren't
problems lurking in 7.3 that weren't in 7.2.1 I would suggest you obtain the
7.3dev and try it out.

> > - With PG 7.1 or 7.2 the only way I see is to use a
separate database
> for each user. As we have about 50 groups in the course, this might
> cause some trouble elsewhere.
> Is there another workaround?

I'm not sure what trouble there could be. I've not heard of people using large
numbers of databases but I don't see that it would be a problem. Unless someone
else has some information to impart on this I'd suggest you try it out, not
necessarily infront of the classes.

I would also suggest that before you start you obtain 7.2.1, if not the 7.3dev
for schema support, and use that.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> The development version (7.3dev) already has schema support. It's not
> necessarily to be recommended but unless you want near certainty there aren't
> problems lurking in 7.3 that weren't in 7.2.1 I would suggest you obtain the
> 7.3dev and try it out.

7.3 is *not* in shape for production use yet. I too would recommend
using 7.2.1 over 7.1, but that won't help his problem.

>> - With PG 7.1 or 7.2 the only way I see is to use a
>> separate database
>> for each user. As we have about 50 groups in the course, this might
>> cause some trouble elsewhere.
>> Is there another workaround?

> I'm not sure what trouble there could be.

Should work fine; I've seen people using hundreds of separate databases.
It's a tad wasteful of disk space (since each DB needs its own copy of
the basic system catalogs, at a meg or two each) but it works. If you
want airtight separation between users, a DB per user will probably be
the preferred setup even in 7.3.

regards, tom lane

On Tue, 2 Jul 2002, Christoph Dalitz wrote:

> Dear PG-users,
>
> while praparing a database teaching class with PG 7.1, I have some trouble
> setting up a working multi user environment.
>
> My problem is that when some user does a "CREATE TABLE", this table is
> created in the *global* namespace, not in the private user schema as I
> would have expected.
>
> This means that only the first user can create a table with a given name;
> all other users are blocked from using this name in the future.
>
> Question:
>
> Is it possible to assign different users different namespaces?
>
> My current impressions:
>
> - SCHEMA seems to be on the TODO list for PG 7.3.
> Thus this problem might be solved next (?) year.
>
> - With PG 7.1 or 7.2 the only way I see is to use a separate database
> for each user. As we have about 50 groups in the course, this might
> cause some trouble elsewhere.
> Is there another workaround?

I'd upgrade to 7.2.1 and give everybody their own database. I've never
seen a problem with lots of seperate databases in postgresql, and we run
dozens on our production box (each program gets its own database).

Each empty database uses only about 1.7 megs of disc space, by the way, so
unless you're on a machine with a really small drive, having bunches of
databases shouldn't be any real problem.

Scott Marlowe

-- "Force has no place where there is need of skill.", "Haste in every
business brings failures.", "This is the bitterest pain among men, to have
much knowledge but no power." -- Herodotus

On Tue, 2 Jul 2002, Scott Marlowe wrote:

> On Tue, 2 Jul 2002, Christoph Dalitz wrote:
>
> > Dear PG-users,
> >
> > while praparing a database teaching class with PG 7.1, I have some trouble
> > setting up a working multi user environment.
> >
> > My problem is that when some user does a "CREATE TABLE", this table is
> > created in the *global* namespace, not in the private user schema as I
> > would have expected.
> >
> > This means that only the first user can create a table with a given name;
> > all other users are blocked from using this name in the future.
> >
> > Question:
> >
> > Is it possible to assign different users different namespaces?
> >
> > My current impressions:
> >
> > - SCHEMA seems to be on the TODO list for PG 7.3.
> > Thus this problem might be solved next (?) year.
> >
> > - With PG 7.1 or 7.2 the only way I see is to use a separate database
> > for each user. As we have about 50 groups in the course, this might
> > cause some trouble elsewhere.
> > Is there another workaround?
>
> I'd upgrade to 7.2.1 and give everybody their own database. I've never
> seen a problem with lots of seperate databases in postgresql, and we run
> dozens on our production box (each program gets its own database).
>
> Each empty database uses only about 1.7 megs of disc space, by the way, so
> unless you're on a machine with a really small drive, having bunches of
> databases shouldn't be any real problem.

Scott, there is a big problem with your approach in web environment
if you want to have persistent connection to each database for each
httpd backend. With SCHEMA you'll need to keep only one connection
(for each backend). It'd be a very big win and solve many pgsql-based
hosting problem, I hope.

>
> Scott Marlowe
>
> -- "Force has no place where there is need of skill.", "Haste in every
> business brings failures.", "This is the bitterest pain among men, to have
> much knowledge but no power." -- Herodotus
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

This is not Oracle. Createing another database is not like creating
another instance. The disk usage is minimal, and there is nearly no extra
memory usage (none when no connections are made to it at the moment).
Creating another database is minor. The only limitation is referencing
tables in other databases, that is other than the one you are currently
connected to.

______________________________________________________________________________

Your mouse has moved.
You must restart Windows for your changes to take effect.

#!/usr/bin/perl
print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);