Re: SQL/MED - core functionality

Thank you for those replies, I understand things much better now.

I have two remaining concerns...

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?

> > 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.

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.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services