Re: pgsql_fdw, FDW for PostgreSQL server

Tom Lane wrote:
> Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> writes:
>> Thanks for the review. Agreed to write own depraser for pgsql_fdw
>> which handles nodes which can be pushed down. Every SQL-based FDW
>> which constructs SQL statement for each local query would need such
>> module inside.
>
> Yeah. That's kind of annoying, and the first thing you think of is
that
> we ought to find a way to share that code somehow. But I think it's
> folly to try to design a shared implementation until we have some
> concrete implementations to compare. An Oracle FDW, for instance,
would
> need to emit SQL code with many differences in detail from pgsql_fdw.
> It's not clear to me whether a shared implementation is even
practical,
> but for sure I don't want to try to build it before we've done some
> prototype single-purpose implementations.

Having written something like that for Oracle, I tend to share that
opinion. Anything general-purpose enough to cater for every whim and
oddity of the remote system would probably be so unwieldy that it
wouldn't be much easier to use it than to write the whole thing from
scratch. To illustrate this, a few examples from the Oracle case:

- Empty strings have different semantics in Oracle (to wit, they mean
NULL).
So you can push down all string constants except empty strings.
- Oracle can only represent intervals with just year and month
or with just day of month and smaller fields. So you can either
punt on intervals or translate only the ones that fit the bill.
- You can push down "-" for date arithmetic except when both
operands on the Oracle side are of type DATE, because that would
result in a NUMERIC value (number of days between).

Yours,
Laurenz Albe