Re: IMPORT FOREIGN SCHEMA statement

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

> If you want something which is
> not the default, just set it on the individual columns of the foreign
> table, exactly how we handle column name differences.
> 
> There might be some value in being able to redefine what the default is
> at the FOREIGN SERVER level, as perhaps MySQL DB X and MySQL DB Y could
> have different default mappings for some reason, but adding in the rest
> of those levels doesn't add value imv.
> 
> >     This would consist of:
> >     - The remote type
> >     - The local type to which it maps
> >     - The inbound transformation (default identity)
> >     - The outbound transformation (default identity)
> 
> The remote type and the local type are known already to the FDW, no?

The above aren't separable items.  They all have to be there, even if
for user convenience we have two default transformations which are the
identity.

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

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

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

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

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

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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