Re: Status of FDW pushdowns - Mailing list pgsql-hackers

On Wed, Nov 27, 2013 at 4:20 AM, Shigeru Hanada
<shigeru.hanada@gmail.com> wrote:
> Hi Merlin,
>
> 2013/11/22 Merlin Moncure <mmoncure@gmail.com>:
>> On Thu, Nov 21, 2013 at 6:43 PM, Shigeru Hanada
>> <shigeru.hanada@gmail.com> wrote:
>>> 2013/11/22 Tom Lane <tgl@sss.pgh.pa.us>:
>>>> Merlin Moncure <mmoncure@gmail.com> writes:
>>>>> On Thu, Nov 21, 2013 at 9:05 AM, Bruce Momjian <bruce@momjian.us> wrote:
>>>>>> I know join pushdowns seem insignificant, but it helps to restrict what
>>>>>> data must be passed back because you would only pass back joined rows.
>>>>
>>>>> By 'insignificant' you mean 'necessary to do any non-trivial real
>>>>> work'.   Personally, I'd prefer it if FDW was extended to allow
>>>>> arbitrary parameterized queries like every other database connectivity
>>>>> API ever made ever.
>>>>
>>>> [ shrug... ]  So use dblink.  For better or worse, the FDW stuff is
>>>> following the SQL standard's SQL/MED design, which does not do it
>>>> like that.
>>>
>>> Pass-through mode mentioned in SQL/MED standard might be what he wants.
>>
>> happen to have a link handy?
>
> SQL/MED standard doesn't say much about PASS THROUGH mode, especially
> about interaction between client.  Besides it, I think it would be
> nice to allow arbitrary FDW as backend of dblink interface like this:
>
> postgres=> SELECT dblink_connect('con1', 'server name of an FDW');
> postgres=> SELECT * FROM dblink('con1', 'some query written in remote
> syntax') as t(/* record type definition */...);
>
> This provides a way to execute query without defining foreign table.

yeah.  (thanks for indulging -- this is barely on topic I guess).

if it were possible to create a supporting function (say, fdw_link)
that could somehow interface with a previously established server, it
could probably be worked out.   Then all FDW could leverage
parameterization without having to copy and paste the pgsql-fdw qual
push code.  But that would be a fairly large break from the rest of
the FDW syntax and having to define the record at each call site is
admittedly a bit of a headache.

Hm, another way to think about this would be to somehow abstract the
qual push into a library so that it could be accessed by other FDWs if
they opted in.  This would address my chief complaint that only the
pgsql-fdw (the only database for which we already have an in-core high
quality connection api) driver could tap the excellent work you've
done.  If this were even possible, it would probably result in more
fdw API changes.

If my:

SELECT * FROM big_sql_server_foreign_table WHERE id = x;

was fast, that'd be pretty nice.

merlin