Re: FDW pushdown of non-collated functions

Hi Ashutosh,

Le ven. 6 oct. 2023 à 14:16, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
a écrit :

> 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.
>

Thanks for your reply. I did not catch these messages in the archive.
Thanks for spotting them.

> 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.
>

I studied postgres_fdw source code a bit and the problem is not as easy to
solve : one could set an option telling the default remote collation is
aligned with local per "server" but nothing guaranties that the parameter
collation is known on the «remote» side.

>
> 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.
>

Yes, definitely. I thought

Anyway, you're right, after 7 years, this is a really difficult problem to
solve and there's no straightforward solution (to my eyes).
Thanks again for your kind explanations
Regards

--
Jean-Christophe Arnu