Обсуждение: What are the advantages of not being able to access multiple databases with one connection?
MySQL permits a connection to access multiple databases. But Postgresql restricts a connection to one database. I think postgresql database connection is somewhat limited. Is it an old and decrepit design? or does it deserve some appreciations?
On 30 October 2012 12:37, crocket <crockabiscuit@gmail.com> wrote: > MySQL permits a connection to access multiple databases. > But Postgresql restricts a connection to one database. > I think postgresql database connection is somewhat limited. > > Is it an old and decrepit design? or does it deserve some appreciations? Actually, a "database" in postgresql supports multiple "schemas", and the "databases" in mysql are more or less congruent to those. To put it another way, pg has a three-level namespace which can be accessed through the same host and port combination; mysql only has two levels. Is that better?
On 10/30/2012 01:37 PM, crocket wrote: > MySQL permits a connection to access multiple databases. > But Postgresql restricts a connection to one database. > I think postgresql database connection is somewhat limited. > > Is it an old and decrepit design? or does it deserve some appreciations? It's an old and decrepit design ;) But nobody has found the ability to use many databases from the same connection valuable enough to do the rewrite needed. If you have ideas on how to enable access to multiple databases from the same connection - both at technical and logical level (i.e. what it would _mean_ and how it would be expressed in SQL) then sure people would be ready to gear you out. It would be valuable at least for some tools doing monitoring and maintenance even in case SQL access to multiple databases simultaneously would not be available. For example it would be nice to get pg_stat_user_tables() info for all databases in a single query or at least to switch databases (say using SET DATABASE <dbname>) without reconnecting. Hannu
On Tue, Oct 30, 2012 at 8:37 AM, crocket <crockabiscuit@gmail.com> wrote: > MySQL permits a connection to access multiple databases. > But Postgresql restricts a connection to one database. > I think postgresql database connection is somewhat limited. > > Is it an old and decrepit design? or does it deserve some appreciations? I think it deserves some appreciation. Each database is completely isolated in terms of privileges, which is sometimes useful. Also, if you somehow manage to fry the system catalogs in one database, the other ones can still survive. The role played by databases in MySQL is served by schemas in PostgreSQL, so I don't see that there is a functional gap here. I am not sure I'd bother implementing the multi-database concept today if we didn't have it already ... but it seems kind of pointless to rip it out given that it's already there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> functional gap here. I am not sure I'd bother implementing the > multi-database concept today if we didn't have it already ... but it > seems kind of pointless to rip it out given that it's already there. It's very useful for webhosts. You can give each user their own private database and not worry about them hacking into other peoples'. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: What are the advantages of not being able to access multiple databases with one connection?
От
Merlin Moncure
Дата:
On Mon, Nov 5, 2012 at 11:33 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Oct 30, 2012 at 8:37 AM, crocket <crockabiscuit@gmail.com> wrote: >> MySQL permits a connection to access multiple databases. >> But Postgresql restricts a connection to one database. >> I think postgresql database connection is somewhat limited. >> >> Is it an old and decrepit design? or does it deserve some appreciations? > > I think it deserves some appreciation. Each database is completely > isolated in terms of privileges, which is sometimes useful. Also, if > you somehow manage to fry the system catalogs in one database, the > other ones can still survive. The role played by databases in MySQL > is served by schemas in PostgreSQL, so I don't see that there is a > functional gap here. I am not sure I'd bother implementing the > multi-database concept today if we didn't have it already ... but it > seems kind of pointless to rip it out given that it's already there. A little trivia: postgres supports full database qualified identifier names: postgres=# select postgres.public.foo.i from postgres.public.foo; Even though you can't specify any other database than the one you're in. merlin