Re: patch: SQL/MED(FDW) DDL

On Wed, Sep 29, 2010 at 3:56 AM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
> In current patch, fdwoptions for relations have been separated from
> reloptins by introducing pg_foreign_table catalog.  As mentioned in
> wiki, integration into rel/attoptions is nothing but an idea, we're
> willing to add pg_foreign_attribute catalog which has columns:
>
>    farelid     oid         not null
>    faattnum    smallint    not null
>    faoptions   text[]      not null
>
> Though this catalog has only fdwoptions as non-key value.
> Or, adding attrfdwoptions column to pg_attribute catalog is better?

I could go either way.  One possibility to isolate all this
information in a separate catalog, similar to what we recently did
with security labels.  That approach scales to dealing with many
object types (relations, attributes, functions, etc.).  That might be
overkill here, though: perhaps we should just add a column to each of
pg_class and pg_attribute.  I could go either way on this point.

>> I think the section about WHERE clause push-down is way off base.
>> First, it seems totally wrong to assume that the same functions and
>> operators will be defined on the remote side as you have locally;
>> indeed, for CSV files, you won't have anything defined on the remote
>> side at all.  You need some kind of a discovery mechanism here to
>> figure out which quals are push-downable.  And it should probably be
>> something generic, not a bunch of hard-wired rules that may or may not
>> be correct in any particular case.  What if the remote side is a
>> competing database product that doesn't understand X = ANY(Y)?
>> Second, even if a functions or operators does exist on both sides of
>> the link, how do you know whether they have compatible semantics?
>> Short of solving the entscheidungsproblem, you're not going to be able
>> to determine that algorithmically, so you need some kind of mechanism
>> for controlling what assumptions get made.  Otherwise, you'll end up
>> with queries that don't work and no way for the user to fix it.
>
> First of all, WHERE clause push-down ideas written in wiki are just
> for FDW for PostgreSQL, implicitly same version, so we have assumed
> that the remote side has same syntax/semantics.  WHERE clause
> push-down is implemented in postgresql_fdw, and optimizer/planner are
> not .

Well, that's not the way it looks like from reading the section
entitled "FDW routines".  What I think we're looking for here is a
general API...

> Using remote indexes might be very effective, but I think there are
> many issues.
>
> For instance, how can planner/optimizer know whether the foreign table
> has been indexed or not?  Checking remote catalogs for each scan must
> be a bad idea.  HiRDB, Hitachi's dbms product, seems to have
> introduced FOREIGN INDEX for that purpose.

Yeah, it's definitely not an easy problem.

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