Re: patch: SQL/MED(FDW) DDL - Mailing list pgsql-hackers

From Shigeru HANADA
Subject Re: patch: SQL/MED(FDW) DDL
Date
Msg-id 20100929214308.9FC7.6989961C@metrosystems.co.jp
Whole thread Raw
In response to Re: patch: SQL/MED(FDW) DDL  (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>)
List pgsql-hackers
On Tue, 28 Sep 2010 22:14:22 +0300
Heikki Linnakangas <heikki.linnakangas@enterprisedb.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_indexUNIQUE, 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
(mustbe owner of the   routine mappping) * Drop routine mapping (must be owner of the routine mapping) * Query routine
mappinginformations 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



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: I: About "Our CLUSTER implementation is pessimal" patch
Next
From: Alvaro Herrera
Date:
Subject: Re: I: About "Our CLUSTER implementation is pessimal" patch