Re: CREATE FOREGIN TABLE LACUNA - Mailing list pgsql-hackers

On Wed, Mar 14, 2012 at 10:22 AM, David Fetter <david@fetter.org> wrote:
>> I think that instead of inventing new grammar productions and a new
>> node type for this, you should just reuse the existing productions for
>> LIKE clauses and then reject invalid options during parse analysis.
>
> OK.  Should I first merge CREATE FOREIGN TABLE with CREATE TABLE and
> submit that as a separate patch?

I don't see any reason to do that.  I merely meant that you could
reuse TableLikeClause or maybe even TableElement in the grammer for
CreateForeignTableStmt.

>> INCLUDING COMMENTS would be OK, but the the rest are no good.
>
> At least for now.  I can see FDWs in the future that would delegate
> the decision to the remote side, and if the remote side happens to be
> PostgreSQL, a lot of those delegations could be in force.  Of course,
> this would either create a dependency that would need to be tracked in
> the other node or not be able to guarantee the durability of DDL, the
> latter being the current situation.  I suspect there would be use
> cases for each.

What's relevant for LIKE is whether we want to create constraints,
defaults, comments, etc. on the *local* side, not the remote side -
and that has nothing do with with the particular choice of FDW in use.

I don't think we should conflate the local and remote sides.  Even if
a foreign table refers to a remote table that has comments on its
columns, there's no rule that the comments on the foreign side must
match up with the comments on the local side, and in fact I think that
in general we want to keep those concepts clearly distinct.  There's
no guarantee that the two systems are controlled by the same DBA, and
they might each have their own choice words about those columns.  IOW,
even if we had the ability to keep those things synchronized, we
shouldn't do it, or at least not by default.

>> Obviously, we can't enforce constraints on remote data, but the point
>> would be allow the system administrator to supply the query planner
>> with enough knowledge to make constraint exclusion work.  The fact
>> that you can't make that work today is a major gap, IMV.
>
> I didn't do INHERITS because most FDWs won't ever have that concept,
> i.e. aren't PostgreSQL.  Are you thinking about this as a general way
> to handle remote partitioned tables?

The original foreign table patch included constraints and the ability
to inherit back and forth between regular tables and foreign tables.
I ripped all that out before committing because it wasn't sufficiently
well thought-out, but I'm not convinced it's something we never want
to do.  Either way, constraint exclusion can also be used in other
scenarios, like a UNION ALL view over several foreign tables.

And yes, I am thinking about remote partitioned tables.  :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company