Re: pgsql_fdw, FDW for PostgreSQL server

On 13.12.2011 11:57, Albe Laurenz wrote:
> Tom Lane wrote:
>> Shigeru Hanada<shigeru(dot)hanada(at)gmail(dot)com> writes:
>>> (2011/12/12 22:59), Robert Haas wrote:
>>>> ... I feel like we might need a system here that
>>>> allows for more explicit user control about what to push down vs.
> not,
>>>> rather than assuming we'll be able to figure it out behind the
> scenes.
>
>>> Agreed. How about to add a per-column boolean FDW option, say
>>> "pushdown", to pgsql_fdw? Users can tell pgsql_fdw that the column
> can
>>> be pushed down safely by setting this option to true.
>
>> [ itch... ] That doesn't seem like the right level of granularity.
>> ISTM the problem is with whether specific operators have the same
>> meaning at the far end as they do locally. If you try to attach the
>> flag to columns, you have to promise that *every* operator on that
>> column means what it does locally, which is likely to not be the
>> case ever if you look hard enough. Plus, having to set the flag on
>> each individual column of the same datatype seems pretty tedious.
>>
>> I don't have a better idea to offer at the moment though. Trying
>> to attach such a property to operators seems impossibly messy too.
>> If it weren't for the collations issue, I might think that labeling
>> datatypes as being compatible would be a workable approximation.
>
> Maybe I'm missing something, but if pushdown worked as follows:
>
> - Push down only system functions and operators on system types.
> - Only push down what is guaranteed to work.
>
> then the only things we would miss out on are encoding- or
> collation-sensitive string operations.
>
> Is that loss so big that it warrants a lot of effort?

The SQL/MED spec handles this with the concept of "routine mappings".
There is syntax for defining which remote "routines", meaning functions,
correspond local functions:

CREATE ROUTINE MAPPING <routine mapping name> FOR <specific routine
designator>
SERVER <foreign server name> [ <generic options> ]

<generic options> is FDW-specific, I'd imagine the idea is to give the
name of the corresponding function in the remote server. It doesn't say
anything about collations, but you could have extra options to specify
that a function can only be mapped under C collation, or whatever.

It seems tedious to specify that per-server, though, so we'll probably
still want to have some smarts in the pgsql_fdw to handle the built-in
functions and types that we know to be safe.

I've been talking about functions here, not operators, on the assumption
that we can look up the function underlying the operator and make the
decisions based on that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com