Re: IMPORT FOREIGN SCHEMA statement

* David Fetter (david@fetter.org) wrote:
> On Tue, May 27, 2014 at 09:41:06AM -0400, Stephen Frost wrote:
> > * David Fetter (david@fetter.org) wrote:
> > > - We make type mappings settable at the level of:
> > >     - FDW
> > >     - Instance (a.k.a. cluster)
> > >     - Database
> > >     - Schema
> > >     - Table
> > >     - Column
> >
> > While I like the general idea, you seem to be making this appear much
> > more complex than it actually is.  For example, I see no value in a
> > table-level "uint4 -> int8" definition.
>
> You chose a particularly silly example, so I have to assume it's a
> straw man.

... I used your example from 20140525194118.GB32355@fetter.org and your
suggestion that we support that on a per-table basis.  If that
combination is silly then let's not support it.

> My point was that since we don't know what things are
> relevant to preserve and transform here in the design stage, we need
> to leave them settable by people who have needs we don't know about,
> i.e. the users of the feature.

This is not relevant as far as I can tell.  Users can already make
changes to the definitions, or create them however they want the first
time by using CREATE FOREIGN TABLE.  The point of IMPORT is that it
should be for bulk operations- if you have a lot of very specific
transformations, then use CREATE instead.  I don't see value in
rebuilding the flexibility of what a script of CREATEs gives you into
a single IMPORT command.

> > The FDW will need to know how to do whatever conversions we're talking
> > about also, right?
>
> No.  The writer of the FDW is not the same person as the user of the
> FDW, and the former is not omniscient.  My idea here is to allow FDW
> users to supply transformation code at these spots.

Then we're not talking about something which should be IMPORT's job, or
at least it goes far beyond it and that's what we're discussing here.
This sounds a bit like you're looking to rebuild what ETLs provide in a
streaming fashion- and I'm all for that as an interesting project that
isn't about adding IMPORT.  I still think you could use views for this,
or ETL, if you really want to implement it using core instead of the
FDW.

> > (If you want to do it in core PG instead of the FDW
> > then I'm thinking you should probably use a view over top of the
> > foreign table..).
>
> We can't know in advance what to preserve and what to jettison.  We
> can't even know which server is responsible for doing the
> transformation.

Either side could handle the transformation using views, or there could
be transformations on both sides.

> > What's nice is that this all falls under what an FDW
> > can do *already*, if it wants (and, actually, it could do it on the
> > table-level technically too, if the FDW supports that, but schema?
> > database?  these things don't make sense...).
>
> Not to you yet, but I've seen deployed applications with exactly these
> requirements.

I don't view IMPORT as being part of a deployed application.  It's a way
of simplifying the process of setting up FDWs, not an ETL mechanism.

> > For the IMPORT bit, it should just be doing whatever the defaults are
> > unless you've set some different defaults for a given foreign server
> > (also something which could be set using our existing system...).
> >
> > >     ALTER FOREIGN TABLE foo ADD (mapping '{
> > >         "datetime": "text",
> > >         "inbound": "IDENTITY",
> > >         outbound: "IDENTITY"
> > >     }')
> >
> > I'm very much against having two different command languages where one
> > is used "when convenient".
>
> I did not suggest that we use two different ways to specify this.  I
> did sketch out one path--adding a bunch of SQL grammar--which I made
> it explicitly clear we shouldn't tread.  Apparently, I wasn't quite
> explicit enough.

What you missed here is that I'd find it objectionable to have some
portion of the system has a JSON-based grammar while the rest is an SQL
grammar.  I'm not entirely thrilled with the jsquery approach for that
reason, but at least that's a very contained case which is about a logic
expression (and we have logic expressions already in SQL).  That's not
what you're describing here.

> > If we wanted to do this, we should build a full-spec mapping from
> > whatever JSON language you come up with for our utility commands to
> > what we actually implement in the grammar.
>
> Excellent plan.

Go for it.  From here, I don't see anything stopping you from making a
wrapper around PG which converts your JSON syntax into SQL (indeed,
people have already done this..).  I wouldn't get your hopes up about
having it ever part of core PG though, and it certainly won't be until
it's as complete and capable as the existing SQL grammar.
Thanks,
    Stephen