Re: Exposing quals

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, we might 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) remote relation,
* or a join involving a remote relation. Can return NULL for join
* relations if the join can't be executed remotely.
*/
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 typical implementation
* would create the SQL string to be executed in the remote database,
* and return a RemotePlan node 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