Re: Changing the concept of a DATABASE

> Why is it OK to allow somebody to access multiple schema in one query,
> but not multiple databases? Are you arguing that schemas are also
> broken?

Because the purpose of a database is to be a Catalog, i.e. an isolated
container, which is not the purpose of schemas. To the extent to which
we can make the isolation a reality (instead of "lossy isolation" the
way it is now), we can enable many multitenant hosting designs which
aren't currently possible. However, pursuing interdatabase queries at
the same time we try to add new isolation features is a doomed effort.

For example, if we created local database users (see other thread), then
what would happen if a local user tries to execute a cross-database
query? If we enable physical migration of a database to a new Postgres
instance, what happens to standing multi-database views? If
interdatabase queries are allowed, how do I, as a hosting operator, make
sure that users can't even see the other databases on the system?

> I see no failure by design. I see an idea for greater ease of use
> being discussed.

You can't attempt mutually contradictory requirements and expect to
succeed, or to improve ease of use. You can't ride two horses,
especially if they're going in opposite directions.

> Personally, I have long recommended that people use schemas. But
> people do use databases and when they do they are pretty much screwed.
> I brought this up as a way of improving our ease of use.

I'm not arguing that we don't have users who would like interdatabase
queries, especially when they port applications from MySQL or MSSQL. We
have a lot of such users. However, we *also* have a lot of users who
would like to treat separate databases as virtual private instances of
Postgres, and there's no way to satisfy both goals. We have to choose
one route or the other.

I personally see the isolation case as the more necessary because there
are several workarounds for the "inter-database queries" issue, but
nothing for the "multitenant catalog" case. Also, treating databases as
catalogs is more consistent with our historical approach, and will thus
break less backwards compatibility.

Maybe interdatabase queries are more useful/desired than catalog
features. If that's the case, then we need to pursue them and abandon
efforts like per-database users. But we have to make a choice.

An alternative idea -- and one which could be deployed a lot faster --
is to come up with a tool which makes it easy to migrate an entire
database into a separate schema or set of schemas in an existing
database. And improvements to manage schema visility/path better, I
suppose.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com