Re: per-column generic option

On Fri, Jun 17, 2011 at 07:19:39PM +0900, Shigeru Hanada wrote:
> (2011/06/17 8:44), David Fetter wrote:
> > Sorry not to respond sooner.
> >
> > First, the per-column generic options are a great thing for us to
> > have. :)
>
> Thanks for the comments. :-)
>
> > I have an idea I've been using for the next release of DBI-Link that
> > has varying levels of data type mapping. In general, these mappings
> > would be units of executable code, one in-bound, and one out-bound,
> > for each of:
> >
> > Universe (everything, default "mapping" is the identity map, i.e. a no-op)
> > Database type (e.g. MySQL)
> > Instance (e.g. mysql://foo.bar.com:5432)
> > Database
> > Schema
> > Table
> > Column
>
> Some of them seem to be able to be mapped to FDW object, e.g. Database
> to SERVER and Table to FOREIGN TABLE.

Yes, I see there are a few missing. "Universe" doesn't really need
much of anything, as far as I can tell, except if we wanted to do
something that affected SQL/MED globally. Is that hierarchy otherwise
OK? DB2 may have one more level between Instance and Database Type,
that latter being the province of an individual FDW.

> > I didn't include row in the hierarchy because I couldn't think of a
> > way to identify rows across DBMSs and stable over time.
> >
> > The finest-grain transformation that's been set would be the one
> > actually used.
> >
> > Here's an example of a non-trivial mapping.
> >
> > Database type:
> > MySQL
> > Foreign data type:
> > datetime
> > PostgreSQL data type:
> > timestamptz
> > Transformation direction:
> > Import
> > Transformation:
> > CASE
> > WHEN DATA = '0000-00-00 00:00:00'
> > THEN NULL
> > ELSE DATA
> > END
> >
> > Here, I'm making the simplifying assumption that there is a bijective
> > mapping between data types.
> >
> > Is there some way to fit the per-column part of such a mapping into
> > this scheme? We'd need to do some dependency tracking in order to be
> > able to point to the appropriate code...
>
> IIUC, you are talking about using FDW options as storage of data
> type mapping setting, or mapping definition itself, right? If so, a
> foreign table needs to be created to use per-column FDW options.
> Does it suit to your idea?

Yes. The only mildly disturbing thing about how that would work is
that "magic" key names would actually point to executable code, so
there would be some kind of non-uniform processing of the options, and
(possibly quite unlikely) ways to escalate privilege.

> BTW, I couldn't get what you mean by "dependency tracking". You
> mean the dependency between foreign column and local column? It
> might include essence of your idea... Would you explain the detail?

I think the dependency between the mapping between the foreign column
and the local one is already handled. On that subject, it's possible
to make an argument that this mapping might need to be expanded so
that in general, M foreign columns map to N local ones (distinct M and
N), but that's a research topic, so let's not worry about it now.

The dependency tracking I have in mind is of the actual executable
code. If the inbound mapping has what amounts to a pointer to a
function, it shouldn't be possible to drop that function without
CASCADE, and if we're caching such functions, the cache needs to be
refreshed any time the function changes.

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