Обсуждение: 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@investsystems.co.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@postgresql.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@sai.msu.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);