Re: SQL/MED - core functionality

On 12/21/2010 02:33 PM, Simon Riggs wrote:
> On Wed, 2010-12-15 at 22:25 +0900, Shigeru HANADA wrote:
>
>> Attached are revised version of SQL/MED core functionality patches.
> Looks very interesting new feature, well done.
>
> Can I ask some questions about how this will work?
> No particular order, just numbered for reference.

Answering a few of your questions as I understand the position, faute de
mieux.
> 1. The docs don't actually say what a foreign table is. Is it a local
> representation of foreign data? Or a local copy of foreign data? Or is
> it a table created on a remote node?

It's an interface to data not managed by Postgres (or at least by this
node). It might be a table on another Postgres node, it might be a file,
it might be a table in another RDBMS, it might be a stream of some sort.
I could imagine creating one over a SOAP call, or for an RSS feed.
Someone has created one for a twitter feed, I believe. An LDAP FDW might
also be useful (think: single sign on).

> 2. Will CREATE FOREIGN TABLE require a transactionid? It seems a good
> replacement for temp tables on Hot Standby to be able to run a CREATE
> FOREIGN TABLE using the file_fdw, then reuse the file again later.

How could it not require a txnid? It's going to write the definition to
the catalog, isn't it?

> 3. Do we support CREATE TEMP FOREIGN TABLE? It seems desirable to be
> able to move data around temporarily, as we do with normal tables.

That would definitely be a good thing to have.

>
> 7. Why does ANALYZE skip foreign tables? Surely its really important we
> know things about a foreign table, otherwise we are going to optimize
> things very badly.

Quite apart from other reasons, such as possible ephemerality of the
data, the difficulty of taking a reasonable random sample from an
arbitrary foreign data source seems substantial, and we surely don't
want ANALYSE to have to run a full sequential scan of a foreign data source.

>
> 10. Can a foreign table be referenced by a FK?

I don't see how it can be. There would be no unique index to use.

cheers

andrew