Re: SQL/MED - core functionality

On 25.11.2010 09:34, Shigeru HANADA wrote:
> Attached is a patch that adds core functionality of SQL/MED.  This
> patch provides:
>
> * new option HANDLER for FOREIGN DATA WRAPPER
>    * CREATE/ALTER DDLs are supported
>    * psql \dew command shows handler option too
>    * pg_dump can dump HANDLER option
>
> * new object type FOREIGN TABLE
>    * CREATE/ALTER/DROP DDLs are supported
>    * system columns except TABLEOID are not supported
>    * inheriting normal table is supported
>    * psql \d shows detail of foreign tables
>    * psql \det lists foreign tables
>    * psql \dE lists foreign tables in \d format
>    * pg_dump can dump the definition
>    * information_schema views added
>    * foreign table is read-only, so INSERT/UPDATE/DELETE are denied
>    * ANALYZE and VACUUM skips foreign tables
>
> * new executor node ForeignScan
>    * it's a counterpart of SeqScan
>    * this node scans one foreign table at a time
>    * FDW HANDLER is necessary to execute SELECT statement
>
> Patches for FDWs which can be used to execute SELECT statement will be
> posted in their own thread soon.
>
>      "SQL/MED - file_fdw"       : FDW for external PostgreSQL
>      "SEL/MED - postgresql_fdw" : FDW for server-side file (CSV, TEXT)
>
> I would reuse existing CommitFest item "SQL/MED" for this patch, and
> add new item for each FDW patch.

Looking at the API again, there's a few things I don't like about it:

* It's tied to the ForeignScanState, so all the executor state
structures are exposed to the FDW implementation. It feels like a
modularity violation that the FDW Iterate function returns the tuple by
storing it directly in scanstate->ss.ss_ScanTupleSlot for example. And
it's not going to work for remote scans that don't go through the
executor, for example if you wanted to rewrite contrib/dblink to use
foreign data wrappers. Or the SQL/MED passthrough mode.

* There's no clear Plan stage in the API. Except for EstimateCosts,
which just fills in the estimated costs in RelOptInfo, so it needs to
understand quite a lot of the planner data structures to come up with a
reasonable estimate. But if it e.g wants to apply a qual remotely, like
the PostgreSQL FDW does, it has to check for such quals at execution
time. And as I complained before, you don't get any meaningful EXPLAIN
output.

I propose the attached API instead. This has a clear separation between
plan and execution. I'm sure we'll have to extend the API in the future
FDWs want tighter integration, but I think this is a good start. It
makes it quite straightforward to write simple FDW like the file FDW,
without having to know anything about the executor or planner internals,
but provides enough flexibility to cover the functionality in your
PostgreSQL FDW.

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