Thread: 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

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