Re: Changing the concept of a DATABASE

On 22 May 2012 14:05, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, May 22, 2012 at 8:40 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> That seems to be leaving aside the fact that we don't currently have any
>> notion of how to allow FDWs to write the foreign tables.
>>
>> What is more, isn't the postgres FDW about talking to any postgres source?
>> If so, does it have special smarts for when we are talking to ourselves? And
>> if it doesn't then it seems unlikely to be an acceptable substitute for
>> allowing talking direct to a sibling database.
>>
>> I'm not at all yet sold on Simon's plan, but I'm skeptical that an FDW would
>> adequately meet the case if we wanted to go that way.
>
> Well, I don't think anyone is claiming that FDWs as they exist today
> solve all of the problems in this area.  But I think that improving
> FDWs is a more promising line of attack than trying to make backends
> talk to multiple databases.  Doing the latter will require massive
> surgery on the relcache, the catcache, most of the secondary catalog
> caches, the ProcArray, and every portion of the backend that thinks an
> OID uniquely identifies an SQL object.  Basically, they'd all need
> database OID as an additional key field, which is undesirable for
> performance reasons even if there were no issue of code churn.

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.

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

> 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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services