Re: patch: SQL/MED(FDW) DDL

On Tue, 28 Sep 2010 22:14:22 +0300
Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 09/28/10 17:26, Robert Haas wrote:
> > First, it seems totally wrong to assume that the same functions and
> > operators will be defined on the remote side as you have locally;
> > indeed, for CSV files, you won't have anything defined on the remote
> > side at all. You need some kind of a discovery mechanism here to
> > figure out which quals are push-downable. And it should probably be
> > something generic, not a bunch of hard-wired rules that may or may not
> > be correct in any particular case. What if the remote side is a
> > competing database product that doesn't understand X = ANY(Y)?
> > Second, even if a functions or operators does exist on both sides of
> > the link, how do you know whether they have compatible semantics?
>
> Or side-effects.
>
> The SQL/MED specification has "routine mappings" for this purpose. We
> will need that or something similar.

Yes, I see the problem.

To resolve function-mismatch issues, we need routine mapping mechanism.
With that, FDW can translate function calls into remote expressions
along rules which are defined by user.

SQL/MED standard requires a routine mapping to map a pair of local
routine and server to a set of generic options. An local routine
is identified by type, name and parameters.

The type is one of ROUTINE/FUNCTION/PROCEDURE/METHOD. I think
supporting only FUNCTION is enough because PostgreSQL doesn't have
ROUTINE/PROCEDURE/METHOD.

I think that the pg_routine_mapping catalog should have
columns/indexes:

rmname name not null
rmprocid oid not null
rmserverid oid not null
rmoptions text[]

pg_routine_mapping_oid_index UNIQUE, btree(oid)
pg_routine_mapping_name_index UNIQUE, btree(rmname)
pg_routine_mapping_proc_server_index UNIQUE, btree(rmprocid, rmserverid)

To use a remote function which PostgreSQL doesn't have in foreign
query, we need a local dummy function which is mapped to remote
function. The local function may be empty but should have appropriate
parameters.

Supported features:
* Create routine mapping (USAGE on the server required)
* Alter routine mappging's generic options (must be owner of the
routine mappping)
* Drop routine mapping (must be owner of the routine mapping)
* Query routine mapping informations via information_schema views
routine_mappings and routine_mapping_options (no restriction)

FDWs can define any specification of generic options.
Maybe FDW for PostgreSQL needs no entry if no special functions was
defined. Routine mappings can be used to absorb the difference of the
versions.

Any comments are welcome.

Regards,

--
Shigeru Hanada