Re: Exposing quals - Mailing list pgsql-hackers

Simon Riggs wrote:
> The notes say "Heikki doesn't think this is a long term solution", but
> in the following discussion it was the *only* way of doing this that
> will work with non-PostgreSQL databases. So it seems like the way we
> would want to go, yes?

How did you come to the conclusion that this is the only way that will 
work with non-PostgreSQL databases? I don't see any limitations like 
that in any of the proposed approaches.

I guess I should clarify my position on this:

We should start moving towards a full SQL:MED solution that will 
ultimately support pushing down joins, aggregates etc. to the remote 
database. Including support for transaction control, using 2PC, and cost 
estimation and intelligent planning.

This should be done in an extensible way, so that people can write their 
own plugins to connect to different RDBMSs, as well as simple data 
sources like flat files. The plugin needs to be able to control which 
parts of a plan tree can be pushed down to the remote source, estimate 
the cost of remote execution, and map remote data types to local ones. 
And it then needs to be able to construct and execute the remote parts 
of a plan.

We're obviously not going to get all that overnight, but whatever we 
implement now should be the first step towards that, rather than 
something that we need to deprecate and replace in the future. 
Unfortunately I don't see a way to extend the proposed "exposing quals 
to functions" patch to do more than just that.

The list of functionality a full-blown plugin will need is quite long. I 
don't think there's any hope of supporting all that without reaching 
into some PostgreSQL internal data structures, particularly the planner 
structures like RelOptInfo, Path and Plan. The plugins will be more 
tightly integrated into the system than say user defined data types. 
They will need to be written in C, and they will be somewhat version 
dependent. Simpler plugins, like one to read CSV files, with no "pushing 
down" and no update support, will need less access to internals, and 
thus will be less version dependent, so pgfoundry projects like that 
will be feasible.

Note that the dependency on internal data structures doesn't go away by 
saying that they're passed as text; the text representation of our data 
structures is version dependent as well.

So what would the plugin API look like? To hook into the planner, I'm 
envisioning the plugin would define these functions:
  /*   * Generate a remote plan for executing a whole subquery remotely. For   * example, if the query is an aggregate,
wemight be able to execute   * the whole aggregate in the remote database. This will be called   * from
grouping_planner(),like optimize_minmax_aggregates().   * Returns NULL if remote execution is not possible. (a dummy
*implementation can always return NULL.   */  Plan *generate_remote_path(PlannerInfo *, List *tlist);
 
  /*   * Generate a path for executing one relation in remote   * database. The relation can be a base (non-join)
remoterelation,   * or a join involving a remote relation. Can return NULL for join   * relations if the join can't be
executedremotely.   */  Path *generate_remote_path(PlannerInfo *, RelOptInfo *)
 
  /*   * Create a Plan node from a Path. Called from create_plan, when   * the planner chooses to use a remote path. A
typicalimplementation   * would create the SQL string to be executed in the remote database,   * and return a
RemotePlannode with that SQL string in it.   */  Plan *create_remote_plan(PlannerInfo *, RemotePath *)
 

On the execution side, the plugin needs to be able to execute a 
previously generated RemotePlan. There would be a new executor node 
type, a RemoteScan, that would be similar to a seq scan or index scan, 
but delegates the actual execution to the plugin. The execution part of 
the plugin API would reflect the API of executor nodes, something like:
  void *scan_open(RemotePlan *)  HeapTuple *scan_getnext(void *scanstate)  void scan_close(void *scanstate)


The presumption here is that you would define remote tables with the 
appropriate SQL:MED statements beforehand (CREATE FOREIGN TABLE). 
However, it is flexible enough that you could implement the "exposing 
quals to functions" functionality with this as well: 
generate_remote_path() would need to recognize the function scans that 
it can handle, and return a RemotePath struct with all the same 
information as create_functionscan_path does (the cost estimates could 
be adjusted for the pushed down quals at this point as well). 
create_remote_plan would return a FunctionScan node, but with the extra 
qualifiers passed into the function as arguments. In case of dblink, it 
could just add extra WHERE clauses to the query that's being passed as 
argument. I'm not proposing that we do the stuff described in this 
paragraph, just using it as an example of the flexibility.

BTW, I think the "exposing quals to functions" functionality could be 
implemented as a planner hook as well. The hook would call the standard 
planner, and modify the plan tree after that, passing the quals as extra 
arguments to functions that can take advantage of them.

A "foreign data wrapper" interface is also defined in the SQL/MED 
standard. I've only looked at it briefly, but it seems provide roughly 
the same functionality as the API I defined above. It would be a good 
idea to look at that, though I don't think that part of the standard is 
very widely adopted.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com