Re: pass-through queries to foreign servers

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 whole point of FDW is that it's at a higher level of
abstraction than that; which offers greater ease of use and will
eventually offer better optimization than what you can get from dblink
et al. If we start trying to shoehorn things like passthrough queries
into FDW, we'll be crippling the technology. As an example, somebody
on planet postgresql was just recently surprised to find that postgres_fdw
honors transaction rollback. Well, it can do that because users can't
disconnect the connection underneath it, nor issue passthrough
commit/rollback commands. You don't get to have it both ways.

regards, tom lane