Re: patch: SQL/MED(FDW) DDL

On Mon, Sep 27, 2010 at 2:50 AM, SAKAMOTO Masahiko
<sakamoto(dot)masahiko(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>  http://wiki.postgresql.org/wiki/SQL/MED

With regard to what is written here, it strikes me that it would be an
extremely bad idea to try to mix reloptions or attoptions with
fdwoptions. fdwoptions are options to be passed transparently to the
fdw to handle as it likes; rel/attoptions affect the behavior of PG.

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.

It seems to me that the API should allow PG to ask the FDW questions like this:

- How many tuples are there on the remote side?
- Here is a qual. Are you able to evaluate this qual remotely?
- What are the startup and total costs of a sequential scan of the
remote side with the following set of remotely executable quals?
- Are there any indices available on the remote side, and if so what
are there names and which columns do they index in which order
(asc/desc, nulls first/last)?
- What are the startup and total costs of an index scan of the remote
side using the index called $NAME given the following set of remotely
executable quals?

and, as you mentIon:

- Please update pg_statistic for this foreign table, if you have that
capability.

Then:

- Begin a sequential scan with the following set of quals.
- Begin an index scan using the index called X with the following set of quals.
- Fetch next tuple.
- End scan.

Maybe that's too much for a first version but if we're not going to
deal with the problems in a general way, then we ought to not deal
with them at all, rather than having hacky rules that will work if
your environment is set up in exactly the way the code expects and
otherwise break horribly.

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