Re: patch: SQL/MED(FDW) DDL

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 2010/9/15 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
>> I've been thinking about that for some time. Here's one sketch I made a
>> while ago:
>> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00395.php.

> I wonder if we might not think of the foreign data wrapper problem as
> an instance of an even more general problem. Right now, we have
> semi-pluggable index access methods - it's probably not quite possible
> to implement one as a standalone module because of XLOG, and maybe
> other reasons, but at least there's some abstraction layer there.
> Perhaps we should be thinking about a similar facility of table-access
> methods. What if someone wants to implement column-oriented storage,
> or index-organized tables, or or tables that are really slow under
> heavy write loads but can instantaneously compute SELECT COUNT(*) FROM
> table, or mauve-colored tables with magic pixie dust? I don't want to
> raise the bar for this project to the point where we can never get it
> off the ground, but if there's a way to avoid baking in the assumption
> that only foreign tables can ever have special capabilities, that
> might be valuable.

Well, the problem is to not draw the abstraction boundary so high that
your plugins have to reimplement the world to get anything done.
mysql got this wrong IMO, and are still paying the price in the form of
bizarre functional incompatibilities between their different storage
engines.

As an example, I don't think there is any sane way to provide
column-oriented storage as a plugin. The entire executor is based
around the assumption that table scans return a row at a time; in
consequence, the entire planner is too. You can't have a plugin that
replaces all of that. You could probably build a plugin that allows
columnar storage but reconstructs rows to return to the executor ... but
having to do that would largely destroy any advantages of a columnar DB,
I fear.

Yet there are other cases that probably *could* work well based on a
storage-level abstraction boundary; index-organized tables for instance.
So I think we need to have some realistic idea of what we want to
support and design an API accordingly, not hope that if we don't
know what we want we will somehow manage to pick an API that makes
all things possible.

I'm personally more worried about whether Heikki's sketch has the
boundary too high-level than too low-level. It might work all right
for handing off to a full-fledged remote database, particularly if
the other DB is also Postgres; but otherwise it's leaving a lot of
work to be done by the plugin author. And at the same time I don't
think it's exposing enough information to let the local planner do
anything intelligent in terms of trading off remote vs. local work.

regards, tom lane