Re: Changing the concept of a DATABASE

On Tue, May 22, 2012 at 1:27 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Ack, part from the bit about OIDs no longer being unique. That might
> be an upgrade issue but its obviously something we wouldn't allow if
> we did that.

And how exactly are you going to disallow that?  We currently enforce
the uniqueness of OIDs within a database by putting a unique index on
the relevant system catalog columns, but that clearly won't work as a
means of guaranteeing cross-database uniqueness.  Unless of course you
put all the catalog entries from all the databases into a single set
of catalogs; but then they're pretty much the same as the schemas we
already have.

> I'm not sure I see changing the caches as being massive surgery.
> Perhaps we could just bypass them altogether.

You're entitled to your opinion on this one, but we have those caches
for a very good reason: the system runs about 20,000 times slower
without them.

>> So I'd rather see us put the effort into pgsql_fdw, which, as Florian
>> says, will also let us talk to a completely separate server.  If
>> you've got multiple databases in the same cluster and really need to
>> be doing queries across all of them, that's what schemas are
>> supposedly for.  Now I know that people feel that doesn't work as well
>> as it needs to, but there again I think it would be easier to fix
>> schemas than to make cross-database queries work.
>
> We're a very long way from making that work well. IMHO easily much
> further than direct access.
>
> If I have a customer with 1 database per user, how do they run a query
> against 100 user tables? It would require 100 connections to the
> database. Doing that would require roughly x100 the planning time and
> x100 the connection delay. Larger SQL statements pass their results
> between executor steps using libpq rather than direct calls.
>
> I find it hard to believe FDWs will ever work sufficiently well to fix
> those problems.

If you have got a customer with one database per user, and yet you
want to run queries across all those tables, then you're using the
database system for something for which it is not designed, and it's
probably not going to work very well.  That sounds like a use case for
schemas, or maybe better, some kind of row-level security we don't
have yet - but hopefully will in 9.3, since KaiGai intends to work on
it.  Databases are designed to act as a firewall, so that somebody
using one database isn't affected by what happen in some other
database.  Unfortunately, because of shared catalogs, that's not
completely true, but that's the idea, and if anything we need to
isolate them more, not less.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company