Re: pass-through queries to foreign servers

On Wed, Jul 31, 2013 at 01:22:56AM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Tue, Jul 30, 2013 at 04:40:38PM -0700, David Gudeman wrote:
> >> When you write an application involving foreign tables, you frequently
> >> end up with queries that are just too inefficient because they bring
> >> too much data over from the foreign server. For a trivial example,
> >> consider "SELECT count(*) FROM t" where t is a foreign table. This
> >> will pull the entire table over the network just to count up the rows.
>
> > Yes, and this case is a known limitation of our planner
> > infrastructure. Aggregates are "special" when it comes to
> > generating paths for the planner to evaluate, so there's no current
> > way a FDW could supply such info to the planner, and hence no API in
> > our FDW code for having FDWs supply that info. That's probably a
> > "should fix" but I don't know whether a project that size could be
> > done by 9.4.
>
> Yeah. There's a lot left to be done in the FDW infrastructure.
> But not this:
>
> > All that said, my DBI-Link, back in the bad old days, provided two
> > important functions: remote_select(), which returned SETOF RECORD and
> > remote_execute(), which returned nothing. It also provided ways to
> > control connections to the remote host, introspect remote schemas,
> > etc., etc. We need capabilities like that in the FDW API, I believe
> > we could have them by 9.4.
>
> I would argue we *don't* want that. If you want pass-through queries
> or explicit connection control, your needs are already met by dblink or
> dbi-link.

The standard actually describes a passthrough mode. That mode is
mind-numbingly weird, but it's there. It's weird because As far as I
can tell, when it's set to true, all commands, *whether the local
server can parse them or not*, are sent to the foreign server until
it's set to false again.

We can easily do better.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate