Re: SQL/MED - core functionality

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.

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?

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.

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.

4. In Hot Standby, we are creating many copies of the data tables on
different servers. That seems to break the concept that data is in only
one place, when we assume that a foreign table is on only one foreign
server. How will we represent the concept that data is potentially
available identically from more than one place? Any other comments about
how this will work with Hot Standby?

5. In PL/Proxy, we have the concept that a table is sharded across
multiple nodes. Is that possible here? Again, we seem to have the
concept that a table is only ever in a single place.

6. Can we do CREATE FOREIGN TABLE .... AS SELECT ...
I guess the answer depends on (1)

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.

8. Is the WHERE clause passed down into a ForeignScan?

9. The docs for CHECK constraints imply that the CHECK is executed
against any rows returned from FDW. Are we really going to execute that
as an additional filter on each row retrieved?

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

11. Can you create a DO INSTEAD trigger on a FOREIGN TABLE?

12. I think it would be useful for both review and afterwards to write
the documentation section now, so we can begin to understand this. Will
there be a documentation section on writing a FDW also? There are enough
open questions here that I think we need docs and a review guide,
otherwise we'll end up with some weird missing feature, which would be a
great shame.

13. How does this relate to dblink? Is that going to be replaced by this
feature?

14. How do we do scrollable cursors with foreign tables? Do we
materialize them always? Or...

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?

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?

17. Can we request different types of transaction isolation on the
foreign server, or do certain states get passed through from our
session? e.g. if we are running a serializable transaction, does that
state get passed through to the FDW, so it knows to request that on the
foreign server? That seems essential if we are going to make pg_dump
work correctly.

18. Does pg_dump dump the data in the FDW or just of the definition of
the data? Can we have an option for either?

19. If we PREPARE a statement, are there API calls to pass thru the
PREPARE to the FDW? Or are calls always dynamic?

20. If default privileges include INSERT, UPDATE or DELETE, does this
cause error, or does it silently get ignored for foreign tables? I think
I would want the latter.

21. Can we LOCK a foreign table? I guess so. Presumably no LOCK is
passed through to the FDW?

22. Can we build an local index on a foreign table?

No too sure what the right answers are to these questions, but I think
we need to know the answers to understand what we are getting.

Thanks

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