Re: SQL/MED - core functionality

On Fri, 24 Dec 2010 11:34:59 +0000
Simon Riggs <simon@2ndQuadrant.com> wrote:
> On Fri, 2010-12-24 at 19:51 +0900, Shigeru HANADA wrote:
> > > 15. In terms of planning queries, do we have a concept of additional
> > > cost per row on a foreign server? How does the planner decide how
> > costly
> > > retrieving data is from the FDW?
> > 
> > Costs for a scan on a foreign table is estimated in FDW routine
> > PlanRelScan().  So FDW can use arbitrary algorithm to estimate costs. 
> > pgsql_fdw might execute "EXPLAIN SELECT ... FROM xxx" on remote side
> > to get remote costs.
> 
> OK, so there is an API call to allow the FDW to determine the size of
> the table, before we attempt to materialize it. That is good, and will
> allow us to make some reasonable optimisations.
> 
> Am I right in thinking that if the materialized result is larger than
> some_limit_parameter, that a ForeignScan will end with an ERROR? I think
> we're much more at risk from this with SQL/MED than we are with direct
> access. Keeping data remote usually means it is very large.
> work_space?

Materialize node uses Tuplestorestate to keep the result, so huge
result would use temporary files.  If FDW need to store result locally,
it can use Tuplestorestate.

> > > 16. If we cancel a query, is there an API call to send query cancel
> > to > the FDW and so on to the foreign server? Does that still work if
> > we hot > other kinds of ERROR, or FATAL?
> > 
> > There is no handler for query cancel.  If FDW wants cleanup on the
> > interrupts, resourceowner mechanism would help.
> 
> Please give this some thought. We need to be able to make a clean cancel
> for a remote query.

Sure.

> If my comments seem in any way negative, it is because I have had
> previous experience with poorly designed SQL gateway products and have
> no wish to repeat those experiences in PostgreSQL. I understand it will
> take many years for whole feature set to arrive, though the ones
> mentioned above I regard as essential for the first release.
> 
> Specifically, I've seen people do "SELECT * FROM BigForeignTable" and
> then be unable to cancel it until it/everyone explodes. That is
> especially annoying, since some SQL tools issue SELECTs as a means of
> doing DESCRIBE.

First of all, I think that it depends on the implementation of FDW and
capability of remote server whether user can cancel remote query.

For example, current pgsql_fdw uses PQexec("SELECT * FROM table") to
execute remote query, and set cleanup callback with
RegisterResourceReleaseCallback() after establishment of connection. 
In cleanup function, pgsql_fdw issues PQfinish() to cancel the whole
query.  With this implementation, pgsql_fdw can stop both of local and
remote query with user interrupt and other errors.

I'll research whether the registration of cleanup handler can be moved into
core.  If we don't provide FdwRoutine handler for query cancel and
other errors, it would be better to document usage of resourceower
mechanism in "How to write FDW" section or somewhere.

Regards,
--
Shigeru Hanada