Re: CREATE FOREGIN TABLE LACUNA

On Wed, Mar 14, 2012 at 08:53:17AM -0400, Robert Haas wrote:
> On Wed, Mar 14, 2012 at 8:28 AM, David Fetter <david(at)fetter(dot)org> wrote:
> > On Tue, Mar 13, 2012 at 08:24:47AM -0700, David Fetter wrote:
> >> Folks,
> >>
> >> This is for 9.3, of course.
> >>
> >> I noticed that CREATE FOREIGN TABLE (LIKE some_table) doesn't work.  I
> >> believe it should, as it would:
> >>
> >> - Remove a POLA violation
> >> - Make data loading into an extant table even easier, especially if
> >>   there need to be filtering or other cleanup steps
> >>
> >> Come to think of it, which CREATE TABLE options are inappropriate to
> >> CREATE FOREIGN TABLE?
> >
> > Here's a WIP patch (lots of cut/paste, no docs, no tests), but it does
> > work.  Still to do in addition: decide whether ALTER FOREIGN TABLE
> > should also handle LIKE.
>
> 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?

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

> I'd actually like to see us allow foreign tables to have constraints.

So would I :)

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

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate