Re: FDW pushdown of non-collated functions

Hi Jean-Christophe,

On Fri, Sep 8, 2023 at 11:30 PM Jean-Christophe Arnu <jcarnu(at)gmail(dot)com> wrote:
>
> Maybe we could add another condition to the first if statement in order to allow a “no-collation” function to be pushed down even if they have “collatable” parameters. I’m not sure about the possible regressions of behaviour of this change, but it
seems to work fine with date_trunc() and date_part() (which suffers
the same problem).

That may not work since the output of the function may be dependent
upon the collation on the inputs.

There were similar discussions earlier. E.g.
https://www.postgresql.org/message-id/flat/CACowWR1ARWyRepRxGfijMcsw%2BH84Dj8x2o9N3kvz%3Dz1p%2B6b45Q%40mail.gmail.com.

Reading Tom's first reply there you may work around this by declaring
the collation explicitly.

Briefly reading Tom's reply, the problem seems to be trusting whether
the default collation locally and on the foreign server respectively
is same or not. May be a simple fix is to declare a foreign server
level option declaring that the default collation on the foreign
server is same as the local server may be a way to move forward. But
given that the problem remains unsolved for 7 years at least, may be
such a simple fix is not enough.

Another solution would be to attach another attribute to a function
indicating whether the output of that function depends upon the input
collations or not. Doing that just for FDW may not be acceptable
though.

--
Best Wishes,
Ashutosh Bapat